Database Sharding: Splitting Data When One Machine Isn't Enough
Database sharding explained — how splitting data across many databases scales storage and writes, choosing a shard key, range vs hash vs geo strategies, the hotspot and cross-shard query problems, resharding, and why you should shard late.
🔵 Fundamentals · Fresher-friendly → 🔴 goes deep
Replication makes copies of your database, which scales reads beautifully. But it doesn't help when the data itself grows too big for one machine, or when writes overwhelm a single primary — because every copy still holds everything and takes every write. The answer to that problem is sharding: splitting your data across many databases, each holding just a slice. It's the most powerful scaling technique — and the one with the sharpest trade-offs. Let's make it click.
The problem sharding solves
Say your users table has grown to 5 billion rows — too big to fit or perform on one server, no matter how beefy. Vertical scaling has hit its ceiling; replication won't help because each replica would still need all 5 billion rows and still funnel writes through one primary. You've run out of single-machine road.
Sharding's move: split the users across, say, 10 databases — 500 million each. Now each machine holds a manageable slice, and writes spread across all 10 primaries instead of hammering one.
The most important decision: the shard key
🟠 Everything hinges on the shard key — the column used to decide which shard a row goes to (e.g. user_id). Choose it well and load spreads evenly; choose it badly and you get a hotspot — one poor shard doing most of the work while others sit idle. A good shard key has high cardinality (many distinct values) and spreads both data and traffic evenly. This single choice makes or breaks a sharded system, which is why interviewers dig into it.
How to split: sharding strategies
| Strategy | How | Watch out for |
|---|---|---|
| Range | By key ranges (A–F, G–M…) | Uneven ranges → hotspots |
| Hash | hash(key) % N → shard | Even spread, but resharding is painful |
| Geo / directory | By region, or a lookup table | Lookup table is one more thing to run |
Hash sharding is the most common because it spreads data evenly by default. But it has a nasty edge: hash(key) % N depends on N (the shard count), so adding a shard changes almost every row's target — a massive reshuffle. The elegant fix is consistent hashing, important enough to get its own article: consistent hashing.
The price you pay (there's always a price)
🔴 Sharding unlocks near-unlimited scale, but it makes your database meaningfully harder to work with:
- Cross-shard queries hurt. "List the 100 most recent users overall" now means asking every shard and merging results — slow and complex. Queries that stay within one shard are fast; ones that span shards are the enemy. You design your shard key to keep common queries on a single shard.
- Joins across shards are painful. Data that used to join in one query may now live on different machines. Teams often denormalise (duplicate data) to avoid cross-shard joins.
- Transactions get hard. A transaction touching multiple shards is a distributed transaction — a genuinely thorny problem.
- Resharding is operationally scary. Splitting a hot shard or adding capacity means moving huge amounts of live data without downtime.
The golden rule: shard late
🟠 Because of all that pain, the senior wisdom is don't shard until you truly must. First exhaust the easier wins: a bigger machine, read replicas, caching, better indexes. Sharding is the tool you reach for when the data genuinely won't fit or writes genuinely won't fit on one primary — not a day sooner. In an interview: "I'd only shard once we've outgrown a single primary even with replicas and caching, and I'd pick a shard key that keeps our hottest queries on one shard." That restraint is the senior signal.
Replication + sharding together
In real large systems you combine both: shard the data across many machines and replicate each shard for read scaling and availability. So a shard isn't a single server — it's a small primary-replica cluster of its own. That's how the biggest databases on earth are built: many shards, each replicated. (New to replication? See database replication.)
What to read next
- Consistent Hashing — sharding without the resharding nightmare
- The CAP Theorem — the law behind distributed data trade-offs
- Distributed Transactions & Saga — writing across shards safely
- ← The complete System Design guide (hub)
Frequently Asked Questions
What is database sharding?
Sharding splits one large database into many smaller databases called shards, each holding a different portion of the data. No single machine holds everything, so together the shards store more data and handle more writes than any one server could, which is why it is used when data or write volume outgrows a single primary.
What is a shard key and why does it matter?
The shard key is the column used to decide which shard a row goes to, such as user_id. A good shard key spreads data and traffic evenly across shards. A poor one creates a hotspot where one shard does most of the work while others sit idle, so this choice makes or breaks a sharded system.
Why should you avoid sharding until necessary?
Sharding makes the database much harder to use: cross-shard queries and joins become slow and complex, transactions across shards are hard, and resharding live data is operationally risky. So you first exhaust easier wins — a bigger machine, read replicas, caching, better indexes — and shard only when data or writes truly no longer fit one primary.
