What is VACUUM in PostgreSQL?

In PostgreSQL, the VACUUM command is used to clean and optimize tables. It removes 'dead' rows that remain after updates and deletes, freeing space and preventing the database size from growing.

Main functions of VACUUM:

• Removing dead rows: PostgreSQL uses MVCC (Multi-Version Concurrency Control), so when a row is updated or deleted, the old version remains until all transactions that can see it are finished. VACUUM removes these outdated versions.

• Releasing space: After removing dead rows, VACUUM can free up space for reuse.

• Updating statistics: When combined with ANALYZE, the command helps the query planner choose optimal plans.

There are two main types of VACUUM:

• Standard VACUUM: Runs without locking the tables, frees space for reuse, but does not reduce the physical file size.

• VACUUM FULL: Performs a full reorganization of the table, freeing disk space, but requires an exclusive lock on the table.

Example usage:

VACUUM;
VACUUM FULL books;

Regularly running VACUUM is important for maintaining performance and preventing database growth issues.