PostgreSQL Partial Indexes: Shrink Index Size for Stable Query Patterns

The problem: indexing rows you never query

When a table contains mostly static or archived data, a full index on a frequently queried column can waste significant storage and slow write operations. Consider a tasks table where the application primarily queries for status = 'pending', but the table also stores completed, cancelled, and archived records that rarely change. A traditional B-tree index on (status, created_at) would store entries for every row, even though only a small subset is actively queried.

Partial indexes solve this by letting you define an index that stores only rows satisfying a predicate. The index size becomes proportional to the working set, not the entire table.

Defining and using a partial index

Create a partial index by adding a WHERE clause to the CREATE INDEX statement. The predicate must use immutable expressions—PostgreSQL rejects volatile functions like now() or random() in index predicates.

For a tasks table with a stable status column, you can index only pending tasks:

CREATE INDEX CONCURRENTLY idx_tasks_pending
ON tasks (assignee_id, created_at)
WHERE status = 'pending';


The CONCURRENTLY keyword allows the index build to proceed without blocking writes, which is essential for production tables. However, concurrent builds can fail if conflicting locks are held, so prepare to drop and retry if needed.

After creating the index, verify the query planner uses it with EXPLAIN (ANALYZE, BUFFERS):

SELECT id, assignee_id, title
FROM tasks
WHERE status = 'pending' AND created_at > '2026-01-01';


If the plan shows Index Scan using idx_tasks_pending or Index Only Scan, the partial index is working. If it shows Seq Scan, the predicate did not match.

Storage and write performance benefits

Compare index sizes to quantify the improvement:

SELECT pg_relation_size('idx_tasks_pending') AS partial_bytes;


Create a full index for comparison:

CREATE INDEX CONCURRENTLY idx_tasks_full
ON tasks (assignee_id, created_at);

SELECT pg_relation_size('idx_tasks_full') AS full_bytes;


The partial index should be significantly smaller when the pending subset is a small fraction of the table. Fewer index entries also reduce write amplification during INSERT and UPDATE operations, as fewer index pages need modification.

Planner requirements and limitations

PostgreSQL only uses a partial index when it can prove the query's WHERE clause logically implies the index predicate. This works reliably for simple equality conditions and certain range predicates. If your query omits the status = 'pending' condition or uses a different expression, the planner will ignore the partial index.

Monitor index usage after deployment:

SELECT relname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'idx_tasks_pending';


A persistently low idx_scan count indicates the predicate is not matching production queries, and the index may be unnecessary overhead.

Verification checklist


Run EXPLAIN (ANALYZE, BUFFERS) on representative queries before and after index creation
Confirm the plan uses Index Scan or Index Only Scan with the partial index name
Compare pg_relation_size() for partial vs full index to measure storage savings
Monitor pg_stat_user_indexes.idx_scan after rollout to verify ongoing usage


Closing: target stable, frequently-queried subsets

Partial indexes are most effective when the filter predicate is stable and the matching rows represent a small, well-defined subset of the table. They reduce storage requirements and write overhead, but only when the query consistently includes the same predicate. Always verify the planner uses the index and monitor usage after deployment.