diff options
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/html/sql-vacuum.html | 267 |
1 files changed, 267 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-vacuum.html b/doc/src/sgml/html/sql-vacuum.html new file mode 100644 index 0000000..e2bfd83 --- /dev/null +++ b/doc/src/sgml/html/sql-vacuum.html @@ -0,0 +1,267 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>VACUUM</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-update.html" title="UPDATE" /><link rel="next" href="sql-values.html" title="VALUES" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">VACUUM</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-update.html" title="UPDATE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-values.html" title="VALUES">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-VACUUM"><div class="titlepage"></div><a id="id-1.9.3.183.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">VACUUM</span></h2><p>VACUUM — garbage-collect and optionally analyze a database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +VACUUM [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ] + +<span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span> + + FULL [ <em class="replaceable"><code>boolean</code></em> ] + FREEZE [ <em class="replaceable"><code>boolean</code></em> ] + VERBOSE [ <em class="replaceable"><code>boolean</code></em> ] + ANALYZE [ <em class="replaceable"><code>boolean</code></em> ] + DISABLE_PAGE_SKIPPING [ <em class="replaceable"><code>boolean</code></em> ] + SKIP_LOCKED [ <em class="replaceable"><code>boolean</code></em> ] + INDEX_CLEANUP { AUTO | ON | OFF } + PROCESS_TOAST [ <em class="replaceable"><code>boolean</code></em> ] + TRUNCATE [ <em class="replaceable"><code>boolean</code></em> ] + PARALLEL <em class="replaceable"><code>integer</code></em> + +<span class="phrase">and <em class="replaceable"><code>table_and_columns</code></em> is:</span> + + <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] +</pre></div><div class="refsect1" id="id-1.9.3.183.5"><h2>Description</h2><p> + <code class="command">VACUUM</code> reclaims storage occupied by dead tuples. + In normal <span class="productname">PostgreSQL</span> operation, tuples that + are deleted or obsoleted by an update are not physically removed from + their table; they remain present until a <code class="command">VACUUM</code> is + done. Therefore it's necessary to do <code class="command">VACUUM</code> + periodically, especially on frequently-updated tables. + </p><p> + Without a <em class="replaceable"><code>table_and_columns</code></em> + list, <code class="command">VACUUM</code> processes every table and materialized view + in the current database that the current user has permission to vacuum. + With a list, <code class="command">VACUUM</code> processes only those table(s). + </p><p> + <code class="command">VACUUM ANALYZE</code> performs a <code class="command">VACUUM</code> + and then an <code class="command">ANALYZE</code> for each selected table. This + is a handy combination form for routine maintenance scripts. See + <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> + for more details about its processing. + </p><p> + Plain <code class="command">VACUUM</code> (without <code class="literal">FULL</code>) 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 <em class="firstterm">parallel vacuum</em>. + To disable this feature, one can use <code class="literal">PARALLEL</code> option and + specify parallel workers as zero. <code class="command">VACUUM FULL</code> 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 <code class="literal">ACCESS EXCLUSIVE</code> lock on + each table while it is being processed. + </p><p> + 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 + <span class="productname">PostgreSQL</span> 9.0; the unparenthesized + syntax is deprecated. + </p></div><div class="refsect1" id="id-1.9.3.183.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">FULL</code></span></dt><dd><p> + Selects <span class="quote">“<span class="quote">full</span>”</span> 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. + </p></dd><dt><span class="term"><code class="literal">FREEZE</code></span></dt><dd><p> + Selects aggressive <span class="quote">“<span class="quote">freezing</span>”</span> of tuples. + Specifying <code class="literal">FREEZE</code> is equivalent to performing + <code class="command">VACUUM</code> with the + <a class="xref" href="runtime-config-client.html#GUC-VACUUM-FREEZE-MIN-AGE">vacuum_freeze_min_age</a> and + <a class="xref" href="runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE">vacuum_freeze_table_age</a> parameters + set to zero. Aggressive freezing is always performed when the + table is rewritten, so this option is redundant when <code class="literal">FULL</code> + is specified. + </p></dd><dt><span class="term"><code class="literal">VERBOSE</code></span></dt><dd><p> + Prints a detailed vacuum activity report for each table. + </p></dd><dt><span class="term"><code class="literal">ANALYZE</code></span></dt><dd><p> + Updates statistics used by the planner to determine the most + efficient way to execute a query. + </p></dd><dt><span class="term"><code class="literal">DISABLE_PAGE_SKIPPING</code></span></dt><dd><p> + Normally, <code class="command">VACUUM</code> will skip pages based on the <a class="link" href="routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP" title="25.1.4. Updating the Visibility Map">visibility map</a>. 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. + </p></dd><dt><span class="term"><code class="literal">SKIP_LOCKED</code></span></dt><dd><p> + Specifies that <code class="command">VACUUM</code> 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, + <code class="command">VACUUM</code> may still block when opening the relation's + indexes. Additionally, <code class="command">VACUUM ANALYZE</code> may still + block when acquiring sample rows from partitions, table inheritance + children, and some types of foreign tables. Also, while + <code class="command">VACUUM</code> ordinarily processes all partitions of + specified partitioned tables, this option will cause + <code class="command">VACUUM</code> to skip all partitions if there is a + conflicting lock on the partitioned table. + </p></dd><dt><span class="term"><code class="literal">INDEX_CLEANUP</code></span></dt><dd><p> + Normally, <code class="command">VACUUM</code> 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 + <code class="command">VACUUM</code> to process indexes when there are more + than zero dead tuples. The default is <code class="literal">AUTO</code>, + which allows <code class="command">VACUUM</code> to skip index vacuuming + when appropriate. If <code class="literal">INDEX_CLEANUP</code> is set to + <code class="literal">ON</code>, <code class="command">VACUUM</code> will + conservatively remove all dead tuples from indexes. This may be + useful for backwards compatibility with earlier releases of + <span class="productname">PostgreSQL</span> where this was the + standard behavior. + </p><p> + <code class="literal">INDEX_CLEANUP</code> can also be set to + <code class="literal">OFF</code> to force <code class="command">VACUUM</code> to + <span class="emphasis"><em>always</em></span> skip index vacuuming, even when + there are many dead tuples in the table. This may be useful + when it is necessary to make <code class="command">VACUUM</code> run as + quickly as possible to avoid imminent transaction ID wraparound + (see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="25.1.5. Preventing Transaction ID Wraparound Failures">Section 25.1.5</a>). However, the + wraparound failsafe mechanism controlled by <a class="xref" href="runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE">vacuum_failsafe_age</a> 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. + </p><p> + This option has no effect for tables that have no index and is + ignored if the <code class="literal">FULL</code> option is used. It also + has no effect on the transaction ID wraparound failsafe + mechanism. When triggered it will skip index vacuuming, even + when <code class="literal">INDEX_CLEANUP</code> is set to + <code class="literal">ON</code>. + </p></dd><dt><span class="term"><code class="literal">PROCESS_TOAST</code></span></dt><dd><p> + Specifies that <code class="command">VACUUM</code> should attempt to process the + corresponding <code class="literal">TOAST</code> 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 + <code class="literal">FULL</code> option is used. + </p></dd><dt><span class="term"><code class="literal">TRUNCATE</code></span></dt><dd><p> + Specifies that <code class="command">VACUUM</code> 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 <code class="literal">vacuum_truncate</code> + option has been set to false for the table to be vacuumed. + Setting this option to false may be useful to avoid + <code class="literal">ACCESS EXCLUSIVE</code> lock on the table that + the truncation requires. This option is ignored if the + <code class="literal">FULL</code> option is used. + </p></dd><dt><span class="term"><code class="literal">PARALLEL</code></span></dt><dd><p> + Perform index vacuum and index cleanup phases of <code class="command">VACUUM</code> + in parallel using <em class="replaceable"><code>integer</code></em> + background workers (for the details of each vacuum phase, please + refer to <a class="xref" href="progress-reporting.html#VACUUM-PHASES" title="Table 28.39. VACUUM Phases">Table 28.39</a>). 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 <code class="literal">PARALLEL</code> option if any which is + further limited by <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a>. + An index can participate in parallel vacuum if and only if the size of the + index is more than <a class="xref" href="runtime-config-query.html#GUC-MIN-PARALLEL-INDEX-SCAN-SIZE">min_parallel_index_scan_size</a>. + Please note that it is not guaranteed that the number of parallel workers + specified in <em class="replaceable"><code>integer</code></em> 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 <code class="literal">2</code> 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 <code class="literal">FULL</code> option. + </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p> + Specifies whether the selected option should be turned on or off. + You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or + <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>, + <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it. The + <em class="replaceable"><code>boolean</code></em> value can also + be omitted, in which case <code class="literal">TRUE</code> is assumed. + </p></dd><dt><span class="term"><em class="replaceable"><code>integer</code></em></span></dt><dd><p> + Specifies a non-negative integer value passed to the selected option. + </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p> + 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. + </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p> + The name of a specific column to analyze. Defaults to all columns. + If a column list is specified, <code class="literal">ANALYZE</code> must also be + specified. + </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.183.7"><h2>Outputs</h2><p> + When <code class="literal">VERBOSE</code> is specified, <code class="command">VACUUM</code> emits + progress messages to indicate which table is currently being + processed. Various statistics about the tables are printed as well. + </p></div><div class="refsect1" id="id-1.9.3.183.8"><h2>Notes</h2><p> + 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 + <code class="command">VACUUM</code> can only be performed by a superuser.) + <code class="command">VACUUM</code> will skip over any tables that the calling user + does not have permission to vacuum. + </p><p> + <code class="command">VACUUM</code> cannot be executed inside a transaction block. + </p><p> + For tables with <acronym class="acronym">GIN</acronym> indexes, <code class="command">VACUUM</code> (in + any form) also completes any pending index insertions, by moving pending + index entries to the appropriate places in the main <acronym class="acronym">GIN</acronym> index + structure. See <a class="xref" href="gin-implementation.html#GIN-FAST-UPDATE" title="67.4.1. GIN Fast Update Technique">Section 67.4.1</a> for details. + </p><p> + 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 <code class="command">VACUUM + ANALYZE</code> command for the affected table. This will update the + system catalogs with + the results of all recent changes, and allow the + <span class="productname">PostgreSQL</span> query planner to make better + choices in planning queries. + </p><p> + The <code class="option">FULL</code> 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. <code class="command">VACUUM FULL</code> will usually shrink the table + more than a plain <code class="command">VACUUM</code> would. + </p><p> + The <code class="option">PARALLEL</code> option is used only for vacuum purposes. + If this option is specified with the <code class="option">ANALYZE</code> option, + it does not affect <code class="option">ANALYZE</code>. + </p><p> + <code class="command">VACUUM</code> 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 <a class="xref" href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="20.4.4. Cost-based Vacuum Delay">Section 20.4.4</a> for + details. + </p><p> + <span class="productname">PostgreSQL</span> includes an <span class="quote">“<span class="quote">autovacuum</span>”</span> + facility which can automate routine vacuum maintenance. For more + information about automatic and manual vacuuming, see + <a class="xref" href="routine-vacuuming.html" title="25.1. Routine Vacuuming">Section 25.1</a>. + </p><p> + Each backend running <code class="command">VACUUM</code> without the + <code class="literal">FULL</code> option will report its progress in the + <code class="structname">pg_stat_progress_vacuum</code> view. Backends running + <code class="command">VACUUM FULL</code> will instead report their progress in the + <code class="structname">pg_stat_progress_cluster</code> view. See + <a class="xref" href="progress-reporting.html#VACUUM-PROGRESS-REPORTING" title="28.4.3. VACUUM Progress Reporting">Section 28.4.3</a> and + <a class="xref" href="progress-reporting.html#CLUSTER-PROGRESS-REPORTING" title="28.4.4. CLUSTER Progress Reporting">Section 28.4.4</a> for details. + </p></div><div class="refsect1" id="id-1.9.3.183.9"><h2>Examples</h2><p> + To clean a single table <code class="literal">onek</code>, analyze it for + the optimizer and print a detailed vacuum activity report: + +</p><pre class="programlisting"> +VACUUM (VERBOSE, ANALYZE) onek; +</pre></div><div class="refsect1" id="id-1.9.3.183.10"><h2>Compatibility</h2><p> + There is no <code class="command">VACUUM</code> statement in the SQL standard. + </p></div><div class="refsect1" id="id-1.9.3.183.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="app-vacuumdb.html" title="vacuumdb"><span class="refentrytitle"><span class="application">vacuumdb</span></span></a>, <a class="xref" href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="20.4.4. Cost-based Vacuum Delay">Section 20.4.4</a>, <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a>, <a class="xref" href="progress-reporting.html#VACUUM-PROGRESS-REPORTING" title="28.4.3. VACUUM Progress Reporting">Section 28.4.3</a>, <a class="xref" href="progress-reporting.html#CLUSTER-PROGRESS-REPORTING" title="28.4.4. CLUSTER Progress Reporting">Section 28.4.4</a></span></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-update.html" title="UPDATE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-values.html" title="VALUES">Next</a></td></tr><tr><td width="40%" align="left" valign="top">UPDATE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> VALUES</td></tr></table></div></body></html>
\ No newline at end of file |