Medium java backend

Partial indexes for hot subsets

The 30 second answer

A partial index only covers rows that match a condition, for example only orders whose status is pending. If most queries hit that small hot subset, the index is much smaller, fits in memory, and is cheaper to maintain on writes. The catch is that the query's WHERE clause must clearly imply the index condition, otherwise the planner cannot use it.

A partial index covers only the rows that match its WHERE clause, so it is smaller and cheaper to update. The planner uses it only when the query's WHERE implies the index predicate.

CREATE INDEX orders_active_idx ON orders (customer_id) WHERE status = 'active';
Sources (1)
  1. PostgreSQL 17: Partial Indexes