When one database isn’t enough
Traffic grows, the database grows with it. You upgrade the instance — more CPU, memory, disk. That works until it doesn’t. Queries slow, writes bottleneck, storage approaches the ceiling. Even large cloud engines (e.g. Aurora-class) top out around hundreds of TiB on a single primary story.
When one machine can’t keep up, the real option is: split the data across multiple machines. That’s sharding — necessary at scale, and full of new failure modes.
One box vs many machines.
Cardinality · evenness · query fit.
Range · hash · directory.
Hot spots · fan-out · consistency.
First: what is partitioning?
Partitioning splits a large table into smaller pieces inside one database. No new machines — just organization so scans and maintenance touch less.
An orders table with 500M rows / ~2TB: “last month’s orders” shouldn’t walk the whole heap. Partitions (e.g. by year) let the planner prune. Vacuum/analyze/index rebuilds can target a partition instead of locking everything.
- Horizontal — split rows (e.g. one partition per year). Same columns, fewer rows each.
- Vertical — split columns (hot fields vs bulky rarely used ones). Same row identity, thinner pieces.
What is sharding?
Sharding is horizontal partitioning across machines. Each shard is its own database — CPU, memory, disk, connection pool. Together they hold the full dataset. No single machine owns all the data or all the traffic.
Sharding strategies
Range-based
Assign continuous ranges: users 1–1M → shard 1, 1M–2M → shard 2, …. Simple, great for range scans. Uneven real traffic: sharding by time dumps writes onto the newest range. Multi-tenant SaaS with ID ranges per tenant can work when each tenant stays in its band.
Hash-based (default)
shard = hash(user_id) % N scrambles keys so new users spread evenly. Changing N with simple modulo remaps almost everything — that’s why interviews pair hash sharding with consistent hashing. Interviewers usually assume hash unless you say otherwise.
Directory-based
A lookup table maps key → shard. Maximum flexibility (move a hot user to a dedicated shard). Cost: every request looks up first; the directory is a critical dependency / SPOF. Rarely the interview default — expect follow-ups if you lead with it.
Challenges of sharding
Hot spots
Even with a good key, one shard can carry disproportionate traffic — the celebrity problem. Hashing doesn’t help when one key is inherently hot. Time-based shards make “today” the write hotspot.
- Isolate hot keys — dedicated shard / special routing (directory helps here).
- Compound keys — e.g. spread a user’s data over time:
hash(user_id + date)when that matches access. - Split/migrate — MongoDB balancer moves chunks; Vitess online resharding is operator-driven, not magic.
Cross-shard operations
“Top 10 posts globally” on a user_id shard layout fans out to every shard, then merges. N× network and latency. Minimize: cache aggregates, denormalize related data onto one shard, or accept slow rare admin queries. If a common path needs all-shard fan-out, rethink the key or precompute.
Consistency
Single-DB transactions don’t span independent shards. Prefer keeping a user’s data on one shard so local ACID still works. For true multi-shard workflows, use sagas (steps + compensations) or accept eventual consistency. Avoid leading with two-phase commit in interviews — slow and fragile; most production systems design around needing it.
Sharding in modern databases
You rarely build sharding from scratch. Pick a store and name the partition key:
- Cassandra — partitioner + vnodes (consistent-hashing style tokens).
- DynamoDB — hash of partition key; internal split/merge (not a user-visible hash ring).
- MongoDB — range chunks on the shard key (hashed key → ranges over hash space); balancer migrates chunks.
- Vitess / Citus — sharding layers in front of MySQL/Postgres; online resharding with ops involvement.
- Spanner / distributed SQL — built-in distribution; still reason about keys and hotspots.
Sharding in the interview
Don’t shard prematurely. Establish why one DB fails first — storage, write QPS, or read load beyond replicas. Use Numbers to know and scalability.
- Propose the key from access patterns — “Most queries are user-scoped → shard by
user_id.” - Choose distribution — “Hash-based with consistent hashing for even spread and cheaper reshard.”
- Name trade-offs — global trending needs cache/precompute, not live fan-out.
- Plan growth — start with enough shards (e.g. 64) and a story for adding more.
Shard key clinic
Resharding options
- Consistent hashing — minimize move.
- Double-write + backfill — safer cutover.
- Directory/lookup service — flexible but hotspot risk.
Mention downtime budget and dual-read validation.
Failure modes to mention
Call out at least one dependency failure (DB down, cache stampede, queue lag, region outage) and your mitigation (timeouts, retries with jitter, degraded mode, circuit breaker).
Cost and performance levers
Interview Q&A by level
Practice saying these out loud for sharding. Interviewers grade clarity and judgment more than buzzwords.
Match depth to the bar: define → trade off → operate. Don't dump principal answers in an entry-level screen.
Wrapping up
Sharding is what you do when a single database can’t handle scale. Two decisions dominate: a shard key aligned with queries, and a distribution strategy that spreads load. Get them wrong and you buy hotspots and expensive fan-out.
- Bring up sharding after a real bottleneck.
- Default: hash by a high-cardinality, query-aligned key.
- Design to keep related data (and transactions) on one shard.
- A well-tuned single database goes surprisingly far — don’t shard early.
Pair with caching (often cheaper than sharding for reads), Scaling writes (full write ladder including when to shard), and data modeling (keys that match APIs).