VACUUM

Garbage collection and optionally analyze the database.

Summary

VACUUM [({ FULL | FREEZE | VERBOSE | ANALYZE } [, ...])] [table [(column [, ...] )]]

VACUUM [FULL] [FREEZE] [VERBOSE] [table]

VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE
              [table [(column [, ...] )]]

describe

VACUUM recycles the storage occupied by deleted tuples. In normal YMatrix database operations, updated deleted or discarded tuples are not physically deleted from the table; they are left on disk until VACUUM is completed. Therefore, it is necessary to perform VACUUM regularly, especially on frequently updated tables.

If there are no parameters, VACUUM will process each table in the current database. With parameters, VACUUM only processes this table.

VACUUM ANALYZE Performs VACUUM on each selected table, and then performs ANALYZE. This is a convenient combination of daily maintenance scripts. See ANALYZE for more details on the processing.

VACUUM (No FULL) marks deleted and outdated data in tables and indexes for future reuse, and can only be recycled for reuse if the space is at the end of the table and exclusive table locks can be easily obtained. The unused space at the beginning or in the middle of the table remains the same. For heap tables, this form of command can be run in parallel with normal reads and writes of the table because no exclusive lock is obtained. However, in most cases, the excess space is not returned to the operating system. It can just be reused in the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file without extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table during processing.

Using append optimization tables, VACUUM first cleans the index, then compresses each segment file in turn, and finally cleans the auxiliary table and updates statistics to compress the table. On each segment, copy the visible line from the current segment file to the new segment file, and then plan to delete the current segment file and make the new segment file available. Normal VACUUM appends optimized tables to allow scanning, inserting, deleting and updating tables while compressing segment files. However, the current segment file will be temporarily deleted and the new segment file will be activated using the Access Exclusive lock.

VACUUM FULL performs a wider processing, including moving tuples between blocks to try to compress tables to a minimum number of disk blocks. This form is much slower and requires an Access Exclusive lock to be applied when processing each table. Access Exclusive lock ensures that the owner is the only transaction that accesses the table in any way.

Parameters

FULL

Choose full vacuum, which reclaims more space, but takes longer to exclusive lock tables. This method also requires additional disk space as it writes to a new copy of the table and the old copy is not released until the operation is completed. Typically, this option should be used only if you need to reclaim a large amount of space from the table.

FREEZE

Specifying FREEZE is equivalent to setting the vacuum_freeze_min_age server configuration parameter to zero to execute VACUUM.

VERBOSE

Print detailed vacuum activity reports for each table.

ANALYZE

Update statistics used by the optimizer to determine the fastest way to execute queries.

table

The name of the table to vacuum (can be modified with schema). Defaults to all tables in the current database.

column

The name of the specific column to be analyzed. Defaults to all columns. If a column list is specified, it means ANALYZE.

Note

VACUUM cannot be executed within a transaction block.

Frequently (at least once a night) vacuum active database in order to delete expired rows. After adding or removing a large number of rows, it may be useful to run the VACUUM ANALYZE command on the affected table. This will use all the results of recent changes to update the system catalog and enable the YMatrix database query optimizer to make better choices in the scheduled query.

Note: PostgreSQL has a separate optional server process called autovacuum daemon, whose purpose is to automatically execute VACUUM and ANALYZE commands. The YMatrix database enables the autovacuum daemon to perform VACUUM operations only on the YMatrix database template database template0. Autovacuum is enabled for template0 because connection to template0 is not allowed. The autovacuum daemon performs VACUUM operations on template0 to manage transaction IDs (XIDs) and helps avoid transaction ID surround issues in template0. Manual VACUUM operations must be performed in user-defined databases to manage transaction IDs (XIDs) in these databases.

VACUUM causes a significant increase in I/O traffic, which may lead to performance degradation in other active sessions. Therefore, it is recommended to vacuum database at low usage.

The VACUUM command skips external tables.

VACUUM FULL recycles all expired row space, but it requires exclusive locking of each table being processed, which is a very expensive operation and can take a long time to complete a large distributed YMatrix database table. Perform VACUUM FULL operation during database maintenance.

The FULL option is not recommended for routine use, but may be useful in special cases. For example, when you delete or update most rows in a table and want the table to be physically reduced to take up less disk space and allow faster table scans. VACUUM FULL usually shrinks the table more than normal VACUUM.

As an alternative to VACUUM FULL, you can use the CREATE TABLE AS statement to recreate the table and delete the old table.

For additional optimization tables, VACUUM requires enough free disk space to accommodate new segment files during the VACUUM process. If the ratio of hidden rows to total rows in a segment file is less than the threshold (default is 10), the segment file is not compressed. The threshold can be configured using the gp_appendonly_compaction_threshold server configuration parameters. VACUUM FULL ignores the threshold and rewrites the segment file regardless of the ratio. VACUUM can be disabled for additional optimization tables using the gp_appendonly_compaction server configuration parameters.

If concurrent serializable transactions are detected while cleaning up additional optimization tables, the current and subsequent segment files are not compressed. If the segment file has been compressed, but a concurrent serializable transaction is detected in the transaction that deleted the original segment file, the deletion is ignored. After the cleaning is complete, this may put one or both segment files in a "waiting for deletion".

Example

To clean up a single table onek, perform an optimization analysis and print a detailed vacuum activity report:

VACUUM (VERBOSE, ANALYZE) onek;

Clean all tables in the current database:

VACUUM;

Clean only specific tables:

VACUUM (VERBOSE, ANALYZE) mytable;

Clean up all tables in the current database and collect statistics for the query optimizer:

VACUUM ANALYZE;

compatibility

There is no VACUUM statement in the SQL standard.