Step 1 · The pattern
Split the table across shards
One database cannot hold everything, so rows are spread over three shards. A shard key in each row decides where it lives. Here the key is the workspace ID.
- query
- answered
In short
Shard key choice: how it works and how it breaks
Sharding splits one big table across several databases. A shard key, a column in every row, decides which shard a row lives on. When a query includes the shard key, the router sends it to exactly one shard. When it does not, the router cannot know where the rows are, so it sends the query to every shard and merges the answers: a scatter-gather query. Each one does a full share of work on every machine. A cluster meant to split the load now multiplies it, and it gets worse with each shard you add. So the right shard key is the column your most frequent queries filter by, typically the tenant, workspace or user that owns the data. It should also have many distinct values so no shard runs hot. Changing a shard key later means moving all the data.
How this comes up in interviews
"Your messages table no longer fits on one database. How do you choose a shard key?"
Then they ask
- A common query does not include the shard key. What does the router do, and what does that cost?
- One tenant holds a large share of all rows. What breaks, and what are your options?
- You chose the wrong key. What does changing it involve?
A strong answer covers
- The shard key decides which shard a row lives on, and a query that includes it goes to exactly one shard.
- A query without the key goes to every shard and the answers are merged: scatter-gather.
- Scatter-gather makes every shard do the work, and it gets worse with each shard you add.
- Choose the column the most frequent queries filter by, typically the tenant, workspace or user that owns the data.
- The key also needs many distinct values so no shard runs hot, and changing it later means moving all the data.
Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.
On one page: the track cheat sheet.
Sources: Notion: Herding elephants, lessons learned from sharding Postgres ↗ · Figma: How Figma's databases team lived to tell the scale ↗ · Pinterest: Sharding Pinterest, how we scaled our MySQL fleet ↗ · Report a problem
Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.