1VACUUM(7) PostgreSQL 16.1 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 { AUTO | ON | OFF }
21 PROCESS_MAIN [ boolean ]
22 PROCESS_TOAST [ boolean ]
23 TRUNCATE [ boolean ]
24 PARALLEL integer
25 SKIP_DATABASE_STATS [ boolean ]
26 ONLY_DATABASE_STATS [ boolean ]
27 BUFFER_USAGE_LIMIT size
28 and table_and_columns is:
30 table_name [ ( column_name [, ...] ) ]
32
34 VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL
35 operation, tuples that are deleted or obsoleted by an update are not
36 physically removed from their table; they remain present until a VACUUM
37 is done. Therefore it's necessary to do VACUUM periodically, especially
38 on frequently-updated tables.
39 Without a table_and_columns list, VACUUM processes every table and
41 materialized view in the current database that the current user has
42 permission to vacuum. With a list, VACUUM processes only those
43 table(s).
44 VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
46 table. This is a handy combination form for routine maintenance
47 scripts. See ANALYZE(7) for more details about its processing.
48 Plain VACUUM (without FULL) simply reclaims space and makes it
50 available for re-use. This form of the command can operate in parallel
51 with normal reading and writing of the table, as an exclusive lock is
52 not obtained. However, extra space is not returned to the operating
53 system (in most cases); it's just kept available for re-use within the
54 same table. It also allows us to leverage multiple CPUs in order to
55 process indexes. This feature is known as parallel vacuum. To disable
56 this feature, one can use PARALLEL option and specify parallel workers
57 as zero. VACUUM FULL rewrites the entire contents of the table into a
58 new disk file with no extra space, allowing unused space to be returned
59 to the operating system. This form is much slower and requires an
60 ACCESS EXCLUSIVE lock on each table while it is being processed.
61 When the option list is surrounded by parentheses, the options can be
63 written in any order. Without parentheses, options must be specified in
64 exactly the order shown above. The parenthesized syntax was added in
65 PostgreSQL 9.0; the unparenthesized syntax is deprecated.
66
68 FULL
69 Selects “full” vacuum, which can reclaim more space, but takes much
70 longer and exclusively locks the table. This method also requires
71 extra disk space, since it writes a new copy of the table and
72 doesn't release the old copy until the operation is complete.
73 Usually this should only be used when a significant amount of space
74 needs to be reclaimed from within the table.
75 FREEZE
77 Selects aggressive “freezing” of tuples. Specifying FREEZE is
78 equivalent to performing VACUUM with the vacuum_freeze_min_age and
79 vacuum_freeze_table_age parameters set to zero. Aggressive freezing
80 is always performed when the table is rewritten, so this option is
81 redundant when FULL is specified.
82 VERBOSE
84 Prints a detailed vacuum activity report for each table.
85 ANALYZE
87 Updates statistics used by the planner to determine the most
88 efficient way to execute a query.
89 DISABLE_PAGE_SKIPPING
91 Normally, VACUUM will skip pages based on the visibility map. Pages
92 where all tuples are known to be frozen can always be skipped, and
93 those where all tuples are known to be visible to all transactions
94 may be skipped except when performing an aggressive vacuum.
95 Furthermore, except when performing an aggressive vacuum, some
96 pages may be skipped in order to avoid waiting for other sessions
97 to finish using them. This option disables all page-skipping
98 behavior, and is intended to be used only when the contents of the
99 visibility map are suspect, which should happen only if there is a
100 hardware or software issue causing database corruption.
101 SKIP_LOCKED
103 Specifies that VACUUM should not wait for any conflicting locks to
104 be released when beginning work on a relation: if a relation cannot
105 be locked immediately without waiting, the relation is skipped.
106 Note that even with this option, VACUUM may still block when
107 opening the relation's indexes. Additionally, VACUUM ANALYZE may
108 still block when acquiring sample rows from partitions, table
109 inheritance children, and some types of foreign tables. Also, while
110 VACUUM ordinarily processes all partitions of specified partitioned
111 tables, this option will cause VACUUM to skip all partitions if
112 there is a conflicting lock on the partitioned table.
113 INDEX_CLEANUP
115 Normally, VACUUM will skip index vacuuming when there are very few
116 dead tuples in the table. The cost of processing all of the table's
117 indexes is expected to greatly exceed the benefit of removing dead
118 index tuples when this happens. This option can be used to force
119 VACUUM to process indexes when there are more than zero dead
120 tuples. The default is AUTO, which allows VACUUM to skip index
121 vacuuming when appropriate. If INDEX_CLEANUP is set to ON, VACUUM
122 will conservatively remove all dead tuples from indexes. This may
123 be useful for backwards compatibility with earlier releases of
124 PostgreSQL where this was the standard behavior.
125 INDEX_CLEANUP can also be set to OFF to force VACUUM to always skip
127 index vacuuming, even when there are many dead tuples in the table.
128 This may be useful when it is necessary to make VACUUM run as
129 quickly as possible to avoid imminent transaction ID wraparound
130 (see Section 25.1.5). However, the wraparound failsafe mechanism
131 controlled by vacuum_failsafe_age will generally trigger
132 automatically to avoid transaction ID wraparound failure, and
133 should be preferred. If index cleanup is not performed regularly,
134 performance may suffer, because as the table is modified indexes
135 will accumulate dead tuples and the table itself will accumulate
136 dead line pointers that cannot be removed until index cleanup is
137 completed.
138 This option has no effect for tables that have no index and is
140 ignored if the FULL option is used. It also has no effect on the
141 transaction ID wraparound failsafe mechanism. When triggered it
142 will skip index vacuuming, even when INDEX_CLEANUP is set to ON.
143 PROCESS_MAIN
145 Specifies that VACUUM should attempt to process the main relation.
146 This is usually the desired behavior and is the default. Setting
147 this option to false may be useful when it is only necessary to
148 vacuum a relation's corresponding TOAST table.
149 PROCESS_TOAST
151 Specifies that VACUUM should attempt to process the corresponding
152 TOAST table for each relation, if one exists. This is usually the
153 desired behavior and is the default. Setting this option to false
154 may be useful when it is only necessary to vacuum the main
155 relation. This option is required when the FULL option is used.
156 TRUNCATE
158 Specifies that VACUUM should attempt to truncate off any empty
159 pages at the end of the table and allow the disk space for the
160 truncated pages to be returned to the operating system. This is
161 normally the desired behavior and is the default unless the
162 vacuum_truncate option has been set to false for the table to be
163 vacuumed. Setting this option to false may be useful to avoid
164 ACCESS EXCLUSIVE lock on the table that the truncation requires.
165 This option is ignored if the FULL option is used.
166 PARALLEL
168 Perform index vacuum and index cleanup phases of VACUUM in parallel
169 using integer background workers (for the details of each vacuum
170 phase, please refer to Table 28.45). The number of workers used to
171 perform the operation is equal to the number of indexes on the
172 relation that support parallel vacuum which is limited by the
173 number of workers specified with PARALLEL option if any which is
174 further limited by max_parallel_maintenance_workers. An index can
175 participate in parallel vacuum if and only if the size of the index
176 is more than min_parallel_index_scan_size. Please note that it is
177 not guaranteed that the number of parallel workers specified in
178 integer will be used during execution. It is possible for a vacuum
179 to run with fewer workers than specified, or even with no workers
180 at all. Only one worker can be used per index. So parallel workers
181 are launched only when there are at least 2 indexes in the table.
182 Workers for vacuum are launched before the start of each phase and
183 exit at the end of the phase. These behaviors might change in a
184 future release. This option can't be used with the FULL option.
185 SKIP_DATABASE_STATS
187 Specifies that VACUUM should skip updating the database-wide
188 statistics about oldest unfrozen XIDs. Normally VACUUM will update
189 these statistics once at the end of the command. However, this can
190 take awhile in a database with a very large number of tables, and
191 it will accomplish nothing unless the table that had contained the
192 oldest unfrozen XID was among those vacuumed. Moreover, if multiple
193 VACUUM commands are issued in parallel, only one of them can update
194 the database-wide statistics at a time. Therefore, if an
195 application intends to issue a series of many VACUUM commands, it
196 can be helpful to set this option in all but the last such command;
197 or set it in all the commands and separately issue VACUUM
198 (ONLY_DATABASE_STATS) afterwards.
199 ONLY_DATABASE_STATS
201 Specifies that VACUUM should do nothing except update the
202 database-wide statistics about oldest unfrozen XIDs. When this
203 option is specified, the table_and_columns list must be empty, and
204 no other option may be enabled except VERBOSE.
205 BUFFER_USAGE_LIMIT
207 Specifies the Buffer Access Strategy ring buffer size for VACUUM.
208 This size is used to calculate the number of shared buffers which
209 will be reused as part of this strategy. 0 disables use of a
210 Buffer Access Strategy. If ANALYZE is also specified, the
211 BUFFER_USAGE_LIMIT value is used for both the vacuum and analyze
212 stages. This option can't be used with the FULL option except if
213 ANALYZE is also specified. When this option is not specified,
214 VACUUM uses the value from vacuum_buffer_usage_limit. Higher
215 settings can allow VACUUM to run more quickly, but having too large
216 a setting may cause too many other useful pages to be evicted from
217 shared buffers. The minimum value is 128 kB and the maximum value
218 is 16 GB.
219 boolean
221 Specifies whether the selected option should be turned on or off.
222 You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF,
223 or 0 to disable it. The boolean value can also be omitted, in which
224 case TRUE is assumed.
225 integer
227 Specifies a non-negative integer value passed to the selected
228 option.
229 size
231 Specifies an amount of memory in kilobytes. Sizes may also be
232 specified as a string containing the numerical size followed by any
233 one of the following memory units: B (bytes), kB (kilobytes), MB
234 (megabytes), GB (gigabytes), or TB (terabytes).
235 table_name
237 The name (optionally schema-qualified) of a specific table or
238 materialized view to vacuum. If the specified table is a
239 partitioned table, all of its leaf partitions are vacuumed.
240 column_name
242 The name of a specific column to analyze. Defaults to all columns.
243 If a column list is specified, ANALYZE must also be specified.
244
246 When VERBOSE is specified, VACUUM emits progress messages to indicate
247 which table is currently being processed. Various statistics about the
248 tables are printed as well.
249
251 To vacuum a table, one must ordinarily be the table's owner or a
252 superuser. However, database owners are allowed to vacuum all tables in
253 their databases, except shared catalogs. (The restriction for shared
254 catalogs means that a true database-wide VACUUM can only be performed
255 by a superuser.) VACUUM will skip over any tables that the calling
256 user does not have permission to vacuum.
257 VACUUM cannot be executed inside a transaction block.
259 For tables with GIN indexes, VACUUM (in any form) also completes any
261 pending index insertions, by moving pending index entries to the
262 appropriate places in the main GIN index structure. See Section 70.4.1
263 for details.
264 We recommend that all databases be vacuumed regularly in order to
266 remove dead rows. PostgreSQL includes an “autovacuum” facility which
267 can automate routine vacuum maintenance. For more information about
268 automatic and manual vacuuming, see Section 25.1.
269 The FULL option is not recommended for routine use, but might be useful
271 in special cases. An example is when you have deleted or updated most
272 of the rows in a table and would like the table to physically shrink to
273 occupy less disk space and allow faster table scans. VACUUM FULL will
274 usually shrink the table more than a plain VACUUM would.
275 The PARALLEL option is used only for vacuum purposes. If this option is
277 specified with the ANALYZE option, it does not affect ANALYZE.
278 VACUUM causes a substantial increase in I/O traffic, which might cause
280 poor performance for other active sessions. Therefore, it is sometimes
281 advisable to use the cost-based vacuum delay feature. For parallel
282 vacuum, each worker sleeps in proportion to the work done by that
283 worker. See Section 20.4.4 for details.
284 Each backend running VACUUM without the FULL option will report its
286 progress in the pg_stat_progress_vacuum view. Backends running VACUUM
287 FULL will instead report their progress in the pg_stat_progress_cluster
288 view. See Section 28.4.5 and Section 28.4.2 for details.
289
291 To clean a single table onek, analyze it for the optimizer and print a
292 detailed vacuum activity report:
293 VACUUM (VERBOSE, ANALYZE) onek;
295
297 There is no VACUUM statement in the SQL standard.
298
300 vacuumdb(1), Section 20.4.4, Section 25.1.6, Section 28.4.5,
301 Section 28.4.2
302PostgreSQL 16.1 2023 VACUUM(7)