Add a Partial Index for Active Rows

Index only the rows queries actually read, cutting index size and write cost for soft-deleted or status-filtered tables.

~30 minpostgresqlindexesperformance

Task

Speed up lookups on active records without indexing millions of archived rows that no query ever touches.

When to use this

An orders table keeps every completed order for seven years. The dashboard only queries open orders, but the index covers all 80 million rows, so it no longer fits in memory and writes pay for entries nobody reads.

Prerequisites

PostgreSQL 12 or newer
A query with a stable predicate such as status or deleted_at
Permission to create indexes concurrently

Steps

1. Confirm the exact predicate the application uses so the planner can match the partial index.

psql -U app_rw -d appdb -c "EXPLAIN SELECT id FROM orders WHERE tenant_id = 42 AND status = 'open' ORDER BY created_at DESC LIMIT 20;"

Expected output: The plan shows a sequential scan or a full index scan, with the filter on status = 'open'.

2. Build the index concurrently with the same predicate and a column order that serves the sort.

psql -U postgres -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_open_tenant ON orders (tenant_id, created_at DESC) WHERE status = 'open';"

Expected output: The index is created without blocking writes and only contains rows where status is open.

3. Run ANALYZE and confirm the planner chooses the partial index.

psql -U app_rw -d appdb -c "ANALYZE orders; EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM orders WHERE tenant_id = 42 AND status = 'open' ORDER BY created_at DESC LIMIT 20;"

Expected output: Index Scan using idx_orders_open_tenant, with buffers far below a full table scan.

4. Compare index sizes and verify the partial index is a fraction of the full one.

psql -U app_rw -d appdb -c "SELECT pg_size_pretty(pg_relation_size('idx_orders_open_tenant')) AS partial, pg_size_pretty(pg_relation_size('idx_orders_tenant_created')) AS full;"

Expected output: The partial index is substantially smaller, often by an order of magnitude when most rows are closed.

Troubleshooting

Problem

The planner ignores the partial index

Solution

The query predicate must match the index predicate exactly; status = 'open' and status IN ('open') are not always equivalent, and stale statistics also prevent matching.

Problem

CREATE INDEX CONCURRENTLY fails and leaves an invalid index

Solution

Drop the invalid index and retry during lower write load; check pg_index.indisvalid before assuming the build succeeded.

Limitations

Queries that omit the predicate cannot use the index, so keep a full index if both access patterns exist.
Concurrent builds take longer and require two table scans; schedule them outside peak hours.

Index the working set

Most operational tables have a hot subset: open orders, active sessions, undeleted records. A full index pays for every row on every write and occupies memory that the hot subset could use. A partial index includes only rows matching a predicate, so it is smaller, fits in cache, and costs less to maintain.

Match the query exactly

The planner can use a partial index only when the query’s WHERE clause implies the index predicate. The safest approach is to use the identical expression in code and index. If the application filters status = 'open', build WHERE status = 'open'. If it filters status IN ('open', 'packed'), either widen the predicate or create a second index.

CREATE INDEX CONCURRENTLY idx_orders_open_tenant
  ON orders (tenant_id, created_at DESC)
  WHERE status = 'open';

Build without blocking

CREATE INDEX CONCURRENTLY avoids blocking writes but takes longer and cannot run inside a transaction. Check pg_index.indisvalid afterward; a failed concurrent build leaves an invalid index that still costs writes. Drop it and retry. Run ANALYZE so the planner has fresh statistics and can estimate the partial index cost correctly.

Watch for predicate drift

If the definition of active changes, the index predicate must change too. A migration that closes orders by setting status = 'archived' is fine, but a new status value not covered by the index silently falls back to a full scan. Document the predicate next to the query and review both when the state machine changes. Measure index size and cache hit ratio monthly to confirm the partial index still represents the hot set.