A partial index is a database index that contains entries only for the rows that satisfy a condition declared in its definition, rather than for every row of the table. In indexing, it is created with a WHERE clause, as in CREATE INDEX ON orders (created_at) WHERE status = 'pending', and SQL Server calls the same concept a filtered index.
Because it covers only a subset of rows, a partial index is smaller, faster to scan and cheaper to maintain than a full index on the same columns. It pays off when queries repeatedly target a small, well-defined slice of a large table.
How a partial index works
When a row is inserted or updated, the database evaluates the index predicate for that row. If the predicate is true, the row gets an entry in the index; if it is false or NULL, the row is skipped entirely. The underlying structure is usually an ordinary B-tree, so lookups and range scans work exactly as with a normal B-tree index, only over fewer entries.
The query planner may use a partial index only when it can prove that the query’s WHERE clause implies the index predicate. A query with WHERE status = 'pending' AND created_at > now() - interval '1 day' qualifies for an index defined with WHERE status = 'pending'. A query with WHERE status = $1, where the value is a parameter, often does not qualify with a generic plan, because the planner cannot know at planning time that the parameter will equal ‘pending’.
The table summarizes support in mainstream systems.
| Database | Name and syntax | Notable limits |
|---|---|---|
| PostgreSQL | Partial index, CREATE INDEX ... WHERE | Predicate must use immutable functions |
| SQLite | Partial index, CREATE INDEX ... WHERE | Only deterministic expressions allowed |
| SQL Server | Filtered index, CREATE INDEX ... WHERE | Simple comparisons only, no OR or functions |
| MySQL and MariaDB | Not supported directly | Workaround with generated columns |
| Oracle | No WHERE clause on indexes | Function-based index returning NULL for excluded rows |
| MongoDB | Partial index with partialFilterExpression | Limited operator set in the filter |
Typical patterns for partial indexes
Partial indexes work best when the queried subset is small relative to the table and stable in its definition. Several patterns recur across applications.
| Pattern | Example predicate | Benefit |
|---|---|---|
| Work queue | WHERE status = 'pending' | Tiny index over open jobs only |
| Soft delete | WHERE deleted_at IS NULL | Excludes logically deleted rows |
| Sparse column | WHERE external_id IS NOT NULL | Skips the many rows without a value |
| Conditional uniqueness | UNIQUE ... WHERE active | One active record per key |
| Rare flag | WHERE needs_review = true | Fast lookup of exceptional rows |
The conditional uniqueness pattern is often the most valuable. A unique partial index such as CREATE UNIQUE INDEX ON subscriptions (user_id) WHERE active enforces that each user has at most one active subscription while allowing any number of canceled ones, a rule that an ordinary unique constraint cannot express.
Partial indexes compared with other index types
A partial index is not a separate data structure but a restriction applied to an index, so it combines with other indexing techniques. It can be composite, covering or expression-based at the same time.
| Index type | What it limits or adds | Combination with a partial index |
|---|---|---|
| Full B-tree index | Indexes every row | Partial index is the filtered alternative |
| Composite index | Several key columns | Often partial, for example status plus date |
| Covering index | Includes extra columns for index-only scans | Partial covering index for hot queries |
| Unique index | Forbids duplicate keys | Unique partial index for conditional rules |
| Bitmap index | Efficient for low-cardinality columns | Different approach to selective flags |
A common alternative to a partial index on a status column is a composite index starting with the status. The composite index serves every status value but is much larger; the partial index serves one status very efficiently and nothing else. The right choice depends on whether the application queries one subset or many.
When to choose a partial index
A partial index is worth considering when a query filter is highly selective and fixed in the code, typically matching a few percent of the rows or less, and when the table is large enough that a full index is costly. The gain grows with the ratio between table size and subset size: an index over 50,000 pending rows in a 200-million-row table is thousands of times smaller than the full alternative and can stay entirely in memory.
- Write the query predicate exactly as the index predicate, or as a stricter version of it, so the planner can match them.
- Avoid parameterized values in the filtered column when relying on the index, or check that the actual plan uses it.
- Verify the benefit with
EXPLAINon realistic data, not on a small development database. - Keep the number of partial indexes modest; many overlapping ones increase planning effort and write cost.
Limitations and common mistakes
The most frequent problem is a partial index the planner never uses, because queries are written with a different but logically equivalent condition, such as status IN ('pending') versus status = 'pending' in a system that does not normalize them, or because an ORM sends the value as a bind parameter. Another pitfall arises when the predicate is not selective at all: an index covering 90 percent of rows brings almost no saving compared with a full index.
Rows that move in and out of the predicate also cost work: updating a job from ‘pending’ to ‘done’ deletes its index entry, and in PostgreSQL it also prevents a heap-only tuple update. Finally, partial indexes can complicate migrations, because changing the predicate requires dropping and recreating the index, which on large tables should be done concurrently to avoid long locks.
The short version
An index is like the alphabetical index at the back of a book. A partial index is an index that lists only the entries a reader actually looks for, such as only the open cases in a large archive of closed ones.
Because it is much shorter, it is quicker to search and cheaper to keep up to date. It only helps, however, when the question asked matches the part of the data it lists.
Where it makes a difference
A logistics platform keeps every delivery task in a PostgreSQL table that has grown to about 150 million rows. Dispatchers constantly query open tasks with WHERE status = 'open' ORDER BY due_at, and at any moment only around 30,000 tasks are open. A full index on (status, due_at) occupies several gigabytes and is mostly made of closed tasks that no one queries.
The team replaces it with CREATE INDEX CONCURRENTLY idx_tasks_open_due ON tasks (due_at) WHERE status = 'open'. The new index takes only a few megabytes and stays in memory. The dispatcher query now reads a handful of pages instead of descending a large tree, and its latency drops sharply under peak load. The team also adds a unique partial index ensuring that each vehicle has at most one task in progress.