The partial index grew three hundred and eighty times in fifteen minutes — and autovacuum never ran
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.
- 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
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.
- Measured on
- Published
measured yesterday
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
To reproduce
DURATION=900 ./bench/endurance.shThe partial index measurement 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.
- partial
- composite
MB lower is better Source: pg_relation_size, sampled every 15 s, bench/endurance.sh
Data table
| Series | 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 chart is drawn in the browser; the table below carries the same data.
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.
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 rowsWe 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.
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 missed the target | 2,000 tps · 0.52 ms | 1,204 tps · 61,533 ms | 5,000 → 126,024 |
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.
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.