Layer 3 · Go deeper · 9 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 big is an index on just the pending orders?
What does the partial index cost on the write side?
Build-up · 5 steps
Tune the dispatcher query, step by step
The tuning loop on one real query: find it, explain it, form one hypothesis, build the index, then verify reads and writes.
Step 1 of 5
Find the top query
pg_stat_statements ranks it first by total time: 17,280 calls a day × 4.1 s ≈ 19.7 hours of database time. Total time, not the slowest single run, is what you pay for.
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
“The dispatcher’s query reads 909,088 pages to return 100 pending orders, every 5 seconds. Which index do you add?”
About 4,000 of 50 million orders are pending. The query filters on status and created_at and sorts by created_at.
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Rider GPS pings: 2 billion rows, 200 GB, appended in time order. Queries fetch one hour by recorded_at. Which index?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“An index shows idx_scan = 0 on the primary. A teammate wants to drop it today. What do you check?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Order IDs are minted by many services across several shards. What primary key do you give the orders table?”
Your move. Pick an approach:
Build-up · 6 steps
Build an index without blocking writes
A plain CREATE INDEX stops every write while it scans. The concurrent build trades a longer, costlier run for writes that never stop.
Step 1 of 6
The plain way blocks writes
A plain CREATE INDEX scans the table once while blocking every INSERT, UPDATE and DELETE; reads continue. On a large table that can mean hours of failed writes.