An orders table holds 200 million rows. Around 40,000 of them are in a pending state at any moment, and the queries that matter — the ones the operations dashboard runs every few seconds — only ever look at those.
A conventional index on the status column covers all 200 million rows. It is large, it slows every insert and update, it bloats, and it exists almost entirely to describe rows nobody queries.
A partial index covers only the pending ones. Same query performance, a fraction of the size, and a fraction of the maintenance cost.
What changed in 2026
- High-churn status columns became ubiquitous. Event-driven and queue-backed architectures produce exactly the pattern partial indexes suit — a small hot subset in a large cold table.
- Index bloat got more attention. Once teams started measuring bloat, the appeal of indexes that simply do not contain most rows became obvious.
- Planner matching improved. Better predicate implication logic widened the set of queries that can use a given partial index.
- Storage cost scrutiny increased. Index size stopped being an afterthought as data volumes grew.
Why it helps more than it sounds
The size reduction is the visible benefit. The write reduction is usually the bigger one.
Every index on a table is maintained on every insert, update, and delete of a covered row. An index covering 200 million rows participates in every write. A partial index covering 40,000 participates only when a row enters, leaves, or changes within the predicate — rows outside it are invisible to the index entirely and cost nothing.
For a table with heavy writes and a small queried subset, that difference is substantial. It compounds with bloat: a smaller index has less to bloat, and rows that never enter it never leave dead entries behind — see index bloat.
|
Full index |
Partial index |
| Rows covered |
All |
Matching the predicate |
| Size |
Proportional to table |
Proportional to the subset |
| Write overhead |
Every write |
Only writes touching the subset |
| Cache footprint |
Large |
Small |
| Bloat exposure |
Full |
Limited to the subset |
| Query coverage |
Any predicate on the column |
Only queries the planner can match |
The planner has to prove the match
This is where partial indexes disappoint people, and it is worth understanding precisely.
For the planner to use an index defined with WHERE status = 'pending', it must be able to prove your query only touches rows satisfying that condition. A query filtering WHERE status = 'pending' matches trivially.
A query filtering WHERE status != 'complete' does not — even if, in your data, the only other status is pending. The planner reasons about the predicate, not your data's contents.
A query with WHERE status = $1 and a parameter that happens to be 'pending' may not match either, depending on when the plan is built. A generic plan prepared for any parameter value cannot assume the predicate holds — which is one way a partial index silently stops being used under prepared statements.
The practical rule: write the query predicate to match the index predicate as literally as possible, and verify with EXPLAIN that the index is actually chosen. A partial index the planner cannot match is pure cost — see query planners.
Conditional uniqueness
A second use that has nothing to do with performance and solves a problem that is otherwise awkward.
Consider a rule: each user may have at most one active subscription, and any number of cancelled ones. A plain unique constraint on (user_id) forbids the cancelled ones. A unique constraint on (user_id, status) allows two active ones with different casing or allows multiple cancelled, depending on how you model it, but does not express the rule.
A unique partial index on (user_id) WHERE status = 'active' expresses it exactly. Uniqueness applies only within the active slice.
This pattern appears constantly — one primary address, one default payment method, one current version — and a partial unique index is the cleanest way to enforce it in the database rather than in application code that will eventually race with itself.
Common mistakes
- A predicate matching most rows. No meaningful saving; just a slightly odd index.
- Query predicate not matching the index predicate. Silently falls back to a scan.
- Not verifying with EXPLAIN. An unused partial index is cost without benefit.
- Predicates on volatile expressions. The index must be deterministic; a predicate referencing the current time cannot be.
- Forgetting it when the predicate's meaning changes. Adding a new status value can silently change what the index covers.
- Using one where a full index is genuinely needed. Queries outside the predicate get no help at all.
FAQ
How small does the subset need to be?
The benefit scales with how much you exclude. Excluding 99% of rows is transformative; excluding 20% is barely worth the added complexity and matching risk.
Can I have both a partial and a full index on the same column?
Yes, and it is sometimes right — the partial one serves the hot queries cheaply while the full one covers everything else. You are paying for both, so confirm both are actually used.
Does this work with multi-column indexes?
Yes. The predicate is independent of the indexed columns, so you can index several columns while restricting which rows are included.
How does it interact with partitioning?
They compose well and solve different problems. Partitioning splits by a lifecycle dimension; partial indexes narrow within whatever remains — see table partitioning.
Where to go next
For index fundamentals and when to add one at all, read database indexing explained. For why an index you created is being ignored, query planners, and for the degradation partial indexes reduce, index bloat.