Why VACUUM Exists
Because MVCC leaves dead row versions behind after UPDATE and DELETE, PostgreSQL needs a process to reclaim that space and make it available for reuse — that's VACUUM. Plain VACUUM scans a table's pages, identifies dead tuples that are no longer visible to any active snapshot, and marks that space as free within the existing table file (it does not, by default, return the space to the operating system or shrink the file on disk). VACUUM also updates the visibility map, which tracks which pages contain only tuples visible to everyone, enabling index-only scans to skip a heap lookup entirely for those pages.
Cricket analogy: VACUUM is like the ground staff clearing discarded bails, used tape, and equipment off the outfield between innings — the ground isn't resized, but the clutter is cleared so play can resume efficiently on the same pitch.
Autovacuum and Transaction ID Wraparound
The autovacuum daemon runs in the background and automatically triggers VACUUM (and ANALYZE, to refresh planner statistics) on tables once the number of dead tuples exceeds a threshold controlled by autovacuum_vacuum_scale_factor (default 20% of table rows) plus autovacuum_vacuum_threshold (default 50 rows). Beyond routine bloat cleanup, VACUUM has a second, more critical job: preventing transaction ID (XID) wraparound. PostgreSQL transaction IDs are a 32-bit counter, and because visibility comparisons rely on 'before/after' ordering that wraps every ~4 billion transactions, VACUUM must periodically 'freeze' old tuples (marking them as permanently visible) so that a wraparound doesn't make old committed data suddenly look like it was written in the future and become invisible.
Cricket analogy: Autovacuum's scale-factor trigger is like a groundskeeper who resurfaces the pitch automatically once wear crosses a measured threshold, rather than waiting for someone to notice cracks and file a manual request.
If autovacuum is disabled or falls behind on a high-write table (common causes: autovacuum_freeze_max_age set too high, long-held locks preventing autovacuum workers from completing, or too few autovacuum workers for the write volume), PostgreSQL will eventually force the issue: once age(datfrozenxid) approaches wraparound, the database enters a protective mode and, in the worst case, refuses new writes with an error until an emergency VACUUM FREEZE is run. This is why monitoring autovacuum lag and dead-tuple counts is considered essential production hygiene, not an optional tuning nicety.
Cricket analogy: Emergency wraparound protection is like the umpires suspending play entirely once the pitch becomes genuinely dangerous, rather than letting the match continue on unsafe ground — a forced stoppage that a groundskeeper should have prevented earlier.
-- Manual vacuum with verbose output and stats refresh
VACUUM (VERBOSE, ANALYZE) accounts;
-- Reclaim space back to the OS too (locks the table, use sparingly)
VACUUM FULL accounts;
-- Check how close a table is to transaction ID wraparound
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 10;
-- Key autovacuum tuning knobs (postgresql.conf or per-table via ALTER TABLE ... SET)
-- autovacuum_vacuum_scale_factor = 0.2 -- trigger at 20% dead tuples (default)
-- autovacuum_vacuum_threshold = 50 -- plus this many dead rows
-- autovacuum_freeze_max_age = 200000000 -- forces freeze before wraparound riskVACUUM FULL rewrites the entire table into a new file and requires an ACCESS EXCLUSIVE lock for its duration, blocking all reads and writes — it is not a routine maintenance command. Plain VACUUM (including autovacuum) runs concurrently with normal read/write activity and should be preferred for regular bloat control.
pg_stat_user_tables.last_autovacuum and last_autovacuum shows when autovacuum last ran on a table; combined with n_dead_tup, this is the fastest way to spot a table that autovacuum is failing to keep up with.
- VACUUM reclaims space from dead tuples left by UPDATE/DELETE, making it reusable within the table file.
- Plain VACUUM does not shrink the file on disk or return space to the OS; VACUUM FULL does, at the cost of an ACCESS EXCLUSIVE lock.
- Autovacuum triggers automatically based on autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold.
- VACUUM's second critical role is freezing old tuples to prevent 32-bit transaction ID wraparound.
- Falling too far behind on freezing can force PostgreSQL into an emergency, write-blocking protective mode.
- pg_stat_user_tables and pg_class.relfrozenxid age are the primary monitoring signals for vacuum health.
- Long-idle transactions and misconfigured thresholds are the most common causes of autovacuum falling behind.
Practice what you learned
1. What does plain VACUUM do with the space it reclaims from dead tuples?
2. Which command reclaims disk space back to the operating system but requires an ACCESS EXCLUSIVE lock?
3. What is the second critical purpose of VACUUM besides reclaiming dead tuple space?
4. What controls when autovacuum triggers a VACUUM on a given table?
5. What happens if a database gets too close to transaction ID wraparound without being vacuumed?
Was this page helpful?
You May Also Like
MVCC Explained
How PostgreSQL's Multi-Version Concurrency Control lets readers and writers avoid blocking each other by keeping multiple row versions and using visibility rules.
Locking in PostgreSQL
The row-level and table-level lock modes PostgreSQL uses to coordinate concurrent writers, and how to inspect and reason about lock contention.
ACID and Isolation Levels
How PostgreSQL guarantees Atomicity, Consistency, Isolation, and Durability, and how the four SQL isolation levels trade off correctness against concurrency.