# Queue table: the index that wins at 30 seconds and loses at 15 minutes

> Four index strategies for a queue table on Postgres 17: a 30-second benchmark, the generic plan cliff, and index size and autovacuum under 15 minutes of churn.

- Kind: Presentation
- Published: 2026-10-03
- Slides: 22
- 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/d/queue-table-index-choice/
- Language: en-US
- Author: Muhammet Şafak

---
## Slide 1 — Queue table: the index that wins at 30 s, loses at 15 min

PostgreSQL · Measurement

Same table, Postgres 17, three measurements.

Muhammet Şafak October 3, 2026

## Slide 2

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.

## Slide 3 — Two sets in one table

Postgres does not know this distinction

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

## Slide 4 — Thirty seconds

Section 01

Four strategies, three dead-row tiers. The live set is 5,000 rows at every tier.

## Slide 5 — Three tiers, four strategies

SKIP LOCKED · 8 clients · 30 s · median of three runs

Claim via FOR UPDATE SKIP LOCKED; 8 clients, 30 seconds, median of three runs. The live set is 5,000 rows at every tier.

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

## Slide 6

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.

## Slide 7

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.

## Slide 8 — The partial index does not grow with the table

As dead rows grow

index size, MB

-   composite · 100k 11.2
-   composite · 1M 40.3
-   composite · 10M 310.4
-   partial · 100k 6.5
-   partial · 1M 7.6
-   partial · 10M 7.6

pg\_relation\_size, bench/run.sh

## Slide 9 — Planner

Section 02

Same table, same index, same query. The only thing that changes is the plan type.

## Slide 10 — The generic plan cliff

10M dead rows · same query, parameterized

Same table, same index, same query. The partial index was never scanned in the generic plan: Limit, LockRows, Sort, Seq Scan.

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

## Slide 11 — Roughly 1,635 times

Why

-   **Note** A generic plan is built without knowing what $1 is. It cannot prove that status = $1 means 'pending', so it rules out the partial index.
-   **Tip** Composite is unaffected: it has no predicate to prove, and status is just a column inside the index.
-   **Warning** Falling off the cliff took force\_generic\_plan. That is not a production setting.

## Slide 12 — Forty runs, forty custom plans

Single session, default auto mode

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

## Slide 13 — 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.

## Slide 14 — Now, 15 minutes

Section 03

Sustained churn: 2,000 jobs per second, default autovacuum settings.

## Slide 15 — The live set is constant, the index grows

Partial · ~305×

index size, MB

The live set is constant, the index grows (index size, MB): 0 s: 0.125, 135 s: 5.9, 286 s: 12.4, 436 s: 18.8, 587 s: 25.3, 738 s: 31.7, 888 s: 38.2.

pg\_relation\_size, sampled every 15 seconds, bench/endurance.sh. The pending→done transition leaves dead entries until vacuum.

## Slide 16 — Composite bloated by a smaller ratio, but more MB

Composite · 42%, +126 MB

index size, MB

Composite bloated by a smaller ratio, but more MB (index size, MB): 0 s: 301, 135 s: 321, 286 s: 344, 436 s: 366, 587 s: 388, 738 s: 409, 888 s: 427.

pg\_relation\_size, sampled every 15 seconds, bench/endurance.sh

## Slide 17 — A bloated index cannot carry the sustained load

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 | Met the target |

## Slide 18 — Two notes

While reading

-   **Note** Latency includes pgbench's schedule lag: the debt of a client that cannot keep up with the target rate is booked here.
-   **Note** The gap opens under sustained load: an index bloated to 427 MB no longer fits in memory, and every scan goes to disk.

## Slide 19 — 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

## Slide 20 — Decouple the threshold from the table

Recommendation

I did not measure this setting. 50,000 is a starting point; tune it to your own throughput.

jobs.sql

```
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';
```

## Slide 21 — The claims in the Sor Bakalım answer, once measured

Result

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

## Slide 22 — Find the condition

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

The value of measuring a piece of advice is not confirming it. It is finding the condition under which a correct sentence flips.
