1VACUUM(7) PostgreSQL 12.6 Documentation VACUUM(7)
2
6 VACUUM - garbage-collect and optionally analyze a database
7
9 VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
10 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
11 where option can be one of:
13 FULL [ boolean ]
15 FREEZE [ boolean ]
16 VERBOSE [ boolean ]
17 ANALYZE [ boolean ]
18 DISABLE_PAGE_SKIPPING [ boolean ]
19 SKIP_LOCKED [ boolean ]
20 INDEX_CLEANUP [ boolean ]
21 TRUNCATE [ boolean ]
22 and table_and_columns is:
24 table_name [ ( column_name [, ...] ) ]
26
28 VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL
29 operation, tuples that are deleted or obsoleted by an update are not
30 physically removed from their table; they remain present until a VACUUM
31 is done. Therefore it's necessary to do VACUUM periodically, especially
32 on frequently-updated tables.
33 Without a table_and_columns list, VACUUM processes every table and
35 materialized view in the current database that the current user has
36 permission to vacuum. With a list, VACUUM processes only those
37 table(s).
38 VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
40 table. This is a handy combination form for routine maintenance
41 scripts. See ANALYZE(7) for more details about its processing.
42 Plain VACUUM (without FULL) simply reclaims space and makes it
44 available for re-use. This form of the command can operate in parallel
45 with normal reading and writing of the table, as an exclusive lock is
46 not obtained. However, extra space is not returned to the operating
47 system (in most cases); it's just kept available for re-use within the
48 same table. VACUUM FULL rewrites the entire contents of the table into
49 a new disk file with no extra space, allowing unused space to be
50 returned to the operating system. This form is much slower and requires
51 an exclusive lock on each table while it is being processed.
52 When the option list is surrounded by parentheses, the options can be
54 written in any order. Without parentheses, options must be specified in
55 exactly the order shown above. The parenthesized syntax was added in
56 PostgreSQL 9.0; the unparenthesized syntax is deprecated.
57
59 FULL
60 Selects “full” vacuum, which can reclaim more space, but takes much
61 longer and exclusively locks the table. This method also requires
62 extra disk space, since it writes a new copy of the table and
63 doesn't release the old copy until the operation is complete.
64 Usually this should only be used when a significant amount of space
65 needs to be reclaimed from within the table.
66 FREEZE
68 Selects aggressive “freezing” of tuples. Specifying FREEZE is
69 equivalent to performing VACUUM with the vacuum_freeze_min_age and
70 vacuum_freeze_table_age parameters set to zero. Aggressive freezing
71 is always performed when the table is rewritten, so this option is
72 redundant when FULL is specified.
73 VERBOSE
75 Prints a detailed vacuum activity report for each table.
76 ANALYZE
78 Updates statistics used by the planner to determine the most
79 efficient way to execute a query.
80 DISABLE_PAGE_SKIPPING
82 Normally, VACUUM will skip pages based on the visibility map. Pages
83 where all tuples are known to be frozen can always be skipped, and
84 those where all tuples are known to be visible to all transactions
85 may be skipped except when performing an aggressive vacuum.
86 Furthermore, except when performing an aggressive vacuum, some
87 pages may be skipped in order to avoid waiting for other sessions
88 to finish using them. This option disables all page-skipping
89 behavior, and is intended to be used only when the contents of the
90 visibility map are suspect, which should happen only if there is a
91 hardware or software issue causing database corruption.
92 SKIP_LOCKED
94 Specifies that VACUUM should not wait for any conflicting locks to
95 be released when beginning work on a relation: if a relation cannot
96 be locked immediately without waiting, the relation is skipped.
97 Note that even with this option, VACUUM may still block when
98 opening the relation's indexes. Additionally, VACUUM ANALYZE may
99 still block when acquiring sample rows from partitions, table
100 inheritance children, and some types of foreign tables. Also, while
101 VACUUM ordinarily processes all partitions of specified partitioned
102 tables, this option will cause VACUUM to skip all partitions if
103 there is a conflicting lock on the partitioned table.
104 INDEX_CLEANUP
106 Specifies that VACUUM should attempt to remove index entries
107 pointing to dead tuples. This is normally the desired behavior and
108 is the default unless the vacuum_index_cleanup option has been set
109 to false for the table to be vacuumed. Setting this option to false
110 may be useful when it is necessary to make vacuum run as quickly as
111 possible, for example to avoid imminent transaction ID wraparound
112 (see Section 24.1.5). However, if index cleanup is not performed
113 regularly, performance may suffer, because as the table is
114 modified, indexes will accumulate dead tuples and the table itself
115 will accumulate dead line pointers that cannot be removed until
116 index cleanup is completed. This option has no effect for tables
117 that do not have an index and is ignored if the FULL option is
118 used.
119 TRUNCATE
121 Specifies that VACUUM should attempt to truncate off any empty
122 pages at the end of the table and allow the disk space for the
123 truncated pages to be returned to the operating system. This is
124 normally the desired behavior and is the default unless the
125 vacuum_truncate option has been set to false for the table to be
126 vacuumed. Setting this option to false may be useful to avoid
127 ACCESS EXCLUSIVE lock on the table that the truncation requires.
128 This option is ignored if the FULL option is used.
129 boolean
131 Specifies whether the selected option should be turned on or off.
132 You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF,
133 or 0 to disable it. The boolean value can also be omitted, in which
134 case TRUE is assumed.
135 table_name
137 The name (optionally schema-qualified) of a specific table or
138 materialized view to vacuum. If the specified table is a
139 partitioned table, all of its leaf partitions are vacuumed.
140 column_name
142 The name of a specific column to analyze. Defaults to all columns.
143 If a column list is specified, ANALYZE must also be specified.
144
146 When VERBOSE is specified, VACUUM emits progress messages to indicate
147 which table is currently being processed. Various statistics about the
148 tables are printed as well.
149
151 To vacuum a table, one must ordinarily be the table's owner or a
152 superuser. However, database owners are allowed to vacuum all tables in
153 their databases, except shared catalogs. (The restriction for shared
154 catalogs means that a true database-wide VACUUM can only be performed
155 by a superuser.) VACUUM will skip over any tables that the calling
156 user does not have permission to vacuum.
157 VACUUM cannot be executed inside a transaction block.
159 For tables with GIN indexes, VACUUM (in any form) also completes any
161 pending index insertions, by moving pending index entries to the
162 appropriate places in the main GIN index structure. See Section 66.4.1
163 for details.
164 We recommend that active production databases be vacuumed frequently
166 (at least nightly), in order to remove dead rows. After adding or
167 deleting a large number of rows, it might be a good idea to issue a
168 VACUUM ANALYZE command for the affected table. This will update the
169 system catalogs with the results of all recent changes, and allow the
170 PostgreSQL query planner to make better choices in planning queries.
171 The FULL option is not recommended for routine use, but might be useful
173 in special cases. An example is when you have deleted or updated most
174 of the rows in a table and would like the table to physically shrink to
175 occupy less disk space and allow faster table scans. VACUUM FULL will
176 usually shrink the table more than a plain VACUUM would.
177 VACUUM causes a substantial increase in I/O traffic, which might cause
179 poor performance for other active sessions. Therefore, it is sometimes
180 advisable to use the cost-based vacuum delay feature. See
181 Section 19.4.4 for details.
182 PostgreSQL includes an “autovacuum” facility which can automate routine
184 vacuum maintenance. For more information about automatic and manual
185 vacuuming, see Section 24.1.
186
188 To clean a single table onek, analyze it for the optimizer and print a
189 detailed vacuum activity report:
190 VACUUM (VERBOSE, ANALYZE) onek;
192
194 There is no VACUUM statement in the SQL standard.
195
197 vacuumdb(1), Section 19.4.4, Section 24.1.6
198PostgreSQL 12.6 2021 VACUUM(7)