IndexesAdvanced
When is a partial index the right tool?
“Give a case where a partial index beats a full one.”
What this tests
- Partial indexes
- Low-cardinality columns
Answers by level
Read the beginner answer first and notice what is missing.
When you only ever query a rare subset of a low-cardinality column: a work queue (WHERE processed_at IS NULL), a soft-delete flag (WHERE deleted = false), a rare status. The index stores only those rows, so it is tiny, its tree is shallow, and it makes an otherwise unindexable column indexable.
The query must repeat the predicate for the planner to use it.
Green flags · Red flags
Strong green flag · Reaches for it to make a low-cardinality column indexable.
Green flags
- Queue / soft-delete / rare-status examples
- Knows the query must match the predicate
- Conditional uniqueness
Red flags
- Thinks it is only about size
- Expects it used without the predicate
Follow-up questions
F1
Enforce "one active subscription per account".
Scenario
A jobs table has 200M processed rows and 50 pending. How do you index the "next pending job" query?