PostgreSQL Partial Indexes

Tip: Partial Index

CREATE INDEX idx_posts_published ON posts (created_at) WHERE published = true;

Only indexes published posts.

Gotcha: Query Must Match

SELECT * FROM posts WHERE published = true AND created_at > '2024-01-01';

The query's WHERE clause must match the index condition.

Tip: Unique Partial Index

CREATE UNIQUE INDEX idx_posts_unique_slug ON posts (slug) WHERE deleted_at IS NULL;

Unique constraint only for non-deleted posts.

Gotcha: Smaller Index Size

Partial indexes are smaller than full indexes, saving disk space and improving write performance.

Tip: Multiple Partial Indexes

CREATE INDEX idx_posts_draft ON posts (created_at) WHERE published = false;
CREATE INDEX idx_posts_published ON posts (created_at) WHERE published = true;

Gotcha: Planner May Not Use Partial Index

If the query doesn't include the index condition, the planner ignores the partial index.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Partial indexes are my secret weapon for query optimization. A CREATE INDEX ON orders (status) WHERE status = 'pending' creates an index that's tiny compared to a full-column index, yet perfectly covers queries looking for pending orders. I use partial indexes for: (1) filtering most-common query patterns, (2) excluding null values from indexes, and (3) creating unique constraints on conditional subsets. The space and maintenance savings compared to full indexes are dramatic.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)