PostgreSQL Partial Indexes: Index Only the Rows You Need

A partial index in PostgreSQL is an index built over only the rows of a table that satisfy a WHERE condition you specify. Rows that do not match the condition are simply left out. The result is an index that is smaller, cheaper to maintain, and often faster to search than a full index on the same columns, as long as your queries target the same subset of rows. It is one of the most effective and underused tools for tuning real-world PostgreSQL workloads.

The syntax

A partial index is an ordinary CREATE INDEX statement with a predicate attached:

CREATE INDEX orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

This index contains an entry only for orders whose status is pending. Completed, cancelled, and refunded orders, which in most systems are the overwhelming majority, never enter it.

Partial indexes work with any index type. You can add a predicate to the default B-tree as well as to GIN, GiST, BRIN, and the others covered in PostgreSQL’s index types. If you are new to indexes generally, see what database indexing is first.

Why skipping rows helps

Every index has costs as well as benefits.

• Storage. Index pages take disk space and, more importantly, compete for space in memory. A smaller index is more likely to stay fully cached.
• Write overhead. Every insert, and many updates and deletes, must also update each index on the table. Rows excluded by the predicate skip that work for the partial index.
• Search depth. Fewer entries mean fewer pages to read, and for a B-tree potentially fewer levels to descend.

When the rows you query are a small fraction of the table, a partial index captures nearly all of the benefit at a fraction of the cost.

Common use cases

Hot subsets of a large table. Queues, workflows, and job tables often have a handful of active rows and millions of finished ones. Workers repeatedly ask for the next pending job:

SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY run_at
LIMIT 10;

An index on (run_at) WHERE status = 'queued' stays tiny no matter how much history accumulates.

Soft deletes. Many applications mark rows as deleted rather than removing them. Nearly every query then filters on deleted_at IS NULL:

CREATE INDEX users_email_live_idx
  ON users (email)
  WHERE deleted_at IS NULL;

Conditional uniqueness. This is a capability a full index cannot provide. A partial unique index enforces uniqueness only among rows that match the predicate:

CREATE UNIQUE INDEX users_email_unique_live
  ON users (lower(email))
  WHERE deleted_at IS NULL;

Active users must have unique emails, but a deleted account no longer blocks someone from signing up with the same address. The same technique enforces rules like “each customer may have only one default payment method” with WHERE is_default.

Excluding uninteresting values. If a column is mostly NULL or dominated by one common value, queries for the rare values benefit from an index that skips the common case entirely. This ties into index cardinality: an index on a low-selectivity value is rarely used, so leaving it out costs nothing.

When the planner can use a partial index

The query planner will only use a partial index if it can prove that the query’s WHERE clause implies the index predicate. That proof is fairly literal.

• WHERE status = 'pending' in the query matches an index predicate of status = 'pending'.
• WHERE created_at > '2026-01-01' can use an index with WHERE created_at > '2025-01-01', because the planner can reason about simple range comparisons on the same column.
• A query using a parameter, such as WHERE status = $1, generally cannot use the index with a generic plan, because the planner cannot know at planning time that $1 will be 'pending'.

The last case trips up applications that use prepared statements. PostgreSQL may use custom plans for the first few executions and then switch to a generic plan, at which point the partial index quietly stops being chosen. Writing the matching condition as a literal in the query, rather than as a bound parameter, avoids the problem. Always confirm with EXPLAIN ANALYZE that the index is actually used.

Combining with other index techniques

Partial indexes compose with other features:

• Multi-column keys. A partial composite index such as (customer_id, created_at) WHERE status = 'open' serves a common dashboard query exactly.
• Covering indexes. Adding INCLUDE (total) lets the planner answer a query from the index alone, combining the ideas in covering indexes with a smaller footprint.
• Expression indexes. You can index an expression and add a predicate in the same statement, as in the lower(email) example above.

Pitfalls to watch for

• Predicate drift. If application code changes a filter, for example adding a new status like 'pending_review', existing queries may stop matching the index predicate. Keep index predicates and query filters in sync.
• Too many narrow indexes. Each partial index still costs writes for the rows it covers. A dozen slightly different partial indexes can be worse than one well-chosen full index.
• Unstable predicates. The predicate must use only immutable expressions. You cannot write WHERE created_at > now() - interval '30 days', because now() changes. For time windows, use a fixed cutoff and recreate the index periodically, or consider partitioning.
• Statistics surprises. If the subset grows much larger than expected, for instance a backlog of pending jobs, the index loses its size advantage. Monitor how many rows it covers.

Full vs partial indexes

	Full index	Partial index
Rows covered	Every row	Only rows matching the predicate
Size	Grows with the whole table	Grows with the matching subset
Write cost	Paid on every row	Paid only on matching rows
Conditional uniqueness	Not possible	Supported
Planner usage	Any query on the columns	Only queries that imply the predicate
Best for	Broad, varied filters	Hot subsets, soft deletes, rare values

The takeaway

A partial index adds a WHERE clause to an index so only the rows you actually query get indexed. That makes it smaller, cheaper to write, and more cache-friendly, and partial unique indexes enforce rules a full index cannot. Use them for queues, soft deletes, and rare values, keep query filters literal and aligned with the predicate, and verify with EXPLAIN ANALYZE that the planner is really using them.