diff options
Diffstat (limited to 'doc/src/sgml/ref/vacuum.sgml')
| -rw-r--r-- | doc/src/sgml/ref/vacuum.sgml | 453 |
1 files changed, 453 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..c582021 --- /dev/null +++ b/doc/src/sgml/ref/vacuum.sgml @@ -0,0 +1,453 @@ +<!-- +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 { AUTO | ON | OFF } + PROCESS_TOAST [ <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> + Normally, <command>VACUUM</command> will skip index vacuuming + when there are very few dead tuples in the table. The cost of + processing all of the table's indexes is expected to greatly + exceed the benefit of removing dead index tuples when this + happens. This option can be used to force + <command>VACUUM</command> to process indexes when there are more + than zero dead tuples. The default is <literal>AUTO</literal>, + which allows <command>VACUUM</command> to skip index vacuuming + when appropriate. If <literal>INDEX_CLEANUP</literal> is set to + <literal>ON</literal>, <command>VACUUM</command> will + conservatively remove all dead tuples from indexes. This may be + useful for backwards compatibility with earlier releases of + <productname>PostgreSQL</productname> where this was the + standard behavior. + </para> + <para> + <literal>INDEX_CLEANUP</literal> can also be set to + <literal>OFF</literal> to force <command>VACUUM</command> to + <emphasis>always</emphasis> skip index vacuuming, even when + there are many dead tuples in the table. This may be useful + when it is necessary to make <command>VACUUM</command> run as + quickly as possible to avoid imminent transaction ID wraparound + (see <xref linkend="vacuum-for-wraparound"/>). However, the + wraparound failsafe mechanism controlled by <xref + linkend="guc-vacuum-failsafe-age"/> will generally trigger + automatically to avoid transaction ID wraparound failure, and + should be preferred. 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. + </para> + <para> + This option has no effect for tables that have no index and is + ignored if the <literal>FULL</literal> option is used. It also + has no effect on the transaction ID wraparound failsafe + mechanism. When triggered it will skip index vacuuming, even + when <literal>INDEX_CLEANUP</literal> is set to + <literal>ON</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PROCESS_TOAST</literal></term> + <listitem> + <para> + Specifies that <command>VACUUM</command> should attempt to process the + corresponding <literal>TOAST</literal> table for each relation, if one + exists. This is usually the desired behavior and is the default. + Setting this option to false may be useful when it is only necessary to + vacuum the main relation. This option is required when 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-maintenance-workers"/>. + 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 all databases be vacuumed regularly in + order to remove dead rows. <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> + + <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> + Each backend running <command>VACUUM</command> without the + <literal>FULL</literal> option will report its progress in the + <structname>pg_stat_progress_vacuum</structname> view. Backends running + <command>VACUUM FULL</command> will instead report their progress in the + <structname>pg_stat_progress_cluster</structname> view. See + <xref linkend="vacuum-progress-reporting"/> and + <xref linkend="cluster-progress-reporting"/> for details. + </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> + <member><xref linkend="vacuum-progress-reporting"/></member> + <member><xref linkend="cluster-progress-reporting"/></member> + </simplelist> + </refsect1> +</refentry> |