Debian Codegraph @

doc/src/sgml/ref/vacuum.sgml

raw
1 <!--
2 doc/src/sgml/ref/vacuum.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-vacuum">
7 <indexterm zone="sql-vacuum">
8 <primary>VACUUM</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>VACUUM</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>VACUUM</refname>
19 <refpurpose>garbage-collect and optionally analyze a database</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
25
26 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
27
28 FULL [ <replaceable class="parameter">boolean</replaceable> ]
29 FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
30 VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
31 ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
32 DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
33 SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
34 INDEX_CLEANUP { AUTO | ON | OFF }
35 PROCESS_MAIN [ <replaceable class="parameter">boolean</replaceable> ]
36 PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
37 TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
38 PARALLEL <replaceable class="parameter">integer</replaceable>
39 SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
40 ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
41 BUFFER_USAGE_LIMIT <replaceable class="parameter">size</replaceable>
42
43 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
44
45 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
46 </synopsis>
47 </refsynopsisdiv>
48
49 <refsect1>
50 <title>Description</title>
51
52 <para>
53 <command>VACUUM</command> reclaims storage occupied by dead tuples.
54 In normal <productname>PostgreSQL</productname> operation, tuples that
55 are deleted or obsoleted by an update are not physically removed from
56 their table; they remain present until a <command>VACUUM</command> is
57 done. Therefore it's necessary to do <command>VACUUM</command>
58 periodically, especially on frequently-updated tables.
59 </para>
60
61 <para>
62 Without a <replaceable class="parameter">table_and_columns</replaceable>
63 list, <command>VACUUM</command> processes every table and materialized view
64 in the current database that the current user has permission to vacuum.
65 With a list, <command>VACUUM</command> processes only those table(s).
66 </para>
67
68 <para>
69 <command>VACUUM ANALYZE</command> performs a <command>VACUUM</command>
70 and then an <command>ANALYZE</command> for each selected table. This
71 is a handy combination form for routine maintenance scripts. See
72 <xref linkend="sql-analyze"/>
73 for more details about its processing.
74 </para>
75
76 <para>
77 Plain <command>VACUUM</command> (without <literal>FULL</literal>) simply reclaims
78 space and makes it
79 available for re-use. This form of the command can operate in parallel
80 with normal reading and writing of the table, as an exclusive lock
81 is not obtained. However, extra space is not returned to the operating
82 system (in most cases); it's just kept available for re-use within the
83 same table. It also allows us to leverage multiple CPUs in order to process
84 indexes. This feature is known as <firstterm>parallel vacuum</firstterm>.
85 To disable this feature, one can use <literal>PARALLEL</literal> option and
86 specify parallel workers as zero. <command>VACUUM FULL</command> rewrites
87 the entire contents of the table into a new disk file with no extra space,
88 allowing unused space to be returned to the operating system. This form is
89 much slower and requires an <literal>ACCESS EXCLUSIVE</literal> lock on
90 each table while it is being processed.
91 </para>
92 </refsect1>
93
94 <refsect1>
95 <title>Parameters</title>
96
97 <variablelist>
98 <varlistentry>
99 <term><literal>FULL</literal></term>
100 <listitem>
101 <para>
102 Selects <quote>full</quote> vacuum, which can reclaim more
103 space, but takes much longer and exclusively locks the table.
104 This method also requires extra disk space, since it writes a
105 new copy of the table and doesn't release the old copy until
106 the operation is complete. Usually this should only be used when a
107 significant amount of space needs to be reclaimed from within the table.
108 </para>
109 </listitem>
110 </varlistentry>
111
112 <varlistentry>
113 <term><literal>FREEZE</literal></term>
114 <listitem>
115 <para>
116 Selects aggressive <quote>freezing</quote> of tuples.
117 Specifying <literal>FREEZE</literal> is equivalent to performing
118 <command>VACUUM</command> with the
119 <xref linkend="guc-vacuum-freeze-min-age"/> and
120 <xref linkend="guc-vacuum-freeze-table-age"/> parameters
121 set to zero. Aggressive freezing is always performed when the
122 table is rewritten, so this option is redundant when <literal>FULL</literal>
123 is specified.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><literal>VERBOSE</literal></term>
130 <listitem>
131 <para>
132 Prints a detailed vacuum activity report for each table
133 at <literal>INFO</literal> level.
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><literal>ANALYZE</literal></term>
140 <listitem>
141 <para>
142 Updates statistics used by the planner to determine the most
143 efficient way to execute a query.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><literal>DISABLE_PAGE_SKIPPING</literal></term>
150 <listitem>
151 <para>
152 Normally, <command>VACUUM</command> will skip pages based on the <link
153 linkend="vacuum-for-visibility-map">visibility map</link>. Pages where
154 all tuples are known to be frozen can always be skipped, and those
155 where all tuples are known to be visible to all transactions may be
156 skipped except when performing an aggressive vacuum. Furthermore,
157 except when performing an aggressive vacuum, some pages may be skipped
158 in order to avoid waiting for other sessions to finish using them.
159 This option disables all page-skipping behavior, and is intended to
160 be used only when the contents of the visibility map are
161 suspect, which should happen only if there is a hardware or software
162 issue causing database corruption.
163 </para>
164 </listitem>
165 </varlistentry>
166
167 <varlistentry>
168 <term><literal>SKIP_LOCKED</literal></term>
169 <listitem>
170 <para>
171 Specifies that <command>VACUUM</command> should not wait for any
172 conflicting locks to be released when beginning work on a relation:
173 if a relation cannot be locked immediately without waiting, the relation
174 is skipped. Note that even with this option,
175 <command>VACUUM</command> may still block when opening the relation's
176 indexes. Additionally, <command>VACUUM ANALYZE</command> may still
177 block when acquiring sample rows from partitions, table inheritance
178 children, and some types of foreign tables. Also, while
179 <command>VACUUM</command> ordinarily processes all partitions of
180 specified partitioned tables, this option will cause
181 <command>VACUUM</command> to skip all partitions if there is a
182 conflicting lock on the partitioned table.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry>
188 <term><literal>INDEX_CLEANUP</literal></term>
189 <listitem>
190 <para>
191 Normally, <command>VACUUM</command> will skip index vacuuming
192 when there are very few dead tuples in the table. The cost of
193 processing all of the table's indexes is expected to greatly
194 exceed the benefit of removing dead index tuples when this
195 happens. This option can be used to force
196 <command>VACUUM</command> to process indexes when there are more
197 than zero dead tuples. The default is <literal>AUTO</literal>,
198 which allows <command>VACUUM</command> to skip index vacuuming
199 when appropriate. If <literal>INDEX_CLEANUP</literal> is set to
200 <literal>ON</literal>, <command>VACUUM</command> will
201 conservatively remove all dead tuples from indexes. This may be
202 useful for backwards compatibility with earlier releases of
203 <productname>PostgreSQL</productname> where this was the
204 standard behavior.
205 </para>
206 <para>
207 <literal>INDEX_CLEANUP</literal> can also be set to
208 <literal>OFF</literal> to force <command>VACUUM</command> to
209 <emphasis>always</emphasis> skip index vacuuming, even when
210 there are many dead tuples in the table. This may be useful
211 when it is necessary to make <command>VACUUM</command> run as
212 quickly as possible to avoid imminent transaction ID wraparound
213 (see <xref linkend="vacuum-for-wraparound"/>). However, the
214 wraparound failsafe mechanism controlled by <xref
215 linkend="guc-vacuum-failsafe-age"/> will generally trigger
216 automatically to avoid transaction ID wraparound failure, and
217 should be preferred. If index cleanup is not performed
218 regularly, performance may suffer, because as the table is
219 modified indexes will accumulate dead tuples and the table
220 itself will accumulate dead line pointers that cannot be removed
221 until index cleanup is completed.
222 </para>
223 <para>
224 This option has no effect for tables that have no index and is
225 ignored if the <literal>FULL</literal> option is used. It also
226 has no effect on the transaction ID wraparound failsafe
227 mechanism. When triggered it will skip index vacuuming, even
228 when <literal>INDEX_CLEANUP</literal> is set to
229 <literal>ON</literal>.
230 </para>
231 </listitem>
232 </varlistentry>
233
234 <varlistentry>
235 <term><literal>PROCESS_MAIN</literal></term>
236 <listitem>
237 <para>
238 Specifies that <command>VACUUM</command> should attempt to process the
239 main relation. This is usually the desired behavior and is the default.
240 Setting this option to false may be useful when it is only necessary to
241 vacuum a relation's corresponding <literal>TOAST</literal> table.
242 </para>
243 </listitem>
244 </varlistentry>
245
246 <varlistentry>
247 <term><literal>PROCESS_TOAST</literal></term>
248 <listitem>
249 <para>
250 Specifies that <command>VACUUM</command> should attempt to process the
251 corresponding <literal>TOAST</literal> table for each relation, if one
252 exists. This is usually the desired behavior and is the default.
253 Setting this option to false may be useful when it is only necessary to
254 vacuum the main relation. This option is required when the
255 <literal>FULL</literal> option is used.
256 </para>
257 </listitem>
258 </varlistentry>
259
260 <varlistentry>
261 <term><literal>TRUNCATE</literal></term>
262 <listitem>
263 <para>
264 Specifies that <command>VACUUM</command> should attempt to
265 truncate off any empty pages at the end of the table and allow
266 the disk space for the truncated pages to be returned to
267 the operating system. This is normally the desired behavior
268 and is the default unless <xref linkend="guc-vacuum-truncate"/>
269 is set to false or the <literal>vacuum_truncate</literal>
270 option has been set to false for the table to be vacuumed.
271 Setting this option to false may be useful to avoid
272 <literal>ACCESS EXCLUSIVE</literal> lock on the table that
273 the truncation requires. This option is ignored if the
274 <literal>FULL</literal> option is used.
275 </para>
276 </listitem>
277 </varlistentry>
278
279 <varlistentry>
280 <term><literal>PARALLEL</literal></term>
281 <listitem>
282 <para>
283 Perform index vacuum and index cleanup phases of <command>VACUUM</command>
284 in parallel using <replaceable class="parameter">integer</replaceable>
285 background workers (for the details of each vacuum phase, please
286 refer to <xref linkend="vacuum-phases"/>). The number of workers used
287 to perform the operation is equal to the number of indexes on the
288 relation that support parallel vacuum which is limited by the number of
289 workers specified with <literal>PARALLEL</literal> option if any which is
290 further limited by <xref linkend="guc-max-parallel-maintenance-workers"/>.
291 An index can participate in parallel vacuum if and only if the size of the
292 index is more than <xref linkend="guc-min-parallel-index-scan-size"/>.
293 Please note that it is not guaranteed that the number of parallel workers
294 specified in <replaceable class="parameter">integer</replaceable> will be
295 used during execution. It is possible for a vacuum to run with fewer
296 workers than specified, or even with no workers at all. Only one worker
297 can be used per index. So parallel workers are launched only when there
298 are at least <literal>2</literal> indexes in the table. Workers for
299 vacuum are launched before the start of each phase and exit at the end of
300 the phase. These behaviors might change in a future release. This
301 option can't be used with the <literal>FULL</literal> option.
302 </para>
303 </listitem>
304 </varlistentry>
305
306 <varlistentry>
307 <term><literal>SKIP_DATABASE_STATS</literal></term>
308 <listitem>
309 <para>
310 Specifies that <command>VACUUM</command> should skip updating the
311 database-wide statistics about oldest unfrozen XIDs. Normally
312 <command>VACUUM</command> will update these statistics once at the
313 end of the command. However, this can take awhile in a database
314 with a very large number of tables, and it will accomplish nothing
315 unless the table that had contained the oldest unfrozen XID was
316 among those vacuumed. Moreover, if multiple <command>VACUUM</command>
317 commands are issued in parallel, only one of them can update the
318 database-wide statistics at a time. Therefore, if an application
319 intends to issue a series of many <command>VACUUM</command>
320 commands, it can be helpful to set this option in all but the last
321 such command; or set it in all the commands and separately
322 issue <literal>VACUUM (ONLY_DATABASE_STATS)</literal> afterwards.
323 </para>
324 </listitem>
325 </varlistentry>
326
327 <varlistentry>
328 <term><literal>ONLY_DATABASE_STATS</literal></term>
329 <listitem>
330 <para>
331 Specifies that <command>VACUUM</command> should do nothing except
332 update the database-wide statistics about oldest unfrozen XIDs.
333 When this option is specified,
334 the <replaceable class="parameter">table_and_columns</replaceable>
335 list must be empty, and no other option may be enabled
336 except <literal>VERBOSE</literal>.
337 </para>
338 </listitem>
339 </varlistentry>
340
341 <varlistentry>
342 <term><literal>BUFFER_USAGE_LIMIT</literal></term>
343 <listitem>
344 <para>
345 Specifies the
346 <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
347 ring buffer size for <command>VACUUM</command>. This size is used to
348 calculate the number of shared buffers which will be reused as part of
349 this strategy. <literal>0</literal> disables use of a
350 <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
351 is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used
352 for both the vacuum and analyze stages. This option can't be used with
353 the <option>FULL</option> option except if <option>ANALYZE</option> is
354 also specified. When this option is not specified,
355 <command>VACUUM</command> uses the value from
356 <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
357 allow <command>VACUUM</command> to run more quickly, but having too
358 large a setting may cause too many other useful pages to be evicted from
359 shared buffers. The minimum value is <literal>128 kB</literal> and the
360 maximum value is <literal>16 GB</literal>.
361 </para>
362 </listitem>
363 </varlistentry>
364
365 <varlistentry>
366 <term><replaceable class="parameter">boolean</replaceable></term>
367 <listitem>
368 <para>
369 Specifies whether the selected option should be turned on or off.
370 You can write <literal>TRUE</literal>, <literal>ON</literal>, or
371 <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
372 <literal>OFF</literal>, or <literal>0</literal> to disable it. The
373 <replaceable class="parameter">boolean</replaceable> value can also
374 be omitted, in which case <literal>TRUE</literal> is assumed.
375 </para>
376 </listitem>
377 </varlistentry>
378
379 <varlistentry>
380 <term><replaceable class="parameter">integer</replaceable></term>
381 <listitem>
382 <para>
383 Specifies a non-negative integer value passed to the selected option.
384 </para>
385 </listitem>
386 </varlistentry>
387
388 <varlistentry>
389 <term><replaceable class="parameter">size</replaceable></term>
390 <listitem>
391 <para>
392 Specifies an amount of memory in kilobytes. Sizes may also be specified
393 as a string containing the numerical size followed by any one of the
394 following memory units: <literal>B</literal> (bytes),
395 <literal>kB</literal> (kilobytes), <literal>MB</literal> (megabytes),
396 <literal>GB</literal> (gigabytes), or <literal>TB</literal> (terabytes).
397 </para>
398 </listitem>
399 </varlistentry>
400
401 <varlistentry>
402 <term><replaceable class="parameter">table_name</replaceable></term>
403 <listitem>
404 <para>
405 The name (optionally schema-qualified) of a specific table or
406 materialized view to vacuum. If <literal>ONLY</literal> is specified
407 before the table name, only that table is vacuumed. If
408 <literal>ONLY</literal> is not specified, the table and all its
409 inheritance child tables or partitions (if any) are also vacuumed.
410 Optionally, <literal>*</literal> can be specified after the table name
411 to explicitly indicate that inheritance child tables (or partitions) are
412 to be vacuumed.
413 </para>
414 </listitem>
415 </varlistentry>
416
417 <varlistentry>
418 <term><replaceable class="parameter">column_name</replaceable></term>
419 <listitem>
420 <para>
421 The name of a specific column to analyze. Defaults to all columns.
422 If a column list is specified, <literal>ANALYZE</literal> must also be
423 specified.
424 </para>
425 </listitem>
426 </varlistentry>
427 </variablelist>
428 </refsect1>
429
430 <refsect1>
431 <title>Outputs</title>
432
433 <para>
434 When <literal>VERBOSE</literal> is specified, <command>VACUUM</command> emits
435 progress messages to indicate which table is currently being
436 processed. Various statistics about the tables are printed as well.
437 </para>
438 </refsect1>
439
440 <refsect1>
441 <title>Notes</title>
442
443 <para>
444 To vacuum a table, one must ordinarily have the <literal>MAINTAIN</literal>
445 privilege on the table. However, database owners are allowed to
446 vacuum all tables in their databases, except shared catalogs.
447 <command>VACUUM</command> will skip over any tables that the calling user
448 does not have permission to vacuum.
449 </para>
450
451 <para>
452 While <command>VACUUM</command> is running, the <xref
453 linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
454 pg_temp</literal>.
455 </para>
456
457 <para>
458 <command>VACUUM</command> cannot be executed inside a transaction block.
459 </para>
460
461 <para>
462 For tables with <acronym>GIN</acronym> indexes, <command>VACUUM</command> (in
463 any form) also completes any pending index insertions, by moving pending
464 index entries to the appropriate places in the main <acronym>GIN</acronym> index
465 structure. See <xref linkend="gin-fast-update"/> for details.
466 </para>
467
468 <para>
469 We recommend that all databases be vacuumed regularly in
470 order to remove dead rows. <productname>PostgreSQL</productname> includes
471 an <quote>autovacuum</quote> facility which can automate routine vacuum
472 maintenance. For more information about automatic and manual vacuuming,
473 see <xref linkend="routine-vacuuming"/>.
474 </para>
475
476 <para>
477 The <option>FULL</option> option is not recommended for routine use,
478 but might be useful in special cases. An example is when you have deleted
479 or updated most of the rows in a table and would like the table to
480 physically shrink to occupy less disk space and allow faster table
481 scans. <command>VACUUM FULL</command> will usually shrink the table
482 more than a plain <command>VACUUM</command> would.
483 </para>
484
485 <para>
486 The <option>PARALLEL</option> option is used only for vacuum purposes.
487 If this option is specified with the <option>ANALYZE</option> option,
488 it does not affect <option>ANALYZE</option>.
489 </para>
490
491 <para>
492 <command>VACUUM</command> causes a substantial increase in I/O traffic,
493 which might cause poor performance for other active sessions. Therefore,
494 it is sometimes advisable to use the cost-based vacuum delay feature. For
495 parallel vacuum, each worker sleeps in proportion to the work done by that
496 worker. See <xref linkend="runtime-config-resource-vacuum-cost"/> for
497 details.
498 </para>
499
500 <para>
501 Each backend running <command>VACUUM</command> without the
502 <literal>FULL</literal> option will report its progress in the
503 <structname>pg_stat_progress_vacuum</structname> view. Backends running
504 <command>VACUUM FULL</command> will instead report their progress in the
505 <structname>pg_stat_progress_cluster</structname> view. See
506 <xref linkend="vacuum-progress-reporting"/> and
507 <xref linkend="cluster-progress-reporting"/> for details.
508 </para>
509 </refsect1>
510
511 <refsect1>
512 <title>Examples</title>
513
514 <para>
515 To clean a single table <literal>onek</literal>, analyze it for
516 the optimizer and print a detailed vacuum activity report:
517
518 <programlisting>
519 VACUUM (VERBOSE, ANALYZE) onek;
520 </programlisting></para>
521 </refsect1>
522
523 <refsect1>
524 <title>Compatibility</title>
525
526 <para>
527 There is no <command>VACUUM</command> statement in the SQL standard.
528 </para>
529
530 <para>
531 The following syntax was used before <productname>PostgreSQL</productname>
532 version 9.0 and is still supported:
533 <synopsis>
534 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
535 </synopsis>
536 Note that in this syntax, the options must be specified in exactly the order
537 shown.
538 </para>
539 </refsect1>
540
541 <refsect1>
542 <title>See Also</title>
543
544 <simplelist type="inline">
545 <member><xref linkend="app-vacuumdb"/></member>
546 <member><xref linkend="runtime-config-resource-vacuum-cost"/></member>
547 <member><xref linkend="autovacuum"/></member>
548 <member><xref linkend="vacuum-progress-reporting"/></member>
549 <member><xref linkend="cluster-progress-reporting"/></member>
550 </simplelist>
551 </refsect1>
552 </refentry>