DB Internals AtlasPostgreSQL 18.6 Home About

Replication

Hot standby and recovery conflicts

A hot standby answers read-only queries while it replays WAL. When replay wants to remove rows a running query still needs, PostgreSQL must either pause replay or cancel the query.

In plain words

A hot standby is a replica you can query. It is busy replaying the primary's changes, and your report reads the same tables at the same time. Usually they get along.

The trouble starts when the primary cleans up. VACUUM on the primary removes old row versions, and that removal arrives at the standby in the WAL. If a long query on the standby still needs those old rows, the standby has two bad choices: pause replay (the replica falls behind) or cancel the query.

max_standby_streaming_delay decides how long replay may wait before cancelling (30 seconds by default). hot_standby_feedback avoids the conflict by telling the primary which rows the standby still needs, at the price of extra bloat on the primary. Try the options below.

For DBAs and operators

The error users see

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

Other DETAIL variants name the conflict type: User was holding a relation lock for too long. (an ACCESS EXCLUSIVE lock replayed from the primary, e.g. by TRUNCATE, DROP, or VACUUM truncating the end of a table), User was holding shared buffer pin for too long., plus tablespace, database and logical-slot conflicts. Sessions idle in a transaction are terminated rather than cancelled.

How long replay waits

max_standby_streaming_delay (WAL from streaming) and max_standby_archive_delay (WAL from restore_command) are a total budget measured from when the WAL was received, not per query. Once replay is that far behind, conflicting queries are cancelled immediately. -1 waits forever (replica can lag without bound); 0 cancels at once. With log_recovery_conflict_waits = on the startup process logs waits longer than deadlock_timeout:

LOG: recovery still waiting after 1000.172 ms: recovery conflict on snapshot

hot_standby_feedback

The walreceiver reports the standby's oldest snapshot xmin to the primary (every wal_receiver_status_interval). Primary VACUUM then treats those rows as still needed, so snapshot conflicts disappear. The costs: long standby queries cause bloat on the primary and hold back freezing, exactly like a long transaction on the primary. Without a slot the feedback is lost while disconnected; with a physical slot it is kept in pg_replication_slots.xmin, even when the standby is gone. It does not prevent lock or buffer-pin conflicts.

Monitoring

-- on the standby: cancels by type since stats reset
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin,
       confl_deadlock, confl_tablespace, confl_active_logicalslot
  FROM pg_stat_database_conflicts;
-- on the primary: who holds back the horizon
SELECT application_name, backend_xmin FROM pg_stat_replication;

Practical choices

  • Reporting replica, lag acceptable: raise the delays (or -1) and accept lag. Keep that replica out of synchronous replication with remote_apply.
  • HA replica that must stay current: keep delays low and run long reports elsewhere.
  • Need both: hot_standby_feedback = on plus monitoring of primary bloat and backend_xmin age.

Lock conflicts are common after VACUUM truncates empty pages at the end of a table on the primary. ALTER TABLE … SET (vacuum_truncate = off) (or the vacuum_truncate server setting new in PostgreSQL 18) avoids that exclusive lock.

For developers: inside the source

Where it lives

  • standby.c: the startup process's side of conflicts (waiting, timeouts, cancelling) and replay of standby WAL records (standby_redo(), StandbyAcquireAccessExclusiveLock()).
  • procarray.c: snapshots during recovery. Running-xacts records (LogStandbySnapshot() on the primary) are replayed by ProcArrayApplyRecoveryInfo(), which fills KnownAssignedXids; GetSnapshotData() builds standby snapshots from it (takenDuringRecovery). Read-only connections are allowed once standbyState reaches STANDBY_SNAPSHOT_READY and the standby is consistent.
  • postgres.c: the receiving backend.

A snapshot conflict, step by step

heap_xlog_prune_freeze()                      access/heap/heapam_xlog.c
  ResolveRecoveryConflictWithSnapshot(horizon, …)   storage/ipc/standby.c
    GetConflictingVirtualXIDs(horizon, dbOid)       storage/ipc/procarray.c
    ResolveRecoveryConflictWithVirtualXIDs()
      wait until GetStandbyLimitTime()  (receipt time + max_standby_*_delay)
      LogRecoveryConflict()  after deadlock_timeout, if log_recovery_conflict_waits
      CancelVirtualTransaction(vxid, PROCSIG_RECOVERY_CONFLICT_SNAPSHOT)
backend: HandleRecoveryConflictInterrupt() → ProcessRecoveryConflictInterrupts()
  ProcessRecoveryConflictInterrupt() → ERROR or FATAL

GetStandbyLimitTime() uses GetXLogReceiptTime(), which is why the delay is a budget measured from WAL receipt, not from the start of the wait. While it waits, the startup process shows wait event RecoveryConflictSnapshot. In ProcessRecoveryConflictInterrupt() for snapshot, lock and tablespace conflicts a backend no longer in a transaction ignores the signal; a top-level transaction running a statement gets ERROR, but one idle in a transaction (DoingCommandRead) or inside a subtransaction gets FATAL, as do database conflicts; errdetail_recovery_conflict() produces the DETAIL lines, and pgstat_report_recovery_conflict() feeds pg_stat_database_conflicts. The reasons are the PROCSIG_RECOVERY_CONFLICT_* values in procsignal.h (database, tablespace, lock, snapshot, logicalslot, bufferpin, startup deadlock).

Locks and buffer pins

ResolveRecoveryConflictWithLock() handles replayed AccessExclusiveLocks; ResolveRecoveryConflictWithBufferPin() is called from LockBufferForCleanup() in bufmgr.c when replay needs a cleanup lock. Both arm the timeouts STANDBY_TIMEOUT, STANDBY_DEADLOCK_TIMEOUT or STANDBY_LOCK_TIMEOUT and sleep. Background reading: "Locking during Hot Standby" in storage/lmgr/README.

hot_standby_feedback

The walreceiver's XLogWalRcvSendHSFeedback() gets the standby horizons from GetReplicationHorizons(). On the primary, ProcessStandbyHSFeedbackMessage() in walsender.c stores them either in the walsender's own PGPROC->xmin or, with a slot, through PhysicalReplicationSlotNewXmin() in the slot's xmin/catalog_xmin.

Experiment

  • src/test/recovery/t/031_recovery_conflict.pl provokes each conflict type and checks pg_stat_database_conflicts; 035_standby_logical_decoding.pl covers logical-slot conflicts.
  • Docs source: doc/src/sgml/high-availability.sgml, section hot-standby.

Parameters that shape this

hot_standby max_standby_streaming_delay max_standby_archive_delay hot_standby_feedback log_recovery_conflict_waits deadlock_timeout wal_receiver_status_interval recovery_min_apply_delay

Related topics

Streaming replication Startup process WAL receiver VACUUM MVCC: row versions Snapshots and visibility Replication slots Locking and deadlocks