Detecting Hot Standby Query Cancellations on Postgres Replicas

Problem statement: Queries against a PostgreSQL read replica intermittently fail with ERROR: canceling statement due to conflict with recovery — not on the primary, not predictably, and often in bursts that line up with nothing obvious in your application — and you need to prove the cause and pick a fix that does not simply trade the errors for unbounded replication lag.

Symptom Identification

The defining symptom is a specific error that can only occur on a hot standby, never on a primary:

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

The DETAIL line varies by conflict class and is the first clue to the cause. Other detail messages you will see include User was holding a relation lock for too long, User was holding shared buffer pin for too long, and User was or might have been using tablespace that must be dropped. All share SQLSTATE 40001 (serialization failure), which is why a retry is a legitimate first-line response.

Distinguishing this from an ordinary slow query matters, because the fixes are unrelated. A slow query is fixed by tuning; a cancelled query executed fine and was killed by the replica’s recovery process. The tell-tale signs that you are looking at cancellations, not slowness:

• The failures arrive in bursts that correlate with write, VACUUM, or DDL activity on the primary, not with query volume on the replica.
• The same query succeeds when re-run immediately, then fails again later — the hallmark of an intermittent conflict.
• The affected queries are the long-running ones (reports, exports); short OLTP reads rarely run long enough to hit max_standby_streaming_delay.

This is the cancellation branch of the query lifecycle covered in the parent guide, examined in full.

Root Cause Analysis

A hot standby continuously applies WAL from the primary while simultaneously serving read queries. These two activities can collide: replay may need to modify or remove data that a running query’s MVCC snapshot still depends on. When that happens, PostgreSQL must choose — pause replay and let the query finish, or cancel the query and let replay proceed. max_standby_streaming_delay sets how long it will pause before it cancels.

There are two fundamentally different roots, and they call for different fixes:

Snapshot conflicts (the common case). The primary runs VACUUM (or HOT pruning) and removes dead row versions. That cleanup replays onto the standby, but a long-running query there still holds a snapshot that could have needed those rows. PostgreSQL cannot let the query see a torn view, so once the delay is exceeded it cancels the query with “row versions that must be removed.” This is driven by primary vacuum activity, not by locks.

Lock, buffer-pin, and tablespace conflicts. Replaying an ACCESS EXCLUSIVE lock — from TRUNCATE, DROP, ALTER TABLE, or an aggressive VACUUM truncating empty pages — requires the standby to acquire the same lock, which a running query blocks. Buffer-pin conflicts arise when replay needs a buffer a query has pinned. Tablespace conflicts occur when a tablespace a query used is dropped. These are driven by primary DDL and maintenance, not by ordinary vacuum, and hot_standby_feedback does not prevent them.

The reason this distinction is worth the effort: the primary remedy for snapshot conflicts (hot_standby_feedback) does nothing for lock conflicts, and the primary remedy for lock conflicts (raising the relevant delay) grows lag. Reading pg_stat_database_conflicts tells you which world you are in before you touch a setting. For the underlying lag mechanics these settings trade against, see replication lag and consistency management.

Architecture: Where the Conflict Arises

The diagram shows the replay process and a read query converging on the same rows, and the counter that records the outcome.

Step-by-Step Resolution

Step 1: Confirm the symptom in the logs

Grep both the PostgreSQL log on the replica and your application logs for the error and its SQLSTATE:

# On the replica host
grep -c "conflict with recovery" /var/log/postgresql/postgresql-*.log

# Break down by the DETAIL line to see which conflict class dominates
grep "conflict with recovery" /var/log/postgresql/postgresql-*.log \
  | grep -oE "must be removed|relation lock|shared buffer pin|tablespace" \
  | sort | uniq -c | sort -rn

Inline verification: a non-zero count confirms cancellations are happening; the breakdown tells you whether “must be removed” (snapshot) or a lock/pin message dominates, which pre-selects your remedy.

Step 2: Read pg_stat_database_conflicts

This system view is the authoritative, always-on record of cancellations by cause. Query it on the replica:

-- On the replica: cumulative recovery conflicts by cause
SELECT datname,
       confl_tablespace,  -- tablespace dropped out from under a query
       confl_lock,        -- exclusive lock replay (DDL, TRUNCATE, vacuum truncate)
       confl_snapshot,    -- vacuum removed rows a snapshot still needed (common)
       confl_bufferpin,   -- replay needed a buffer the query had pinned
       confl_deadlock     -- replay vs query deadlock, query chosen as victim
FROM pg_stat_database_conflicts
WHERE datname = current_database();

The columns are cumulative counters since the last stats reset. To see the rate, snapshot the view twice a minute apart, or scrape it into Prometheus and take a rate().

Inline verification: the column with the largest and fastest-growing value is your dominant conflict class. confl_snapshot climbing points at primary vacuum; confl_lock climbing points at primary DDL.

Step 3: Identify the conflict class against primary activity

Correlate the rising counter with what the primary was doing. For snapshot conflicts, check the primary’s vacuum activity and the standby’s own oldest transaction:

-- On the PRIMARY: recent autovacuum on the hot tables
SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE last_autovacuum > now() - interval '15 minutes'
ORDER BY last_autovacuum DESC;

-- On the REPLICA: how long is the oldest running query?
SELECT pid, now() - query_start AS runtime, state, left(query, 60) AS q
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
ORDER BY query_start
LIMIT 5;

Inline verification: if long-running replica queries (runtime greater than max_standby_streaming_delay) coincide with recent autovacuum on the tables they read, the snapshot-conflict diagnosis is confirmed.

Step 4: Choose a remediation lever

Match the lever to the dominant class. Only change one at a time, per replica:

# postgresql.conf on the REPLICA

# Lever A — suppress snapshot conflicts (fixes confl_snapshot).
# Cost: primary retains dead tuples -> bloat if this replica runs long queries.
hot_standby_feedback = on

# Lever B — let long queries finish before replay cancels them.
# Cost: replay pauses -> replication lag grows by up to this amount.
max_standby_streaming_delay = 30s     # OLTP replica; use a high value only on analytics nodes

# Related: applies to WAL replayed from the archive rather than the stream
max_standby_archive_delay = 30s

For an analytics replica whose lag budget is generous, a high or unlimited max_standby_streaming_delay (or -1) plus hot_standby_feedback = on all but eliminates cancellations. For an OLTP replica, keep the delay low and add application retry instead:

# Application-side bounded retry for SQLSTATE 40001
import time, psycopg2

def run_read_with_retry(conn, sql, params=(), attempts=3):
    for i in range(attempts):
        try:
            with conn.cursor() as cur:
                cur.execute(sql, params)
                return cur.fetchall()
        except psycopg2.errors.SerializationFailure:   # SQLSTATE 40001
            conn.rollback()
            if i == attempts - 1:
                raise
            time.sleep(0.05 * (2 ** i))   # exponential backoff

Inline verification: after applying hot_standby_feedback, the confl_snapshot counter should stop rising within a few minutes while pg_stat_activity on the primary shows the standby’s backend_xmin being respected.

Step 5: Apply, reload, and verify

hot_standby_feedback and the delay settings are reloadable — no restart needed:

# On the replica
psql -c "ALTER SYSTEM SET hot_standby_feedback = on;"
psql -c "SELECT pg_reload_conf();"
psql -c "SHOW hot_standby_feedback;"   # -> on

Then watch the conflict counter and the lag together:

-- Conflicts should plateau...
SELECT confl_snapshot, confl_lock FROM pg_stat_database_conflicts
WHERE datname = current_database();

-- ...without the lag budget blowing out
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int AS lag_seconds;

Inline verification: cancellations stop climbing and lag_seconds stays within your SLO. If lag climbs after raising the delay, you have moved the problem rather than fixed it — see the gotchas below.

Configuration Snippet

A pragmatic split: OLTP replica favors low lag with retry, analytics replica favors query survival.

# --- OLTP replica: protect lag, retry cancellations in the app ---
hot_standby_feedback = on          # kill the common snapshot conflicts cheaply
max_standby_streaming_delay = 15s  # cancel truly long queries fast
max_standby_archive_delay = 15s

# --- Analytics replica: protect queries, absorb lag ---
# hot_standby_feedback = on
# max_standby_streaming_delay = 300s   # or -1 for unlimited on a lag-tolerant node

Route long reporting queries to the analytics node so the OLTP node never runs a query long enough to conflict — the routing side of this lives in connection routing and pooling strategies, and pooling the read-only endpoint is covered in configuring PgBouncer for read-only connection pools.

Verification and Rollback

Confirm the fix held, and know how to back it out:

-- Fix is holding: conflicts flat, lag within SLO
SELECT confl_snapshot, confl_lock, confl_bufferpin
FROM pg_stat_database_conflicts WHERE datname = current_database();
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int AS lag_seconds;

Roll back hot_standby_feedback if primary bloat becomes the bigger problem:

psql -c "ALTER SYSTEM RESET hot_standby_feedback;"
psql -c "SELECT pg_reload_conf();"

If bloat has already accumulated on the primary, follow the reset with a targeted VACUUM of the affected tables once the standby’s long queries have drained.

Edge Cases and Gotchas

hot_standby_feedback trades cancellations for primary bloat

Feedback works by having the standby report its oldest snapshot’s xmin to the primary, which then refrains from vacuuming rows newer than it. If the standby runs a genuinely long query — a multi-hour report — the primary cannot vacuum those rows for the whole duration, and dead tuples accumulate as table bloat, degrading primary performance. This is the exact trade-off discussed for cascading topologies in the PgBouncer read-only pool guide. Monitor n_dead_tup on the primary after enabling feedback; if it climbs unbounded, the real fix is to move the long query off that replica, not to keep feedback on.

Raising the delay just converts cancellations into lag

max_standby_streaming_delay does not make conflicts disappear — it makes replay wait. Every second a long query holds off replay is a second of replication lag added to that replica. On an OLTP node this is often worse than the cancellation, because it makes the node stale for every reader to protect one long query. Only raise it where the lag budget explicitly permits, and alert on lag so a runaway query cannot silently push the replica past its freshness SLO.

Lock conflicts survive hot_standby_feedback

Feedback only addresses snapshot conflicts. A confl_lock counter that keeps climbing after you enable feedback means DDL or vacuum-truncate on the primary is replaying ACCESS EXCLUSIVE locks. The remedies there are different: schedule DDL for low-traffic windows, set vacuum_truncate = off on hot tables to avoid the truncate lock, and keep replica queries short enough that they are not holding a conflicting lock when the replay arrives.

FAQ

Related

← Back to Query Performance Analysis on Read Replicas

• Finding Slow Queries on Read Replicas with pg_stat_statements — when a high-variance query turns out to be cancellations rather than slow execution.
• Replication Lag & Consistency Management — the lag budget that max_standby_streaming_delay and hot_standby_feedback trade against.
• Configuring PgBouncer for Read-Only Connection Pools — pooling the read endpoint and the same feedback/bloat trade-off in cascading topologies.
• Monitoring & Observability for Read Replicas — the parent section covering lag, saturation, and query-level replica signals.