Postgres partial indexes: when less index is more

The orders table holds eight years of history. Ninety-eight percent of rows are status = 'completed'. The dashboard query filters status = 'pending' and sorts by created_at. A full index on (status, created_at) works — and grows with every completed order forever. Vacuum runs longer. Inserts on checkout pay index maintenance on rows the query will never read. Postgres partial indexes solve this by indexing only the slice the application actually queries: same planner access path, fraction of the size, fraction of the write amplification.

A partial index is a standard btree (or other index type) with a WHERE clause attached at creation time. Only rows satisfying the predicate appear in the index. The planner uses the partial index only when the query's conditions imply the same predicate — a match that must be deliberate, not accidental. Partial indexes complement Postgres composite index patterns; they do not replace column-order discipline. They answer a different question: which rows deserve to be indexed at all.

Partial indexes shrink the working set the planner searches

Full indexes on low-cardinality status columns often degenerate into wide structures where most entries point at rows the hot query never touches. Partial indexes trade generality for precision.

-- Full index: grows with every order
CREATE INDEX orders_status_created_idx
  ON orders (status, created_at);
 
-- Partial index: only pending rows
CREATE INDEX orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

The partial index stores only pending orders. Completed and cancelled rows never enter the structure. Inserting a completed order does not touch orders_pending_created_idx. Promoting a row from pending to completed removes it from the partial index — still a write, but bounded to the small pending set.

Size reduction is proportional to selectivity. If one percent of rows match the predicate, expect roughly two orders of magnitude less index pages — fewer buffer cache evictions, faster index-only scans when visibility map cooperates, shorter autovacuum cycles on the index itself.

A partial index with a predicate the application forgets to repeat is an invisible index — present in \di, absent from EXPLAIN.

Predicate design must mirror real query shapes

The partial index predicate must be implied by the query's WHERE clause for the planner to choose it. Postgres does not guess.

Works — query repeats the predicate or a stricter subset:

-- Index
CREATE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;
 
-- Query: predicate matches
SELECT id FROM users
WHERE deleted_at IS NULL AND email = $1;

Fails — query omits the indexed predicate:

-- Same index
SELECT id FROM users WHERE email = $1;
-- Planner may seq scan: index does not guarantee deleted rows are excluded

Works with stable sentinels — soft-delete and archive patterns:

CREATE INDEX documents_published_slug_idx
  ON documents (slug)
  WHERE published = true;
 
CREATE INDEX events_upcoming_idx
  ON events (starts_at)
  WHERE starts_at > '1970-01-01'::timestamptz AND cancelled = false;

Avoid volatile functions in predicates (WHERE expires_at > now()). The indexed row set would need constant maintenance; the planner cannot reason about a moving boundary at plan time. Use application-maintained status columns or partition by time instead.

Common high-value partial index patterns:

For uniqueness among a subset, a partial unique index replaces brittle application checks:

CREATE UNIQUE INDEX one_active_sub_per_user
  ON subscriptions (user_id)
  WHERE status = 'active';

Only one active subscription per user_id — cancelled rows do not participate. Attempts to insert a second active row fail at the database with a clear constraint violation.

Partial indexes interact with Row-Level Security and write paths

Row-Level Security policies append implicit filters. A partial index predicate must align with how secured queries actually run — not how they read in application code without RLS enabled.

If every query through PostgREST or Supabase client enforces tenant_id = current_setting('app.tenant_id')::uuid, a partial index on WHERE status = 'open' alone may be invisible to plans that must also prove tenant isolation. Safer shape:

CREATE INDEX tasks_open_tenant_idx
  ON tasks (tenant_id, updated_at)
  WHERE status = 'open';

Queries that filter tenant_id and status = 'open' match. The Supabase RLS performance guidance applies: indexes should include columns RLS policies reference, and partial predicates should reflect the intersection of business filters and policy filters.

Write-path caveat: updating a row across the predicate boundary moves it in or out of the index. pending → completed deletes an index entry; completed → pending inserts one. High churn on the boundary column concentrates write load — sometimes a full index with better column order is cheaper than constant partial-index maintenance.

Verify with EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Expect Index Scan or Index Only Scan on the partial index name. If the plan shows Seq Scan, check predicate mismatch, stale statistics (ANALYZE orders), or enable_indexscan disabled in session (rare). Compare shared hit and execution time before and after — partial indexes win on read latency and cache pressure, not magic.

When should the planner prefer a partial index over a composite?

Can one partial index serve multiple query variants?

Only when every variant implies the same predicate. WHERE status = 'pending' and WHERE status = 'pending' AND tenant_id = $1 both qualify for an index on (tenant_id, created_at) WHERE status = 'pending'. WHERE status IN ('pending', 'processing') does not match an index predicated on status = 'pending' alone — use WHERE status IN ('pending', 'processing') in the index or maintain separate partial indexes for disjoint hot paths.

Do partial indexes work with INCLUDE columns?

Yes. Postgres supports CREATE INDEX ... INCLUDE (col) WHERE predicate for index-only scans that cover extra columns without btree key ordering on them. Useful for SELECT id, slug when the predicate is published = true and slug is the lookup key — INCLUDE (id) avoids heap fetches when visibility map conditions hold.

How small must the filtered set be to justify partial?

No fixed ratio — measure. Below roughly five to ten percent selectivity, partial indexes usually dominate on size and write cost. Between ten and thirty percent, compare pg_relation_size and EXPLAIN costs against a full composite index. Above thirty percent, partial indexes still help when write amplification on the excluded rows is the bottleneck (archived rows inserted once, never updated). The decision is workload-shaped, not rule-shaped.

A common argument runs the other way

The opposing view holds that partial indexes add cognitive load — every developer must know the predicate — and that table partitioning or plain composite indexes are simpler to reason about at scale.

Partitioning by status or date is powerful when entire query classes route to one partition. It carries operational overhead: partition pruning rules, attachment DDL, backup complexity. Partial indexes are lighter-weight when one hot slice of a single table drives pain — open tickets, pending webhooks, unreconciled ledger entries. Composite indexes without predicates remain the default; partial indexes are the precision tool when selectivity is extreme and the predicate is stable in application code.

Key takeaways

• Postgres partial indexes store only rows matching a WHERE predicate — smaller indexes, less write amplification on excluded rows.
• Query WHERE clauses must imply the index predicate or the planner ignores the index.
• Partial unique indexes enforce uniqueness among a subset without application-level races.
• Align predicates with RLS filters and include tenant columns when policies require them.
• Avoid volatile functions in predicates; prefer status columns or partitioning for time boundaries.
• Confirm with EXPLAIN (ANALYZE, BUFFERS) — partial indexes are invisible when misconfigured.

Conclusion

Most tables accumulate rows the hot path never reads. Indexing the full history because one dashboard queries the tail is how btree structures become multi-gigabyte append-only logs that tax every insert. Partial indexes are the admission that ninety percent of rows are cold — and cold rows do not belong in the same structure that serves sub-millisecond lookups on the two percent that are not.

The next index added to a status-filtered table should justify why the predicate is not WHERE status = '<hot value>'. If the answer is that nobody defined the hot slice, the full index is probably indexing the past at the expense of the present.