canceling statement due to conflict with recovery

What it means

A hot standby (read replica) replays the primary’s changes as they arrive, and lets you run queries at the same time. Sometimes the next change to replay would pull the ground from under a running query:

• the primary’s VACUUM removed old row versions that your query’s snapshot can still see;
• the primary took an ACCESS EXCLUSIVE lock (ALTER TABLE, DROP TABLE, TRUNCATE, and some vacuum truncation) on a table your query is reading.

The replica waits up to max_standby_streaming_delay (30 seconds by default) for the query to finish. If it doesn’t, the replica cancels the query so it can catch up:

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

The DETAIL names the kind of conflict; a lock conflict says User was holding a relation lock for too long. The code is 40001, the same as a serialisation failure, because the fix is the same: run it again.

Common causes

1. Long queries on the replica (reports, exports, analytics) while the primary is busy updating and vacuuming the same tables.
2. Migrations on the primary that take ACCESS EXCLUSIVE locks on tables that replica queries read.
3. A short max_standby_streaming_delay chosen to keep the replica up to date, which leaves little time for queries.

How to fix it

Retry

For short queries, retrying is usually enough: the conflict is over by the time you run it again. Treat 40001 from a replica like any serialisation failure and retry the transaction a few times.

Give replica queries more time

On the replica:

max_standby_streaming_delay = 5min    # -1 waits forever

It takes effect on reload. The cost: while a query holds up replay, the replica falls behind the primary, so everything else reading from it sees older data. Watch pg_last_xact_replay_timestamp().

Stop vacuum removing rows the replica needs

hot_standby_feedback = on

The replica then tells the primary which rows its queries still need, and the primary’s vacuum keeps them. That ends the “row versions that must be removed” conflicts, but long replica queries now cause bloat on the primary, as if they ran there. Lock conflicts still happen.

Run long reports somewhere else

A replica dedicated to reporting, with a long delay and feedback on, keeps the replicas that serve the application fresh. Or run the long query on the primary, outside busy hours.

See how often it happens

SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts;

Run it on the replica; the counters show which kind of conflict cancels your queries.

Reproduce it

We used a throwaway pair of PostgreSQL 18.6 containers: a primary, and a hot standby made with pg_basebackup and started with max_standby_streaming_delay = 5s. On the standby, a query that held its snapshot for 20 seconds:

SELECT count(*), pg_sleep(20) FROM orders;

Meanwhile, on the primary:

DELETE FROM orders WHERE id <= 100000;
VACUUM orders;

With the 5-second limit, the standby’s query failed long before its 20 seconds were up:

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

With ALTER TABLE orders ADD COLUMN note text on the primary instead, and \set VERBOSITY verbose on the standby:

ERROR:  40001: canceling statement due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.

After adding hot_standby_feedback = on to the standby’s configuration and reloading, a 12-second version of the query finished while the primary deleted 50,000 more rows and vacuumed, and the primary’s VACUUM (VERBOSE) reported 50000 are dead but not yet removable: the rows kept for the replica.

In Inlet

Inlet connects to a replica like any other server, and environment tags with colours help you keep track of which connection is which. EXPLAIN ANALYZE is drawn as a tree with the slowest step highlighted, which helps make a long report fast enough to finish before replay needs to move on.