Should I use a partial index on a queue table where I only ever scan 'pending' rows?
Question
I have a `jobs` queue table in Postgres. It accumulates millions of `completed` rows, but I only ever query the few thousand `pending` ones: workers pull the next job with `WHERE status = 'pending' ORDER BY created_at`. Should I use a plain index on `status`, or a partial index with a `WHERE status = 'pending'` predicate? And when using a partial index, what do I need to watch out for so the planner actually picks it?
Answer
Short answer
Yes, a partial index is the textbook case for exactly this scenario.
Context
An index with a WHERE status = 'pending' predicate holds only a few thousand rows; it’s small, fits in memory, is fast to scan and cheap to update — and it steps right past the millions of dead rows without touching them. Which columns an index should carry and in what order is something I covered separately in the composite index order record; the question here is how to narrow that index further.
Why
-
A partial index only indexes matching rows. With
CREATE INDEX ... WHERE status='pending'the index holds a few thousand entries, not millions. That keeps it almost entirely in cache and shrinks the tree that has to be updated on every insert/update. -
It’s strictly better than a plain index on status. A plain index on the low-cardinality
statuscolumn is mostly uselessdonevalues; the planner will often skip it and do a seq scan. For this access pattern the partial index is a clear win. -
The same logic applies to any skewed predicate. The lesson isn’t queue-specific: any query targeting a small minority of the table —
WHERE deleted_at IS NULL(soft delete),WHERE processed = false— gets the same win from a partial index. Anytime you pull “a few rows out of a sea of dead ones”, reach for it.
What to do
-
Put the ORDER BY column in the index. Workers typically do
WHERE status='pending' ORDER BY created_at FOR UPDATE SKIP LOCKED. Include the sort key in the index:(created_at) WHERE status='pending'. That gets you an index-ordered scan and letsSKIP LOCKEDstep over locked rows and grab the next job instantly. The SQL looks like this:CREATE INDEX idx_jobs_pending ON jobs (created_at) WHERE status = 'pending'; -- the worker's pull query: SELECT id, payload FROM jobs WHERE status = 'pending' ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1; -
Pass the predicate as a literal in the query. Your query’s
WHEREmust be a condition the index predicate (status='pending') provably implies. If you query with a parameter likestatus = $1, the planner may not pick the index; write the condition as the literal'pending'so the match can be proven. -
Watch out for churn and bloat. When a row flips from
pendingtodoneit drops out of the partial index — that’s good. But heavy update traffic can produce bloat, so a healthy autovacuum matters. The good news: because the index is small, vacuuming it is cheap too.
Bottom line: personally I’d create a partial index on the pull predicate, including the ORDER BY column, and pair it with FOR UPDATE SKIP LOCKED. The one thing to watch: make sure your queries pass 'pending' as a literal so the planner actually picks the index. Verify once with EXPLAIN (ANALYZE, BUFFERS) that you’re getting an index scan; after that you can happily let millions of completed rows pile up.
Compiled from questions I have run into in the field or been asked.
Related measurement
A partial index makes a queue table forty-one times smaller — for as long as the planner picks it
On a Postgres queue table with millions of dead rows, what does a partial index buy, and when does the planner refuse to use it?
Finding
At 10 million dead rows a partial index sustains 11,537 claims per second where the same table without one manages 7. Against a composite index the throughput difference is small (6.9%) but the size difference is not: 7.6 MB against 310.4 MB, and the partial one does not grow with the table because it indexes only the 5,000 live rows. None of that is the real finding. The moment the planner switches a prepared statement to a generic plan the partial index stops being used at all — 11,752 tps becomes 7, and 0.68 ms becomes 1.1 seconds. A factor of 1,635. The composite index is untouched under the same conditions.
measured 88 days ago
Comments
Sign in with your GitHub account to join the discussion. Comments are stored in GitHub Discussions.