Layer 3 · Go deeper · 6 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 5 steps
Two signups, one email
Asha taps “Create account” on her phone and laptop at once. First with only an app check, then with a unique index.
Step 1 of 5
Check, then insert
At READ COMMITTED both SELECTs run before either insert commits, so both see nothing and both insert. REPEATABLE READ fails the same way, because each snapshot predates the other’s insert.
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
“Users keep ending up with two accounts for one email after double taps. How do you make that impossible?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“A host may keep old payout accounts but have at most one active. How do you enforce that?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“On PostgreSQL, two guests book overlapping nights in the same room at the same moment. What stops a double booking?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“A property has 20 identical Deluxe rooms. How do you guarantee at most 20 Deluxe bookings per night?”
Your move. Pick an approach:
Build-up · 5 steps
Add a CHECK to a busy table
The bookings table holds 500M rows of about 200 bytes, 100 GB, and takes 2,000 writes a second. The rule is amount_paise >= 0.
Step 1 of 5
The one-step trap
A plain ADD CONSTRAINT … CHECK takes ACCESS EXCLUSIVE, which blocks even SELECT, then scans 100 GB at 500 MB/s. That’s 200 seconds, with 400,000 writes queued or timed out.