PostgreSQL Partial Indexes: Targeted Indexing for Faster Queries

I recently reviewed a database where the orders table had a 400 MB index on the status column. 95% of the rows were 'completed' -- queries never touched them. The application only ever queried pending, processing, and failed orders. A partial index on those 5% of rows: 20 MB. Same query performance, 95% less disk, and dramatically less write overhead.

Partial indexes are one of PostgreSQL's most underappreciated features. Let me show you when and how to use them.

What Is a Partial Index?

A partial index is any index with a WHERE clause in its definition:

CREATE INDEX idx_orders_active_status
    ON orders (status)
    WHERE status IN ('pending', 'processing', 'failed');

Only rows matching the condition are stored in the index. Rows that don't match are invisible to the index -- they don't consume space, and writes to those rows don't trigger index maintenance.

Three Patterns Where Partial Indexes Shine

1. Skewed status columns

Most tables have a status column where one value dominates. 95% of orders are completed, 98% of jobs are processed, 99% of notifications are read. Your queries target the minority.

-- Full index: 400 MB, includes 47.5M completed orders you never query
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index: 20 MB, includes only the 2.5M rows you actually search
CREATE INDEX CONCURRENTLY idx_orders_active_status
    ON orders (status)
    WHERE status IN ('pending', 'processing', 'failed');

The 95% size reduction keeps the index in shared_buffers longer and eliminates write amplification for completed orders.

2. Soft-delete uniqueness

"Email must be unique among active users, but deleted users can share emails."

A regular unique index can't do this. A partial unique index handles it natively:

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

Works with upsert:

INSERT INTO users (email, name, deleted_at)
VALUES ('user@example.com', 'Jane Smith', NULL)
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET name = EXCLUDED.name;

One critical gotcha: the ON CONFLICT WHERE clause must match the index's WHERE clause exactly. Character-for-character. A mismatch gives you a cryptic error about "no unique or exclusion constraint matching the ON CONFLICT specification."

3. Boolean job queues

A background_jobs table with 50 million completed jobs and 500 unprocessed ones:

CREATE INDEX CONCURRENTLY idx_jobs_unprocessed
    ON background_jobs (created_at)
    WHERE is_processed = false;

This indexes 500 rows instead of 50 million. The index is tiny, inserts to completed jobs don't touch it, and queue polling queries are instant.

Finding Candidates in Your Database

Look for columns where one value dominates:

SELECT
    tablename,
    attname AS column_name,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
    AND n_distinct BETWEEN 2 AND 20
    AND most_common_freqs[1] > 0.8
ORDER BY most_common_freqs[1] DESC;

A column where the most common value covers 80%+ of rows is a prime partial index candidate -- index the other 20%.

Check your query patterns:

SELECT
    substring(query, 1, 120) AS query_preview,
    calls,
    mean_exec_time AS avg_ms
FROM pg_stat_statements
WHERE query ILIKE '%WHERE%status%'
    AND calls > 100
ORDER BY total_exec_time DESC
LIMIT 10;

If queries consistently filter on the minority values, you have a winner.

Verifying the Planner Uses Your Partial Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

You need to see Index Scan using idx_orders_active_status. If you see Seq Scan, the planner couldn't prove your query condition implies the index condition.

The planner is conservative. WHERE status = 'pending' clearly implies WHERE status IN ('pending', 'processing', 'failed'), so it works. But WHERE status != 'completed' might not be recognized as equivalent. When in doubt, make your query filter explicit.

When Partial Indexes Don't Help

The benefit is proportional to selectivity:

• Indexing 5% of rows: 95% size reduction -- enormous benefit
• Indexing 20%: 80% reduction -- still significant
• Indexing 80%: 20% reduction -- barely worth the complexity

If your WHERE clause covers most of the table, a regular index is simpler and performs nearly as well.

Building Partial Indexes Into Your Process

When adding a status, flag, or soft-delete column to a schema:

1. Check the expected value distribution
2. If one value will dominate (>80%), plan a partial index on the minority
3. Add the partial index in the same migration that creates the column
4. Use descriptive names: idx_orders_active_status, not idx_orders_status_partial
5. If using with ON CONFLICT, document the WHERE clause match requirement

Don't wait until the table has millions of rows and someone reports slow queries. The partial index should be part of the initial schema design.

Quick Reference

-- Partial index (status pattern)
CREATE INDEX CONCURRENTLY idx_name
    ON table (column)
    WHERE status IN ('value1', 'value2');

-- Partial unique (soft-delete pattern)
CREATE UNIQUE INDEX CONCURRENTLY idx_name
    ON table (column)
    WHERE deleted_at IS NULL;

-- Partial index (boolean pattern)
CREATE INDEX CONCURRENTLY idx_name
    ON table (sort_column)
    WHERE flag = false;

-- Verify usage
EXPLAIN (ANALYZE) SELECT * FROM table WHERE condition;