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 soon does the ledger hit the 32 TB table limit?
Build-up · 6 steps
Follow one UPDATE to COMMIT
One primary, many processes, one shared memory. Watch where a change goes, and what COMMIT actually waits for.
Step 1 of 6
Pool the connections
Every connection is a whole backend process, and max_connections defaults to 100. PgBouncer in transaction mode maps 3,000 app connections onto about 40 server ones.
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
“Assume 50 million payments a day: 40 GB a day, 14.6 TB a year. What do you do about the ledger table?”
Peak is about 2,900 transactions a second. Assume pgbench shows one box sustaining 10,000.
Your move. Pick an approach:
Deep dive 2 of 3
Interviewer
“Hotel booking: two guests must never hold the same room on overlapping dates. How do you enforce it?”
Your move. Pick an approach:
Deep dive 3 of 3
Interviewer
“A busy table has grown to three times the size of its data, and queries are slowing. What do you do?”
Your move. Pick an approach:
Build-up · 5 steps
One row, two versions, no waiting
Postgres never updates a row in place. Follow one balance change from ₹500 to ₹300 while two readers look at it.
Step 1 of 5
One row, one version
Each row version carries xmin, the transaction that created it, and xmax, the one that deleted or replaced it: 0 while it’s live. The balance is ₹500.