Not every row deserves an index slot; a partial index keeps the tree thin exactly where the query never looks. Most tables have a skew the schema pretends not to see: ninety percent of orders are closed, nearly all events are processed, the flag column is false almost everywhere. A full index dutifully records all of it, charging maintenance on every write to protect lookups that only ever touch the small live slice. The partial index stops paying for the rows nobody queries.

The bargain is precision for size. The index becomes smaller, faster to scan, and cheaper to maintain — but only queries whose predicates match the index condition may use it. Everything else falls back to competing plans. That makes partial indexes a deliberate pairing of access pattern with storage, not a general speedup. Choose them where the hot subset is stable and the queries agree on its definition.

Index only what you query

A partial index carries a WHERE clause, so rows that fail the predicate never enter the tree. When most rows are rarely read, this keeps the index small and the lookups fast on the ones that matter. An index on open orders rather than all orders can be an order of magnitude smaller, which means more of it fits in cache, fewer pages per lookup, and plans that stay index-driven as the cold history grows unboundedly.

Pick the predicate from the queries. The right condition is the one every important query already repeats: status equals open, deleted_at is null. If queries slice the table differently, either the dominant slice wins or the table needs two partial indexes, each still thinner than a full one.

Fewer rows, cheaper maintenance

Fewer entries mean inserts, updates, and VACUUM touch a lighter structure, and disk usage drops visibly. The savings are most obvious on write-heavy tables where the full set would be mostly noise. Every insert that fails the predicate skips the index entirely — no page split risk, no WAL for that entry — and updates that leave the predicate untouched skip index maintenance too. On tables absorbing thousands of cold writes per second, the avoided work dwarfs the lookup gains.

Smaller indexes also vacuum faster with fewer pages for bloat to hide in. But monitor size against the full-index equivalent: if the partial index approaches full size, the predicate no longer selects a minority and the bargain has expired — drop it or redefine the slice.

Shape the statement to match

The planner only honors the index for queries whose predicate matches the partial condition exactly, or implies it. Write the filter the same way in the query and the index so the planner can actually use it. status = ‘open’ matches an index predicated on status = ‘open’; status IN (‘open’, ‘pending’) does not, even though a human sees the overlap. The matcher is logical implication, not textual similarity, and it is conservative by design.

This strictness dictates a discipline: canonicalize the filter expression across the codebase. If the index says deleted_at IS NULL, no query may phrase it as deleted_at = NULL or COALESCE(deleted_at, …) IS NULL and expect the index. Centralize the hot-slice condition — a view, a scope, a query builder fragment — so every statement inherits the exact predicate. EXPLAIN is the arbiter: if the plan ignores the index, the predicates differ in a way the matcher cannot bridge.

When it breaks

The classic failure is the drifting predicate: the business redefines “open” to include a new status, application queries update, and the partial index silently stops matching half the workload. Nothing errors — plans just degrade to sequential scans one query at a time, and latency climbs without a deploy to blame. Guard with a periodic EXPLAIN on the canonical queries in CI or monitoring, asserting the index name appears in the plan. A plan assertion catches predicate drift the day it ships, not the quarter it gets noticed.

The second failure is the over-sliced table: five partial indexes, each serving one report, whose combined maintenance exceeds the full index they replaced. Audit with pg_stat_user_indexes — near-zero-scan indexes are removal candidates, and overlapping predicates should merge into one wider partial index.

Check it quickly

Prove the index matches and pays:

CREATE INDEX ON orders (created_at) WHERE status = 'open';
EXPLAIN SELECT * FROM orders WHERE status = 'open' ORDER BY created_at LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('orders_created_at_idx'));

The plan must show an index scan on the partial index, and the size should be a small fraction of the full-index equivalent.

Sources