Don't pick a partial index for speed: what I measured on a 10M-row Postgres queue table

Early in August someone asked me a question: on a queue table where a few thousand pending rows sit among millions of completed ones, should they use a partial index or a plain one?

I said yes, this is the textbook case for a partial index, and listed six points. It will be small. It fits in cache. It is cheap to update. Put the ORDER BY column in it. If you ask with a bound parameter the planner may not pick it. Watch out for churn and bloat.

I had measured none of them.

So I measured all of them. Four of the six turned out right. What I want to write about is the two that didn't — because the interesting part of testing your own advice is never the confirmation. It's finding the condition under which a true sentence flips.

TL;DR

• Against a composite index, the partial index is 6.9% faster. That's noise. Speed is the wrong reason to choose it.
• It is 41x smaller at 10M rows (7.6 MB vs 310.4 MB) — and it stops growing, because it indexes the queue, not the table.
• Bind status as a parameter and force a generic plan, and the partial index is not scanned at all: 11,752 tps becomes 7, 0.68 ms becomes 1.1 seconds. 1,673x. The composite index is untouched.
• Postgres will not walk you into that on its own — it declines the generic plan, 40 executions out of 40. You have to set plan_cache_mode = force_generic_plan by hand.
• Under sustained churn the partial index bloated 380x in fifteen minutes and autovacuum never ran once. Small index ≠ less vacuuming. It means cheaper vacuuming, needed more often.

The bench

Postgres 17 in one container. shared_buffers 1 GB, work_mem 64 MB, autovacuum on — turning it off would have made the numbers prettier and the answer wrong.

The table is deliberately ordinary. Anything clever here — a partition, an archive table — would be answering the question instead of asking it:

CREATE TABLE jobs (
    id          bigserial PRIMARY KEY,
    status      text        NOT NULL,
    queue       text        NOT NULL DEFAULT 'default',
    payload     jsonb       NOT NULL,
    created_at  timestamptz NOT NULL,
    updated_at  timestamptz
);

The live set is held at 5,000 pending rows at every tier. The only thing that changes is how many dead rows surround it: 100k, 1M, 10M. pgbench is the consumer — 8 clients, 30 seconds, three repeats, median. The claim is what a worker would actually write:

BEGIN;

SELECT id AS claimed_id
  FROM jobs
 WHERE status = 'pending'
 ORDER BY created_at
   FOR UPDATE SKIP LOCKED
 LIMIT 1;

UPDATE jobs
   SET updated_at = now(),
       created_at = now()
 WHERE id = :claimed_id;

END;

The claimed row goes back to pending with a fresh created_at instead of to done. A consumer that drained its own queue would spend the back half of every run measuring an empty table. This keeps the write pattern and the dead tuple per claim; what it does not reproduce is producer-side insert traffic, and the record says so rather than implying otherwise.

Four strategies, applied one at a time — no index, (status), (status, created_at), and:

CREATE INDEX idx_jobs_pending
    ON jobs (created_at)
 WHERE status = 'pending';

Each table size is seeded once into a template database and copied per run, so every strategy meets the same physical layout. And the seed shuffles on the way in: an ordered insert leaves a table whose physical order matches created_at, which makes every index scan read almost sequentially and flatters all four strategies equally.

Three tiers, four strategies

The first row is why the question gets asked at all. Without an index a queue table doesn't degrade as dead rows pile up — it disappears. 2,001 tps down to 7. Finding 5,000 rows among ten million by sequential scan is something you can do seven times a second.

The plain (status) index behaves as advertised: indexing a column with a cardinality of two helps, but its ceiling is low — about 6,400 at all three tiers.

And then the anticlimax: between partial and composite, the throughput difference is 6.9%. If you are comparing those two on speed, there is nothing there to measure.

The real difference is size

The composite index grows with the table. The partial one stops at 7.6 MB, because what it indexes is not the table but the queue — and the queue is constant.

Holding a 310 MB index in shared buffers is not the same proposition as holding a 7.6 MB one, and neither is the size of the tree every insert and update has to maintain. A queue table grows forever by definition; whether the thing indexing it does too is an architectural decision, not a performance tweak.

Then the planner changes its mind

My second point had been: "if you ask with a parameter like status = $1, the planner may not be able to pick the index." I did not know how serious that warning was when I wrote it.

Same table, same index, same query, 10M dead rows, status bound as a parameter. The only variable is whether Postgres uses a plan built for this call or a general one:

1,673x. Under a generic plan the partial index is never scanned — its scan counter stays at zero — and the query lands on the number for a table with no index at all. The composite index doesn't flinch.

The mechanism is right there in the plan tree. A generic plan is built without knowing what $1 is. To use the partial index the planner has to prove $1 = 'pending', because the index contains only those rows. It can't, so it discards the index and falls back to a sequential scan. The composite index has no predicate to prove: status is a column inside it, and it can be scanned whatever $1 turns out to be.

So the warning was true and badly scoped. The problem isn't asking with a parameter. It's asking a predicated index with a parameter.

The sentence I got wrong

When I published that record I added a paragraph: under plan_cache_mode = auto Postgres uses a custom plan for the first five executions and may then switch, so this profile degrades as it warms up — the app starts fast and a thousand times slower a few minutes later. It wouldn't show up in staging, because staging finishes before that statement runs five times.

Reasonable inference. Wrong.

pg_prepared_statements keeps the decision as a counter, so nothing has to be guessed from timings:

The composite index switches exactly where the docs say it will:

#5  0.266 ms   custom=5  generic=0
#6  0.275 ms   custom=5  generic=1   ← switched
#7  0.185 ms   custom=5  generic=2

On the partial index it never happens. Forty executions, forty custom plans — and it declines for exactly the reason the cliff exists. A generic plan can't use the index, so its estimated cost comes out high, and the comparison goes to the custom plan every single time. The cost model's whole job here is keeping you off the cliff, and it doesn't falter once.

The cliff is real but fenced. Reaching it means writing plan_cache_mode = force_generic_plan — turning the protection off by hand.

There is a price for the protection: the partial-index query is re-planned on every execution. At this scale that's unmeasurable (0.20 ms vs 0.21 ms). Planning is cheap on a cheap query — not on a many-table join or a long IN list.

Small index, more vacuuming

The one point of the original six that thirty-second runs can't touch is bloat. That needed a fifteen-minute endurance run, and it does not leave "the index is small, so vacuuming it is cheap" standing as written.

Queue depth held near five thousand rows throughout. The partial index went from 0.1 MB to 38.2 MB — 380x. The composite grew 42% (301 → 427 MB): less in proportion, more in absolute terms (+126 MB).

The cause fits in a sentence: pending → done drops the row out of the partial index, but the dead entry stays there until vacuum arrives. The index's smallness comes from the live set; its bloat rate comes from throughput; nothing connects the two.

And vacuum didn't arrive. Fifteen minutes produced 1,753,949 dead rows. Autovacuum count: 0.

threshold = autovacuum_vacuum_threshold + scale_factor × live rows
          = 50 + 0.2 × 10,005,000
          ≈ 2,001,050 dead rows

We stopped just under it. At defaults this table triggers autovacuum roughly every seventeen minutes and the indexes bloat freely in between. The real problem isn't the ratio, it's the direction of the scaling: the threshold grows with the whole table, while the churn happens in a small live set and does not speed up as the table grows. The bigger the table, the later vacuum comes.

Then the bloated index stops carrying load. Both runs targeted 2,000 jobs/s:

The composite index missed the target: 1,204 tps, 61-second latency, a 126,000-job backlog. The partial one held. That is the opposite of the thirty-second result, where the two were nearly equal — the gap opens under sustained load, because a 427 MB index no longer fits in memory and every scan goes to disk.

What I'd actually do

1. Choose a partial index for size, not speed. 6.9% against a composite index; 41x on size, and the gap widens with the table.
2. Don't reach a predicated index through a bound parameter. Write the predicate as a literal, or accept that its usability depends on a proof the planner may not be able to make.
3. Leave plan_cache_mode alone. Postgres protects you here by default; force_generic_plan is the only route to the 1,673x cliff.
4. Tune autovacuum per table. Drop autovacuum_vacuum_scale_factor to something like 0.01 for this table, or pin autovacuum_vacuum_threshold to a fixed number. The global default was not designed for this access pattern.
5. Don't measure sustained load in thirty seconds. Two strategies that tie at 30 s are 60 seconds of latency apart at minute fifteen.

Caveats, stated plainly: one machine, one Postgres version (17), one access pattern, no producer-side insert traffic. The planner's decision rests on an estimate, and stale statistics make a wrong estimate — so the honest claim is "it did not switch under these conditions", not "it never switches". Repeating this with stale statistics is the one route by which the sentence I corrected above could turn out to have been right after all. That's a separate measurement.

Where these numbers live

These aren't blog posts. They're entries in a research notebook I keep on my own site, where I measure the advice I give and publish the corrections when a record turns out to be wrong. Every entry opens with a question, states its method and hardware, ships the dataset and a one-line reproduce command.

• The full record, with the plan trees, the charts and the limits: A partial index makes a queue table forty-one times smaller — for as long as the planner picks it
• The correction: Postgres never turned the partial index into a generic plan: forty executions, forty custom plans
• The endurance run: The partial index grew three hundred and eighty times in fifteen minutes — and autovacuum never ran
• The question that started it: Should I use a partial index on a queue table?
• The whole notebook: muhammetsafak.com.tr/en/research
• Bench, dataset and ./bench/run.sh: github.com/muhammetsafak/pg-queue-bench

If you run this on your own queue table and get a different number, I want to hear it. That's what the notebook is for.