Replicas, Lag, and Read Scaling

Pointing reads at pg-replica-a roughly doubles Cartwheel's read capacity and introduces one new class of bug. A customer commits an order on pg-primary at 11:04:07.140, the confirmation page reloads 50 milliseconds later, the reload is routed to the replica, and the order is not there. Nothing is broken. The replica has not applied that WAL record yet, and it never promised to.

This is the reason read scaling is a consistency decision rather than a routing decision. Turning it on is a configuration flag; getting it right means deciding, for every query in the application, how old an answer is acceptable. The dashboard on pg-replica-a tolerates 30 seconds. The confirmation page tolerates zero. Everything interesting is in between, and the server exposes the lag without proposing a threshold for it.

Where Lag Comes From

The structural cause is an asymmetry in parallelism. On pg-primary, WAL is produced concurrently by every backend that is writing — 3,000 orders a minute at the Saturday peak, plus roughly four million delivery_events rows a day. On the standby, that stream is applied by a single startup process, in order, one record at a time. When the write rate on sixteen cores outruns what one replay process can apply, the replica falls behind and there is no knob that adds a second replay process.

Beyond that, lag has three other sources worth telling apart: a slow network between the hosts, slower storage on the replica than on the primary, and replay being blocked outright by a conflicting query. The last one is the interesting case, because the replica looks healthy from every angle except the one that matters.

-- on pg-replica-a
SELECT now() - pg_last_xact_replay_timestamp()          AS data_age,
       pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS caught_up,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_last_wal_receive_lsn(),
                         pg_last_wal_replay_lsn()))     AS unapplied;

  data_age   | caught_up | unapplied
-------------+-----------+-----------
 00:41:12.6  | f         | 512 MB

Received and replayed are two different positions, and the gap between them is the whole diagnosis. Here half a gigabyte of WAL has arrived on the replica and is sitting unapplied, so the network and the disk are both fine and replay itself is stuck. From the primary's side the same story reads out of the three intervals the previous topic separated, and which of them is large is what points at the cause: a large write_lag means bandwidth or distance, while a small write_lag beside a large replay_lag means the WAL arrived promptly and one of the standby's own queries is in the way. pg_last_xact_replay_timestamp() converts all of it into the only number a product owner will ever ask about: how old is the newest data on this machine.

Read-Your-Writes

The confirmation-page bug has a name, and naming it matters because shrinking the lag does not remove it. It is a correctness problem that gets rarer as lag gets smaller and therefore harder to reproduce, harder to attribute, and much harder to argue about in a bug tracker. At 30 milliseconds of lag it affects perhaps one order in four hundred, which is exactly the frequency at which a team decides the reports are user error.

Of the fixes that actually work there are three, and each puts the cost somewhere different. The blunt one is sticky routing: after a session writes, send that session's reads to the primary for a fixed window, ten or fifteen seconds, and accept the extra primary load. The strict one is synchronous_commit = remote_apply on the transactions that need it, which makes the commit itself wait until the replica has applied and can serve the change — durability and consistency arriving in the same setting, and the subject of the next topic. The precise one is to carry the commit's LSN forward and let the replica prove it is current enough.

-- on pg-primary, immediately after the order commits
SELECT pg_current_wal_flush_lsn();       -- 6F/AB12C480, stored in the session

-- on pg-replica-a, before the confirmation page trusts it
SELECT pg_last_wal_replay_lsn() >= '6F/AB12C480'::pg_lsn AS fresh_enough;

 fresh_enough
--------------
 f                                       -- so this request goes to the primary

The check costs one cheap round trip and turns staleness into a decision the code makes rather than a race it silently loses. The honest part is what happens on false: fall back to the primary and answer correctly, rather than sleeping in a loop until the replica catches up, which converts a consistency bug into a latency bug and pushes it to a different dashboard. Whichever of the three you pick, pick one per read path and write it down; a path with no rule against its name is using the fourth option.

Which Reads May Be Stale

The classification is short and it is worth doing on paper. Safe on a replica: the analytics dashboard with its 30-second budget, product search, category listings, the courier roster, and every report that reads delivery_events. Unsafe anywhere but the primary: the order confirmation page, a customer's own order list in the minute after checkout, anything an operator is about to act on, and, the category people forget, any read whose result feeds a write. The inventory.on_hand check that decides whether the last box of strawberries can be sold is a read, and putting it on a replica reintroduces at the routing layer a problem Chapter 6 already solved properly at the isolation layer.

Doing this per query, rather than per service or per connection pool, is the actual engineering work of read scaling. The output is a table with two columns, the read path and its tolerable staleness in seconds, and that table has a second life as the source of the alert thresholds. A replica whose lag exceeds the budget published for the queries running on it should leave rotation automatically, and the number that triggers it is the same 30 seconds the dashboard's owner agreed to.

Recovery Conflicts and hot_standby_feedback

The analytics workload creates the conflict described in the previous topic on a daily basis. A 40-minute aggregation over delivery_events holds a snapshot on pg-replica-a; vacuum on pg-primary removes row versions that snapshot can still see; the cleanup record arrives; replay waits out max_standby_streaming_delay and then cancels the report. Raising the delay stops the cancellations and buys them with lag, which is the trade the previous topic left open.

hot_standby_feedback, off by default, is the other lever and it works in the opposite direction. The standby reports its oldest live snapshot upstream, where it shows as backend_xmin in pg_stat_replication and as the slot's xmin in pg_replication_slots, and the primary's vacuum then refuses to remove anything that snapshot needs. The cancellations stop entirely. What has happened is that a cost moved machines: the primary's vacuum horizon is now pinned by a query running on a different host, with all of the consequences Chapter 6 laid out for a long transaction: dead tuples that cannot be collected, tables that grow while their row counts do not. The documentation's defence is fair, that this is no worse than running the report on the primary directly. Moving the query moved the CPU and the I/O; the vacuum horizon stayed on pg-primary.

Routing Mechanics

Routing happens in one of three places. At the proxy: separate pools or backends per target, which is where pgbouncer-01 and tools like HAProxy and pgcat sit, and which Chapter 10 covers as part of pooling. At the driver: libpq accepts several hosts in one connection string and filters them with target_session_attrs. In the application: two connection handles, and every query names the one it wants.

# analytics — any standby, tried in random order
host=pg-replica-a,pg-replica-b port=5432 dbname=cartwheel
  user=cartwheel_analytics target_session_attrs=standby load_balance_hosts=random

# the API's write handle — through the pooler, must be writable
host=pgbouncer-01 port=6432 dbname=cartwheel
  user=cartwheel_app target_session_attrs=read-write

The six values of target_session_attrs divide into two families and confusing them causes a real outage. primary and standby ask about recovery state: is this server replaying WAL or not. read-write and read-only ask whether the session can write, which is recovery state and default_transaction_read_only — so a primary someone put into read-only mode for a migration is rejected by read-write and accepted by primary. The remaining two are any, the default, and prefer-standby, which falls back to any host when no standby answers. load_balance_hosts=random spreads connections instead of stacking them all on the first host that replies. Of the three layers, the application-level form is the least magical: the routing decision appears in the diff, a reviewer can see that the confirmation page reads from the write handle, and nothing has to be inferred from a proxy's configuration during an incident.

What Replicas Do Not Solve

Replicas do not scale writes. Every INSERT still lands on pg-primary, and adding a second replica makes the primary's job marginally larger rather than smaller: it generates the same WAL and now streams it to two walsender processes as well. If the write rate is the ceiling, the answers are the ones from the partitioning chapter and not this one. Read replicas also do nothing for the storage the primary needs, the vacuum it must run, or the locks its own DDL takes.

A replica also competes with itself. Every report running on pg-replica-a is contending for the same CPU and the same I/O that its replay process needs, so a busy analytics replica has worse lag exactly when the analytics are busiest. That is the whole argument for Cartwheel ending this chapter with two of them: pg-replica-a with hot_standby_feedback = on and a generous delay, held to the published 30-second budget and allowed to be slow; pg-replica-b with feedback off, a short delay, no user queries, and a lag measured in hundreds of milliseconds because the only thing it does is stay ready.