Layer 3 · Go deeper · 6 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 6 steps
Follow one UPDATE through both engines
One delivery-status update on an orders table with four indexes, in each engine. status isn’t indexed; updated_at is.
Step 1 of 6
One update, four indexes
An order moves from PICKED_UP to DELIVERED, and updated_at changes too. The table has four indexes, one of them on updated_at; status isn’t indexed.
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
“A new MySQL orders table will reach 500 million rows with 4 secondary indexes. Which primary key do you choose?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Every delivery step updates status and updated_at on a PostgreSQL orders table with four indexes. WAL keeps growing. What do you do?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“You’re porting a clinic’s booking service from MySQL to PostgreSQL. How do you keep “at most three slots per doctor per morning”?”
Today it locks the doctor’s morning slots with SELECT … FOR UPDATE, counts them, then inserts.
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“You need a tip_paise column on a big, busy MySQL orders table. How do you run the ALTER?”
Your move. Pick an approach:
Build-up · 5 steps
Watch the same two transactions diverge
Doctor 7 has slots at 10 and 12, indexed on doctor and hour. T1 locks the late-morning range while T2 inserts an 11 o’clock slot.
Step 1 of 5
T1 locks a range
T1 runs SELECT … FOR UPDATE on doctor 7’s slots between 10 and 12. Both engines return 10 and 12 and lock those rows.