Part 152: PostgreSQL VACUUM, Autovacuum Tuning & TXID Wraparound

Managing dead tuple cleanup, disk bloat, and the 2-billion transaction limit.

1. Why VACUUM is Essential

Because MVCC creates new tuples on updates and leaves dead tuples on deletes: - Without vacuuming, tables and indexes experience runaway disk bloat. - Table scans become sluggish because they must read megabytes of dead tuples from disk. - Free space is not returned to the operating system; regular VACUUM marks space inside pages as reusable for future inserts.

2. Standard VACUUM vs VACUUM FULL

-- Non-blocking VACUUM: marks dead space reusable, updates visibility map
VACUUM (VERBOSE, ANALYZE) orders;

-- Exclusive lock VACUUM FULL: rewrites entire table to brand new file on disk
-- WARNING: Blocks ALL reads and writes until complete!
VACUUM FULL orders;

3. Transaction ID (TXID) Wraparound Catastrophe

PostgreSQL transaction IDs are 32-bit integers (~4.2 billion values). - Concurrency logic uses modulo arithmetic: active transactions can only see ~2.1 billion transactions in the past. - If transactions exceed 2.1 billion without vacuum freezing old tuples, PostgreSQL goes into read-only emergency safety mode! - Autovacuum automatically triggers aggressive freeze workers when autovacuum_freeze_max_age is approached:

-- Check oldest un-frozen transaction age per database
SELECT datname, age(datfrozenxid) 
FROM pg_database 
ORDER BY age(datfrozenxid) DESC;

Key Takeaways

• Never disable autovacuum globally in production.
• For high-write tables, tune autovacuum_vacuum_scale_factor down from default 0.2 (20%) to 0.05 (5%) for frequent, small cleanups.

In Part 153, we dive into MySQL InnoDB Architecture: Buffer Pool, Redo Log & Undo Log!