(opens in a new tab)

Canceling Statement Due to Conflict with Recovery

September 27, 2026 by Chat2DB Team

You move a slow report off the primary onto a read replica, and a few minutes into the run it dies with:

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

The query was valid, the replica was healthy, and nothing was misconfigured in an obvious way. This error is PostgreSQL making a deliberate trade-off: a hot standby must keep replaying WAL from the primary, and sometimes a running query stands in the way. This article explains exactly why "canceling statement due to conflict with recovery" happens, how to tell which kind of conflict you are hitting, and the real options for fixing it, from hot_standby_feedback and max_standby_streaming_delay to dedicated reporting replicas and client-side retries.

Why a standby cancels queries

A hot standby does two things at once: its startup process replays WAL records streamed from the primary, and it serves read-only queries. The primary has no idea what those queries are doing (unless you tell it; more on that later). So the primary happily does things that are perfectly safe for its own sessions:

• VACUUM removes dead row versions that no transaction on the primary can still see.
• VACUUM truncates empty pages at the end of a table, which takes an ACCESS EXCLUSIVE lock.
• DROP TABLE, TRUNCATE, ALTER TABLE, LOCK TABLE ... IN ACCESS EXCLUSIVE MODE take exclusive locks.
• DROP DATABASE or DROP TABLESPACE remove files.

All of these are written to WAL and replayed on the standby. If a standby query still needs the removed row versions, holds a conflicting lock, or is using the dropped object, replay cannot proceed. PostgreSQL then has two choices: pause replay (the standby falls behind) or cancel the query. It does the first for a bounded amount of time and then the second.

If MVCC and row visibility are fuzzy, the Postgres MVCC explainer is a good primer: snapshot conflicts are simply MVCC where the primary cannot see the standby's snapshots.

The error and its DETAIL variants

The error is raised with SQLSTATE 40001 (serialization_failure), the same class as serializable transaction failures, which signals that retrying the transaction is a sensible response. The DETAIL line tells you which kind of conflict occurred:

The database conflict is different: it terminates the session with FATAL: terminating connection due to conflict with recovery and SQLSTATE 57P04, because there is nothing left to connect to.

In practice the first two rows account for most incidents. Snapshot conflicts hit long-running queries on busy tables; lock conflicts hit queries that read a table while someone runs DDL or while autovacuum truncates it.

Measuring conflicts with pg_stat_database_conflicts

Every standby keeps per-database counters of cancelled queries. Run this on the standby (on the primary the counters are always zero):

SELECT datname,
       confl_snapshot,
       confl_lock,
       confl_bufferpin,
       confl_deadlock,
       confl_tablespace,
       confl_active_logicalslot   -- PostgreSQL 16+
FROM pg_stat_database_conflicts
WHERE datname NOT LIKE 'template%'
ORDER BY datname;

On PostgreSQL 15 and older, drop the confl_active_logicalslot column. pg_stat_database.conflicts holds the total per database if you just want one number for alerting.

Two more sources of evidence:

1. The standby's log. Every cancellation is logged with its DETAIL. Turn on log_recovery_conflict_waits (PostgreSQL 14+) and the startup process will also log whenever replay has waited longer than deadlock_timeout for a conflict, and again when the wait ends, which shows you near-misses as well as actual cancellations.

# postgresql.conf on the standby
log_recovery_conflict_waits = on

2. Replay lag. If you raise the delay settings discussed below, conflicts turn into lag instead of errors. Watch it:

-- On the standby
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay,
       pg_is_wal_replay_paused()               AS paused;

Be careful interpreting replay_delay on an idle primary: if nothing is committed, the last replay timestamp ages even though the standby is fully caught up. Our replication lag troubleshooting guide covers lag measurement in detail.

Fix 1: give queries more time with max_standby_streaming_delay

Two settings on the standby control how long replay waits before cancelling conflicting queries:

• max_standby_streaming_delay: applies to WAL received via streaming replication. Default 30s.
• max_standby_archive_delay: applies to WAL read from the archive (restore_command). Default 30s.

A value of -1 means wait forever. Both can be changed with a reload:

-- On the standby
ALTER SYSTEM SET max_standby_streaming_delay = '10min';
ALTER SYSTEM SET max_standby_archive_delay = '10min';
SELECT pg_reload_conf();

The semantics are often misunderstood. This is not a per-query time limit. It is the maximum amount by which WAL application may lag behind the time the WAL was received. When replay is stuck behind one long query, the whole standby falls behind; once replay resumes, it has to catch up, and a query that conflicts during that catch-up gets less grace, because the "budget" is measured against WAL receipt time, not against when the query started.

Trade-offs:

• Every second of delay is a second of staleness for all readers of that standby, not just the report.
• If the standby is a synchronous standby with synchronous_commit = remote_apply, commits on the primary wait for replay, so a large delay can stall writes on the primary.
• A standby used for failover with a large delay has more WAL to replay before it can be promoted.

Use large or unlimited delays only on standbys that are not failover targets and not serving latency-sensitive reads.

Fix 2: hot_standby_feedback

Snapshot conflicts exist because the primary does not know which row versions standby queries still need. hot_standby_feedback tells it:

-- On the standby
ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

With feedback on, the standby's walreceiver periodically sends the oldest xmin among its running queries to the primary. The primary treats it like a long-running transaction of its own: VACUUM will not remove row versions newer than that horizon. You can see it on the primary:

-- On the primary
SELECT application_name, client_addr, state, backend_xmin
FROM pg_stat_replication;

What hot_standby_feedback costs

The cost moves from the standby to the primary: bloat. A four-hour report on the standby now holds back vacuum cleanup on the primary for four hours, exactly as if the report were running on the primary itself. Dead tuples accumulate in heavily updated tables, index bloat grows, and in extreme cases the held-back horizon delays freezing. Monitor dead tuples and consider a statement_timeout on the standby for ad hoc users so a forgotten session cannot pin the horizon indefinitely:

ALTER ROLE reporting_user SET statement_timeout = '2h';
ALTER ROLE reporting_user SET idle_in_transaction_session_timeout = '10min';

The vacuum and autovacuum tuning guide covers how to spot vacuum being held back.

What hot_standby_feedback does not fix

Feedback only prevents snapshot conflicts (and the logical slot ones on PostgreSQL 16+). Lock conflicts still happen: if someone runs TRUNCATE or ALTER TABLE on the primary, the ACCESS EXCLUSIVE lock is replayed and your standby query on that table is still cancelled after the delay. Buffer pin conflicts also remain possible.

One common lock-conflict source is autovacuum truncating empty pages at the end of a table. You can disable that per table:

ALTER TABLE events SET (vacuum_truncate = false);

PostgreSQL 18 also adds a server-wide vacuum_truncate setting. Disabling truncation means empty trailing space is kept in the file instead of being returned to the operating system, so reserve it for tables where you actually see lock conflicts.

Feedback and disconnections

Feedback is only sent while the standby is connected. If the connection drops and the primary vacuums in the meantime, the standby can still hit snapshot conflicts after reconnecting. Using a physical replication slot makes the horizon survive disconnects: the slot's xmin persists on the primary.

-- On the primary
SELECT pg_create_physical_replication_slot('reporting_replica');
 
-- On the standby (postgresql.conf / ALTER SYSTEM)
-- primary_slot_name = 'reporting_replica'

The flip side is that a dead standby with a slot holds back vacuum and retains WAL indefinitely. Set max_slot_wal_keep_size to cap retained WAL, monitor pg_replication_slots, and drop abandoned slots. PostgreSQL 18 adds idle_replication_slot_timeout to invalidate slots that stay inactive too long. The replication slots guide walks through monitoring and cleanup.

-- On the primary: slots holding back xmin or WAL
SELECT slot_name, slot_type, active, xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

What about vacuum_defer_cleanup_age?

Older tuning guides recommend vacuum_defer_cleanup_age on the primary, which delayed cleanup by a fixed number of transactions. It was hard to size, did not map to time, and had correctness problems. It was removed in PostgreSQL 16. If you are on 16 or later, setting it produces an unrecognized configuration parameter error; use hot_standby_feedback (optionally with a slot) instead.

Fix 3: a dedicated reporting replica

The cleanest architecture separates the two jobs a standby can do:

• HA / failover standbys: low delay, hot_standby_feedback off or on depending on read traffic, short-running queries only.
• Reporting / analytics standby: tuned for long queries, not in the failover set.

A reasonable starting point for the reporting replica:

# postgresql.conf on the reporting standby
hot_standby = on
max_standby_streaming_delay = -1     # never cancel; accept lag instead
max_standby_archive_delay = -1
hot_standby_feedback = off           # keep primary free of bloat
log_recovery_conflict_waits = on

With -1 and feedback off, the primary vacuums freely, and the reporting standby simply pauses replay while a conflicting query runs, then catches up. Consumers of that replica must tolerate data that is minutes or hours old during big reports. If they cannot, flip it around: feedback on, moderate delay, and accept some primary bloat.

For a one-off very long export you can also pause replay explicitly on a dedicated replica, then resume:

-- On the reporting standby, as superuser or a role granted EXECUTE on these functions
SELECT pg_wal_replay_pause();
-- run the report
SELECT pg_wal_replay_resume();

While paused, WAL accumulates on the standby's disk (and on the primary if a slot is used), so do not forget the resume step. Setting up another standby is covered in Postgres streaming replication setup.

Fix 4: make queries shorter or retry them

Sometimes the right answer is on the application side:

1. Retry on SQLSTATE 40001. Conflicts are transient. A short query cancelled because of a vacuum replay will almost certainly succeed on retry. The same retry wrapper you use for serializable failures (see could not serialize access fixes) works here.
2. Break long reports into chunks. Ten one-minute queries over key ranges are far less likely to be cancelled than one ten-minute query, and each can be retried independently.
3. Avoid long read transactions. In REPEATABLE READ, the snapshot lives for the whole transaction, so a transaction with many short queries is as exposed as one long query. Use READ COMMITTED for batch readers that do not need a consistent snapshot across statements.
4. Schedule DDL away from report windows to avoid lock conflicts that feedback cannot prevent.

A minimal retry pattern in a shell job:

#!/usr/bin/env bash
set -u
for attempt in 1 2 3; do
  if psql "host=replica dbname=app user=reporting_user" \
       -v ON_ERROR_STOP=1 -f daily_report.sql -o /tmp/report.csv; then
    exit 0
  fi
  echo "attempt $attempt failed, retrying in 30s" >&2
  sleep 30
done
exit 1

This retries on any error; in application code, check the SQLSTATE and retry only on 40001.

When you are investigating which queries are being cancelled, it helps to run the conflict and replication queries above side by side against the primary and the standby. Chat2DB (opens in a new tab) lets you keep both connections open in one workspace, which makes comparing pg_stat_replication on one side with pg_stat_database_conflicts on the other straightforward.

A decision checklist

1. Check the DETAIL and pg_stat_database_conflicts on the standby to identify the conflict type.
2. Mostly snapshot conflicts? Choose between hot_standby_feedback = on (bloat on primary) and a larger max_standby_streaming_delay (lag on standby).
3. Mostly lock conflicts? Find the DDL or vacuum truncation on the primary; reschedule it, set vacuum_truncate = false on hot tables, or raise the delay.
4. Long analytic workloads? Move them to a dedicated reporting replica with its own settings.
5. Always add retries for 40001 in clients that read from standbys.

FAQ

Is "canceling statement due to conflict with recovery" a sign of a broken replica?

No. It is expected behaviour on a hot standby when a query conflicts with WAL replay for longer than max_standby_streaming_delay or max_standby_archive_delay. The replica is working as designed.

Does hot_standby_feedback eliminate all recovery conflicts?

No. It prevents snapshot conflicts caused by vacuum cleanup on the primary. Lock conflicts (from DDL, TRUNCATE, vacuum truncation), buffer pin conflicts, and tablespace or database drops can still cancel queries.

Should I set max_standby_streaming_delay to -1?

Only on standbys that are not failover targets and whose readers can tolerate lag. With -1, a single long query can stop replay indefinitely, so the standby can fall arbitrarily far behind.

What replaced vacuum_defer_cleanup_age?

Nothing directly. It was removed in PostgreSQL 16. Use hot_standby_feedback, optionally with a physical replication slot so the horizon survives reconnects, or accept lag with the standby delay settings.