The full picture
- Separate the three lags before theorizing: send lag (leader → replica bytes in flight), write lag (durably received), apply lag (visible to queries). Only apply lag affects readers, and its causes are entirely different from the other two — this triage step is what turns a vague 'replication is slow' into a fixable problem.
- The classic replica-only stall: a long analytical query holds a snapshot, replay conflicts with it, and Postgres either delays replay (
max_standby_streaming_delay) or cancels the query. Teams see 'lag spikes at 9am' and never connect it to the daily report someone runs. Check for read-side conflicts before touching replication settings. - Write amplification is usually the real culprit: every index, trigger and foreign key adds to the WAL the replica must replay, and an update that touches an indexed column rewrites every one of those index entries. Going from one index to eight can multiply replay IO several-fold (not exactly 8×, since heap writes count too and Postgres's HOT updates skip index maintenance when no indexed column changed). Reducing write cost on the leader fixes the replica for free.
- Lag is not a single number — it's a per-replica, per-time distribution. Alert on p99 lag and on lag trend (is it recovering?), because a replica that hits 8s and returns is very different from one that grows monotonically toward disk-full on the WAL.
- The architectural fix when lag is inherent (cross-region, huge write volume): stop pretending replicas are interchangeable with the leader. Route by operation: lag-tolerant reads (search, listings, analytics) to replicas; read-your-writes and any read that feeds a write decision to the leader. Make it explicit in the data layer rather than a per-query judgement call.
-- Postgres, on the primary: where is each replica actually stuck?
SELECT client_addr, state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes
FROM pg_stat_replication;
-- send ≈ 0 but replay large -> apply is the bottleneck (CPU/IO/query conflicts)
-- send large -> network or leader is saturated
-- On the replica: is replay being blocked by a reader?
SELECT * FROM pg_stat_database_conflicts;