Part III · Chapter 4 · The Database · lesson 2
You added a read replica and users started seeing old data
13 min
Builds on Why the diagram has two of everything and Why it gets slow at 70% busy, not at 100%
Part III · Chapter 4 · The Database · lesson 2
13 min
Builds on Why the diagram has two of everything and Why it gets slow at 70% busy, not at 100%
The primary was at 80% CPU, so you added a Postgres streaming replica and pointed the read-heavy endpoints at it. Three good weeks. Then the tickets: an order marked shipped still shows as pending, a renamed project shows its old name. Never reproducible. Always somebody who had just done something.
A second copy is not storage. It is a promise about time, and nobody wrote the promise down.
Predict
Postgres replays the write-ahead log on a replica in a single recovery process, serially, in commit order. That is a queue with one server in it, and the standard single-server result applies: the wait grows with one over the spare capacity, which means it is flat and then it is not.
Queueing delay
Hypothetical model, not a forecast
How long work waits in a single-server queue as utilisation rises, and the utilisation at which that wait breaks a stated budget.
It assumes
It ignores
Leave the write rate and the replay cost alone. Tighten the lag budget until the replay utilisation at which it breaks falls to 95% or below, and read off what you had to promise to get a utilisation number worth watching.
Requests reaching the server each second.
How long one request occupies the server when nothing is queued.
The queueing wait that counts as too long. Service time is not part of it.
| Replay utilisation (%) | Time a write waits before replay starts | Total lag, wait plus replay |
|---|---|---|
| 1 | 202 us | 20 ms |
| 2 | 408 us | 20 ms |
| 5 | 1.1 ms | 21 ms |
| 10 | 2.2 ms | 22 ms |
| 20 | 5 ms | 25 ms |
| 30 | 8.6 ms | 29 ms |
| 40 | 13 ms | 33 ms |
| 50 | 20 ms | 40 ms |
| 60 | 30 ms | 50 ms |
| 70 | 47 ms | 67 ms |
| 80 | 80 ms | 100 ms |
| 85 | 113 ms | 133 ms |
| 90 | 180 ms | 200 ms |
| 92 | 230 ms | 250 ms |
| 94 | 313 ms | 333 ms |
| 95 | 380 ms | 400 ms |
| 96 | 480 ms | 500 ms |
| 97 | 647 ms | 667 ms |
| 98 | 980 ms | 1,000 ms |
| 99 | 1.98 s | 2 s |
Not there yet — keep moving the controls.
Two readings matter more than the shape.
Utilisation is useless as an early warning here. The model reports the utilisation at which the lag budget breaks, and at a one-second budget against a twenty-millisecond replay cost that figure is around 98%. The queueing wait is measured in multiples of the service time, and a one-second promise is fifty of them, so you can run the apply process very hot indeed and still look fine. To get a utilisation ceiling low enough to watch on a dashboard you have to promise lag in the low hundreds of milliseconds, which is a promise most products cannot keep. So alarm on lag, not on replay utilisation.
The cliff is real and it is narrow. Push the write rate until the model flags the queue as overloaded and the arithmetic stops producing a lag at all: once arrivals exceed what the replay process can apply, the backlog grows for as long as the overload lasts, and recovery takes as long again. That is the whole ticket pattern — nothing for three weeks, a cluster of unreproducible complaints during one busy hour, then nothing again.
On a Postgres primary, pg_stat_replication gives you write_lag,
flush_lag and replay_lag per connected replica — three different promises,
and replay_lag is the one your readers feel. On the replica itself,
now() - pg_last_xact_replay_timestamp() is the figure to graph, and
pg_wal_lsn_diff against pg_last_wal_replay_lsn() gives you the same gap in
bytes, which is the better alarm because it keeps working when the primary
goes quiet.
That last point is the trap. A time-based lag metric reads zero on an idle
primary whether the replica is healthy or disconnected, because there is
nothing newer to be behind. MySQL's Seconds_Behind_Source has the same flaw
and two more: it is computed from the applier thread's position, so it reads
zero while a replica is still fetching a backlog it has not started applying,
and it jumps around when replication breaks and reconnects. If you run MySQL,
graph the worker timestamps in
performance_schema.replication_applier_status_by_worker instead, or run a
heartbeat row that the primary updates on a timer so the metric never has
nothing to measure.
On managed services the metric is handed to you and it is not the same metric:
Amazon RDS publishes ReplicaLag in seconds, Aurora publishes
AuroraReplicaLag in milliseconds against a shared storage layer rather than
a replayed log, and Google Cloud SQL publishes a replica lag metric per
replica. Read the definition before you alarm on the number.
Check
Route by intent, not by endpoint. Every read path gets a declared tolerance at the point the route is defined: current, or stale-tolerant with a number attached. Stale-tolerant goes to the replica. Current goes to the primary, and the list of current paths is short enough to read in one screen.
Then two guardrails. Alarm on the byte gap rather than the seconds, with the
threshold derived from your declared tolerance and not from what looks normal.
And set max_standby_streaming_delay deliberately: Postgres will either cancel
a long-running query on the replica or let replay stall behind it, and the
default favours the query, which converts a slow report into replica lag for
everyone else.
What I would not do is let the application choose per query. A per-call flag turns into a flag that defaults to the replica because the replica is faster, and the one request that must never read stale data is the one somebody added last Thursday.
Three categories, and only the first is about staleness.
A read whose result the same user is about to see again. This is read-your-own-writes, it is the most common complaint, it has four standard fixes with four different costs, and it gets its own lesson next.
A read that authorises a write. Checking a balance before a debit, checking stock before a reservation, checking a permission before an action. The read is not being displayed, it is being used as a precondition, and a stale precondition is a wrong decision rather than a stale screen. These belong on the primary even where the staleness is small, because the failure is not visual.
A read that will be compared against another read. Two queries served by two replicas at different positions in the log can disagree with each other in ways neither disagrees with the primary, which produces totals that do not add up and foreign keys that appear broken. If one request makes several reads that must be mutually consistent, they need one connection to one copy.