Where Bloat Comes From
In databases that use multiversion concurrency control, an UPDATE or DELETE does not overwrite a row. It marks the old version dead and writes a new one so other transactions still see a consistent snapshot. Over time these dead versions pile up as table bloat.
What Vacuum Does
A vacuum scans tables and marks the space of dead versions reusable once no running transaction needs them. Autovacuum is a background process that triggers this automatically when enough rows have changed.
Costs of Bloat
- A bloated table has more pages, so scans read more disk.
- Indexes also bloat, slowing lookups.
- Left unchecked, the oldest transaction id can approach a wraparound limit that forces an emergency vacuum.
Tuning
- A plain vacuum reclaims space for reuse but does not shrink the file. A full vacuum rewrites the table but locks it.
- Raise autovacuum aggressiveness on hot tables so dead rows are cleaned before they accumulate.
Key idea
Under multiversion concurrency control updates leave dead row versions that bloat tables, and autovacuum reclaims their space in the background to keep scans and indexes efficient.