PostgreSQL Autovacuum Internals and Benchmark
a day ago
- Autovacuum is critical for PostgreSQL performance and database survival; it must be properly tuned to avoid performance degradation.
- MVCC keeps multiple row versions; vacuum reclaims dead tuples that are no longer visible to any transaction.
- The autovacuum launcher starts workers per database; workers independently vacuum eligible tables using thresholds based on dead tuples and inserts.
- Vacuum uses the visibility map to skip pages with no dead tuples, making it efficient; the buffer ring limits cache impact.
- Index cleanup requires a full scan of each index per vacuum cycle; limited work memory can cause multiple scans, increasing cost.
- Autovacuum cost is driven by the number of dirty pages (pages with dead tuples), not the count of dead tuples themselves.
- Benchmarks show that indexes amplify vacuum cost linearly; a compact distribution of dead tuples results in lower cost than a spread distribution.
- Concurrent operations are handled via page-level locking, and vacuum does not block normal DML except for short cleanup locks.