Replica conflicts and hot_standby_feedback

A standby replays WAL and serves read queries from the same pages. When replay needs to change a page a running query is reading, something has to give.

Why the conflict exists

Lesson XIV-01 established that replay is the crash recovery process, applying records strictly in order. It cannot skip a record, cannot reorder them, and cannot apply half of one.

Meanwhile a query on the standby holds a snapshot, which requires that the row versions its snapshot can see continue to exist.

The primary knows nothing about that query. It vacuums on its own schedule, removes row versions that are dead by its own reckoning, and ships the WAL that says so. Replay must apply it.

Two irreconcilable requirements, and PostgreSQL resolves them by cancelling the query.

With feedback off

$ SELECT name, setting FROM pg_settings WHERE name IN ('max_standby_streaming_delay','hot_standby_feedback');

            name             | setting
-----------------------------+---------
hot_standby_feedback        | off
max_standby_streaming_delay | 1000

A query on the standby opens a snapshot and holds it. On the primary, rows that snapshot can see are deleted and vacuumed:

$ DELETE FROM conflict_demo WHERE id % 2 = 0;
VACUUM (VERBOSE) conflict_demo;

DELETE 100000
tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable

$ -- the session that had been holding the snapshot

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

max_standby_streaming_delay bounded the wait at one second, and the query lost. That setting is how long replay will wait before cancelling, not a query timeout — the clock is on replay, not on the query.

With feedback on

hot_standby_feedback = on makes the standby report its oldest xmin to the primary, which then treats it as it would a local transaction’s horizon.

$ -- on the standby, after the sleep

   after_sleep
-------------
      200000

$ DELETE FROM conflict_demo WHERE id % 3 = 0;
VACUUM (VERBOSE) conflict_demo;

DELETE 66667
tuples: 0 removed, 200000 remain, 66667 are dead but not yet removable
removable cutoff: 229577, which was 1 XIDs old when operation ended

Zero removed. A read query on a different machine held the primary’s vacuum horizon exactly as a long local transaction would — everything in lesson VII-05 applies, including the table that went from 1.7 MB to 19 MB and never came back.

$ VACUUM (VERBOSE) conflict_demo;

tuples: 66667 removed, 133333 remain, 0 are dead but not yet removable

All 66,667 removed on the next pass. The bloat was deferred, not avoided — which is the honest way to describe what feedback buys you.

With a large delay instead

The third option is to keep feedback off and let replay wait.

$ -- sampled on the primary every 6 seconds

   t+6s   replay_lag=00:00:05.999552  behind=43 MB
 t+12s  replay_lag=00:00:12.052013  behind=74 MB
 t+18s  replay_lag=00:00:18.104707  behind=96 MB
 t+24s  replay_lag=00:00:24.159844  behind=112 MB
 t+30s  replay_lag=00:00:30.004856  behind=124 MB

-- after the standby query ended:
        replay_lag=00:00:00.001752  behind=0 bytes

The query survived. The standby served data 30 seconds stale, and would have gone on falling behind for as long as the query ran.

What to take from this

• Replay cannot yield, and a snapshot cannot lose its row versions. Something must give.
• max_standby_streaming_delay is how long replay waits, not a query timeout.
• Feedback on: the query survived, and the primary’s vacuum removed 0 of 66,667 dead tuples until it ended. Bloat deferred, not avoided.
• With a slot, the held horizon is on pg_replication_slots.xmin, not pg_stat_replication.backend_xmin. Measured NULL five times running.
• Large delay instead: 124 MB and 30 seconds behind, from one query.
• Feedback does not prevent lock conflicts. DDL on the primary still cancels standby queries.
• Failover target and reporting replica want opposite settings. Use two.

Cross-course references

• Observability for Production Sysadmins — Part LIX (Database observability) covers exporting conflict counters per standby, which is how a reporting replica’s cancellations become visible to the team running the primary.
• Linux for Production Sysadmins — Part LXXXII (Root cause analysis) covers tracing a cancelled query on one host to a setting changed on another.

Quiz