Partial Index

ELI5 — The Vibe Check

A partial index only indexes the rows you actually care about. Why index 10 million archived orders when you only ever query the active ones? It's like building a phone book that only lists people who actually answer their phone. Smaller, faster, smarter.

Real Talk

A partial index (filtered index) includes only rows that satisfy a specified WHERE clause predicate. This reduces index size, speeds up inserts and updates, and improves query performance for filtered queries. They're particularly effective for columns with skewed distributions or boolean flags where only one value is queried.

Show Me The Code

-- Only index active users (much smaller than full index)
CREATE INDEX idx_active_users
ON users(email)
WHERE is_active = true;

-- Only index unprocessed orders
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';

When You'll Hear This

"A partial index on status = 'pending' is way smaller than indexing all orders." / "Use partial indexes when you only query a subset of your data."