Lab 22: Measure replication lag four ways, and stop paging on the wrong one

Objective

“Replication lag” is four different numbers, and the one most teams alert on produces false pages on any database that is ever quiet.

By the end of this lab you will have measured lag from the primary and from the standby, under load and at rest, and reproduced the false alarm deliberately: seventeen seconds of apparent apply lag on a standby whose byte lag was zero and which was fully caught up the entire time.

You will finish with two queries — one for each side — that alert on conditions that are actually wrong.

Architecture

The same pair from Lab 21, measured from both ends.

flowchart LR
    P["primary\npg_current_wal_lsn()"] --> S["sent_lsn"]
    S --> W["write_lsn\nwrite_lag"]
    W --> F["flush_lsn\nflush_lag"]
    F --> R["replay_lsn\nreplay_lag"]
    R --> V["visible to standby queries"]
    P -.->|pg_stat_replication| M1["primary-side view"]
    R -.->|pg_last_wal_replay_lsn| M2["standby-side view"]

Requirements

A primary with a streaming standby, as built in Lab 21.
pgbench on the primary, to generate measurable load.

Scenario

Monitoring pages twice a week for “replication lag above 15 seconds”. Every time, the standby turns out to be fine. The team has started ignoring the alert, which is the worst possible outcome.

Tasks

Task 1 — An idle pair

LAB="$HOME/rbpg-lab-22"
mkdir -p "$LAB"

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT application_name, sent_lsn, write_lsn, flush_lsn, replay_lsn,
         write_lag, flush_lag, replay_lag
  FROM pg_stat_replication WHERE application_name='lab21-standby';" | tee "$LAB/idle.txt"

Read-only / Safefour identical LSNs and three null lags— Captured from the lab run of 2026-08-28 with no write activity.

$ psql -X -c "SELECT application_name, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication WHERE application_name='lab21-standby';"

 application_name |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag 
------------------+------------+------------+------------+------------+-----------+-----------+------------
lab21-standby    | 0/4F46F760 | 0/4F46F760 | 0/4F46F760 | 0/4F46F760 |           |           | 
(1 row)

The four LSNs are checkpoints in a pipeline, and each is at or behind the one before it:

sent_lsn — the primary has put this much on the wire.
write_lsn — the standby has written it to its operating system.
flush_lsn — the standby has flushed it to durable storage.
replay_lsn — the standby has applied it, so its queries see it.

The three *_lag columns are the times the corresponding stage took, measured on the primary by timing a round trip. They are NULL here because nothing has been sent recently — not because there is no lag.

Task 2 — Byte lag

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT application_name,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   AS pending_send,
         pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn))              AS sent_not_flushed,
         pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))            AS flushed_not_replayed,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_behind
  FROM pg_stat_replication WHERE application_name='lab21-standby';" | tee -a "$LAB/idle.txt"

Read-only / Safethe pipeline broken into its stages, in bytes— Captured from the lab run of 2026-08-28.

$ pg_wal_lsn_diff between each pair of positions

 application_name | pending_send | sent_not_flushed | flushed_not_replayed | total_behind 
------------------+--------------+------------------+----------------------+--------------
lab21-standby    | 0 bytes      | 0 bytes          | 0 bytes              | 0 bytes
(1 row)

Byte lag has a property time lag does not: it is meaningful when nothing is happening. An idle primary produces zero byte lag, not a growing number, because it is a difference between two positions rather than a duration since an event.

Splitting it into stages localises a problem. pending_send large means the network or the sender is the constraint. flushed_not_replayed large means the standby is receiving fine and cannot apply fast enough — often because a query on the standby is blocking replay, which Lab 24’s territory covers.

Task 3 — Under load

docker exec -u postgres rbpg-sb pgbench -i -s 20 -q lab21
docker exec -d -u postgres rbpg-sb pgbench -c 8 -j 4 -T 40 -M prepared lab21
sleep 8

for i in 1 2 3; do
  docker exec -u postgres rbpg-sb psql -X -c "
    SELECT write_lag, flush_lag, replay_lag,
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS bytes_behind
    FROM pg_stat_replication WHERE application_name='lab21-standby';"
  sleep 3
done | tee "$LAB/under-load.txt"

Read-only / Safethe three lags in the order the pipeline produces them— Captured from the lab run of 2026-08-28 under 8 concurrent pgbench clients.

$ sample the lag columns three times during a pgbench run

 write_lag       | flush_lag       | replay_lag      | bytes_behind 
-----------------+-----------------+-----------------+--------------
00:00:00.001626 | 00:00:00.004096 | 00:00:00.006205 | 3272 bytes
00:00:00.000849 | 00:00:00.003318 | 00:00:00.005110 | 2440 bytes
00:00:00.000844 | 00:00:00.003318 | 00:00:00.003751 | 2024 bytes

write_lag < flush_lag < replay_lag in every sample, which is the ordering the pipeline guarantees. All three are under seven milliseconds and the standby is a couple of kilobytes behind while the primary is committing several hundred transactions a second.

This is what healthy looks like. Anything you alert on should be far above it.

Task 4 — The false alarm

# Wait for the pgbench run to finish, so the primary goes quiet.
sleep 35

for i in 1 2 3; do
  docker exec -u postgres rbpg-lab21 psql -X -c "
    SELECT now() - pg_last_xact_replay_timestamp() AS apply_lag_time,
           pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS fully_caught_up;"
  sleep 8
done | tee "$LAB/false-alarm.txt"

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS bytes_behind,
         replay_lag
  FROM pg_stat_replication WHERE application_name='lab21-standby';" | tee -a "$LAB/false-alarm.txt"

Service impact possibleseventeen seconds of lag on a standby that is not behind at all— Captured from the lab run of 2026-08-28 after the pgbench run ended.

$ sample apply lag from the standby three times while the primary is idle

 apply_lag_time  | fully_caught_up 
-----------------+-----------------
00:00:00.951159 | t
00:00:09.003664 | t
00:00:17.054691 | t

bytes_behind | replay_lag 
--------------+------------
0 bytes      | 
(1 row)

apply_lag_time climbs steadily: 0.95 seconds, 9 seconds, 17 seconds. It will keep climbing for as long as the primary stays quiet, and after ten minutes of overnight idleness it reads ten minutes.

And throughout, fully_caught_up is true and bytes_behind is zero.

Why now() - pg_last_xact_replay_timestamp() is not lag

pg_last_xact_replay_timestamp() returns the commit timestamp of the last transaction the standby applied. Subtracting it from now() gives “time since the last commit reached this standby”.

On a busy primary, commits arrive constantly, so that quantity closely tracks genuine replication delay — which is why the metric became popular. On a quiet primary, there are no new commits to arrive, so the quantity is measuring how long the primary has been idle. The standby has nothing to do with it.

The metric conflates two different things and only distinguishes them when the primary is busy. It is a reasonable proxy under continuous load and useless outside it.

Note also that it is only available on a standby, and that on a standby which has never replayed a commit it returns NULL.

Task 5 — A delay that is deliberate

Not all lag is a fault. A standby can be told to hold back on purpose.

docker exec -u postgres rbpg-lab21 psql -X -c "ALTER SYSTEM SET recovery_min_apply_delay = '30s';"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-sb psql -X -d lab21 -c \
  "CREATE TABLE delayed_marker(id int, at timestamptz); INSERT INTO delayed_marker VALUES (1, now());"

for i in 1 2 3 4; do
  sleep 9
  echo -n "after $((i*9))s: "
  docker exec -u postgres rbpg-lab21 psql -X -tAd lab21 -c "SELECT count(*) FROM delayed_marker;"
done

Configuration changethe standby withholds the change for its configured delay— Captured from the lab run of 2026-08-28 with recovery_min_apply_delay = 30s.

$ create a table on the primary, then poll the standby every nine seconds

after 9s:  ERROR:  relation "delayed_marker" does not exist
after 18s: ERROR:  relation "delayed_marker" does not exist
after 27s: ERROR:  relation "delayed_marker" does not exist
after 36s: 1

The table appeared between 27 and 36 seconds, consistent with the 30-second delay. And critically:

Read-only / Safebyte lag stayed zero throughout the delay— Captured from the lab run of 2026-08-28, from the primary, during the delay.

$ check bytes_behind on the primary while the standby is deliberately delayed

 bytes_behind | replay_lag 
--------------+------------
0 bytes      | 
(1 row)

A delayed standby is a recovery tool, and it needs its own alerting

recovery_min_apply_delay gives you a standby that is deliberately N behind — typically an hour. Its purpose is human error: a DELETE without a WHERE on the primary has not yet reached it, so the correct rows can be read straight off the delayed standby while the incident is still in progress. It is a faster answer than the PITR in Lab 20 for the first hour, and complements it rather than replacing it.

Two consequences to plan for.

It is not a failover target during its delay window. Promoting it loses everything in the delay.

Your lag alerting must know about it. A standby deliberately an hour behind will trip every lag threshold you own. Exclude it by application_name and alert instead on the delay differing from its configured value.

Note that the delay is measured against the commit timestamp in the WAL, so it depends on the two servers’ clocks agreeing. A delayed standby whose clock drifts applies at the wrong time.

Task 6 — The queries to keep

docker exec -u postgres rbpg-lab21 psql -X -c "ALTER SYSTEM RESET recovery_min_apply_delay;"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT application_name, state,
         pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind,
         replay_lag,
         CASE
           WHEN state <> 'streaming' THEN 'ALERT: not streaming'
           WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 100*1024*1024
             THEN 'ALERT: >100MB behind'
           WHEN replay_lag > interval '1 minute' THEN 'ALERT: >1min replay lag'
           ELSE 'ok'
         END AS verdict
  FROM pg_stat_replication ORDER BY application_name;" | tee "$LAB/monitoring.txt"

Read-only / Safeboth standbys, with a verdict rather than a raw number— Captured from the lab run of 2026-08-28.

$ the primary-side monitoring query

 application_name |   state   | bytes_behind |   replay_lag    | verdict 
------------------+-----------+--------------+-----------------+---------
lab21-standby    | streaming |            0 | 00:00:00.002621 | ok
oldprimary       | streaming |            0 | 00:00:00.001930 | ok
(2 rows)

docker exec -u postgres rbpg-lab21 psql -X -c "
  SELECT pg_is_in_recovery() AS in_recovery,
         pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS caught_up,
         pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS unreplayed_bytes,
         (SELECT count(*) FROM pg_stat_wal_receiver) AS receiver_running;"
docker exec -u postgres rbpg-lab21 psql -X -c "
  SELECT status, sender_host, sender_port, slot_name FROM pg_stat_wal_receiver;" \
  | tee -a "$LAB/monitoring.txt"

Read-only / Safethe standby's own view, which does not need the primary— Captured from the lab run of 2026-08-28.

$ the standby-side monitoring query, then pg_stat_wal_receiver

 in_recovery | caught_up | unreplayed_bytes | receiver_running 
-------------+-----------+------------------+------------------
t           | t         |                0 |                1
(1 row)

status   | sender_host | sender_port | slot_name  
-----------+-------------+-------------+------------
streaming | 172.26.0.3  |        5432 | lab21_slot
(1 row)

pg_stat_wal_receiver is the standby-side counterpart of pg_stat_replication, and it answers the question the primary cannot when the primary is the thing that failed: is this standby still connected, to whom, and using which slot.

Note that it also carries conninfo, which contains the replication password — so a monitoring role granted access to this view is granted a credential. Select the columns you need rather than *.

Validation

test -s "$LAB/idle.txt"        && echo "OK idle"
test -s "$LAB/under-load.txt"  && echo "OK under-load"
test -s "$LAB/false-alarm.txt" && echo "OK false-alarm"
test -s "$LAB/monitoring.txt"  && echo "OK monitoring"

grep -q "0 bytes"        "$LAB/idle.txt"        && echo "OK idle byte lag captured"
grep -q "00:00:00.00"    "$LAB/under-load.txt"  && echo "OK sub-ms lag under load captured"
grep -q "00:00:17"       "$LAB/false-alarm.txt" && echo "OK false alarm reproduced"

Questions to answer without looking anything up:

replay_lag is NULL. Is the standby caught up?
flushed_not_replayed is 4 GB and pending_send is 0. Where is the problem?
now() - pg_last_xact_replay_timestamp() reads eight minutes at 04:00. What is most likely happening?
Which lag measurement still works when the primary is unreachable?
A standby is deliberately an hour behind. How should monitoring treat it?

Expected Outcome

You have measured lag from both ends, in bytes and in time, under load and at rest, and reproduced the false alarm that trains teams to ignore their replication alerts.

The rules that follow:

Alert on a missing standby before you alert on a slow one.
Alert on bytes, not on time, because bytes are meaningful when the primary is idle and time is not.
Use the standby-side query too, because the primary-side one is unavailable exactly when you need it most.
Exclude deliberately delayed standbys by name, and alert on their delay differing from its configured value instead.

Troubleshooting

pg_stat_replication shows null in the lag columns. The *_lag intervals are computed from feedback the standby sends; on a completely idle primary there is nothing recent to compare against and they are legitimately null.

Time lag reports minutes on a healthy pair. This is the false alarm in Task 4. The time-based columns measure how old the last replayed transaction is, so on an idle primary they climb steadily while the standby is perfectly caught up. Alert on bytes, not on time.

pg_stat_replication is empty during an incident. It is a primary-side view. When the primary is unreachable — which is when you most want to know the standby’s position — it tells you nothing. Use pg_stat_wal_receiver and pg_last_wal_replay_lsn() on the standby.

Byte lag is large and the standby is not behind. Check which of the four positions you are subtracting. sent, write, flush and replay answer different questions, and a large sent - replay with a small sent - flush means the data is safe on the standby but not yet visible on it.

recovery_min_apply_delay looks like lag. Because it is, by design. A deliberately delayed standby must be excluded from the lag alert by name and alerted on separately — its delay differing from the configured value is the real signal.

Lag never returns to zero under load. The standby may be apply-bound rather than network-bound. wait_event on the startup process on the standby distinguishes them, and a conflict pause looks the same from the primary.

Cleanup

docker exec -u postgres rbpg-lab21 psql -X -c "ALTER SYSTEM RESET recovery_min_apply_delay;"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-sb psql -X -d lab21 -c "DROP TABLE IF EXISTS delayed_marker;"

Leave the standby running; Lab 23 uses it.

Production notes

Alert on a missing standby before you alert on a slow one. A standby that has disconnected produces no row in pg_stat_replication at all, which a threshold on lag will never fire on.
Alert on bytes. Byte lag is meaningful whether or not the primary is writing; time lag is meaningless on an idle primary and is the reason teams learn to ignore replication alerts.
Put the standby-side query in monitoring too. The primary-side view is unavailable in exactly the failure you care about most.
Exclude deliberately delayed standbys by name, and alert instead on their delay diverging from its configured value.
Pick the position that matches the promise you are making. An RPO is about flush; a read-replica staleness budget is about replay.

What You Learned

Lag has four meanings — sent, written, flushed, replayed — and they answer different questions.
Time-based lag produces a false alarm on an idle primary, reliably, every quiet weekend.
Byte lag is the one to alert on, because it is meaningful whether or not the primary is writing.
pg_stat_replication is primary-side and empty when the primary is the thing that failed; the standby has its own views.
recovery_min_apply_delay is lag on purpose, and must be excluded from the alert by name.
A missing standby is the alert that matters most, and it is the one a lag threshold cannot express.

Objectives

Prerequisites