Interview questions
When an index wins
1You created an index and the database ignores it. Why?
Usually because the planner estimates that using it would touch too many pages. An unclustered index beats a scan only when the fraction of rows wanted, k / N, is below the ratio of sequential to random page cost, s / r, which is a small fraction. Other causes: a parameterized query against a partial index, a function wrapped around the indexed column, or a wrong row estimate because of correlated columns.
Read moreWhen does the reader save more than the writer pays?Partial indexesWhy errors multiply (Query planning)
2Where does the rule of thumb that an index pays below about 5% of rows come from?
It is the ratio of sequential to random access cost on old spinning disks. The real crossover depends on the hardware, on whether the table is clustered on the indexed column, and on whether the index and table fit in memory.
Index anatomy
3Why does a clustered index matter so much?
It is the only mechanism that reduces the number of pages a query touches without reducing the rows it returns. Matching rows sit together, so a range reads up to f times fewer pages, where f, the rows per page, is typically 20–200. A range scan on a clustered index essentially never loses to a table scan.
Read moreClustered versus non-clustered
4Why are secondary indexes more expensive in MySQL's InnoDB than in PostgreSQL when the primary key is wide?
InnoDB stores the full primary key in every secondary-index entry and finds the row by looking it up, so a wide primary key enlarges every secondary index and lowers every fanout. PostgreSQL stores a 6-byte physical tuple address instead.
Read morePrimary versus secondary
5Why use a time-ordered UUID (UUIDv7) instead of a random one (UUIDv4) as a clustered primary key?
In a clustered table, the primary key decides where each row is written. Random keys insert into random pages, splitting them and destroying locality; time-ordered keys append at the right edge.
Read morePrimary versus secondary
6What is a covering index, and how can it silently stop helping?
An index that holds every column a query needs, so the query never visits the table. In PostgreSQL, an index-only scan still has to check whether each row is visible, using the visibility map. If vacuum falls behind, the map goes stale and the "index-only" scan fetches table rows anyway.
Read moreCovering indexes
7For the filter WHERE a = 1 AND b > 5 AND c = 3, which column order is best for a composite index?
(a, c, b): equality columns first, then a single range column. Entries are sorted lexicographically, so after a range column the later columns are no longer contiguous and can only filter.
Read moreComposite indexes
8Should the most selective column always come first in a composite index?
No. For equality-only filters, the column order does not change how many entries are scanned. Order columns so that prefixes can be reused across the workload's queries, so the index can supply a needed sort order, and so it compresses well.
Read moreComposite indexes
9When is a partial index a good idea, and what is its trap?
When queries target a small subset that can be described statically, such as pending work or live rows. The index is smaller by that subset's fraction, and writes to other rows do not touch it. The trap: the planner must prove that the query's predicate implies the index's predicate, which it cannot do for a parameterized query.
Read morePartial indexes
10How can an expression index silently return wrong results?
The index stores the function's output, and nothing watches the function's definition. If a function wrongly declared immutable changes behaviour, or a collation library changes how strings sort (as with glibc 2.28), the index silently disagrees with the data.
Read moreExpression indexes
11What is a zone map, and why does clustering matter to it?
A per-block min/max summary, used only to skip blocks that cannot match. On unclustered data every block spans almost the whole range, so nothing is skipped. A B-tree on unclustered data merely degrades; a zone map's benefit goes to zero.
Read moreZone maps
12How do bitmap indexes help when no single predicate is selective on its own?
Bitmaps combine predicates with AND and OR at about N / 64 word operations, whatever the selectivity. Five predicates that each keep 20% of rows narrow to 0.2⁵ = 0.032% of rows before any data is read.
Read moreBitmap indexes
13How do you decide which indexes to drop?
Look at each index's scan count over a full month, so monthly jobs are included, and drop those with zero. An unused index is a pure tax on every write.
Read moreThe write side has no clean formula
14Why does a block's min/max statistic sometimes prune nothing?
If the column is unrelated to physical order, each block of b random values spans nearly the whole domain: expected width (b − 1) / (b + 1) of it. And one sentinel value such as 9999-12-31 can widen a block's range permanently.
Read moreZone mapsTwo practitioner facts
Kinds of keys: text, time, space and vectors
15Why are hash indexes rare in practice?
A B-tree answers equality lookups almost as fast, and also ranges, prefixes, sorting and more. One B-tree can replace several hash indexes, with less write amplification.
Read moreEquality: the degenerate case
16In a full-text inverted index, why are posting lists sorted by document ID, and why does intersection start from the shortest list?
Sorted lists can be intersected in linear time, merging them in step. List lengths follow a Zipf distribution, so starting from a rare term's short list prunes most candidates before the long lists of common terms are touched.
Read moreInverted indexes
17Why is a B-tree on the timestamp often unnecessary in log systems?
Timestamps follow insertion order, so the data is already laid out sorted by time, and per-block min/max statistics prune almost perfectly. This stops working when event time diverges from arrival time, for example with late data or backfills.
Read moreTime: the nearly redundant index
18What does the cost of a geohash or S2 cell query depend on?
On the number of cell ranges the region covers, which grows with the region's perimeter, not its area. Coarser cells mean fewer ranges to seek but more false positives to filter out.
Read moreEscape 1: linearize
19Why is nearest-neighbour search in high dimensions approximate?
In many dimensions, distances between points concentrate: almost everything is about equally far away, so bounds can no longer rule regions out. Nothing can be pruned, and an exact search becomes a full scan. Approximation is the only option left.
Read moreThe criterion collapses
20What is the main purpose of quantizing vectors in a vector index?
Mostly to get the index back across the memory cliff. 100 million 768-dimensional float vectors take about 300 GB; with product quantization, about 10 GB.
Read moreApproximate the vectors themselves
Indexes across shards
21In a sharded database, should a secondary index be local to each shard or global?
A local index makes writes cheap, since each stays on its shard, but every lookup must query all shards and wait for the slowest. A global index serves lookups from one shard, but a write must update another shard, which needs a distributed transaction or accepts eventual consistency.