PostgreSQL Partial Indexes: Targeted Indexing for Faster Queries

A PostgreSQL partial index is an index with a WHERE clause that only indexes rows matching a condition. Instead of indexing every row in the table, you index exactly the subset your queries actually filter on — making the index smaller, faster to scan, and cheaper to maintain.

The Problem

Most PostgreSQL indexes cover every row in the table. For a 50-million-row orders table, an index on status includes all 50 million entries — even though 95% of those rows have status = 'completed' and your application only ever queries the 2.5 million active, pending, or failed orders.

This creates three compounding problems.

First, the index is much larger than it needs to be. A B-tree index on 50 million rows might consume 400 MB. The same index restricted to the 2.5 million non-completed rows would be 20 MB. That 380 MB difference matters for buffer cache efficiency — a smaller index stays in shared buffers longer, reducing disk I/O for every query that uses it.

Second, write performance suffers. Every INSERT, UPDATE, or DELETE that touches an indexed column must update the full index, even if the new or changed row does not match the queries you are trying to optimize. A partial index skips maintenance for rows outside its WHERE clause, reducing write amplification on high-throughput tables.

Third, you cannot enforce partial unique constraints without a partial index. A common requirement is "email must be unique among active users, but deleted users can share emails." A regular unique index enforces uniqueness across all rows, including deleted ones. A partial unique index — CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL — enforces the constraint only where it matters.

Despite these advantages, partial indexes are underused in practice. Many developers learn about standard B-tree indexes and never discover the WHERE clause option. The result is oversized indexes, missed optimization opportunities, and awkward application-level workarounds for constraints that PostgreSQL can handle natively.

How to Detect It

Start by identifying indexes that are much larger than they need to be, and tables where queries consistently filter on the same condition:

-- Find large indexes where the underlying table has a skewed column distribution
-- These are candidates for partial indexing
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
JOIN pg_indexes USING (schemaname, tablename, indexname)
WHERE pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- indexes > 10 MB
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Next, check for columns with highly skewed value distributions — the hallmark of a good partial index candidate:

-- Find columns where one value dominates (e.g., 95% of rows have status='completed')
-- These are prime candidates for partial indexes on the MINORITY values
SELECT
    tablename,
    attname AS column_name,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
    AND n_distinct BETWEEN 2 AND 20  -- low cardinality columns
    AND most_common_freqs[1] > 0.8   -- dominant value covers >80% of rows
ORDER BY most_common_freqs[1] DESC;

Finally, look at your actual query patterns to confirm which subset of rows is being queried:

-- Check pg_stat_statements for queries that filter on status-like columns
SELECT
    substring(query, 1, 120) AS query_preview,
    calls,
    mean_exec_time AS avg_ms,
    rows
FROM pg_stat_statements
WHERE query ILIKE '%WHERE%status%'
    AND calls > 100
ORDER BY total_exec_time DESC
LIMIT 10;

If you see queries that consistently filter on status != 'completed' or deleted_at IS NULL hitting tables with millions of rows, you have a strong partial index candidate.

How myDBA.dev Shows It

myDBA.dev's Index Advisor analyzes your query patterns alongside index usage statistics to identify partial index opportunities. When it detects a table where queries consistently filter on a specific condition and the existing full index is significantly oversized for the actual query workload, it flags the table with a recommendation to create a partial index instead.

The advisor shows the current index size, the estimated size of a partial index covering only the queried subset, and the queries that would benefit. This makes it straightforward to calculate the space savings and performance improvement before making any changes.

On the Queries page, you can drill into specific queries to see their EXPLAIN plans and confirm whether a sequential scan or an oversized index scan is the bottleneck. Queries that would benefit from a partial index typically show an Index Scan reading far more index pages than necessary for the number of rows actually returned.

How to Fix It

Create a partial index using the WHERE clause that matches your query filter. The index will only contain entries for rows satisfying the condition:

-- Instead of indexing ALL orders by status:
-- CREATE INDEX idx_orders_status ON orders (status);

-- Index only the rows your queries actually filter on:
CREATE INDEX CONCURRENTLY idx_orders_active_status
    ON orders (status)
    WHERE status IN ('pending', 'processing', 'failed');

For soft-delete patterns, index only the non-deleted rows:

-- Index active users only (skip deleted rows entirely)
CREATE INDEX CONCURRENTLY idx_users_active_email
    ON users (email)
    WHERE deleted_at IS NULL;

Partial unique indexes solve the "unique among active records" problem elegantly:

-- Email must be unique, but only among active users
-- Deleted users can have duplicate emails without violating the constraint
CREATE UNIQUE INDEX CONCURRENTLY idx_users_unique_active_email
    ON users (email)
    WHERE deleted_at IS NULL;

This works with ON CONFLICT for upsert operations too:

-- Upsert using the partial unique index as the conflict target
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;

The ON CONFLICT clause must exactly match the partial index's WHERE condition. If the conditions do not match character-for-character, PostgreSQL will not recognize the partial index as the conflict arbiter and the query will fail.

For boolean flag patterns, partial indexes are particularly efficient:

-- Index only unprocessed jobs (typically a tiny fraction of the table)
CREATE INDEX CONCURRENTLY idx_jobs_unprocessed
    ON background_jobs (created_at)
    WHERE is_processed = false;

-- Index only active subscriptions
CREATE INDEX CONCURRENTLY idx_subscriptions_active
    ON subscriptions (customer_id, plan_id)
    WHERE is_active = true AND cancelled_at IS NULL;

After creating a partial index, verify that the query planner uses it:

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

You should see Index Scan using idx_orders_active_status in the output. If the planner still chooses a sequential scan, check that your query's WHERE clause matches or is a subset of the partial index's WHERE clause. The planner can only use a partial index when it can prove that the query condition implies the index condition.

How to Prevent It

Build partial index awareness into your schema design process. Whenever you add a status column, a soft-delete flag, or any column with a heavily skewed distribution, consider whether queries will consistently target the minority values. If so, design the partial index from the start rather than retrofitting it after the table has grown to millions of rows.

Establish naming conventions that make partial indexes self-documenting. Prefixes like idx_tablename_active_ or idx_tablename_pending_ immediately communicate the index scope to anyone reading the schema. Avoid generic names like idx_orders_status_partial — the name should describe which rows are indexed, not just that it is partial.

Review index sizes as part of your regular database health checks. When an index is significantly larger than the working set of rows your queries actually touch, it is a signal that a partial index could replace it. myDBA.dev's Index Advisor surfaces these opportunities automatically by comparing index sizes against actual query patterns.

Watch for the ON CONFLICT gotcha. If you plan to use a partial unique index with upsert queries, ensure the development team knows the WHERE clause must match exactly. A mismatch produces a confusing error (there is no unique or exclusion constraint matching the ON CONFLICT specification) that is hard to debug if you are not familiar with partial index semantics.

Test partial index performance in staging with production-scale data. The performance benefit of a partial index is proportional to the selectivity of its WHERE clause — indexing 5% of rows gives you a 95% size reduction, but indexing 80% of rows barely helps. If the condition covers most of the table, a regular index may perform just as well with less complexity.

FAQ

What is partial indexing in PostgreSQL?

A partial index in PostgreSQL is a standard B-tree (or GiST, GIN, etc.) index that includes a WHERE clause in its definition. Only rows satisfying the WHERE condition are stored in the index. This makes the index smaller and faster for queries that filter on the same condition. You create one with CREATE INDEX idx_name ON table (column) WHERE condition. The query planner will use a partial index whenever it can determine that a query's filter implies the index's WHERE clause.

What is the difference between a sparse index and a partial index?

In PostgreSQL terminology, a "partial index" is the correct term — it indexes a subset of rows defined by a WHERE clause. "Sparse index" is a term from other database systems (like MongoDB or traditional B-tree theory) that refers to indexes which only contain entries for documents or rows that have the indexed field. PostgreSQL does not use the term "sparse index" in its documentation. However, you can achieve a similar effect with a partial index: CREATE INDEX idx_name ON table (column) WHERE column IS NOT NULL indexes only rows where the column has a value, which is functionally equivalent to a sparse index.

What does partially indexed mean?

A table is "partially indexed" when it has one or more partial indexes — indexes that cover a subset of the table's rows rather than all of them. This is intentional and desirable. It means you have targeted your indexing strategy to cover the specific query patterns your application uses, rather than blindly indexing every row. A partially indexed table often performs better than a fully indexed one because the indexes are smaller, fit in memory more easily, and require less maintenance on writes.