PostgreSQL "canceling statement due to conflict with recovery": Causes and Fixes

The PostgreSQL error "canceling statement due to conflict with recovery" appears only on a hot standby. It means the replica had to apply a change from the primary that your query was in the way of, waited as long as it was allowed to, and then cancelled the query. Nothing is corrupted and the replica is working as designed. The fix is a decision: how much replication delay, or how much bloat on the primary, you are willing to trade for long queries on the replica.

A typical client sees this:

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

The statement fails with SQLSTATE 40001 (serialization_failure), the same code applications already use for retryable transactions, according to postgres.c and the error code table. In a few cases the whole session is ended instead, with "terminating connection due to conflict with recovery". The useful part is the DETAIL line: it tells you which kind of conflict you hit.

Why a replica cancels queries at all

On the primary, conflicting work simply waits: a DROP TABLE waits for the query that reads the table. The primary does not know what is running on its standbys, so it does not wait for them. The standby receives the WAL record afterwards and cannot refuse it, because the change already happened. As the hot standby documentation puts it, the standby must either delay applying WAL, and everything after it, or cancel the conflicting query.

How long it delays is set by two parameters, both 30 seconds by default (replication settings):

• max_standby_streaming_delay for WAL arriving over streaming replication;
• max_standby_archive_delay for WAL read from the archive, for example while a standby catches up.

One detail surprises people: the timer counts from when the standby received the WAL, not from when your query started. If the standby is already behind, a query can be cancelled well before 30 seconds.

Read the DETAIL line

The DETAIL texts come straight from the source. The ones you will meet in practice:

• "User query might have needed to see row versions that must be removed." A snapshot conflict, by far the most common. VACUUM on the primary removed dead rows that your long query on the replica could still see. Even VACUUM marking pages all-visible can trigger it, because index-only scans depend on the visibility map.
• "User was holding a relation lock for too long." A lock conflict. Something on the primary took an ACCESS EXCLUSIVE lock: DDL such as ALTER TABLE or DROP, an explicit LOCK, or VACUUM truncating empty pages at the end of a table, which also needs that lock (VACUUM docs).
• "User was holding shared buffer pin for too long." A buffer pin conflict: replay needed to clean a page that your query had pinned.
• "User was connected to a database that must be dropped." The database was dropped on the primary. The session is terminated, with SQLSTATE 57P04.

Two more rules from the documentation: if the conflict is over a lock held by a transaction that is idle, the whole session is terminated, not just a statement. And a cancelled query may well succeed if you simply run it again in a new transaction.

Step 1: measure before you tune

The pg_stat_database_conflicts view counts cancellations per database and per reason. It is only filled on the standby, so query it there:

-- run on the STANDBY: counters are only filled there
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin,
       confl_deadlock, confl_tablespace, confl_active_logicalslot
FROM pg_stat_database_conflicts
ORDER BY confl_snapshot + confl_lock DESC;

If confl_snapshot dominates, look at VACUUM and long queries. If confl_lock grows, look at DDL and table truncation on the primary. Also turn on log_recovery_conflict_waits (PostgreSQL 14+, off by default): the standby then logs when replay waits longer than deadlock_timeout because of a conflict, which shows the problem before queries start dying. Pair this with your replication lag graphs: conflicts and lag are two sides of the same trade.

Step 2: decide what the replica is for

This is the question that settles the configuration. The documentation's advice is clear: a standby that exists mainly for high availability should keep the delay settings short, so it never falls far behind. A standby meant for long queries can have a high or even infinite delay (-1). One server rarely does both jobs well. If the same replica is your failover target and your BI database, a 40-minute report can leave it 40 minutes behind exactly when you need to promote it.

Step 3: pick the fix

Here are the options, from the cheapest change to the biggest. All of these settings live in postgresql.conf or on the command line, not in SET:

# postgresql.conf on the standby, then: SELECT pg_reload_conf();
log_recovery_conflict_waits = on     # PostgreSQL 14+, default off
max_standby_streaming_delay = 5min   # default 30s, -1 = wait forever
max_standby_archive_delay   = 5min   # default 30s
hot_standby_feedback        = on     # default off

1. Retry in the application. For short queries that only occasionally hit a conflict, retrying on 40001 is often enough. If your code already retries serialization failures, the same path covers this error.
2. Raise max_standby_streaming_delay on a reporting replica. Queries get more time, and the price is replication delay while they run. Treat a finite value like a statement_timeout for queries that collide with replay.
3. Turn on hot_standby_feedback. The standby tells the primary which rows its queries still need, and VACUUM on the primary leaves them alone. This removes snapshot conflicts caused by cleanup, but the documentation warns it can bloat tables on the primary: one forgotten transaction on the replica now holds back VACUUM on the primary too. Keep an eye on autovacuum and bloat after you turn it on.
4. Add a physical replication slot if the replica reconnects often. Feedback alone does not protect anything while the standby is disconnected; a slot keeps the protection. But a slot also keeps WAL, and an abandoned slot can fill pg_wal, so set max_slot_wal_keep_size (replication slots).
5. Separate HA from reporting. A short-delay replica for failover and a long-delay or feedback-enabled replica for analytics is usually the cleanest fix.

None of these help with lock conflicts from DDL. For those, schedule migrations away from heavy report windows. If VACUUM truncation is the cause, the table storage option vacuum_truncate = false avoids the ACCESS EXCLUSIVE lock, at the cost of not returning that space to the OS. PostgreSQL 18 also adds a server-level vacuum_truncate setting.

-- run on the PRIMARY: whose xmin is holding back VACUUM?
SELECT application_name, client_addr, backend_xmin, age(backend_xmin)
FROM pg_stat_replication;

SELECT slot_name, slot_type, active, xmin, age(xmin)
FROM pg_replication_slots;

If age(backend_xmin) keeps growing, find the long transaction on the replica, usually a forgotten session in pg_stat_activity with state idle in transaction, and end it. From our experience, a per-user idle_in_transaction_session_timeout and a statement_timeout for reporting roles on the replica stop this before it hurts.