In PostgreSQL, what does VACUUM do?

In PostgreSQL's Multi-Version Concurrency Control (MVCC) architecture, when a row is updated or deleted, the old version of the row (the "dead tuple") is not immediately removed from the data files. The VACUUM command's primary purpose is to scan tables and reclaim the storage occupied by these dead tuples. This makes the space available for reuse by subsequent INSERT and UPDATE operations, preventing table and index bloat and ensuring efficient space management. It also updates the visibility map, which can speed up index-only scans.

A. VACUUM reclaims unused space but does not perform data compression. VACUUM FULL rewrites the table, which can shrink its on-disk size, but this is a side effect of rewriting, not compression.

C. Database backups are created using dedicated utilities such as pgdump, pgdumpall, or pgbasebackup, not the VACUUM command.

D. The ANALYZE command updates statistics used by the query planner. While often run with VACUUM (e.g., VACUUM ANALYZE), VACUUM's core function is storage reclamation, not plan optimization.

1. PostgreSQL 16 Documentation, Chapter 25. Routine Database Maintenance Tasks, Section 25.1.1. Recovering Disk Space.

"In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC)... But eventually, an outdated row version is no longer of interest to any active transaction. The space it occupies must be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM."

2. PostgreSQL 16 Documentation, SQL Commands, VACUUM.

The command description explicitly states: "VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done."

3. Pavlo, A. (2022). Lecture #16: Multi-Version Concurrency Control. 15-445/645 Intro to Database Systems (Fall 2022), Carnegie Mellon University.

Slide 39, "Garbage Collection," explains the necessity of a background process in MVCC systems to remove old tuple versions that are no longer visible to any active transaction, which is the process PostgreSQL calls vacuuming.