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,673. The composite index is untouched under the same conditions.
- Partial index, 10M dead rows
- 11,537 tps
- Same table, no index
- 7 tps
- Index size, partial / composite
- 7.6 / 310.4 MB
- Once the plan goes generic
- 11,752 → 7 tps −1,673×
Method
Postgres 17, one container, explicit settings (shared_buffers 1 GB, work_mem 64 MB, autovacuum on — turning it off would make the numbers prettier and the answer wrong). The `jobs` table holds a constant live set of 5,000 `pending` rows at every tier; the dead set is 100k, 1M and 10M. Each size is seeded once into a **template database** and copied per run: re-seeding per strategy would take longer than the measurements and would hand each strategy a differently shuffled table, so the numbers would carry the seed's noise rather than the strategy's effect. 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. pgbench is the consumer: it ships with Postgres, it reports latency rather than an average alone, and using the standard tool keeps the argument about the index instead of the harness. 8 clients, 30 seconds, three repeats, median. The claimed row returns to `pending` with a fresh `created_at` instead of going to `done`: a consumer that drained its own queue would spend the back half of each run measuring an empty table. Every run also records the `EXPLAIN` plan, the index size, the index scans actually performed and the dead tuples left behind — throughput alone cannot tell a chosen index from an ignored one.
- Measured on
- Published
- Updated
measured yesterday
Environment
- Postgres
- 17-alpine · shared_buffers 1 GB · work_mem 64 MB · autovacuum on
- Table
- jobs · 5,000 live rows held constant · 100k / 1M / 10M dead rows
- Load
- pgbench · 8 clients · 30 s · FOR UPDATE SKIP LOCKED
- Hardware
- Apple M4 Pro · 12 cores · 24 GB · macOS 26.6.1
- Virtualisation
- Docker Desktop 29.7.2 · aarch64
- Repeats
- 3 · median reported
- Determinism
- every run starts from a copy of the same template database
Technologies
To reproduce
./bench/run.shEarly in August a question came in:
on a queue table where a few thousand pending rows sit among millions of
completed ones, partial index or plain index? I answered “yes, this is the textbook case for a partial index” and
listed six points. I had measured none of them.
This record tests that answer.
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 fastest and smallest | 13,041 tps | 11,707 tps | 11,537 tps | 7.6 MB |
The first row confirms why the answer was given at all: without an index a queue table does not degrade as dead rows pile up, it disappears. From 2,001 tps to 7. Finding 5,000 rows among ten million by sequential scan can be done seven times a second.
The plain (status) index behaves as claimed too: indexing a column with a
cardinality of two helps, but its ceiling is low — around 6,400 at all three
tiers.
The real difference is size, not speed
Between partial and composite the throughput difference is 6.9%. Small. The size difference widens with the table:
Index size as the table grows
The composite index covers every row; the partial one covers only the pending rows. The live set is constant, so the partial index is too.
- (status, created_at)
- partial
MB lower is better Source: pg_relation_size, bench/run.sh
Data table
| Series | 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 chart is drawn in the browser; the table below carries the same data.
The composite index grows with the table: 11.2 → 40.3 → 310.4 MB. The partial one stops at 7.6 MB, because what it indexes is not the table but the queue — and the queue is constant. At ten million rows that is a forty-one-fold difference.
This is the numeric version of the answer’s “it will be small, it fits in cache, it is cheap to update”. 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.
And then the planner changes its mind
The answer’s second point was: “if you ask with a parameter like status = $1
the planner may not be able to pick the index; write the condition as a literal
'pending'.” I did not know how serious that warning was when I wrote it.
| 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 the index was never scanned | 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 |
One thousand six hundred and seventy-three times. Under a generic plan the partial index is not scanned — its scan counter stays at zero — and the query lands on the number for a table with no index at all: 7 tps. Latency goes from 0.68 ms to 1.1 seconds.
The composite index is untouched under the same conditions.
The mechanism is visible in the plan tree. A generic plan is built without
knowing what $1 is. For the partial index to be usable the planner has to
prove that $1 = 'pending', because the index contains only those rows. It
cannot, 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 the index,
and it can be scanned whatever $1 turns out to be.
How this blows up in production
Under auto, Postgres uses a custom plan for the first five executions, then
compares the generic plan’s cost and may switch to it. So the profile degrades
as it warms up: the application starts, the first requests are fast, and after
the workers have been running a few minutes the same query is a thousand times
slower.
It does not show up in staging, because in staging the test finishes before that statement has run five times.
There was a very similar question
in Sor Bakalım: why does a query that gets an Index Scan in staging fall back to
a Seq Scan in production? I put the diagnosis down to stale statistics and table
bloat. This measurement points at a third cause, and that one has nothing to do
with ANALYZE.
The answer’s report card
| Claim from the Sor Bakalım answer | Result |
|---|---|
| A partial index is small and fits in cache | true — 7.6 MB, and it does not grow with the table |
| Clearly better than a plain (status) index | true — 11,537 against 6,426 tps |
| Put the ORDER BY column in the index | true — the Index Scan gets the ordering for free |
| With a parameter the planner may not pick it | true but narrow: the problem is the predicate |
| Watch out for churn and bloat | not measured — 30 s is too short for autovacuum |
The value of measuring a piece of advice is not confirming it — four of these were right already. The value is finding the condition under which a true sentence turns around.