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 to 72 MB 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.

Mental model: four LSNs, four different promises

An LSN (Log Sequence Number) is a byte offset into the WAL stream, written as 0/46BEED00. 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 is actively misleading.

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.

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 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

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 63 MB behind. A \watch on the primary showing sent_lsn and replay_lsn sawtoothing 16 MB apart and then locking to 0 bytes the moment writes stop. A count on the replica that sits at 4000001 for six 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 17 seconds stale, and a primary still cheerfully reporting replay_lag | 00:00:00.102855. And under synchronous replication, an INSERT that returns in 80 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.

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.

• 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 — a dozen 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:
  FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000061 has already been removed LOG: waiting for WAL to become available at 0/61002000
  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, 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.
• 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 and the conflict disappears — at the cost of the replica's snapshots now 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.
• A delayed standby. Append recovery_min_apply_delay = '1min' to the replica's postgresql.auto.conf and reload. 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 | 14 MB
  with the replica reporting count = 0 for the new rows and a staleness of 00:04:01. That is Part 3's flush/replay split made permanent and on purpose. Delete the line and reload, and the backlog applies in seconds.
• 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
  SELECT timeline_id FROM pg_control_checkpoint(); now returns 2 on the replica and 1 on the old primary. They 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.

Sources: PostgreSQL Docs: Log-Shipping Standby Servers for synchronous_standby_names and the ANY N quorum syntax · pg_stat_replication for the exact definition of the three lag columns · Hot Standby for query conflicts and hot_standby_feedback

Cleanup

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