Lab: Add replicas safely

In this lab you'll add read replicas to Tiffin for real: decide which reads move, how many replicas you need, how to stop customers seeing missing or stale orders, and how to roll it out without a bad Friday. For each step, decide first, then compare with one way of reasoning it through.

The brief

Tiffin's database team has done the cheap fixes from this module: queries are indexed, menus are cached in Redis, PgBouncer pools connections, and the primary has been scaled up to 64 cores. A synchronous standby in a second zone handles failover.

It's not enough for long. At Friday dinner, the primary sits at 62% CPU, and traffic grows about 8% a month. The goal for this project:

• Get the primary under 40% CPU at dinner, with room to grow for a year.
• No customer ever sees an order they just placed go missing, and order status never goes backwards.
• Losing any one replica doesn't hurt customers.

Here's where the primary's CPU goes at peak, from pg_stat_statements and request tracing:

Step 1: Which reads can move?

Notice the default. Workload E could technically go to a replica with read-your-writes tricks, but it's small, so keeping it on the primary buys simplicity cheaply. Move the big, easy loads first. Don't spend complexity on the small ones.

Step 2: How many replicas?

Plus one more for finance: a smaller, separate reporting replica. Its queries can take minutes, and you can let it fall behind during them (by allowing longer replay delays) without any customer noticing.

Step 3: Read-your-writes and monotonic reads

This is the step that decides whether the project succeeds or causes the "my order vanished" tickets from the replication lag lesson.

async function getOrderStatus(orderId, customerId) {
  const db = replicaFor(customerId);
  let row = await db.oneOrNone('SELECT status, updated_at FROM orders WHERE id = $1', [orderId]);
  if (!row) {
    // just placed: this replica may not have it yet
    row = await primary.oneOrNone('SELECT status, updated_at FROM orders WHERE id = $1', [orderId]);
  }
  return row;
}

Case 1 as a request flow, with the fallback that saves the customer from an empty screen:

%% caption: A brand-new order isn't on the replica yet, so the status read falls back to the primary once.
sequenceDiagram
  autonumber
  participant C as Customer app
  participant S as App server
  participant R as Pinned replica
  participant P as Primary
  C->>S: POST /orders
  S->>P: insert the order
  S-->>C: the order, shown at once
  C->>S: GET /orders/:id/status
  S->>R: read the status
  R-->>S: not found yet
  S->>P: read the status
  P-->>S: placed
  S-->>C: placed

Write these rules down where every engineer will see them. The most common way this design breaks isn't the rules being wrong. It's a new endpoint added six months later that reads from a replica without anyone thinking about lag. That's why the default for new code is the primary.

Step 4: Guardrails for lag

Step 5: Roll it out

Step 6: Failover and backups

Adding replicas changes two things you already had working.

The final design

%% caption: Tiffin after the project. Customer reads spread over three replicas, one per zone, each about 14% CPU at dinner.
flowchart TD
  A["App servers"] --> PB["PgBouncer primary"] --> P[("Primary<br/>writes, checkout, my orders, ~24%")]:::hot
  A -- "customer reads" --> RS[("Replicas 1, 2, 3<br/>zones A, B and C")]:::store
  P -. "replicates" .-> RS
  P -.-> SB[("Sync standby<br/>zone B, failover only")]:::store
  P -.-> RR[("Reporting replica<br/>finance only")]:::store

How the app chooses:

default for new code:          primary
menus, search, reviews:        replicas, any of the three
order status, kitchen polls:   the customer's or restaurant's pinned replica,
                               with primary fallback for new orders and LSN check after writes
checkout, my orders, writes:   primary
finance:                       reporting replica
replica lag > 5 s:             removed from rotation

And the results: the primary drops from 62% to about 24% at dinner, the replicas run at about 14% each, any one replica or a whole zone can fail without customers noticing, and nobody's order vanishes.

Build it on AWS

Tiffin's database is RDS for PostgreSQL, and the "synchronous standby in a second zone" is simply RDS Multi-AZ, which Tiffin has had since launch (see the first version lab). Read replicas are a few clicks on top. One change since the dinner rush lab: when the team scaled up to 64 cores, they also moved from the general-purpose m7g family to the memory-optimised r7g, which has twice the memory per vCPU, so more of the working set stays in cache. The interesting parts are the ones the console doesn't tell you about: what this costs, which metric lies at 4am, and why the managed connection pooler can't pool replicas. Prices are Mumbai (ap-south-1) on-demand list prices as of late 2026; check the AWS pricing calculator before quoting them.

The architecture

%% caption: The replica project on RDS. Writes through RDS Proxy; pinned customer reads through PgBouncer to three replicas.
flowchart TD
  T["ECS tasks"] -- "writes, checkout" --> RP["RDS Proxy"] --> P[("Primary, Multi-AZ<br/>db.r7g.16xlarge")]:::hot
  T -- "pinned customer reads" --> PB["PgBouncer on ECS"] --> RS[("3 read replicas<br/>one per zone")]:::store
  P -. "asynchronous" .-> RS
  P -.-> RR[("Reporting replica<br/>db.r7g.4xlarge")]:::store
  HB["Heartbeat writer"] -.-> P

Argument 1: what three full copies cost, and the other shapes

Replicas are billed as ordinary database instances, while the replication traffic itself is free within a Region:

Primary, Multi-AZ, db.r7g.16xlarge:   $17.408/hour  = ~$12,700 a month
3 replicas, same class:          3 x $8.704/hour    = ~$19,100
Reporting replica, r7g.4xlarge:       $2.176/hour   = ~$1,600
Storage, 800 GB gp3: Multi-AZ primary plus 4 replicas = ~$630

More than half the database bill is now replicas, because RDS read scaling means copying the whole database onto more machines. AWS sells two other shapes, and both are worth knowing even though this project doesn't pick them:

• A Multi-AZ DB cluster has two readable standbys behind a reader endpoint. That gives fewer readers than the three this lab needs, and the readers are also the failover targets, which is exactly what Step 6 chose to avoid ("keep the standby free of read traffic").
• Aurora PostgreSQL keeps one shared storage volume under all its replicas, so a replica doesn't need its own full copy on its own disk, and a reader endpoint spreads connections. It changes cost and failover more than any setting here. The course moves Tiffin to Aurora in Module 9, when storage growth forces the question. For this project, RDS replicas are a configuration change, not a migration, and that's the right size of risk.

Argument 2: the lag guardrail that fires at 4am

Step 4 removes a replica from rotation when its lag passes 5 seconds. The obvious way to measure lag on RDS is the ReplicaLag metric in CloudWatch. It has a trap that AWS documents: for PostgreSQL, the value is "now minus the time of the last replayed commit". So when the primary commits nothing, which can happen at 4am, the reported lag climbs for up to five minutes until the next log segment switches, even though the replica is fully caught up. A rule of "remove above 5 seconds" built on that metric evicts every replica overnight and pages someone about it.

The fix is to give replicas something to replay. Run a tiny heartbeat job that updates one row on the primary every second with the current time. Each replica's lag is then "now minus the heartbeat it can see", which is honest whether Tiffin is busy or asleep. For read-your-writes, the LSN check in Step 3 still uses PostgreSQL's own functions (pg_last_wal_replay_lsn() on the replica), which work unchanged on RDS.

There's no reader endpoint for plain RDS replicas either, so replicaFor(customerId) hashes over a list of replica endpoints kept in configuration. Removing a lagging replica means taking it out of that list (or out of PgBouncer's pool), and the customers pinned to it re-hash to the others, exactly as Step 4 planned.

Argument 3: who pools the replica connections?

RDS Proxy is the managed answer to PgBouncer, and it's good at the primary's problem: it holds a small pool of database connections for hundreds of app processes, and it handles failover without each task reconnecting on its own. In Mumbai it costs $0.017 per vCPU-hour of the database it fronts, about $800 a month for a 64-vCPU primary.

But it doesn't cover this lab's replicas. AWS documents that for RDS instances in a replication setup, a proxy can attach only to the writer, not to a read replica. Reader endpoints exist only for Aurora and Multi-AZ DB clusters. So the split is:

• Primary: RDS Proxy. Checkout and writes are where connection storms and failovers hurt most, and someone else runs it.
• Replicas: PgBouncer, which the team already runs, as a small ECS service with one pool per replica. It costs a few tens of dollars a month in tasks, plus some care when a replica is removed from the list.

One more number: tasks reading from replicas in other zones pay for cross-zone data transfer, $0.01 per GB each way. The moved reads are 5,500 a second at the dinner peak; averaged over the day, say about 2,200 a second. At about 2 KB per response that's roughly 11 TB a month, two thirds of it crossing zones, which is about $150 a month. That's not worth giving up the customer-pinned design for zone affinity.

Argument 4: failover drills and backups, re-read for RDS

Two tools from Step 6 go away. Patroni isn't needed, because RDS runs the failover: it promotes the standby and moves the database's DNS name, typically within 60 to 120 seconds. The monthly drill becomes "reboot with failover" on the primary, at a quiet hour, then checking that every replica's heartbeat lag returns to normal and that the app reconnected through RDS Proxy.

pgBackRest isn't needed either. RDS takes the automated backups, and Multi-AZ exists partly so that backups don't cause latency spikes on the primary. Point-in-time restore works on the primary, not on read replicas, so the weekly restore test restores the primary to a new instance, as before. One quota to watch: Mumbai's default limit is 20 RDS instances per account. This project uses five, but staging copies and restore tests count too.

What it costs

At about 200,000 orders a day, that's roughly 0.6 cents per order. Replicas are the biggest line, which is the subject of the exercise.

The same design on GCP and Azure

The traps carry over: check how each platform reports replica lag on an idle database, and whether its managed pooler can reach replicas.

Four replicas:      24 / 4 + 1 = 7 cores of 32 = ~22% each
One fails:          24 / 3 + 1 = 9 cores of 32 = ~28% each