🚨 PostgreSQL Error Guide: 'canceling statement due to conflict with recovery' Standby Query Conflicts

Exact Error Message

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

The SQLSTATE is 40001 (serialization_failure), and the DETAIL line varies by conflict type (snapshot, lock, tablespace, buffer pin, or database).

What the Error Means

This error occurs only on a hot standby (a read replica that serves queries while replaying WAL from the primary). The standby must keep applying WAL to stay current, but a long-running read query on the standby may depend on row versions or locks that the incoming WAL wants to remove or change. When the replaying recovery process and a running query conflict, PostgreSQL resolves it by canceling the query so replay can proceed.

The most common variant — “User query might have needed to see row versions that must be removed” — is a snapshot conflict: the primary vacuumed away old tuples, that cleanup arrived as WAL on the standby, and a query still needed those tuples for its MVCC snapshot. Because the standby cannot fall behind indefinitely, it sacrifices the query. This is the fundamental tension of hot standby: keeping replicas current versus letting long read queries run. The tuning levers are max_standby_streaming_delay, max_standby_archive_delay, and hot_standby_feedback.

Common Causes

• Long-running read queries on the standby. Reporting, analytics, or backup queries that run for many seconds collide with WAL cleanup records.
• Aggressive VACUUM on the primary. Vacuum on the primary removes dead tuples; the resulting WAL forces the standby to cancel queries that still need them.
• Low max_standby_streaming_delay. A small (or zero) delay means the standby cancels conflicting queries almost immediately to stay current.
• hot_standby_feedback disabled. Without feedback, the primary does not know the standby has old snapshots open and vacuums freely, causing snapshot conflicts.
• Lock conflicts from DDL. An ALTER TABLE or DROP on the primary replays as a conflicting lock on the standby, canceling readers of that relation.

How to Reproduce the Error

On a hot standby with a low max_standby_streaming_delay (for example 1s) and hot_standby_feedback = off, start a long query on the replica:

-- on the standby
SELECT pg_sleep(30), count(*) FROM big_table;

Meanwhile, on the primary, generate dead tuples and vacuum:

-- on the primary
UPDATE big_table SET updated_at = now();
VACUUM big_table;

When that vacuum’s WAL reaches the standby, the long query is canceled:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Diagnostic Commands

Confirm you are on a standby (returns t in recovery):

psql -c "SELECT pg_is_in_recovery();"

Check the conflict-related tuning parameters:

psql -c "SELECT name, setting FROM pg_settings \
WHERE name IN ('max_standby_streaming_delay','max_standby_archive_delay','hot_standby_feedback');"

View per-database recovery conflict counters (read-only):

psql -c "SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock, confl_tablespace \
FROM pg_stat_database_conflicts WHERE datname = current_database();"

Check replication lag to understand how far behind the standby is:

psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;"

Find long-running queries on the standby:

psql -c "SELECT pid, now() - query_start AS runtime, left(query,60) AS query \
FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start LIMIT 10;"

Look for the conflicts in the server log:

sudo journalctl -u postgresql --no-pager | grep -A1 "conflict with recovery" | tail -30

Step-by-Step Resolution

1. Confirm you are on a standby. This error is impossible on a primary. Verify with pg_is_in_recovery().

2. Categorize the conflict. Use pg_stat_database_conflicts to see which counter is climbing. A high confl_snapshot points to vacuum/snapshot conflicts; confl_lock points to DDL replaying conflicting locks.

3. Enable hot_standby_feedback for snapshot conflicts. Setting hot_standby_feedback = on on the standby tells the primary about the standby’s oldest snapshot, so the primary delays vacuuming those tuples. This eliminates most snapshot cancellations at the cost of slightly more bloat on the primary.

4. Increase the standby delay tolerance. Raise max_standby_streaming_delay (and max_standby_archive_delay) to give long queries more time before they are canceled. The trade-off is that the standby can fall further behind during conflicts.

5. Move long queries appropriately. If reporting queries are very long, either run them with feedback on, accept some replica lag, or run them on a dedicated reporting replica tuned for that purpose rather than one expected to stay near-real-time.

6. Add retry logic. Like other 40001 errors, a canceled standby query can often simply be retried, since the conflicting WAL has now been applied.

Prevention and Best Practices

• Enable hot_standby_feedback = on on replicas that serve long read queries to prevent snapshot conflicts, accepting modest extra bloat on the primary.
• Tune max_standby_streaming_delay to balance query tolerance against acceptable replica lag for your workload.
• Dedicate a reporting/analytics replica with generous delay settings, separate from low-latency read replicas.
• Wrap read-replica queries in retry logic, since cancellations are transient.
• Monitor pg_stat_database_conflicts to see which conflict type dominates and tune accordingly.
• For triage, the free incident assistant can turn a recovery-conflict log block into the right tuning lever for your standby.

Related Errors

• terminating connection due to conflict with recovery — a stronger form that drops the connection instead of just the statement.
• could not serialize access due to concurrent update — also SQLSTATE 40001, but on a primary under high isolation.
• canceling statement due to statement timeout — a time-based cancel unrelated to replication.
• the database system is in recovery mode — the standby is still catching up and not yet accepting queries.

Frequently Asked Questions

Why does this only happen on replicas? The error is specific to hot standby. A standby must keep replaying WAL to stay current, and that replay can conflict with queries that still need soon-to-be-removed row versions or held locks. A primary has no incoming WAL to replay, so it never produces this conflict.

What does hot_standby_feedback actually do? It makes the standby report its oldest in-progress snapshot back to the primary. The primary then avoids vacuuming tuples the standby still needs, preventing snapshot conflicts. The downside is slightly increased table bloat on the primary because vacuum is delayed.

Will increasing max_standby_streaming_delay cause replication lag? It can. A higher delay lets conflicting queries run longer before being canceled, but during that window the standby pauses WAL replay, so it falls behind. Choose a value that tolerates your queries without exceeding acceptable lag.

Should I retry the canceled query? Usually yes. By the time the query is canceled, the conflicting WAL has been applied, so an immediate retry typically succeeds. Treat SQLSTATE 40001 as transient and retry with a short backoff.

Can I eliminate these conflicts entirely? You can come close by enabling hot_standby_feedback and using generous delay settings on a dedicated reporting replica. Lock conflicts from DDL on the primary are harder to avoid completely, but snapshot conflicts (the common case) are largely solved by feedback.