Layer 3 · Go deeper · 7 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.
How long is a 400 GB table locked while it’s rewritten?
How long does a gentle backfill of 200 million rows take?
Build-up · 6 steps
Rename a column in six small deploys
Renaming customers.phone to mobile with rolling deploys. Every schema state must work with the app version before it and the one after it.
Step 1 of 6
Expand: add the new column
Add mobile as a nullable column, behind a lock timeout: a brief lock, catalog only. Version N doesn’t know it exists, so nothing breaks. Way back: drop it.
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 ADD COLUMN on orders keeps failing with “canceling statement due to lock timeout” after 2 s. What do you do?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Checkout writes to orders all day, and a new query needs an index on city. In PostgreSQL, how do you build it?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“Your hottest MySQL table needs a change InnoDB can only make by copying the table, blocking writes. How do you run it?”
No foreign keys point at the table, and replicas serve reads.
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Orders are leaving the monolith’s database for a new sharded one. How does every new write reach both stores?”
Your move. Pick an approach: