Postgres under list pressure: partial indexes that skip cold rows

Your tickets table has a million rows. The admin inbox always asks for status = 'open'. You added CREATE INDEX ON tickets (created_at). The list is still slow, and EXPLAIN keeps showing a sequential scan or a fat index that mostly stores closed tickets.

Postgres already has a precise tool for this: a partial index. It indexes only the rows that match a predicate, so for lists that filter on status or soft-delete it often stays smaller and cheaper to maintain. Official docs: Partial Indexes.

Problem: a full index for a hot filter

Hypothetical schema: a support inbox. Most rows are closed. Almost every list query cares about open ones.

id           bigserial PRIMARY KEY,
  org_id       bigint NOT NULL,
  status       text NOT NULL CHECK (status IN ('open', 'closed')),
  created_at   timestamptz NOT NULL DEFAULT now(),
  subject      text NOT NULL
);

-- Full index: every row, including years of closed tickets
CREATE INDEX tickets_created_at_idx ON tickets (created_at);

A typical list:

FROM tickets
WHERE status = 'open'
  AND org_id = 42
ORDER BY created_at DESC
LIMIT 50;

The planner may still refuse a useful index path if selectivity is wrong, or it may use tickets_created_at_idx and then filter out closed rows. Either way you pay to maintain index entries for rows the inbox never reads.

Solution: partial index only on hot rows

Same idea as the docs' "unbilled orders" example: keep the hot subset in the index.

ON tickets (org_id, created_at DESC)
  WHERE status = 'open';

The index stores entries only for open tickets. Closed rows do not bloat it. Updates that flip open → closed remove the row from this index; flips the other way add it. That is documented behavior of a partial index predicate, not magic.

Check the plan (illustrative shape; costs depend on your stats):

SELECT id, subject, created_at
FROM tickets
WHERE status = 'open'
  AND org_id = 42
ORDER BY created_at DESC
LIMIT 50;

You want something that mentions tickets_open_org_created_idx (Index Scan or Bitmap Index Scan), not a seq scan over the whole table. How to read plans: Using EXPLAIN.

Pitfall: the query must imply the predicate

Postgres will use a partial index only when it can prove that the query's WHERE implies the index predicate. Matching happens at plan time. The docs are blunt: there is no fancy theorem prover. Simple inequality implications work (x < 1 implies x < 2); otherwise the predicate usually needs to appear in the same form in the query.

So this can use the index:

This cannot, even if every remaining row happens to be open:

-- planner cannot assume status = 'open'

And a prepared parameter like WHERE amount < $1 will not match a partial index WHERE amount < 100, because $1 is not known to imply that bound for every possible value. Soft-delete variants have the same rule: if the index is WHERE deleted_at IS NULL, the query needs that condition too (or something the planner recognizes as implying it).

Keep the predicate text aligned with the filters your app actually sends. ORM layers that rewrite status = 'open' into a different expression can silently drop the index from the plan.

Bonus: partial unique constraints

Another documented use: uniqueness on a subset. Example from the docs shape, adapted to a web app: at most one active subscription per user, any number of canceled ones.

ON subscriptions (user_id)
  WHERE status = 'active';

That is a constraint, not only a speed trick. Failures show up as unique violations on insert/update of a second active row.

Trade-off: when not to reach for partial indexes

The docs warn against a wall of non-overlapping partial indexes (WHERE category = 1, = 2, … = N) as a homemade partition scheme. Prefer one composite index (category, data) or real table partitioning when the table is that large. The planner does not understand that those partial indexes are mutually exclusive, so it burns effort testing each one.

Also: if open tickets are most of the table, a partial index buys little. The win shows up when the hot filter selects a small fraction and that filter is stable in your workload.

Verdict

1. Pick one list endpoint that always filters the same way (status, soft-delete, is_read = false).
2. Add a partial index whose WHERE matches that filter and whose columns match ORDER BY / equality keys.
3. Run EXPLAIN (ANALYZE, BUFFERS) on staging data that looks like production size. Confirm the index name appears.
4. If the plan ignores it, check predicate wording first, then stats (ANALYZE), before adding more indexes.
Partial indexes are not a silver bullet. They are a precise tool for the boring case every CRUD app has: most rows are cold, the UI only asks for the hot ones, and you were indexing both.