PostgreSQL Vacuum Tuning: Mastering Bloat
Postgres is an MVCC (Multi-Version Concurrency Control) database. When you update a row, Postgres doesn't overwrite it; it marks the old row as "dead" and inserts a new one. Over time, these "dead tuples" accumulate, causing Bloat.
1. The Autovacuum Daemon
Autovacuum is the background process responsible for cleaning these dead rows so the space can be reused.
2. Tuning for High-Volume Writes
For a table with 10k+ TPS, the default autovacuum settings are too lazy. You will fall behind, and your tables will explode in size.
- : Reduce this (e.g., to 0.05) to trigger vacuuming sooner.
- : Increase this to let autovacuum work harder without being throttled by the OS.
3. The Visibility Map
Postgres keeps a "Visibility Map" to know which pages contain only "live" rows. It allows index-only scans to skip reading the heap entirely, provided the map is kept up to date by the vacuum process.
