Hot Standby Reads, Conflict Cancellation, max_standby Delays, and Feedback

Learning outcomes

Once ServiceHub has a standby, product teams naturally want to run dashboards and exports there. A hot standby can serve read-only queries while WAL replay continues, but it is not an isolated analytics database. Recovery has priority: if replay must apply a primary-side change that conflicts with a standby query, PostgreSQL must delay replay or cancel the query.

1. Hot standby is read-only recovery, not independent MVCC history

SELECT pg_is_in_recovery();SHOW hot_standby;SHOW max_standby_streaming_delay;SHOW max_standby_archive_delay;SHOW hot_standby_feedback;SHOW log_recovery_conflict_waits;

max_standby_streaming_delay limits how long replay may be delayed by conflicts while consuming streaming WAL. It is not a guaranteed runtime allowance for each query. If recovery is already behind, much of that delay budget may already be spent before a newly-started query conflicts.

2. What can conflict?

Common hard conflicts include primary-side ACCESS EXCLUSIVE locks (for example DDL), cleanup of row versions needed by an old standby snapshot, dropped tablespaces, buffer-pin conflicts, and deadlock-like recovery interactions. Unlike normal primary locking, the primary cannot see the standby query and wait for it before generating WAL.

SELECT datname, confl_tablespace, confl_lock, confl_snapshot,       confl_bufferpin, confl_deadlockFROM pg_stat_database_conflictsWHERE datname = 'servicehub_ha_lab';

These are cumulative counters. Record a before/after delta around a controlled experiment rather than assuming a nonzero historical value belongs to the query you are investigating.

3. Controlled snapshot-conflict experiment

Use the disposable pair from Lesson 1. A recovery conflict needs the standby query to hold a snapshot that actually depends on row versions the primary later wants to remove. Therefore, seed the rows on the primary first, wait until the standby can see all of them, and only then start the long standby read. This ordering makes the causal chain observable instead of relying on luck.

INSERT INTO app.ch14_work_orders (work_order_id, status, changed_at)SELECT g, 'conflict_seed', clock_timestamp()FROM generate_series(14100, 14399) AS gON CONFLICT (work_order_id) DO UPDATESET status = EXCLUDED.status,    changed_at = EXCLUDED.changed_at;SELECT pg_current_wal_flush_lsn() AS seed_lsn;

Record the returned seed_lsn. On the standby, wait until replay has reached at least that position, then verify that all 300 rows are visible. A count of 300 proves the standby snapshot can include the exact row versions that the primary will subsequently delete.

SELECT pg_last_wal_replay_lsn() AS replay_lsn;SELECT count(*) AS seeded_rowsFROM app.ch14_work_ordersWHERE work_order_id BETWEEN 14100 AND 14399;

BEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*)FROM app.ch14_work_orders AS wCROSS JOIN LATERAL     pg_sleep(0.03 + (w.work_order_id * 0)) AS sleeperWHERE w.work_order_id BETWEEN 14100 AND 14399;

The correlated LATERAL expression references each row, preventing the sleep from being treated as an unrelated one-time expression. With 300 candidate rows, the query remains active long enough for the primary-side cleanup attempt below. Do not use this pattern outside the disposable lab.

DELETE FROM app.ch14_work_ordersWHERE work_order_id BETWEEN 14100 AND 14399;VACUUM app.ch14_work_orders;

With hot_standby_feedback=off and a sufficiently short conflict delay, replay of the cleanup WAL can require cancellation of the standby statement. PostgreSQL reports recovery-conflict cancellation as SQLSTATE 40001, commonly with canceling statement due to conflict with recovery and detail explaining that row versions needed by the query had to be removed. Exact timing remains workload-sensitive, but the experiment now guarantees the snapshot predates the delete/vacuum work.

\set VERBOSITY verboseROLLBACK;SELECT datname, confl_snapshot, confl_lock, confl_bufferpin,       confl_deadlock, confl_tablespaceFROM pg_stat_database_conflictsWHERE datname = 'servicehub_ha_lab';

A rise in confl_snapshot ties the cancellation to an old-snapshot cleanup conflict. The counter proves that PostgreSQL canceled queries for this conflict class; it does not identify which exact application request was canceled, so production diagnosis should correlate counters with logs and application request IDs.

4. Delay settings trade query completion for replay freshness

Raising max_standby_streaming_delay allows replay to wait longer for conflicting reads, so long queries may complete more often. The cost is that every WAL record behind the blocked record is also delayed. A reporting replica can therefore become stale even while network receive is current.

SELECT pg_last_wal_receive_lsn() AS received,       pg_last_wal_replay_lsn()  AS replayed,       pg_size_pretty(         pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())       ) AS receive_to_replay_gap,       pg_last_xact_replay_timestamp() AS last_replayed_commit;

The receive-to-replay gap separates transport from apply. A large gap with a healthy receiver points you toward replay work, conflicts, I/O, or CPU rather than network connectivity.

5. hot_standby_feedback prevents one conflict class by moving cost upstream

With hot_standby_feedback=on, the standby sends its oldest required snapshot horizon upstream. The primary then avoids vacuuming away row versions still needed by standby queries, reducing cleanup conflicts. The tradeoff is delayed cleanup and potentially significant table/index bloat on the primary.

SELECT application_name, state, backend_xmin,       replay_lsn, replay_lagFROM pg_stat_replication;

SELECT relname, n_live_tup, n_dead_tup,       last_autovacuum, autovacuum_countFROM pg_stat_user_tablesWHERE schemaname='app' AND relname='ch14_work_orders';

backend_xmin is evidence that feedback is pinning an MVCC horizon, not proof that bloat is currently severe. Combine it with dead-tuple trends, relation size, vacuum logs, and workload history.

6. DDL conflicts are different

hot_standby_feedback cannot solve every conflict. If the primary commits DDL requiring an ACCESS EXCLUSIVE effect on replay, a standby query holding incompatible access cannot run forever while replay applies that schema change. This is why a “never cancel analytics” replica may need different architecture—ETL, logical replication, a warehouse, or explicit replay-delay policy—rather than one global knob.

ALTER TABLE app.ch14_work_ordersADD COLUMN IF NOT EXISTS technician_note text;

Use a disposable table and long standby read if you want to demonstrate the lock conflict. Do not inject production DDL solely to “test” cancellation behavior.

7. Replica-read application contract

A production read path should define: maximum acceptable replay staleness, whether a user must read their own recent write, which SQLSTATEs are retryable, how many retries are permitted, where to fall back when the replica is stale, and how long a request may wait overall. Recovery conflict SQLSTATE 40001 is retryable in the sense that repeating later may succeed, but blindly retrying an expensive report forever simply transfers replay conflict into an application retry storm.

SELECT now() - pg_last_xact_replay_timestamp() AS replay_timestamp_age,       pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS received_equals_replayed;

8. Separate query cancellation from connection termination

Most recovery conflicts can cancel the current statement with SQLSTATE 40001, allowing the session to remain usable after the transaction is rolled back as needed. Some conflict classes can escalate to termination of the whole connection when recovery cannot resolve the conflict by canceling one statement—for example, certain cases involving database or tablespace state. Client code should therefore distinguish a retryable database statement from a broken connection that must be re-established.

\set VERBOSITY verbose-- Run the controlled conflict query here.-- psql will include SQLSTATE/LOCATION details in verbose diagnostics.

Do not code a retry policy by matching English error text. Drivers expose SQLSTATE independently of localized messages. Use SQLSTATE plus the operation's idempotency and an overall request deadline.

9. Read scaling needs a freshness contract

A hot standby can be “healthy” from a process perspective and still be too stale for a particular endpoint. ServiceHub might allow a 30-second-old fleet utilization dashboard but require work-order confirmation screens to see the user's just-committed write. Route by data contract, not by a blanket “reads go to replicas” rule.

SELECT CASE         WHEN pg_last_xact_replay_timestamp() IS NULL THEN NULL         ELSE clock_timestamp() - pg_last_xact_replay_timestamp()       END AS replay_age,       pg_last_wal_receive_lsn() AS receive_lsn,       pg_last_wal_replay_lsn()  AS replay_lsn;

Timestamp age can be misleading during an idle primary because there may be no recent transaction to replay. Pair it with LSN positions and application markers when freshness is important. For causal reads, the application can capture a primary LSN after a write and wait/reroute until a chosen standby has replayed at least that location, subject to a bounded deadline.

10. Feedback outages matter

hot_standby_feedback only protects horizons while feedback is actually flowing. If a standby disconnects, the primary can advance cleanup. When the standby later reconnects and replays accumulated WAL, old snapshots can still conflict with cleanup records generated during the disconnected period. This is why a reporting replica that frequently loses its upstream can show conflict bursts immediately after reconnecting even though feedback is enabled now.

11. Production judgment

For an HA standby, keep replay current and tolerate/read-retry cancellations. For an analytics-oriented standby, a larger delay and feedback may be reasonable if you actively monitor replay lag and primary cleanup. For strict read-after-write behavior, use the primary or a synchronous remote_apply strategy with explicit latency budget; physical replication alone does not guarantee that a just-committed row is already visible on an asynchronous standby.

Authoritative references

Replication and HA behavior is topology-, version-, privilege-, and operating-system-sensitive. These primary PostgreSQL sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Hot Standby
• PostgreSQL 18 — Monitoring Statistics
• PostgreSQL 18 — Replication Configuration
• PostgreSQL 18 — Log-Shipping Standbys