Skip to content
Muhammet Şafak
tr

October 3, 2026

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.

Tan holding a blueprint

PostgreSQL · Measurement

Building a queue table's index around its live set

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

Muhammet Şafak

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.

10M dead rows, 5,000 live rows

A queue table without an index vanishes

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

Same table, same index, same query

The partial index on a generic plan

  • 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

Default auto mode, single session

Forty runs, forty custom plans

  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

Correction

It did not switch under these conditions

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

15 min · 2,000 jobs/s · pg_relation_size, every 15 s

The live set is constant, the indexes grow

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

Target 2,000 jobs/s · second 900

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

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

The threshold grows with the table

Autovacuum never came

  • 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
Tan giving a thumbs-up

What to do

Build the queue table around its live set

  • 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)
Tan smiling

Table size is noise

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.

Same topic

Share and download

Share:
Paste this code into your page’s HTML.

Search the site

Start typing to search posts, projects and pages.

Esc to close Powered by Pagefind