# Building a queue table's index around its live set

> A partial index on a Postgres 17 queue table: 41 times smaller, a cliff on the generic plan, bloat under 15 minutes of churn, and an autovacuum that never came.

- Kind: Infographic
- Published: 2026-10-03
- Based on these articles: https://muhammetsafak.com/research/partial-index-queue-table/ · https://muhammetsafak.com/research/queue-table-bloat-and-autovacuum/ · https://muhammetsafak.com/research/when-does-the-planner-go-generic/ · https://muhammetsafak.com/engineering/notes/sizing-a-postgres-queue-table-by-its-live-set/
- Source: https://muhammetsafak.com/visuals/i/queue-table-index-choice/
- Language: en-US
- Author: Muhammet Şafak

---
## Building a queue table's index around its live set

PostgreSQL · Measurement

Single machine, Postgres 17, synthetic bench: a 30-second measurement, the planner, and 15 minutes of churn.

Muhammet Şafak October 3, 2026

## Panel 2

41×

At 10 million dead rows

smaller index

Partial is 7.6 MB, composite is 310.4 MB. The throughput gap is only 6.9%: the reason to pick it is not speed but that the index does not grow with the table.

## A queue table without an index vanishes

10M dead rows, 5,000 live rows

tps

-   no index 7
-   (status) 6,426
-   (status, created\_at) 10,795
-   partial, pending 11,537

partial: (created\_at) WHERE status = 'pending' · 8 clients, 30 seconds, median of three runs · bench/run.sh

## The partial index on a generic plan

Same table, same index, same query

-   11,752 tps, custom plan Index Scan, 0.68 ms
-   7 tps, generic plan Sort → Seq Scan; index never scanned
-   1,113 ms, generic latency Roughly 1,635 times; forced with force\_generic\_plan
-   11,416 Composite, generic No predicate to prove, unaffected

## Forty runs, forty custom plans

Default auto mode, single session

1.   First five runs: all three on custom plan done
2.   Sixth: composite switches to generic done
3.   Partial: 40 of 40 custom done
4.   (status): 40 of 40 custom done

## It did not switch under these conditions

Correction

-   **Correction** In my earlier post I said it 'degrades as it warms up'. When measured, that was wrong: all forty of forty runs stayed on the custom plan.
-   **Why** Most likely explanation: the generic plan, which cannot use the partial index, comes out with a high estimated cost. Plan costs were not recorded.
-   **Cost** Partial is re-planned on every run. At this scale the difference could not be measured: 0.20 ms vs 0.21 ms.

## The live set is constant, the indexes grow

15 min · 2,000 jobs/s · pg\_relation\_size, every 15 s

pg\_relation\_size, sampled every 15 seconds, bench/endurance.sh. Queue depth stays constant at around five thousand rows; what grows is dead index entries.

| Time | partial | composite |
| --- | --- | --- |
| 0 s | 0.125 MB | 301 MB |
| 286 s | 12.4 MB | 344 MB |
| 587 s | 25.3 MB | 388 MB |
| 888 s | 38.2 MB | 427 MB |
| Growth | ~305× | 42%, +126 MB |

## Two indexes that were nearly equal at thirty seconds split apart at fifteen minutes

Target 2,000 jobs/s · second 900

|  | composite | partial |
| --- | --- | --- |
| Start | 2,000 tps · 0.52 ms | 2,024 tps · 1.7 ms |
| Second 900 | 1,204 tps · 61,533 ms | 2,016 tps · 4,906 ms |
| Pending jobs | 5,000 → 126,024 | 5,003 → 14,364 |
| Result | Missed the target; 427 MB does not fit in memory | Met the target |

## Autovacuum never came

The threshold grows with the table

-   1.75 Million dead rows 1,753,949 in 15 minutes
-   0 Autovacuum Did not run even once
-   ≈2 Threshold, million rows 50 + 0.2 × 10,005,000 ≈ 2,001,050
-   17 Minutes, estimated interval Specific to this workload; vacuum cost was not measured

## Build the queue table around its live set

What to do

-   Index the queue: partial (created\_at) WHERE status = 'pending' (done)
-   Do not reach a predicate index through a parameter (done)
-   Decouple the autovacuum threshold from the table; the effect was not measured, 50,000 is a starting point (done)
-   First look at the pending/done ratio, the index size, n\_dead\_tup and the threshold (done)
-   Drop the pattern: if you delete completed rows right away or move them to an archive, if your ORM binds status as a parameter and a generic plan is forced, if you partition the table by time (pending)

## Table size is noise

[github.com/muhammetsafak/pg-queue-bench](https://github.com/muhammetsafak/pg-queue-bench)

Build the index and vacuum around the set that does the work. The numbers come from a single machine, Postgres 17 and a single access pattern; they do not represent months of a real queue.
