VACUUM

Description

VACUUM recycles storage space occupied by deleted rows in 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 execute VACUUM periodically, especially on frequently-updated tables.

Precautions

• If no table is specified, VACUUM processes the tables on which the user has the corresponding permission in the current database. If a table is specified, VACUUM processes only the specified table.
• To vacuum a table, you must be the owner of the table or a user granted the VACUUM permission for the specified table. When separation of duties is disabled, a system administrator has this permission by default. However, database owners are allowed to perform VACUUM on 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 system administrators). VACUUM skips over any tables that the calling user does not have permission to vacuum.
• VACUUM cannot be executed inside a transaction block.
• It is recommended that production databases be vacuumed frequently (at least nightly) to ensure that dead rows are removed. It might be a good idea to always perform VACUUM ANALYZE on the affected table after adding or deleting a large number of rows. 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 use the REINDEX statement. If the occupied physical space does not decrease after you run the statement, check whether there are other active transactions (that started before the data deleting transaction and has not finished before you run VACUUM FULL). If there are such transactions, wait for them to finish and run the statement again.
• VACUUM FULL returns the free space in the table to the tablespace by rebuilding the table. During the rebuilding, extra storage space equivalent to the valid data size in the table is required. For a non-segment-page table, after VACUUM FULL is executed, the physical files occupied by the source table are deleted, and the physical file space occupied by the source table is returned to the OS. For a segment-page table, after VACUUM FULL is executed, the physical space occupied by the source table is returned to the segment-page data file instead of the OS.
• VACUUM causes a significant increase in I/O traffic, which may affect the performance of 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.
• When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, the options must be specified in the order given 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 recycles 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.
• A deadlock may occur when multiple VACUUM FULL statements are executed simultaneously.
• If the xc_maintenance_mode parameter is not enabled, the VACUUM FULL operation will skip all system catalogs.
• If you run VACUUM FULL immediately after running DELETE, the space will not be recycled. After running DELETE, execute 1000 non-SELECT transactions, or wait for 1s and then execute one transaction. Then, run VACUUM FULL to recycle the space.
• During the execution of VACUUM FULL, the table will be locked exclusively. You are advised not to run VACUUM FULL during peak hours because it may result in long waiting time or service interruption.

Syntax

• Recycle space and update statistics. The keyword order does not matter.

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

• Recycle space, without updating statistics information.

  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name 
  [ PARTITION ( partition_name )  | SUBPARTITION ( subpartition_name ) ] ];

• Recycle space and update statistics. The keywords need to be in order.

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

Parameters

• FULL

  Selects "FULL" vacuum, which can recycle more space, but takes much longer and exclusively locks the table.

  

  Using FULL will result in statistics missing. To collect statistics, specify the keyword ANALYZE in the VACUUM FULL statement.

• FREEZE

  Specifying FREEZE is equivalent to running VACUUM with the vacuum_freeze_min_age parameter set to 0.

• VERBOSE

  Prints a detailed vacuum activity report for each table.

• ANALYZE

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

  

  Ustore partitioned tables will trigger VACUUM.

• table_name

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

  Value range: name of a specific table to vacuum. The default value indicates all tables in the current database.

• column_name

  Specifies the name of the column to be analyzed. This parameter must be used together with ANALYZE.

  Value range: name of a specific column to analyze. The default value indicates all columns.

  

  The mechanism of the VACUUM ANALYZE statement is to execute VACUUM and ANALYZE in sequence. Therefore, if column_name is incorrect, VACUUM may be successfully executed but ANALYZE may fail to be executed. For a partitioned table, ANALYZE may fail to be executed after VACUUM is successfully executed on a partition.

• partition_name

  Specifies the names of the level-1 table partitions to vacuum. The default value is all level-1 partitions.

• subpartition_name

  Specifies the names of the level-2 table partitions to vacuum. The default value is all level-2 partitions.

Examples

-- Create a schema.
m_db=# CREATE SCHEMA tpcds;

-- Create a table named tpcds.reason.
m_db=# CREATE TABLE tpcds.reason
(
  r_reason_sk      integer,
  r_reason_id      character(16),
  r_reason_desc    character(100)
);

-- Insert multiple records into the table.
m_db=# INSERT INTO tpcds.reason values(1,'AAAAAAAABAAAAAAA','reason 1'),(2,'AAAAAAAABAAAAAAA','reason 2');

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

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

-- Drop the index.
m_db=# DROP INDEX tpcds.ds_reason_index1 CASCADE;
m_db=# DROP TABLE tpcds.reason;
m_db=# DROP SCHEMA tpcds;

Suggestions

• vacuum
  • VACUUM cannot be executed inside a transaction block.
  • It is recommended that production databases be vacuumed frequently (at least nightly) to ensure that dead rows are removed. It is strongly recommended that you perform VACUUM ANALYZE on corresponding tables 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.