Replication Lag Dashboards Mask a Stalled PostgreSQL Standby

Fictional Simulation

This is a fictional Daily Triage exercise. The organisation, systems, logs and query output below are invented for training purposes only. They do not describe a real incident, a real customer environment or live telemetry.

Scenario

Aldergate Retail Group runs a PostgreSQL 15 primary (orders_db) with a single streaming standby dedicated to read-only reporting queries. At 09:12 on a Monday, the reporting team raises a ticket: order totals on the reporting dashboard appear roughly twenty minutes stale compared with the operational system. The platform team checks their Grafana panel, which is driven from pg_stat_replication, and sees write_lag and flush_lag both under two seconds, labelled "Replication: Healthy". The two teams now have directly conflicting signals: one says the standby is current, the other says it is materially behind.

Evidence

The on-call engineer gathers the following fictional evidence before acting.

• On the primary, pg_stat_replication shows sent_lsn, write_lsn and flush_lsn all closely aligned, with write_lag and flush_lag under two seconds for the standby's connection.
• On the standby, now() - pg_last_xact_replay_timestamp() returns an interval of roughly 21 minutes, contradicting the dashboard's healthy status.
• On the standby, pg_stat_activity shows a single connection in state active running an ad-hoc reporting query for 24 minutes, submitted by the analytics team shortly before the ticket was raised.
• The standby's postgresql.conf shows max_standby_streaming_delay = -1 (unlimited) and hot_standby_feedback = off.
• No network monitoring, firewall or routing alerts fired between the primary and standby during the window in question.

Hypotheses and Actions

Four plausible explanations fit parts of the evidence. Each is tested against the collected data before any action is taken.

1. Network partition or packet loss. Checked against WAL shipping metrics; sent_lsn and write_lsn are closely aligned and no network alerts fired, so this is not supported.
2. WAL apply stalled by a long-running standby query. Supported: a 24-minute active query on the standby coincides with the replay gap, and max_standby_streaming_delay = -1 means PostgreSQL will wait indefinitely rather than cancel that query to let replay continue.
3. Monitoring blind spot. Supported as a contributing factor: the Grafana panel is wired only to write_lag/flush_lag, which describe WAL shipping over the network, not WAL replay on the standby, so a replay stall is invisible on that panel.
4. Primary-side WAL storm from autovacuum. Checked against pg_stat_replication send rates and autovacuum logs; no unusual WAL volume or autovacuum activity is recorded on the primary, so this is not supported.

Reveal

The stale reads are caused by the long-running analytics query on the standby. With max_standby_streaming_delay set to unlimited, PostgreSQL will never cancel a conflicting query to let WAL replay proceed; instead replay simply waits behind it. Because the dashboard only graphs write_lag and flush_lag — both of which describe how promptly WAL is shipped over the network, not how promptly it is applied on the standby — the growing replay lag never surfaced until users noticed stale data directly. The apparently conflicting evidence is explained once write lag and replay lag are recognised as two different measurements of two different pipeline stages.

Learning Outcome

The exercise is designed to build the habit of checking replay lag directly, rather than trusting a single dashboard panel, whenever standby data freshness is disputed.

• Treat pg_stat_replication write/flush lag and pg_last_xact_replay_timestamp()-derived replay lag as separate signals; a healthy value in one does not confirm the other.
• Terminating the blocking query and watching replay lag fall is a bounded, reversible first action; it does not remove data and can be repeated safely if the query recurs.
• Escalate to a database administrator, and treat failover as out of scope for this exercise, if replay lag has not started closing within roughly ten minutes of the blocking query ending — that pattern suggests a different or additional cause.
• Confirm recovery only once now() - pg_last_xact_replay_timestamp() has returned to a small, stable value consistent with normal network lag, and record the resolution time for the incident record.