summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/ref/vacuum.sgml
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
commit6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/ref/vacuum.sgml
parentInitial commit. (diff)
downloadpostgresql-13-upstream.tar.xz
postgresql-13-upstream.zip
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/ref/vacuum.sgml')
-rw-r--r--doc/src/sgml/ref/vacuum.sgml412
1 files changed, 412 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
new file mode 100644
index 0000000..a837917
--- /dev/null
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -0,0 +1,412 @@
+<!--
+doc/src/sgml/ref/vacuum.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-vacuum">
+ <indexterm zone="sql-vacuum">
+ <primary>VACUUM</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>VACUUM</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>VACUUM</refname>
+ <refpurpose>garbage-collect and optionally analyze a database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+ FULL [ <replaceable class="parameter">boolean</replaceable> ]
+ FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
+ DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
+ SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+ TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
+ PARALLEL <replaceable class="parameter">integer</replaceable>
+
+<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
+
+ <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>VACUUM</command> reclaims storage occupied by dead tuples.
+ In normal <productname>PostgreSQL</productname> operation, tuples that
+ are deleted or obsoleted by an update are not physically removed from
+ their table; they remain present until a <command>VACUUM</command> is
+ done. Therefore it's necessary to do <command>VACUUM</command>
+ periodically, especially on frequently-updated tables.
+ </para>
+
+ <para>
+ Without a <replaceable class="parameter">table_and_columns</replaceable>
+ list, <command>VACUUM</command> processes every table and materialized view
+ in the current database that the current user has permission to vacuum.
+ With a list, <command>VACUUM</command> processes only those table(s).
+ </para>
+
+ <para>
+ <command>VACUUM ANALYZE</command> performs a <command>VACUUM</command>
+ and then an <command>ANALYZE</command> for each selected table. This
+ is a handy combination form for routine maintenance scripts. See
+ <xref linkend="sql-analyze"/>
+ for more details about its processing.
+ </para>
+
+ <para>
+ Plain <command>VACUUM</command> (without <literal>FULL</literal>) simply reclaims
+ space and makes it
+ available for re-use. This form of the command can operate in parallel
+ with normal reading and writing of the table, as an exclusive lock
+ is not obtained. However, extra space is not returned to the operating
+ system (in most cases); it's just kept available for re-use within the
+ same table. It also allows us to leverage multiple CPUs in order to process
+ indexes. This feature is known as <firstterm>parallel vacuum</firstterm>.
+ To disable this feature, one can use <literal>PARALLEL</literal> option and
+ specify parallel workers as zero. <command>VACUUM FULL</command> rewrites
+ the entire contents of the table into a new disk file with no extra space,
+ allowing unused space to be returned to the operating system. This form is
+ much slower and requires an <literal>ACCESS EXCLUSIVE</literal> lock on
+ each table while it is being processed.
+ </para>
+
+ <para>
+ When the option list is surrounded by parentheses, the options can be
+ written in any order. Without parentheses, options must be specified
+ in exactly the order shown above.
+ The parenthesized syntax was added in
+ <productname>PostgreSQL</productname> 9.0; the unparenthesized
+ syntax is deprecated.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>FULL</literal></term>
+ <listitem>
+ <para>
+ Selects <quote>full</quote> vacuum, which can reclaim more
+ space, but takes much longer and exclusively locks the table.
+ This method also requires extra disk space, since it writes a
+ new copy of the table and doesn't release the old copy until
+ the operation is complete. Usually this should only be used when a
+ significant amount of space needs to be reclaimed from within the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FREEZE</literal></term>
+ <listitem>
+ <para>
+ Selects aggressive <quote>freezing</quote> of tuples.
+ Specifying <literal>FREEZE</literal> is equivalent to performing
+ <command>VACUUM</command> with the
+ <xref linkend="guc-vacuum-freeze-min-age"/> and
+ <xref linkend="guc-vacuum-freeze-table-age"/> parameters
+ set to zero. Aggressive freezing is always performed when the
+ table is rewritten, so this option is redundant when <literal>FULL</literal>
+ is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>VERBOSE</literal></term>
+ <listitem>
+ <para>
+ Prints a detailed vacuum activity report for each table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ANALYZE</literal></term>
+ <listitem>
+ <para>
+ Updates statistics used by the planner to determine the most
+ efficient way to execute a query.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DISABLE_PAGE_SKIPPING</literal></term>
+ <listitem>
+ <para>
+ Normally, <command>VACUUM</command> will skip pages based on the <link
+ linkend="vacuum-for-visibility-map">visibility map</link>. Pages where
+ all tuples are known to be frozen can always be skipped, and those
+ where all tuples are known to be visible to all transactions may be
+ skipped except when performing an aggressive vacuum. Furthermore,
+ except when performing an aggressive vacuum, some pages may be skipped
+ in order to avoid waiting for other sessions to finish using them.
+ This option disables all page-skipping behavior, and is intended to
+ be used only when the contents of the visibility map are
+ suspect, which should happen only if there is a hardware or software
+ issue causing database corruption.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SKIP_LOCKED</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should not wait for any
+ conflicting locks to be released when beginning work on a relation:
+ if a relation cannot be locked immediately without waiting, the relation
+ is skipped. Note that even with this option,
+ <command>VACUUM</command> may still block when opening the relation's
+ indexes. Additionally, <command>VACUUM ANALYZE</command> may still
+ block when acquiring sample rows from partitions, table inheritance
+ children, and some types of foreign tables. Also, while
+ <command>VACUUM</command> ordinarily processes all partitions of
+ specified partitioned tables, this option will cause
+ <command>VACUUM</command> to skip all partitions if there is a
+ conflicting lock on the partitioned table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INDEX_CLEANUP</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should attempt to remove
+ index entries pointing to dead tuples. This is normally the desired
+ behavior and is the default unless the
+ <literal>vacuum_index_cleanup</literal> option has been set to false
+ for the table to be vacuumed. Setting this option to false may be
+ useful when it is necessary to make vacuum run as quickly as possible,
+ for example to avoid imminent transaction ID wraparound
+ (see <xref linkend="vacuum-for-wraparound"/>). However, if index
+ cleanup is not performed regularly, performance may suffer, because
+ as the table is modified, indexes will accumulate dead tuples
+ and the table itself will accumulate dead line pointers that cannot be
+ removed until index cleanup is completed. This option has no effect
+ for tables that do not have an index and is ignored if the
+ <literal>FULL</literal> option is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TRUNCATE</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should attempt to
+ truncate off any empty pages at the end of the table and allow
+ the disk space for the truncated pages to be returned to
+ the operating system. This is normally the desired behavior
+ and is the default unless the <literal>vacuum_truncate</literal>
+ option has been set to false for the table to be vacuumed.
+ Setting this option to false may be useful to avoid
+ <literal>ACCESS EXCLUSIVE</literal> lock on the table that
+ the truncation requires. This option is ignored if the
+ <literal>FULL</literal> option is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>PARALLEL</literal></term>
+ <listitem>
+ <para>
+ Perform index vacuum and index cleanup phases of <command>VACUUM</command>
+ in parallel using <replaceable class="parameter">integer</replaceable>
+ background workers (for the details of each vacuum phase, please
+ refer to <xref linkend="vacuum-phases"/>). The number of workers used
+ to perform the operation is equal to the number of indexes on the
+ relation that support parallel vacuum which is limited by the number of
+ workers specified with <literal>PARALLEL</literal> option if any which is
+ further limited by <xref linkend="guc-max-parallel-workers-maintenance"/>.
+ An index can participate in parallel vacuum if and only if the size of the
+ index is more than <xref linkend="guc-min-parallel-index-scan-size"/>.
+ Please note that it is not guaranteed that the number of parallel workers
+ specified in <replaceable class="parameter">integer</replaceable> will be
+ used during execution. It is possible for a vacuum to run with fewer
+ workers than specified, or even with no workers at all. Only one worker
+ can be used per index. So parallel workers are launched only when there
+ are at least <literal>2</literal> indexes in the table. Workers for
+ vacuum are launched before the start of each phase and exit at the end of
+ the phase. These behaviors might change in a future release. This
+ option can't be used with the <literal>FULL</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
+ <listitem>
+ <para>
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">integer</replaceable></term>
+ <listitem>
+ <para>
+ Specifies a non-negative integer value passed to the selected option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">table_name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of a specific table or
+ materialized view to vacuum. If the specified table is a partitioned
+ table, all of its leaf partitions are vacuumed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">column_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific column to analyze. Defaults to all columns.
+ If a column list is specified, <literal>ANALYZE</literal> must also be
+ specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Outputs</title>
+
+ <para>
+ When <literal>VERBOSE</literal> is specified, <command>VACUUM</command> emits
+ progress messages to indicate which table is currently being
+ processed. Various statistics about the tables are printed as well.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ To vacuum a table, one must ordinarily be the table's owner or a
+ superuser. However, database owners are allowed to
+ vacuum all tables in their databases, except shared catalogs.
+ (The restriction for shared catalogs means that a true database-wide
+ <command>VACUUM</command> can only be performed by a superuser.)
+ <command>VACUUM</command> will skip over any tables that the calling user
+ does not have permission to vacuum.
+ </para>
+
+ <para>
+ <command>VACUUM</command> cannot be executed inside a transaction block.
+ </para>
+
+ <para>
+ For tables with <acronym>GIN</acronym> indexes, <command>VACUUM</command> (in
+ any form) also completes any pending index insertions, by moving pending
+ index entries to the appropriate places in the main <acronym>GIN</acronym> index
+ structure. See <xref linkend="gin-fast-update"/> for details.
+ </para>
+
+ <para>
+ We recommend that active production databases be
+ vacuumed frequently (at least nightly), in order to
+ remove dead rows. After adding or deleting a large number
+ of rows, it might be a good idea to issue a <command>VACUUM
+ ANALYZE</command> command for the affected table. This will update the
+ system catalogs with
+ the results of all recent changes, and allow the
+ <productname>PostgreSQL</productname> query planner to make better
+ choices in planning queries.
+ </para>
+
+ <para>
+ The <option>FULL</option> option is not recommended for routine use,
+ but might be useful in special cases. An example is when you have deleted
+ or updated most of the rows in a table and would like the table to
+ physically shrink to occupy less disk space and allow faster table
+ scans. <command>VACUUM FULL</command> will usually shrink the table
+ more than a plain <command>VACUUM</command> would.
+ </para>
+
+ <para>
+ The <option>PARALLEL</option> option is used only for vacuum purposes.
+ If this option is specified with the <option>ANALYZE</option> option,
+ it does not affect <option>ANALYZE</option>.
+ </para>
+
+ <para>
+ <command>VACUUM</command> causes a substantial increase in I/O traffic,
+ which might cause poor performance for other active sessions. Therefore,
+ it is sometimes advisable to use the cost-based vacuum delay feature. For
+ parallel vacuum, each worker sleeps in proportion to the work done by that
+ worker. See <xref linkend="runtime-config-resource-vacuum-cost"/> for
+ details.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> includes an <quote>autovacuum</quote>
+ facility which can automate routine vacuum maintenance. For more
+ information about automatic and manual vacuuming, see
+ <xref linkend="routine-vacuuming"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To clean a single table <literal>onek</literal>, analyze it for
+ the optimizer and print a detailed vacuum activity report:
+
+<programlisting>
+VACUUM (VERBOSE, ANALYZE) onek;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ There is no <command>VACUUM</command> statement in the SQL standard.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="app-vacuumdb"/></member>
+ <member><xref linkend="runtime-config-resource-vacuum-cost"/></member>
+ <member><xref linkend="autovacuum"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>