VACUUM

VACUUM — garbage-collect a database

VACUUM [ ( option [, ...] ) ] [ name [, ...] ]
VACUUM [ VERBOSE ] [ name [, ...] ]

where option can be one of:
    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]
    TRUNCATE [ boolean ]

VACUUM is used to do the following:

• Scan and clean all pages in the table. Attempts to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system.
• Attempts to shrink undos.
• Clearing dropped objects in the recycle bin.

Without a name list, VACUUM processes every table, materialized view, undo, and dropped object in recycle bin in the current database that the current user has permission to vacuum. With a list, VACUUM processes only those table(s).

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

• VERBOSE

  Prints a detailed vacuum activity report for each table.

• SKIP_LOCKED

  Specifies that VACUUM should not wait for any conflicting locks to be released when beginning work on a relation: if a relation cannot be locked immediately without waiting, the relation is skipped. Note that even with this option, VACUUM may still block when opening the relation’s indexes. Also, while VACUUM ordinarily processes all partitions of specified partitioned tables, this option will cause VACUUM to skip all partitions if there is a conflicting lock on the partitioned table.

• TRUNCATE

  Specifies that VACUUM should attempt to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system. This is normally the desired behavior and is the default unless the vacuum_truncate option has been set to false for the table to be vacuumed. Setting this option to false may be useful to avoid ACCESS EXCLUSIVE lock on the table that the truncation requires.

• 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.

• name

  The name (optionally schema-qualified) of a specific table, materialized view or undo to vacuum. If the specified table is a partitioned table, all of its leaf partitions are vacuumed.

When VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.

To vacuum a table, one must ordinarily be the table’s owner or a superuser. 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 performed by a superuser.) VACUUM will skip over any tables that the calling user does not have permission to vacuum.

VACUUM cannot be executed inside a transaction block.

To clean a single table onek, and print a detailed vacuum activity report:

VACUUM (VERBOSE) onek;

There is no VACUUM statement in the SQL standard.