What is VACUUM in PostgreSQL?

The process of cleaning up "dead" rows that are created as a result of UPDATE and DELETE operations.

• UPDATE — does not physically delete the old version of the row, but creates a new one.
• DELETE — marks the row as deleted but does not remove it physically.

"Dead" rows occupy space on the disk and can slow down queries.

Main tasks:

• Releasing disk space: Returns the space occupied by "dead" rows to the operating system.
• Updating statistics: Gathers information about data distribution, helping the query optimizer to build more efficient execution plans.
• Preventing transaction ID wraparound: Important for maintaining data integrity. Each transaction is assigned an ID. If the ID overflows, old transactions might be mistakenly perceived as future ones, leading to data loss. VACUUM cleans up old IDs.

Types of VACUUM:

• VACUUM: Cleans "dead" rows and updates statistics (if NOVACUUM is not specified). Does not return space to the operating system.
• VACUUM FULL: A more aggressive option. Rewrites the entire table, removing "dead" rows and freeing space. Locks the table during execution.
• VACUUM FREEZE: Sets the transaction ID for "old" rows to a value OldestXID, preventing their deletion during regular VACUUM and helping to avoid transaction ID wraparound.

Autovacuum:

By default, PostgreSQL has autovacuum enabled. It automatically runs VACUUM and ANALYZE (updates statistics) for tables that have been significantly modified. Its parameters can be tuned for optimal performance.

Usage examples:

-- Regular vacuuming of a table
VACUUM my_table;

-- Vacuuming with full space reclamation
VACUUM FULL my_table;

-- Vacuuming with transaction ID freezing
VACUUM FREEZE my_table;

The importance of regular VACUUM:

Regular vacuuming, either manually or via autovacuum, is critical for maintaining the performance and health of a PostgreSQL database.