'\" t .\" Title: VACUUM .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "VACUUM" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" VACUUM \- garbage\-collect and optionally analyze a database .SH "SYNOPSIS" .sp .nf VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, \&.\&.\&.] ) ] [ \fItable_name\fR [ (\fIcolumn_name\fR [, \&.\&.\&.] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ \fItable_name\fR ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ \fItable_name\fR [ (\fIcolumn_name\fR [, \&.\&.\&.] ) ] ] .fi .SH "DESCRIPTION" .PP \fBVACUUM\fR reclaims storage occupied by dead tuples\&. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a \fBVACUUM\fR is done\&. Therefore it\(aqs necessary to do \fBVACUUM\fR periodically, especially on frequently\-updated tables\&. .PP With no parameter, \fBVACUUM\fR processes every table in the current database that the current user has permission to vacuum\&. With a parameter, \fBVACUUM\fR processes only that table\&. .PP \fBVACUUM ANALYZE\fR performs a \fBVACUUM\fR and then an \fBANALYZE\fR for each selected table\&. This is a handy combination form for routine maintenance scripts\&. See \fBANALYZE\fR(5) for more details about its processing\&. .PP Plain \fBVACUUM\fR (without FULL) simply reclaims space and makes it available for re\-use\&. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained\&. However, extra space is not returned to the operating system (in most cases); it\(aqs just kept available for re\-use within the same table\&. \fBVACUUM FULL\fR rewrites the entire contents of the table into a new disk file with no 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 while it is being processed\&. .PP 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\&. The parenthesized syntax was added in PostgreSQL 9\&.0; the unparenthesized syntax is deprecated\&. .SH "PARAMETERS" .PP FULL .RS 4 Selects \(lqfull\(rq 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 doesn\(aqt release the old copy until the operation is complete\&. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table\&. .RE .PP FREEZE .RS 4 Selects aggressive \(lqfreezing\(rq of tuples\&. Specifying FREEZE is equivalent to performing \fBVACUUM\fR with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero\&. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified\&. .RE .PP VERBOSE .RS 4 Prints a detailed vacuum activity report for each table\&. .RE .PP ANALYZE .RS 4 Updates statistics used by the planner to determine the most efficient way to execute a query\&. .RE .PP \fItable_name\fR .RS 4 The name (optionally schema\-qualified) of a specific table to vacuum\&. Defaults to all tables in the current database\&. .RE .PP \fIcolumn_name\fR .RS 4 The name of a specific column to analyze\&. Defaults to all columns\&. If a column list is specified, ANALYZE is implied\&. .RE .SH "OUTPUTS" .PP When VERBOSE is specified, \fBVACUUM\fR emits progress messages to indicate which table is currently being processed\&. Various statistics about the tables are printed as well\&. .SH "NOTES" .PP To vacuum a table, one must ordinarily be the table\(aqs 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 \fBVACUUM\fR can only be performed by a superuser\&.) \fBVACUUM\fR will skip over any tables that the calling user does not have permission to vacuum\&. .PP \fBVACUUM\fR cannot be executed inside a transaction block\&. .PP For tables with GIN indexes, \fBVACUUM\fR (in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main GIN index structure\&. See Section 61.4.1, \(lqGIN Fast Update Technique\(rq, in the documentation for details\&. .PP We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows\&. After adding or deleting a large number of rows, it might be a good idea to issue a \fBVACUUM ANALYZE\fR command for the affected table\&. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries\&. .PP The \fBFULL\fR 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 would like the table to physically shrink to occupy less disk space and allow faster table scans\&. \fBVACUUM FULL\fR will usually shrink the table more than a plain \fBVACUUM\fR would\&. .PP \fBVACUUM\fR causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions\&. Therefore, it is sometimes advisable to use the cost\-based vacuum delay feature\&. See Section 18.4.4, \(lqCost-based Vacuum Delay\(rq, in the documentation for details\&. .PP PostgreSQL includes an \(lqautovacuum\(rq facility which can automate routine vacuum maintenance\&. For more information about automatic and manual vacuuming, see Section 23.1, \(lqRoutine Vacuuming\(rq, in the documentation\&. .SH "EXAMPLES" .PP The following is an example from running \fBVACUUM\fR on a table in the regression database: .sp .if n \{\ .RS 4 .\} .nf regression=# VACUUM (VERBOSE, ANALYZE) onek; INFO: vacuuming "public\&.onek" INFO: index "onek_unique1" now contains 1000 tuples in 14 pages DETAIL: 3000 index tuples were removed\&. 0 index pages have been deleted, 0 are currently reusable\&. CPU 0\&.01s/0\&.08u sec elapsed 0\&.18 sec\&. INFO: index "onek_unique2" now contains 1000 tuples in 16 pages DETAIL: 3000 index tuples were removed\&. 0 index pages have been deleted, 0 are currently reusable\&. CPU 0\&.00s/0\&.07u sec elapsed 0\&.23 sec\&. INFO: index "onek_hundred" now contains 1000 tuples in 13 pages DETAIL: 3000 index tuples were removed\&. 0 index pages have been deleted, 0 are currently reusable\&. CPU 0\&.01s/0\&.08u sec elapsed 0\&.17 sec\&. INFO: index "onek_stringu1" now contains 1000 tuples in 48 pages DETAIL: 3000 index tuples were removed\&. 0 index pages have been deleted, 0 are currently reusable\&. CPU 0\&.01s/0\&.09u sec elapsed 0\&.59 sec\&. INFO: "onek": removed 3000 tuples in 108 pages DETAIL: CPU 0\&.01s/0\&.06u sec elapsed 0\&.07 sec\&. INFO: "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages DETAIL: 0 dead tuples cannot be removed yet\&. There were 0 unused item pointers\&. Skipped 0 pages due to buffer pins\&. 0 pages are entirely empty\&. CPU 0\&.07s/0\&.39u sec elapsed 1\&.56 sec\&. INFO: analyzing "public\&.onek" INFO: "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows VACUUM .fi .if n \{\ .RE .\} .SH "COMPATIBILITY" .PP There is no \fBVACUUM\fR statement in the SQL standard\&. .SH "SEE ALSO" \fBvacuumdb\fR(1), Section 18.4.4, \(lqCost-based Vacuum Delay\(rq, in the documentation, Section 23.1.6, \(lqThe Autovacuum Daemon\(rq, in the documentation