Refresh the Page, and the Comment Is Gone

With two followers at different lag, a user's repeated reads can move backward in time — a count goes 1 then 0 for the same query as the refresh lands on a laggier replica. You reproduce it on two real Postgres replicas, then see why "monotonic reads" is the guarantee that forbids it.

Concept

Replication lag causes a second, stranger anomaly than a stale read: with two followers at different lag, a user's repeated reads can move backward in time. Read once from a caught-up replica and you see a new comment; refresh, get routed to a laggier replica, and the comment is gone — the second read observes an older state than the first. You will reproduce it on two real Postgres replicas — one fresh, one lagged 5 s — and watch a count go 1 then 0 for the same query, then see why "monotonic reads" is the guarantee that forbids it.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 6 — Replication, §"Monotonic reads":

it's possible for a user to see things moving backward in time … the second query observes the system state at an earlier point in time than the first.

The book describes a user refreshing a page and each request hitting a random replica. Here you stage exactly that and watch time reverse.

Prerequisites

The surprise is about reads bouncing between replicas at different lag. These help; each line notes where it shows up.

1. Multiple followers, different lag — a leader can have many followers, each independently behind by its own amount. A load balancer may route successive reads to different ones.
2. Replication lag (again) — the delay between a leader commit and a given follower reflecting it; here one follower is fresh, one is 5 s behind.
3. Monotonic reads — the guarantee that if a user reads a newer state, a later read never shows them an older one. Weaker than reading the latest value; stronger than nothing. The property being violated.
4. Sticky routing — pinning a user's reads to one replica (e.g. by hashing the user ID), the usual way to provide monotonic reads. The fix.

Mental model

setup_replication.sh gives you a primary and two followers: ddia-ch6-standby2 (fresh — applies WAL immediately) and ddia-ch6-standby (lagged 5 s). A user reads the same query twice; the first read lands on the fresh replica, the refresh lands on the laggy one. In the 5-second window after a write, the fresh replica has the new comment and the laggy one doesn't — so the user's two reads disagree, newer then older. The setup is scripted; the reads you run by hand.

Setup

cd study/ddia/ch06/code
bash setup_replication.sh     # primary + fresh standby (…-standby2) + 5s-lagged standby (…-standby)

Do not read the output yet — make each prediction first.

docker exec ddia-ch6-primary psql -U postgres -d study -c \
  "INSERT INTO comments VALUES (2, 'a hot take');"
sleep 1

# first read -> fresh replica:
docker exec ddia-ch6-standby2 psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=2;"
# refresh    -> laggy replica:
docker exec ddia-ch6-standby  psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=2;"

# both reads for this user go to the SAME replica -> the second never predates the first
docker exec ddia-ch6-standby2 psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=2;"
docker exec ddia-ch6-standby2 psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=2;"
#  -> 1, then 1

What you should see

• Same query, two replicas: count 1 (fresh) then 0 (laggy) — a read into the past.
• Pinning both reads to one replica: 1 then 1 — time only moves forward.
• Nothing was deleted; the reads hit different points in the replication stream.

Why

A leader can have many followers, and they lag independently — one might be current while another is seconds behind, depending on load, network, and (here) a deliberate apply delay. A stateless load balancer happily sends a user's first request to one follower and their refresh to another. Each follower is internally consistent — it shows some past state of the database — but two different followers show two different past states. When the newer one answers first and the older one answers second, the user watches the database run backward: a comment appears, then un-appears. It's the read-your-writes lag from the previous exercise, seen through a second lens — not "my own write is missing" but "the timeline jumped backward between two reads."

Monotonic reads is the precisely-scoped guarantee that rules this out. It does not promise you the latest data (that would forfeit async replication's benefit); it promises only that your reads never regress — once you've seen a state, you won't later see an older one. The standard implementation is sticky routing: map each user to one replica (hash their ID) so all their reads traverse a single follower's monotonically-advancing timeline. That follower can be behind the leader, and different users can be pinned to different followers at different lag — none of that matters, because within one user's session the state only moves forward.

Go deeper

1. Random routing, repeated. Alternate reads between the two standbys in a loop for ~10 s after a write. Predict the pattern of 1s and 0s as the laggy replica slowly catches up — the flicker a real random-routed user would see.
2. Lag from the leader's view. Run SELECT application_name, replay_lag FROM pg_stat_replication on the primary during the window. Predict which standby shows ~5 s of lag and which shows ~0.
3. Sticky by hashing. Write a tiny router that hashes a user ID to one of the two standbys and always reads there. Predict whether any user can still see time go backward (they can't — that's the point) and what happens the moment their replica restarts.

Sources: DDIA 2e, Ch. 6, §"Monotonic reads", §"Solutions for Replication Lag" · the PostgreSQL streaming replication docs (pg_stat_replication).