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

Keyboard: ← → to move, F for full screen, O for overview.

Tan with a laptop

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

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

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

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

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

Why

Roughly 1,635 times

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

Single session, default auto mode

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.

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

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.

Composite · 42%, +126 MB

Composite bloated by a smaller ratio, but more 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

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

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

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

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.

jobs.sql
ALTER TABLE jobs SET (  autovacuum_vacuum_scale_factor = 0,  autovacuum_vacuum_threshold    = 50000);CREATE INDEX CONCURRENTLY  jobs_pending_created_at  ON jobs (created_at)WHERE status = 'pending';
Tan giving a thumbs-up

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

Find the condition

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.

Share, embed, download

Same topic