Five Cases Where a Partial Index Is the Right Answer
A partial index only covers rows that satisfy a WHERE clause defined at creation time, rather than every row in the table. For most columns, a full index is the right default. But in a handful of recurring workload patterns, indexing everything wastes disk space, slows down writes, and produces a larger, less cache-friendly index than the query actually needs. The following five cases show where a partial index isn't just an optimization—it's the correct design choice.
Filtering Soft-Deleted or Archived Rows
Many applications never physically delete rows; they set a `deleted_at` or `status = 'archived'` flag instead. If 90% of a table's rows are archived and every query filters on `WHERE deleted_at IS NULL`, a full index on that column (or on columns queried alongside it) still has to carry the dead weight of archived rows. A partial index defined as `WHERE deleted_at IS NULL` shrinks the index to only the "live" rows that queries actually care about, cutting index size and improving cache hit rates without changing a single query.
Indexing Rare Status Values in a Skewed Column
Status columns are a classic case of data skew. If 99% of orders are `completed` and only a fraction are `pending` or `failed`, a full index on `status` is dominated by the common value the planner will usually ignore anyway—sequential scans often beat an index lookup when a value matches most of the table. A partial index like `CREATE INDEX ON orders (status) WHERE status IN ('pending', 'failed')` targets exactly the rare rows that background jobs and dashboards query repeatedly, giving the planner a small, highly selective structure instead of a bloated one it would skip for the common case regardless. Reviewing query plans before and after this change makes the selectivity gain obvious.
Enforcing Uniqueness on a Subset of Rows
Partial indexes aren't only for read performance—they can express constraints a full unique index can't. Consider a table of user invitations where only one active invitation per email should exist, but historical, expired, or revo