Database Sharding

Build the mental model

Replication multiplies read capacity, but every replica still holds a full copy of the data — once a dataset outgrows what any single machine can store or serve even for writes (a single leader has finite disk and write throughput), replication alone can't help. Sharding (horizontal partitioning) fixes this by splitting one logical table into multiple physical pieces, called shards, each living on a separate database instance, using some shard key to decide which shard a given row belongs to — for example, splitting by a hash of user ID, or by a range of IDs. This lets total storage and write throughput grow roughly linearly by just adding more shards. The danger is picking a naive shard key: sharding sequential auto-increment IDs by range sends all new writes to the newest (last) shard while older shards sit idle, and sharding by something like 'country' overloads the shard holding a hugely popular country while others are nearly empty — both create a hotspot, one overloaded shard that becomes the new bottleneck the whole sharding effort was meant to avoid.

Connect it to a real scenario

Once the Tutorial Platform has tens of millions of learners, the enrollments and activity tables outgrow what a single leader can hold and write to, even with replicas. The team shards by hash(user_id) across many database instances — this spreads users roughly evenly so no shard is overloaded. Early on, someone proposed sharding by course_id instead, since course data seemed simpler to split — but with millions of learners piling into a handful of viral courses (like an introductory Python course), that would create a severe hotspot on the popular courses' shards while niche-course shards sat nearly idle.

Sharding splits one table across many database instances using a shard key.
Sharding becomes necessary once a dataset outgrows a single machine's storage or write capacity.
A poor shard key (sequential IDs, a popular single value) creates hotspots — one overloaded shard while others sit idle.

Try the working example

text

BEFORE (one giant table)              AFTER (sharded by hash(user_id) % 3)
+----------------------+               +---------+ +---------+ +---------+
| enrollments (100M)   |               | Shard 0 | | Shard 1 | | Shard 2 |
| all on ONE machine   |   ------>     | ~33M    | | ~33M    | | ~33M    |
+----------------------+               | rows    | | rows    | | rows    |
     write bottleneck                  +---------+ +---------+ +---------+
     storage ceiling                    evenly spread, no bottleneck

BAD SHARD KEY EXAMPLE (by country):
+---------+ +---------+ +---------+
| Shard 0 | | Shard 1 | | Shard 2 |
| "US"    | | "MM"    | | "NZ"    |
| 60M rows| | 35M rows| | 5M rows |  <- HOTSPOT on Shard 0
+---------+ +---------+ +---------+     (overloaded, others idle)

You should see

The diagram shows a shard key like hash(user_id) spreads data evenly, while a skewed key like country creates a hotspot on one shard.

5-minute try-it

Suppose the Tutorial Platform shards its quiz_attempts table. Compare (a) sharding by auto-increment attempt_id vs (b) hash(user_id), and explain which is more likely to create a hotspot.

One important caution

Using sequential auto-increment IDs as a range shard key sends all new writes to the newest shard, leaving others idle.

Picking a shard key based on what looks 'logically clean' rather than actual real-world data distribution, where one popular value can dominate a huge share of the data.

Wikipedia — Shard (database architecture) — System Design