Database Sharding

Sharding splits one logical database into multiple independent pieces — shards — each holding a subset of the data and running on its own machine. It's the database-specific application of horizontal scaling: instead of one machine holding (and serving all queries against) the entire dataset, many machines each hold and serve a slice of it, so total capacity grows by adding shards rather than by buying an ever-bigger single machine.

Why sharding is necessary eventually

A single database server has a hard ceiling: finite disk, finite memory, finite CPU, finite I/O. Database Scaling covers the earlier, cheaper moves (indexing, read replicas, vertical scaling) — sharding is what's left once those are exhausted and the dataset or write volume genuinely no longer fits on one machine. It solves a problem replication alone can't: a read replica still holds a full copy of all the data, so it doesn't help once the dataset itself is too large for a single disk, or once write volume exceeds what any single leader can absorb.

The central decision: the shard key

Every row has to be assigned to exactly one shard, and that assignment is driven by a shard key — the column (or columns) whose value determines which shard a row lives on. This one decision shapes almost everything else about how the sharded system behaves:

System Design Lab

• Range-based sharding — assign contiguous ranges of the key to each shard (users 1–1000 on shard A, 1001–2000 on shard B). Simple, and makes range queries efficient, but risks hot spots: if writes cluster around one range (e.g. sequentially increasing IDs, or a single celebrity user's activity), one shard takes disproportionate load while others sit idle.
• Hash-based sharding — hash the shard key and use the hash to pick a shard. Spreads load much more evenly, at the cost of making range queries expensive (adjacent keys land on unrelated shards).

Real systems almost always use hash-based sharding via Consistent Hashing specifically so that adding or removing a shard only remaps a small fraction of the data, instead of triggering a massive rebalance across every shard — exactly the problem consistent hashing was designed to solve.

What sharding costs you

Splitting data across machines breaks two things a single-node database gives you almost for free, and both are worth naming explicitly:

• Cross-shard queries and joins get expensive. A query that needs data from multiple shards (a join across two differently-sharded tables, an aggregate across all users) can no longer be answered by one machine — it has to fan out to multiple shards and merge results in application code or a query coordinator layer, which is slower and more complex than a local join.
• Cross-shard transactions lose easy ACID guarantees. A transaction that touches rows on two different shards can no longer rely on a single machine's transaction log — see ACID Transactions — and needs an explicit distributed coordination protocol (like two-phase commit) if strict atomicity across shards is required at all. Good shard key choices minimize how often this comes up, by keeping data that's usually accessed together (e.g. all of one user's orders) on the same shard.

Choosing a shard key well

The practical goal is to pick a key that (a) distributes load evenly across shards and (b) keeps data that's typically queried or transacted together on the same shard, so most requests only ever touch one shard. user_id is a common, effective choice for many applications precisely because most queries are naturally scoped to one user already — a poor choice (like sharding by signup date, or a low-cardinality status field) tends to reintroduce hot spots or force cross-shard queries for common access patterns.

Why this matters in an interview

Naming the shard key — and being ready to explain why it distributes load evenly and keeps related data together — is the single most concrete, checkable detail in a sharding design. It's also worth proactively naming the cost: cross-shard joins and transactions are harder after sharding, and a strong answer shows you'd design the schema and access patterns to minimize how often that actually happens, rather than treating sharding as a free scaling lever.

Sharding a database: pros and cons

Pros

• ✓No single-machine ceiling — capacity scales by adding more shards
• ✓Each shard has its own disk, memory, and CPU, spreading load horizontally
• ✓A well-chosen key keeps most real queries scoped to a single shard

Cons

• ✕Cross-shard joins and aggregates require expensive fan-out and merging
• ✕Cross-shard transactions need distributed coordination to stay atomic
• ✕Resharding (changing the key or shard count) is a significant migration

Further Reading

• MongoDB — Sharding — a concrete, production-grounded explanation of shard keys, chunk distribution, and rebalancing.
• Vitess Docs — Sharding — how a widely used MySQL sharding layer solves shard key selection and cross-shard queries in practice.