Sharding

The only way to scale writes past one machine, and the decision you'll find hardest to undo.

The story

A team shards its users table across four Postgres servers the simplest way possible:

code

shard = user_id % 4

It works well for a year. Then the four servers fill up and they add a fifth. The formula becomes user_id % 5, and they discover what that means: a user stays on the same shard only if user_id % 4 equals user_id % 5, and that's true for just 4 in every 20 users.

80% of all rows now live on the wrong server. Moving them means weeks of dual-writing, backfilling and checking, all while the database is already at capacity. That's the reason they needed a fifth server in the first place.

When Notion sharded its Postgres in 2021, it avoided this trap from the start. It split the data into 480 logical shards spread over 32 physical databases, keyed by workspace ID. To add capacity, they move whole logical shards to new machines. No row ever has to be re-hashed. 480 was chosen because it divides evenly by many numbers, so the logical shards can be spread evenly across 32, 40, 48, 60 or 96 machines.

The lesson: choose the shard key and the shard-to-machine mapping as if you'll never get to change them, because changing them is the most expensive migration you'll ever do.

The idea

Sharding splits one dataset across several independent databases. Each shard holds a subset of rows and handles reads and writes for them. It's the last rung of the Database Scaling Ladder, the one you reach when writes or data size outgrow a single primary.

Three ways to decide which shard a row goes to:

Strategy	Good	Bad
Range	Range scans are cheap ("orders from last week")	New keys all land on the last shard, so it runs hot
Hash	Even spread	Range queries hit every shard. Plain `% N` reshuffles everything.
Directory	Move any tenant anywhere, isolate big customers	The lookup service is in every request's path

Don't map keys to machines directly

Use a level of indirection: keys → many logical shards → few physical machines.

Adding a machine means moving some logical shards to it and updating the mapping. The key-to-logical-shard formula never changes. Consistent hashing solves the same problem differently: adding a node moves only about 1/N of the keys.

Loading simulation…

Choosing the shard key

This is the decision that matters. A good key has three properties:

High cardinality: many distinct values, so data can spread out. country is a bad key, because India alone could be 40% of rows.
Even load: no single value gets a large share of the traffic.
Query locality: most queries include the key, so they go to one shard.

For SaaS products the answer is almost always tenant/workspace ID: every request is scoped to one tenant, and a tenant's data stays together (Data Locality). For consumer apps it's usually user ID. For queries that don't include the key (login by email), keep a small global lookup table: email → user_id.

What sharding takes away

Cross-shard joins. Gone, or done in application code. Design so they're rare.
Cross-shard transactions. No more simple BEGIN … COMMIT across shards. You need sagas or two-phase commit, or a key choice that keeps transactions on one shard.
Global unique IDs. Auto-increment no longer works across shards. Use UUIDs (v7 sorts by time) or Snowflake-style IDs.
Global queries and analytics. "Top 10 customers by revenue" hits every shard. Feed a separate analytics store via CDC.
Hot keys. One huge tenant or one celebrity can overload their shard no matter how good the hash is. Fixes: move big tenants to dedicated shards (easy with a directory), or split one hot key into several sub-keys (celebrity_id#0…#9) and merge on read.

Loading simulation…

Cheat sheet

Question	Answer
When to shard	Write volume or data size beyond one big primary, after the cheaper rungs
Default strategy	Hash on the key, through many logical shards
Never	Plain `hash % N` mapped straight to machines
SaaS shard key	tenant_id / workspace_id
Consumer app shard key	user_id
Lookups by another field	Global index table: `email → user_id`
Range-heavy queries (time series)	Range on time, plus a hash prefix to spread writes
Hot tenant	Move it to its own shard (directory). Hot key → split it into sub-keys.
IDs	UUIDv7 or Snowflake, not auto-increment
Cross-shard transactions	Avoid by key choice. Otherwise sagas.
Managed options	Citus, Vitess, CockroachDB/Spanner, DynamoDB (shards for you)

Say it in the interview

"I'd shard by tenant_id, since every request is scoped to one tenant, so queries and transactions stay on one shard. Tenants hash into 1,024 logical shards mapped onto 8 physical databases, so adding capacity means moving logical shards, not re-hashing rows. The few largest tenants get dedicated shards, and login uses a small global email → tenant lookup."

Go deeper

SD 1 · Sharding: strategies and resharding in depth.
SD 2 · Chapter 7 — Sharding and Chapter 8 — Consistent Hashing.
SD 3 · Q36 — Database Sharding: the chain of whys.