VACUUM
Garbage-collects and optionally analyzes a database.
Note: HAWQ VACUUM
support is provided only for system catalog tables. VACUUM
ing a HAWQ user table has no effect.
Synopsis
VACUUM [FULL] [FREEZE] [VERBOSE] <table>
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE
[<table> [(<column> [, ...] )]]
Description
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.
Plain VACUUM
(without 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.
Note: VACUUM FULL
is not recommended in HAWQ.
Outputs
When VERBOSE
is specified, VACUUM
emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.
Parameters
Note: A VACUUM FULL is not recommended in HAWQ. See Notes.
FREEZE
is equivalent to performing VACUUM
with the vacuum_freeze_min_age
server configuration parameter set to zero. The FREEZE
option is deprecated and will be removed in a future release.Notes
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 VACUUM
and 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
command.
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:
-
max_fsm_pages
-
max_fsm_relations
Examples
Vacuum all tables in the current database:
VACUUM;
Vacuum a specific table only:
VACUUM mytable;
Vacuum all tables in the current database and collect statistics for the query planner:
VACUUM ANALYZE;
Compatibility
There is no VACUUM
statement in the SQL standard.