System Design · Architecture under constraints
How does database sharding work and what are the pitfalls?
Short answer
Sharding splits data across independent databases by a partition key. It increases write capacity but complicates cross-shard queries, transactions, secondary indexes and resharding.
Sharding is horizontal partitioning across separate database instances. Each shard holds a subset of rows and has its own CPU, memory and disk, so write capacity grows with the number of shards. The cost is that the application must route queries and accept that some operations span shards.
Choosing a partition key
- Hash-based: uniform distribution, but range queries scatter across shards
- Range-based: efficient range scans, but sequential keys create a hot shard
- Directory-based: a lookup service maps keys to shards, flexible but another dependency
- Entity-based: shard by tenant or user so related data stays together
- Geographic: keep data near users, but can create uneven shard sizes
The key should match the dominant access pattern and distribute both data and load. Sharding by user id works when most queries are per user. Sharding by timestamp concentrates writes on the newest shard and is usually a bad default. A good key also keeps multi-entity operations on one shard, such as all messages in a conversation.
The costs
- Cross-shard joins become application-side fan-out or a denormalized read model
- Transactions across shards need two-phase commit, a saga or a redesign
- Secondary indexes are local, so a query may have to scatter and merge
- Resharding requires dual writes, backfill and a careful cutover
- Hot keys can overload one shard even when the hash is uniform
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1How do you handle a query that must read from every shard?
- 2What is a hot shard and how do you mitigate it?
- 3How would you reshard without downtime?
- 4Why is sharding by timestamp usually a poor choice?
Say it in the interview
Sharding splits data across independent databases using a partition key, which adds write capacity but makes cross-shard queries and transactions expensive. I pick a key that distributes load and keeps the common access pattern local, such as user id or tenant id rather than timestamp. I plan for hot keys, scatter-gather reads and a resharding path before committing. I only shard after replicas, caching and query tuning have been tried.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live System Design mock interview and get scored feedback on depth, clarity and confidence.