Partial Indexes in PostgreSQL: Index Only the Rows You Actually Query

You have a large table and one query pattern dominates. A full index on the column helps that query, but it also costs write amplification and memory for rows you never filter on. The practical move is to index only the subset you care about.

PostgreSQL supports partial indexes. An index entry is created only for rows that satisfy a WHERE predicate defined at index creation time. The planner can use the index when the query’s WHERE clause matches the predicate, and the index stays small because it ignores the rest of the table.

When a partial index pays off

Partial indexes reduce size and improve cache efficiency when the predicate is selective. Typical cases are a status column with a rare value, a boolean flag that is mostly false, or a nullable column where you only query non-NULL rows.

The index is useful only if the query condition matches the predicate closely. The planner requires the query’s filter to be provably equivalent to the index predicate. If it isn’t, the index is ignored.

Defining a partial index

Syntax is the same as a normal index with an added WHERE clause. Run as the table owner or a role with CREATE privilege on the schema.

CREATE INDEX idx_orders_completed_id ON orders (id)
WHERE status = 'completed';

The index stores only rows where status = 'completed'. It works with any column type and can be used for unique constraints on the subset:

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

That enforces uniqueness only for active users. It does not prevent duplicate emails among soft-deleted rows.

Check whether the planner can use it:

EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND id = $1;

Look for an Index Scan or Index Only Scan referencing the partial index name. Size can be inspected with:

SELECT pg_size_pretty(pg_relation_size('idx_orders_completed_id'));

Compare against a full index on the same column to see the difference in storage.

Worked example with orders

Consider orders with statuses pending, processing, completed, cancelled. Reporting queries almost always ask for completed orders in a date range.

CREATE INDEX idx_orders_completed_created
ON orders (created_at)
WHERE status = 'completed';

A matching query can use the index:

SELECT order_id, total
FROM orders
WHERE status = 'completed' AND created_at >= '2026-01-01';

A query without the predicate cannot use this index:

SELECT * FROM orders WHERE created_at >= '2026-01-01';

Maintenance is standard. VACUUM and ANALYZE keep statistics accurate for the partial index. If data distribution shifts so the predicate becomes less selective, the benefit shrinks. Changing the predicate requires dropping and recreating the index, which locks writes on large tables and can be expensive.

Trade-offs and limitations

Partial indexes add management complexity. You must keep predicates in sync with application queries. If the predicate does not match exactly, the index is invisible to the planner.

Partial unique indexes require careful design. Uniqueness is enforced only within the predicate. A different predicate can allow duplicates that would be illegal for a full unique index.

For highly variable predicates, multiple partial indexes can accumulate and increase write overhead. Monitor usage with pg_stat_user_indexes and drop indexes with zero scans.

Actionable check: after creating a partial index, run EXPLAIN on representative queries, verify index usage, check size with pg_relation_size, and re-run ANALYZE after bulk loads. Keep the predicate simple and stable, and document why the index exists so future changes don’t silently break query plans.