Backend Developer interview question
How do database indexes work?
A database index is a data structure that speeds qualifying reads by avoiding a full scan, at the cost of storage and slower writes. Choose it from real predicates, joins, ordering, and selectivity, then verify the plan.
Direct answer
What a strong answer covers
How it works
Most relational indexes are B-trees: sorted copies of one or more columns with pointers back to the rows. The planner uses one when the predicate matches the leading columns and the index is selective enough that reading it plus the matching rows beats scanning the table. Every index must be maintained on insert, update, and delete, so each one trades read speed for write cost and storage. Covering indexes that include every column a query touches avoid the table lookup entirely.
Example
For WHERE tenant_id = ? ORDER BY created_at DESC, a composite index beginning with tenant_id can avoid scanning and sorting many unrelated rows. Checking EXPLAIN ANALYZE before and after shows whether the planner switched from a sequential scan and sort to an index scan with no sort step.
Common mistakes
- Indexing a low-cardinality column such as a boolean and expecting a speedup.
- Adding indexes for every query without measuring the write slowdown on a hot table.
- Wrapping the indexed column in a function in the WHERE clause so the index cannot be used.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.