What is VACUUM in PostgreSQL and why is it needed?

VACUUM in PostgreSQL is a command to clean the database of 'dead' rows that remain after updates and deletions.

PostgreSQL uses MVCC (Multi-Version Concurrency Control), so when data is modified, old versions of rows are not immediately deleted but marked as obsolete. VACUUM frees up the space occupied by these rows to prevent the growth of table size and improve performance.

Additionally, VACUUM updates statistics and helps avoid issues with autovacuum, preventing 'bloat' — excessive use of disk space.

Example usage:

VACUUM ANALYZE my_table;

The command will clean the table and update statistics for the query optimizer.