Partitioning and sharding
Sharding splits a large dataset across several databases (shards), each holding part of the data.
Why it matters
Even the biggest single database has limits on storage and write speed. When your data or writes outgrow it, you have to split the data across machines.
The idea
When one database can't handle the writes or the data size, you split it by a shard key. Range sharding (A–M, N–Z) keeps neighbouring keys together but can create hot spots. Hash sharding spreads data evenly but makes range queries harder. The shard key is the biggest decision: it decides balance, which queries stay on one shard, and how hard it is to add shards later.
A library too big for one building, split across several buildings: by the author's first letter, or by a code that spreads the books evenly.
Library visitors can walk between buildings. Queries that need data from many shards (joins, global counts) are slow and complicated, so you choose the shard key around the main queries.
How it works
- Choose a shard key that matches your main query, such as user_id.
- A routing rule maps each key to a shard: for example, shard = hash(user_id) % 16.
- The application, or a proxy, uses that rule to send each query to the right shard.
- Queries by user_id hit one shard and are fast. Queries across all users must ask every shard and merge the results, which is slow.
- To add capacity, split shards or move some keys. Consistent hashing, or many small logical shards, makes this much easier.
Example
An Instagram-like app shards user data by a hash of user_id, so one user's posts all live on the same shard and 'show my profile' touches one database.
Key points
- A good shard key: many distinct values, an even spread, and it matches the main access pattern.
- Hash sharding: even load, poor for ranges. Range sharding: good for ranges, risk of hot spots.
- Hot keys (a celebrity) can overload one shard: split them further, or cache them.
- Cross-shard joins and transactions are hard: design so you don't need them.
- Resharding is painful; consistent hashing or many small logical shards make it easier.
- Shard only when needed: try caching, read replicas and a bigger machine first.
Common mistakes
- A shard key with few values (like country), which creates huge, uneven shards.
- A time-based key that sends every new write to the same shard.
- Designs whose common requests need joins or transactions across shards.
Not true: Shard key: country is a good one, since each user has exactly one.
In fact: A key with few values makes huge, uneven shards: one big country can overload its shard. A good shard key has many distinct values, spread evenly.
Not true: Time-based shard key: new writes are spread evenly across the shards.
In fact: New rows all carry the latest time, so they all land on the same shard. That shard becomes a hot spot while the others sit idle.
In the interview
Once writes or data outgrow one database, I'd shard by a key that matches the main query. Here that is user_id, hashed for an even spread, so a user's data lives on one shard. I'd use consistent hashing or many logical shards to make adding machines easier, and handle very hot users with caching.
Part of Unit 3: Data: databases, storage and scaling data in the free System Design course.