# The partial index grew three hundred and eighty times in fifteen minutes — and autovacuum never ran

> The partial index measurement worked in thirty-second windows — far too short to say anything about bloat or vacuum. This one runs the same table for fifteen minutes and samples it every fifteen seconds.

- Kind: Measurement
- Question: Under sustained churn, does a partial index stay small on a queue table, and do the default autovacuum settings keep up with it?
- Finding: With the live set holding steady at five thousand rows the partial index went from 0.1 MB to 38.2 MB — three hundred and eighty times. Its smallness comes from the live set, its bloat rate comes from throughput, and nothing connects the two. The composite index bloated less in proportion (42%) and more in absolute terms (+126 MB), and while bloating it stopped fitting in memory: its latency went from 0.52 ms to 61 seconds and its backlog climbed to 126,000. Fifteen minutes produced 1.75 million dead rows and autovacuum **did not run once** — the default threshold scales with the whole table (50 + 0.2 × 10 million ≈ 2 million) while the churn happens in a tiny subset.
- Method: The same harness, Postgres settings and 10-million-dead-plus-5,000-live table as the partial index measurement. Three differences, all required to ask this question. The consumer marks rows `done` rather than returning them to `pending` — the claim under test is about rows LEAVING the index, and it cannot be measured on rows that never leave. A producer runs alongside at a fixed arrival rate of 2,000/s, so the table also gets the insert traffic the previous record had to declare as missing. And the claim uses a sub-select `UPDATE` rather than `\gset`: an unthrottled consumer empties the queue in under a second and `\gset` kills the client on the empty result. The consumer is throttled to the arrival rate as well, so the queue depth holds steady — and it is sampled rather than assumed. Table size, index size, queue depth, dead tuples and the autovacuum counters were recorded every fifteen seconds; 60 samples per strategy.
- Metrics: Partial index over fifteen minutes: 0.1 → 38.2 MB (380×) · Composite index, same window: 301 → 427 MB (+42%) · Autovacuum runs: 0 · Composite latency at 900 s: 61,533 ms
- Measured on: 2026-08-22
- Confidence: High confidence
- Status: Current
- Programme: Database & queries
- Environment: Postgres 17-alpine · shared_buffers 1 GB · autovacuum at defaults · Table 10M dead + 5,000 live rows · 1,421 MB at the start · Load producer fixed at 2,000/s · consumer 8 clients at the same rate · Duration 900 s per strategy · sampled every 15 s · 60 samples · Hardware Apple M4 Pro · 12 cores · 24 GB · Docker Desktop 29.7.2
- Technologies: PostgreSQL, SQL, pgbench, Docker
- To reproduce: DURATION=900 ./bench/endurance.sh
- Source code: https://github.com/muhammetsafak/pg-queue-bench
- Raw data: https://github.com/muhammetsafak/pg-queue-bench/blob/main/results/2026-08-22/endurance-partial.jsonl
- Raw data licence: https://github.com/muhammetsafak/pg-queue-bench/blob/main/LICENSE
- Published: 2026-08-23
- Source: https://www.muhammetsafak.com.tr/en/research/queue-table-bloat-and-autovacuum/
- Language: en-US
- Author: Muhammet Şafak

---
The [partial index measurement](/en/research/partial-index-queue-table/) worked in
thirty-second windows and said so in its limits: *"30-second runs are far too
short to measure autovacuum's long-run effect; that is a separate record."* This
is that record.

The claim under test was the one point of the Sor Bakalım answer nothing had
measured: *"a row that turns from pending to done drops out of the partial index —
that is good. But heavy update traffic can produce bloat, so autovacuum working
properly matters. The good news: the index is small, so vacuuming it is cheap
too."*

## The bloat curve

**Index size under sustained churn**

Queue depth stayed near five thousand rows throughout. The only thing growing is dead index entries.

Source: pg_relation_size, sampled every 15 s, bench/endurance.sh

|  | 0 s | 135 | 286 | 436 | 587 | 738 | 888 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| partial | 0.1 MB | 5.9 MB | 12.4 MB | 18.8 MB | 25.3 MB | 31.7 MB | 38.2 MB |
| composite | 301 MB | 321 MB | 344 MB | 366 MB | 388 MB | 409 MB | 427 MB |

The partial index grew **three hundred and eighty times**. The composite one grew
**42%**. In absolute terms the composite gained more (+126 MB); in proportion the
two are not comparable.

The cause fits in a sentence: the `pending → done` transition drops the row out of
the partial index, but the dead entry stays there until vacuum arrives. The
index's **smallness** comes from the live set and its **bloat rate** comes from
throughput, and nothing connects the two. A five-thousand-row queue processing two
thousand jobs a second accumulates thirty-eight megabytes of dead entries in
fifteen minutes.

> **Result**
>
> The answer's "the index is small, so vacuuming it is cheap" is true and
> incomplete. Vacuum is cheap — but it is needed **often**, because the bloat rate
> follows throughput rather than index size. A small index does not mean less
> vacuuming; it means cheaper vacuuming.

## Autovacuum never came

Fifteen minutes produced 1,753,949 dead rows. The autovacuum counter: **0.**

The arithmetic is unforgiving:

```
threshold = autovacuum_vacuum_threshold + scale_factor × live rows
          = 50 + 0.2 × 10,005,000
          ≈ 2,001,050 dead rows
```

We stopped just under it. At defaults this table would trigger autovacuum roughly
every **seventeen minutes**, and the indexes bloat freely in between.

The real problem is not the ratio but the **direction of the scaling**: the
threshold grows with the whole table while the churn happens in a small live set
and does not speed up as the table grows. So the bigger the table, the later
vacuum arrives.

> **Caveat**
>
> The moment a queue table most needs vacuuming is the moment the defaults give it
> the least. Lowering `autovacuum_vacuum_scale_factor` per table (to 0.01, say) or
> pinning `autovacuum_vacuum_threshold` to a fixed number requires a setting
> specific to this table — the global default was not designed for this access
> pattern.

## A bloated index stops carrying sustained load

Both runs started with a target of 2,000 jobs a second.

| Strategy | At the start | At 900 s | Queue depth |
| --- | --- | --- | --- |
| 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 here.

The composite index missed the target — 2,000 tps down to 1,204, latency out to 61
seconds, backlog up to 126,000. The partial one held.

That is the **opposite** of the thirty-second result, where the two were nearly
equal (10,795 against 11,537 tps). The gap opens under sustained load, because an
index bloated to 427 megabytes no longer fits in memory and every scan goes to
disk.

> **Caveat**
>
> One machine, one Postgres version, one arrival rate. 2,000 jobs/s is far below
> this hardware's ceiling; it was chosen to stop the consumer draining the queue and
> measuring an empty table. At higher rates bloat accumulates faster and the
> autovacuum threshold arrives sooner, so the "seventeen minutes" here belongs to
> this workload rather than to Postgres. And because autovacuum never ran, the
> **cost** of vacuuming was not measured — only when it would have arrived.

## Back to the answer

This was the fifth of the answer's six points, and the only untested one. The
verdict: the point is right and its warning is weak. "Autovacuum working properly
matters" does not say that at defaults autovacuum **does not work properly** on
this table.

Choosing a partial index costs two things: dead index entries accumulate at the
rate of throughput, and the mechanism that clears them does not arrive unless it
is configured per table.