Database Sharding and Partitioning, Explained for System Design

You said "shard." The interviewer's first follow-up is "what's your key?", and there are nine more waiting behind it. Sometimes the smartest answer is "we don't."

(The plate is your database. Notice one shard is way bigger than the others. That's your hotspot, and it's the whole problem in one photo.)

"OK, You Said Shard. What's Your Key?"

I've run maybe two hundred system design interviews. The fastest way to fail mine is to say "we'll just shard the database" like it's a checkbox item.

The follow-up is always the same: what's your shard key? Meaning, which field in your data decides which machine each row lives on. And the whiteboard goes quiet.

That's question one. The others are already loaded: what happens to your joins? Who generates unique IDs now? What about the customer that's 100x bigger than the rest? What's the plan for re-splitting the data later? Four more in that vein, and then the killer. Why not just buy a bigger box?

Sharding is a commitment to ten new problems, and the interviewer wants to know whether you've met any before. Getting it wrong burns whole quarters. Two companies in this piece did it in public; one had to do it twice.

Partitioning Splits Tables. Sharding Splits Your Life.

The tutorial definition is true and useless: partitioning splits a table inside one database instance, sharding spreads data across many machines. The version that matters is about what you keep.

Partitioning is a storage layout decision. One machine, one failure domain (one thing that can die), and your transactions, joins, and foreign keys all keep working. Postgres does it natively:

-- Partitioning, not sharding. One box. Transactions and joins still work.

CREATE TABLE events (

id bigint,

workspace_id uuid,

created_at timestamptz

) PARTITION BY RANGE (created_at);

-- Each quarter of data becomes its own chunk under the hood:

CREATE TABLE events_2026_q1 PARTITION OF events

FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

-- Queries skip chunks they can't match (called pruning), and

-- deleting old data is one instant DROP instead of a DELETE

-- that chews the table for a week:

DROP TABLE events_2026_q1;

One catch people miss: a unique index on a partitioned table has to include the partition key, and Postgres refuses to build it otherwise. Your migration runs for six hours, reaches the index it can't create, and rolls the whole night back.

Sharding is an architecture decision, the kind that quietly rewrites your job description. The moment data lives on two machines, cross-shard transactions, joins, and global uniqueness are gone. Concretely: two users sign up with the same email on two different shards, and both INSERTs succeed, because neither machine can see the other. Nobody gives you those guarantees back.

(Same data split two ways. The left keeps one failure domain and full SQL. The right buys write scale and quietly removes joins, transactions, and constraints.)

Picking a Shard Key: Three Options, All Painful

Every routing strategy is a bet on your access pattern, and I've watched that bet lose three different ways.

Hash sharding. Run the key through a hash function and use the result to pick a machine. The simple version is hash(key) mod N; the better version deals out ranges of hash values instead, because a range can move to a new machine later without re-hashing anything. Hold that thought for the ring section. Data spreads evenly and single-row lookups stay cheap. The loss: queries that scan a span, like "last 7 days of orders," die, because neighboring rows now sit on different machines. I only reach for hash when the top queries fetch one thing by one ID, like a profile or a session.

Range sharding. Rows stay sorted, so span queries stay cheap. The trap is that "sorted" usually means "sorted by time," and then every new insert lands on the newest shard while the others nap.

I carry a scar from this one. At a previous company we range-sharded an events table on created_at across eight shards. During a marketing push the p99 on event writes, the time the slowest 1% of writes took, went from 15 milliseconds to over a second, with shard eight pinned at 100% CPU and the other seven near idle. The week-one band-aid was embarrassing. We doubled the instance size and shoved reporting onto a replica, which bought us about a month.

"So we paid for eight databases to run one?"

Our head of infrastructure read that line off the postmortem slide, and nobody laughed. The real fix: dual-writing every new event to both the old layout and a new hash-keyed copy, a backfill copying the history for days, and a verification pass before we dared cut over. Most of a quarter, gone. Range-shard when you scan more than you write, and never on a key that only ever goes up, like a timestamp.

-- Pseudo-DDL. No database ships this literal syntax

-- (Citus, Vitess, and Mongo each spell it differently).

-- It looks reasonable in the design review. It melts on day one:

-- every INSERT carries NOW(), so 100% of writes hit the newest shard.

SHARD BY RANGE (created_at);

Directory sharding. A lookup table maps each key to a shard, so moving a tenant is one UPDATE. The catch is that this little table now sits in the path of every request in the company, and the day it lags, everything starts throwing 500s together. So you cache it hard and guard it like prod secrets.

The Celebrity Problem

Interviewers love this one. Hash sharding spreads keys evenly, not load, because one key still lives on exactly one shard no matter how popular it gets. There are two different fires here, and candidates blur them constantly.

Fire one is reads. Forty million people load the same account page, and the one shard that owns it melts. Cache handles this, provided you collapse the stampede of identical requests into one database fetch when a hot key expires; a celebrity profile is the most cacheable object on the internet.

The second fire is the one no cache can touch. Forty million likes and replies aimed at one account are forty million writes, and each one has to land somewhere. That's when you split the key itself, by tacking a random suffix onto it:

Salting one hot key into 16 sub-keys.

def write_shard(user_id, n_buckets=16):

salt = random.randint(0, n_buckets - 1)

return f"{user_id}#{salt:02d}" # writes now spread across 16 shards

Don't salt everything. Flip this on per key, when its write rate

crosses what one shard can take. The cost: reading that user now

means querying all 16 buckets and merging, so only whales get this.

Discord fights the neighboring problem, one chat channel growing without limit, by bucketing channel messages into time windows. Directory sharding gives you a third move here. Park the whale tenants on their own hardware and stop pretending they're normal.

One rule outranks all three strategies. The shard key must show up in the WHERE clause of your top queries. Any query missing it becomes a scatter-gather: the database asks every shard and merges the answers. That's not a query anymore. That's an incident.

So Node 3 Just Died. What Moves?

Naive modulo is how the node count gets baked into every key's address:

shard = hash(user_id) % 5 # the 5 is now load-bearing

Go from 5 nodes to 4 and roughly 80% of keys map somewhere new,

because almost every remainder changes. Your cache goes cold and

every database gets stampeded at once, on the worst possible day,

since node counts only change when something already broke.

Consistent hashing shrinks the blast radius, and it's small enough to hold in your head mid-incident. Picture the hash space, 0 to 2^32, bent into a circle. Keys and machines both get hashed onto it, and a key belongs to the first machine you meet walking clockwise. That's the whole thing.

Now run the failure. Five nodes on the circle, node 3 drops at peak. One arc moves: the keys sitting between node 2 and node 3 slide clockwise onto node 4. About a fifth of the data relocates, and the other four fifths never notice anything happened. With modulo, every cache in the fleet goes cold at once.

Caption: (Node 3 dies. Only its arc, about one fifth of the keys, slides to node 4. Modulo hashing would have reshuffled nearly everything on this ring.)]

Except node 4 just inherited a double serving, and if it tips over, node 5 gets triple. Cascading failure by design. The fix is virtual nodes. Each machine appears on the circle 100 to 256 times under different names, so a dead node's load sprays across the fleet in small slices instead of landing on one neighbor. Cassandra calls these vnodes; DynamoDB plays the same trick with partitions.

What Breaks: Notion Did This Twice

Cross-shard joins die first. I watched a teammate ship an innocent ORM include that queried every one of our shards at once, and the latency graphs looked like a seismograph until the flag came back off. So you either duplicate data so each shard is self-sufficient (denormalizing) or you join in app code, and either way you now maintain what the database did for free. Auto-increment goes next, replaced by Snowflake-style IDs, which pack a timestamp plus machine bits so two shards can never hand out the same number. Schema migrations turn into rolling deploys across every shard. Backups stop being one snapshot. Distributed transactions? Most teams quietly design around them.

The tax that hurts most is resharding, re-splitting live data across more machines. Look at Notion's numbers.

In 2021 they split their Postgres monolith by workspace ID, since everything a team touches lives together and most queries stay on one shard. They built 480 logical shards across 32 physical machines. A logical shard is a bucket the routing layer knows about, not hardware you pay for; Notion made each one a Postgres schema, basically a named folder inside a database. Why 480? It divides evenly by 48, 60, 80, 96. They were picking their future machine counts before buying the machines. The senior move is overprovisioning logical shards on day one, so resharding means moving folders between boxes and nobody ever rewrites a key.

By 2023 those 32 boxes ran hot, so they went to 96 machines, 15 schemas per box down to 5. The execution was pure paranoia, the good kind. Postgres logical replication, where the old databases stream every change to the new ones, kept both sides in sync, with one filthy trick: skipping index builds until after the copy, which cut sync from three days to twelve hours. Then "dark reads," sending the same query to old and new and shipping the mismatch rate to Datadog. They didn't cut over until that rate sat at zero for days, with PgBouncer pausing traffic for seconds.

Slack hit the same wall with different tooling. Their webapp ran hand-rolled team sharding on MySQL, shard map hardcoded in application logic, and their biggest customers kept outgrowing whatever host they were pinned to. Moving onto Vitess took years, and the payoff was resharding they could run on a Tuesday, without a project codename.

Months of staff-engineer time, at two companies that already knew what they were doing. That's the real price tag on "we'll just shard."

When NOT to Shard (Most of You)

Nobody selling you a distributed database will tell you that a single well-tuned Postgres serves tens of thousands of queries per second. It does. On RDS that's one db.r6i.32xlarge with 128 vCPUs and a terabyte of RAM, rentable this afternoon. It runs five figures a month, which sounds painful until you price the distributed-systems team it replaces. Most companies never outgrow that box.

So before anyone says shard, walk the ladder:

Find the real bottleneck. Most "we need to shard" tickets are one missing index wearing a trench coat.
Read replicas, read-only copies of the database, for read-heavy load. Boring. Works.
PgBouncer, a connection pooler, because your problem might just be 5,000 idle connections.
Partitioning for query pruning and cheap deletes on huge tables.
Cache the hot 1%.
Then, only then, shard. Pick a tenant-shaped key, meaning everything one customer touches lives together. That's why Notion chose workspace ID and Slack chose team ID.

If the dataset fits on one machine you can buy, buy the machine. The machine does not need standups.

Interviewers dig here deliberately. Premature sharding is the tell that you've read the blog posts but never paid the bill.

The Interviewer Ladder

What each level sounds like in the room:

Junior: "We'll shard by user ID." No reasoning, no cost model. The follow-ups end the round.
Mid: "Hash on user_id, because our top three queries all hit one user. The feed query doesn't, so it fans out to every shard, and I'd cap that with a cache in front."
Senior: Covers keeping each customer's data together, then pushes back: "At 5K queries per second this fits one Postgres with replicas. I'd shard when we can name the write ceiling we're hitting, and I'd overprovision logical shards from day one."

Three questions I hear in almost every loop:

"Partitioning vs sharding in one line?" Partitioning rearranges the inside of one box. Sharding is many boxes, and your SQL toys don't come along.

"How many shards do I start with?" More logical than physical, by 10x or more. They cost nothing on day one and they're the escape hatch on reshard day.

"Doesn't Vitess or Citus or Spanner handle this for me?" They handle the routing and the reshard plumbing. The key choice and the hotspots stay yours. So does the missing cross-shard join.

One last warning, because there are no happy endings in this business. Your shard key encodes today's access pattern, and access patterns drift. Resharding is on your roadmap whether you wrote it down or not. Notion's shard-out came in 2021 and the reshard landed two years later. When yours arrives, the only question is whether you left yourself the 480 doors out.

Thank you for reading. If you found this useful, follow PracHub for more practical deep dives on engineering, system design, and interview preparation written to help you build skills that last beyond a single article.