Most indexes cover every row of a table. That’s wasteful when the queries that matter only ever look at a small slice: the 4,000 pending jobs in a table of two million, the unshipped orders, the accounts that aren’t soft-deleted. A partial index has a WHERE clause, and only rows that satisfy it get an entry. It’s smaller, faster to scan, cheaper to maintain for rows outside the slice, and it enables a kind of uniqueness rule that a normal constraint can’t express.
The numbers here are from PostgreSQL 16.15 on a two-million-row jobs table.
A job queue where almost everything is done
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
queue text NOT NULL,
status text NOT NULL,
run_at timestamptz NOT NULL,
payload jsonb
);
-- 2,000,000 rows: 1,975,423 done, 20,577 failed, 4,000 pendingThe worker’s hot query asks for the next pending jobs in its queue:
SELECT id FROM jobs
WHERE status = 'pending' AND queue = 'email'
ORDER BY run_at
LIMIT 10;A normal composite index on (queue, run_at) would hold two million entries, nearly all for completed jobs the worker will never ask about again. Compare it with a partial index on the same columns:
CREATE INDEX jobs_full ON jobs (queue, run_at);
CREATE INDEX jobs_pending ON jobs (queue, run_at) WHERE status = 'pending'; relname | pg_size_pretty
--------------+----------------
jobs_full | 60 MB
jobs_pending | 144 kBThe partial index is about 400 times smaller. It fits in a handful of pages that stay in cache permanently, and since completed jobs never enter it, the index doesn’t grow with history. With the full index dropped, here’s the worker query:
Limit (actual rows=10 loops=1)
-> Index Scan using jobs_pending on jobs (actual rows=10 loops=1)
Index Cond: (queue = 'email'::text)Notice that status = 'pending' doesn’t appear as a condition at all. Every entry in the index already satisfies it, so there’s nothing left to check. The scan walks the email section in run_at order and stops after 10.
Choosing the columns and the predicate
Two separate decisions go into a partial index:
- The predicate picks the rows: a condition that’s true for a small, stable fraction that hot queries filter on. Status values,
deleted_at IS NULL,archived = false,amount > 10000for a fraud review queue. - The key columns serve the rest of the query, using the same rules as any composite index: equality first, then the sort or range column. Here,
queuethenrun_at.
A common mistake is indexing the predicate column itself, as in CREATE INDEX ON jobs (status) WHERE status = 'pending'. Every key in that index is the same value. It does find pending rows, but it can’t help with queue or ordering, so PostgreSQL has to fetch and sort them.
When the planner can’t use it
PostgreSQL uses a partial index only if it can prove, while planning, that the query’s WHERE clause implies the index’s predicate. It does that with simple logical reasoning, not by running anything. A few cases from the same table:
A different value. WHERE status = 'failed' AND queue = 'email' can’t use jobs_pending; failed rows aren’t in it. The plan fell back to a parallel sequential scan and a sort. If failed jobs get queried too, they need their own partial index or a broader predicate like WHERE status <> 'done'.
An equivalent spelling. WHERE status IN ('pending') did use the index, because PostgreSQL simplifies a one-element IN to =. Don’t count on the prover being clever in general, though. It understands operators and simple implications like x > 100 implying x > 50 for built-in types; it won’t see through function calls or arbitrary expressions.
A parameter. This is the one that bites real applications. With a prepared statement, the value isn’t known when a generic plan is built:
PREPARE q(text) AS
SELECT id FROM jobs WHERE status = $1 AND queue = 'email' ORDER BY run_at LIMIT 10;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE q('pending'); Limit
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: run_at
-> Parallel Seq Scan on jobs
Filter: ((status = $1) AND (queue = 'email'::text))The generic plan has to work for any $1, including 'done', so it can’t use an index that only contains pending rows. With the default plan_cache_mode = auto, PostgreSQL builds custom plans with the real value for the first five executions and the same EXECUTE used jobs_pending. It may switch to a generic plan later if it thinks the generic one isn’t much worse on cost. ORMs and drivers that prepare statements automatically make this easy to hit. The safe fix is to write the predicate’s constant in the SQL text (status = 'pending'), parameterising only the other values. The prepared statements guide covers generic and custom plans in more depth.
Partial unique indexes as business rules
The other big use has nothing to do with speed. “A user may have many subscriptions over time, but only one active at once” can’t be a plain UNIQUE (user_id). As a partial unique index it’s one line:
CREATE TABLE subscriptions (
id serial PRIMARY KEY, user_id int NOT NULL,
plan text NOT NULL, ended_at timestamptz
);
CREATE UNIQUE INDEX one_active_subscription
ON subscriptions (user_id) WHERE ended_at IS NULL;
INSERT INTO subscriptions (user_id, plan, ended_at) VALUES (7, 'basic', '2026-03-01'); -- ok, ended
INSERT INTO subscriptions (user_id, plan) VALUES (7, 'pro'); -- ok, active
INSERT INTO subscriptions (user_id, plan) VALUES (7, 'team');
-- ERROR: duplicate key value violates unique constraint "one_active_subscription"
-- DETAIL: Key (user_id)=(7) already exists.Ended subscriptions aren’t in the index, so there can be any number of them. Like any unique index, it holds under concurrency: two requests creating an active subscription at the same moment can’t both succeed. The same pattern gives you “email unique among non-deleted users” with WHERE deleted_at IS NULL.
To use it with ON CONFLICT, repeat the predicate so PostgreSQL can match the index:
INSERT INTO subscriptions (user_id, plan) VALUES (7, 'team')
ON CONFLICT (user_id) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
INSERT INTO subscriptions (user_id, plan) VALUES (7, 'team')
ON CONFLICT (user_id) WHERE ended_at IS NULL DO NOTHING;
-- INSERT 0 0More on that in upserts and conflict semantics.
Costs and caveats
- Updates that move rows in or out of the slice still cost index work. When a job goes from pending to done, its entry has to leave
jobs_pending. A column referenced in an index predicate counts as indexed for HOT purposes, so status changes can’t be heap-only updates. That’s normally a fine trade for a queue. - Skewed slices change size. If an outage leaves 3 million jobs pending, the “tiny” index isn’t tiny anymore. It still works; just don’t assume its size in capacity planning.
- Predicates must be immutable.
WHERE run_at > now()isn’t allowed, because the set of matching rows would change by itself. Use a fixed cutoff and rebuild periodically if you need a moving window. - Too many partial indexes become a maintenance puzzle. One per status value is usually a sign that a single composite index with
statusas a leading equality column would serve better.
Interview practice: soft-deleted users and email uniqueness
A users table uses soft deletes (deleted_at timestamptz). Emails must be unique among active users, but a person who deleted their account must be able to sign up again with the same email. Login runs SELECT ... FROM users WHERE lower(email) = lower($1) AND deleted_at IS NULL thousands of times per minute. Design the index or indexes.
Answer
One partial unique expression index covers both needs:
CREATE UNIQUE INDEX users_active_email
ON users (lower(email))
WHERE deleted_at IS NULL;The UNIQUE plus the predicate enforces “unique among active users”: deleted rows aren’t in the index, so a returning user’s new row doesn’t collide with their old one. The lower(email) expression makes uniqueness case-insensitive, which matches how the login query compares.
The login query can use it because its WHERE contains the same expression, lower(email) = lower($1), and the literal condition deleted_at IS NULL, which implies the predicate even in a generic plan; the parameter is only in the indexed expression, not in the predicate. I’d check with EXPLAIN under plan_cache_mode = force_generic_plan to be sure. On an existing table, I’d first find conflicts with a GROUP BY lower(email) HAVING count(*) > 1 over active users, resolve them, and build the index with CREATE UNIQUE INDEX CONCURRENTLY to avoid blocking writes. Signup code should catch the unique violation (SQLSTATE 23505) rather than relying on a pre-check.