Core Concepts
Sharding
Splitting one dataset across many databases when it no longer fits — choosing a shard key, routing, the cost of cross-shard queries, and the cheaper options to exhaust first.
Sharding splits your data across multiple independent databases so that no single machine holds, or handles, all of it. It's the most powerful scaling tool here and the most expensive. Most systems never need it, and good teams postpone it for years.
Before anything else: sharding is a last resort. Read replicas scale reads; sharding is for when the data genuinely won't fit on the biggest reasonable machine, or write volume exceeds what one primary can take.
Try these first
| Option | What it buys you |
|---|---|
| A bigger primary + faster disks | Years, for most apps |
| Archiving cold data | Shrinks the working set |
| Table partitioning (same server) | Splits one huge table into pieces, queries stay simple |
| Moving workloads out | Analytics to a warehouse, search to a search engine |
| A distributed database | Vitess, Citus, CockroachDB shard for you |
If one of those solves it, take it — every one is cheaper than hand-rolled sharding.
The shard key is everything
When you do shard, one decision dominates all others: the shard key — the column used to decide which database a row lives on. Pick the column most of your queries already filter by, so each query touches exactly one shard:
| App | Good shard key | Why |
|---|---|---|
| Social | user_id | Most queries are "this user's data" |
| Multi-tenant SaaS | tenant_id | A company's data stays together |
| Chat | conversation_id | Messages of one chat live together |
Bad keys cause pain: a timestamp sends all new writes to one shard (a hotspot); a low-variety value like country makes lopsided shards. And keep related data together — if you shard by user_id, a user's posts, likes and settings must also carry user_id and land on the same shard.
Route with logical shards
The naive rule hash(key) % N works until you add a database — then almost every key's shard changes, forcing a massive data move. Instead, hash into a large, fixed number of logical shards (say 1024) and map those to physical databases. To add capacity you move whole logical shards between machines; the hash never changes.
key ──hash──► logical shard (0..1023) ──lookup table──► physical DBThe lookup table is small and lives in config or a metadata store. (This is the same problem Consistent Hashing solves for caches.)
Cross-shard queries are the tax
Once sharded, any question that spans shards — "count all users," "search everyone's posts" — must ask every shard and combine the results (a scatter-gather), and it gets slower with every shard you add. You design to avoid these:
- Keep global counts in a separate counter or metadata table.
- Precompute aggregates with background jobs into summary tables.
- Send analytics to a data warehouse instead of querying shards.
- For lookups by a non-shard key (login by email when you shard by
user_id), keep a small global index table mappingemail → user_id, then route.
And cross-shard transactions are effectively gone — design so a single business operation only ever touches one shard.
What to say in an interview
Signal that you know the cost, exhaust the alternatives, then pick a key tied to the access pattern:
"I'd avoid sharding unless the numbers force it — first a bigger box, partitioning, and read replicas. If messages truly outgrow one primary, I'd shard by
conversation_idso a chat's messages stay on one shard and the common 'load this conversation' query hits one place. Global search would go to a separate search index, not a scatter-gather across shards."
Next up: Consistent Hashing — adding and removing nodes without reshuffling everything.