PostgreSQL Table Bloat and Defragmentation: Autovacuum vs VACUUM vs VACUUM FULL
When rows are updated or deleted in PostgreSQL, the database does not immediately remove the old row versions from the table. Instead, PostgreSQL uses MVCC (Multi-Version Concurrency Control) and creates new row versions when necessary. As a result, a table can contain dead tuples and occupy significantly more disk space than the amount required by its current live data. In this article, we will use the same table and workload to demonstrate how PostgreSQL handles this situation with: Autovacuum: Automatically removes dead tuples in the background and makes their space reusable. It does not require an exclusive table lock and normally does not shrink the table on disk. Manual VACUUM: Does the same cleanup as autovacuum, but you start it manually. It removes dead tuples and makes space reusable, but normally does not shrink the table.It does not require an exclusive table lock. VACUUM FULL: Rebuilds the table using only live rows and physically releases unused disk space. It ...