# Postgres: replication lag ## 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. The headline surprise, if you only take one thing: the replica does not gradually count up to a million. It sits at the old number and then jumps. ## 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. §27.2.8 also 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 that *"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: - **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. If you don't already have this, read [PostgreSQL: Transactions and MVCC](https://www.postgresql.org/docs/16/mvcc-intro.html), or the exercise next door, [MVCC and vacuum](mvcc-and-vacuum.md). - **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, skim [§30.6 WAL Internals](https://www.postgresql.org/docs/16/wal-internals.html), or run [WAL and crash recovery](wal-and-crash-recovery.md) first. 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 21. ## Environment - **Host:** macOS 26.5.2 (build 25F84), Apple Silicon (arm64) - **Docker:** Docker Desktop, Engine 29.6.2 — Linux VM kernel `6.12.76-linuxkit`, 16 GiB allocated to the VM - **Postgres image:** `postgres:16` — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest `sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20` - **Captured:** 2026-07-26 > **On the numbers.** LSNs, byte distances and wall-clock timings are > genuinely run-variable — they depend on how fast your host writes and how > busy it is. Every figure below is real output from the run described here, > but treat the *magnitudes and the shapes* as the claim, not the digits. On > a lightly loaded host the burst in step 12 peaked around 18 MB behind; on a > loaded one the same burst peaked at 144 MB. The claim that survives both is > "it climbs into the tens of MB and drains to exactly zero". ## 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: | Column | Reached when… | What is actually guaranteed at this point | | --- | --- | --- | | `sent_lsn` | the primary's walsender has pushed the bytes onto the network | Nothing about the replica at all. This is the primary's opinion. | | `write_lsn` | the replica's walreceiver has `write()`n them to the OS | Survives the replica's `postgres` process crashing. Does **not** survive the replica's machine losing power — the bytes are in the OS page cache. | | `flush_lsn` | the replica has `fsync()`ed them to disk | Survives the replica's machine dying. The data is durable on two nodes. **But it is not yet queryable on the replica** — this is the distinction Part 3 is built around. | | `replay_lsn` | the replica's startup process has *applied* the records | The change is now visible to `SELECT`s on the replica. | 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: | `synchronous_commit` | Commit returns once… | Reach for it when… | | --- | --- | --- | | `off` | the WAL is in the primary's own buffer — not even flushed locally | You can tolerate losing the last ~0.2s of committed transactions on a primary crash. Bulk loads, analytics staging, click-tracking. Note this risks data loss with *no* replica involved. | | `local` | the primary has fsynced locally. Standbys ignored. | Per-transaction escape hatch: you have sync replication on globally, but this one bulk job shouldn't pay for it. `SET synchronous_commit = local;` inside that session. | | `remote_write` | the standby's `write_lsn` has passed the commit | You want to survive the *standby's* process dying, but not a full standby power loss, and you don't want to pay for its fsync. An unusual middle ground. | | `on` *(default)* | the standby's `flush_lsn` has passed the commit | The normal choice when you want zero data loss on primary failure. The transaction is durable on two machines before the client hears "ok". | | `remote_apply` | the standby's `replay_lsn` has passed the commit | You are load-balancing reads onto the replica and cannot tolerate read-your-writes violations. The most expensive setting — you pay the replay time on every commit. | The gap between `on` and `remote_apply` is the whole point: with `on`, your committed transaction is *durable* on the replica but not yet *visible* there. Part 4 demonstrates exactly that, by making one setting block and the other not, with nothing else changed. ## 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. ```bash docker network create pgnet ``` **1. The primary.** ```bash 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.** ```bash 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();" ``` The `pg_hba.conf` line is required and easy to forget: `replication` is a *pseudo-database* in `pg_hba.conf`, and the stock file only allows it from `localhost`. A rule granting `all` databases does not cover it. **3. Take a base backup into a named volume.** ```bash 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" ``` ``` waiting for checkpoint 23182/23182 kB (100%), 0/1 tablespace 23182/23182 kB (100%), 1/1 tablespace ``` `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. The `install -d … -m 0700` dance is not incidental. A fresh Docker volume is owned by root and mode 0755; Postgres refuses to start on a data directory it doesn't own with exactly `0700` (or `0750`). Creating the directory with the right owner and mode up front avoids a `data directory has invalid permissions` failure at step 4. **4. Start the standby.** ```bash 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: ```bash docker logs pg-replica ``` ``` LOG: entering standby mode LOG: consistent recovery state reached at 0/2000100 LOG: database system is ready to accept read-only connections LOG: started streaming WAL from primary at 0/3000000 on timeline 1 ``` That last line is the one you want. Now open **two terminals**: Terminal **P** (the primary): ```bash docker exec -it pg-primary psql -U postgres ``` Terminal **R** (the replica): ```bash docker exec -it pg-replica psql -U postgres ``` Keep both open for the whole exercise. That matters more than it looks: several steps depend on firing a query the instant another one returns, and a fresh `docker exec` costs a few hundred milliseconds of container startup — enough for the replica to catch up and hide the effect you came to see. ## Steps ### 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). 1. **R**: `SELECT pg_is_in_recovery();` ``` pg_is_in_recovery ------------------- t ``` **P**: the same query returns `f`. This boolean is the cheapest way to ask a connection "am I talking to the primary or a replica?", and it's what most connection poolers use to route writes. 2. **P**: look at the link from the primary's side. ```sql 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; ``` ``` -[ RECORD 1 ]----+------------ application_name | walreceiver client_addr | 172.19.0.3 state | streaming sent_lsn | 0/3000060 write_lsn | 0/3000060 flush_lsn | 0/3000060 replay_lsn | 0/3000060 write_lag | flush_lag | replay_lag | sync_state | async ``` (`\x` for expanded display makes this readable.) All four LSNs are identical because the system is idle — there is no WAL in flight for them to disagree about. The three lag columns are *null*, not zero, and that is documented behaviour rather than "nothing has happened yet": per §28.2, once the standby has entirely caught up the last measured lag is shown for a short while and then goes NULL. Run this query within a second or two of starting the standby and you'll catch real sub- millisecond values instead; wait a few seconds and they blank out. And `sync_state | async` is the default — the primary is not waiting for this replica for anything. Note this view lives on the **primary**. Run it on the replica and you get zero rows, which confuses everyone once. 3. **P**: create something to replicate. ```sql CREATE TABLE events (id bigserial PRIMARY KEY, payload text, created_at timestamptz DEFAULT clock_timestamp()); INSERT INTO events (payload) VALUES ('hello from the primary'); ``` **R**: `SELECT id, payload FROM events;` ``` id | payload ----+------------------------ 1 | hello from the primary ``` The DDL replicated too. Physical replication ships WAL, and `CREATE TABLE` is WAL — there is no schema/data distinction at this level. 4. **Predict**: the replica is a fully functional Postgres with the whole table in it. What happens if you write to it? 5. **R**: ```sql INSERT INTO events (payload) VALUES ('hello from the replica'); ``` 6. **Observe**: ``` ERROR: cannot execute INSERT in a read-only transaction ``` And `CREATE TABLE t (x int);` gives `ERROR: cannot execute CREATE TABLE in a read-only transaction`. This isn't a permissions check on the `postgres` role — it's structural. The replica's WAL position is a single linear pointer into the primary's stream; a local write would fork history and there would be no way to continue replaying. So every write path is refused, superuser or not. The docs put it flatly: *"All such connections are strictly read-only; not even temporary tables may be written."* 7. **R**: the replica's own view of where it is. ```sql SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(); ``` ``` pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp ------------------------+------------------------------- 0/30655B8 | 2026-07-25 16:23:23.414661+00 ``` These are the two functions you actually use in production monitoring, because they work from the replica — where your read traffic is — rather than requiring a connection to the primary. ### 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. 8. **Predict**: you're about to insert a million rows on the primary and then immediately count on the replica. The replica currently sees 1 row. What count comes back — something part-way, or one of the endpoints? 9. **P**: ```sql INSERT INTO events (payload) SELECT 'burst-'||g FROM generate_series(1,1000000) g; ``` **R** (immediately): `SELECT count(*) FROM events;` 10. **Observe**: ``` count ------- 1 ``` One. Not 400,000, not 999,000 — the same number as before the burst. **P**, meanwhile: ```sql SELECT pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind FROM pg_stat_replication; ``` ``` behind -------- 16 MB ``` 16 MB of WAL exists that the replica has not applied. Wait a few seconds, re-run the count on **R**, and it is `1000001`. Speed matters here, and it's the one place the exercise is fragile. The insert itself took 1.8 s, and the replica applies WAL the whole time it is running — so what you are catching is only the tail. If you type the count into a *fresh* `docker exec` instead of the already-open terminal **R**, the few hundred milliseconds of container startup are enough for the replica to finish, and you'll see `1000001` and conclude there's no lag at all. 11. Now watch the drain happen rather than sampling it. In **P**, leave this running (`\watch` re-runs a query on an interval): ```sql 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 ``` Then in a *third* shell (or just re-use **R**'s terminal with a `docker exec` to the primary), fire a bigger burst: ```sql INSERT INTO events (payload) SELECT 'burst2-'||g FROM generate_series(1,3000000) g; ``` 12. **Observe** (the repeated `\watch` header block between iterations is trimmed here; each line is one iteration): ``` 16:23:34.839 | 0/C6CC740 | 0/C6CC740 | 0 bytes | 00:00:00.06938 16:23:35.338 | 0/C6CC740 | 0/C6CC740 | 0 bytes | 00:00:00.06938 16:23:35.839 | 0/C6CC740 | 0/C6CC740 | 0 bytes | 00:00:00.06938 16:23:36.341 | 0/C6CC778 | 0/C6CC778 | 0 bytes | 00:00:00.001624 16:23:36.838 | 0/C6CC778 | 0/C6CC778 | 0 bytes | 00:00:00.001624 16:23:37.337 | 0/E000000 | 0/CECD858 | 17 MB | 00:00:00.250322 16:23:37.836 | 0/11000000 | 0/107FFFE8 | 8192 kB | 00:00:00.121514 16:23:38.336 | 0/13400000 | 0/12FFFFD8 | 4096 kB | 00:00:00.239075 16:23:38.837 | 0/16C00000 | 0/159C5470 | 18 MB | 00:00:00.20663 16:23:39.339 | 0/193FE000 | 0/193FDFE0 | 32 bytes | 00:00:00.141107 16:23:39.836 | 0/1CC00000 | 0/1BFFFFF0 | 12 MB | 00:00:00.129262 16:23:40.339 | 0/1F400000 | 0/1F3FFFB8 | 72 bytes | 00:00:00.090136 16:23:40.836 | 0/22000000 | 0/21FFFFD0 | 48 bytes | 00:00:00.152997 16:23:41.339 | 0/25000000 | 0/23FFFFB0 | 16 MB | 00:00:00.261945 16:23:41.839 | 0/27400000 | 0/273FFFD0 | 48 bytes | 00:00:00.100813 16:23:42.337 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 16:23:42.841 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 16:23:43.339 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 16:23:43.843 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 16:23:44.34 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 16:23:44.839 | 0/28A04938 | 0/28A04938 | 0 bytes | 00:00:00.114559 ``` Both LSNs march forward together, with the replica sawtoothing between roughly 18 MB behind and a handful of *bytes* behind, then settling at `0 bytes` the moment the burst ends. Steady state on an idle link is exact equality, not "close enough". The sawtooth is the shape to notice: replay isn't a smooth trickle, it proceeds in bursts as WAL segments arrive and get applied. The peak depends entirely on how loaded your host is — this run sawtoothed against 18 MB, an otherwise-busy run of the identical burst climbed in one hump to 144 MB before draining. What is *not* run-variable is the last two lines: once writes stop, the two LSNs become the same number. Also look at `replay_lag` — it stays around 0.09–0.26 s the entire time, even in the rows where the replica is 16 MB behind. Bytes-behind and time-behind are genuinely different measurements: the replica is far behind in *volume* while staying close behind in *wall-clock*, because it is applying continuously and just can't keep up with the rate. 13. **Predict**: so during those seconds when the replica is 16 MB behind, a `count(*)` on it should return something part-way through the burst. Should it not? 14. **R**: watch the count itself, then burst again from **P**. ```sql SELECT clock_timestamp()::time(3) AS t, count(*) FROM events \watch 0.3 ``` ```sql INSERT INTO events (payload) SELECT 'atomic-'||g FROM generate_series(1,3000000) g; ``` 15. **Observe** (headers trimmed; the middle of the run is elided, every sample in it reads `4000001`): ``` 16:23:51.386 | 4000001 16:23:51.716 | 4000001 16:23:52.043 | 4000001 … 16:23:59.344 | 4000001 16:23:59.601 | 4000001 16:23:59.893 | 4000001 16:24:00.229 | 7000001 16:24:00.506 | 7000001 16:24:00.797 | 7000001 16:24:01.087 | 7000001 ``` Eight and a half seconds at `4000001` — thirty consecutive samples — and then a single step to `7000001`. Never `5200000`. Never anything in between. This is the most important thing in the exercise, and it's the thing the phrase "replication lag" tends to hide. The replica *was* receiving and applying those three million row-insert WAL records the whole time — you just watched `replay_lsn` climb through them in step 12. But row versions only become visible when the transaction that created them is known to have committed, and the commit record is a single WAL record at the very end. Replaying it flips all three million rows into visibility at once. MVCC on the replica is the same MVCC as on the primary, so a replica is never "half a transaction" behind — it is always a consistent snapshot of some past moment of the primary. Which is a much better guarantee than "it's a bit behind", and a much worse one for your latency graph: a single huge transaction means the replica is stale by *its entire duration*, no matter how fast your network is. ### 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. 16. **R**: note where you are, then stop time. ```sql SELECT count(*) FROM events; SELECT pg_wal_replay_pause(); SELECT pg_get_wal_replay_pause_state(); ``` ``` count --------- 7000001 pg_get_wal_replay_pause_state ------------------------------- paused ``` 17. **P**: write, with the replica frozen. ```sql INSERT INTO events (payload) SELECT 'paused-'||g FROM generate_series(1,200000) g; ``` 18. **Predict**: the replica is not applying WAL. Is it also not *receiving* it? What do you expect `flush_lsn` and `replay_lsn` to say? 19. **P**: ```sql 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; ``` 20. **Observe**: ``` -[ RECORD 1 ]-+---------------- sent_lsn | 0/46D266D0 write_lsn | 0/46D266D0 flush_lsn | 0/46D266D0 replay_lsn | 0/44F113C8 write_lag | 00:00:00.037965 flush_lag | 00:00:00.043601 replay_lag | 00:00:00.244351 flush_behind | 0 bytes replay_behind | 30 MB ``` There it is: the four LSNs split into two groups. `sent`, `write` and `flush` are all at `0/46D266D0` — every byte the primary produced is already `fsync()`ed onto the replica's disk, `0 bytes` behind. And `replay_lsn` is stuck at `0/44F113C8`, exactly where it was when you paused, 30 MB back. Read that as a sentence: **those 200,000 rows are durable on two machines and queryable on neither but one.** If the primary's disk caught fire right now, nothing is lost. If you query the replica right now, they don't exist. Durability and visibility are separate properties, and this is the row of the mental-model table that most people collapse into one. 21. **R**: confirm from the other side. ```sql SELECT count(*) FROM events; SELECT pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS staleness; ``` ``` count --------- 7000001 pg_last_wal_replay_lsn | staleness ------------------------+----------------- 0/44F113C8 | 00:00:20.135367 ``` Still `7000001`. The replica is serving reads perfectly happily — it is a completely healthy database, just one that is now twenty seconds in the past. Now go back to **P** and watch what `replay_lag` does while the stall continues: ```sql 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 ``` ``` 16:24:28 | 00:00:14.461593 | 30 MB 16:24:33 | 00:00:14.461593 | 30 MB 16:24:38 | 00:00:27.074364 | 30 MB 16:24:43 | 00:00:27.074364 | 30 MB 16:24:48 | 00:00:37.089507 | 30 MB 16:24:53 | 00:00:37.089507 | 30 MB 16:24:58 | 00:00:47.102225 | 30 MB 16:25:03 | 00:00:47.102225 | 30 MB 16:25:08 | 00:00:57.089105 | 30 MB 16:25:13 | 00:00:57.089105 | 30 MB ``` **`replay_lag` is a staircase, not a clock.** It does climb — but only in ~10-second steps, holding the same value for two consecutive five-second samples before jumping. That interval is not a coincidence: it is `wal_receiver_status_interval`, which defaults to `10s` and is how often the standby volunteers its position. `replay_lag` can only be recomputed when a status message arrives, so between messages it is a stale reading of a stale reading. Two consequences, and they are the operational point of this whole part: - **In the first seconds of a stall it reads near zero.** Step 20 was taken two seconds after the write, with replay completely stopped and 30 MB outstanding, and `replay_lag` said `00:00:00.244351`. If your alert samples every 15 s and fires on `replay_lag > 5s`, a stall that starts just after a status message is invisible for a full interval. - **It measures the wrong thing.** At `16:25:13` the primary reported `00:00:57`; the replica's own `now() - pg_last_xact_replay_timestamp()` at that moment said `00:01:15.532`. Both are correct and they answer different questions. `replay_lag` is "how long has this WAL been un-applied" — it starts counting when the primary flushed the record. Staleness is "how old is the newest data a client can see here" — it counts from the *commit timestamp* of the last transaction actually applied, which is earlier. The second number is the one your users experience. So: alert on `now() - pg_last_xact_replay_timestamp()` measured on the replica, or on `pg_wal_lsn_diff` bytes, which was pinned at a flat `30 MB` for the entire stall and never lied for a moment. 22. **Predict**: you're about to resume. Does the replica have to re-fetch those 30 MB? 23. **R**: `SELECT pg_wal_replay_resume();` then `SELECT count(*) FROM events;` 24. **Observe**: ``` count --------- 7200001 ``` By the time you can type the next query it has already caught up: back on **P**, `replay_behind` reads `0 bytes`. The WAL was already on local disk (that's what `flush_lsn` was telling you), so catching up was pure CPU, no network — timed on a repeat run, the 30 MB backlog applied in about 0.4 s. Fire the count fast enough and you can still catch the old `7000001`, which is a neat demonstration that the "instant" jump has a real, measurable width. ### 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. 25. **P**: promote the standby to synchronous. ```sql ALTER SYSTEM SET synchronous_standby_names = '*'; SELECT pg_reload_conf(); SELECT application_name, sync_state, sync_priority FROM pg_stat_replication; ``` ``` application_name | sync_state | sync_priority ------------------+------------+--------------- walreceiver | sync | 1 ``` `async` → `sync`, with no restart — this is a reload-only setting. `'*'` means "any connected standby will do"; in production you name them, and the name is the standby's `application_name`. (Run these as two separate statements. `ALTER SYSTEM` cannot run inside a transaction block, so pasting both on one line into `psql -c` fails with `ERROR: ALTER SYSTEM cannot run inside a transaction block`.) 26. **R**: freeze replay again, so "has the replica applied it?" is a question with a knowable answer. ```sql SELECT pg_wal_replay_pause(); ``` 27. **Predict**: replication is now synchronous and the replica is not applying anything. Does a plain `INSERT` on the primary block? 28. **P**: ```sql INSERT INTO events (payload) VALUES ('sync-A'); ``` 29. **Observe**: it returns immediately. ``` INSERT 0 1 real 0m0.086s ``` 86 ms, and that is almost entirely `docker exec` startup. No blocking at all. Because `synchronous_commit` defaults to `on`, and per the mental model `on` waits for the standby's **`flush_lsn`**. The pause stopped replay, not receipt — flush is still keeping up, so the commit's condition is satisfied. "Synchronous replication" out of the box guarantees durability on two nodes, and says nothing whatsoever about whether you can read your write on the replica. 30. **Predict**: now change one setting — the last row of the table — and run the same insert. 31. **P**: ```sql SET synchronous_commit = remote_apply; INSERT INTO events (payload) VALUES ('sync-B'); ``` 32. **Observe**: nothing. The `INSERT` hangs, indefinitely, with the replica's replay paused. Same statement, same cluster, same replica — the only thing that changed is which of the four LSNs the commit is waiting on. From a second connection to the primary you can see exactly what it is waiting on: ``` pid | state | wait_event_type | wait_event | query -----+--------+-----------------+------------+------------------------------------------ 206 | active | IPC | SyncRep | INSERT INTO events (payload) VALUES ('sy ``` Leave it hanging and hit **Ctrl-C**: ``` postgres=# INSERT INTO events (payload) VALUES ('sync-B'); ^CCancel request sent WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby. INSERT 0 1 postgres=# ``` Read that carefully, because it is one of the sharpest edges in Postgres. You cancelled the statement and the statement *committed anyway* — `INSERT 0 1`. Cancelling a synchronous commit only abandons the *wait*; the transaction was already durable locally before the wait began. There is no way to un-commit it. So a client that times out and reports failure to its user may be reporting a transaction that is permanently in the database. Any application talking to a synchronous cluster has to treat a commit timeout as "unknown", never "failed". 33. **R**: `SELECT pg_wal_replay_resume();` — and on **R**, `SELECT payload FROM events WHERE payload LIKE 'sync-%';` returns: ``` payload --------- sync-A sync-B ``` Confirming the cancelled one is really there. 34. **Predict**: the failure mode. Replication is synchronous with `synchronous_standby_names = '*'` and exactly one standby. What happens to the primary if that standby goes away entirely? 35. In a shell: `docker stop pg-replica`. Then on **P**, with `synchronous_commit` back at its default `on`: ```sql INSERT INTO events (payload) VALUES ('during-outage'); ``` 36. **Observe**: it hangs. Not for a timeout period — there is no timeout. Open a second connection to the primary and look: ```sql SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query FROM pg_stat_activity WHERE state = 'active' AND wait_event = 'SyncRep'; ``` ``` -[ RECORD 1 ]---+----------------------------------------- pid | 223 state | active wait_event_type | IPC wait_event | SyncRep query | INSERT INTO events (payload) VALUES ('du ``` `wait_event = SyncRep`, and `SELECT count(*) FROM pg_stat_replication;` returns `0` — there is no standby to satisfy the condition, so the condition can never be satisfied. This is a primary that is up, healthy, accepting connections, answering every read query instantly, and unable to commit a single write. The docs say so in one flat sentence: *"Such transaction commits may never be completed if any one of the synchronous standbys should crash."* This is the classic operational trap of synchronous replication with one standby: you configured it to avoid losing data, and you converted a *replica* outage into a *primary* outage. The standard answer is to run at least two standbys with `synchronous_standby_names = 'ANY 1 (s1, s2)'`, so any one of them can be down without stalling writes. 37. **The escape hatch.** From that second connection — reads and `ALTER SYSTEM` still work fine: ```sql ALTER SYSTEM SET synchronous_standby_names = ''; SELECT pg_reload_conf(); ``` The hung `INSERT` returns the instant the reload lands: ``` INSERT 0 1 real 0m10.437s ``` 10.4 s, which was exactly how long it took to type the escape hatch. Downgrading to asynchronous is a config reload, no restart — which is the thing to have committed to memory before you ever turn synchronous replication on. 38. `docker start pg-replica`, wait a few seconds, and check **P**: ```sql SELECT application_name, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind FROM pg_stat_replication; ``` ``` application_name | state | sync_state | behind ------------------+-----------+------------+--------- walreceiver | streaming | async | 0 bytes ``` It reconnected on its own and caught up, including `during-outage` — both nodes now agree on `7200004`. The standby re-requests the stream from its last flushed position; because the primary still had those WAL segments on disk, it simply resumed. Note the word *because* — see "Go deeper" for what happens when it doesn't. ## 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](../ddia/ch06/read-your-writes.md). 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 36 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](mvcc-and-vacuum.md): a long read on your replica becomes bloat on your primary. - **Replication slots.** Step 38 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 31 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](../ddia/ch06/read-your-writes.md) 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](../ddia/ch06/monotonic-reads.md) 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. ## Cleanup ```bash docker rm -f pg-primary pg-replica docker volume rm pg-replica-data docker network rm pgnet ```