Vacuum cleans a Postgres database by cleaning records from rolled back transactions. It also updates statistics stored in the system catalogs. These statistics are used by the optimizer to determine the most efficient way to execute a query. By performing a vacuum, the query optimizer can make an informed decision on which execution path it should take. This, in turn increases the speed of the database when processing user queries.
The syntax to run a VACUUM command is:
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
The possible options are:
|VERBOSE - Displayes a detailed vacuum activity report for each table.|
|ANALYZE - Updates the column statistics used by the optimizer to determine|
|the most efficient way to execute a query.|
|table - The name of a specific table to vacuum. If not specified, it defaults to all tables.|
|column - The name of a specific column to analyze. If not specified, it defaults to all columns.|
Example 7-2. Vacuuming a Table
VACUUM analyze book;
You should have received a message which says, "VACUUM." It did not print the detailed report because we did not specify VERBOSE . The following updates the statistics used by the query optimizer for all tables in this database and display a detailed report:
Example 7-3. Vacuuming a Database
VACUUM VERBOSE ANALYZE;
|VACUUMing a Specific Database|
The previous command only performs a VACUUM on the database it is executed in. To use VACUUM on a specific database requires you to either log into that database and execute the VACUUM command, or use the vacuumdb command. The vacuumdb command can only be executed from the command line because it is a system command. For more information on the vacuumdb command, refer to the next section.