The Database Scaling Ladder

Indexes, pooling, cache, bigger box, replicas, partitions, shards: climb in order.

The story

The orders page is slow. p99 is two seconds and getting worse every week. The team decides the database has hit its limits and starts planning to shard: pick a shard key, write a routing layer, plan a migration. It's a quarter of work.

Two weeks in, someone runs EXPLAIN ANALYZE on the slowest query. It filters orders by customer_id. There's no index on customer_id, so every page load reads all 40 million rows.

One line fixes it:

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

p99 drops from 2 seconds to 4 milliseconds. Database CPU falls from 85% to 12%. The sharding project is cancelled.

This keeps happening because sharding feels like the serious engineering answer and an index feels like a beginner's fix. But database scaling options run from cheap and reversible to expensive and permanent, and you climb them in order.

The idea: the ladder

Each rung costs more, takes longer and is harder to undo than the one before it. Only move up when the rung you're on has really run out.

0 · Measure

You can't fix what you haven't found. In Postgres, pg_stat_statements ranks queries by total time spent. Usually the top three queries account for most of the load. Fix those before anything else.

1 · Queries and indexes

A missing index turns a lookup that reads a few pages into a scan of the whole table. Also look for N+1 loops (a query inside a for-loop), SELECT * pulling large columns nobody uses, and OFFSET 100000 pagination. This rung often gets you 10–100×.

2 · Connection pooling

Every Postgres connection is a separate OS process using several MB of memory. Two hundred app instances × 20 connections each = 4,000 connections, and the database spends its time switching between them instead of doing work. A pooler (PgBouncer, RDS Proxy) puts thousands of client connections onto a few dozen real ones.

3 · Cache hot reads

If the same product page is read 10,000 times between edits, read it once and keep it in Redis. The cost is staleness and the work of invalidating the cache when data changes.

4 · Bigger machine

More RAM means more of the working set stays in memory, and memory is about 1,000× faster than SSD. On a managed database this is a few minutes of failover. It's boring, and it works (Horizontal vs Vertical).

5 · Read replicas

Copies of the primary that serve reads. This scales reads, not writes, and replicas lag behind the primary: a user who saves a change and reloads can be shown the old value. The fix is to send that user's reads to the primary for a short window after they write (read-your-writes).

6 · Partitioning

Split one huge table into pieces on the same server, usually by time (orders_2026_10, orders_2026_11). Queries that filter on the partition key skip the irrelevant pieces, and deleting old data becomes DROP TABLE on an old partition instead of a giant DELETE. You still have one database and full SQL, so it's cheap.

7 · Sharding

Split the data across separate servers by a shard key. This is the only rung that scales writes past one machine. It's also the one where joins across shards, transactions across shards, resharding and hot keys all become your problem, permanently.

Choose the shard key badly and one shard does all the work:

Which rung fixes my symptom?

Cheat sheet

Say it in the interview

"Before sharding I'd climb the cheap rungs: find the top queries with pg_stat_statements, fix indexes, add a pooler, cache the hot reads, scale the box up, then add read replicas. I'd only shard if writes outgrow one primary, and I'd shard on customer_id so most queries stay on one shard."

Go deeper

• SD 1 · Query Optimization & EXPLAIN: rung 1, properly.
• SD 1 · Replication, WAL & Lag: rung 5.
• SD 1 · Sharding: rung 7, and choosing a shard key.
• SD 2 · Chapter 3 — Indexes From First Principles: why one missing index costs 1,000×.