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
Which WHERE clauses read one run?
Ten leaf entries of an index on (user_id, created_at, status), in index order. A lookup seeks to a start key, then walks right.
Step 1 of 5
Sorted by user, then date
Entries are ordered by user_id, then by created_at within a user, then by status within a date. Dictionary order, applied to columns instead of letters.
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
“User 42 wants payments of ₹1 lakh or more, newest first. Only three of 4,812 qualify. How do you order the index?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“You have (user_id, created_at, status) INCLUDE (amount_paise). The Failed payments tab wants user 42’s latest 20 failures. New index?”
About 2% of payments fail, and the table takes thousands of inserts a second.
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“On PostgreSQL 18, ops wants a per-minute count of all payments, any status. You have (status, created_at) and (user_id, created_at, status). Now what?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“An audit of transactions finds (user_id), (user_id, created_at, status) INCLUDE (amount_paise), and (status, created_at). Which do you drop?”
Your move. Pick an approach:
Build-up · 5 steps
From five queries to two indexes
The five hottest queries on the payments table. Design the indexes from this list, not from the columns.
Step 1 of 5
Five hot queries
Tag each query’s columns equality, sort or range. Q1 to Q4 all have equality on user_id and use created_at; Q5 has equality on status instead.