| 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> |