VACUUM

Function

VACUUM recycles storage space occupied by tables or B-Tree indexes. In normal database operation, rows that have been deleted are not physically removed from their table; they remain present until a VACUUM is done. Therefore, it is necessary to do VACUUM periodically, especially on frequently-updated tables.

Precautions

• With no table specified, VACUUM processes all the tables that the current user has permission to vacuum in the current database. With a parameter, VACUUM processes only that table.
• To vacuum a table, you must ordinarily be the table's owner or the system administrator. However, database owners are allowed to VACUUM all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide VACUUM can only be executed by the system administrator). VACUUM skips over any tables that the calling user does not have the permission to vacuum.
• VACUUM cannot be executed inside a transaction block.
• It is recommended that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. After adding or deleting a large number of rows, it might be a good idea to run VACUUM ANALYZE for the affected table. This will update the system catalogs with the results of all recent changes, and allow the query planner to make better choices in planning queries.
• FULL is recommended only in special scenarios. For example, you wish to physically narrow the table to decrease the occupied disk space after deleting most rows of a table. VACUUM FULL usually shrinks a table more than VACUUM does. The FULL option does not clear indexes. You are advised to periodically run the REINDEX statement. Deleting all indexes, running VACUUM FULL, and rebuilding indexes is usually a faster choice. If the physical space usage does not decrease after you run the statement, check whether there are other active transactions (that have started before you delete data transactions and not ended before you run VACUUM FULL). If there are such transactions, run this statement again when the transactions quit.
• VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based VACUUM delay feature.
• When VERBOSE is specified, VACUUM prints progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well. However, if you execute VACUUM and specify the VERBOSE option for column-store tables, no information is returned.
• When the option list is surrounded by parentheses, the options can be written in any order. If there are no brackets, the options must be given in the order displayed in the syntax.
• VACUUM and VACUUM FULL clear deleted tuples after the delay specified by vacuum_defer_cleanup_age.
• VACUUM ANALYZE executes a VACUUM operation and then an ANALYZE operation for each selected table. This is a handy combination form for routine maintenance scripts.
• Plain VACUUM (without FULL) simply reclaims space and makes it available for reuse. This form of statement can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. VACUUM FULL executes wider processing, including moving rows across blocks to compress tables so they occupy the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.
• When you do VACUUM to a column-store table, the following operations are internally performed: data in the delta table is migrated to the primary table, and the delta and desc tables of the primary table are vacuumed. VACUUM does not reclaim the storage space of the delta table. To reclaim it, do VACUUM DELTAMERGE to the column-store table.
• A deadlock may occur when multiple VACUUM FULL statements are executed simultaneously.

Syntax

• Reclaim space and update statistics information, no requirements for keyword orders.

  VACUUM [ ( { FULL | FREEZE | VERBOSE | {ANALYZE | ANALYSE }} [,...] ) ]
      [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];
  

• Reclaim space, without updating statistics information.

  VACUUM [ FULL [COMPACT] ] [ FREEZE ] [ VERBOSE ] [ table_name ] [ PARTITION ( partition_name ) ];
  

• Reclaim space and update statistics information, and require keywords in order.

  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] { ANALYZE | ANALYSE } [ VERBOSE ] 
      [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];
  

Parameter Description

• FULL

  Selects “FULL” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table.

  NOTE:
  Using FULL will cause statistics missing. To collect statistics, add the keyword ANALYZE to VACUUM FULL.

• FREEZE

  Is equivalent to running VACUUM with the vacuum_freeze_min_age parameter set to zero.

• VERBOSE

  Prints a detailed VACUUM activity report for each table.

• ANALYZE | ANALYSE

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

• table_name

  Specifies the name (optionally schema-qualified) of a specific table to vacuum.

  Value range: name of a specific table to vacuum Defaults are all tables in the current database.

• column_name

  Specifies the name of a specific field to analyze.

  Value range: name of a specific field to analyze Defaults are all columns.

• PARTITION

  COMPACT and PARTITION cannot be used at the same time.

• partition_name

  Specifies the partition name of the table to be cleared. Defaults are all partitions.

• DELTAMERGE

  (For column-store tables) Migrates data from the delta table to primary tables. For a column-store table, this operation is controlled by deltarow_threshold. For details, see enable_delta_store and Parameter Description.

  NOTE:
  The following DFX functions are provided to return the data storage in the delta table of a column-store table:

  • pgxc_get_delta_info(TEXT): The input parameter is a column-store table name. The delta table information on each node is collected and displayed, including the number of active tuples, table size, and maximum block ID.
  • get_delta_info(TEXT): The input parameter is a column-store table name. The system summarizes the results returned from pgxc_get_delta_info and returns the total number of active tuples, total table size, and maximum block ID in the delta table.

Examples

-- Create an index in the tpcds.reason tabletable:
postgres=# CREATE UNIQUE INDEX ds_reason_index1 ON tpcds.reason(r_reason_sk);

-- Vacuum the tpcds.reason table that has indexes:
postgres=# VACUUM (VERBOSE, ANALYZE) tpcds.reason;

-- Drop an index.
postgres=# DROP INDEX ds_reason_index1 CASCADE;
postgres=# DROP TABLE tpcds.reason;

Suggestions

• vacuum
  • VACUUM cannot be executed inside a transaction block.
  • It is recommended that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. It is strongly recommended that you run VACUUM ANALYZE after adding or deleting a large number of records.
  • FULL is recommended only in special scenarios. For example, you wish to physically narrow the table to decrease the occupied disk space after deleting most rows of a table.
  • Before performing the VACUUM FULL operation, you are advised to delete all indexes in related tables, run VACUUM FULL, and then re-create the index.