Read replicas

Tiffin has done the cheap things. Slow queries are indexed, menus come from Redis, connections go through PgBouncer, and the database has been scaled up to 64 cores. It worked: dinner p99 is back under 300 ms.

But Tiffin keeps growing about 8% a month, and the database is already at 62% CPU at 8pm. The next size up is very expensive, and after that there isn't much of a next size. Meanwhile, the first lesson of this module showed that 80% of Tiffin's queries are reads. Reads don't change anything. Any copy of the data could answer them.

So make copies.

A read replica is a copy of the database that continuously receives every change from the primary and can answer read-only queries. All writes still go to the primary.

How a replica stays in sync

Every time Postgres changes data, it first writes a record of the change to its write-ahead log (WAL). This log exists so the database can recover after a crash (Module 19 explains how). It also turns out to be the perfect thing to send to a copy.

1. The app sends a write to the primary, such as INSERT INTO orders ....
2. The primary applies it and records it in the WAL.
3. The WAL records stream to each replica over the network.
4. Each replica replays them, in the same order, to its own copy.

By default this is asynchronous: the primary tells the app "done" without waiting for the replicas. That keeps writes fast, but it means a replica is always a little behind, usually by milliseconds. Sometimes by much more. That gap gets its own lesson next, because it causes some of the strangest bugs in this module. Module 13 compares this with synchronous replication in depth.

MySQL works on the same idea with its binary log, though that log usually records the changed rows rather than changed disk pages, so a MySQL replica re-applies each row change itself. Most managed databases let you add a replica with a few clicks.

What replicas give you, and what they don't

The writes row is the one people forget. Every replica must replay every write the primary makes, so writes don't spread out. Ten replicas means ten machines all doing the same write work. Replicas are a way to scale reads only. Scaling writes needs partitioning and sharding (Modules 9 and 14), which is much harder, and why you want to know your read/write mix early.

How many replicas?

A rough sizing for Tiffin. Writes are fewer than reads but each costs more, so say the primary's 62% CPU at dinner splits like this:

writes:                       ~14% of primary CPU
reads that must stay on it:   ~11%
reads that can move:          ~37%

Move the movable reads to replicas, and the primary drops to about 25%. That leaves plenty of room for write growth.

Each replica, sized like the primary, still replays all writes. Replay is much cheaper than running the original queries, since there's no query planning, locking or searching for rows. In Postgres it's also done by a single process, so it can't use more than about one core: call it 2% of a 64-core machine. With two replicas splitting 37% of reads:

each replica: 2% replay + 18.5% reads ≈ 20%

That single replay process is also a ceiling worth knowing. A primary that writes hard enough on many cores can produce WAL faster than one process can replay it, and then the replica falls further and further behind. (MySQL replicas can apply changes with several threads in parallel.)

That's a comfortable start. As reads grow, you add a third replica, then a fourth. Two replicas also mean one can fail without the other being overloaded, the same N+1 thinking as app servers in Module 4.

Sending reads to replicas

The database doesn't decide which queries go where. Something in front of it has to. There are three common ways.

In the app, explicitly. The app keeps two pools, one for the primary and one for the replicas, and each query picks:

const primary  = new Pool({ host: 'pgbouncer-primary.internal',  max: 10 });
const replicas = new Pool({ host: 'pgbouncer-replicas.internal', max: 10 });

// menu: a few seconds stale is fine
async function getMenu(restaurantId) {
  return replicas.query('SELECT ... FROM menu_items WHERE restaurant_id = $1', [restaurantId]);
}

// checkout: price and stock must be current
async function getCartForCheckout(cartId) {
  return primary.query('SELECT ... FROM cart_items JOIN menu_items ... WHERE cart_id = $1', [cartId]);
}

In a proxy. Some database proxies look at each statement and send SELECTs to replicas and everything else to the primary automatically. It's convenient, but the proxy can't know which reads need fresh data. A SELECT right after placing an order looks exactly like any other SELECT.

In the driver. Some drivers accept a list of hosts and can prefer a read-only one. Postgres's own client library, libpq, can do this with its target_session_attrs setting. This helps with finding a replica, but you still decide which queries use it.

Explicit routing in the app is the most work, and also the safest, because the person writing the code is the one who knows whether a read can be stale. Most mature teams end up there.

Which reads can go to a replica?

The question to ask for each read is simple:

"If this data were a second or two out of date, would anyone be hurt or confused?"

For Tiffin:

A useful default: new code reads from the primary unless someone has decided it's safe to read from a replica. Moving a query to a replica is then a deliberate choice, made by someone who thought about staleness, rather than an accident.

A replica just for heavy work

That finance report from the bottleneck lesson, scanning millions of rows, is a perfect job for its own replica. Customers never touch it, so a slow report can't hurt dinner.

There's one surprise to know about. In Postgres, a long query on a replica can clash with changes arriving from the primary. Say the report is reading rows that the primary has since deleted and cleaned up. The replica must either delay applying the cleanup, which makes it fall behind, or cancel the report. By default, after waiting 30 seconds (max_standby_streaming_delay), it cancels the query with an error like "canceling statement due to conflict with recovery". Raising that setting trades cancellations for lag. Turning on hot_standby_feedback avoids most cancellations a different way: the replica asks the primary to hold off cleaning up rows its queries still need, at the cost of extra bloat on the primary. For a reporting replica, letting it fall behind is usually fine. For a customer-facing replica, it isn't.

When a replica is unhealthy

Replicas fail like any other server, so treat them the same way:

• Spread reads across replicas through a load balancer or pooler that health-checks them.
• If one replica dies, the others take its share. That's why you size for N+1.
• Be careful with "if all replicas are down, fall back to the primary". It sounds safe, but sending 37% more load to a primary sized for 25% can take down the one machine that handles every write. Often it's better to serve cached or degraded results for a few minutes.

Tiffin with replicas

%% caption: Two replicas for customer reads, one for reports. Every write still goes to the primary.
flowchart TD
  A["App servers"] --> PBP["PgBouncer primary"] --> P[("Primary<br/>writes, checkout, payments")]:::hot
  A --> PBR["PgBouncer replicas"] -- "customer reads" --> RS[("Replicas 1 and 2<br/>customer reads")]:::store
  P -. "replicates" .-> RS
  P -.-> RR[("Reporting replica<br/>finance and ops only")]:::store

The primary drops from 62% to about 25% CPU at dinner. Reads can now grow by adding replicas. And as a bonus, Tiffin now has up-to-date copies of the database on other machines, which matters a great deal when the primary dies, as it will in two lessons' time.