Skip to main content
CodePeetCodePeet
Data Systems at Scale

The Read-Scaling Playbook

Introduction

The dashboard is red. The orders page is taking two seconds. Somebody in the channel types the sentence that has been typed in every engineering channel since databases were invented:

"The database can't keep up. Let's add a read replica."

Everyone nods. It sounds right. The database is slow, so: more database.

I want to show you what happens when you do that — not as a thought experiment, but measured, on a real Postgres with two million rows, with a real streaming replica running on a real second machine.

Here is the answer, up front, because it is the entire lesson:

the slow query, on the primary :  48.3 ms
the SAME query, on a dedicated read replica :  56.4 ms

You added a whole second machine, and the query got slower.

That is not a fluke, and it is not a misconfiguration. It is what a read replica is. And the reason nearly everybody reaches for one anyway is that they have quietly confused two completely different problems.

The read-scaling ladder, drawn as four stacked rungs, with a red arrow running down the right-hand side labelled MORE COPIES OF THE TRUTH — which is the axis the ladder is actually ordered by. Rung one, in green, is INDEX: it buys a new path to the same rows, it creates ZERO copies, and nobody has to keep anything in sync. Rung two, in blue, is REPLICAS: it buys more lanes, not a faster car; it creates N copies, and the DATABASE keeps them in sync. Rung three, in amber, is DENORMALIZE: it buys the answer pre-joined; it creates more copies, inside your own schema, and YOU keep them in sync. Rung four, in red, is CACHE: it buys the answer pre-computed; it creates a copy in another system entirely, and NOBODY keeps it in sync. A banner across the foot states the measured punchline: a replica does not make a slow query fast — measured, the same query went from 48.3 milliseconds on the primary to 56.4 milliseconds on a dedicated replica, while a single index took it to 0.17 milliseconds.

The Only Question That Matters First

Before you touch anything, answer this:

Is your database slow, or is it busy?

They are not the same problem. They do not have the same fix. And almost every read-scaling disaster I have seen started with someone answering the wrong one.

  • SLOW means one query takes too long. One user, one request, an idle box — and it still takes 48 ms. The machine is not out of capacity; it is doing too much work per query.
  • BUSY means the box is out of capacity. Each individual query is fine — a millisecond or two — but there are forty thousand of them a second and the CPU is pinned at 100%. Everything is queueing behind everything else.

You already have the vocabulary for this, from Latency vs Throughput vs Bandwidth in Container 1. Slow is a latency problem. Busy is a throughput problem. And that lesson gave you the metaphor that settles this entire argument — the highway:

"More lanes is pure throughput — it does nothing for the trip time of any single car."

So here is what a read replica actually is:

A read replica is MORE LANES.

It does not make your car go faster.

A replica runs the same query, over the same rows, on the same size box. There is no mechanism by which it could be faster. It was never going to be. And yet a slow query is precisely the moment everybody reaches for one — because "the database is slow" makes "more database" feel like the fix.

It is a category error, and it has a price tag.

The diagnostic that decides which rung of the read-scaling ladder you need, drawn as two panels. The left panel, in red, is headed SLOW: one query takes too long. A single database answers in 48.3 milliseconds; the same query on a dedicated read replica answers in 56.4 milliseconds. The caption reads: a whole second machine, and it got SLOWER. The fix, in green, is ONE INDEX — 0.17 milliseconds, on the machine you already own. The right panel, in blue, is headed BUSY: the box is out of capacity. Here replicas add more lanes, and the caption reads: THIS is what a replica is actually for. But index first: one index took a single box from 28.8 to 16,772 transactions per second — 583 times more per box — so every replica you add afterwards is worth 583 times more. A dark banner across the foot lands the comparison: one machine with an index serves 16,772 transactions per second, while two machines without one serve 51 — the index beats doubling your hardware by 327 times.

What the Slow Query Actually Costs You (and What the Replica Doesn't Fix)

Two million orders. 173 MB. One perfectly ordinary query — how much has customer 12345 spent?

SELECT count(*), sum(total) FROM orders WHERE customer_id = 12345;

There is no index on customer_id. Ask Postgres what it is doing:

Parallel Seq Scan on orders   (actual time=9.367..49.674 rows=4 loops=3)
  Buffers: shared hit=16667          <-- it reads the ENTIRE 173 MB table
Execution Time: 57.041 ms            <-- ...to return 4 rows.

Sixteen thousand six hundred and sixty-seven pages, to find four rows. That is the problem. Not the machine. Not the hardware. Not the number of servers. The query is doing a hundred thousand times more work than it needs to, and it will do exactly that much work on any machine you run it on.

Which is why the replica changes nothing:

PRIMARY  (5 runs, ms):  48.3  45.5  50.9  44.9  51.2      -> median 48.3
REPLICA  (5 runs, ms):  67.7  57.9  56.4  53.2  54.0      -> median 56.4

A whole second machine. Dedicated to this read. And the query got SLOWER.

(Slightly slower, in fact — a colder cache and the replay work the replica is also doing. But the point isn't the 8 ms. The point is that it was never going to be faster, and any number you hoped for was a hope about a mechanism that does not exist.)

Now the right rung. One line:

CREATE INDEX idx_orders_customer ON orders(customer_id);
Bitmap Index Scan on idx_orders_customer  (actual time=0.025..0.025 rows=13)
  Buffers: shared hit=13 read=3      <-- 16 pages. Not 16,667.
Execution Time: 0.168 ms             <-- 48.3 ms  ->  0.17 ms

index size: 18 MB on a 173 MB table.

Roughly a thousand times less work, on the machine you already own, for 18 MB and one line of SQL.

You did not need a second machine. You needed to stop reading the whole table.

…And Now the Trap Runs the Other Way

If the lesson stopped there it would be doing you a disservice, because there is an equal and opposite mistake — and it is the one that gets made on Black Friday.

Sometimes the query is fine and the box really is full. Forty thousand requests a second, CPU pinned, everything queueing. Here, an index does not conjure capacity out of thin air. This is the problem a replica exists to solve. This is what more lanes are for.

But look at what happens when you measure both rungs against actual load — pgbench, 8 concurrent clients, 10 seconds:

NO INDEX:   primary   28.8 tps    |   replica   22.5 tps
            -> two machines. ~51 tps between them. Both crawling.

+ INDEX:    primary   16,772 tps  |   replica   15,531 tps

ONE machine with an index does 16,772 tps.

TWO machines without one do 51.

The index beats doubling your hardware by about 327×.

And now the number that actually decides the order of the ladder. The index took a single box from 28.8 tps to 16,772 tps — 583× more capacity per machine.

⭐ An index makes every replica you buy afterwards 583 times more valuable.

Climb the rungs out of order and you are not just wasting money — you are buying hardware to run a bad query in parallel. To serve 40,000 tps on un-indexed machines at 28.8 tps each, you would need roughly 1,389 of them. With the index: three.

The Ladder — and Why It Is In That Order

So there is a ladder, and it goes: index → replica → denormalize → cache.

Everybody can recite it. Almost nobody can tell you why it is in that order — and the answer is not "cheapest first" or "easiest first". It is something much more useful, and it comes straight out of the section you just finished.

The ladder is ordered by how many copies of the truth you create.

rungcopies of the truthwho keeps them in sync?
1 · indexzero. It is a new access path to the same rows. There is still exactly one copy of your data.nobody has to
2 · replicaN, whole-database copiesthe database — automatically, milliseconds behind
3 · denormalizecopies inside your own schemayou, in application code, forever
4 · cachea copy in a different system, with a different lifetimenobody. That is what "invalidation" means.

Read that last column again, top to bottom. It goes: nobody has to → the database → you → nobody.

Section 3 spent nine lessons teaching you one law: a distributed system is a pile of news that has not arrived yet. Well —

Every rung you climb creates one more place where the news has to arrive.

An index creates none. A replica creates copies the database will chase for you (and you now know exactly what that staleness costs, because §3 measured it). Denormalisation creates copies you have to chase, in code, on every write, forever. And a cache creates a copy that nothing on earth is keeping in sync — which is why cache invalidation is a joke about how hard it is.

So: climb in order, and stop at the first rung that is fast enough. Not because the higher rungs are bad, but because each one hands you a new consistency problem, and you should only accept a new consistency problem in exchange for something you actually needed.

The Four Rungs, and What Each One Bills You

Each of these gets its own lesson — this is the map, not the territory.

Rung 1 · Index. Buys: less work per query. Measured: 48.3 ms → 0.17 ms; 28.8 → 16,772 tps. Bills you: write throughput, on every single write, forever. Measured on this table: 10,000 inserts took 37.7 ms with no extra indexes and 109.4 ms with four — 2.9×. (Indexes Deep-Dive (#18) measured the same tax at scale: 368 ms → 3,617 ms going from zero to five indexes.) An index is not free; it is just the cheapest thing on the ladder, and the only one that adds no copies.

Rung 2 · Read replicas. Buys: throughput — more lanes. Bills you: money, and staleness. And that staleness is not theoretical any more. In §3 it was a fascinating property of distributed systems; the moment you route a user's read to a replica, it becomes a bug with their name on it — they update their profile, the read goes to a replica that hasn't heard yet, and their change vanishes in front of them. That is the entire subject of the very next lesson, Read Replicas & Replication Lag (#31).

Rung 3 · Denormalize. Buys: the answer, pre-joined. Bills you: write amplification — one logical change now has to be applied in several places — and the permanent possibility that those places disagree. You have taken a job the database was doing for free (keeping one truth) and moved it into your application code. Sometimes that is exactly right. Denormalization (#32) and Materialized Views (#33) are where you learn when.

Rung 4 · Cache. Buys: the answer, pre-computed, and it is the fastest rung by a mile. Bills you: a whole new distributed system, with its own failure modes — cold starts, stampedes, and the copy that nobody invalidates. Caches & Databases (#36) is an entire lesson on keeping this one honest.

Notice that not one of these rungs is free — and notice that the bill gets stranger as you climb. Rung 1 costs you milliseconds on writes. Rung 4 costs you a category of bug.

Climb It Yourself

Three pages are slow. They are not slow for the same reason, and the fix that rescues one of them does literally nothing for another.

Start with the 48 ms query, and do the thing the room always wants to do: add replicas. One. Three. Five. Watch the p99 refuse to move while the bill goes up six-fold and you quietly manufacture five copies of the truth. Then add the index and watch it ship on one machine, with zero copies.

Then go to Black Friday, where the trap runs backwards, and find out that an index cannot conjure capacity out of nothing.

And if you are tempted — and you will be — skip straight to the cache and see what the widget says about what you just bought.

Three pages are slow, and they are not slow for the same reason. Climb the ladder on each of them and watch what happens. On the 48 ms query, add replicas — one, three, five — and the p99 does not move (it gets slightly worse), the bill goes up six-fold, and you have created five copies of the truth for nothing. Then add the index and it ships on one machine with zero copies. On Black Friday the trap runs the other way: the index alone cannot conjure capacity out of thin air. And if you skip straight to the cache, the widget will tell you exactly what you just bought. Every number in it came off a real Postgres with 2,000,000 rows.

When to Stop Climbing

The most valuable skill in this whole section is knowing when to stop.

There is enormous professional pressure to climb. Adding a cache looks like engineering. Adding replicas looks like scale. Writing CREATE INDEX and going home looks like you did nothing — even though you just made the system 583× more efficient per box and created zero new places for the news to be late.

So, three rules:

  1. Measure before you climb. "The database is slow" is not a diagnosis. EXPLAIN (ANALYZE, BUFFERS) is. If the plan says Seq Scan and Buffers: shared hit=16667, you do not have a scaling problem. You have a missing index, and no amount of hardware will fix it.
  2. Stop at the first rung that meets the requirement. Not the first rung that feels impressive. If one index gets you to 0.17 ms and you need 10 ms, you are done. Go and do something else.
  3. Every rung you take, say out loud what it costs. "I am adding a replica; I am accepting stale reads and I will handle read-your-writes." "I am adding a cache; I am accepting that a copy of this data now exists that nothing keeps in sync, and I own the invalidation." If you cannot say the sentence, you are not ready to take the rung.

Mental-Model Corrections

"The database is slow — add a read replica." The single most common mistake in read scaling, and a category error: a replica buys throughput, not latency. Measured: 48.3 ms → 56.4 ms. You bought a machine and got nothing. Ask first: slow, or busy?

"We need to scale reads." Do you? A 48 ms query running twenty times a second is not a scaling problem. It is a missing index. Measure before you climb.

"Caching is the fastest fix." It is the fastest rung and the most expensive decision. It is the only one that creates a copy of your data that nobody keeps in sync — and people reach for it while a single unused index is sitting right there.

"Indexes are free reads." They are paid for on every write, forever. Measured here: 2.9× on inserts with four indexes. Cheap — but not free, and they are the only rung whose bill you pay in milliseconds rather than in correctness.

"Denormalize for speed." Denormalisation does not remove the work; it moves it from read time to write time, and it moves the responsibility from the database to you. You are now the one keeping two copies of the truth in agreement, on every write, forever.

"Replicas give me read-your-writes." They give you the exact opposite. You have just taken §3's staleness and pointed it directly at your users. That is the next lesson.

"More machines = more performance." More machines = more capacity. Performance per request is decided by the access path, and the access path is decided by the query and the index — on every machine, identically.

Key Takeaways

  • ⭐⭐⭐ Ask first: is the database SLOW, or is it BUSY? Slow = one query takes too long → an index. Busy = the box is out of capacity → a replica. Different problems. Different rungs. Almost everyone answers the wrong one.
  • ⭐⭐⭐ A read replica is MORE LANES. It does not make your car go faster. Measured: the same query went 48.3 ms → 56.4 ms on a dedicated replica. It runs the same plan over the same rows on the same size box. It was never going to be faster.
  • ⭐⭐ One machine with an index does 16,772 tps. Two machines without one do 51. The index beats doubling your hardware by 327× — and it makes every replica you buy afterwards 583× more valuable. That is why it is rung one.
  • ⭐⭐ The ladder is ordered by how many COPIES OF THE TRUTH you create: index = 0 · replica = N (the database keeps them in sync) · denormalize = copies in your schema (you keep them in sync) · cache = a copy elsewhere (nobody keeps it in sync).
  • ⭐ Every rung you climb creates one more place where the news has to arrive. That is §3's law, walking into §4. Read scaling is the deliberate manufacture of places for news to be late.
  • Nothing on the ladder is free. An index costs 2.9× on writes. A replica costs money and staleness. Denormalisation costs write amplification and the risk of disagreement. A cache costs you a whole new consistency problem.
  • Climb in order. Stop at the first rung that is fast enough — and be able to say out loud what the rung costs you before you take it.

Next: Read Replicas & Replication Lag. You are about to take rung two — and discover that the staleness §3 taught you as a beautiful theoretical property becomes, the instant you route a user's read to a replica, a bug with their name on it.