How to scale databases

The database is the hardest tier to scale, and the reason is one word: it is stateful. You cannot just clone it the way you clone a stateless backend, because every copy has to agree on the data. So scaling a database is an ordered climb from cheap, safe moves to expensive, complex ones, and the whole skill is doing them in the right order and stopping as early as you can. This post explains how each rung actually works, building on the replication, sharding, and consistency fundamentals.

First, do not scale - make it fast

Most "we need to scale the database" problems are really a handful of slow queries, and the fix costs nothing but attention.

• Add the right indexes. An index is a sorted secondary structure, almost always a B-tree, that turns a full-table scan (O(n)) into a lookup (O(log n)). Run EXPLAIN (or EXPLAIN ANALYZE) on your slow query and look for a sequential scan on a big table where an index would give an index scan. A composite index on (tenant_id, created_at) speeds queries filtering on tenant_id or on both, but not on created_at alone (the leftmost-prefix rule), and a covering index that contains every column the query needs is answered from the index without touching the table at all.
• Fix the query shapes. Kill N+1 patterns (one query in a loop becomes one query total), stop selecting columns you do not use, and paginate large result sets.

The catch to remember: indexes are not free - every index must be updated on every insert, update, and delete (write amplification) and costs disk. Index what you filter and sort on, and no more. A single well-indexed node handles far more than people expect; only when a tuned node is genuinely saturated do you start scaling it.

Scale up, and pool your connections

The simplest scaling is vertical: a bigger box with more RAM (so more of the working set stays in memory instead of hitting disk) and faster SSDs. For databases this carries you further than for the stateless tier, and a single primary is dramatically simpler than a distributed cluster, so do not rush to leave it.

One cheap win people skip: connection pooling. In a database like PostgreSQL, every connection is a real backend process with its own memory, and a horizontally scaled app tier can open thousands of them - at which point the database spends its resources managing connections instead of running queries. A pooler like PgBouncer sits in front and multiplexes many client connections onto a small set of real database connections. In transaction pooling mode a database connection is only held for the duration of a transaction, so a few dozen real connections can serve thousands of clients. This alone often clears a "database is overloaded" problem that was really a connection problem.

Cache the reads

Put a cache in front of the database so repeated reads are served from memory and never reach it. For read-heavy workloads this is the single biggest offload available: the hot rows live in the cache, and the database only sees misses and writes. The right pattern and eviction policy are their own decisions (covered in the caching posts); the point here is that a cache is a database-scaling tool, and usually the first one to reach for after indexing.

Scale reads with replication

When reads still outgrow one node, replicate. In the standard leader-follower model, all writes go to one leader (primary), which streams its change log - the write-ahead log - to one or more read replicas that serve SELECT traffic. Since most systems are 90%+ reads, spreading reads across replicas scales the common case well, and the replicas double as hot standbys: if the primary dies, one is promoted to take over.

The mechanic you must understand is replication lag. Replication is usually asynchronous - the primary acknowledges a write without waiting for replicas, so a replica is a few milliseconds to seconds behind. That produces a concrete bug: a user updates their profile (write to primary), immediately reloads (read from a lagging replica), and sees the old data - the read-your-writes problem. The fix is to route reads that must be current to the primary for a short window after a write, and send everything else to replicas. Two limits keep replication from being a silver bullet: it does not scale writes at all (every write still goes to the one primary), and past a point, pushing the change stream to many replicas becomes its own load on the primary.

Scale writes and storage with partitioning

When the writes or the sheer data volume outgrow one machine, you split the data. Two shapes:

• Vertical partitioning - split by columns or by feature: users in one database, orders in another, rarely-read blob columns in a separate table. Simple, often an early step, and it reduces the working set each node holds.
• Horizontal partitioning (sharding) - split the rows across many machines, each holding a subset. This is what actually scales writes and storage without limit, and it is the biggest jump in complexity in the whole ladder.

Sharding hinges on the shard key and the routing method:

• Hash sharding - shard = hash(key) % N. Spreads data evenly, but range queries scatter across all shards, and changing N reshuffles almost everything (which is why real systems use consistent hashing - place shards on a ring so adding one only moves ~1/N of the keys).
• Range sharding - shard by key ranges (A-M here, N-Z there). Range scans stay efficient, but you risk a hot shard if one range is far more active (everyone signing up today lands on the "recent" shard).
• Directory sharding - a lookup service maps keys to shards. Most flexible, but the directory is another component to keep available and fast.

The costs sharding imposes are permanent and worth stating plainly: a query that needs data from several shards becomes a scatter-gather (ask every shard, merge results); joins and transactions across shards become expensive or impossible (you reach for two-phase commit or sagas); a hot key can still overwhelm its shard no matter how even the overall spread; and resharding a live cluster - changing the shard count or key - is one of the genuinely hard operational tasks in the field. So the universal advice holds: exhaust indexing, caching, and read replicas first, and shard only when you must.

Beyond a single leader

Two directions open at the far end:

• Multi-leader / leaderless stores (Dynamo-style, like Cassandra or DynamoDB) accept writes on many nodes and reconcile with quorums - require W nodes to ack a write and R to agree on a read, and if W + R > N a read always overlaps the latest write. Conflicts from concurrent writes are resolved with last-write-wins timestamps or vector clocks. This scales writes and availability, at the cost of embracing eventual consistency - the CAP trade-off made concrete: during a network partition you choose to stay available and reconcile later.
• Distributed SQL (NewSQL) - Vitess (MySQL sharding, born at YouTube) and CockroachDB give you horizontal sharding while keeping SQL and ACID transactions. They split data into ranges, replicate each range with a consensus protocol like Raft, and hide the routing and resharding behind the database itself - increasingly the pragmatic answer when you have outgrown a single relational node but do not want to hand-roll sharding.

And on the architecture side, CQRS takes the read/write split to its conclusion: run a separate, independently scaled read store (often denormalized, fed from the write store via events) so reads and writes stop competing entirely.

The full architecture

Combine the moves and the mature picture is a read/write split, replicated, and sharded - app servers talk through a connection pooler, hot reads are served from a cache, and a shard router sends writes to each shard's primary and reads to its replicas:

Trace both paths. A read goes app -> pooler -> cache; on a hit it returns instantly, and on a miss the shard router sends it to the right shard's read replica, so read load spreads across replicas and shards. A write goes app -> pooler -> shard router -> that shard's primary, which applies it and streams it to its replica. Reads and writes now scale on different axes - replicas for reads, shards for writes and storage - and no single node sees all of either. You would not build this on day one; you arrive at it one rung at a time.

The order you climb it

1. Index and tune - fix the queries before touching hardware.
2. Scale up the single node, and pool connections.
3. Cache reads in front of the database.
4. Add read replicas to scale reads (and mind replication lag).
5. Vertically partition by feature as an intermediate step.
6. Shard (or move to distributed SQL) only when writes or storage truly outgrow one node.

Climb it in order and stop at the first rung that holds. Most systems never need the last one - and knowing that, rather than sharding on reflex, is the actual scaling skill. That closes this set: a CDN carries the reads at the edge, a stateless backend scales out behind it, and the database - scaled carefully, from the cheap moves up - holds the state they all depend on.