Reads That Scale — Replicas and Stale Data

The organizer's sales report and the public event list are read a hundred times for every write, and pg-primary should not spend its capacity on them. pg-replica-a is a streaming copy that applies the primary's write-ahead log as it arrives, lagging by 20 milliseconds on an ordinary evening and by seconds during a large migration or a backfill. Routing reads to it takes the report load off the primary, and it means the service must decide, per read, whether stale is acceptable. The buyer who places an order and does not see it on the next page load is the cost of getting that decision wrong, and Stagedoor's support queue had that ticket, phrased as "my payment vanished."

The engine's side of replication, how the WAL streams, how the replica is promoted, is PostgreSQL Deep Dive's subject. The application's side is smaller and sharper: two pools, a decision written beside every read, a rule for the reads that must see the writer's own writes, a number for how far behind the replica is, and a plan for the minute the replica is not there.

Two Pools, One Decision

Stagedoor's storage layer holds two pools from Topic 31, one connected to pg-primary and one to pg-replica-a, and every read in the layer names which one it uses. The choice is per operation, not per table: the orders table is read on the primary when a buyer looks up their own order and on the replica when an organizer's report sums it. A read that names no pool is a compile error in the storage layer's own conventions, which is a stronger guarantee than a comment.

async def get_order(svc, public_id) -> Order | None:
    # primary: the buyer may have placed this order 200 ms ago
    async with svc.primary.connection() as conn:
        return await svc.orders.by_public_id(conn, public_id)

async def sales_report(svc, organizer_id, event_id) -> Report:
    # replica: 30 seconds stale is fine; one snapshot across the five queries is not optional
    async with svc.replica.connection() as conn, conn.transaction():
        await conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY")
        totals = [await svc.orders.section_total(conn, event_id, s) for s in SECTIONS]
        return Report.from_totals(totals)

The order lookup goes to the primary, and the comment says why: the buyer who is looking may have written the row a moment ago. The report goes to the replica, and its comment says two things: that half a minute of staleness is acceptable for a sales chart, and that the five section queries must see one snapshot, so the transaction is Repeatable Read and read-only. That second point is Topic 32's rule applied on the replica. Without it, the five queries run at five slightly different replay positions, and a sale that replays between the second and the third is in some totals and not in others. The replica is a Postgres like any other; it gives one snapshot when asked for one.

What Lag Means

The replica is behind by whatever WAL it has received and not yet applied. On an ordinary evening that is 20 milliseconds: a row committed on the primary at 21:00:00.000 is visible on the replica at 21:00:00.020. During Topic 35's backfill, or a large index build, the primary produces WAL faster than the replica applies it, and the delay grows to seconds; during the one-statement backfill Stagedoor once ran, it grew to an hour. A read on the replica sees the world as of that delay ago, and the delay is not constant, so "the replica is 20 milliseconds behind" is a statement about the median and not a promise.

The service can ask. On the replica, pg_last_wal_replay_lsn() returns the position it has applied up to and pg_last_xact_replay_timestamp() the commit time of the last transaction it replayed; on the primary, pg_current_wal_lsn() is the position it has written. The difference between the two positions is the lag in bytes and the difference between now and the replay timestamp is the lag in time, and Stagedoor's worker samples both every 10 seconds into a metric. Chapter 13 puts the metric on the dashboard; the alert on it fires at 30 seconds, before the report is wrong enough for an organizer to notice, and long before the ticket arrives.

Read-Your-Writes

The buyer places an order, the handler commits it on the primary and answers 201, and the browser loads the orders page 200 milliseconds later. If that page reads the replica, and the replica is 300 milliseconds behind because a backfill is running, the order is not there. The buyer sees an empty list, a receipt in their inbox, and a charge on their card, and files the ticket. Nothing failed; the read was answered correctly from a copy that was correct 300 milliseconds ago.

The property the buyer expects is read-your-writes: a client sees its own writes, whatever anyone else sees. The service provides it by routing. The simplest form records the time of the user's last write in the session from Chapter 5, and for a window after it, 5 seconds in Stagedoor, every read for that user goes to the primary. The window is longer than any lag the alert would tolerate, and the cost is a few extra primary reads per buyer per purchase, which is nothing. The precise form carries the write's position instead of its time: the handler reads pg_current_wal_lsn() after the commit and returns it to the client in a header, the client sends it back, and a replica read first checks that pg_last_wal_replay_lsn() has reached it, waiting briefly or falling back to the primary if not. Stagedoor uses the time window, because its lag is measured and the window is cheap, and it keeps the position form in reserve for the day the replica count grows.

What May Be Stale

The decision is written beside each read, and it is a decision about the reader, not the table. The public event list may be 2 seconds stale: a new event appearing on the home page 2 seconds after the organizer published it is unnoticeable. The organizer's sales chart may be 30 seconds stale, and the organizer knows it refreshes. The buyer's own orders may not be stale at all in the seconds after a purchase, which is what the routing above is for. And the seat map may not be stale ever, because a buyer who holds 14C must see it held on the next load, and another buyer must see it held too. That read does not go to the replica; it goes to Redis in Chapter 9, where the cache is invalidated at the moment of the write, and the primary is the fallback behind it.

One class of read is never sent to the replica whatever its tolerance: a read inside a write transaction. The unit of work in Topic 32 reads the holds and inserts the order in one decision, and a read of the holds from a copy that is 300 milliseconds old is a decision made on stale data followed by a write based on it, which is 14C's race with a replica in the gap instead of a concurrent request. A transaction that writes reads from the primary, in the same transaction, on the same connection. The replica is for reads whose result is shown, not for reads whose result is acted on.

Replica as Failover Is a Different Thing

The replica that serves reports is also the copy that becomes the primary when pg-primary fails, and promoting it is PostgreSQL Deep Dive's subject, in its chapter on failover. The service's part is small and non-negotiable. Its pools must survive the change: the primary pool's connections now point at a host that is gone or demoted, and Topic 31's lifetime and checkout validation are what make the pool reconnect to the promoted host without a restart. And a promoted replica is the primary for writes from that moment, so the address the primary pool resolves must be a name that failover moves, not the host's own; the service that hard-codes pg-primary keeps writing to a dead host after the cluster has already recovered.

When the Replica Is Gone

A replica read whose pool cannot connect is not a 500. The read had a perfectly good primary to fall back to, and a sales report answered from the primary is a correct report that cost the primary 40 milliseconds it would rather not have spent. The storage layer's replica read catches the connection failure, logs one line, increments a metric, and runs the same query on the primary pool. Chapter 7's circuit breaker is the shape of the fallback: after a few failures the layer stops trying the replica for a while and goes straight to the primary, so that a dead replica does not add a connection timeout to every report.

The primary can carry the read load for a while. At 2,600 seat-map reads a second the cache absorbs the worst of it, and the reports and the event list add perhaps 200 queries a second, which a primary sized for the write path handles with room to spare for an hour. The metric is what makes the hour safe: the alert on replica-fallback reads fires before the on-call engineer's phone shows a latency alert, and the message says "the replica is down" rather than "something is slow," which is the difference between a five-minute fix and a two-hour hunt.