Indexing the 1%
Imagine an orders table with 1 billion rows:
- 990 million rows have
status = 'completed'— archive data, queried rarely (maybe for monthly reports). - 10 million rows have
status = 'active'— hot data, queried constantly by the application.
A standard index on (order_id) indexes all 1 billion rows. It consumes gigabytes of RAM in the buffer pool, takes minutes to rebuild, and slows down every INSERT and UPDATE. Yet 99% of your queries only care about the 10 million active rows.
The Solution: WHERE Clause in the Index
A Partial Index is an index with a WHERE clause that restricts which rows are included — the same CREATE INDEX statement, just with a trailing WHERE status = 'active' instead of indexing all 1 billion rows.
The partial index is 100x smaller. It fits in RAM. Writes are faster because only INSERTs and UPDATEs affecting active orders touch this index.
The Critical Constraint
The query optimizer can only use a partial index if the query's WHERE clause logically implies the index's WHERE clause. This means:
-- ✅ Uses the partial index — query matches the index predicate
SELECT * FROM orders WHERE order_id = 12345 AND status = 'active';
-- ✅ Also uses it — PostgreSQL can infer status = 'active' from context
-- (only if the planner can prove it statically)
-- ❌ Does NOT use the partial index — status is not constrained
SELECT * FROM orders WHERE order_id = 12345;
Always verify with EXPLAIN (ANALYZE, BUFFERS) that your partial index is actually being used.
Real-World Patterns
Queue / Soft-Delete Pattern: index only the unprocessed jobs (WHERE processed = false) or only the non-deleted rows (WHERE deleted_at IS NULL), so the index tracks just the hot set instead of the full history.
Unique Constraint on a Subset: a CREATE UNIQUE INDEX can carry the same WHERE deleted_at IS NULL predicate, enforcing uniqueness only among active records.
This last pattern is powerful: it allows multiple "deleted" users to share an email address (since deleted records are excluded from the index), while still enforcing uniqueness among active users.