VACUUM

VACUUM — Garbage-collect and optionally analyze a database

Synopsis

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

FULL [ boolean ]

FREEZE [ boolean ]

VERBOSE [ boolean ]

ANALYZE [ boolean ]

DISABLE_PAGE_SKIPPING [ boolean ]

SKIP_LOCKED [ boolean ]

INDEX_CLEANUP [ boolean ]

TRUNCATE [ boolean ]

PARALLEL integer

and table_and_columns is:

table_name [ ( column_name [, ...] ) ]

Description

VACUUM reclaims storage space occupied by dead tuples. In normal operation, tuples that have been deleted or obsoleted by updates are not physically removed from their tables; they remain until a VACUUM is performed. Therefore, it is necessary to run VACUUM periodically, especially on frequently updated tables.

Without a table_and_columns list, VACUUM processes every table and materialized view in the current database that the current user has privilege to vacuum. If a list is given, VACUUM processes only those tables in the list.

VACUUM ANALYZE performs an ANALYZE on each selected table. This is a convenient combined form of the two commands for routine maintenance scripts. See ANALYZE for processing details.

Plain VACUUM (without FULL) simply reclaims space and makes it available for reuse. This form of the command can run in parallel with normal read/write operations on the table, since it does not acquire an exclusive lock. However, the extra space in this form is not returned to the operating system (in most cases); it is simply kept in the same table for reuse. It also allows us to leverage multiple CPUs to process indexes. This feature is called parallel vacuum. To disable this feature, the PARALLEL option can be used with parallel workers specified as zero. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, which returns unused space to the operating system. This form of the command is slower and requires an exclusive lock on each table while it is being processed.

When the option list is enclosed in parentheses, options can be written in any order. Without parentheses, options must be specified in exactly the order shown above.

Parameters

FULL

Selects "full" vacuum, which can reclaim more space but takes longer and requires an exclusive lock on the table. This method also requires additional disk space because it creates a new copy of the table and does not release the old copy until the operation completes. Typically, this method is only used when a large amount of space needs to be reclaimed from a table.

FREEZE

Selects aggressive tuple "freezing". Specifying FREEZE is equivalent to performing a VACUUM with the parameters vacuum_freeze_min_age and vacuum_freeze_table_age set to 0. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

VERBOSE

Prints a detailed vacuum activity report for each table.

ANALYZE

Updates statistics used by the optimizer to determine the most efficient way to execute a query.

DISABLE_PAGE_SKIPPING

Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen are always skipped, and pages where all tuples are visible to all transactions may be skipped (unless an aggressive vacuum is being performed). Additionally, unless an aggressive vacuum is being performed, some pages may be skipped to avoid waiting for their use to complete. This option disables all page-skipping behavior and is intended to be used only when the visibility map contents are suspect, which only happens when database corruption has occurred due to hardware or software issues.

SKIP_LOCKED

Specifies that VACUUM should not wait for any conflicting locks to be released when beginning to process a relation: if the relation cannot be locked immediately without waiting, the relation is skipped. Note that even with this option, VACUUM may still block when opening a relation's indexes.

Additionally, VACUUM ANALYZE may still block when collecting sample rows from partitions, inheritance child tables, and certain types of foreign tables. Also, while VACUUM normally processes all partitions of a specified partitioned table, this option will cause VACUUM to skip all partitions if there is a lock conflict on the partitioned table.

INDEX_CLEANUP

Specifies that VACUUM should attempt to remove index entries pointing to dead tuples. This is normally the desired behavior and is the default, unless the vacuum_index_cleanup option is set to false for the table being vacuumed. Setting this option to false may be useful if you need to run vacuum as quickly as possible, for example, to avoid imminent transaction ID wraparound. However, if index cleanup is not performed regularly, performance may suffer because indexes will accumulate dead tuples as the table is modified, and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is complete. This option has no effect on tables without indexes and is ignored if the FULL option is used.

TRUNCATE

Specifies that VACUUM should attempt to truncate any empty pages at the end of the table and return the disk space of the truncated pages to the operating system.

This is normally the desired behavior and is the default, unless the vacuum_truncate option is set to false for the table being vacuumed. Setting this option to false may help avoid the ACCESS EXCLUSIVE lock needed to truncate the table. This option is ignored if the FULL option is used.

PARALLEL

Performs VACUUM's index vacuum and index cleanup phases in parallel using integer background workers. The number of workers used to perform the operation equals the number of indexes on the relation that support parallel vacuum, limited by the number of workers specified by the PARALLEL option (if any), and further limited by max_parallel_maintenance_workers. An index can participate in parallel vacuum only if its size is greater than min_parallel_index_scan_size. Note that the number of parallel workers specified in integer is not guaranteed to be used during execution. The vacuum run may require fewer workers than specified, or none at all. Only one worker can be used per index. Therefore, parallel workers are started only when the table has at least 2 indexes. Vacuum workers are started before each phase begins and exit when the phase ends. These behaviors may change in future releases. This option cannot be used with the FULL option.

boolean

Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

Integer

Specifies a non-negative integer value passed to the selected option.

table_name

The name of the table or materialized view to vacuum (can be schema-qualified). If the specified table is a partitioned table, all of its leaf partitions will also be vacuumed.

column_name

The name of a specific column to analyze. The default is all columns. If a column list is specified, ANALYZE must also be specified.

Output

If VERBOSE is declared, VACUUM emits progress messages indicating which table is currently being processed. Various statistics about these tables are also printed.

Notes

To vacuum a table, the user must typically be the table owner or a superuser. However, database owners are allowed to vacuum all tables in their database except shared catalogs (the restriction on shared catalogs means that a true database-wide VACUUM can only be performed by a superuser). VACUUM will skip tables for which the user does not have vacuum privileges.

VACUUM cannot be executed inside a transaction block.

For tables with GIN indexes, VACUUM (in any form) also completes any pending index insertions by moving pending index entries to the appropriate positions in the main GIN index structure.

It is recommended to vacuum active production databases frequently (at least nightly) to ensure that dead rows are removed. After adding or deleting a large number of rows, it is good practice to run VACUUM ANALYZE on the affected tables. Doing so updates the system catalogs with the recent changes and allows the Halo query optimizer to make better choices when optimizing user queries.

For everyday use, the FULL option is not recommended, but it can be useful in special situations. One example is when you have deleted or updated most of the rows in a table; if you want to physically shrink the table to reduce disk space usage and allow faster table scans, this option is appropriate. VACUUM FULL typically shrinks a table more than a plain VACUUM.

The PARALLEL option is for vacuum purposes only. If this option is specified along with the ANALYZE option, it does not affect ANALYZE.

VACUUM causes a substantial increase in I/O traffic, which may cause degraded performance in other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. For parallel vacuum, each worker's sleep time is proportional to the amount of work done by that worker.

Halo includes an "autovacuum" facility that can automatically perform routine vacuum maintenance.

Examples

Vacuum a single table onek, analyze it for the optimizer, and print a detailed vacuum activity report:

VACUUM (VERBOSE, ANALYZE) onek;

See Also

VACUUMDB