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.
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.
Prints a detailed vacuum activity report for each table.
VACUUMshould 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,
VACUUMmay still block when opening the relation’s indexes. Also, while
VACUUMordinarily processes all partitions of specified partitioned tables, this option will cause
VACUUMto skip all partitions if there is a conflicting lock on the partitioned table.
VACUUMshould 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_truncateoption has been set to false for the table to be vacuumed. Setting this option to false may be useful to avoid
ACCESS EXCLUSIVElock on the table that the truncation requires.
Specifies whether the selected option should be turned on or off. You can write
1to enable the option, and
0to disable it. The
booleanvalue can also be omitted, in which case
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.
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.