PostgreSQL Partial Indexes: Reduce Index Size and Speed Up Writes for Skewed Data

The Problem: Indexing Low-Cardinality Columns with Skewed Access Patterns

Tables often have columns like status, deleted_at, or priority where only a small fraction of rows match the values your application queries most. A full B-tree index on such a column wastes space and slows every INSERT, UPDATE, and DELETE because the index must be maintained for rows you rarely search.

Useful takeaway: A partial index indexes only rows that satisfy a WHERE predicate. It can be 10–100× smaller than a full index, cuts write amplification for the majority of rows, and the planner uses it automatically when the query predicate implies the index predicate.

Syntax and a Worked Example

Create a partial index with:

CREATE INDEX idx_orders_pending ON orders (customer_id, created_at) WHERE status = 'pending';

Run this in psql or any client connected as a role with CREATE privilege on the schema (typically the table owner or a superuser). The predicate status = 'pending' is stored in pg_index.indpred and evaluated at index build time and on every row modification to decide whether to add an entry.

Assume an orders table with 10 million rows where only ~5% have status = 'pending'. The partial index holds ~500,000 entries instead of 10 million — roughly 20× smaller. Queries filtering WHERE status = 'pending' use an index scan; queries for other statuses ignore the index entirely.

Planner Behavior: When the Index Is Used

The planner uses a partial index only when it can prove the query's WHERE clause implies the index predicate. Examples:

• WHERE status = 'pending' → matches directly.
• WHERE status IN ('pending', 'processing') → used if a CHECK constraint guarantees status only takes those two values.
• WHERE status = 'pending' AND customer_id = 42 → used (predicate implied).

It will not use the index for:

• WHERE status IS NULL (predicate not implied).
• WHERE status = 'pending' OR priority = 'high' (OR across non-matching column).
• Predicate hidden behind a function: WHERE lower(status) = 'pending'.

Unique Partial Indexes for Soft-Delete Patterns

Enforce uniqueness only among active rows:

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

This allows multiple deleted users with the same email while preventing duplicates among active users. Test it:

INSERT INTO users (email, deleted_at) VALUES ('[contact removed]', NULL);
INSERT INTO users (email, deleted_at) VALUES ('[contact removed]', now()); -- succeeds

Limitation: Foreign keys cannot reference a partial unique index. The referenced index must be a full unique index or primary key.

Expression Indexes Combined with Partial Predicates

Index a computed value for a subset:

CREATE INDEX idx_recent_errors ON logs ((created_at::date)) WHERE level = 'ERROR';

Enables fast daily error counts without indexing INFO/DEBUG rows. The expression (created_at::date) is evaluated only for rows where level = 'ERROR'.

Monitoring Usage and Bloat

Check actual scans:

SELECT indexrelid::regclass, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelid = 'idx_orders_pending'::regclass;

Zero idx_scan after a representative workload means the planner isn't using the index — verify with EXPLAIN.

Confirm the stored predicate:

SELECT indexrelid::regclass, pg_get_expr(indpred, indrelid)
FROM pg_index WHERE indpred IS NOT NULL;

Common Mistakes and Limitations

Parameterized Queries (Prepared Statements)

With PREPARE p(text) AS SELECT * FROM orders WHERE status = $1;, the planner cannot prove $1 = 'pending' at plan time, so it may choose a sequential scan. Workarounds:

• Use EXECUTE p('pending') with EXPLAIN EXECUTE to verify.
• Set plan_cache_mode = force_custom_plan (PG 12+) or use RECOMPILE in client drivers.
• Embed literal values in application-generated SQL when the predicate value is fixed.

OR Conditions

WHERE status = 'pending' OR priority = 'high' prevents index use unless a CHECK constraint or partition makes the implication provable. Restructure queries or add a covering index.

IS NULL Predicate Support

Before PostgreSQL 15, WHERE status IS NULL in a partial index definition was rejected. deleted_at IS NULL works because deleted_at is a timestamp column; the restriction applied to IS NULL on any column. Verify your version with SELECT version();.

Vacuum Still Scans the Whole Table

A partial index reduces index bloat, but VACUUM must still scan the entire table for dead tuples. Table bloat is unchanged.

Concurrent Build Risks

CREATE INDEX CONCURRENTLY ... WHERE predicate takes a ShareUpdateExclusiveLock. If the predicate throws an error for any row (e.g., division by zero in an expression), the build fails. Test the predicate first:

SELECT 1 FROM orders WHERE status = 'pending' LIMIT 1;

Verification Checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending'; — look for Index Scan using idx_orders_pending.
2. Check pg_stat_user_indexes.idx_scan after production workload; zero scans = planner mismatch.
3. Test parameterized vs literal plans: PREPARE p(text) AS ...; EXPLAIN EXECUTE p('pending');.
4. Verify unique partial index behavior with the two INSERT statements above.
5. Attempt a foreign key to the partial unique index — expect an error confirming the limitation.

When to Choose a Partial Index

• Column has highly skewed distribution (e.g., 95% status = 'completed', 5% 'pending').
• Application queries the minority value frequently.
• Write throughput matters — skipping index maintenance for the majority of rows reduces WAL volume and lock contention.
• You need a unique constraint on a subset (soft delete, active/inactive).

If the predicate matches a large or growing fraction of rows, a full index may be simpler and the planner more predictable.