Optimizing PostgreSQL Performance with Partial Indexes

When a query consistently filters for a specific subset of data—such as "active" records or "unprocessed" tasks—a standard index wastes storage and slows down writes by indexing rows that are never queried. A partial index solves this by applying a WHERE clause to the index itself, ensuring only rows that satisfy the predicate are stored in the index tree.

Reducing Index Bloat and Write Overhead

In a typical production table, you might have millions of rows, but only a small fraction are "active." Indexing every single row increases the B-tree depth and requires the database to update the index every time any row is inserted or modified, regardless of its status.

The primary advantage of a partial index is that it reduces the index size on disk and minimizes the I/O required for maintenance. This is particularly effective for "soft-delete" patterns where rows marked as deleted are rarely accessed but occupy significant space in a full index.

Implementing Partial Indexes

A partial index is created using the standard CREATE INDEX syntax, appended with a WHERE clause. This predicate must use immutable functions and columns available in the table.

-- Run as a user with CREATE permissions on the schema
-- This index only tracks users who are currently active
CREATE INDEX idx_active_users_email 
ON users (email) 
WHERE status = 'active';

Conditional Uniqueness

Partial indexes can also be defined as UNIQUE. This allows you to enforce uniqueness constraints on a subset of data, which is impossible with a standard table-level unique constraint.

-- Ensure a user has only one 'primary' address, 
-- while allowing multiple 'secondary' addresses
CREATE UNIQUE INDEX idx_single_primary_address 
ON addresses (user_id) 
WHERE address_type = 'primary';

Worked Example: Selective Task Queue

Consider a task queue where most tasks are completed and only a few are pending. Querying for pending tasks is the most frequent operation.

-- Setup table
CREATE TABLE task_queue (
  task_id serial PRIMARY KEY,
  payload text,
  status text NOT NULL,
  created_at timestamp DEFAULT now()
);

-- Populate with 100,000 rows (95% completed, 5% pending)
INSERT INTO task_queue (payload, status)
SELECT 
  'Task data ' || i, 
  CASE WHEN i % 20 = 0 THEN 'pending' ELSE 'completed' END
FROM generate_series(1, 100000) i;

-- Create the partial index
CREATE INDEX idx_pending_tasks ON task_queue (created_at) 
WHERE status = 'pending';

Risk: Creating an index on a live production table locks it in ACCESS EXCLUSIVE mode, blocking writes. To avoid downtime, use the CONCURRENTLY keyword:

CREATE INDEX CONCURRENTLY idx_pending_tasks ON task_queue (created_at) 
WHERE status = 'pending';

Planner Requirements and Limitations

For the PostgreSQL query planner to use a partial index, the query's WHERE clause must explicitly include the predicate used in the index. If the query is SELECT * FROM task_queue WHERE created_at < '2023-01-01', the planner cannot use idx_pending_tasks because it cannot guarantee that all rows in the index satisfy the query, nor that all rows satisfying the query are in the index.

Common Pitfalls

• Over-indexing: Creating multiple partial indexes that overlap can negate the write-performance gains.
• Dynamic Predicates: Using volatile functions (like now()) in the WHERE clause is not permitted as the index must be static.
• Low Selectivity: If the predicate matches 80% of the table, the overhead of managing the partial index is nearly the same as a full index, and the planner may still prefer a sequential scan.

Verification and Validation

To verify the index is functioning as intended, use EXPLAIN ANALYZE to check the execution plan.

-- This query should trigger an Index Scan using idx_pending_tasks
EXPLAIN ANALYZE 
SELECT * FROM task_queue 
WHERE status = 'pending' 
ORDER BY created_at ASC 
LIMIT 10;

Check the output for Index Scan using idx_pending_tasks. You can also compare the physical size of the partial index against a full index using pg_relation_size:

SELECT pg_size_pretty(pg_relation_size('idx_pending_tasks'));

Rollback: If the index does not provide the expected performance gain, remove it using:

DROP INDEX CONCURRENTLY idx_pending_tasks;