The database scaling ladder: exhaust the rung below before you climb

“The database is slow” is the start of a conversation, not a diagnosis. I just finished a five-part course on database scaling, and the most useful thing in it wasn’t any single technique. It was the shape: a ladder, where you only climb once you can justify to yourself that the rung below has nothing left to give. Every step up buys you something and charges you something, and the charge is usually complexity you’ll carry forever.

This is the ladder as I now hold it, with the price tag on each rung.

Rung 1: ask the database, don’t guess

Before changing architecture, change the question. Put EXPLAIN ANALYZE in front of the slow query and the database will tell you, step by step, what it did. Without an index, Postgres falls back to a sequential scan, which is what it sounds like: read every row. On ten rows nobody cares. On ten million, that’s your latency.

A B-tree index turns that walk into a few hops down a tree. Cheap to add, big payoff, and it’s where most performance problems actually live. The exception is low-cardinality columns. An is_active flag that’s 90% true gives an index little to cut with.

Two more things belong on this rung:

• N+1. One query for 100 posts, then 100 queries for their authors. Use a join or eager loading and it’s one round trip.
• Connection pooling. Every connection costs the server memory (Postgres forks a process per connection; MySQL uses a thread). A monolith with a handful of instances never notices. A large microservice fleet, or Lambdas that each open a connection on cold start, can exhaust the database with connections alone. A pooler such as PgBouncer sits in the middle.

The tip I’m keeping: paste the EXPLAIN output into an LLM and ask what it sees. The output is dense, and reading it is a skill an assistant can lend you while you learn it.

Rung 2: vertical scaling is underrated

Buy a bigger machine. No query changes, no new failure modes, and often the right call when the pressure is on. Its limits are real, just far away: backups and migrations on a huge instance get slow, and the disk has a physical IOPS ceiling. And at the very top end the machines get expensive fast. Still, a few thousand dollars more per month is frequently cheaper than the engineering time of any rung above this one.

Rung 3: read replicas, and the price is consistency

This is the first rung that changes how the application thinks. Postgres streams its write-ahead log to replicas, and reads can go there. Replication is asynchronous by default: the primary commits without waiting for the replica. So you now have eventual consistency, and a user can write, read from a replica that hasn’t caught up, and see stale data.

You can make replication synchronous, at the cost of a network round trip on every commit. Within one availability zone that’s small; across regions it’s painful. A reasonable split: async for most things, sync for money.

Ways to cope with stale reads:

• Read-your-writes: after a write, route that user’s reads to the primary for a short window.
• RETURNING: have the write hand back what it wrote, so the client already has the fresh row.
• Sticky sessions that keep a user on one replica, so reads at least don’t go backwards in time.

One trap deserves its own line: don’t fail reads over to the primary when replica lag rises. Lag usually means the system is already struggling with writes. Moving reads onto the primary adds load exactly where it hurts.

Routing, finally, is easiest in the application. Most drivers and frameworks already support separate read and write connections. A proxy is transparent but adds infrastructure you have to run.

Rung 4: partitioning, for tables that are just too big

At hundreds of millions of rows the problems stop being about query speed. Indexes get deep. VACUUM, which is Postgres’s garbage collection for the dead row versions that MVCC leaves behind on every UPDATE and DELETE, takes forever. Backups, migrations, and reindexing all slow down with volume.

Partitioning splits one logical table into many physical ones. Range partitioning by created_at is the common case: an orders table with one partition per quarter. The application doesn’t change. The planner does partition pruning, so a query with created_at in its WHERE clause only touches the relevant partitions. Omit that column and it reads all of them, and you’ve paid for partitioning without collecting.

The best part is deletion. Removing old data with DELETE generates millions of dead tuples, WAL traffic, replication load, and vacuum work. With partitions, you DETACH the old one and DROP it. That’s a metadata operation. Pair it with a retention policy (hot, warm, cold, archive to object storage) and the table stops growing without bound.

Costs: planning time rises with thousands of partitions, the primary key must include the partition key, and every schema change now touches many tables (though Postgres propagates a lot of it).

Before partitioning, check that autovacuum is tuned. The default triggers at 20% dead tuples, which on a 50-million-row table means ten million dead rows before it even starts. Lower that threshold.

For heavy reporting, reach for a materialized view before a replica. It precomputes the result of an expensive query, trading freshness for speed, and needs no new node.

Rung 5: CQRS, when reads and writes want different things

Writes want normalized tables, strong constraints, ACID transactions. Reads want denormalized shapes, one index per question, and are happy with relaxed guarantees. When a single orders model serves checkout, admin, and reports, optimizing for one hurts another.

CQRS (Command Query Responsibility Segregation) separates the write model from one or more read models. It’s a pattern, not a framework, and it isn’t event sourcing. What I liked is that it has its own mini-ladder, simplest first:

1. Write the read model in the same transaction as the write model. Still coupled, but the read path no longer touches the checkout tables.
2. Update the read model from a background job. Now you have eventual consistency.
3. Drive it from events, including change data capture (Debezium reading the WAL into Kafka).

Most systems stop at step 2. A materialized view is already a tiny read model. And if you’re building plain CRUD at a few million rows with a small team, this is over-engineering.

Rung 6: functional partitioning, one database per domain

When one domain’s workload keeps hurting another’s (billing queries slowing checkout), they share more than you think: buffer cache, locks from DDL, vacuum, backup windows. Giving each domain its own database isolates those. This doesn’t require microservices; a monolith can talk to several databases.

The price is the loss of joins across domains. You replace them with duplicating slow-changing fields, calling another module at runtime, or building read models from events. And you inherit the dual-write problem: if you write to the database and then publish a message, one can succeed and the other fail. The fix is the outbox pattern: write the event to an outbox table in the same transaction as the data, and let a poller or CDC publish it, with at-least-once delivery and idempotent consumers.

Do this for a problem you have, not one you predict. It’s also a good precursor to sharding, since moving five tables is easier than moving six hundred.

Rung 7: sharding is a different kind of thing

Partitioning is one server, many tables. Sharding is many servers. The database no longer manages it for you, and you’ve entered distributed systems with everything that implies: network failures, eventual consistency, and routing logic in your application or a proxy.

The shard key is the decision. You want high cardinality, even distribution, and presence in nearly every hot-path query, so that a request goes to one shard. Without it you get scatter-gather: query every shard, merge, and wait for the slowest. That’s tail latency, and it’s fine for reports and wrong for payments. Good keys: tenant ID, user ID. Bad ones: created_at (permanent hotspot on the newest shard), status or country (too few values), a bare order UUID (even, but nobody queries by it alone).

Three ways to place data:

• Range: simple and great for range queries, prone to hotspots.
• Hash: even distribution, but range queries scatter.
• Directory: a lookup table from key to shard. Flexible (a dedicated shard for a huge customer, EU-only data), but it’s now a single point of failure in front of every query and needs aggressive caching.

Naive hashing makes resharding a disaster. hash(key) % N means going from 4 to 5 shards moves about 80% of your data. Consistent hashing, which puts shards on a ring, moves only about 1/N. Virtual nodes smooth out the ring’s uneven spacing.

The best resharding is the one you avoid. Create many logical shards up front, say 256, spread over a few physical servers. Adding capacity then means moving whole logical shards between servers, with no rehashing and no application changes.

Then the operational bill arrives: connection pool explosion, cluster-wide backups and restores, per-shard monitoring and tracing, rolling schema migrations across hundreds of shards, no cross-shard foreign keys, unique ID generation (time-ordered IDs rather than auto-increment), and cross-shard transactions replaced by sagas with compensating steps. It is a permanent increase in cognitive load for everyone who touches the system.

What I’m taking from it

The ladder is a discipline more than a menu. Before each climb, ask:

• Have I really exhausted the rung below, or just skipped it?
• Is the database the bottleneck, or is the app using it badly?
• Is this a problem I have now, or one I’m imagining?
• Do I have someone on the team who has done this before?

Most systems never need rung 7. That isn’t a failure of ambition; it’s pragmatism. A bigger machine and the right index get further than most of us expect, and every rung you don’t climb is a class of problems you never have to debug at 2 a.m.