Episode 77 · Copies for reads, splits for writes

Replication and sharding

Tuesday, 10:00: PostgreSQL's primary is at 80% CPU on Sunday evenings and climbing 5% a month, 94% of its queries are reads, and its disk will be full in eight months.

Copy your data to handle more reads and survive failures. Split your data to handle more writes. Mixing them up is expensive.

30 min advancedLeader/follower replicationSynchronous vs asynchronous replicationReplication lag and read-your-writesRead-your-writes consistencyHash vs range partitioningChoosing a shard key

Problem
One PostgreSQL machine holds every hotel, room and booking. Reads are at 90% CPU, writes are climbing and the disk will be full in eight months.
Why the simple system fails
A bigger machine buys time but has a ceiling, stays a single point of failure, and every read and write still competes for the same CPU, memory and disk.
Engineering concept
Replication copies the same data to several machines (more read capacity, survives failures). Sharding splits different data across machines (more write capacity and storage).
Technology
Leader/follower streaming replication, read replicas, sync/async/semi-sync modes, hash/range/consistent-hash partitioning, shard routers, online resharding tools.
Trade-offs
Replicas lag, so reads can be stale. Shards make cross-shard queries, joins and transactions expensive, and a bad shard key is very costly to change later.

The problem

2 min

Tuesday, 10:00. Two weeks after the TV ad.

Omar · On-call SRE
Postgres primary (64 vCPU): 80% CPU at Sunday peak, +5% a month. 94% of queries are reads.
Omar · On-call SRE
Disk: 6 of 8 TB, growing about 250 GB a month. Full in about 8 months.
Lina · Senior engineer, your mentor
No bigger machine is worth buying. You lead this one.

The big idea

4 min

Copy or split?

The big idea

Copy to scale reads and survive failures. Split to scale writes and storage. Name the bottleneck before the tool, and shard last.

600 writes a second is comfortable for one leader. Reads are the load, so copies come first; archiving fixes the disk. Sharding solves a write problem we don't have yet.

How it works

5 min

What happens with read replicas

1
The leader writes
Replication keeps copies of the same data on several machines. One leader takes every INSERT and UPDATE and records each change in its log.
2
Changes stream out
Each applies the log. Replication lag is how far behind it is: usually 50–300 ms, several seconds during the nightly price import.
3
Reads spread out
Search, hotel pages and owner reports read from 3 replicas. The primary drops from 80% to about 35% CPU.
4
Except your own writes
A guest who just booked reads from the leader for a few seconds, so they always see their own change.
5
Disk, separately
Bookings that checked out before 2023 (~2.4 TB) move to object storage: 6 TB → 3.6 TB. Years of room.
6
Later: sharding
splits data across machines by a (like hotel_id), so each holds and writes only part. It's the tool for writes and size, not reads.

Watch a decision being made

4 min

Worked example

You add read replicas safely

Take reads off the 80%-CPU primary without guests ever missing their own bookings, and without stale data deciding anything important. Lina checks the routing.

1Classify every query
Writes → leader. Reads that may lag (search, hotel pages, owner reports) → replicas. Reads of fresh writes or decisions (My bookings after paying, checkout's availability check) → leader.
Why: The routing rule is a product decision: where is a second of staleness harmless, and where does it break a promise?
2Route in one place
One data-access layer picks the connection; no query chooses a host by itself. Owner reports get their own smaller replica.
ts
```
const conn = intent === "write" || pinnedToLeader(guestId)
  ? leader
  : pickHealthyReplica({ maxLagMs: 1000 });
```
Why: One place to change rules and check lag. Heavy reports stay away from guests' searches.
3Guarantee read-your-writes, guard against lag
After a guest writes, pin their reads to the leader for 5 s. Skip any replica more than 1 s behind; if all lag, fall back to the leader and alert Omar.
Why: Lina: "lag is usually 300 ms but sometimes seconds; 5 s covers almost all of it, and only for this guest."
4Measure
Primary CPU at Sunday peak: 80% → 35%. Replica lag p99: 280 ms. "Empty My bookings" tickets: zero.
Why: Confirms the load moved and the pinning works.

In real life

3 min

Shard early, or replicate and split by function first?

Instagram: sharded PostgreSQL by user

Has publicly described splitting its PostgreSQL data into thousands of logical shards by user ID
Driven by enormous write volume from photos, likes and follows
Every query must know which shard to ask

Worth it when write volume outgrows any single leader.

GitHub: replicas and functional splits first

Has publicly described scaling MySQL with read replicas and moving groups of tables to separate clusters
Kept the data model mostly intact for years
Sharded specific huge tables later

Worth it while reads dominate and one leader takes the writes.

What goes wrong

3 min

Letting a replica decide something irreversible

A lagging copy can show a room as free that was just sold.

Instead

Decisions read from the leader, with a lock.

Treating replicas as backups

A DELETE without WHERE replays on every replica in milliseconds.

Instead

Real backups with , and tested restores.

Sharding with hash(key) mod N and planning to "just add a shard"

Going from 4 to 5 shards moves ~80% of rows.

Instead

Consistent hashing, or many logical shards on fewer machines.

Trade-offs

3 min

Future shard key	Single-shard	Costs
guest_id	My trips, guest data deletion (~6% of queries)	Availability and prices ask every shard; a hotel's rooms scattered
hotel_id	Availability, prices, front desk, the booking lock (80%+)	My trips needs a guest index; giant chains make hot shards

Friday, 11:00. Writing down the future shard key.

Omar · On-call SRE
Shard by guest_id. My trips is one shard, and deleting a guest's data is all in one place.
Lina · Senior engineer, your mentor
My trips is 6% of queries. Availability, prices and the front-desk list are over 80%, and all name a hotel.
Omar · On-call SRE
Then My trips asks every shard.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Which wall you're hitting: 94% reads means replicas, not sharding. An agent asked to "scale the database" may jump to the most impressive answer.
Which pages may read stale data and which must read the primary (My bookings right after paying). A product decision, not a default.
If you ever shard: the shard key, chosen from the hottest queries. Never left to the agent.

Tell Claude Code

Our PostgreSQL primary is at 80% CPU at peak and 94% of queries are reads. We're adding 3 async read replicas. Plan first, no code yet: list every query path in booking-api and classify each as replica-safe or primary-only. Replica-safe: search, hotel pages, owner reports. Primary-only: anything a guest reads right after writing, starting with My bookings after payment, and everything in the booking transaction. Route reads through one helper, never a global default. Add a test that books and immediately reads My bookings with 400 ms simulated lag, expecting to see it.

Check before you accept

For each "replica-safe" path: would a guest be hurt by data 2 seconds old?
The payment and booking flow never reads a replica, not even to "check availability".
The lag test fails if you switch My bookings to the replica; the primary's CPU drops toward ~35%.

Red flags

It sends all GET requests to replicas by default.
It proposes sharding by hotel_id to fix a read-heavy load.
It calls the replicas "backups".

Check yourself

2 min

Q1/3Decision

Your booking database runs at 80% CPU at peak on 64 vCPU. 94% of queries are SELECTs for hotel pages and search; writes are ~600/s, and the 6 TB of data drops to ~3.6 TB once bookings older than 2023 are archived. What is the best next step?

How confident are you?

Next episode: investors want a plan for 10× growth and two new continents within a year. You lead it, start to finish.

Finished?

Mark it complete to update your skill map.

Next: The scale journey: 100 to 100 million users

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Interactive simulation, Visual diagram, Analogy, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

If we ever shard: choosing the key

You lead the replica rollout; Lina checks the routing rules.

Worked example

You add read replicas safely

Take reads off the 80%-CPU primary without guests ever missing their own bookings, and without stale data deciding anything important.

1Classify every query
Three groups: writes (bookings, payments, price updates) → leader; reads that may lag (search, hotel pages, owner reports) → replicas; reads of your own fresh writes (My bookings after paying, checkout's availability check) → leader.
Why: The routing rule is a product decision: where is a second of staleness harmless, and where does it break a promise?
2Add replicas sized by role
Two full-size replicas for search and hotel pages, one smaller for owner reports (slow, heavy queries kept away from guests).
Why: Separating heavy reporting from guest traffic stops one owner's giant report from slowing everyone's search.
3Route in one place
A small data-access layer picks the connection: db.write, db.readLagOk, db.readFresh. No query chooses a host by itself.
ts
```
const conn = intent === "write" || pinnedToLeader(guestId)
  ? leader
  : pickHealthyReplica({ maxLagMs: 1000 });
```
Why: One place to change rules, and one place to add a lag check.
4Guarantee read-your-writes
After a guest writes, store pinnedUntil = now + 5 s in their session; their reads go to the leader until then.
Why: Lina: "lag is usually 300 ms but sometimes seconds; 5 s covers almost all of it, and it's only for this guest."
5Guard against big lag
Monitor replay_lag on each replica. Above 1 s, the router skips that replica; if all lag, reads fall back to the leader and Omar is alerted.
sql
```
SELECT application_name, replay_lag FROM pg_stat_replication;
```
Why: During the nightly price import, lag can reach seconds. Search showing prices from ten seconds ago is fine; from ten minutes ago is not.
6Measure
Primary CPU at Sunday peak: 80% → 35%. Replica lag p99: 280 ms. "Empty My bookings" tickets: zero.
Why: Re-measuring confirms the load moved and that the lag guard and pinning actually work.

Strategy	How a row is placed	Good at	Bad at
Hash mod N	hash(key) % N	Even spread of many keys	Changing N moves almost every row; no range scans
Range	key in [lo, hi) → shard	Range scans (dates, alphabetical)	Sequential keys (timestamps, auto-increment ids) make the last shard hot
Consistent hashing	Key and shards on a hash ring, with virtual nodes	Adding a shard moves only ~1/N of keys	Still no range scans; a single huge key stays hot
Directory (lookup table)	Explicit map key → shard	Moving one tenant; isolating giants	The directory itself must be fast and highly available

Real systems often combine them: consistent hashing by default, directory overrides for the largest tenants.

Interactive simulation

Visual diagram

Booking API

Shard router

Shard A leader

Shard A follower

Shard A sync standby

Shard B leader

Shard B follower

Click a component

Each shard is a leader with its own followers. Click a component, or run the walkthrough.

Analogy

The hotel's guest registerA database

The hotel's guest registerOne register, one clerkA databaseA single database
The hotel's guest registerPhotocopies for the concierge and restaurantA databaseReplication: read copies
The hotel's guest registerA guest not on the copy yetA databaseReplication lag
The hotel's guest registerOne register per group of floorsA databaseSharding by a key
The hotel's guest registerFinding every "Dupont": ask all clerksA databaseA query across all shards
The hotel's guest registerA 300-person conference on one floorA databaseA hot shard

Where the analogy breaks: photocopies are handed out every few minutes. Database copies usually trail by fractions of a second, but under load that gap can grow to seconds, right when it hurts most.

Check your understanding

Q1/4Reasoning

With asynchronous replication, if the leader crashes and a follower is promoted, every booking that was confirmed to a guest is guaranteed to exist on the new leader.

How confident are you?

Practice ladder

Step 1 · Trace

Route each query

Scenario Warm-up

With the new router (writes → leader; readLagOk → a replica with lag < 1 s; a guest's reads → leader for 5 s after they write), decide where each goes and what the guest sees.

Aiko searches hotels in Lisbon. Replica lag: 200 ms.
Aiko books room 812; 2 s later her browser loads My bookings.
Kenji opens the hotel page for room 812's hotel 300 ms after Aiko's booking.
At checkout, Kenji's request checks that room 812 is free for the same night.
During the nightly import, all replicas lag 4 s; a guest searches.

Step 2 · Complete

How many rows move?

Estimation Practice

Lina's note on placing rows, with gaps. Bookings table: 400 million rows.

With hash(id) mod 4, a row stays on the same shard after going to mod 5 only if hash mod 4 = hash mod 5, which happens for about 1 in ___ values. So about ___% of rows move: ___ million.
With consistent hashing (many virtual nodes), adding a 5th shard moves about ___ of the rows: ___ million.
A third approach: 256 logical shards mapped onto 4 machines. Adding a 5th machine moves ___ logical shards, about ___% of rows.

Step 3 · Solve

The owner report that slows search

Debugging Practice

A week after the replicas: on Monday mornings, search p95 on replica 1 jumps from 120 ms to 900 ms. Data:

Router sends search, hotel pages and owner reports to "any healthy replica".
Monday 08:00–10:00, hundreds of owners run the weekly revenue report: a heavy aggregation, ~8 s each.
Replica 1's CPU: 95% on Monday mornings; replica 3 (the small one) is mostly idle because it's "smaller".
Lag on replica 1 also climbs to 2–3 s.

Explain and fix.

Step 4 · Decide

Synchronous or asynchronous standby?

Decision Stretch

StayHub's failover standby (in another zone) is currently synchronous: each commit waits for it, adding ~2 ms. Karim proposes making it asynchronous to save those 2 ms and to stop writes from blocking if the standby is down.

Facts: ~600 bookings writes/s at peak; a confirmed booking must never be lost (a guest has an email saying "confirmed"); the standby was unavailable for 3 minutes once last quarter, and writes blocked until Omar intervened.

Write a short decision: "We chose X over Y because…, accepting the risk of…".

Explain it yourself

Explain to a teammate the difference between replication and sharding, and how you'd decide which one your overloaded database needs.

0 words