On a PostgreSQL streaming standby serving a newsroom's reporting team, long queries fail with 'canceling statement due to conflict with recovery'; why does this happen, and what are your options?
answer
- replay cannot wait forever
- vacuum removed rows you still see
- 30 seconds, then cancel
- feedback trades bloat for fewer cancels
- pg_stat_database_conflicts
basics
~20 sReplay of the primary's WAL must not wait forever: when a record, such as vacuum cleanup or an ACCESS EXCLUSIVE lock, conflicts with a standby query, replay waits up to max_standby_streaming_delay (30 s) and then cancels the query with SQLSTATE 40001.
solid answer
~40 sThe primary does not know what runs on the standby, so `VACUUM` there can remove row versions a standby snapshot still needs, and DDL takes `ACCESS EXCLUSIVE` locks that replay must apply. The standby's startup process waits up to `max_standby_streaming_delay` (default 30 s, measured from when the WAL arrived) and then cancels the conflicting query with SQLSTATE `40001`; `pg_stat_database_conflicts` on the standby counts each kind. Options, each with a cost: `hot_standby_feedback = on` stops snapshot conflicts but holds back cleanup on the primary, so bloat; a larger or `-1` delay lets queries finish but lets replay fall behind, acceptable only on a standby nobody fails over to; turning off `vacuum_truncate` on the hot tables removes the truncation lock conflicts that feedback cannot prevent; and the application retries, which often succeeds.
code
ini · 5 lines# postgresql.conf on the newsroom reporting standby
hot_standby_feedback = on
max_standby_streaming_delay = 15min
log_recovery_conflict_waits = on
statement_timeout = 30mingo deeper
Recall that a standby must keep applying the primary's changes, so a standby query that blocks replay can be cancelled after a grace period, 30 seconds by default.
Explain the conflict kinds, especially vacuum removing rows a standby snapshot still sees and ACCESS EXCLUSIVE locks, and the two max_standby delay settings with their defaults.
Choose remedies with their costs: feedback versus primary bloat, long delays versus stale and slow-to-promote standbys, vacuum_truncate for lock conflicts, retries on 40001, and separate HA and reporting standbys.
Decide where analytics belongs at all: a tuned hot standby, a logical replica with its own indexes and retention, or a separate warehouse, judged by query length, freshness needs and the cleanup cost on the primary.
## Why the standby cannot simply wait On a primary, two conflicting operations wait for each other: a `DROP TABLE` waits for the query reading that table. A **hot standby** cannot do that. The WAL record describing the drop, or the removal of dead row versions by `VACUUM`, has already happened on the primary, which knows nothing about queries on the standby. The standby's startup process must apply it — and while it waits, **all later WAL waits too**, so the standby falls further behind. PostgreSQL therefore lets replay wait only for a bounded time and then **cancels the standby query** in the way. ## The kinds of conflict | Conflict | Caused on the primary by | Counter in `pg_stat_database_conflicts` | |---|---|---| | snapshot | `VACUUM` or page pruning removing row versions a standby snapshot can still see, or `VACUUM` marking pages all-visible | `confl_snapshot` | | lock | an `ACCESS EXCLUSIVE` lock: most DDL, `LOCK TABLE`, and `VACUUM` truncating empty pages off the end of a table | `confl_lock` | | buffer pin | a cleanup record needing a page a standby query has pinned | `confl_bufferpin` | | tablespace | `DROP TABLESPACE` while a standby query uses it for temporary files | `confl_tablespace` | | deadlock | a standby query and replay waiting on each other | `confl_deadlock` | | database | `DROP DATABASE`: the whole session is terminated (SQLSTATE `57P04`) | not counted; the database is gone | A logical slot on the standby that must be invalidated is counted in `confl_active_logicalslot`. The error detail names the kind, for example `User query might have needed to see row versions that must be removed.` Ordinary cancellations carry SQLSTATE **`40001`**, the same code as a serialization failure, and a query that was cancelled may well succeed when retried. A session idle in a transaction that holds a conflicting lock is terminated rather than cancelled. ## The grace period - `max_standby_streaming_delay` (WAL arriving by streaming) and `max_standby_archive_delay` (WAL restored from an archive) both default to **30 s**; `-1` waits forever. Both change with a reload. - The delay is measured from **when the standby received the WAL**, not from when the query started, so a standby that is already behind gives each query far less than 30 s. - `log_recovery_conflict_waits` (off by default) logs when replay has waited longer than `deadlock_timeout`, which shows the conflicts that resolve on their own as well as the ones that end in a cancel. ## Options and what each costs | Option | What it fixes | What it costs | |---|---|---| | `hot_standby_feedback = on` on the standby | snapshot conflicts | the standby's oldest snapshot (`backend_xmin` in the primary's `pg_stat_replication`) holds back cleanup on the primary: bloat on busy tables | | larger or `-1` `max_standby_streaming_delay` | every kind, by waiting | replay stalls behind long queries; the standby's data and its failover readiness fall behind | | `vacuum_truncate` off for the hot tables | lock conflicts from end-of-table truncation | empty pages at the end of the table are not returned to the OS | | retry on `40001` in the client | transient cancels | wasted work on very long reports | | a separate standby for reporting | keeps HA standbys short-delayed | another server to run | `hot_standby_feedback` is sent at most once per `wal_receiver_status_interval`, only while the standby is connected, and passes upstream through cascading standbys. It does **nothing for lock, buffer-pin, tablespace or database conflicts**. Its cleanup cost is no worse than running the same query on the primary — the offloading is still worth it — but a reporting query that runs for hours now holds back `VACUUM` on the primary for hours. In PostgreSQL 18, `vacuum_truncate` also exists as a server setting; before 18 it is set per table as a storage parameter, which still overrides the server value. ## A setup for the newsroom 1. Keep the **HA standbys** at the default delays and feedback off, so they stay current and ready to promote. 2. Give the **reporting standby** `hot_standby_feedback = on` and a generous `max_standby_streaming_delay` such as `15min`, and cap reports with `statement_timeout` there, so no single query holds cleanup back without limit. 3. Set `vacuum_truncate = false` on the few large tables that report queries read, such as `article_views`. 4. Watch `pg_stat_database_conflicts` on the reporting standby, and the bloat of the hot tables on the primary.
- Why does hot_standby_feedback not stop every recovery-conflict cancellation on a PostgreSQL standby?It only reports the standby's oldest snapshot so `VACUUM` on the primary keeps the rows it needs, which prevents snapshot conflicts. Lock conflicts — DDL or `VACUUM` truncating a table end under `ACCESS EXCLUSIVE` — buffer-pin, tablespace and database conflicts are untouched, and while the standby is disconnected no feedback is sent at all.
- Why set max_standby_streaming_delay = -1 only on a PostgreSQL standby nobody fails over to?With `-1`, replay waits as long as any conflicting query runs, so the standby can fall hours behind. Promoting it would first mean replaying that backlog, lengthening the outage, and its reads are stale meanwhile. If it were a synchronous standby under `remote_apply`, primary commits would wait on it too.
- Where does a PostgreSQL primary show what a standby's hot_standby_feedback is holding back?In `pg_stat_replication.backend_xmin` for that standby's row: the oldest transaction ID its queries still need. Compare its age with the primary's own long transactions; a reporting query that runs for hours keeps that value old, and dead rows newer than it cannot be cleaned up on the primary until the query ends.
A newspaper archive reading room must reshelve on the publisher's schedule. A reader may keep an old edition for a grace period, then the clerk takes it back. Telling the publisher not to pulp old editions while anyone reads stops those take-backs, but the warehouse fills — and it does nothing when the publisher demolishes a whole shelf.
- Recovery-conflict cancels mean the standby ran out of memory or disk.
- hot_standby_feedback prevents every kind of standby query cancellation.
- max_standby_streaming_delay guarantees each standby query at least 30 seconds.
- Setting max_standby_streaming_delay = -1 is harmless on any standby.
- A query cancelled by a recovery conflict will fail again if retried.
- hot_standby_feedback has no effect on the primary at all.
Assumes PostgreSQL 18, where vacuum_truncate became a server setting; earlier releases offer it only as a per-table storage parameter.