PostgreSQL replica: 'canceling statement due to conflict with recovery' - causes, settings and trade-offs

#Symptom

Reports or ETL queries on a read replica fail after running for a while:

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

Other DETAIL lines you may see:

• User was holding a relation lock for too long.
• User was holding shared buffer pin for too long.
• User transaction caused buffer deadlock with recovery.

#Why it happens

The standby replays WAL from the primary. Sometimes the next WAL record would remove data a running query still needs, or needs a lock the query holds. PostgreSQL waits up to max_standby_streaming_delay (default 30 s), then cancels the query so replication can continue.

Main sources of conflicts:

#Step 1: count conflicts by type (on the standby)

SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock, confl_tablespace
FROM pg_stat_database_conflicts
WHERE datname = current_database();

Counters are cumulative. Sample twice, an hour apart, to see which type is growing. On PostgreSQL 16+ there's also confl_active_logicalslot.

#Step 2: pick the right fix for the conflict type

#Snapshot conflicts: three options

A. Let the standby wait longer.

-- On the standby (postgresql.conf or server parameters); reload is enough
ALTER SYSTEM SET max_standby_streaming_delay = '15min';
SELECT pg_reload_conf();

Effect: queries survive longer, but replication lag grows while replay waits. Fine for a dedicated reporting replica; bad for an HA standby you fail over to.

B. Tell the primary not to remove rows the standby needs.

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

Effect: the primary's VACUUM keeps rows that standby queries still see. Cancellations mostly stop, but long standby queries now cause bloat on the primary, the same as a long transaction there. Watch it:

-- On the primary: how old is the xmin the standby is holding back?
SELECT application_name, backend_xmin, age(backend_xmin) AS xmin_age
FROM pg_stat_replication;

If xmin_age keeps growing into the hundreds of millions, a standby query is holding back vacuum. Also cap statement duration on the replica (statement_timeout for the reporting role) so one runaway report can't bloat the primary.

C. Split the workloads. Keep the HA standby strict (default delay, feedback off), and add a separate reporting replica with a long delay or with feedback on. This is the cleanest answer when you have both needs.

#Lock conflicts

Often caused by VACUUM truncating empty pages at the end of a table, which needs an ACCESS EXCLUSIVE lock that's replayed on the standby. For big, frequently-vacuumed tables you can disable truncation:

ALTER TABLE events SET (vacuum_truncate = false);

Other lock sources: DDL during business hours, TRUNCATE, partition detach/drop. Schedule them outside the reporting window.

#Buffer pin conflicts

Usually rare and short. If they grow, look for very long cursors or FETCH loops on the standby that hold pages pinned.

#Managed services

#Decision table

#Prevent it next time

• Give reporting users a role with statement_timeout and idle_in_transaction_session_timeout.
• Monitor replay lag (now() - pg_last_xact_replay_timestamp() on the standby) and backend_xmin age on the primary.
• Related: idle in transaction sessions blocking vacuum, table bloat and autovacuum.