What is Vacuum (Garbage Collection)?
In an MVCC database, updates and deletes do not immediately remove old row versions. They create new versions and mark old ones as "dead." Over time, these dead tuples accumulate, wasting disk space and slowing down sequential scans (which must skip over them). Vacuum (also called garbage collection) is the process of reclaiming space occupied by dead tuples that are no longer visible to any active transaction.
When Is a Tuple Dead?
A tuple version is "dead" when no active transaction can ever see it. This means:
- The tuple was deleted or replaced by a committed transaction.
- No currently running snapshot has a low enough transaction ID to still see the old version.
The database tracks the oldest active snapshot across all connections. Any tuple version whose replacement committed before that oldest snapshot is guaranteed dead.
PostgreSQL's VACUUM
PostgreSQL's standard VACUUM command scans the table for dead tuples and marks their space as reusable within the table's data files. However, it does not return space to the operating system — the file size on disk stays the same. The reclaimed space is tracked in a free space map (FSM) and will be reused by future inserts and updates.
VACUUM FULL
VACUUM FULL rewrites the entire table into a new file, compacting live tuples together and eliminating all dead space. This does return space to the OS, shrinking the file. However, it requires an ACCESS EXCLUSIVE lock on the table for the entire duration — no reads or writes are allowed. It also requires enough temporary disk space for the new copy. In practice, VACUUM FULL is rarely used on large production tables because of the lock.
Autovacuum
PostgreSQL runs an autovacuum daemon in the background. It monitors tables and triggers vacuum automatically when the ratio of dead tuples to live tuples exceeds a configurable threshold (default: 20% dead tuples, or 50 dead tuples, whichever is more). Autovacuum runs concurrently with normal operations, throttling itself to avoid overwhelming I/O.
Transaction ID Wraparound
PostgreSQL uses 32-bit transaction IDs, which means there are approximately 4 billion possible IDs. Since transaction IDs are compared using modular arithmetic, IDs that are more than 2 billion transactions "in the past" wrap around and appear to be in the future — making their data invisible. This would cause catastrophic data loss.
To prevent wraparound, VACUUM freezes old tuples by replacing their xmin with a special "frozen" transaction ID that is always considered "in the past" by every snapshot. Once frozen, the tuple's visibility no longer depends on its original transaction ID.
Anti-Wraparound Vacuum
If a table has not been vacuumed for too long (approaching the 2-billion-transaction wraparound threshold), PostgreSQL triggers an aggressive anti-wraparound vacuum. If autovacuum cannot keep up, PostgreSQL will refuse to start new transactions entirely (the dreaded "database is not accepting commands" error) to force a manual vacuum. This is one of the most critical operational concerns in PostgreSQL administration.
Monitoring and Tuning
Key metrics to watch: n_dead_tup (dead tuple count per table), last_autovacuum (when vacuum last ran), and age(relfrozenxid) (how close the table is to wraparound). Tuning parameters include autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, and autovacuum_vacuum_cost_delay for controlling I/O throttling.
Real-Life: Vacuum Prevents Disk Bloat and Wraparound
A high-traffic PostgreSQL database processes 10,000 UPDATE statements per second on an orders table. Each update creates a new tuple version and leaves the old one dead.
Without vacuum:
- After 1 hour: 36 million dead tuples accumulated.
- The table file grows from 2 GB to 8 GB even though live data is still 2 GB.
- Sequential scans slow down 4x because they must read and skip dead tuples.
- After a few months: the table's oldest unfrozen transaction ID approaches the 2-billion wraparound limit.
With autovacuum (default settings):
- Autovacuum kicks in when dead tuples exceed 20% of the table.
- It runs in the background, scanning the table and marking dead space as reusable.
- New inserts and updates reuse the reclaimed space.
- The table file remains roughly stable at 2-3 GB.
- Transaction IDs are frozen periodically, preventing wraparound.
Real-world incident: In 2019, a major SaaS company's PostgreSQL instance stopped accepting writes because autovacuum had been inadvertently disabled on a critical table. The table's transaction ID age hit the wraparound limit. Recovery required an emergency manual VACUUM that took 6 hours to complete.
Other databases' approaches:
- MySQL InnoDB: purge thread cleans up undo log entries for old row versions
- Oracle: uses undo tablespace with automatic undo retention management
- CockroachDB: uses a background GC job that removes old MVCC versions based on a configurable GC TTL