Queue table: the index that wins at 30 seconds and loses at 15 minutes
Keyboard: ← → to move, F for full screen, O for overview.
PostgreSQL · Measurement
Queue table: the index that wins at 30 s, loses at 15 min
Same table, Postgres 17, three measurements.
Muhammet Şafak
Single machine, Postgres 17, synthetic bench
Nearly equal at 30 seconds. Apart at 15 minutes.
Partial and composite index: 11,537 vs 10,795 tps at thirty seconds. Under fifteen minutes of sustained load, composite missed the target.
Postgres does not know this distinction
Two sets in one table
| Archive · done | Live set · pending | |
|---|---|---|
| Who looks | Nobody queries it | Workers search it, claims lock it |
| Size | Grows forever unless deleted | Stays roughly constant |
| Measured | n_live_tup 10,005,726 | 5,000 rows |
| Postgres | Live tuples: the index, statistics and vacuum threshold all count them | Same treatment; the two are not told apart |
Section 01
Thirty seconds
Four strategies, three dead-row tiers. The live set is 5,000 rows at every tier.
SKIP LOCKED · 8 clients · 30 s · median of three runs
Three tiers, four strategies
| Strategy | 100k dead rows | 1M | 10M | Index (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 |
No index
A queue without an index vanishes.
It does not just degrade with dead rows: 2,001 down to 7 tps. A sequential scan can find the 5,000 rows among 10 million only seven times a second.
41×
The real difference is size
smaller index
At 10 million dead rows, partial is 7.6 MB and 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.
As dead rows grow
The partial index does not grow with the table
index size, MB
- composite · 100k11.2
- composite · 1M40.3
- composite · 10M310.4
- partial · 100k6.5
- partial · 1M7.6
- partial · 10M7.6
Section 02
Planner
Same table, same index, same query. The only thing that changes is the plan type.
10M dead rows · same query, parameterized
The generic plan cliff
| Index · plan | tps | Latency | Plan |
|---|---|---|---|
| partial · custom | 11,752 | 0.68 ms | Index Scan |
| partial · generic | 7 | 1,113 ms | Sort → Seq Scan |
| composite · custom | 11,105 | 0.72 ms | Index Scan |
| composite · generic | 11,416 | 0.70 ms | Index Scan |
Why
Roughly 1,635 times
- NoteA generic plan is built without knowing what $1 is. It cannot prove that status = $1 means 'pending', so it rules out the partial index.
- TipComposite is unaffected: it has no predicate to prove, and status is just a column inside the index.
- WarningFalling off the cliff took force_generic_plan. That is not a production setting.
Single session, default auto mode
Forty runs, forty custom plans
- 00First five runs: all three on custom plandone
- 01Sixth: composite switches to genericdone
- 02Partial: 40 of 40 customdone
- 03(status): 40 of 40 customdone
Correction
It did not switch under these conditions
- CorrectionIn 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.
- WhyMost likely explanation: the generic plan, which cannot use the partial index, comes out with a high estimated cost. Plan costs were not recorded.
- CostPartial is re-planned on every run. At this scale the difference could not be measured: 0.20 ms vs 0.21 ms.
Section 03
Now, 15 minutes
Sustained churn: 2,000 jobs per second, default autovacuum settings.
Partial · ~305×
The live set is constant, the index grows
index size, MB
pg_relation_size, sampled every 15 seconds, bench/endurance.sh. The pending→done transition leaves dead entries until vacuum.
Composite · 42%, +126 MB
Composite bloated by a smaller ratio, but more MB
index size, MB
pg_relation_size, sampled every 15 seconds, bench/endurance.sh
Target 2,000 jobs/s · second 900
A bloated index cannot carry the sustained load
| 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 | Met the target |
While reading
Two notes
- NoteLatency includes pgbench's schedule lag: the debt of a client that cannot keep up with the target rate is booked here.
- NoteThe gap opens under sustained load: an index bloated to 427 MB no longer fits in memory, and every scan goes to disk.
The threshold grows with the table
Autovacuum never came
- 1.75Million dead rows1,753,949 in 15 minutes
- 0AutovacuumDid not run even once
- ≈2Threshold, million rows50 + 0.2 × 10,005,000 ≈ 2,001,050
- 17Minutes, estimated intervalSpecific to this workload; vacuum cost was not measured
Recommendation
Decouple the threshold from the table
I did not measure this setting. 50,000 is a starting point; tune it to your own throughput.
1ALTER TABLE jobs SET (2 autovacuum_vacuum_scale_factor = 0,3 autovacuum_vacuum_threshold = 500004);56CREATE INDEX CONCURRENTLY7 jobs_pending_created_at8 ON jobs (created_at)9WHERE status = 'pending';
Result
The claims in the Sor Bakalım answer, once measured
- A partial index stays small: 7.6 MB at 10M (done)
- Beats the (status) index: 11,537 vs 6,426 tps (done)
- The ORDER BY column should go into the index (done)
- With a parameter, the planner may not pick the partial: true, but narrow (done)
- Churn and bloat: not measured in the first post, measured afterwards (done)
Find the condition
The value of measuring a piece of advice is not confirming it. It is finding the condition under which a correct sentence flips.