Hot Standby Query Conflicts and Cancellations #

A nightly report on a read replica fails roughly one night in three with ERROR: canceling statement due to conflict with recovery. It is not a timeout, nothing is deadlocked, and re-running it usually works. The replica cancelled it because WAL replay needed to remove rows the query was still reading, and replay does not wait indefinitely.

The wider comparison of primary and standby behaviour is in replica and standby query plans. This page is about conflicts specifically.

The Condition #

A hot standby must apply WAL in order and without falling behind, while simultaneously serving read-only queries against the same data. When a WAL record would change something a running query depends on, that is a recovery conflict, and the standby resolves it by delaying replay for a while and then cancelling the query.

There are six kinds, and pg_stat_database_conflicts counts each:

Two settings control the delay before cancellation: max_standby_streaming_delay for WAL arriving over the connection and max_standby_archive_delay for WAL read from the archive. Both default to 30 seconds and can be set to -1, meaning “wait indefinitely” — which protects queries and allows replication lag to grow without limit.

hot_standby_feedback attacks the cause instead: the standby tells the primary the oldest snapshot it needs, and the primary refrains from cleaning up rows still required. Snapshot conflicts largely disappear. The price is paid on the primary, where those dead rows accumulate exactly as if a long transaction were open there — including holding back the vacuuming discussed in autovacuum and plan stability.

Three ways to stop the cancellations Three options. Raising max_standby_streaming_delay lets replay wait longer, which protects queries at the cost of replication lag. Enabling hot_standby_feedback prevents the primary cleaning up rows the standby needs, which removes most conflicts at the cost of bloat on the primary. Moving the query to the primary avoids the question entirely. which cost is acceptable? replication lag raise max_standby_delay standby falls behind bloat on the primary hot_standby_feedback = on cleanup deferred load on the primary run the query there no conflict possible every option moves the cost somewhere; none removes it

Annotated Evidence #

The error and its counters:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
-- "row versions that must be removed" identifies it as a snapshot conflict
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock, confl_tablespace
FROM pg_stat_database_conflicts WHERE datname = 'appdb';
 datname | confl_snapshot | confl_lock | confl_bufferpin | confl_deadlock | confl_tablespace
---------+----------------+------------+-----------------+----------------+------------------
 appdb   |            412 |          4 |               8 |              0 |                0
-- overwhelmingly snapshot conflicts: hot_standby_feedback is the lever

Lag while replay is waiting:

SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag,
       pg_is_wal_replay_paused() AS paused;
 replay_lag | paused
------------+--------
 00:04:12   | f
-- four minutes behind: replay is being held up by a long-running query

And the cost on the primary once feedback is enabled:

SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 3;
   relname   | n_dead_tup | last_autovacuum
-------------+------------+------------------
 orders      |    4102008 | 2026-09-15 08:12
-- autovacuum runs but cannot remove rows the standby still needs
Three readings that decide the trade Three items are annotated. The error detail naming row versions that must be removed identifies a snapshot conflict. The counters in pg_stat_database_conflicts show which kind dominates. Dead tuples accumulating on the primary after enabling feedback show where the cost moved. DETAIL: row versions that must be removed a snapshot conflict confl_snapshot 412 vs others near zero feedback is the right lever n_dead_tup rising on the primary the cost of feedback check the counters before choosing a setting

Step-by-Step Resolution #

  1. Identify the conflict type from the error detail and from pg_stat_database_conflicts. Snapshot conflicts and lock conflicts have different remedies.

  2. For snapshot conflicts, consider hot_standby_feedback = on. It is the direct fix and it is usually right when the standby’s queries are short to medium and the primary has vacuum headroom.

  3. Bound the damage on the primary. With feedback on, a runaway query on the standby holds back cleanup indefinitely, so pair it with statement_timeout on the standby’s roles and with idle_in_transaction_session_timeout — the settings discussed in statement timeouts behind a pooler.

  4. For long reports, raise max_standby_streaming_delay instead, accepting lag during the report window. A value of a few minutes covers most reports; -1 removes the limit and should be used only where unbounded lag is genuinely acceptable.

  5. For lock conflicts, look at the DDL. Migrations on the primary replay as lock requests on the standby; scheduling them away from long-running standby queries removes the collision.

  6. Monitor both sides. Conflict counters on the standby, dead tuples and vacuum progress on the primary. A change to either setting moves cost between them, so both need watching.

  7. Consider a second standby where reporting and high availability have genuinely different requirements: one tuned for queries with feedback and generous delays, one kept close to the primary for failover.

Two settings, two different costs Raising max_standby_streaming_delay lets replay wait, so queries complete but the standby falls behind and failover recovery time grows. Enabling hot_standby_feedback stops the primary removing needed rows, so queries complete and lag stays low, but dead tuples accumulate on the primary and vacuum cannot reclaim them. raise the standby delay queries complete replay waits, lag grows failover recovery slower no effect on the primary hot_standby_feedback = on queries complete lag stays low primary retains dead rows vacuum blocked meanwhile a timeout on the standby bounds the right-hand cost

Before and After #

-- BEFORE: default settings, nightly report on the standby
ERROR: canceling statement due to conflict with recovery   (about one night in three)

-- AFTER: hot_standby_feedback = on, statement_timeout = 45min on the reporting role
report completes; primary dead tuples peak at 1.2M during the window and are vacuumed after

Why the delay settings are not simply generous #

It is tempting to set max_standby_streaming_delay = -1 and stop thinking about conflicts. The reason not to is that a standby’s purpose is usually twofold: it serves reads and it is the failover target, and unbounded replay lag degrades the second role silently.

A standby four hours behind is four hours of WAL away from being able to take over, which turns a failover from seconds into a long wait at exactly the moment nobody can afford one. If the standby is purely a reporting server and something else is the failover target, unbounded delay is defensible; if it is both, the delay should be bounded by how much recovery time is acceptable.

That framing usually makes the choice clear. Feedback keeps lag low and moves the cost to the primary’s vacuum, which is measurable and bounded by a statement timeout. Delay keeps the primary clean and moves the cost to recovery time, which is bounded by nothing but the setting itself.

Common Pitfalls #

Enabling feedback without a statement timeout. One runaway query blocks cleanup on the primary indefinitely. Diagnostic signal: dead tuples rising on the primary with no long transaction there. Fix: timeouts on the standby’s roles.

Setting the delay to -1 on a failover target. Recovery time becomes unbounded. Diagnostic signal: hours of replay lag. Fix: bound it by acceptable recovery time.

Treating all conflicts as snapshot conflicts. Lock conflicts need scheduling, not feedback. Diagnostic signal: confl_lock dominating the counters. Fix: schedule DDL away from long queries.

Retrying the query unchanged. It conflicts again on the next similar replay. Diagnostic signal: intermittent failures with no pattern in the query. Fix: change the settings, not the retry count.

Frequently Asked Questions #

What causes “canceling statement due to conflict with recovery”? #

WAL replay on the standby needed to change something a running query depended on — most often removing row versions the query’s snapshot still required. After max_standby_streaming_delay the standby cancels the query so replay can continue.

What does hot_standby_feedback do? #

It tells the primary the oldest snapshot the standby still needs, so the primary does not remove those row versions. Snapshot conflicts largely stop, at the cost of dead tuples accumulating on the primary until the standby’s queries finish.

Should I set max_standby_streaming_delay to -1? #

Only if unbounded replication lag is acceptable, which it is not when the standby is also the failover target: lag translates directly into recovery time. Bound it by how long a failover may take.

Up: Replica and Standby Query Plans