PostgreSQL Partial Indexes for Skewed Data in Large Tables

Quick answer: a PostgreSQL partial index indexes only the rows that satisfy a WHERE condition. It is ideal when queries target a small, well-defined slice of a large table, such as pending jobs or active records. The index is smaller, cheaper to maintain, and faster to scan, but the planner uses it only when it can prove the query condition matches the index condition.

What Problem Do Partial Indexes Solve?

Many tables have skewed data. A jobs table may hold hundreds of millions of completed rows and a few thousand pending ones, and the application only ever polls for pending work. A regular index on the status column indexes every row, including all the completed ones nobody queries, so it is large, costs more on every write, and is mostly wasted space.

A partial index fixes this by indexing only the interesting slice. Because it holds far fewer entries, it fits better in memory, scans faster, and is updated only when a row enters or leaves the slice or changes within it. Inserts of rows outside the condition do not touch the index at all, which reduces write overhead on busy tables.

The benefit grows with skew. If the indexed slice is a large fraction of the table, a partial index saves little and adds complexity. The sweet spot is when the condition selects a small percentage of rows that you query often.

CREATE INDEX jobs_pending_idx
    ON jobs (created_at)
    WHERE status = 'pending';

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

When Does the Planner Use a Partial Index?

The planner can use a partial index only if the query's conditions imply the index predicate. With a constant condition like status = 'pending', the match is straightforward. The query must include a condition at least as restrictive as the index predicate, otherwise the index could miss qualifying rows, and the planner will not use it.

Parameterized queries need attention. If the status value arrives as a bind parameter, the planner may build a generic plan that cannot prove the parameter equals the index condition, and it may skip the partial index. Behavior depends on the driver and on how prepared statements are configured, so test with EXPLAIN using the same query shape your application sends, and check the documentation for plan caching behavior in your version.

Always verify with realistic data. Run EXPLAIN on the real query, confirm the index appears in the plan, and compare it with and without the index. Statistics also matter, so keep ANALYZE current to give the planner accurate estimates of how selective the predicate is.

Where Else Are Partial Indexes Useful?

Several common patterns benefit from this feature beyond the pending-work queue:

• Soft deletes with uniqueness: a unique partial index on email WHERE deleted_at IS NULL allows a deleted account's email to be reused while keeping active emails unique.
• Rare flags: indexing rows where a boolean flag is true when only a tiny fraction qualifies, such as flagged or failed records.
• Hot recent data: indexing only rows in an active state, while historical rows stay unindexed or covered by a different index.
• Excluding nulls: indexing only rows where an optional column is not null, avoiding wasted entries for the many rows without a value.

CREATE UNIQUE INDEX users_email_active_uniq
    ON users (email)
    WHERE deleted_at IS NULL;

What Pitfalls Should You Avoid?

The predicate may use only immutable expressions, so you cannot use functions like now() in it. A condition such as created_at > now() minus an interval cannot be indexed, because the set of matching rows would silently change over time. Use a fixed condition and handle time windows in the query or through partitioning instead.

Do not create many near-duplicate partial indexes for different values. Each one adds write cost and maintenance, and a single regular index may serve better when you query many different values. Also remember that dropping or changing the status values in your application can quietly invalidate assumptions, so keep predicates in sync with the code and document why each partial index exists.

Frequently Asked Questions

Is a partial index faster than a full index?

For queries that target the indexed slice, usually yes, because it is smaller and cheaper to scan and maintain. For queries outside the slice, it cannot help at all.

Can I use a partial index for a unique constraint?

Yes, a unique partial index enforces uniqueness only among rows that satisfy the predicate. It is a common way to implement uniqueness with soft deletes.

Why is the planner ignoring my partial index?

Usually the query does not clearly imply the index predicate, or a parameterized plan hides the value. Compare the query condition with the index condition and test with EXPLAIN.

Do partial indexes reduce write overhead?

Yes, because rows outside the predicate are not added to the index. Updates that move rows in or out of the slice still modify it.

Summary

Use partial indexes when queries consistently target a small, well-defined slice of a large table. Keep predicates simple and immutable, match them exactly in queries, and verify with EXPLAIN. Confirm syntax and planner behavior in the PostgreSQL documentation for the version you run.