Layer 3 · Go deeper · 5 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 5 steps
One row, three tuples, three snapshots
Asha’s wallet row through two updates on PostgreSQL. Nothing is overwritten; the snapshot alone decides which tuple you get. Transaction IDs and ctids are illustrative.
Step 1 of 5
INSERT stamps xmin
Transaction 100 inserts Asha’s wallet at ₹500. The tuple records its creator in xmin; xmax is 0 because nothing has replaced it yet.
3 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 3
Interviewer
“On PostgreSQL, an orders table’s
statusandupdated_atchange at every delivery step. A teammate wants both indexed. What do you say?”Your move. Pick an approach:
Deep dive 2 of 3
Interviewer
“Dead tuples pile up across your PostgreSQL primary. A REPEATABLE READ report has sat “idle in transaction” since 09:00. What do you do?”
Your move. Pick an approach:
Deep dive 3 of 3
Interviewer
“A long Oracle report keeps failing with ORA-01555, snapshot too old. What do you change?”
Your move. Pick an approach:
Build-up · 5 steps
An old reader walks the undo chain
The same wallet row in InnoDB. The row holds only the newest value, so an old reader rebuilds its version from undo.
Step 1 of 5
Change the row in place
InnoDB keeps one copy of the row, in the clustered index. Transaction 110 changed it in place to ₹300 and stamped its id in DB_TRX_ID.