PostgreSQL Replication Lag: How to Measure It, Find the Cause and Fix It

PostgreSQL replication lag is easy to notice and surprisingly easy to measure wrong. A report run on the standby shows numbers from ten minutes ago, a user saves something and doesn't see it on the next page, or the monitoring graph says "lag: 0" while the standby is in fact an hour behind. Lag matters for two reasons: stale reads today, and lost transactions on the day you fail over to an asynchronous standby. Here is how to measure it properly and how to find out which part of the pipeline is slow.

Lag is three different delays

Streaming replication moves WAL from the primary to the standby in steps. The primary sends it, the standby writes it, flushes it to disk, and finally replays it so the changes become visible to queries. A delay can build up at any of these steps, and each one has a different cause:

• Not sent yet: the primary or the network can't keep up.
• Sent but not flushed: the standby's disk is too slow to write incoming WAL.
• Flushed but not replayed: the data is safe on the standby, but applying it is slow or paused. This is the most common kind, and the one users notice, because queries on the standby see only replayed data.

Measuring on the primary

pg_stat_replication has one row per connected standby. Comparing its LSN columns with pg_wal_lsn_diff splits the lag into those three parts, in bytes:

SELECT application_name, client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS not_sent_bytes,
       pg_wal_lsn_diff(sent_lsn, flush_lsn)              AS not_flushed_bytes,
       pg_wal_lsn_diff(flush_lsn, replay_lsn)            AS not_replayed_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

The write_lag, flush_lag and replay_lag columns give the same picture as time: how long it took for recently flushed WAL to be written, flushed and replayed on the standby, and for the primary to hear about it. Two details from the documentation are worth knowing before you put these on a dashboard:

• When the standby has fully caught up, the columns don't drop to zero. They show the time the most recent WAL took, and then turn NULL after a short while on an idle system. Your monitoring has to decide whether NULL means "no data" or "no lag".
• They are not a forecast of how long the standby needs to catch up.

If a standby is missing from pg_stat_replication altogether, it isn't lagging, it is disconnected. That deserves its own alert.

Measuring on the standby

On the standby, recovery information functions show how much WAL has arrived and how much has been applied:

SELECT pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn()  AS replayed,
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(),
                       pg_last_wal_replay_lsn()) AS replay_backlog_bytes,
       now() - pg_last_xact_replay_timestamp()   AS since_last_replayed_commit;

The popular now() - pg_last_xact_replay_timestamp() check has a trap. The timestamp belongs to the last replayed commit, as it was recorded on the primary. If nobody writes to the primary for ten minutes, this check shows ten minutes of "lag" on a standby that is completely up to date. It also depends on the clocks of both servers being in sync. Combine it with the byte backlog: a large time value with zero bytes to replay is just a quiet primary.

Finding the cause

Once you know which step is slow, the usual suspects are short lists.

Not sent: network or primary. Large write bursts, such as bulk loads, index builds or a big UPDATE, can generate WAL faster than the link to a remote standby can carry it. Check bandwidth between data centres or availability zones, and whether the burst was a one-off or a nightly job that could be spread out.

Not flushed: standby disk. Standbys are often built on cheaper, smaller storage than the primary because they "only replicate". They write exactly the same WAL volume. If the standby's disk has lower IOPS or throughput, it falls behind every time the primary is busy.

Not replayed: the apply side. This has several common causes:

1. Replay is a single process. The primary writes WAL from many sessions in parallel, but the standby applies it with one startup process. A write-heavy workload can be more than one process can replay, especially on a standby with slower disks or CPU.
2. Queries on the standby are holding replay back. When replaying WAL would remove row versions that a running standby query still needs, PostgreSQL waits for that query, up to max_standby_streaming_delay (30 seconds by default), and then cancels it. While it waits, replay stops and lag grows.
3. A deliberate delay. recovery_min_apply_delay keeps a standby behind on purpose, as protection against accidental deletes. That standby should never be a failover target or a read replica, and it should be excluded from lag alerts.
4. Replay is paused. Someone ran pg_wal_replay_pause() and forgot about it. pg_is_wal_replay_paused() tells you.

"canceling statement due to conflict with recovery"

This error on a standby is the other side of point 2 above. The documentation calls the most common reason "early cleanup": VACUUM on the primary removes old row versions that no transaction on the primary needs, but a long query on the standby still does. The standby can either wait, and fall behind, or cancel the query. You can see which kind of conflicts you get per database:

SELECT datname, confl_snapshot, confl_lock,
       confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts;

The options are a trade-off, and there is no setting that wins on all fronts:

• hot_standby_feedback = on tells the primary which rows standby queries still need, so VACUUM keeps them. Cancellations from cleanup go away. The price is that long standby queries now delay cleanup on the primary, which can cause bloat there. If that sounds familiar, our autovacuum and bloat guide explains why it matters.
• A higher max_standby_streaming_delay lets standby queries run longer before being cancelled, at the cost of more replay lag. Fine for a reporting replica, bad for a standby you plan to fail over to.
• Separate standbys for separate jobs. One standby with feedback and long delays for analytics, another with defaults that stays close to the primary for HA. This is often the cleanest answer.

When lag becomes an outage

Lag is not only about stale reads. With asynchronous replication, everything the standby hasn't received is lost if the primary dies and you promote the standby. And if the standby uses a replication slot, the primary keeps all WAL the standby hasn't confirmed. A standby that is far behind, or gone, can fill the primary's disk; we cover that case in PostgreSQL disk full: why pg_wal keeps growing. Setting max_slot_wal_keep_size limits how much WAL a slot can hold back.

A failover tool such as Patroni can refuse to promote a standby that is more than a configured amount of WAL behind. That protects your data, but it also means a lagging standby may not be available as a failover target exactly when you need it.

What to alert on

1. Standby missing from pg_stat_replication on the primary.
2. Byte lag per step, with thresholds that match your write volume.
3. Heartbeat age on each standby, which is time lag that isn't fooled by a quiet primary.
4. Retained WAL per replication slot, so a dead standby doesn't fill the disk.
5. Growth in pg_stat_database_conflicts on read replicas.