Partial Index
also called Filtered Index
An index built over only the rows matching a predicate, so it is far smaller and cheaper to maintain than a full index.
If a query always filters to a small subset — WHERE status = 'active', WHERE deleted_at IS NULL,
WHERE region = 'EU' — indexing the whole table wastes space and write throughput on rows the query
will never reach.
A partial index covers only the matching rows. On a table where 2% of rows are active, it is roughly 2% of the size, fits in memory far more easily, and — the part that matters most — is only updated when a write touches a row inside the predicate.
Two further uses that are less obvious. A unique partial index enforces a conditional constraint: one active subscription per customer, while historical rows are exempt. And it is a way to index a large table incrementally without paying for full coverage.
The constraint is that the query planner must be able to prove the query's predicate implies the
index's predicate. A query filtering status IN ('active','trialling') will not use an index built
WHERE status = 'active', which surprises people and is worth checking in the plan rather than
assuming.