Database Sharding in System Design
Learn how to choose shard keys, avoid hot partitions, route requests, and plan safe resharding in a distributed database architecture.
What you'll learn
Build your mental model
- How to select a shard key from real access patterns rather than cardinality alone.
- Why skew, joins, and resharding make sharding an architectural commitment.
- How to explain a safe migration path in a senior system design interview.
Database sharding divides one logical dataset across independent partitions so a single database boundary no longer owns every read, write, and byte. It is powerful because it changes the scale ceiling; it is costly because every cross-shard assumption becomes an application concern.
Start with the decision canvas, then use the guide to test the model against scale and failure.
Read the conditions before the components.
ATOFF architecture reasoning canvas
Database Sharding in System Design: the mental model
Short answer
Sharding raises data and write capacity by assigning each record to an ownership partition, at the cost of cross-partition simplicity.
Proof signal
A shard key is good only until distribution data proves where it is not.
01 Decision checkpoint
Core model
Select a key that keeps common reads and writes local, spreads expected load, and reflects a stable business boundary such as tenant or account.
02 Decision checkpoint
Pressure test
A popular tenant or uneven range can turn a sound shard scheme into a hot partition, so measure distribution and retain escape hatches.
03 Decision checkpoint
Design move
Resharding requires dual reads or writes, verification, cutover, and cleanup; it is not a one-time routing configuration change.
Say it in the interview
I would begin with the access pattern, name the shard key and hotspot risk, then explain how data moves without losing or double-writing records.
- 1
Application
Shard router
- 2
Shard router
Shard A
- 3
Shard A
Shard B
- 4
Shard B
Shard C
- 5
Shard C
- Application flows to Shard router.
- Shard router flows to Shard A.
- Shard A flows to Shard B.
- Shard B flows to Shard C.
From model to real-world behavior
Shard when a real data boundary is the constraint
Sharding is not the first remedy for a slow query. Indexes, archival, read replicas, caching, schema changes, and vertical growth are often simpler. Sharding becomes appropriate when a single primary cannot sustain a durable write rate, dataset size, or fault-domain requirement despite those measures.
The decision should be anchored in access patterns. If most requests are scoped to one tenant or account, that key may localize reads and writes. If reports routinely span all tenants, sharding by tenant trades operational scale for a harder analytics path.
A shard key is a workload prediction
A good shard key distributes the expected load, keeps common transactions local, and can be derived reliably at request time. High cardinality alone is not enough: a celebrity account or a large tenant can still create a hot partition even when IDs look evenly distributed.
Hashing can spread keys; range partitioning can make range scans and retention easier. Both choices shape future rebalancing. Document the queries that will become expensive across shards before committing to either.
Resharding is the test of the design
Traffic and customer distribution change. A design that cannot move a partition safely eventually turns a successful tenant into an outage risk. Use a stable routing layer, copy data with a checkpoint, dual-read or dual-write only when necessary, verify consistency, then cut traffic over gradually.
Avoid making every service calculate shard placement independently. That spreads routing logic, makes migrations inconsistent, and turns a single data move into a multi-team release.
Staff-level insight: make imbalance measurable and reversible
At staff level, define shard health in terms of write rate, storage, latency, error rate, and maintenance burden—not just row count. Make a hot-key mitigation playbook: split a tenant, introduce a sub-key, dedicate capacity, or change the product behavior that causes unbounded fanout.
The critical operational question is whether a shard move can be rehearsed, paused, rolled back, and audited. A clever partition scheme without a boring migration path is an expensive future incident.
Keep this with you
Key takeaways
- Sharding changes a data-scale boundary but adds application and operational complexity.
- Select a shard key from locality, distribution, and query patterns together.
- A safe resharding plan is part of the initial architecture, not a future detail.
Practice aloud
Interview questions to explore
- 1.Which requests stay local to one shard, and which become cross-shard?
- 2.How will you detect and mitigate a hot partition?
- 3.How do you move one shard without corrupting or losing writes?
Common follow-ups
Frequently asked questions
Does sharding improve read performance?
It can by distributing reads, but its primary value is distributing ownership of data and writes. Read replicas or caching may be a simpler first answer for read-heavy systems.
Is consistent hashing always the right shard strategy?
It helps reduce movement when nodes change, but it is not ideal for every range query, tenant isolation, or regulatory boundary. Choose it only when its distribution and rebalancing properties match the workload.
Need the broader preparation context? Go back to Interview Preparation for behavioral readiness, question practice, and the larger AceTheOffer preparation framework.