Lesson 7: Replication & Scaling the Read Tier

The last five lessons were all layers that sit in front of the database. This one steps into the database itself, and the first question is the cheapest in the whole series: if your read-to-write ratio is 100:1 — a very common number — then simply adding copies to read from already handles 99% of the work.

This lesson's lab stands up a PostgreSQL primary and a real read replica, with streaming replication. And the very first measurement produced a number I did not see coming: the lab's replication lag is only 0.557 ms — less than half a thousandth of a second — and yet the share of users who "write, read back, and see stale data" was still 87.21%. Lag does not need to be large to break a system; it only needs to be larger than the gap between that same user's write and their next read.

7.1 Three different purposes, usually collapsed into one

"Add a replica" sounds like one job, but it serves three different goals — and those three goals need three different configurations. Collapsing them is the source of most disappointment with replicas.

7.2 The replication mechanism, and the price of each safety level

PostgreSQL does not copy "the data", it copies the WAL (write-ahead log) — the journal stream recording every change before it is applied. The replica receives that stream and replays it. MySQL calls it the binlog; the idea is the same.

What matters comes down to a single question: at what moment does the primary answer "write committed"? Answer early and writes are fast but data can be lost when the primary dies; answer late and it is safer, but every write has to wait for the replica.

The figures in the diagram come from pgbench — the load-measuring tool that ships with every PostgreSQL installation, nothing extra to install. It opens N connections, loops one SQL script for T seconds, and reports transactions per second (tps) and the average latency of a transaction. Here it runs inside the primary container with 8 connections for 10 seconds, and the script is a single INSERT — the less work inside the transaction, the closer the rest is to the pure cost of the commit step, which is exactly what the four synchronisation modes change:

cd blog/sysdesign/sysdesign-lab
docker compose --profile replica up -d

# The smallest possible write script
docker compose exec -T postgres sh -c 'echo "INSERT INTO write_load (filler)
  VALUES (repeat(md5(random()::text), 8));" > /tmp/w.sql'

# Switch mode, then measure again. `walreceiver` is the replica's application_name
# in this lab — read it from: SELECT application_name FROM pg_stat_replication;
for cfg in "'':local" "walreceiver:remote_write" \
           "walreceiver:on" "walreceiver:remote_apply"; do
  names="${cfg%%:*}"; mode="${cfg##*:}"
  docker compose exec -T postgres psql -U lab -d lab -q \
    -c "ALTER SYSTEM SET synchronous_standby_names = $names;" \
    -c "ALTER SYSTEM SET synchronous_commit = '$mode';" \
    -c "SELECT pg_reload_conf();"
  echo "== $mode"
  docker compose exec -T postgres pgbench -U lab -d lab -n -f /tmp/w.sql -c 8 -j 2 -T 10
done

7.3 Replication lag, and the bug every replicated system meets

This is the heart of the lesson. The familiar scenario: a user changes their avatar — the write goes to the primary. The page reloads — the read goes to a replica. If the replica has not applied the change yet, they see the old avatar and conclude the system is broken. That is a read-your-writes violation: you must always be able to read back what you yourself just wrote.

The figure below names the two intervals that decide this, and both symbols are used for the rest of the section: Δ (delta) is the replication lag — how long the replica needs to finish applying a change that just happened on the primary. ε (epsilon) is the gap between that same user's write and their next read. If ε is larger than Δ the replica has caught up and the user sees fresh data; if ε is smaller than Δ they see stale data. That is all there is to it — and note that both are intervals, so what decides the outcome is the ratio between them, not whether Δ is large or small.

The lab has a /rww endpoint that does exactly two things: write to the primary (using RETURNING version so it knows precisely which version it wrote), then immediately read from the replica. If the version read back is lower than the version just written, that is stale data — measurable, not a matter of impression. This is the smallest ε possible: the two statements sit right next to each other inside one request, with nothing in between. The three counters rwwPinned, rwwTotal and rwwStale are exactly where the percentages in the table below come from:

// 1) WRITE to the primary. RETURNING tells us exactly which version we just wrote.
const wrote = await pgPrimary.query(
  `UPDATE profiles SET avatar = 'avatar-v' || (version + 1) || '.png',
                       version = version + 1, updated_at = now()
   WHERE id = ${id} RETURNING version`
);
const wroteVersion = Number(wrote[0] && wrote[0].version);
if (usePin && READ_PIN_MS > 0) readPin.set(id, Date.now() + READ_PIN_MS);

// 2) READ IMMEDIATELY. This is the decisive moment: the replica may not have caught up.
const pinned = usePin && shouldReadPrimary(id, Date.now());
if (pinned) rwwPinned++;
const target = pinned ? pgPrimary : pgReplica;
const read = await target.query(`SELECT version, avatar FROM profiles WHERE id = ${id}`);
const readVersion = Number(read[0] && read[0].version);

rwwTotal++;
// "Stale" is MEASURABLE, not a feeling: reading a version LOWER than the one we
// just wrote is not open to argument.
const stale = readVersion < wroteVersion;
if (stale) rwwStale++;

Four configurations, each with 16 connections for 12 seconds, about 100,000 write-then-read rounds:

Row A is the one worth stopping on. A lag of 0.557 ms — a number no dashboard would alert on, and many people would call "essentially no lag" — still made 87% of reads return stale data. Because the app writes and then reads inside the same request, the gap ε between the two statements is only a few tens of microseconds. Against that ε, half a millisecond is a very long time.

Row C shows the fix working absolutely: 0 out of 98,358. And the price is surprisingly cheap — throughput falls from 7,547 to 7,200, which is 4.6%. Row D is the control: the same configuration but with pinning disabled per request sends the rate straight back to 100%, proving it is the pinning mechanism itself that made the difference and not some other incidental change.

The second fix: let the database handle it

There is another way that needs no application code changed at all: set synchronous_commit = remote_apply. The primary then answers "write committed" only after the replica has finished applying it, so every subsequent read on the replica sees fresh data. Measured for real, at natural lag, with no pinning whatsoever:

Both fixes give 0%, but they pay for it in two different places. Pinning at the application layer only slows down the reads of someone who just wrote — a very small share of traffic. remote_apply slows down every write in the system, including those of people who never read anything back. That is why the application-layer fix is the more common one, even though it means writing code.

Measuring lag correctly

PostgreSQL exposes three different kinds of lag, and only one of the three is the number a reader actually feels. The lab's tools/pg-lag.sh script asks the primary directly; here is its query portion, quoted verbatim:

# write_lag   time until the replica has WRITTEN the WAL to disk
# flush_lag   time until the replica has FLUSHED it (durable)
# replay_lag  time until the replica has APPLIED it ← THIS is what a reader sees
docker compose exec -T postgres psql -U lab -d lab -x -c "
  SELECT application_name,
         state,
         sync_state,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   AS unsent,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS unreplayed,
         COALESCE(write_lag::text,  '-') AS write_lag,
         COALESCE(flush_lag::text,  '-') AS flush_lag,
         COALESCE(replay_lag::text, '-') AS replay_lag
  FROM pg_stat_replication;" 2>/dev/null

Two columns worth noting: unsent is how much WAL the primary has not sent yet, and unreplayed is how much WAL the replica has received but not applied. The three lag columns behind them are wrapped in COALESCE because PostgreSQL returns NULL when there has been no transaction to measure — and NULL here means "not known", not "zero".

7.4 Failover and split-brain — building the disaster by hand

When the primary dies, a replica has to be promoted to become the new primary. It sounds simple, and PostgreSQL does it with exactly one statement. The problem is not the promoting, it is the question: how do you know for certain that the old primary really is dead?

The lab lets you reproduce the worst case exactly. We cut the network between the replica and the primary — simulating a partition, with the primary still alive and still accepting writes — and then promote the replica with no mechanism guaranteeing the old primary has stopped:

cd blog/sysdesign/sysdesign-lab

# Before: the replica is a standby and REFUSES every write
docker compose exec -T postgres-replica \
  psql -U lab -d lab -tAc "SELECT pg_is_in_recovery();"
# t

# 1) CUT THE NETWORK between replica and primary (primary is STILL ALIVE, still writing)
docker network disconnect sysdesign-lab_default sysdesign-lab-postgres-replica-1

# 2) PROMOTE the replica — no fencing, nobody confirmed the old primary is dead
docker compose exec -T postgres-replica \
  psql -U lab -d lab -tAc "SELECT pg_promote(wait => true);"
docker compose exec -T postgres-replica \
  psql -U lab -d lab -tAc "SELECT pg_is_in_recovery();"
# f        ← it is a primary now

# 3) BOTH now accept writes for the SAME row
docker compose exec -T postgres psql -U lab -d lab -tAc \
  "UPDATE profiles SET avatar='tu-PRIMARY-CU.png', version=version+100
   WHERE id=500 RETURNING version, avatar;"
# 762|tu-PRIMARY-CU.png
docker compose exec -T postgres-replica psql -U lab -d lab -tAc \
  "UPDATE profiles SET avatar='tu-PRIMARY-MOI.png', version=version+200
   WHERE id=500 RETURNING version, avatar;"
# 862|tu-PRIMARY-MOI.png

# 4) Reconnect the network. Can it reconcile itself?
docker network connect sysdesign-lab_default sysdesign-lab-postgres-replica-1
docker compose logs postgres-replica | tail -4
# LOG:  selected new timeline ID: 2
# LOG:  archive recovery complete
# LOG:  database system is ready to accept connections

The result: for the same row id=500, the old primary holds 762 | tu-PRIMARY-CU.png and the new one holds 862 | tu-PRIMARY-MOI.png (those two strings are just labels the write statements set — Vietnamese for "from-OLD-PRIMARY" and "from-NEW-PRIMARY" — so you can tell at a glance which node a row came from). The data has diverged. And reconnecting the network reconciles nothing — the log line selected new timeline ID: 2 is PostgreSQL itself telling you that history has forked in two.

7.5 Operating it: what to watch, and the classic conflict

The replica is the most easily forgotten component in a system, because when it works nobody sees it, and when it falls behind the symptom shows up somewhere else entirely — users report "wrong data", nobody reports "the replica is slow".

Reproduce the measurements yourself

cd blog/sysdesign/sysdesign-lab
docker compose --profile replica up -d   # real primary + replica, streaming replication

# Check that replication is actually running
./tools/pg-lag.sh
docker compose exec -T postgres-replica \
  psql -U lab -d lab -tAc "SELECT count(*) FROM profiles;"                    # 1000
docker compose exec -T postgres-replica \
  psql -U lab -d lab -tAc "UPDATE profiles SET version=99 WHERE id=1;"
# ERROR: cannot execute UPDATE in a read-only transaction  ← correct, replica is read-only

# --- A: stale-read rate at the NATURAL lag ---
./tools/cache-stats.sh reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/rww" -c 16 -d 12 -w 2 --json
./tools/cache-stats.sh          # look at the READ-YOUR-WRITES line

# --- B: force a CONTROLLED 200ms lag, simulating a replica in another region ---
docker compose exec -T postgres-replica psql -U lab -d lab \
  -c "ALTER SYSTEM SET recovery_min_apply_delay = '200ms';" -c "SELECT pg_reload_conf();"
./tools/cache-stats.sh reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/rww" -c 16 -d 12 -w 2 --json
./tools/cache-stats.sh          # 100%

# --- C: turn the pin-to-primary mechanism on ---
# set READ_PIN_MS to '500' for app1/app2/app3 in docker-compose.yml, then:
docker compose --profile replica up -d --force-recreate app1 app2 app3
./tools/cache-stats.sh reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/rww" -c 16 -d 12 -w 2 --json
./tools/cache-stats.sh          # 0%

# --- D: control run. Same pinned setup, but ?pin=0 disables the pin per request,
#        which proves the pin is what made the difference and not something else.
./tools/cache-stats.sh reset
docker compose run --rm loadgen loadgen.js \
  --url "http://lb:8080/rww?pin=0" -c 16 -d 12 -w 2 --json
./tools/cache-stats.sh          # back to 100%

# Restore the default
docker compose exec -T postgres-replica psql -U lab -d lab \
  -c "ALTER SYSTEM SET recovery_min_apply_delay = '0';" -c "SELECT pg_reload_conf();"

In summary

A replica serves three different purposes that need three different configurations, and a replica is not a backup — in the lab you get 0.557 ms to regret a bad delete. Choosing a synchronisation level is choosing a point on the durability ↔ write-latency axis: going from async to sync, write latency rose 79% and write throughput fell 44%.

The biggest lesson is row A of the table in section 7.3. A replication lag of 0.557 ms — a number every dashboard would show in green — still broke read-your-writes on 87% of reads. Because what decides the outcome is not whether the lag is large or small, but whether it is larger than the gap between that same user's write and their read. Inside a web request, that gap is measured in microseconds.

Both fixes give exactly 0%: pinning reads to the primary (application layer, −4.6% throughput) and synchronous_commit = remote_apply (database layer, −12.6%). They differ in where they pay: the application-layer fix only slows down the person who just wrote, while the database-layer fix slows down every write in the system.

Lesson 8 asks the question replicas cannot answer: what happens when the writes themselves exceed what one machine can take, or when the data no longer fits on one disk. That is when you have to shard — the most expensive step in the whole series in terms of complexity, and also the one people reach for too early most often.