What is a partial index in PostgreSQL and when is it useful?
A partial index has a WHERE clause and indexes only the rows that match it. It is smaller and faster to maintain, and ideal when queries target a small, well-defined slice such as unprocessed jobs or active users.
Explain it simply
Think of a library register where 99 percent of books are returned and only a few are currently issued. If you keep a short list of just the issued books, finding overdue ones is quick. A partial index is that short list: it only contains rows matching a condition.
It is smaller, so more of it fits in memory, and rows outside the condition cost nothing to update.
Example
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status text NOT NULL,
run_at timestamptz
);
CREATE INDEX jobs_pending_idx ON jobs (run_at) WHERE status = 'pending';
-- Uses the index: the query's condition implies the index condition
SELECT id FROM jobs WHERE status = 'pending' ORDER BY run_at LIMIT 10;
-- Cannot use it: status is not restricted to 'pending'
SELECT id FROM jobs WHERE run_at < now();Partial unique indexes also enforce rules like "one active subscription per user":
CREATE UNIQUE INDEX one_active_sub ON subscriptions (user_id) WHERE active;What to say in the interview
"A partial index covers only rows matching a WHERE condition, so it is small and cheap. The planner uses it only when the query's own condition implies the index condition. I use it for hot slices like pending jobs, and as a conditional unique constraint."
Common mistakes
- Writing a query condition that does not match the index predicate, so the index is ignored.