Skip to main content

VACUUM

Garbage-collects and optionally analyzes a database.

Syntax​

VACUUM [FULL] [FREEZE] [VERBOSE] [<table>]

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

Description​

VACUUM reclaims storage occupied by deleted tuples. In normal Relyt operations, tuples that are deleted or obsoleted by an update are not physically removed from their table. They remain present on disks until a VACUUM is done. Therefore, it is necessary to do VACUUM periodically, especially on frequently-updated tables.

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.

Parameters​

  • FULL

    Selects a full vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and does not release the old copy until the operation is complete. It is recommend that you use FULL only when a significant amount of space needs to be reclaimed from the table.

  • FREEZE

    Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age server configuration parameter set to zero.

  • VERBOSE

    Prints a detailed vacuum activity report for each table.

  • ANALYZE

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

  • <table>

    The name of the table from which the space will be reclaimed.

  • <column>

    The name of the column to analyze.

    When you specify columns in a VACUUM ANALYZE command, the command analzyes only the specified columns. No matter whether a column is specified, the VACUUM operation handles the entire table.

    The name of the column to analyze.

    When you specify columns in a VACUUM ANALYZE command, the command analzyes only the specified columns. No matter whether a column is specified, the VACUUM operation handles the entire table.

Usage notes​

VACUUM cannot be run inside a transaction block.

Vacuum active databases frequently (at least nightly), in order to remove expired rows. After adding or deleting a large number of rows, running the VACUUM ANALYZE command for the affected table might be useful. This updates the system catalogs with the results of all recent changes, and allows the Relyt query optimizer to make better choices in planning queries.

Manual VACUUM operations must be performed in user-defined databases to manage transaction IDs (XIDs) in those databases. 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.

VACUUM commands skip external tables.

VACUUM FULL reclaims all expired row space, however it requires an exclusive lock on each table being processed, is a very expensive operation, and might take a long time to complete on large and distributed Relyt tables. Perform VACUUM FULL operations during database maintenance periods.

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and expect the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM will.

Examples​

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

VACUUM (VERBOSE, ANALYZE) onek;

Vacuum all tables in the current database:

VACUUM;

Vacuum a specific table only:

VACUUM (VERBOSE, ANALYZE) mytable;

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

VACUUM ANALYZE;

SQL standard compatibility​

There is no VACUUM statement in the SQL standard.