Interview questions

Interview questions (with the counter-questions you'll get)

Q1. What is a database index, and how does it speed up queries?

They're testing: a correct mental model.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** An index is a separate structure, usually a B-tree, that stores the values of one or more columns in sorted order with pointers to the rows. Instead of scanning every row, the database navigates the tree in a few page reads to the matching entries, so lookup cost grows with the logarithm of the table size instead of linearly. The trade-off is extra storage and slower writes, since every index must be updated on each change.
3
4**Counter-questions:**
5- *"Then why not index every column?"* → Each index slows every insert, update and delete, takes space and memory, and many would never be used. Index for actual queries.
6
7**Trap:** "an index makes the database faster", full stop.

Q2. How does a B-tree index work?

They're testing: depth.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** It's a balanced tree of pages, each holding many sorted keys. Internal pages point to the next level by key range; leaf pages hold the keys and row locations and are linked in order. A lookup walks from the root to a leaf, typically three or four page reads even for huge tables. Because leaves are sorted and linked, it also handles ranges, sorting and prefix matches.
3
4**Counter-questions:**
5- *"Why not a binary tree?"* → Each node would hold one key, so the tree would be about 20 to 30 levels deep for millions of rows, each level a separate disk read. B-tree pages hold hundreds of keys, keeping the tree shallow.
6
7**Trap:** describing a binary search tree.

Q3. B-tree vs hash index vs LSM tree?

They're testing: matching structures to workloads.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** B-trees handle equality, ranges and ordering well and are the default in relational databases. Hash indexes are fast for exact matches but can't do ranges or sorting. LSM trees optimise for write throughput by buffering writes in memory and flushing sorted files that are merged later, at the cost of reads sometimes checking several files; they're used by Cassandra and RocksDB.
3
4**Counter-questions:**
5- *"How do LSM trees keep reads fast?"* → Bloom filters skip files that can't contain the key, and compaction keeps the number of files down.
6
7**Trap:** not knowing that hash indexes can't do ranges.

Q4. Explain composite indexes and column order.

They're testing: the leftmost-prefix rule.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** A composite index sorts by the first column, then the second within each first-column value, and so on. It can seek on any leftmost prefix of its columns but not on a later column alone. I put equality columns first and the range or sort column last, so an index on `(user_id, created_at)` serves "this user's orders, newest first" without a separate sort.
3
4**Counter-questions:**
5- *"Does the order of conditions in the WHERE clause matter?"* → No. The planner matches conditions to index columns regardless of how the WHERE clause is written. What matters is the order of columns in the index.
6
7**Trap:** thinking an index on (a, b) helps a query on b alone.

Q5. What is a covering index?

They're testing: a common optimisation.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** An index that contains every column a query needs, so the database answers from the index alone without fetching table rows. It removes many random reads for hot queries. You build one by adding the needed columns to the index, or using an INCLUDE clause where supported, at the cost of a bigger index and slower writes.
3
4**Counter-questions:**
5- *"How do you know it worked?"* → The plan shows an index-only scan or "using covering index".
6
7**Trap:** confusing covering with composite.

Q6. A query is slow. How do you investigate?

They're testing: a methodical approach.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Run EXPLAIN (or EXPLAIN ANALYZE) to see the plan: look for full scans on big tables, expensive sorts, and big differences between estimated and actual rows. Check whether a suitable index exists and whether the query is written so it can use it: no functions on indexed columns, no leading wildcards, matching types. Then add or adjust an index, update statistics if needed, and confirm with EXPLAIN that the plan changed.
3
4**Counter-questions:**
5- *"The plan looks fine but it's still slow."* → It might be lock waits, too many rows returned, a slow network or client, or the database being overloaded by other queries. Check waits and server load.
6
7**Trap:** "add an index" without looking at the plan.

Q7. Why might the database not use an index you created?

They're testing: planner awareness.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** The condition matches a large fraction of rows, so a scan is cheaper; the table is small; statistics are stale; or the query can't use the index because of a function on the column, a type conversion, a leading wildcard, or the wrong column order in a composite index.
3
4**Counter-questions:**
5- *"Can you force the index?"* → Some databases offer hints, but it's usually better to fix the query, the index or the statistics; forcing a plan can backfire as data changes.
6
7**Trap:** assuming the planner is wrong.

Q8. What's the cost of indexes?

They're testing: trade-offs.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Every insert, update to an indexed column, and delete must update each index, so writes slow down; indexes take disk and memory; building one on a big live table can lock it or slow it unless done online. On write-heavy tables, unused or overlapping indexes are pure cost.
3
4**Counter-questions:**
5- *"How would you find unused indexes?"* → Use the database's index usage statistics, then drop indexes with no reads after a sensible period, carefully.
6
7**Trap:** treating indexes as free.

Q9. How would you index a column for "contains" text search?

They're testing: knowing B-tree limits.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** A B-tree can't help `LIKE '%word%'`, because it's sorted by prefix. For word search, use a full-text index: PostgreSQL's full-text search, or a search engine like Elasticsearch built on inverted indexes (Chapter 19). For substring search, PostgreSQL's trigram indexes are an option.
3
4**Counter-questions:**
5- *"What about LIKE 'word%'?"* → A prefix search can use a B-tree, with the right collation or operator class in some databases.
6
7**Trap:** "add an index on the title column".

Q10. How does indexing differ for a write-heavy table, like events?

They're testing: workload thinking.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Keep indexes minimal, because every write pays for each one. Often an append-friendly design is better: an LSM-based store, or partitioning by time so old partitions can be dropped instead of deleting rows. For analytics, copy events into a separate store built for scanning and aggregating, instead of indexing the write path heavily.
3
4**Counter-questions:**
5- *"Queries still need to filter by user."* → One composite index on the main filter, or a separate read-optimised copy built asynchronously.
6
7**Trap:** indexing every column an analyst might query.
8
9---
10
11## Practice coverage
12
13Every section in this chapter is tested at least once.
14
15| Section | Tested by |
16|---|---|
17| 9.1 What an index is | Level 1A, Q1 |
18| 9.2 Scan vs lookup | Level 2.2, Level 3, Q1 |
19| 9.3 Index structures | Level 2.1, Level 3.4, Q2, Q3 |
20| 9.4 Composite indexes | Level 1B, Level 3, Level 5.1, Q4 |
21| 9.5 Covering index | Level 3, Q5 |
22| 9.6 EXPLAIN | Level 3, Level 4, Level 5.3, Q6 |
23| 9.7 Write cost | Level 2.3, Level 3.5, Level 5.5, Q8, Q10 |
24| 9.8 When indexes don't help | Level 1A, Level 2.4, Level 4, Q7, Q9 |
25
26---

9. Indexes