canceling statement due to conflict with recovery

ERROR: canceling statement due to conflict with recovery (SQLSTATE 40001) comes from a read replica — a hot standby — and means that the primary did something the replica has to replay, and your query on the replica was in the way. The DETAIL line says what: User query might have needed to see row versions that must be removed means the primary cleaned up rows your query’s snapshot could still see — by vacuum, or by ordinary queries pruning old row versions as they read pages; User was holding a relation lock for too long means an ACCESS EXCLUSIVE lock on the primary — ALTER TABLE, DROP, TRUNCATE, or a plain VACUUM truncating empty pages at the end of the table — needed the table you were reading. The replica waits max_standby_streaming_delay — 30 seconds by default — and then cancels the query.

We built a primary and a streaming standby on PostgreSQL 17.11 with a table of 100,000 rows, and measured each case below on the standby.

When does a replica cancel a query?

Three things in that table are worth knowing before you look at settings.

An ERROR keeps the connection, a FATAL does not. A statement running at top level got an error, and the session could continue — inside a transaction after a rollback, since the next statement got current transaction is aborted (see current transaction is aborted). Two cases got FATAL and a disconnect instead: a session idle inside a transaction that still held what replay needed — a lock on the table, or a REPEATABLE READ snapshot — and a statement running inside a savepoint, which many ORMs open for nested transactions. psql only noticed the disconnect at its next request, 12 seconds after the server had ended the session. An idle session outside a transaction, and a READ COMMITTED transaction idle between statements, hold no snapshot and were not touched by the vacuum.

The conflict is about the snapshot, not the table. The last row of the table surprised us most: a transaction that never touched t was terminated by a vacuum of t. What counts is the transaction’s oldest visible point (backend_xmin 830 in our test, the same number as the snapshotConflictHorizon the standby logged), not which tables it read. A long-running report on a replica is exposed to cleanup anywhere in the same database — by vacuum or autovacuum, and also by plain selects on the primary, which prune old row versions: an update followed by a select count(*), without any vacuum, cancelled a standby query too. A vacuum in another database did not affect it.

The 30 seconds count from when the standby received the conflicting WAL, not from when your query started. When replay is already waiting on one query, the clock for the next conflict is already running: a second ALTER TABLE, sent 20 seconds into the first wait, cancelled its reader 10 seconds after it arrived — at the same moment as the first. The documentation says the same: the setting “is not the same as the maximum length of time a query can run before cancellation; rather it is the maximum total time allowed to apply WAL data once it has been received from the primary server.”

What do max_standby_streaming_delay and hot_standby_feedback cost?

Every fix trades the cancelled query for something else. We measured the two settings that matter:

The documentation describes the same trade: with hot_standby_feedback, “this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat”. In a multi-tenant SaaS that is concrete: one tenant’s long export on the replica held back cleanup of every table in every database on the primary — a database per tenant does not change that. If you rely on the setting, use a physical replication slot. And -1 turns the replica into a stale copy for as long as the longest transaction on it stays open, including one that is idle: a forgotten idle REPEATABLE READ session held replay for 33 seconds, and new rows stayed invisible until it committed. A user who just saved something and reads it back from such a replica does not see it.

For most applications the sensible split is: keep the default or a modest delay for the interactive replica, run long reports on a separate replica with a high delay and idle_in_transaction_session_timeout set, and treat 40001 as retryable — including when it arrives as a dropped connection, which then means a new connection, not just a new transaction. The documentation says so directly: “Canceled queries may be retried immediately (after beginning a new transaction, of course).”

How do you see which conflicts happen?

On the standby, pg_stat_database_conflicts counts them per type. After our five conflicts (three cancelled statements, two terminated sessions) it read confl_snapshot = 3, confl_lock = 2, and 0 for confl_bufferpin, confl_deadlock and confl_tablespace; pg_stat_database.conflicts said 5. With log_recovery_conflict_waits = on the standby also logs each wait as it happens (recovery still waiting after …) and how long it lasted (finished waiting after 30356.120 ms). For a cancelled query that is simply the limit: it tells you replay was blocked, and by which process, not how long the query would have needed.

What we did not measure

Buffer-pin conflicts, which come from vacuum needing a page a standby query has pinned. Archive recovery (max_standby_archive_delay). Managed replicas (Amazon RDS, Aurora), which appear in searches for this error and have their own defaults. Logical replication. Retrying a cancelled query. DROP DATABASE on the primary, which according to the documentation disconnects standby sessions in that database at once, with a different code.

Sources: PostgreSQL 17 documentation on hot standby and replication settings, read on 21 and 22 October 2026. All measurements are our own, on a PostgreSQL 17.11 primary and streaming standby in containers, on 21 October 2026.