The syntax for the VACUUM SQL command is as follows:
VACUUM [ VERBOSE ] [ ANALYZE ] [
table
]
VACUUM [ VERBOSE ] ANALYZE [
table
[ (
column
[, ...] ) ] ]
Used without any of the optional keywords or identifiers, a VACUUM statement will
clean up each table in the presently connected database, one at a time, deleting temporary data and recovering disk space.
This use of VACUUM is primarily to maximize free disk space.
An optional
table
identifier may be specified if you want the
VACUUM to clean a single table in the connected database, rather than all tables.
It will also update statistics in the system catalogs pertaining to the number of records and amount of data stored in each table.
Example 9-14 shows the use of a VACUUM statement in the
booktown database, on the books table.
Example 9-14. Using VACUUM on a table
booktown=#
VACUUM books;
VACUUM
The VACUUM message returned in Example 9-14 indicates that
the process finished successfully. If a specified table cannot be found, you will instead receive the following
notice:
NOTICE: Vacuum: table not found
With the use of the optional ANALYZE keyword, PostgreSQL examines the allocation of
data in each column for each table (or the specified table, if provided), and uses the information to prepare the
query optimizer for better planning. With the use of the ANALYZE keyword, you also have the
option to analyze only specified columns. Example 9-15 shows the use of the
VACUUM ANALYZE command on the entire booktown database.
Example 9-15. Using VACUUM ANALYZE on a database
booktown=#
VACUUM ANALYZE;
VACUUM
Finally, the optional VERBOSE keyword may be applied if you are interested in seeing
a detailed internal report of the findings of the VACUUM statement. This is most likely
not of interest to anyone not actively developing the PostgreSQL engine, or related software.
