1. Measure from the primary
SELECT application_name, client_addr, state, sync_state,
write_lag, flush_lag, replay_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap
FROM pg_stat_replication;
2. Measure from the standby
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay,
pg_is_in_recovery(),
pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
Note
On a quiet primary,
replay_delaykeeps growing even with no real lag, because there's nothing new to replay. Compare LSNs, or write a heartbeat row every few seconds.
3. Which lag is high?
| High value | Bottleneck |
|---|---|
write_lag | Network between primary and standby, or the standby is slow to receive |
flush_lag but not write_lag | Standby disk can't fsync fast enough |
replay_lag only | Replay is slow: single-threaded replay, IO, or replay paused by conflicts with queries |
No row in pg_stat_replication | The standby isn't connected at all. Check its log. |
4. Standby not connected
Standby log: could not connect to the primary server → network, pg_hba.conf replication entry, password.
requested WAL segment ... has already been removed → the standby fell too far behind and the primary recycled WAL. It must be rebuilt (pg_basebackup), unless you have a WAL archive (restore_command). Prevent this with a replication slot or wal_keep_size.
5. Query conflicts on hot standbys
ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
The standby must choose: delay replay (lag grows) or cancel the query. Settings:
| Setting | Effect |
|---|---|
max_standby_streaming_delay (default 30s) | How long replay waits for queries before canceling them |
hot_standby_feedback = on | The primary avoids removing rows the standby still needs. Fewer cancels, but can cause bloat on the primary |
Reporting replicas: a larger delay is usually acceptable. HA replicas: keep it small so failover stays fast.
SELECT * FROM pg_stat_database_conflicts WHERE datname = 'appdb';
6. Heavy write bursts
Big batch jobs, CREATE INDEX or VACUUM FULL generate a lot of WAL at once. Lag spikes and then recovers. Schedule them off-peak and check replicas are on the same instance size and storage class as the primary.
Tip
Alert on replay lag in bytes and seconds, and on a missing replication row. A disconnected standby looks like "no lag" if you only graph seconds.