PostgreSQL Partial Indexes: Index Less, Write Faster

The problem: a full index on a skewed table

Imagine an orders table with 50 million rows. Ninety-five percent of those rows have a status of 'completed' or 'cancelled'; only a small fraction are 'pending'. Your application repeatedly queries the pending orders, but a standard B-tree index on created_at includes entries for all 50 million rows.

That full index costs storage and, more importantly, adds write amplification—the extra disk I/O required to maintain index entries every time a row is inserted, updated, or deleted. Even rows you never search still generate index maintenance work.

How partial indexes work

A partial index adds a WHERE clause to CREATE INDEX. Only rows that satisfy the predicate enter the index structure. In the example above, an index on created_at filtered to WHERE status = 'pending' would be dramatically smaller and cheaper to maintain.

PostgreSQL supports partial indexes across all access methods, not just the default B-tree. You can use them with GiST, GIN, and BRIN indexes as well. They are a long-standing feature available in all currently supported major versions.

The critical rule is that the planner will only choose a partial index when it can prove your query predicate implies the index predicate. This check is syntactic, not logical. The planner compares the shapes of the conditions, not their real-world equivalence.

A worked example: pending orders

Assume a table defined as follows:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL,
  total numeric NOT NULL
);

Your application runs this query frequently:

SELECT id, created_at, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 20;

A full index on created_at helps the sort, but it still indexes archived rows. A partial index targets only the working set:

CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';

Where to run it: any SQL client connected to the database, such as psql. You need privileges to create an index on the table, typically table ownership or superuser rights.

Expected check: run EXPLAIN (ANALYZE, BUFFERS) on the query above. You should see an index scan using idx_orders_pending_created instead of a sequential scan (reading every row in the table) or a full index scan. If the planner falls back to a sequential scan, the predicate shapes do not match.

Storage check: compare sizes with pg_relation_size():

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders';

The partial index should be far smaller than a full index on the same column.

Usage check: after the application has run for a while, confirm the index is actually used:

SELECT idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_pending_created';

If idx_scan remains zero, the index is consuming write I/O and vacuum cycles without benefiting reads.

Rollback: if you need to remove it, run DROP INDEX idx_orders_pending_created; in the same database. This changes state by freeing the index storage and removing its maintenance overhead.

Limitations and trade-offs

Partial indexes are not a universal optimization. Three limitations matter most:

• Syntactic matching is brittle. A query written as WHERE status <> 'completed' will not use an index defined with WHERE status = 'pending', even if the two conditions cover the same rows in your data. The planner cannot prove the implication.
• Uniqueness is not enforceable across the whole table. You cannot use a partial index to enforce a UNIQUE constraint unless the predicate is guaranteed to cover every row that could violate uniqueness. For example, CREATE UNIQUE INDEX ... WHERE active = true does not prevent duplicate values among inactive rows.
• They do not solve retention. If your goal is to bulk-delete old rows or shrink the table itself, use table partitioning or time-based retention strategies instead. A partial index only reduces index size, not heap size.

What to do next

1. Query pg_stat_user_indexes for large indexes with idx_scan = 0 or very low scan counts.
2. Identify queries that consistently filter on the same column and value, such as a status flag or a soft-delete marker.
3. Create a partial index matching that predicate, then verify with EXPLAIN (ANALYZE, BUFFERS) that representative queries use it.
4. Monitor pg_stat_user_indexes.idx_scan and pg_relation_size() after deployment to confirm the index earns its keep.

Partial indexes reward precision. When your access pattern is skewed and your query predicates are stable, indexing less can make both reads and writes faster.