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 | Strategy | 100k dead rows | 1M | 10M | Index size (10M) | |---|---|---|---|---| | no index | 2,001 tps | 247 tps | 7 tps | - | (status) | 6,499 tps | 6,417 tps | 6,426 tps | 66.1 MB | (status, created_at) | 12,414 tps | 11,202 tps | 10,795 tps | 310.4 MB | partial (created_at) WHERE pending | 13,041 tps | 11,707 tps | 11,537 tps | 7.6 MB | 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 | Index size | 100k dead rows | 1M | 10M | |---|---|---|---| (status, created_at) | 11.2 MB | 40.3 MB | 310.4 MB | | partial | 6.5 MB | 7.6 MB | 7.6 MB | 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: | 10M dead rows, with a parameter | tps | Mean latency | Plan | |---|---|---|---| | partial · custom plan | 11,752 | 0.68 ms | Limit → LockRows → Index Scan | | partial · generic plan | 7 | 1,113 ms | Limit → LockRows → Sort → Seq Scan | | composite · custom plan | 11,105 | 0.72 ms | Index Scan | | composite · generic plan | 11,416 | 0.70 ms | Index Scan | 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: | Strategy | First generic plan | Final counter (custom/generic) | First 5 (ms) | Last 5 (ms) | |---|---|---|---|---| | partial | never | 40 / 0 | 0.36 | 0.20 | | composite | execution 6 | 5 / 35 | 0.40 | 0.21 | (status) | never | 40 / 0 | 0.98 | 0.78 | 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: | Strategy | At the start | At 900 s | Queue depth | |---|---|---|---| | partial | 2,024 tps · 1.7 ms | 2,016 tps · 4,906 ms | 5,003 → 14,364 | | composite | 2,000 tps · 0.52 ms | 1,204 tps · 61,533 ms | 5,000 → 126,024 | 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. W