Sizing a PostgreSQL Queue Table by Its Live Set

On a ten-million-row jobs table, 5,000 jobs were waiting in the queue, and the index the claim query used took up 310 MB. A 7.6 MB partial index did the same job, and the speed difference between the two was 6.9 percent.

So the question was never speed. It was what grows along with the table.

A queue table is two tables

Two separate sets live in a queue table:

• The live set — the rows in pending. The part that workers look for, that claims lock, and that actually does the work. Its size depends on the balance between throughput and the arrival rate; in a healthy system it stays roughly constant.
• The archive — the rows in done. Nobody queries them, but as long as they are not deleted the table grows with them forever.

PostgreSQL does not know about this split. From MVCC’s point of view a finished row is a live tuple: pg_class.reltuples counts it, the planner statistics count it, the autovacuum threshold counts it. In the table I measured there were 5,000 pending rows and an n_live_tup of 10,005,726.

That is why two wrong scalings happen on a queue table by themselves: the index grows with the table, and so does the vacuum threshold. Both look at the archive, not at the set doing the work.

The numbers below come from pg-queue-bench: PostgreSQL 17, one container, shared_buffers 1 GB, 8 clients, claims with FOR UPDATE SKIP LOCKED. Every pgbench transcript is in the repository.

Index the queue, not the table

The live set was held at 5,000 rows at every size; the only thing that changed was how many finished rows surrounded it. Claims per second, median of three repeats:

The no-index row shows why the question exists at all: finding 5,000 rows among 10 million with a sequential scan can be done only 7.6 times a second.

The throughput difference between composite and partial is 6.9 percent. That is not a reason to pick a partial index. The reason is size:

• The composite index grew with the table: 11.2 → 40.3 → 310.4 MB.
• The partial index stopped at 6.5 → 7.6 → 7.6 MB. What it indexes is not the table but the queue, and the queue is constant, so the index is too.

At ten million rows the difference is 41 times. Keeping a 310 MB index in shared_buffers and keeping a 7.6 MB one are not the same decision.

CREATE INDEX CONCURRENTLY jobs_pending_created_at
    ON jobs (created_at)
 WHERE status = 'pending';

ORDER BY created_at gets its ordering from the index for free; the claim query must write the predicate exactly as the index does. Building the index with CONCURRENTLY on a live table is how you avoid blocking writes; the rest of zero-downtime schema change is covered in its own piece.

If you ask with a parameter

PostgreSQL uses a partial index only if it can prove, at planning time, that the query’s WHERE clause implies the index predicate. A generic plan is built without knowing what $1 is; it cannot prove that status = $1 means status = 'pending', so it has to drop the partial index.

Claims that ran at 11,752 per second under force_custom_plan fell to 7.2 when I forced force_generic_plan, and mean latency went from 0.68 ms to 1,113 ms — roughly 1,635 times. The index’s scan counter stayed at zero. Under the same condition the composite index was untouched (11,417 per second), because it has no predicate to prove; status is a column inside the index.

The cliff is real but fenced. In the default auto mode PostgreSQL runs the first five executions on custom plans, then compares the generic plan’s estimated cost against the average of the custom ones. In a single session, 40 out of 40 executions used a custom plan; the planner never switched to a generic one. I did not record plan costs; the likeliest explanation is that a generic plan, unable to use the partial index, comes out with a high estimated cost. It may not be the only reason, though: the (status) index, which has no predicate, also stayed on custom plans for all 40 executions. The composite index switched to a generic plan at the sixth execution and lost nothing by it.

That protection has a price: a query that reaches the partial index is re-planned on every execution. At this scale the difference is too small to measure (averaged over the last five executions: partial 0.20 ms, composite 0.21 ms). The same cannot be said for a many-table join or a long IN list.

The rule: do not reach a predicated index through a parameter. Write status = 'pending' as a literal, and leave plan_cache_mode alone.

A small index does not mean less vacuuming

In thirty-second runs the two indexes were nearly equal. Under fifteen minutes of sustained load the picture changed. In this run the claimed row was set to done, and a producer ran alongside at 2,000 jobs per second.

The partial index went from 0.125 MB to 38.2 MB — roughly 305 times. The live set stayed between 3,000 and 5,000 rows for most of the run; what grew was the dead entries in the index.

The mechanism fits in one sentence: the pending → done transition takes the row out of the partial index’s scope, but its entry stays in the index until vacuum arrives. The partial index’s smallness comes from the live set, its bloat rate comes from throughput, and nothing connects the two. A small index makes vacuum cheaper, not rarer.

The autovacuum threshold looks at the archive

In fifteen minutes 1,753,949 dead tuples piled up. The autovacuum counter: 0.

The default threshold is computed like this:

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples
                 = 50 + 0.2 × ~10,000,000
                 ≈ 2,000,000 dead tuples

Because reltuples counts finished rows too, the threshold grows with the archive. The set doing the work stays constant and dead tuples arrive at the rate of throughput, but the number vacuum waits for drifts a little further away with every new done row. The bigger the table, the later vacuum comes.

The composite index paid for it. In the same fifteen minutes it went from 301 MB to 427 MB and fell behind the target:

Latency includes pgbench’s scheduling lag; a client that cannot keep to the target rate books its debt there. The partial index slipped too in the last 45 seconds (1,740 tps at 855 s, pending jobs from 4,549 to 14,364), but it finished the run at the target rate. The composite index never recovered after 660 s.

Cut the threshold loose from the table

The global default was not designed for this access pattern. Cut the threshold loose from the table’s size and pin it to a fixed number:

ALTER TABLE jobs SET (
    autovacuum_vacuum_scale_factor = 0,
    autovacuum_vacuum_threshold    = 50000
);

scale_factor = 0 makes the threshold independent of reltuples; only the fixed base remains. The right number is set by throughput, not table size: a queue processing 2,000 claims per second produces 50,000 dead tuples in about 25 seconds.

I did not measure this setting — the run above used the defaults, and because autovacuum never ran, the cost of vacuuming was not measured either. 50,000 is a starting point; tune it to your own throughput and to how long vacuum takes on your own table.

Measure your own table

First, look at the ratio between the two sets:

SELECT status, count(*)
  FROM jobs
 GROUP BY status;

Then at the size of the indexes, and whether they are used at all:

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
  FROM pg_stat_user_indexes
 WHERE relname = 'jobs';

Finally, put the dead tuple count next to the global threshold:

SELECT s.n_dead_tup,
       s.autovacuum_count,
       s.last_autovacuum,
       current_setting('autovacuum_vacuum_threshold')::int
         + current_setting('autovacuum_vacuum_scale_factor')::float8 * c.reltuples
         AS global_threshold,
       c.reloptions
  FROM pg_stat_user_tables s
  JOIN pg_class c ON c.oid = s.relid
 WHERE s.relname = 'jobs';

A reltuples of -1 means the table has not been analyzed yet and the calculation is meaningless. An empty reloptions means the table uses the global threshold. If n_dead_tup keeps growing below global_threshold for hours while autovacuum_count stands still, your vacuum is looking at the archive.

When to drop this pattern

• You delete finished rows right away, or move them to an archive table. The table stays close to its live set, so both the composite index’s growth and the default threshold’s drift stay small.
• Your ORM or driver binds status as a parameter and a generic plan is forced. You cannot use the partial index; a composite index is safer.
• You partition the table by time. Dropping old partitions separates both the index and the vacuum load from the table’s size; it solves the problem this note solves by another route.

The numbers come from one machine, PostgreSQL 17 and one access pattern. Thirty-second and fifteen-minute runs do not stand in for months of a real queue; the direction holds, the magnitudes belong to your workload.

---

On a queue table, table size is noise. Size the index and the vacuum to the set doing the work.