Partial Index

DatabasesMiddb

The Question

What is a partial index and when would you use one?

What a Strong Answer Covers

• Index on a subset of rows
• WHERE condition in the index definition
• use case (e.g. pending transactions).

Senior-Level Answer

A partial index (also called a filtered index in SQL Server) is an index built only over the rows that satisfy a specified WHERE condition. Instead of indexing every row in a table, the index stores only entries for the rows matching the predicate, making it smaller, faster to scan, and cheaper to maintain on writes.

The canonical use case is a status column with extreme skew. Consider a `jobs` table with 10 million rows where 9.9 million have `status = 'completed'` and 100,000 have `status = 'pending'`. A full index on `status` is large and most of it is useless — queries almost always filter for `pending` jobs. A partial index `CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending'` covers only 100,000 rows. The index is 100x smaller, fits in cache, and queries for pending jobs execute dramatically faster.

Partial indexes also enforce conditional uniqueness. In PostgreSQL, you can create `CREATE UNIQUE INDEX idx_users_active_email ON users(email) WHERE deleted_at IS NULL` to enforce unique emails only among non-deleted users, allowing multiple soft-deleted rows with the same email without violating the constraint.

For the query optimizer to use a partial index, the query's WHERE clause must be logically compatible with the index predicate — the query's filter must be at least as restrictive. A query with `WHERE status = 'pending' AND created_at > now() - interval '1 day'` would use the partial index above. A query with `WHERE created_at > now() - interval '1 day'` (without the status filter) would not.

Write performance benefits from partial indexes: INSERT/UPDATE/DELETE only update the index when the row satisfies the index predicate. Rows outside the filter incur zero index maintenance cost. This makes partial indexes particularly valuable on append-heavy tables where new rows frequently fall outside the filtered subset.

What Separates a 2/3 from a 3/3

Common Mistakes

• Confusing partial indexes with composite indexes — partial indexes filter rows; composite indexes index multiple columns.
• Not knowing the optimizer compatibility rule — a partial index won't be used if the query's WHERE doesn't imply the index predicate.
• Overlooking the conditional uniqueness use case, which is one of the most practically valuable applications.
• Assuming partial indexes help with write performance on all rows — they only skip maintenance for rows outside the predicate.

Follow-Up Questions

• You have a 50M row orders table. 99% have status='archived'. How would you optimize a query for active orders? — Create a partial index WHERE status != 'archived' — the index only covers the 1% of active orders, stays small, and query uses it exclusively.
• How would you enforce that only one active subscription is allowed per user, while allowing multiple cancelled ones? — UNIQUE INDEX on (user_id) WHERE status = 'active' — PostgreSQL partial unique index enforces the constraint only for active rows.
• Will PostgreSQL use a partial index created with WHERE status = 'pending' for the query SELECT * FROM jobs WHERE status = 'pending' AND user_id = 5? — Yes — the query's filter implies the index predicate. The optimizer can use the partial index and apply the user_id filter as a post-scan condition.
• Compare a partial index to a full index on a column with uniform value distribution. When does the partial index not help? — If the filtered subset is a large fraction of the table, the partial index saves little space. Also if queries don't filter by the predicate column, the full index may be needed.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization