Splitting your data across machines. The only way to scale writes past one server — and the decision that permanently constrains every query you’ll ever write.
Prerequisites: Replication, Databases Overview Time to read: ~28 minutes
Replication gave you read scaling. But every write still lands on one leader, and:
Sharding splits the data itself: users 1–1M on shard 1, 1M–2M on shard 2, and so on. Each shard is an independent database holding a subset.
🚨 Sharding is the most expensive scaling decision you can make. It is close to irreversible, it removes capabilities you rely on, and it makes everything operationally harder. Do the estimation first — most systems that think they need it don’t.
Two different things sharing a word.
Vertical partitioning — split by column. Put rarely-accessed or large columns in a separate table or database.
users (id, email, name, created_at) ← hot, small, queried constantly
user_profiles (user_id, bio, avatar_blob) ← cold, large, rarely read
Useful, simple, no distribution problems. It’s really just normalization applied for performance.
Horizontal partitioning (sharding) — split by row. Different rows on different machines. This is what people mean by sharding, and the rest of this chapter is about it.
The choice here determines everything downstream.
Partition by value ranges: A–F on shard 1, G–M on shard 2, and so on. Or by date.
✅ Range queries are efficient — “all orders in March” hits one shard. ✅ Easy to reason about and to add new ranges. ❌ 🚨 Hotspots are almost guaranteed. Partitioning users by surname puts far more on the “S” shard than the “Q” shard. Partitioning by date means all of today’s writes hit one shard while the rest idle — the worst possible distribution for a write-heavy system.
Use when: range queries dominate and you can control the distribution — time-series data where you want recent data isolated, or multi-tenant systems partitioned by tenant.
shard = hash(key) % N.
✅ Even distribution, essentially by definition. ✅ Simple to implement. ❌ Range queries are dead. “All orders in March” now requires querying every shard and merging. ❌ 🚨 Changing N rehashes almost everything. Going from 4 shards to 5 moves ~80% of your data.
That last point is fatal in practice, and it’s why consistent hashing exists — it reduces movement to roughly K/N keys when you add a node. → Consistent Hashing ⭐
Use when: access is by key (which is most OLTP workloads) and even distribution matters most.
A lookup service maps each key to its shard.
✅ Maximum flexibility — move any key to any shard, rebalance arbitrarily, support heterogeneous shard sizes. ✅ Great for multi-tenant systems where one huge customer needs a dedicated shard. ❌ The directory is an extra lookup on every request (cache it) and a single point of failure.
Use when: you need per-key control — most commonly, multi-tenant SaaS.
Partition by region: EU users in Frankfurt, US users in Virginia.
✅ Low latency (data near users), and it’s how you satisfy data residency laws (GDPR, and increasingly national data localization requirements). ❌ Uneven distribution by population; cross-region queries are expensive; users who move are awkward.
Use when: compliance requires it, or latency demands it. Note that compliance requirements make this mandatory regardless of technical preference — worth saying in an interview.
🚨 This is the highest-stakes choice in the entire design. Changing it later means rewriting every row and every query.
A good shard key has four properties:
1. High cardinality. Many distinct values. Sharding by country gives you ~200 possible values
and hopeless skew. Sharding by user_id gives you millions.
2. Even distribution. No value dramatically more common than others.
3. Matches your access pattern. 🚨 The most important one. If most queries filter by
user_id, shard by user_id — then each query hits exactly one shard. If you shard by order_id
but query by user_id, every query fans out to every shard, and you’ve made things worse than
before.
4. Avoids hotspots over time. Sharding by timestamp means all writes go to the newest shard, forever.
Worked example — an e-commerce system:
| Shard key | Verdict |
|---|---|
order_id |
❌ Even distribution, but “show me my orders” queries every shard |
product_id |
❌ Popular products create hot shards; user queries fan out |
created_at |
❌ All writes hit today’s shard; yesterday’s shards idle |
user_id |
✅ High cardinality, even, and matches the dominant access pattern |
🎙️ “I’d shard by user_id. Most queries are ‘this user’s orders,’ so they resolve to a single shard. The trade-off is that ‘all orders for product X’ becomes a scatter-gather, so I’d serve that from a separate read model or the search index.”
That last sentence is the mark of a strong answer: naming what you gave up and how you’ll cover it.
This is the part candidates underestimate.
-- Fine on one database. Impossible across shards.
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id;
Options: denormalize (copy the user’s name into the order — now you must keep it updated), application-side joins (fetch from both, join in code), or keep related data on the same shard.
Moving money between two users on different shards can’t be a single ACID transaction. Your options are two-phase commit (slow, blocking, and it can leave you stuck if the coordinator dies) or the saga pattern (eventual consistency with compensating actions).
Auto-increment IDs collide across shards. You need
distributed ID generation — Snowflake IDs, UUIDs, or a central
allocator. And a UNIQUE constraint on email can’t be enforced by any single shard.
SELECT COUNT(*) FROM users now queries all shards and sums the results. Latency is bounded by the
slowest shard, and with 100 shards you’re exposed to the tail latency of all of them
(tail amplification). Usually answered from a
separate analytics store instead.
Adding shards means moving data while serving traffic.
The technique that solves this: virtual shards (a.k.a. logical shards or slots). Create far more logical partitions than physical machines — say 1,024 — and map logical shards to physical ones.
1024 logical shards → 4 physical machines (256 each)
Need more capacity? → 8 machines (128 each)
You move logical shards, not individual keys. The hash never changes; only the mapping does. Redis Cluster uses 16,384 hash slots for exactly this reason, and every well-designed sharded system does something similar.
🎙️ Mentioning virtual shards unprompted is a strong signal. It’s the difference between having read about sharding and having thought about operating it.
Backups × N. Schema migrations × N (and they must be tolerant of shards being at different versions mid-rollout). Monitoring per shard. Failover per shard. A hot shard needs individual attention.
Even distribution of keys doesn’t mean even distribution of load.
Causes: a celebrity user with 100M followers; a viral product; one enterprise tenant 1000× larger than the rest; a bot hammering one key.
Fixes:
| Fix | How |
|---|---|
| Sub-sharding / key salting | Split the hot key across shards: celebrity:42:0 … celebrity:42:9 |
| Dedicated shard | Give the whale tenant its own machine (needs directory-based sharding) |
| Cache the hot key | Serve it from Redis or a CDN so it never reaches the shard |
| Read replicas per shard | Scale reads on the hot shard specifically |
→ Hot Keys
Client-side. The application computes the shard and connects directly. Fast (no extra hop), but every client needs the logic and config changes must propagate everywhere.
Proxy layer. A router (Vitess, ProxySQL, Citus) sits in front and speaks normal SQL; the application is mostly unaware. Extra hop, and the proxy needs its own redundancy — but vastly simpler for developers. Usually the right answer.
Database-native. The database shards itself: MongoDB sharded clusters, Cassandra, CockroachDB, Vitess, Citus. Least work for you. Prefer this if it’s available.
🎙️ “I’d use Vitess rather than hand-rolling sharding in the application. Application-level sharding means every developer needs to think about shard keys on every query, and you eventually reimplement half a database.”
🚨 Before sharding, exhaust these. An interviewer will be pleased that you did.
| Alternative | Buys you |
|---|---|
| Vertical scaling | Modern machines reach 24 TB of RAM and hundreds of cores |
| Read replicas | If reads are the problem, this is far cheaper |
| Caching | Often 90%+ of load removed for a fraction of the effort |
| Archiving old data | Move 3-year-old rows to cold storage. Often shrinks the working set by 10× |
| Better indexes / query tuning | A single missing index can look exactly like “we need to shard” |
| Moving large objects out | Put blobs in object storage, keep only keys in the database |
| Functional partitioning | Split by service (users DB, orders DB) before splitting by row — much easier |
| A purpose-built store | Time-series data in a TSDB rather than sharding Postgres |
🎙️ “Before sharding I’d want to confirm we’ve exhausted the cheaper options — archiving alone often buys years, and it’s reversible. Sharding isn’t.”
| Gain | Cost | |
|---|---|---|
| Sharding | Unlimited write and storage scale | No cross-shard joins/transactions; rebalancing; N× operations |
| Range partitioning | Efficient range queries | Hotspots, especially on time-based keys |
| Hash partitioning | Even distribution | Range queries become scatter-gather |
| Directory-based | Per-key placement flexibility | Extra lookup; the directory is a SPOF |
| Virtual shards | Rebalancing without rehashing | Slight indirection complexity |
| Proxy layer (Vitess) | Application stays simple | An extra hop and another system to run |
1. Shard something by hand. Two Postgres instances. Write a small routing layer:
shard = user_id % 2. Implement create_user, get_user, and then try count_all_users and
get_users_by_city. Feel how the last two go from one query to a fan-out and merge — and notice how
much application code that requires.
2. Feel the rehash problem. With hash(key) % N, compute which shard 10,000 keys land on for
N=4, then N=5. Count how many moved. It’ll be around 80%. Now implement consistent hashing and count
again — it’ll be around 20%.
3. Try a real sharded system. Run a MongoDB sharded cluster or Citus in Docker. Insert data, then
run explain() on a query that includes the shard key and one that doesn’t. The difference between
“targeted” and “scatter-gather” in the output makes the shard-key lesson concrete.
4. Create a hot shard. Distribute keys with a Zipf distribution instead of uniformly (which is what real traffic looks like). Watch one shard take a disproportionate share of load even though the hash is perfectly even.