Partial Indexes in Postgres: The Right Index Is Often a Smaller One

A partial index is an index over a subset of a table's rows, defined by a WHERE clause. It is smaller than a full index, faster to scan, and cheaper to maintain. For workloads where queries only touch a fraction of the rows, a partial index is often the right answer.

Most teams I have worked with did not use partial indexes nearly enough. Here are the patterns that come up.

The shape

CREATE INDEX orders_active_customer_id
  ON orders (customer_id)
  WHERE status NOT IN ('cancelled', 'refunded');

This index only contains rows where status is not cancelled or refunded. Smaller table, smaller index, but the queries that filter by status NOT IN ('cancelled', 'refunded') get an index that exactly matches their predicate.

The critical rule: the query's WHERE clause must include the partial index's WHERE clause. The planner needs to know the index's predicate covers the query's predicate.

The most common case: soft delete

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

Uniqueness on email applies only to non-deleted users. A soft-deleted user's email can be reused by a new user. The full table can have many rows with the same email (one active, several deleted), but the index sees only the active one.

This is the canonical use case. Without partial indexing, soft delete forces an awkward unique-with-deleted-at-included composite, which does not enforce the right rule.

Hot-row indexes

CREATE INDEX orders_pending_assigned_at
  ON orders (assigned_at)
  WHERE status = 'pending';

A work-queue table where 99% of rows are completed and 1% are pending. A full index on (assigned_at, status) is huge and mostly useless. A partial index on just the pending rows is tiny — fits in cache, fast to scan.

The pattern: any column where one value dominates and only the rare value matters for queries.

Recent-data indexes

CREATE INDEX events_recent_user_id
  ON events (user_id, created_at DESC)
  WHERE created_at > '2025-01-01';

A partial index over only recent events. For queries that always filter by recent dates, this index is much smaller than a full one and fits in cache.

The gotcha: the date in the WHERE clause is fixed in the index definition. As time passes, more rows fall into the "recent" range and the index grows. You eventually need to rebuild with a newer date threshold. Some teams automate this with a quarterly migration.

What does NOT work

Partial indexes do not work when:

• The query's WHERE clause is more permissive than the index's: if the index is WHERE status = 'pending' and the query is WHERE status IN ('pending', 'processing'), the index covers only half the query. Postgres will not use it.
• The WHERE expression uses non-IMMUTABLE functions: WHERE created_at > now() - interval '30 days' is not allowed in a partial index because now() is not immutable. The index condition must produce the same partition forever.
• The application forgets the WHERE clause: a query that just says SELECT * FROM orders WHERE customer_id = 123 will not use a partial index that has WHERE status = 'pending'. The query must include the same condition (or a subset of it).

Verifying it gets used

Run EXPLAIN:

EXPLAIN SELECT *
FROM orders
WHERE status = 'pending'
  AND assigned_at < now() - interval '5 minutes';

Look for Index Scan using orders_pending_assigned_at. If you see Seq Scan, the planner did not pick it. Check that:

1. The query's predicate is at least as restrictive as the index's.
2. The statistics are up to date (ANALYZE orders).
3. The index's leading column matches what the query needs.

Cost benefit

A partial index pays off when:

• The fraction of rows in the partial set is small (under 20% is a good rule of thumb).
• Queries reliably include the partial condition.
• The full table is large enough that index size matters.

For a hundred-thousand-row table, partial indexes are usually overkill. For a hundred-million-row table where 99% of rows are "completed," a partial index on the active 1% is potentially 100x smaller and 100x faster to maintain.

What I do today

When reviewing a schema, I look for indexes that could be partial:

• Indexes on (deleted_at IS NULL, ...) columns. Should usually be partial.
• Indexes on tables where one status value dominates. Should usually be partial on the rare statuses.
• Indexes on time-series data where queries always filter recent. Sometimes worth partial, sometimes worth partitioning instead.

The shift from full index to partial index is usually a small change with measurable wins on large tables. Worth doing systematically as part of an index audit.