Replication Lag

Write a million rows to the primary and watch the replica report a count of 1. Then freeze the replica mid-stream to see that "durable on the replica" and "visible on the replica" are two different numbers — and make a COMMIT hang forever.

Concept

A streaming replica is a second Postgres that replays the primary's write-ahead log as it arrives. It is never quite the same database as the primary — there is always some amount of WAL in flight between them, and during that window the replica will happily answer a query with an older answer. This exercise makes that window visible and measurable: you'll write a million rows to the primary and watch the replica report a count of 1, watch the byte distance between the two nodes climb into the tens of megabytes and drain back to zero, freeze the replica mid-stream so the lag stops being a blur, and then flip replication to synchronous and watch a COMMIT on the primary block until the replica says it's caught up — and then hang forever when the replica dies.

Provenance

PostgreSQL 16 documentation, Chapter 27, "High Availability, Load Balancing, and Replication".

§27.2.8, "Synchronous Replication" — the claim Part 4 tests. On the default setting:

This configuration will cause each commit to wait for confirmation that the standby has written the commit record to durable storage.

And on the setting that changes the answer:

Setting synchronous_commit to remote_apply will cause each commit to wait until the current synchronous standbys report that they have replayed the transaction, making it visible to user queries. In simple cases, this allows for load balancing with causal consistency.

Those two sentences are the whole exercise — durable and visible are different waits. The same section states the failure mode Part 4 ends on:

Such transaction commits may never be completed if any one of the synchronous standbys should crash.

§27.4, "Hot Standby" — the claim Part 1 tests: "All such connections are strictly read-only; not even temporary tables may be written." And the query-conflict behaviour in "Go deeper": "a mechanism is provided to forcibly cancel standby queries that conflict with to-be-applied WAL records."

§28.2, the pg_stat_replication view — the definitions Part 3 leans on. replay_lag is "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it." Note what that does not say: it is not the age of the data on the standby. The docs also warn:

If the standby server has entirely caught up with the sending server and there is no more WAL activity, the most recently measured lag times will continue to be displayed for a short time and then show NULL.

Prerequisites

Two ideas carry most of the result. Neither is explained by the Postgres replication chapter itself, and both are the reason the observations surprise people.

1. MVCC visibility rules — the one that matters most. Part 2's whole point is that a count on the replica jumps from 4000001 to 7000001 in one step, and the reason is not replication at all: a row version is visible only once its creating transaction is known to have committed, and the commit is a single WAL record at the end of three million insert records.
2. WAL and LSNs as a byte stream. Every number in Parts 2 and 3 is a subtraction of two byte offsets into a single append-only log. If "0/46D266D0 minus 0/44F113C8 is 30 MB" doesn't read as obvious arithmetic yet, start there.

One smaller thing, used only in Part 3: the standby reports its position on a timer, wal_receiver_status_interval (default 10 s). That single default explains the shape of the replay_lag staircase in step 11.

Mental model: four LSNs, four different promises

An LSN (Log Sequence Number) is a byte offset into the WAL stream, written as 0/46D266D0. Every change to the database appends to that stream, so "how far behind is the replica" is literally a subtraction of two byte offsets — which is what pg_wal_lsn_diff() does.

The important thing is that a WAL record makes four separate journeys, and pg_stat_replication on the primary has a column for each. They are not the same event and they do not carry the same guarantee:

Alongside those, write_lag / flush_lag / replay_lag report the same three distances as time rather than bytes — how long it took the most recent locally-flushed WAL to reach each stage. Useful, but Part 3 shows a case where replay_lag badly understates how stale the replica really is.

The replica has its own view, from the other side: pg_last_wal_replay_lsn() (same number as the primary's replay_lsn) and pg_last_xact_replay_timestamp() (the commit time of the last transaction it replayed — subtract it from now() for an honest staleness figure).

Where the commit is allowed to return

By default, replication is asynchronous: the primary commits, returns to the client, and the WAL makes its way over whenever it makes its way over. Setting synchronous_standby_names makes at least one standby synchronous, and then synchronous_commit decides which of the four journeys above a COMMIT waits for:

Setup

Two Postgres containers on a private Docker network, so they can find each other by name. This is the one part of the exercise worth scripting — get the cluster up, then type everything after it by hand.

docker network create pgnet

1. The primary.

docker run -d --name pg-primary --network pgnet \
  -e POSTGRES_PASSWORD=postgres postgres:16

Wait a few seconds for it to initialize. The defaults are already replication-ready — wal_level = replica and max_wal_senders = 10 — so there is nothing to tune. Note there is no -p: nothing in this exercise connects over a published port, every psql goes through docker exec.

2. A replication role, and permission to use it.

docker exec -i pg-primary psql -U postgres \
  -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replpass';"

docker exec -i pg-primary bash -c \
  "echo 'host replication replicator all scram-sha-256' >> /var/lib/postgresql/data/pg_hba.conf"

docker exec -i pg-primary psql -U postgres -c "SELECT pg_reload_conf();"

3. Take a base backup into a named volume.

docker volume create pg-replica-data

docker run --rm --network pgnet \
  -e PGPASSWORD=replpass -v pg-replica-data:/pgdata \
  --entrypoint bash postgres:16 -c \
  "install -d -o postgres -g postgres -m 0700 /pgdata/pgdata && \
   gosu postgres pg_basebackup -h pg-primary -U replicator -D /pgdata/pgdata -Fp -Xs -R -P"

pg_basebackup is a physical, byte-for-byte copy of the primary's data directory. -Xs streams WAL concurrently with the copy so the backup is self-consistent, and -R is the flag that matters here: it writes standby.signal and a primary_conninfo line into the new data directory, which is what turns a copy into a standby.

4. Start the standby.

docker run -d --name pg-replica --network pgnet \
  -v pg-replica-data:/var/lib/postgresql/data \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  postgres:16

The official entrypoint sees an already-initialized PGDATA and skips initdb, so it just starts Postgres — which finds standby.signal and comes up in recovery. Using a named volume (rather than baking the backup into the container's own command) matters for Part 4, where you stop and start this container and it needs to survive that.

Check the log with docker logs pg-replica:

That last line is the one you want. Now open two terminals:

Terminal P — primary

docker exec -it pg-primary psql -U postgres

Terminal R — replica

docker exec -it pg-replica psql -U postgres

Part 1 — what a replica is

What this part establishes: a standby is a whole running Postgres that happens to be permanently in recovery. That single fact explains both what it can do (serve reads) and what it can't (accept any write at all).

SELECT pg_is_in_recovery();

SELECT application_name, client_addr, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag, sync_state
FROM pg_stat_replication;

CREATE TABLE events (id bigserial PRIMARY KEY, payload text,
                     created_at timestamptz DEFAULT clock_timestamp());
INSERT INTO events (payload) VALUES ('hello from the primary');

SELECT id, payload FROM events;

INSERT INTO events (payload) VALUES ('hello from the replica');

SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

Part 2 — the burst

What this part tests: how big the in-flight window really is. Per the mental model, sent_lsn - replay_lsn is a byte distance, so a large enough write should make it large enough to see.

INSERT INTO events (payload) SELECT 'burst-'||g FROM generate_series(1,1000000) g;

SELECT count(*) FROM events;

SELECT pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind
FROM pg_stat_replication;

SELECT clock_timestamp()::time(3) AS t, sent_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind,
       replay_lag
FROM pg_stat_replication \watch 0.5

INSERT INTO events (payload) SELECT 'burst2-'||g FROM generate_series(1,3000000) g;

SELECT clock_timestamp()::time(3) AS t, count(*) FROM events \watch 0.3

INSERT INTO events (payload) SELECT 'atomic-'||g FROM generate_series(1,3000000) g;

Part 3 — freeze the replica

What this part tests: the distinction between flush_lsn and replay_lsn from the mental model — durable versus visible. Both drain to zero instantly on a healthy local link, so the only way to look at them properly is to stop one of them. pg_wal_replay_pause() does exactly that: the walreceiver keeps receiving and flushing, but the startup process stops applying.

SELECT count(*) FROM events;
SELECT pg_wal_replay_pause();
SELECT pg_get_wal_replay_pause_state();

INSERT INTO events (payload) SELECT 'paused-'||g FROM generate_series(1,200000) g;

SELECT sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn))  AS flush_behind,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_behind
FROM pg_stat_replication;

SELECT count(*) FROM events;
SELECT pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS staleness;

SELECT clock_timestamp()::time(0) AS t, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_behind
FROM pg_stat_replication \watch i=5 c=10

SELECT pg_wal_replay_resume();
SELECT count(*) FROM events;

Part 4 — make it synchronous

What this part tests: the synchronous_commit table from the mental model. So far every commit on the primary has returned without the replica being consulted at all — that's what sync_state | async meant in step 2. Now make the primary wait, and find out precisely what it waits for.

ALTER SYSTEM SET synchronous_standby_names = '*';
SELECT pg_reload_conf();
SELECT application_name, sync_state, sync_priority FROM pg_stat_replication;

SELECT pg_wal_replay_pause();

INSERT INTO events (payload) VALUES ('sync-A');

SET synchronous_commit = remote_apply;
INSERT INTO events (payload) VALUES ('sync-B');

docker stop pg-replica

INSERT INTO events (payload) VALUES ('during-outage');

SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query
FROM pg_stat_activity WHERE state = 'active' AND wait_event = 'SyncRep';

ALTER SYSTEM SET synchronous_standby_names = '';
SELECT pg_reload_conf();

docker start pg-replica

SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind
FROM pg_stat_replication;

What you should see

A replica that answers SELECT pg_is_in_recovery() with t and refuses every write with cannot execute INSERT in a read-only transaction. A million-row burst on the primary that leaves the replica reporting a count of 1 while pg_stat_replication shows it 16 MB behind. A \watch on the primary showing sent_lsn and replay_lsn sawtoothing up to 18 MB apart and then locking to 0 bytes the moment writes stop. A count on the replica that sits at 4000001 for thirty consecutive samples over eight and a half seconds and then jumps to 7000001 in a single step, never passing through anything in between. With replay paused: flush_behind | 0 bytes next to replay_behind | 30 MB, a replica twenty seconds stale, and a primary reporting replay_lag | 00:00:00.244351 — which then climbs in a 10-second staircase to 00:00:57 while the replica's own staleness reads 00:01:15. And under synchronous replication, an INSERT that returns in 86 ms under synchronous_commit = on, blocks forever under remote_apply, commits anyway when you Ctrl-C it, and stalls the entire primary on wait_event = SyncRep when the standby dies.

Why

Physical streaming replication is one linear byte stream and nothing more. The primary appends WAL; the standby reads it, writes it, flushes it, and applies it. Every property in this exercise falls out of that one design.

The replica is read-only because its position in that stream is a single pointer. A local write would create WAL that does not exist on the primary, and the next record streamed in would have nowhere consistent to land. It's not a policy, it's the absence of any coherent alternative — which is why even a superuser can't override it.

The count jumps rather than climbs because visibility on the replica is decided by exactly the same MVCC rules as on the primary: a row version is visible once its creating transaction is known to have committed. Replay processes the three million insert records first and the single commit record last, so three million rows become visible in one instant. That's what makes a replica usable — it always shows a consistent past state of the primary, never a torn one — and it's also why a long transaction on the primary pins the replica's apparent staleness to that transaction's whole duration, regardless of bandwidth.

The four LSNs are four separate promises because there are genuinely four distinct moments, and different applications need different ones. Pausing replay just holds two of them still long enough to see they were never the same number. Once you accept that flush and replay are different events, synchronous_commit = on blocking on one and remote_apply blocking on the other stops being trivia and becomes the actual decision: do you need this write to be safe, or do you need it to be visible over there? The default answers "safe", and a read-your-writes bug on a read replica is what it costs — reproduced as a user-facing failure, on this same substrate, in ddia/ch06/read-your-writes.

The replay_lag staircase has the same root cause. Lag-as-time can only be computed from information the standby volunteers, and it volunteers on a timer. Lag-as-bytes is computed from sent_lsn, which the primary knows without asking anyone — which is precisely why it never went stale during the stall while the time-based figure did. When you want a monitoring signal that degrades gracefully, prefer the one whose inputs are local. And note where the effect vanishes: drop wal_receiver_status_interval to 1s and the staircase becomes fine-grained enough to look like a clock — at the cost of ten times the status traffic, which is exactly the tradeoff the default is making on your behalf.

And the hung primary in step 16 is the honest price of synchronous replication. synchronous_commit = on is a promise that no committed transaction can be lost if the primary dies — which is only keepable if the primary refuses to commit when it has nobody to tell. There is no timeout setting because a timeout would silently break the promise at the worst possible moment. The only real fix is more standbys, so that "nobody to tell" stops being one machine away.

Go deeper

Everything below was run against this same cluster; the quoted output is real, but the steps are left for you to reproduce. Run them in this order — promotion forks the timeline and ends the cluster's usefulness.

• A delayed standby. Append recovery_min_apply_delay = '1min' to the replica's postgresql.auto.conf and SELECT pg_reload_conf();. It now streams and flushes in real time but deliberately replays a minute late — a running undo button for DELETE FROM users; with no WHERE. Insert 100,000 rows on the primary and look:
  flush_behind | 0 bytes replay_behind | 12 MB
  with the replica reporting count = 0 for the new rows and a staleness of 00:00:52. That is Part 3's flush/replay split made permanent and on purpose. Delete the line and reload, and the backlog applies in seconds.
• Query conflicts. On the replica set max_standby_streaming_delay = 2s (the default is 30 s and you don't want to wait), then open a BEGIN ISOLATION LEVEL REPEATABLE READ; and hold a snapshot with a SELECT count(*) and a pg_sleep(20). Meanwhile on the primary, DELETE a few million rows and VACUUM. The replica must choose between applying WAL that removes row versions your open snapshot still needs and stalling replay — and by default replay wins:
  ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.
  Set hot_standby_feedback = on, reload, and repeat: the pg_sleep(20) now returns normally and the conflict is gone — at the cost of the replica's snapshots holding back vacuum on the primary, which connects this exercise directly to Part 4 of MVCC and vacuum: a long read on your replica becomes bloat on your primary.
• Replication slots. Step 18 worked only because the primary still had the needed WAL segments. docker stop pg-replica, then churn WAL on the primary until they're recycled — fourteen rounds of pg_switch_wal(), CHECKPOINT, and a 200k-row insert does it (the CHECKPOINT is the part that matters; segments are recycled at checkpoint, not on switch). Then docker start pg-replica and read its log:
  LOG: started streaming WAL from primary at 0/6F000000 on timeline 1 FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000000000006F has already been removed LOG: waiting for WAL to become available at 0/6F002000
  Note what doesn't happen: the replica doesn't crash. It stays up, pg_is_in_recovery() still returns t, it keeps serving reads at its frozen position — mine sat at 2300004 rows while the primary had 5100004 — and it retries every five seconds forever. A silently, permanently stale replica is a nastier failure than a dead one. The fix is a slot: SELECT pg_create_physical_replication_slot('replica1'); on the primary and primary_slot_name = 'replica1' on the replica. The primary then retains WAL for that slot no matter how long the standby is gone — which is the fix and the new failure mode, since a forgotten slot fills the primary's disk. Watch pg_replication_slots.wal_status go reserved → extended → lost.
• Promotion. SELECT pg_promote(); on the replica. pg_is_in_recovery() flips to f, INSERT starts working, and the log says:
  LOG: received promote request LOG: selected new timeline ID: 2 LOG: database system is ready to accept connections
  To see the fork, ask for the current WAL file rather than the control file — SELECT pg_walfile_name(pg_current_wal_lsn()); returns 00000002000000000000006F on the promoted node and 00000001000000000000008F on the old primary, and the first eight hex digits are the timeline. SELECT timeline_id FROM pg_control_checkpoint(); is the tempting query and it will mislead you: immediately after promotion it still reads 1 on both, because it reports the timeline of the last completed checkpoint. Run CHECKPOINT; on the promoted node and it flips to 2. Either way the two nodes have forked history, and the old primary cannot stream from the new one without pg_rewind. This is what a failover actually is — and why "just point the app at the replica" is never the whole story.
• remote_write vs on. Repeat step 15 with SET synchronous_commit = remote_write; while replay is paused. It returns immediately, like on — both are satisfied by the receive path. Then compare their latency under a tight loop of small commits to see what the replica's fsync actually costs you per transaction.
• The two anomalies this lag actually causes. Everything above measures the window; two Ch. 6 exercises put a user inside it. ddia/ch06/read-your-writes writes to the primary and reads the replica in the same breath, and the user's own comment comes back missing — the failure mode the flush/replay split in Part 3 is the mechanism for. ddia/ch06/monotonic-reads adds a second replica at different lag and gets the stranger one: a refresh that moves backward in time, 1 then 0. Note the two fixes are on opposite sides of the wire — those exercises fix it in the client (route reads-after-writes to the leader; pin a user to one replica), Part 4 here fixes it in the server (synchronous_commit = remote_apply, the only setting that waits for visible rather than durable). Predict which one you'd actually ship before you read either.

Sources: PostgreSQL 16 §27.2, Log-Shipping Standby Servers for synchronous_standby_names, synchronous_commit and the ANY N quorum syntax · §28.2, pg_stat_replication for the exact definition of the three lag columns · §27.4, Hot Standby for read-only standbys, query conflicts and hot_standby_feedback

Cleanup

docker rm -f pg-primary pg-replica
docker volume rm pg-replica-data
docker network rm pgnet