# A partial index makes a queue table forty-one times smaller — for as long as the planner picks it

> I answered a question in Sor Bakalım without measuring anything: a partial index is the textbook case for this table. Three table sizes, four index strategies and two query forms later, three of the claims hold and one needs narrowing.

- Kind: Measurement
- Question: 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.
- 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.
- Metrics: 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×)
- Measured on: 2026-08-22
- Confidence: High confidence
- Status: Current
- Programme: Database & queries
- 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: PostgreSQL, SQL, pgbench, Docker
- To reproduce: ./bench/run.sh
- Source code: https://github.com/muhammetsafak/pg-queue-bench
- Raw data: https://github.com/muhammetsafak/pg-queue-bench/blob/main/results/2026-08-22/queue.json
- Raw data licence: https://github.com/muhammetsafak/pg-queue-bench/blob/main/LICENSE
- Published: 2026-08-22
- Updated: 2026-08-23
- Source: https://www.muhammetsafak.com.tr/en/research/partial-index-queue-table/
- Language: en-US
- Author: Muhammet Şafak

---
Early in August [a question came in](/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/):
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 | 13,041 tps | 11,707 tps | 11,537 tps | 7.6 MB |

8 clients, 30 seconds, median of three repeats. The live set is 5,000 rows at every tier; the only thing that changes is how many dead rows surround it.

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.

Source: pg_relation_size, bench/run.sh

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

> **Result**
>
> Speed is the wrong reason to pick a partial index — the difference there is 7%.
> The reason is that the index does not grow with the table. A queue table grows
> forever by definition; whether the thing indexing it does too is an
> architectural decision.

## 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 | 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 |

Same table, same index, same query. The only thing that changes is whether Postgres uses a plan built for this call or a general one.

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

> **Caveat**
>
> This **narrows** the warning: the problem is not asking with a parameter, it is
> asking a **predicated index** with a parameter. The answer's second point needs
> correcting, and the correction is not "write a literal" but "do not reach a
> predicated index through a parameter" — or "stay on custom plans".

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

> **Added later — 23 August 2026**
>
> The paragraph above went too far, and [measuring it showed so](/en/research/when-does-the-planner-go-generic/).
> Postgres does **not** make this switch on its own: on the partial index all forty
> executions stayed on a custom plan (`pg_prepared_statements` reads 40/0). It
> declines for exactly the reason this record is about — a generic plan cannot use
> the index, so its estimated cost comes out high and the planner does not pick it.
> The 1,673-fold cliff below is real but fenced: reaching it takes setting
> `plan_cache_mode` to `force_generic_plan` by hand. "Degrades as it warms up" was
> an inference rather than a measurement, and this section's rule is that the
> correction gets published too.

There was [a very similar question](/en/just-ask/why-does-a-query-use-an-index-scan-in-staging-but/)
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`.

> **Caveat**
>
> The measurement comes from one machine, one Postgres version (17) and one access
> pattern. Because the claimed row returns to `pending` rather than going to
> `done`, there is no insert traffic — the write pattern and the dead tuple per
> claim are the same, but a real queue's producer side is not represented here.
> `force_generic_plan` is not a production setting; in real life the choice belongs
> to Postgres and when it switches depends on the workload. The two extremes here
> bound the real behaviour between them. Autovacuum was left on, but 30-second runs
> are far too short to measure its long-run effect; that is a separate record.

## 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 |

Four of the six points were tested. The fifth deserves a record of its own.

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.