VACUUM is a PostgreSQL command for cleaning up dead rows from the database.
VACUUM is a PostgreSQL command for cleaning up dead rows from the database.
Because PostgreSQL uses multiversion concurrency control (MVCC), rows that are obsolete due to updates or deletion of rows are internally retained so that other concurrent transactions can continue to use the version of the database that existed at the time said transactions began. Eventually, these rows become obsolete, or dead. VACUUM removes these rows from the indexes and marks the space as usable for future transactions. Vacuuming is also required to advance the frozen XID required to properly keep track of past and pending transactions.
By default, an autovacuum daemon handles this automatically, vacuuming the database periodically to clean up dead rows as needed. This autovacuum daemon can be adjusted as appropriate or disabled altogether and replaced with VACUUM operations scheduled through the operating system.
VACUUM FULL performs a complete vacuum of the tables by rewriting them completely (as of PostgreSQL 9.0) to reclaim all space in dead rows, instead of simply marking the space as available. However, this operation requires an access-exclusive lock which prevents all access to each table as it undergoes a full vacuum, requires a significant amount of disk space as a full extra copy of the table is required, and can take a very long time to complete. This is not done by the autovacuum daemon and should only be performed when truly necessary.
For more information on vacuuming, see the Routine Vacuuming section of the PostgreSQL manual.
