Read replicas and what the application must accept · PostgreSQL

Read replicas are usually adopted to reduce load on the primary. What they actually do is move a consistency problem into the application.

The guarantee

A read replica gives you eventual consistency, with no bound on how eventual. A query on the replica sees the database as of pg_last_wal_replay_lsn(), which is some unspecified distance behind the primary.

Lesson XIV-04 measured that distance as 2.5 ms at 1,623 tps on a healthy pair — and lesson XIV-07 measured the same pair 124 MB and 30 seconds behind, because one ordinary read query conflicted with replay. Both are normal operation.

The measurement that proves the hazard by failing to find it

Read-only / Safe200 write-then-read-from-the-replica trials— EXECUTION VALIDATED on 18.6. Each trial: INSERT on the primary, then immediately count rows on the standby over a fresh connection, and compare with the primary's own count.

$ for i in $(seq 1 200); do
psql -c "INSERT INTO rw(note) VALUES ('x')"
n=$(psql -h rbpg-sb -t -A -c "SELECT count(*) FROM rw")
m=$(psql        -t -A -c "SELECT count(*) FROM rw")
[ "$n" != "$m" ] && stale=$((stale+1))
done

trials: 200   reads that did not yet see the write: 0

Zero stale reads is the finding, and it is not reassuring

Both containers were on one host. Replication replayed in about 2.5 ms, and establishing the connection to the standby took longer than that. The race was real and the client lost it every time.

This is exactly why the hazard reaches production. On a healthy low-latency pair the bug is nearly unobservable: it does not appear in development, it does not appear in staging, and it does not appear in a 200-trial test written specifically to find it.

It appears when the replica falls behind — under load, during a conflicting query, when the network hiccups, when a batch job runs. That is precisely when the system is already under stress, which is when a “user updated their profile and the next page shows the old values” bug is hardest to diagnose and most damaging.

An application that reads its own writes from a replica works in staging and fails in production.

What can safely go to a replica

Safe:

Analytical queries and reports, where minutes-old data is fine.
Dashboards and aggregates.
Search across data that changes slowly.
Exports and batch reads.
Anything already tolerating a cache.

Not safe without care:

Reading a record immediately after writing it.
Any read-modify-write cycle.
Uniqueness or existence checks before an insert.
Anything a user perceives as “did my change save?”
Session state.

The dividing line is not “reads versus writes”. It is whether the caller can distinguish stale data from correct data, and whether it matters when they can.

Routing

Application-level routing is explicit and it is the only mechanism that can express intent:

# unambiguous, and the decision is visible in the code
report_rows = replica.query("SELECT ... FROM big_table ...")
user        = primary.query("SELECT * FROM users WHERE id = %s", uid)

target_session_attrs lets libpq choose from a list:

postgresql://host1,host2/app?target_session_attrs=read-write   # primary
postgresql://host1,host2/app?target_session_attrs=prefer-standby

Useful for finding the primary after a failover, and not a substitute for deciding which queries may be stale.

A proxy (pgpool-II, HAProxy, pgbouncer with routing in front) can route by rule. The rules are heuristics, and a heuristic that sends a SELECT to a replica does not know whether that SELECT follows a write in the same logical operation.

If you need read-your-writes, ask for it explicitly

Two mechanisms, and both require the application to participate.

Route the read to the primary. Simplest, always correct, and it gives up the offload for that operation — which is usually the right trade, because these operations are a small fraction of traffic.

Wait for the LSN. Capture the primary’s LSN after the write and have the replica wait until it has replayed that far:

-- on the primary, immediately after the write
SELECT pg_current_wal_insert_lsn();

-- on the replica, before the dependent read
SELECT pg_last_wal_replay_lsn() >= '0/3A2F118'::pg_lsn;

This is correct and it costs a round trip plus a wait whose duration is exactly the lag you were trying to ignore. It is worth it for a small number of operations and is not a general strategy.

What does not work is a fixed sleep. It is unbounded lag; there is no delay that is both short enough to be acceptable and long enough to be safe.

Does a read replica solve your problem?

Worth asking before building one, because the answer is often no.

If the primary is CPU-bound on reads — yes, this is what replicas are for.

If the primary is I/O-bound on reads — yes.

If the primary is write-bound — no. Every replica replays every write. Replicas add write load to the system as a whole and remove none from the primary.

If specific queries are slow — probably not. A slow query is slow on the replica too, and lesson X-08’s methodology will help more than a second machine.

If connection count is the problem — no. That is lesson IV-06’s pooling, and a replica multiplies connections rather than reducing them.

If you need a failover target — yes, and lesson XIV-07’s settings for that role are the opposite of a reporting replica’s. Part XV covers what else failover requires.

Why the replica can be slower than the primary on the same query

A standby holds the same data and frequently runs the same query more slowly, for reasons that are all consequences of it being a recovery process rather than a normal cluster.

Hint bits. When a backend on the primary first reads a tuple whose transaction has committed, it sets a hint bit recording that fact, so later readers skip the commit-log lookup. A standby cannot set hint bits — it may not write pages recovery did not tell it to write. Every read of a not-yet-hinted tuple therefore does the lookup, on every read, forever. The effect is largest on recently-written data, which is usually what a report is reading.

A cold and differently-shaped cache. The standby’s buffer pool reflects what replay has touched, not what queries want. Replay pulls in pages being modified; a reporting query wants pages being read. The two working sets can barely overlap, which is why an identical query can be buffer-hits on the primary and disk reads on the standby.

Replay competing for I/O. The startup process is reading WAL and writing pages continuously, on the same storage the query is reading.

Restartpoints. The standby’s equivalent of checkpoints, with the same write bursts as lesson XII-04 measured, and constrained to positions replay has reached rather than to a schedule you chose.

There are also two things that genuinely differ in the standby’s favour: its work_mem and shared_buffers can be sized for analytics without regard to the primary’s transaction workload, and it has no autovacuum competing for I/O because it has nothing to vacuum.

The practical advice is to measure the query on the replica rather than assuming parity, and to expect the largest divergence on the newest data — which is exactly where hint bits have not yet been set and where replay has just been writing.

What to take from this

A read replica is eventually consistent with no bound on how eventual.
0 stale reads in 200 trials on a healthy pair is why this bug reaches production, not evidence that it is safe.
Route by whether the caller can tolerate stale data, not by read-versus-write.
Read-your-writes needs the primary, or an explicit LSN wait. Never a sleep.
A replica does not help a write-bound primary, a slow query, or a connection-count problem.
The same query can be slower on the replica: no hint bits, a cache shaped by replay, and replay competing for I/O.

Cross-course references

Kubernetes for Production Sysadmins — Part XXXVIII (Services) and Part XXXIX (Service discovery) cover routing reads to a replica set and what happens to that routing when a member is not caught up.
Git, CI/CD & GitOps — Part CVI (Change management) covers getting the read-your-writes consequence agreed with the application team before the replica is offered to them.