Skip to content
Muhammet Şafak
tr
Answered:

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

  1. 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.

  2. It’s strictly better than a plain index on status. A plain index on the low-cardinality status column is mostly useless done values; the planner will often skip it and do a seq scan. For this access pattern the partial index is a clear win.

  3. 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

  1. 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 lets SKIP LOCKED step 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;
  2. Pass the predicate as a literal in the query. Your query’s WHERE must be a condition the index predicate (status='pending') provably implies. If you query with a parameter like status = $1, the planner may not pick the index; write the condition as the literal 'pending' so the match can be proven.

  3. Watch out for churn and bloat. When a row flips from pending to done it 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

High confidence

Comments

Sign in with your GitHub account to join the discussion. Comments are stored in GitHub Discussions.

More Questions

All questions

Search the site

Start typing to search posts, projects and pages.

Esc to close Powered by Pagefind