Layer 3 · Go deeper · 8 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Back of the envelope
Size it up
Tap through each step of the arithmetic.
When do Instagram-style IDs turn negative in a PostgreSQL bigint?
Build-up · 5 steps
Design a workspace-rooted schema
Whichever tool routes the queries, the schema decides whether most of SQL keeps working. Five rules cover most designs.
Step 1 of 5
Pick a root entity
Find the thing most requests are scoped to, a workspace, organisation, restaurant or user. Its ID becomes the shard key, and whole workspaces land on shards, like client folders moved whole.
4 interview moments
You’re the candidate
The interviewer pushes on the hard parts. Pick an approach and see how it holds up.
Deep dive 1 of 4
Interviewer
“Your multi-tenant SaaS has run on PostgreSQL for years, and one primary is nearly full. Where should shard routing live?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Accounts are spread across shards, and no two may share an email address. How do you enforce that?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“You’re splitting 32 hosts into 96, moving whole schemas unchanged. How do new writes reach the new copies during the move?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Product needs a new column on a table that lives on all 480 shards. How do you roll it out?”
Your move. Pick an approach:
Build-up · 7 steps
Move live data without a one-way door
Published accounts differ in tools, not shape. Watch three things in each phase, which copy is the truth, which serves reads, and which way changes flow.
Step 1 of 7
Prepare the key
Populate the shard key in every row and query path, and route through the shard-aware layer while it still points at one database. Notion filled missing workspace IDs on the fly while copying.