What is VACUUM in PostgreSQL?

VACUUM in PostgreSQL is a command for cleaning and reorganizing database tables. It removes 'dead' rows left after updates and deletions, freeing space and preventing table size growth.

PostgreSQL uses MVCC (Multi-Version Concurrency Control), so when data is modified, old row versions are not immediately deleted but marked as obsolete. VACUUM cleans up these obsolete records so they do not occupy space or slow down queries.

There are two main types of VACUUM:

• VACUUM — a regular cleanup that frees space for reuse but does not reduce the physical size of files.
• VACUUM FULL — a more intensive operation that compresses the table and reduces file size on disk but requires locking the table.

Example usage:

VACUUM my_table;
VACUUM FULL my_table;

Regularly running VACUUM is important for maintaining performance and preventing bloating issues. Modern versions of PostgreSQL include autovacuum — an automatic process that runs VACUUM on a schedule.