Databases
Indexes: free-recall review
Retrieval beats re-reading. For each prompt, say or write a full answer from memory before you open the model answer — the effort of recall is what makes the leading-column rule and the Visibility Map stick.
Reconstruct the unit’s core mechanisms — the leading-column rule, when partial beats full, what makes an index-only scan fire, how to choose an index type, and the most common production failures — without looking back at the lessons.
- 01Why does a composite index on (a, b, c) help WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? — and what is the one design rule that follows?
- 02When does a partial index win over a full index, and what must the query satisfy to use it?
- 03What two conditions must both hold for an index-only scan to avoid heap fetches, and how do you keep them true in production?
- 04Name when you would reach past B-tree for GIN, GiST, BRIN, and pgvector HNSW, and the cost each carries.
- 05Why are foreign-key columns a recurring production trap, and what is the rule?
- 06List the seven production index failure modes and the single diagnostic that exposes each.
If you could reconstruct each answer from memory, you hold the unit’s spine: a composite is usable only from a leading prefix, so design it around the always-present filter and trail it with the ORDER BY columns; partial indexes shrink in proportion to the hot subset and skip cold-row writes; an index-only scan needs both full coverage (key + INCLUDE) and a current Visibility Map (VACUUM); the index type must fit the data shape; FK columns are never auto-indexed; and the seven failure modes all surface under one diagnostic — EXPLAIN (ANALYZE, BUFFERS) on the real query.