Most indexes index everything. A partial index indexes only the rows that match a WHERE clause. The difference sounds small. In practice, it changes how you think about indexing.
StreamEngineeringAuthorVeraTopicsPostgres, indexing, partial indexes, query optimizationDepthDeep dive
The Syntax
A partial index adds a WHERE clause to CREATE INDEX:
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';This index only includes rows where status = 'pending'. If 95% of your orders are completed, you've indexed 5% of the table. The index is smaller, fits in memory more easily, and writes to it are cheaper because fewer inserts and updates touch it.
The planner can use this index only when the query's WHERE clause implies the index's predicate. "Implies" means: if the query says WHERE status = 'pending' AND created_at > now() - interval '1 day', the planner knows every row it retrieves satisfies status = 'pending', so it can use the index.
This doesn't work in the other direction. A query on WHERE created_at > now() - interval '1 day' alone cannot use this index, because it might need completed orders too.
Active-Record Pattern: Soft Deletes
The most common use case in web applications: soft delete with a deleted_at column.
-- Without partial index: full index scan on 10M rows
CREATE INDEX idx_users_email ON users (email);
-- With partial index: only non-deleted users
CREATE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;If you have 10 million users and 500K are soft-deleted, you've just cut the index from 10M entries to 9.5M. That's not dramatic. But if you have 10 million users and 9.9 million are deleted—which happens in any app with significant churn—the partial index is 50x smaller.
Every query for a user's email almost certainly wants only active users. The partial index serves all of them and ignores the inactive rows entirely.
Queue-Worker Pattern: The Pending Set
Job queues are another canonical case. A jobs table has millions of rows. Completed jobs are the vast majority. Active workers only care about pending jobs.
CREATE INDEX idx_jobs_queue ON jobs (queue_name, created_at)
WHERE status = 'pending';The pending set might be 1% of the table at any moment. This index covers only that 1%. Workers scanning for available jobs do index scans against a tiny data structure, not a full-table index containing years of historical completed work.
Compare index sizes:
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE tablename = 'jobs'
ORDER BY pg_relation_size(indexrelid) DESC;On a real queue table with 5 million completed jobs and 50K pending, the difference is often 200MB vs 2MB.
Size Comparison
You can measure the savings directly:
-- Full index
CREATE INDEX idx_orders_full ON orders (created_at);
-- Partial index
CREATE INDEX idx_orders_partial ON orders (created_at)
WHERE status = 'pending';
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE tablename = 'orders';The storage savings are real, but the more important benefit is cache efficiency. A 2MB index fits in shared_buffers. A 200MB index doesn't. When your hot index fits in memory, every lookup is a buffer hit. When it doesn't, you're waiting on disk I/O.
Partial Unique Constraints
Partial indexes can enforce conditional uniqueness. The classic case: a user can have only one active subscription, but many cancelled ones.
CREATE UNIQUE INDEX idx_subscriptions_active_user
ON subscriptions (user_id)
WHERE status = 'active';This allows multiple cancelled subscriptions per user while enforcing that no user has two active ones. A full unique constraint would prevent this entirely.
Another common case: unique email among active users only.
CREATE UNIQUE INDEX idx_users_email_unique_active
ON users (email)
WHERE deleted_at IS NULL;Deleted users can have their email freed for reuse. Active users must have unique emails. One partial unique index handles both requirements.
How the Planner Decides
EXPLAIN shows whether the planner is using a partial index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - interval '1 hour';Look for Index Scan using idx_orders_pending in the output. If you see Seq Scan instead, either the planner doesn't believe the index is selective enough for this query, or your query's predicate doesn't imply the index predicate.
The planner won't use a partial index if the plan doesn't logically guarantee that only matching rows are needed. This is by design—using the index when it shouldn't would return wrong results, not just slow ones.
When Not to Use Partial Indexes
High-cardinality predicates: If your WHERE clause filters to a large fraction of the table, a partial index saves little space and the full table scan might win anyway. Partial indexes shine when they exclude the majority of rows.
Frequently changing predicates: If the values in the WHERE column change constantly—orders moving through statuses rapidly—writes hit both the partial index (to add/remove entries) and any full index you might have. Measure before assuming a partial index helps on high-write tables.
Complex predicates: Postgres supports partial indexes with most predicates, but very complex ones may not be recognized by the planner as implied by query predicates. Stick to simple equality and IS NULL conditions where possible.
The Underlying Idea
A full index is a data structure that represents the entire table. A partial index is a data structure that represents a well-defined subset. For workloads where your queries always need the same subset, the partial index isn't just smaller—it's more correct. It's an index of exactly what you query.
This also explains why partial indexes can replace full indexes rather than supplement them. If every query that uses an index on email also implies deleted_at IS NULL, there's no point maintaining a full index on email. The partial index covers all real-world queries while being a fraction of the size.
More from Anethoth: builds.anethoth.com — public build dossiers for software projects in progress.