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 multiplies the work the replica must replay. A table with eight indexes replays roughly eight times the IO of the same table with one. 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;