Interview prompt
Explain how a b-tree index narrows a database search to an engineer who understands the surrounding system but has not used this technique. Walk from its contract to a concrete operation, then discuss where it fails or becomes expensive.
A strong answer
A database index is an auxiliary structure that helps find rows without scanning every table page. A B-tree keeps keys in sorted order across pages and uses separator keys to direct a search from the root toward a leaf. The index is valuable when its lookup cost is lower than the work it avoids.
For a query filtering by customer_id and ordering by created_at, a composite index beginning with customer_id may narrow to one customer’s key range and then return rows in order. If the query selects columns present in the index, some engines may avoid visiting table pages for each result.
A complete answer also calls out the assumptions that control correctness. Indexes consume storage and add work to inserts, deletes, and updates. A low-selectivity column may not justify an index by itself, and a query planner can prefer a sequential scan when many rows qualify. Composite index column order changes which predicates can use its leading range efficiently.
Close by describing one representative test or measurement. Given an index on (tenant_id, created_at), compare queries filtering by tenant_id alone and created_at alone. Explain what information the index ordering makes directly available.
Follow-up questions
Answer the follow-ups in the frontmatter. Use the linked article for the concept and the trace to make the explanation concrete.