Measuring lag properly: sent, written, flushed, replayed · PostgreSQL

“Replication lag” is four different questions with four different answers, and using the wrong one produces monitoring that is confidently wrong.

The four LSNs

Column	Meaning	Answers
`sent_lsn`	The walsender put it on the socket	Is the primary keeping up?
`write_lsn`	The standby handed it to the OS	Is the network keeping up?
`flush_lsn`	The standby fsynced it	Would it survive a standby crash?
`replay_lsn`	The startup process applied it	Is it visible to queries?

Each is a superset of the last, so sent >= write >= flush >= replay always holds.

The two that matter operationally are flush_lsn, which is the durability point and what synchronous replication waits for, and replay_lsn, which is the visibility point and what a read replica serves from.

Byte lag and time lag

Read-only / Safeboth units, side by side— EXECUTION VALIDATED on 18.6 during pgbench at 1,623 tps, 8 clients.

$ SELECT application_name, write_lag, flush_lag, replay_lag,
     pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;

 22:10:50  write_lag=00:00:00.000898  flush_lag=00:00:00.002532  replay_lag=00:00:00.004165  bytes_behind=43240
22:10:54  write_lag=00:00:00.000857  flush_lag=00:00:00.002503  replay_lag=00:00:00.002503  bytes_behind=9776
22:10:59  write_lag=00:00:00.000823  flush_lag=00:00:00.002483  replay_lag=00:00:00.002483  bytes_behind=26376

write < flush < replay every time, as it must.

Byte lag answers “how much data is at risk?” It is honest about volume and useless for judging staleness: 43 kB behind is nothing on a busy cluster and could be minutes of work on a quiet one.

Time lag answers “how stale is this replica?” It is what an application cares about. It is computed from timestamps in the WAL stream, so it is meaningful across clusters of different write rates.

Monitor both. Alert on time.

On an idle cluster the lag columns are NULL, and NULL is healthy

Read-only / Safea perfectly healthy idle standby— EXECUTION VALIDATED on 18.6, no write activity.

$ SELECT application_name, state, write_lag, flush_lag, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS behind FROM pg_stat_replication;

 application_name |   state   | write_lag | flush_lag | replay_lag | behind
------------------+-----------+-----------+-----------+------------+--------
walreceiver      | streaming |           |           |            |      0

NULL means “nothing outstanding to measure”, not “unknown” and not “broken”. The standby has confirmed everything the primary has produced, so there is no interval to report.

Two monitoring bugs follow from misreading this:

-- BUG 1: fires on a healthy idle cluster
SELECT * FROM pg_stat_replication WHERE replay_lag IS NULL;

-- BUG 2: goes silent when the standby disappears entirely.
-- Zero rows means max() returns NULL, which is not > any threshold.
SELECT max(replay_lag) > interval '30 seconds' FROM pg_stat_replication;

The second is the more dangerous, because the check reports healthy at exactly the moment the standby is gone.

A lag check that works

-- Run on the PRIMARY. Returns one row per expected standby, plus a
-- count so that a missing standby is detectable rather than silent.
SELECT
  (SELECT count(*) FROM pg_stat_replication)          AS connected,
  application_name,
  state,
  coalesce(replay_lag, interval '0')                  AS replay_lag,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)   AS bytes_behind,
  now() - reply_time                                  AS since_last_report
FROM pg_stat_replication;

Three alerting rules, and the first is the one that matters most:

connected below the expected number. A standby that is gone produces no row at all.
replay_lag above a threshold you chose from your RPO.
since_last_report above roughly 2 × wal_receiver_status_interval — a standby that has stopped reporting but has not yet hit wal_sender_timeout shows stale LSNs that read as lag rather than absence.

From the standby’s side

The standby cannot see the primary’s current position, so it cannot compute true lag. What it can report:

-- how long since this standby applied anything from the WAL stream
SELECT
  pg_is_in_recovery()                            AS in_recovery,
  pg_last_wal_receive_lsn()                      AS received,
  pg_last_wal_replay_lsn()                       AS replayed,
  pg_last_xact_replay_timestamp()                AS last_replayed_txn,
  now() - pg_last_xact_replay_timestamp()        AS apparent_delay;

pg_last_xact_replay_timestamp() is the commit timestamp of the last transaction replayed. now() minus that is an apparent delay, and it has one important failure mode: on a primary with no write traffic it grows without bound while the standby is perfectly current. It measures “time since the last replayed commit”, which on an idle system is time since the last commit anywhere.

Lag that is stopped is not lag that is slow

Read-only / Safereplay stopped by a conflicting query on the standby— EXECUTION VALIDATED on 18.6. max_standby_streaming_delay = 300s, one long read query held open on the standby while pgbench ran on the primary.

$ -- sampled on the primary every 6 seconds

   t+6s   replay_lag=00:00:05.999552  behind=43 MB
 t+12s  replay_lag=00:00:12.052013  behind=74 MB
 t+18s  replay_lag=00:00:18.104707  behind=96 MB
 t+24s  replay_lag=00:00:24.159844  behind=112 MB
 t+30s  replay_lag=00:00:30.004856  behind=124 MB

-- after the standby query ended:
        replay_lag=00:00:00.001752  behind=0 bytes

replay_lag tracked wall-clock time to within five milliseconds over thirty seconds. That signature — lag equal to elapsed time — means replay is stopped, not slow, and it is worth recognising on sight.

Note also what happened next. The standby cleared 124 MB of backlog in under five seconds once unblocked. Replay was never the bottleneck; a lock was. Lesson XIV-07 is about which lock and why.

How the lag intervals are computed, and why they can look strange

The primary does not measure the standby’s clock, which would require the clocks to agree. Instead, each WAL position the primary sends is remembered together with the local time it was sent. When a standby status update reports that position as written, flushed or replayed, the primary subtracts the remembered send time from the current local time.

Every lag interval is therefore measured entirely on the primary’s clock, and clock skew between the hosts does not affect it at all. This is worth knowing because the equivalent computation from the standby side — now() - pg_last_xact_replay_timestamp() — does compare two clocks, and is skewed by any difference between them.

Three consequences of the mechanism.

Lag is only updated when a status update arrives. With wal_receiver_status_interval at its default of 10 seconds, a lag value can be that stale. During an incident, small movements are noise.

On an idle primary the values are NULL, because there is no outstanding position whose acknowledgement is being awaited. This is the mechanism behind the NULL discussed above, and it is why NULL means healthy rather than unknown.

The three intervals are measured against the same send timestamps, which is why they are directly comparable and why their ordering is guaranteed. flush_lag - write_lag is the standby’s fsync cost. replay_lag - flush_lag is the startup process’s apply cost. In the measurement above, flush cost about 1.6 ms and apply about 1.7 ms on top of a 0.9 ms network write — which is a useful decomposition when deciding whether a slow standby is slow at storage or slow at replay.

What to take from this

Four LSNs: sent, write, flush, replay. flush is durability; replay is visibility.
Byte lag for volume at risk; time lag for staleness. Alert on time.
NULL lag on an idle cluster is healthy. Do not alert on it.
Zero rows is the alert. max() over an empty set returns NULL and fires nothing.
pg_last_xact_replay_timestamp() grows on an idle primary even when the standby is current.
replay_lag tracking wall-clock exactly means replay is stopped, not slow.

Cross-course references

Observability for Production Sysadmins — Part XIII (Rates and counters) covers why a byte lag and a time lag answer different questions, and Part XX (Alert quality) covers why the time-based one fires every quiet weekend.
Linux for Production Sysadmins — Part XLII (Network Performance) covers proving the link rather than blaming it.