Garbage-collects and optionally analyzes a database.
VACUUM support is provided only for system catalog tables.
VACUUMing a HAWQ user table has no effect.
VACUUM [FULL] [FREEZE] [VERBOSE] <table> VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [<table> [(<column> [, ...] )]]
VACUUM reclaims storage occupied by deleted tuples. In normal HAWQ operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present on disk until a
VACUUM is done. Therefore it is necessary to do
VACUUM periodically, especially on frequently-updated catalog tables. (
VACUUM has no effect on a normal HAWQ table, since the delete or update operations are not supported on normal HAWQ table.)
With no parameter,
VACUUM processes every table in the current database. With a parameter,
VACUUM processes only that table.
VACUUM ANALYZE performs a
VACUUM and then an
ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE for more details about its processing.
FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained.
VACUUM FULL does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.
VACUUM FULL is not recommended in HAWQ.
VERBOSE is specified,
VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.
Note: A VACUUM FULL is not recommended in HAWQ. See Notes.
FREEZEis equivalent to performing
vacuum_freeze_min_ageserver configuration parameter set to zero. The
FREEZEoption is deprecated and will be removed in a future release. Set the parameter by using
hawq configor by modifying your
hawq-site.xmlconfiguration file instead.
VACUUM cannot be executed inside a transaction block.
A recommended practice is to vacuum active production databases frequently (at least nightly), in order to remove expired rows. After adding or deleting a large number of rows, it may be a good idea to issue a
VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the HAWQ query planner to make better choices in planning queries.
VACUUM causes a substantial increase in I/O traffic, which can cause poor performance for other active sessions. Therefore, it is advisable to vacuum the database at low usage times. The
auto vacuum daemon feature, that automates the execution of
ANALYZE commands is currently disabled in HAWQ.
Expired rows are held in what is called the free space map. The free space map must be sized large enough to cover the dead rows of all tables in your database. If not sized large enough, space occupied by dead rows that overflow the free space map cannot be reclaimed by a regular
VACUUM FULL will reclaim all expired row space, but is a very expensive operation and may take an unacceptably long time to finish on large, distributed HAWQ tables. If you do get into a situation where the free space map has overflowed, it may be more timely to recreate the table with a
CREATE TABLE AS statement and drop the old table.
VACUUM FULL is not recommended in HAWQ. It is best to size the free space map appropriately. The free space map is configured with the following server configuration parameters:
Vacuum all tables in the current database:
Vacuum a specific table only:
Vacuum all tables in the current database and collect statistics for the query planner:
There is no
VACUUM statement in the SQL standard.