PostgreSQL "canceling statement due to conflict with recovery" — Why Replicas Cancel Queries?

Why replica queries get cancelled due to recovery conflicts, diagnosis via pg_stat_database_conflicts, and three mitigation strategies.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: PostgreSQL 14.10, 15.6, 16.4, 17.0 (Ubuntu 24.04 LTS / Docker 26.1)

Operational Verification & Reliability Metadata

Last Technical Review: 27 Ekim 2026

Tested VersionsPostgreSQL 14.10, 15.6, 16.4, 17.0 (Ubuntu 24.04 LTS, Docker 26.1, Kernel 6.8)

Required PrivilegesSUPERUSER or pg_read_all_stats / pg_signal_backend

Risk Assessment

MediumTerminating active sessions forces application-layer transaction rollbacks

Expected OutputTotal connections drop safely below max_connections threshold in pg_stat_activity

Rollback Procedure:Reset temporary session timeouts, reload PgBouncer or revert pool sizing

PostgreSQL Docs: Connections

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptoms

plaintext

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

This error is encountered exclusively on standby replicas (hot standby). While a long-running SELECT query executes, incoming WAL records from the primary attempt to purge dead tuple versions read by that query. The replica must either delay WAL replay (inducing replication lag) or cancel the query. Default behavior cancels the query after waiting 30 seconds.

▸Root Cause Diagnostic Query

Execute on the replica:

sql

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

•confl_snapshot climbing → Tuple version conflict.
•confl_lock climbing → Exclusive table lock (ALTER TABLE / TRUNCATE) executed on primary.
•confl_bufferpin climbing → Long-held buffer pin contention.

▸Ordered Remediation Steps

1Enforce Statement Timeouts: Set predictable bounds (SET statement_timeout = '120s';).
2Increase Standby Delay: Increase max_standby_streaming_delay = '300s' trading replication freshness for query stability.
3Enable `hot_standby_feedback` cautiously: Prevents vacuum on primary for active standby transactions, but carries severe table bloat risks if analytical queries are unbounded.
4Isolate Workloads: Separate transactional read-replicas from heavy batch analytical reporting nodes.

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (20)

POSTGRESQL5 min