---
title: "Sizing a PostgreSQL Queue Table by Its Live Set"
description: "On a queue table, an index and an autovacuum threshold that grow with the table scale wrong; the measured case for sizing both to the pending jobs"
url: https://sade.dev/en/notes/sizing-a-postgres-queue-table-by-its-live-set/
lang: en
author: "Muhammet Şafak"
published: 2026-09-16
updated: 2026-10-07
section: Note
tags: ["postgresql","queue","performance","indexing","production"]
summary: "On a queue table the set doing the work is the pending rows, while the table grows forever with finished ones. Anything that scales with the table scales wrong: at 10 million rows a composite index reached 310 MB while a partial index stayed at 7.6 MB, and the default autovacuum threshold never caught the 1.75 million dead tuples that piled up in fifteen minutes."
---

# Sizing a PostgreSQL Queue Table by Its Live Set

> On a queue table the set doing the work is the pending rows, while the table grows forever with finished ones. Anything that scales with the table scales wrong: at 10 million rows a composite index reached 310 MB while a partial index stayed at 7.6 MB, and the default autovacuum threshold never caught the 1.75 million dead tuples that piled up in fifteen minutes.

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](https://github.com/muhammetsafak/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:

| Strategy | 100k | 1M | 10M | Index size (10M) |
| --- | --- | --- | --- | --- |
| no index | 2,001 | 248 | 8 | — |
| `(status)` | 6,500 | 6,417 | 6,426 | 66.1 MB |
| `(status, created_at)` | 12,415 | 11,203 | 10,796 | 310.4 MB |
| partial `(created_at) WHERE status = 'pending'` | 13,041 | 11,708 | 11,538 | 7.6 MB |

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.

```sql
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](/en/notes/zero-downtime-database-migrations) 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:

| Strategy | At the start | At 900 s | Pending jobs |
| --- | --- | --- | --- |
| partial | 2,024 tps · 1.7 ms | 2,016 tps · 4,906 ms | 5,003 → 14,364 |
| composite | 2,000 tps · 0.52 ms | 1,204 tps · 61,533 ms | 5,000 → 126,024 |

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:

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

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

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

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

```sql
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](/en/notes/archiving-strategy).** 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.

## Frequently asked

**Is a partial index faster on a queue table?**

Not meaningfully. On a table with 10 million finished rows the partial index carried 11,538 claims per second and the (status, created_at) composite index 10,796; the difference is 6.9 percent. The real difference is size: the partial index held at 7.6 MB while the composite one reached 310 MB.

**Why does autovacuum reach a queue table so late?**

The default threshold is 50 + 0.2 × reltuples, and reltuples counts finished rows too. On a 10-million-row table that is roughly 2 million dead tuples; in the measurement 1.75 million dead tuples piled up in fifteen minutes and autovacuum did not run once. As the table grows, so does the threshold.


## Sources

- [Routine Vacuuming: the autovacuum daemon and its thresholds](https://www.postgresql.org/docs/17/routine-vacuuming.html) — PostgreSQL
- [Partial Indexes](https://www.postgresql.org/docs/17/indexes-partial.html) — PostgreSQL
- [PREPARE: generic and custom plans](https://www.postgresql.org/docs/17/sql-prepare.html) — PostgreSQL
- [pg-queue-bench: raw pgbench runs behind every number in this note](https://github.com/muhammetsafak/pg-queue-bench) — GitHub

## In practice

- [A partial index makes a queue table forty-one times smaller — for as long as the planner picks it](https://muhammetsafak.com/research/partial-index-queue-table/)
- [The partial index grew three hundred and five times in fifteen minutes — and autovacuum never ran](https://muhammetsafak.com/research/queue-table-bloat-and-autovacuum/)
- [Postgres never turned the partial index into a generic plan: forty executions, forty custom plans](https://muhammetsafak.com/research/when-does-the-planner-go-generic/)
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://muhammetsafak.com/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/)