Partial Index
A partial index covers only rows matching a predicate, shrinking hot-path indexes dramatically when most of the table is inactive or irrelevant to the query pattern.
Definition
A partial index is an index with a WHERE clause: only rows satisfying the predicate are indexed. Queries whose predicates imply the index predicate can use it. Common shapes index only unprocessed work queues, active records, or one tenant-heavy slice, keeping the index small and hot in cache.
Context and scope
Used for job queues with status columns, soft-deleted records, regional or status-filtered lookups, and uniqueness constraints that apply conditionally.
Common misconceptions
- The optimizer uses partial indexes automatically — only when the query predicate provably implies the index predicate
- Partial indexes are for filters only — they also enforce conditional uniqueness, a separate and valuable capability
- Any predicate works — write-heavy partial indexes need a stable predicate; volatile values churn entries in and out
Anti-patterns
- Indexing status equals pending with a predicate the optimizer cannot prove from the query and never seeing the index used
- Forgetting to drop the partial index after the queue drains and maintaining it forever on every insert
- Applying a partial unique constraint where the business rule actually needs full uniqueness, letting duplicates slip through
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/indexes-partial.html
What it is
Full indexes pay maintenance on every row. When only a slice of rows is ever queried — pending jobs, live sessions, one market — a partial index covers just that slice. Smaller trees mean shallower lookups, faster scans, and far better cache residency.
How it works
The index stores rows passing its predicate. The planner uses it when a query constraint logically implies that predicate: querying status equals pending can use an index created where status equals pending. Conditional uniqueness works the same way — a unique partial index enforces uniqueness only within the predicate.
When to use it
Work-queue tables where completed rows dominate but queries hit pending ones; soft-delete schemes where active rows are the hot set; uniqueness that must hold per-status or per-tenant but not globally. Any hot query whose predicate is stable and narrow qualifies.
Pitfalls
Proof requirements are strict: a query on status in (pending, retrying) does not use an index predicated on exactly pending unless a second index covers retrying. Predicate churn — rows entering and leaving the indexed set — causes index insert and delete traffic on top of heap updates.