skip to content

On a PostgreSQL primary, how do you turn pg_stat_replication's LSN columns into byte lag per stage, and what do write_lag, flush_lag and replay_lag measure instead?

level: middleimportance: should knowfreq 52%

answer

  1. subtract positions, get bytes
  2. sent, written, flushed, replayed
  3. pg_wal_lsn_diff
  4. intervals go NULL when idle

basics

~20 s

Subtract the standby's sent_lsn, write_lsn, flush_lsn and replay_lsn from pg_current_wal_lsn() or from each other with pg_wal_lsn_diff() to get bytes behind per stage; the *_lag intervals time how long recent WAL took to be written, flushed and replayed.

solid answer

~40 s

`pg_stat_replication` has one row per directly connected standby, with four `pg_lsn` positions: `sent_lsn` on the primary side and `write_lsn`, `flush_lsn`, `replay_lsn` as reported back by the standby. `pg_wal_lsn_diff(a, b)` (or `a - b`) turns two of them into a `numeric` byte count, so `pg_current_wal_lsn() - sent_lsn` is WAL not yet sent and `flush_lsn - replay_lsn` is WAL safely on the standby but not yet applied. `write_lag`, `flush_lag` and `replay_lag` are different: each is the time between the primary flushing recent WAL and learning the standby had written, flushed or replayed it, which is the delay the matching `synchronous_commit` level would add. On an async standby, `replay_lag` approximates how stale its reads are. They are not catch-up forecasts, and after the standby catches up on an idle system they turn `NULL`.

code

sql · 7 lines
sql
-- on the newsroom primary
SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS not_sent,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn))             AS not_flushed,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))           AS not_replayed,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

go deeper

for a junior

Recall that pg_stat_replication runs on the primary, has a row per connected standby, and carries sent, write, flush and replay positions plus three lag intervals.

for a middle

Explain turning LSN pairs into bytes with pg_wal_lsn_diff, which stage each difference isolates, and why the interval columns are timings of recent WAL rather than byte counts.

for a senior

Demonstrate diagnosis: replay backlog with an idle network points to the startup process, conflicts or paused replay; alert on missing rows, and do not trust replay-timestamp age on an idle primary.

for a principal

Decide which number drives which action: bytes for WAL retention and disk risk, replay_lag for routing reads, and who owns the alert when a standby simply vanishes from the view.

## Where the numbers come from On any server that sends WAL — the primary, or a cascading standby — `pg_stat_replication` has **one row per WAL sender**, which means one row per directly connected standby. The standby's WAL receiver reports three positions back: what it has **written**, what it has **flushed** to disk, and what its startup process has **replayed**. It reports whenever the write or flush position moves, and at least every `wal_receiver_status_interval` (10 s by default), so the reported replay position can trail the truth slightly. The positions are `pg_lsn` values — **log sequence numbers**, byte positions in the WAL stream. ## Byte lag per stage Subtracting two `pg_lsn` values — with `pg_wal_lsn_diff(a, b)` or the `-` operator — gives a `numeric` number of **bytes**, and `pg_size_pretty()` makes it readable. Each pair isolates one stage: | Difference | Bytes that are… | Usual suspects when it grows | |---|---|---| | `pg_current_wal_lsn() - sent_lsn` | generated on the primary, not yet sent | WAL sender starved, network, standby reconnecting | | `sent_lsn - write_lsn` | sent, not yet written by the standby | network in flight, standby I/O | | `write_lsn - flush_lsn` | written, not yet fsynced on the standby | slow standby storage | | `flush_lsn - replay_lsn` | durable on the standby, not yet applied | replay itself: one startup process, a recovery-conflict wait, paused replay | The last row surprises people: the network can be idle while `replay_lsn` falls gigabytes behind, because **replay is done by a single startup process**, which also waits — up to `max_standby_streaming_delay` — for standby queries that conflict with the WAL it must apply. ## The three interval columns `write_lag`, `flush_lag` and `replay_lag` are not derived from those byte counts. Each is the **time** between the primary flushing a piece of WAL locally and hearing that the standby has written it, written and flushed it, or written, flushed and replayed it. - They equal the commit delay that the `remote_write`, `on` and `remote_apply` levels of `synchronous_commit` would add if this standby were synchronous. - On an asynchronous standby, `replay_lag` **approximates how long recent commits took to become visible** to queries there. - They describe **recent** WAL, not a forecast: they do not say how long the standby needs to catch up. - When the standby has fully caught up and the primary goes idle, they keep the last measured value briefly and then turn **`NULL`**. A `NULL` interval with a zero byte difference means caught up, not broken. ## Reading it from the standby When only the standby is reachable: 1. `pg_last_wal_receive_lsn()` — the last WAL received and synced to disk by streaming. 2. `pg_last_wal_replay_lsn()` — the last WAL replayed; the byte gap between the two is the replay backlog. 3. `now() - pg_last_xact_replay_timestamp()` — the age of the last replayed commit, stamped with the time it was generated on the primary. It **keeps growing while the primary is idle**, because nothing new arrives to replay, so it overstates lag on a quiet system. 4. `pg_stat_wal_receiver` — `status`, `sender_host`, `latest_end_lsn` and `last_msg_receipt_time` for the connection itself. `pg_current_wal_lsn()` cannot be used there: on a standby it fails with `recovery is in progress`. ## A worked reading Suppose the newsroom's reporting standby shows this on the primary during the morning traffic peak: | Column or expression | Value | |---|---| | `state` | `streaming` | | not sent (`pg_current_wal_lsn() - sent_lsn`) | 0 bytes | | not flushed (`sent_lsn - flush_lsn`) | 48 kB | | not replayed (`flush_lsn - replay_lsn`) | 3 GB | | `replay_lag` | `00:04:10` | The network and the standby's disk are keeping up: almost nothing is in flight. Three gigabytes are durable on the standby but not applied, and readers there see data about four minutes old. The suspect is replay — most often a long report holding it back through a recovery conflict — not the link. ## Monitoring rules that follow - Alert on **bytes** for capacity (how much WAL is waiting) and on **`replay_lag`** for read freshness. - Alert when a standby's row is **missing**: a disconnected standby simply disappears from `pg_stat_replication`, so a lag query over the rows that exist reports nothing wrong. - Run the query on **every sending server** in a cascade; the primary sees only its direct standbys. - If only `flush_lsn - replay_lsn` is growing, look at replay — `pg_get_wal_replay_pause_state()`, recovery-conflict waits, long queries on the standby — before looking at the network.

  • Why does replay_lag in PostgreSQL's pg_stat_replication show NULL while the standby is still connected?
    When the standby has replayed everything and the primary generates no new WAL, the lag columns show the last measurement for a short time and then revert to `NULL`, by design, so idle systems do not show a stale number. Read it together with the byte difference: `NULL` plus zero bytes means caught up. A disconnected standby has no row at all.
  • The newsroom's reporting standby shows flush_lsn current but replay_lsn far behind; what do you check in PostgreSQL?
    The bytes have arrived, so the problem is replay, not the network. Check whether replay is paused (`pg_get_wal_replay_pause_state()`), whether it is waiting on a long standby query because of a recovery conflict (turn on `log_recovery_conflict_waits` to log such waits), and whether a `recovery_min_apply_delay` is configured on purpose.

  • replay_lag predicts how long the standby needs to catch up.
  • A NULL replay_lag means the standby has disconnected.
  • Subtracting two pg_lsn values gives a number of WAL segments.
  • pg_current_wal_lsn() on a standby returns the standby's own position.
  • The primary's pg_stat_replication lists every standby in a cascade.
  • If the network is idle, replay cannot be behind.