Under multi-version concurrency control, updating or deleting a row leaves a dead version behind. A background process reclaims that space so it can be reused. When it keeps up, nobody thinks about it.
When it falls behind on a high-churn table, the table grows far beyond its live data, scans read pages full of invisible rows, indexes bloat alongside, and query performance degrades for reasons entirely invisible in the application.
What changed in 2026
- Managed database defaults improved. Better out-of-the-box settings reduced how often manual tuning was needed.
- Per-table configuration became standard advice. Recognition that global settings cannot serve both a small reference table and a high-churn queue table.
- Monitoring moved into standard dashboards. Dead row counts and last-vacuum times became visible rather than something you queried during an incident.
- The long-transaction interaction got emphasised. Awareness that no tuning helps while an old transaction pins versions.
Why defaults underserve big tables
The default triggering rule combines a fixed number of dead rows with a fraction of the table's size. The fractional component is the problem.
A table with a thousand rows triggers vacuum after a small number of dead rows. A table with a hundred million rows must accumulate a proportionally enormous number of dead rows before the same fraction is reached.
So the largest, busiest tables — the ones where bloat hurts most — are vacuumed least often relative to their churn. That is exactly backwards, and it is why per-table settings matter.
| Table |
Default behaviour |
Better |
| Small, low churn |
Frequent enough |
Leave alone |
| Large, low churn |
Infrequent |
Usually fine |
| Large, high churn |
Far too infrequent |
Lower the scale factor substantially |
| Queue-like table |
Far too infrequent |
Aggressive per-table settings |
For a high-churn table, reducing the scale factor so vacuum triggers on a much smaller proportion — or effectively on an absolute row count — keeps cleanup proportional to churn rather than to size.
Throttling and workers
Vacuum is deliberately throttled so it does not saturate I/O. A cost limit governs how much work it does before pausing.
That is sensible protection and it means cleanup can be unable to keep up on a busy system, even when triggered promptly. If dead rows accumulate despite vacuum running, the throttle is the constraint and raising the cost limit is the lever.
Worker count matters too. A fixed number of workers process tables one at a time; if you have many tables needing attention, they queue. On a database with many high-churn tables, more workers helps — each still throttled individually, so total I/O impact scales with worker count.
The tradeoff is honest: more aggressive vacuum means more background I/O competing with queries. That is nearly always better than the alternative, because bloat degrades everything permanently while vacuum I/O is transient.
Long transactions defeat everything
The most important thing to know, and the one no amount of tuning addresses.
Vacuum can only reclaim versions no active transaction might need. A transaction that started an hour ago pins every version created since, across the whole database. Vacuum runs, finds nothing reclaimable, and dead rows accumulate regardless of how aggressively you configured it.
The usual causes are an idle-in-transaction application connection, a long-running analytical query, or an abandoned session — see MVCC explained.
Which means monitoring the age of the oldest transaction is more valuable than any vacuum setting. If cleanup is falling behind, check that first; tuning is pointless while a transaction from this morning is still open.
Common mistakes
- Global tuning only. Cannot serve tables with different churn profiles.
- Ignoring the scale factor on large tables. The core reason defaults fail.
- Disabling autovacuum. The problem compounds into an emergency.
- Tuning while a long transaction is open. Nothing will help.
- Not monitoring dead row counts. Bloat is invisible until it is severe.
- Raising cost limits without watching I/O. Can affect query latency.
- Forgetting indexes bloat too. They need attention alongside — see index bloat.
FAQ
How do I know if vacuum is keeping up?
Monitor dead row counts and time since last vacuum per table. Rising dead rows on a table being vacuumed means the throttle or the trigger threshold is the constraint.
Should I run manual vacuum?
Occasionally useful after a large bulk operation. Regular manual vacuum on a schedule suggests autovacuum is misconfigured — fix the configuration instead.
Does more aggressive vacuum hurt performance?
It uses I/O that queries would otherwise have. Almost always a better trade than bloat, which degrades performance permanently rather than transiently.
What about very large tables that never get vacuumed?
Lower the scale factor substantially for those specifically. A fractional threshold on a huge table is effectively never.
Where to go next
For what vacuum is cleaning up, read MVCC explained. For measuring the problem, bloat monitoring, and for the index side, index bloat.