Database Sharding: When One Database Becomes Many
You've maxed out the biggest instance your cloud sells. Read replicas help reads but every write still lands on one machine. Sharding splits your data across many databases — and hands you a new set of problems around shard keys, hotspots, and queries that can no longer see all your data at once.
The Ceiling You Eventually Hit
For a long time, scaling a database means buying a bigger box. More RAM, more cores, faster disks. It works — until it doesn't. Eventually you're renting the largest instance your cloud offers, and it's still saturated on writes.
Read replicas buy you room on the read side: copy the data to more machines, spread the SELECTs. But every INSERT, UPDATE, and DELETE still funnels to a single primary. When that primary's write throughput is the bottleneck, replicas don't help. You've run out of vertical.
Sharding is the answer that scares people, and reasonably so. It's the point where one logical database becomes many physical ones, and a lot of things you took for granted stop being free.
What Sharding Actually Does
Replication copies the same data to multiple machines — every replica has everything. It scales reads and adds redundancy. Sharding splits different data across machines — each shard holds a distinct slice, and no single shard has the whole dataset. It scales writes and storage, because each shard only handles its own portion of the traffic.
Split users across four shards and each machine handles roughly a quarter of the writes, a quarter of the storage, a quarter of the load. Four modest machines now do what no single machine could.
Everything now hinges on one decision: how does the router know which shard holds a given row? That's the shard key, and it's the choice that will either save you or haunt every migration you ever run.
Choosing a Shard Key: Three Strategies
The shard key is the column (or columns) that decides which shard a row lives on. There are three common ways to map keys to shards.
| Strategy | How it maps | Strength | Weakness |
|---|---|---|---|
| Range-based | Key ranges → shards (A–F, G–M...) | Simple; efficient range scans | Hotspots if data skews to one range |
| Hash-based | hash(key) % N → shard | Even distribution, no hotspots | Range queries hit every shard; resharding is painful |
| Directory-based | A lookup table maps key → shard | Flexible; easy to rebalance | The directory is a bottleneck + SPOF |
Range-based is intuitive but dangerous: if you shard orders by date, all of today's writes hammer one shard while the others sit idle. Hash-based spreads load evenly but destroys locality — a query for "all users created last week" now has to ask every shard. Directory-based gives you flexibility at the cost of maintaining (and scaling) the lookup table itself.
⚠️ A bad shard key is nearly unfixable
Changing the shard key means physically moving most of your data to new shards — a massive, risky migration, often with downtime. This is the decision you cannot cheaply undo. Spend real time here: pick a key with high cardinality, even distribution, and one that matches how you actually query.
The Hotspot Problem
The goal of a shard key is even distribution. Get it wrong and one shard takes disproportionate load — a hotspot — while the others idle. You've paid for four machines and one of them is doing all the work.
Classic ways to create a hotspot:
- Sharding by a low-cardinality key (like
countryorstatus) — if 60% of users are in one country, that shard carries 60% of the load. - Sharding by an auto-incrementing ID with range partitioning — every new row goes to the highest range, so the newest shard is always the hot one.
- A celebrity/whale row — one tenant or user with 1000× the activity of everyone else lands entirely on one shard.
✅ Match the key to your access pattern
There's no universally correct shard key — only one that's correct for your queries. If you mostly look up a single user by ID, hash on user ID for even spread. If you run tenant-scoped queries, shard by tenant so each tenant's data stays on one shard (keeping its queries single-shard). The right key is the one that keeps your common queries on as few shards as possible while still spreading load.
What's your dominant query pattern?
What You Give Up
Sharding isn't free scaling — it's a trade. Once data spans machines, several things that were trivial in a single database become hard or impossible.
Cross-shard joins die
A JOIN across data on different shards can't happen in the database. You either denormalize so related data lives together, or you join in the application layer by querying multiple shards and stitching results — slower and more code.
Transactions stop being atomic across shards
A single transaction can't span shards with normal ACID guarantees. Writing to two shards atomically pushes you toward sagas or two-phase commit — the distributed-transaction problem you were trying to avoid.
Aggregate queries fan out
COUNT, SUM, or "top 10 across all users" must query every shard and merge results (scatter-gather). Fine for a few shards, painful for many.
Unique constraints get harder
A globally unique constraint (like a unique email) can't be enforced by one shard that only sees its own slice. You need a separate global index or a coordination layer.
🔴 Shard late, not early
Sharding adds permanent complexity to every query and migration. Exhaust the cheaper options first: query and index optimization, caching, read replicas, and vertical scaling. Many systems that 'need sharding' actually need one good index. Shard when you've genuinely hit the write ceiling of a single primary — not before.
Key Takeaways
- Replication scales reads; sharding scales writes and storage by splitting distinct data across machines. Reach for it when a single primary can't absorb your write load.
- The shard key is the decision that matters most — and the one you can't cheaply change. Choose for high cardinality, even distribution, and alignment with your common queries.
- Range, hash, and directory strategies trade off locality, evenness, and flexibility. Hash avoids hotspots but kills range queries; range is simple but skews; directory is flexible but adds a bottleneck.
- Sharding costs you cross-shard joins, atomic multi-shard transactions, cheap aggregates, and global constraints. Design your data so common queries stay single-shard.
- Shard late. Optimize, cache, and replicate first — sharding is the tool for when you've truly run out of vertical room.
References
- Sharding pattern — Microsoft Azure Architecture — the three strategies and their trade-offs in depth
- Vitess — Horizontal sharding — how a production sharding layer handles routing and resharding for MySQL
- Citus — Choosing a distribution column — practical guidance on picking a shard key for Postgres