Sharding Postgres while it is running
Shard a Live Database Without Downtime, from a blank page
This is how the interview actually runs: one prompt, and you decide what to cover and in what order. Write each section, then compare it with a reference design and see what you left out.
A 45-minute round. You drive; nothing prompts you.
The prompt
A collaborative workspace app stores everything (pages, blocks, comments) in one Postgres primary, already on the largest instance available. The biggest table has billions of rows. Writes keep climbing, autovacuum can no longer keep up, and the database is drifting toward transaction ID wraparound: the point at which Postgres stops accepting writes to protect itself.
Each block lives in a single workspace, and almost every query stays inside one workspace. The infrastructure team is small, and it knows Postgres well.
Notion was in this position in 2020 and wrote up the whole migration, then wrote again in 2023 about growing from 32 to 96 database hosts. Figma described a similar journey in 2024, and Stripe and GitHub have published the patterns for moving live data safely.
What the interviewer would tell you if you asked
- One Postgres primary on the largest instance size; billions of rows in the largest tables.
- Vacuum is falling behind, and transaction ID wraparound is a hard deadline.
- Stay on Postgres: the team's expertise and tooling are built around it.
- Every row can be attributed to a workspace (directly, or through its parent).
- IDs are UUIDs, so rows can be copied between databases without ID conflicts.
- Rows carry an updated-at version that increases on every write.
01
about 5 minWhat does the system have to do, and how well? List the functional requirements, then the non-functional ones (latency, availability, consistency, scale), and the questions you would ask the interviewer.
02
about 5 minTurn the volumes into the numbers that drive the design: requests per second at peak, storage, bandwidth, and anything else that decides whether one machine is enough.
03
about 10 minName the components and what each one is responsible for. Then trace the main request through them, and say where the durable state lives.
04
about 15 minPick the hardest decisions in this design and make them: what you chose, what you rejected, and which constraint decided it.
05
about 10 minWhat breaks? Walk through crashes, duplicates, slow dependencies and overload, and what the design does in each. Then: what changes at ten times the load, or with a new requirement?
Write something in at least 3 sections first. Gaps are fine; the comparison shows what they cost.