Ten database scaling techniques, in the order you should try them
Lists of database scaling techniques are usually presented as a menu, as though you pick the one that suits you. They aren’t a menu. They’re a ladder, and the rungs are ordered by two things that matter far more than any of the techniques: how much they cost you, and how hard they are to undo.
Two questions sort all ten:
- What currency does it cost you? Some cost only money and effort. Some cost you correctness — you end up holding a copy of the truth that can silently stop being true. And two cost you your architecture, permanently. That progression is the order, and it matters that money comes first: it is by far the cheapest of the three.
- Does it help reads or writes? This is the one people discover too late. Every technique here helps reads. Only two add write capacity, and only one of those has no ceiling.
Work through them in order and most systems never reach the bottom. Skip to the bottom first — as teams reaching for sharding before they have run a single EXPLAIN routinely do — and you buy years of complexity to solve a problem a missing index was causing.
1 · Query optimisation — the free one
Before anything else, look at what the database is actually doing. The gap between a query as written and the same query written well can be two or three orders of magnitude, and closing it costs nothing but attention.
The recurring offenders are worth knowing by name. N+1 queries — a loop issuing one query per row, usually courtesy of an ORM’s lazy loading. Non-sargable predicates — wrapping an indexed column in a function (WHERE lower(email) = …) so the index can’t be used, unless you built an index on that expression. SELECT * when you need three columns, which inflates I/O and forfeits index-only scans. And OFFSET-based pagination, which gets linearly slower as users page deeper because the rows it skips still have to be produced inside the server and then thrown away. Keyset pagination (WHERE id > :last) stays flat instead — at the price of needing a matching index, a deterministic sort order with a unique tie-break, and giving up the ability to jump straight to page 400.
Cost: none. Reversibility: total. Do this first, always.
2 · Indexing — fast reads, paid for on every write
An index is a separate ordered structure that turns “look at every row” into “descend a tree”. It is the highest-leverage change on this list, and the most commonly misused.
That last point is the one to internalise, and it is worse than the usual telling. The folk version — “every index is updated by every write” — is too generous in one direction and far too generous in the other. Postgres can sometimes update a row without touching any index at all, when the statement changes no indexed column and the new row version fits on the same page (it still writes a new version — it just keeps it off the indexes); and a DELETE removes nothing from an index synchronously, leaving that to vacuum. But when an update doesn’t qualify for the in-place path, it writes a whole new row version and therefore a new entry into every index on the table — including indexes on columns the statement never mentioned. That is Postgres’ MVCC design, where an update always creates a new row version; InnoDB instead updates the row in place and touches only the indexes whose columns actually changed. Ten indexes means ten structures dirtied by an update to an unindexed column.
So unused indexes aren’t merely idle: they are disk, memory, write amplification and slower bulk loads. Audit them — every mature database tracks which indexes have been scanned. Two traps when you do: those counters reset when statistics are reset, and they’re per-server, so an index that looks unused on the primary may be serving a replica’s reporting queries. And an index backing a unique or primary key constraint is doing a job whether or not anything ever scans it.
Cost: disk, and write throughput. Reversibility: high, with a lock caveat worth knowing. In Postgres, building an index locks the table against writes unless you use the concurrent variant — and dropping one is worse than people expect: a plain DROP INDEX takes the strongest lock there is, briefly blocking even plain reads, so there is a concurrent variant for that too. MySQL adds and drops secondary indexes online by default, no keyword required.
3 · Connection pooling — stop paying the setup cost
Opening a database connection is not free: TCP, TLS, authentication, and then — in Postgres specifically — a whole new backend process with its own memory. Doing that per request is a self-inflicted wound.
The counter-intuitive half: a pool’s maximum size is a throughput control, not a generosity setting. Past a certain point, extra concurrent queries contend for the same CPUs, memory and locks, and total throughput falls while every individual query gets slower. The long-standing starting point is around twice the core count plus room for the concurrent I/O the storage can absorb — a number tied to the machine, not to your traffic. Teams routinely discover that cutting a pool from thousands of connections to a couple of hundred makes everything faster, which feels wrong until you notice that the surplus connections were never doing work, only queueing for it.
Cost: essentially config. Reversibility: total.
4 · Vertical scaling — buy a bigger machine
The most underrated option on the list, because it requires no application change at all. Double the CPU and RAM and, for a great many workloads, you are done for another two years. Modern single machines are enormous — hundreds of cores and terabytes of memory — and a database that fits in RAM behaves like a different piece of software.
Cost: money, and a single failure domain. The price premium per unit of capacity is milder than folklore suggests across most of the range — it climbs sharply only at the very largest sizes, where you are buying scarcity. Reversibility: total, at the cost of a restart or failover — and on hardware you own, it isn’t a knob at all.
5 · Vertical partitioning — narrow the rows you actually read
Split a wide table by column: the columns your hot queries need in one table, the rest in another. Databases read in fixed-size pages and a row cannot span one, so narrower rows mean more rows per page, fewer pages read, and a much better chance the hot table stays resident in memory.
Read the next paragraph before you reach for this, because the obvious example is the one case where it buys nothing. If your cold columns are large — a 4 KB bio, an 80 KB avatar — Postgres has already done this for you. Once a row exceeds roughly 2 KB, TOAST first compresses the oversized values, and moves them to a side table if that alone is not enough — leaving a pointer behind, and fetching them only if a query actually selects them. (Which order matters: a 4 KB bio that compresses under the threshold stays in the main row; an 80 KB avatar is going out of line regardless.) Your main table is already the narrow hot table. MySQL’s default row format does the same thing for long text and blobs. Splitting a table by hand to separate out big values is a migration that reproduces what the storage engine did automatically.
Manual partitioning earns its keep on a different shape: many medium-width columns that each stay under the threshold. Forty columns of settings, counters, flags and denormalized labels, none individually large, together making a row so wide that the handful of columns your hottest query needs are spread thin across a lot of pages.
Cost: a join for queries needing both halves, and a schema migration. Reversibility: moderate.
6 · Caching — the first rung that costs you truth
Keep hot results in memory and skip the database entirely. Enormous wins, and the first technique on this ladder that changes what your system knows rather than how fast it runs.
Two failure modes worth designing for up front. Staleness: a cached row that changed underneath you, which you fix with TTLs, explicit invalidation on write, or writing through the cache. Stampede: everything expiring at once, which you fix by jittering TTLs and letting only one request recompute a missing key while the others wait.
Cost: correctness attention, plus a new piece of infrastructure to run and lose. Reversibility: high.
7 · Materialized views — cache the query, not the row
A view whose results are stored. Expensive aggregate joins run once at refresh time instead of once per request, and readers get a table lookup.
Know what your database actually gives you here, because “the database maintains it” is only true on some of them. Oracle and SQL Server can keep such a view current automatically. Core Postgres does not: there is no automatic refresh, no incremental refresh, and no built-in scheduler — you run REFRESH MATERIALIZED VIEW from cron or an extension, and each run recomputes the whole thing. Two more details that bite: a freshly created materialized view has no indexes, so “it’s just a small table lookup” stops being true as it grows; and the plain refresh takes a lock strong enough to block readers. There is a concurrent refresh that doesn’t — but it requires a unique index on the view, only works once the view is populated, refuses to run twice at once, and is slower when a large fraction of rows changed.
Cost: staleness, plus refresh time, storage, and a scheduler you own. Reversibility: high — drop it and queries go back to the base tables.
8 · Denormalization — trade write cost for read cost
Store the same fact in more than one place so reads don’t have to join. This is not a failure of design; it’s a deliberate purchase of read speed with write complexity.
Cost: write amplification, storage, and a class of consistency bug that normalisation existed to prevent. Reversibility: moderate — the schema is easy to revert, the data drift is not.
9 · Read replicas — more read capacity, and a new kind of bug
Stream changes from a primary to copies, then send reads to the copies. Read throughput becomes something you can add machines to.
The read-your-own-writes problem is the one that reaches users. The fixes are all forms of the same idea: send a user’s reads to the primary for a short window after they write, or carry a token recording the position of your write and refuse to be served anything older. Both Postgres and MySQL now expose primitives for the second approach, which is the more precise one.
Worth stating plainly: replicas add no write capacity, and synchronous replication actively subtracts from it. If the primary must wait for a standby to confirm before committing, every write pays at least one network round trip. That’s often the right trade for durability — it is never a scaling win.
Cost: replication lag becomes application logic; more machines. Reversibility: high — point reads back at the primary.
10 · Sharding — the only one that scales writes horizontally
Split the data itself across independent databases by a shard key. Each shard owns a slice and takes its own writes, so write throughput finally becomes horizontal.
Pick the shard key badly and everything above gets worse: a key with skew gives you one shard doing all the work, and a key that doesn’t match your access patterns makes every ordinary query a scatter-gather across all shards. Changing it later means moving all your data.
“But what about multi-primary replication?” It looks like the hole in this argument and isn’t. In a multi-primary or synchronous cluster, every node still applies every write — you gain availability and the ability to write locally, not aggregate write throughput, because the work isn’t divided between the nodes. Splitting the data is what divides the work, and that is sharding by another name.
“And doesn’t a distributed SQL database do this for me?” Increasingly, yes — and that’s the one part of this section that has genuinely aged. Systems in the Spanner lineage shard by key range automatically, splitting and rebalancing without you choosing a shard key up front, and keep cross-shard joins and distributed transactions inside the database. Proxy layers do something similar for Postgres and MySQL. They don’t repeal the trade — you still pay in latency, operational surface and cost, and a badly chosen access pattern still produces hot ranges — but “sharding means your application does the joins” describes hand-rolled sharding specifically, not the category.
Cost: high, ongoing, and paid by every engineer who touches the system afterwards. Reversibility: the lowest on this list.
The ten, side by side
| Technique | Reduces work / adds capacity | Helps reads | Helps writes | Introduces staleness | Reversible | What it costs |
|---|---|---|---|---|---|---|
| Query optimisation | reduces | ✓ | ✓ | ✓ no | fully | attention |
| Indexing | reduces | ✓ big | ✗ slows them | ✓ no | fully | disk + write throughput |
| Connection pooling | reduces | ✓ | ✓ | ✓ no | fully | config only |
| Caching | reduces | ✓ big | ✗ | ✗ yes | high | invalidation + infra |
| Materialized views | reduces | ✓ big | ✗ | ✗ yes | high | refresh + storage |
| Denormalization | reduces | ✓ | ✗ slows them | ✗ risk of drift | moderate | write amplification |
| Vertical partitioning | reduces | ✓ | partly | ✓ no | moderate | joins + migration |
| Vertical scaling | adds | ✓ | ✓ | ✓ no | fully | money, and a ceiling |
| Read replicas | adds | ✓ big | ✗ none — negative if synchronous | ✗ lag | high | lag becomes app logic |
| Sharding | adds | ✓ | ✓ the only one that scales out | ✓ none per shard | lowest | joins, transactions, forever |
↔ scroll the table sideways to see every column.
Read the middle columns together and the shape of the whole problem appears. Every one of the ten makes reads cheaper or more plentiful. Only two add write capacity — a bigger machine, which has a ceiling, and sharding, which doesn’t.
Be precise about that claim, because it’s easy to overstate. Plenty of things raise write throughput without adding capacity: batching inserts into a bulk-copy path, dropping indexes before a large load and rebuilding after, relaxing commit durability so transactions don’t wait for the disk flush, or moving to a write-optimised storage engine. Those are all real, and several are on this list already. What none of them do is let you answer “we need twice the write throughput” by buying hardware. For that there are exactly two moves, which is why write-heavy systems hit architectural walls that read-heavy systems never do — and why “we’ll shard later” is a decision you are making now whether you say so or not.
Staleness: which rungs cost you the truth
| What goes stale | How stale | How you notice | Mitigation | |
|---|---|---|---|---|
| Caching | individual values | until TTL or invalidation | a user sees an old value after saving | short TTLs, invalidate on write, write-through |
| Materialized views | the whole result set | until the next refresh | dashboards disagree with detail pages | schedule refreshes, refresh without blocking readers |
| Denormalization | copies of a fact | forever, if you miss a writer | two rows disagree and neither is obviously right | own every write path; reconcile periodically |
| Read replicas | everything, briefly | replication lag, usually ms–s | read-your-own-writes failures | route recent writers to the primary |
| Sharding | nothing per shard | — | a read spanning shards can catch one mid-commit | a global snapshot, if your system offers one |
| Query optimisation · indexing · pooling · vertical scaling · vertical partitioning | nothing | — | — | — |
↔ scroll the table sideways to see every column.
Note which row is the dangerous one — but draw the line in the right place, because it isn’t really about which technique you picked. Staleness is bounded when something re-derives the copy on a clock, and unbounded when it is only re-derived by an event somebody has to remember to fire.
A TTL expires whether or not you were paying attention, so caches and replicas converge on their own. A denormalized copy converges only if every code path that changes the original also updates it — miss one, and that row is wrong forever with nothing to tell you. The distinction follows the maintenance mechanism, not the label: a cache invalidated only on write has exactly the unbounded failure mode, and a denormalized column maintained by a trigger or a rebuild job is as bounded as any replica. Denormalization is the canonical unbounded case for one reason — it’s the one people almost always maintain by hand. (And a replica’s lag is bounded only while replication is healthy; a stalled standby never converges, which is why lag is something you alert on rather than assume.)
Three techniques that are secretly one technique
Caching, materialized views and denormalization look like three different tools. They are one move — precompute a result and store a redundant copy — performed at three different layers:
| Where the copy lives | Who maintains it | How you invalidate | |
|---|---|---|---|
| Caching | outside the database | your application | TTL or explicit delete |
| Materialized views | inside the database | the database, on refresh | refresh the view |
| Denormalization | inside your tables | every writer, by hand | update every copy |
Same trade in all three: faster reads, redundant data, and a new obligation to keep it true. They differ only in how much the system helps you meet that obligation — and denormalization, which helps least, is the one people reach for most casually.
When to stop
| Symptom | The rung that usually fixes it |
|---|---|
| One endpoint is slow, everything else fine | query optimisation, then an index |
| Slow after a traffic increase, CPU not saturated | connection pooling; check pool size |
| Same expensive read repeated constantly | caching |
| Expensive aggregate on a dashboard | materialized view |
| Read-heavy, CPU saturated, single box | vertical scaling, then read replicas |
| Reads fine, writes saturating one machine | vertical scaling — then sharding, and only then |
| Table is enormous but only a few columns are hot | vertical partitioning |
| Joins dominate a hot read path | denormalize that path |
TL;DR
TL;DR: These aren’t ten options, they’re a ladder ordered by cost and reversibility, and most systems that believe they need the bottom actually need the top. Rungs 1–3 — read the query plan, add the right index, pool your connections — are free or nearly so, fully reversible, and routinely worth two orders of magnitude.
The moment you reach caching, materialized views or denormalization you start paying in truth rather than money. Those three are the same move at different layers — precompute and keep a redundant copy — and they differ mainly in who maintains the copy. Staleness is bounded when a clock re-derives the copy and unbounded when only an event you must remember to fire does — which is why denormalization, almost always maintained by hand, is the one that stays wrong forever.
The asymmetry that decides your architecture: all ten make reads faster or more plentiful. Only two add write capacity — a bigger machine, which has a ceiling, and sharding, which doesn’t. Plenty of things raise write throughput (bulk-loading, fewer indexes, relaxed commit durability); nothing else lets you buy more of it. That’s why sharding is last: it’s the only technique that adds write capacity, and the only one that permanently moves database responsibilities into your application — cross-shard joins, cross-shard transactions, global uniqueness, rebalancing, and a shard key you can’t realistically change.
And note where vertical scaling sits: fourth, above everything that costs you correctness. It needs no code change and is reversible, so it belongs before you start keeping copies that can be wrong. Money is the cheapest currency on this list; truth is the most expensive. Buying two more years for the price of a bigger instance is usually the best trade on this page.