Replication lag: write, flush and replay

Replication lag is how far a standby is behind its primary, and PostgreSQL reports it as three separate distances because a record passes three milestones on the standby. Write means the record reached the standby’s operating system. Flush means it reached durable storage there, which is what a synchronous commit waits for. Replay means it was applied and is visible to queries on that standby. Each can be measured as a byte distance in the log stream or as a time interval, and the two answer different questions.

Three numbers, and the one your alert probably uses

Replay is the number a read replica’s users feel, and it is the last of the three, so it is never smaller than the other two. Flush is the number that decides whether a failover loses committed transactions. Write is mostly a network statement. An alert on the wrong one produces either false alarms during a replay stall that costs nobody anything, or silence during the flush gap that would have lost data.

The byte and time forms are not two views of one quantity. The byte distance is the amount of log the standby has yet to consume, and it is the right unit for capacity, because that is what the primary must retain. The time interval, which the server derives by timing how long a recent record took to reach each milestone, is the right unit for a user-facing promise. On an idle primary they diverge sharply: with nothing being written the byte distance is zero and the time interval reports the last measured sample, so a quiet system can look stale when it is perfectly current.

Then there is the case both forms handle badly. If the standby is not connected at all, it has no row here, and a query that averages or takes the maximum over the rows present returns a healthy number for a replica that is infinitely behind. Absence is the most severe reading this view has, and it is the one shaped like no reading at all.

Asking for all three at once

The three byte distances and the three intervals sit side by side on the primary.

SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)  AS write_behind_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)  AS flush_behind_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_behind_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

 application_name | state | write_behind_bytes | flush_behind_bytes | replay_behind_bytes | write_lag | flush_lag | replay_lag 
------------------+-------+--------------------+--------------------+---------------------+-----------+-----------+------------
(0 rows)

No rows, because the server it ran on has no standby streaming from it. That empty result is the case described above, reproduced deliberately: the query is correct, the server is healthy, and any rule that reads this as low lag is wrong. Alert on the row count first and on the distances second.

On a standby the same question is asked differently, through pg_last_wal_replay_lsn() and pg_last_xact_replay_timestamp(), and those are the functions to use when the primary is the thing you cannot reach.

Turning three numbers into one rule

Which distance to alert on, what threshold survives a nightly bulk load, and how lag interacts with the log the primary is keeping for a slot are all one problem, and the guide on replication lag and slot health is where it is worked through. If the reason lag is growing is that the standby stopped fetching rather than stopped applying, the next thing to check is whether its slot was invalidated.