1VACUUM(7)                PostgreSQL 10.7 Documentation               VACUUM(7)
2
3
4

NAME

6       VACUUM - garbage-collect and optionally analyze a database
7

SYNOPSIS

9       VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ]
10       VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]
11       VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]
12

DESCRIPTION

14       VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL
15       operation, tuples that are deleted or obsoleted by an update are not
16       physically removed from their table; they remain present until a VACUUM
17       is done. Therefore it's necessary to do VACUUM periodically, especially
18       on frequently-updated tables.
19
20       With no parameter, VACUUM processes every table in the current database
21       that the current user has permission to vacuum. With a parameter,
22       VACUUM processes only that table.
23
24       VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
25       table. This is a handy combination form for routine maintenance
26       scripts. See ANALYZE(7) for more details about its processing.
27
28       Plain VACUUM (without FULL) simply reclaims space and makes it
29       available for re-use. This form of the command can operate in parallel
30       with normal reading and writing of the table, as an exclusive lock is
31       not obtained. However, extra space is not returned to the operating
32       system (in most cases); it's just kept available for re-use within the
33       same table.  VACUUM FULL rewrites the entire contents of the table into
34       a new disk file with no extra space, allowing unused space to be
35       returned to the operating system. This form is much slower and requires
36       an exclusive lock on each table while it is being processed.
37
38       When the option list is surrounded by parentheses, the options can be
39       written in any order. Without parentheses, options must be specified in
40       exactly the order shown above. The parenthesized syntax was added in
41       PostgreSQL 9.0; the unparenthesized syntax is deprecated.
42

PARAMETERS

44       FULL
45           Selects “full” vacuum, which can reclaim more space, but takes much
46           longer and exclusively locks the table. This method also requires
47           extra disk space, since it writes a new copy of the table and
48           doesn't release the old copy until the operation is complete.
49           Usually this should only be used when a significant amount of space
50           needs to be reclaimed from within the table.
51
52       FREEZE
53           Selects aggressive “freezing” of tuples. Specifying FREEZE is
54           equivalent to performing VACUUM with the vacuum_freeze_min_age and
55           vacuum_freeze_table_age parameters set to zero. Aggressive freezing
56           is always performed when the table is rewritten, so this option is
57           redundant when FULL is specified.
58
59       VERBOSE
60           Prints a detailed vacuum activity report for each table.
61
62       ANALYZE
63           Updates statistics used by the planner to determine the most
64           efficient way to execute a query.
65
66       DISABLE_PAGE_SKIPPING
67           Normally, VACUUM will skip pages based on the visibility map. Pages
68           where all tuples are known to be frozen can always be skipped, and
69           those where all tuples are known to be visible to all transactions
70           may be skipped except when performing an aggressive vacuum.
71           Furthermore, except when performing an aggressive vacuum, some
72           pages may be skipped in order to avoid waiting for other sessions
73           to finish using them. This option disables all page-skipping
74           behavior, and is intended to be used only the contents of the
75           visibility map are thought to be suspect, which should happen only
76           if there is a hardware or software issue causing database
77           corruption.
78
79       table_name
80           The name (optionally schema-qualified) of a specific table to
81           vacuum. If omitted, all regular tables and materialized views in
82           the current database are vacuumed. If the specified table is a
83           partitioned table, all of its leaf partitions are vacuumed.
84
85       column_name
86           The name of a specific column to analyze. Defaults to all columns.
87           If a column list is specified, ANALYZE is implied.
88

OUTPUTS

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

NOTES

95       To vacuum a table, one must ordinarily be the table's owner or a
96       superuser. However, database owners are allowed to vacuum all tables in
97       their databases, except shared catalogs. (The restriction for shared
98       catalogs means that a true database-wide VACUUM can only be performed
99       by a superuser.)  VACUUM will skip over any tables that the calling
100       user does not have permission to vacuum.
101
102       VACUUM cannot be executed inside a transaction block.
103
104       For tables with GIN indexes, VACUUM (in any form) also completes any
105       pending index insertions, by moving pending index entries to the
106       appropriate places in the main GIN index structure. See Section 64.4.1
107       for details.
108
109       We recommend that active production databases be vacuumed frequently
110       (at least nightly), in order to remove dead rows. After adding or
111       deleting a large number of rows, it might be a good idea to issue a
112       VACUUM ANALYZE command for the affected table. This will update the
113       system catalogs with the results of all recent changes, and allow the
114       PostgreSQL query planner to make better choices in planning queries.
115
116       The FULL option is not recommended for routine use, but might be useful
117       in special cases. An example is when you have deleted or updated most
118       of the rows in a table and would like the table to physically shrink to
119       occupy less disk space and allow faster table scans.  VACUUM FULL will
120       usually shrink the table more than a plain VACUUM would.
121
122       VACUUM causes a substantial increase in I/O traffic, which might cause
123       poor performance for other active sessions. Therefore, it is sometimes
124       advisable to use the cost-based vacuum delay feature. See
125       Section 19.4.4 for details.
126
127       PostgreSQL includes an “autovacuum” facility which can automate routine
128       vacuum maintenance. For more information about automatic and manual
129       vacuuming, see Section 24.1.
130

EXAMPLES

132       To clean a single table onek, analyze it for the optimizer and print a
133       detailed vacuum activity report:
134
135           VACUUM (VERBOSE, ANALYZE) onek;
136

COMPATIBILITY

138       There is no VACUUM statement in the SQL standard.
139

SEE ALSO

141       vacuumdb(1), Section 19.4.4, Section 24.1.6
142
143
144
145PostgreSQL 10.7                      2019                            VACUUM(7)
Impressum