Partitioning and Sharding: A System Design Guide

If you remember five things

The whole answer in one glance. Everything below defends these five calls.

The partition key is the whole game. Pick it from your dominant query so the common read hits exactly one shard. Pick it wrong and you are doing an emergency migration in six months.
Derive shard count from a target size. Target ~1 TB per shard, then check that both reads and writes fit per shard. That math out loud is the skill they are grading.
Consistent hashing, not modulo. Modulo moves nearly every key when you add a shard. Consistent hashing with virtual nodes moves only ~1/N.
Even load fights query locality. Hash spreads load but kills range scans. Range keeps scans but invites hotspots. You compensate, you do not get both free.
Hot keys are a traffic problem, not a data problem. One celebrity cooks a shard that is balanced by size. You salt or cache that key, you do not add shards.

1The core idea#

Split one logical dataset into many smaller pieces so no single piece holds everything. Say the terms once and move on.

Partitioning splits one dataset into smaller pieces.
Sharding is partitioning where those pieces live on different machines.
In an interview just say "I'll partition the data across shards" and define it once. Nobody is grading vocabulary.

Two splits worth separating early.

Vertical partitioning splits by columns or features. Profiles in one store, billing in another. This is really service decomposition wearing a database hat.
Horizontal partitioning splits by rows. Users 1 to 1M on shard A, 1M to 2M on shard B. This is what people mean 95% of the time, and it is what this guide is about.

The mental model I use.

A shard is a tiny, self-contained copy of your schema that owns a slice of the keyspace.
The hard part is never "how do I run five databases."
The hard part is "how does a request find the one shard with its data, and what happens when a query needs two shards at once."

The one decision that dominates everything

The partition key is the most important choice you'll make in the entire design. Pick it well and the system scales almost linearly. Pick it badly and no amount of hardware saves you, because the problem is structural, not capacity. Everything else in this guide is downstream of this one call.

2A worked example with real numbers#

Ground the abstraction before going near it. Say I'm building the storage layer for a social app with these numbers:

500 million users.
Each user's data (profile, settings, last 1,000 posts, follower list) averages 4 KB of hot metadata plus roughly 200 KB of post bodies and media pointers. Call it 204 KB per user.
Total: 500M x 204 KB = roughly 100 TB.
Read load: 2 million reads/second at peak, mostly "fetch user X's profile and recent posts."
Write load: 200,000 writes/second at peak.

Why one box can't hold this.

One Postgres box on NVMe comfortably holds a few TB and pushes maybe 50,000 to 100,000 writes/second on a good day.
100 TB and 200k writes/second blow past a single primary on both axes.
So I have to shard. That is the trigger, not a preference.

The sizing math, done out loud.

Target ~1 TB per shard: keeps backups, restores, and rebuilds under an hour and keeps each shard's working set in RAM.
100 TB / 1 TB = 100 shards.
Reads: 2,000,000 / 100 = 20,000 reads/second per shard. Easy.
Writes: 200,000 / 100 = 2,000 writes/second per shard. Comfortable, with headroom for growth.

The key and placement.

I pick user_id as the partition key, because the dominant query is "give me this one user's stuff," and that now hits exactly one shard.
Placement is shard = consistent_hash(user_id), not modulo (reason in the next section).
Grow from 100 to 125 shards next year and consistent hashing moves only about 1/125 of keys per added shard, not nearly all 500 million rows.

~100 TB

The number that forces the shard count100 TB against a 1 TB target shard gives 100 shards, and 100 shards is what makes both 2M reads/s and 200k writes/s fit per machine. Every other decision on this page hangs off deriving this number, not guessing it.

Why say all this in the room.

I derived the shard count from a target shard size.
I checked that both reads and writes fit per shard.
I tied the key to the access pattern.
That sequence is the whole skill. The scheme is downstream of it.

3How it works#

Every shard scheme answers one question: given a key, which shard? A router applies a function to the partition key and gets back a shard ID.

flowchart LR A[Client] --> B[Router] B --> C[Shard 1] B --> D[Shard 2] B --> E[Shard 3] C --> F[(Users A to H)] D --> G[(Users I to P)] E --> H[(Users Q to Z)]

The router is the only thing standing between a key and its shard. Where you put this router (client, proxy, or data layer) is a trade-off interviewers listen for, because it decides who holds the routing map and who hurts during a rebalance.

Where the router lives (name the trade-off).

In the client or app. The app computes the shard and connects directly. Fast, no extra hop, but every client needs the routing map and gets confused mid-rebalance.
In a proxy. A middle layer like Vitess or a custom router owns the map. Clean, one place to update, costs a network hop and a thing to keep alive.
In the data layer itself. Cassandra and DynamoDB do this. Any node takes the request and forwards it. Simplest for the app, most complex to build.

Now the function itself. Four families.

Range partitioning. Sort by key and cut the keyspace into contiguous ranges (Jan on shard 1, Feb on shard 2). Great for range scans ("all orders in Q3") because they hit few shards. The catch: ranges fill unevenly and recent data is usually hottest, so your newest shard melts while old ones idle.
Hash partitioning (modulo). Hash the key and take it modulo the shard count: shard = hash(user_id) % N. Spreads load beautifully. The problem shows up the day you add a shard: change N and nearly every key maps somewhere new. Going from % 100 to % 101 leaves only about 1% of keys where they were, which on 100 TB is a near-total migration.
Consistent hashing. The fix for modulo's resharding pain. Map both shards and keys onto a ring (say 0 to 2^32). A key belongs to the first shard clockwise from it. Add a shard and only the keys between it and its neighbor move, roughly 1/N of the data. On a 100-shard cluster that is about 1% per added shard instead of 99%.
Directory / lookup-based. Keep an explicit table: this key range lives on that shard. Maximum flexibility (move any slice anywhere) at the cost of a lookup service you now keep fast and highly available. Reach for this when you need to lift a specific hot tenant off a crowded shard.

From the interviewer's chair

When you say "hash partitioning," I am waiting to hear whether you mean modulo or consistent hashing, because they behave completely differently the day you grow. Candidates who say "hash" and stop have usually never had to resize a live cluster. Name consistent hashing and virtual nodes without being asked and I relax.

Why virtual nodes matter.

Each physical shard owns many points on the ring, not one.
Load stays even across shards instead of clumping between two ring neighbors.
When a node dies, its share spreads across all survivors instead of dumping onto one unlucky neighbor.

Pause before you read on: you built on `hash(user_id) % 100` and now need a 101st shard. How much of your 100 TB moves, and why?

Reveal the answer

About 99 TB. Changing the divisor from 100 to 101 remaps almost every key to a new shard, so you reshuffle nearly the entire dataset while serving live traffic. That is the exact pain consistent hashing removes: there, adding the 101st shard moves only ~1/101 of keys, the slice between the new node and its ring neighbor. Reaching for consistent hashing before you are forced to grow is the whole point.

4Trade-offs#

Here is the comparison I sketch on the whiteboard.

Scheme	Even load	Range queries	Cost to add a shard	Best for
Range	Poor, hotspots on recent data	Excellent	Cheap, split one range	Time-series, scans by key
Hash (modulo)	Excellent	Terrible, hits all shards	Brutal, moves ~all keys	Rarely, avoid at scale
Consistent hash	Good with vnodes	Poor	Cheap, moves ~1/N	Key-value, caches, large clusters
Directory	Tunable by hand	Depends on layout	Cheap and precise	Multi-tenant, hot-key relief

You give up cheap joins and transactions.

On one database, a join across users and orders is free.
Once those tables live on different shards, that join is a scatter-gather across the network, or you denormalize so related data sits together.
A transaction spanning shards needs two-phase commit or a saga, both slower and failure-prone.
The honest line: "I'll design the key so common queries stay single-shard, and accept that the rare cross-shard query is expensive."

Even load versus query locality.

Hashing gives even load but destroys locality.
Range gives locality but invites hotspots.
You almost never get both from the raw scheme, so you compensate: salt hot keys, split hot ranges, or add a directory layer on top.

Rebalancing is the hidden cost.

Adding capacity means moving data while serving live traffic. Done wrong it saturates the network and tanks latency.
Picture moving 1 TB off a shard over a 10 Gbps link: that is at least 13 to 14 minutes of flat-out copy before any headroom for real queries, so in practice you throttle it to a trickle and it runs for hours.
Good systems move data in small chunks, throttle the copy, and keep both source and destination serving reads until the handoff is atomic.

You never get even load and query locality for free. Pick which one the raw scheme gives you, then pay to patch the other.

5Where it is used#

Concrete anchors, because interviewers want to hear you've touched the real thing.

DynamoDB and Cassandra use consistent hashing with virtual nodes under the hood. You pick a partition key, they place the data. Cassandra's "wide column" model exists so you can keep everything you query together inside one partition.
Vitess (what powers YouTube's and Slack's MySQL at scale) is a proxy-based sharding layer. Keep MySQL semantics while it routes and resplits shards for you.
Kafka partitions topics by key so all events for a given user land on the same partition and stay ordered. Same idea, different domain.
Postgres with Citus turns a single logical table into many physical shards across worker nodes, chosen by a distribution column.

When to reach for sharding (only when you have to).

First scale up: a bigger box.
Then add read replicas.
Then cache aggressively.
Sharding is the step after those stop working, usually driven by write throughput or dataset size no single primary can hold.
In the worked example, 200k writes/second is what forced it: replicas help reads, not writes, and no cache absorbs writes.
Sharding before you need it is a classic over-engineering tell, and I'll call it out in an interview.

6Common pitfalls and gotchas#

The celebrity / hot-key problem.

Pick user_id and one celebrity with 80 million followers overwhelms their shard while others sit idle.
The data is balanced; the traffic isn't.
Fixes: split that user across sub-keys (celebrity#0 through celebrity#15 spread across 16 shards), or cache that key hard in front of the shard.

Choosing a low-cardinality key.

Sharding by country sounds clean until one country is 60% of your users.
Cardinality and distribution both matter.
A good key has many distinct values and roughly even traffic across them.

Resharding under modulo.

Build on hash % N and need to grow, and you face a near-total data move: % 100 to % 101 relocates about 99% of keys.
I've seen teams run frozen for a weekend over this.
Use consistent hashing or a directory from day one if growth is likely.

Cross-shard queries sneaking in.

The query you forgot about ("show me all orders in this city") ignores your partition key and fans out to every shard.
One of these on a hot path can quietly cost you your whole scaling win.
Audit real query patterns against your key before committing.

Monotonic keys on range partitions.

Sharding time-series by timestamp means every write goes to the newest shard.
All 200k writes/second concentrate on one machine while the other 99 idle.
Prefix the key with something higher-entropy, or hash the leading bits.

Secondary indexes.

An index on a non-partition field is itself a distributed-data problem.
Local indexes are fast to write but need scatter-gather to read.
Global indexes read cleanly but add write amplification.
No free lunch, just pick which pain you want.

Red flag that fails you here

Answering "a shard is on fire" with "add more shards." Adding capacity does nothing for a hot key: the celebrity's data still lands on one shard after you scale out, because the problem is traffic to one key, not total size. Diagnose skew versus hot key first, then salt or cache. Candidates who reach for raw capacity have not actually fought this at 2am.

7How it shows up in interviews#

The question is almost never "what is sharding." It's embedded: design a URL shortener, a rate limiter, a feed, a chat system. The moment the numbers get big, I'm watching whether you shard deliberately or by reflex.

"What's your partition key and why?"

Weak: "I'll shard by ID." A column name with no reasoning, no access pattern, no sizing.
Strong: "The dominant query is fetching a user's own data, so I'll partition by user_id to keep that single-shard. 500M users at 204 KB each is about 100 TB, so roughly 100 shards of 1 TB. Cross-user analytics is rare and runs on a separate store or a batch job."
The strong version ties the key to the access pattern, derives the shard count from numbers, and names what it sacrifices. Always start with the queries.

"A shard is on fire, what now?"

Weak: "add more shards." Does nothing for a hot key, since the celebrity's data still lands on one shard after you add capacity.
Strong: "First I'd check data skew versus hot key. If it's one key, salt it across celebrity#0 through celebrity#15 or cache it hard. If the whole shard is hot, split its range or move a tenant off via the directory layer, throttling the copy so I don't hurt live traffic."

"How do you add capacity without downtime?"

Weak candidates don't realize rebalancing is even a problem, and happily say "just spin up a node."
Strong ones move data in small chunks, dual-write or dual-read during cutover, throttle the copy so it doesn't saturate the link, and do an atomic switch of ownership once the copy catches up.

"What breaks when a query spans shards?"

Scatter-gather across the network.
Tail latency: you wait for the slowest of N shards, so p99 gets worse as N grows.
Partial failure: one shard down means an incomplete or failed answer.
The fix: denormalize so the common case stays local.

The strongest signal I listen for

The candidate who asks about access patterns before picking a key. That is the actual job. The scheme is downstream of the queries, and anyone who starts with the queries is someone I'd trust with a real cluster.

Follow-ups the interviewer will ask (and your answers)

"Why not just use `hash % N`? It spreads load fine."

It spreads load fine until you add a shard. Changing N remaps nearly every key, so `% 100` to `% 101` moves about 99% of the data, a near-total migration on 100 TB. Consistent hashing with virtual nodes moves only ~1/N per added shard and keeps load even, so I use that instead.

"Your key is `user_id` but you need 'all orders in a city.' Now what?"

That query ignores the partition key and fans out to every shard, a scatter-gather with tail-latency and partial-failure risk. I don't serve it from the sharded store on the hot path: I denormalize into a city-keyed view, or run it as a batch job or on a separate analytics store. The sharded primary stays tuned for the single-shard common case.

"A celebrity is melting one shard. Adding shards didn't help. Why?"

Because it's a traffic problem, not a size problem. The celebrity's rows still hash to one shard after you scale out, so capacity doesn't touch it. I salt the key (`celebrity#0` through `celebrity#15`) to spread the one user across many shards, or cache that key hard in front of the shard so most reads never reach it.

"Walk me through rebalancing 1 TB off a shard without downtime."

Move it in small chunks with a throttled copy (1 TB over 10 Gbps is 13 to 14 minutes flat-out, so in practice it trickles over hours to leave room for live traffic). Keep source and destination both serving reads during the copy, dual-write new changes, and do an atomic ownership switch only once the destination has fully caught up.

"When would you not shard at all?"

When scale-up, read replicas, and caching still cover you. Sharding is the step after those stop working, usually forced by write throughput or dataset size no single primary can hold. Sharding before you need it adds cross-shard joins, rebalancing, and operational load for nothing, which is a clear over-engineering tell.

What you can now explain in the room

Why the partition key is the one decision that dominates whether the system scales.
How to derive shard count from a target shard size and check reads and writes fit per shard.
Why modulo hashing is brutal to grow and consistent hashing with virtual nodes fixes it.
Why even load and query locality trade against each other, and how you compensate.
Why a hot key is a traffic problem you salt or cache, not a size problem you add shards for.
What breaks on a cross-shard query (scatter-gather, tail latency, partial failure) and how to avoid it.

Next, try the System Design Question walkthrough on designing a URL shortener, where picking the partition key for billions of short codes is the whole ballgame.

← Previous

Database Types: SQL, NoSQL and Beyond

Replication: Leader-Follower and Multi-Leader

Get a prep plan built around your Partitioning and Sharding interview

Tell us the role and your interview date. We will send back a dated, day by day plan. Free.

Thanks. Check your inbox, your Partitioning and Sharding prep plan is on the way.