| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.55 2009/03/24 20:17:08 tgl Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-VACUUM"> |
| <refmeta> |
| <refentrytitle id="sql-vacuum-title">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> |
| |
| <indexterm zone="sql-vacuum"> |
| <primary>VACUUM</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> ] |
| VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</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> |
| With no parameter, <command>VACUUM</command> processes every table in the |
| current database that the current user has permission to vacuum. |
| With a parameter, <command>VACUUM</command> processes only that table. |
| </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" endterm="sql-analyze-title"> |
| for more details about its processing. |
| </para> |
| |
| <para> |
| Plain <command>VACUUM</command> (without <literal>FULL</>) 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. <command>VACUUM |
| FULL</command> does more extensive processing, including moving of tuples |
| across blocks to try to compact the table to the minimum number of disk |
| blocks. This form is much slower and requires an exclusive lock on each |
| table while it is being processed. |
| </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. |
| </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"> parameter |
| set to zero. The <literal>FREEZE</literal> option is deprecated and |
| will be removed in a future release; set the parameter instead. |
| </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><replaceable class="PARAMETER">table</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of a specific table to |
| vacuum. Defaults to all tables in the current database. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">column</replaceable></term> |
| <listitem> |
| <para> |
| The name of a specific column to analyze. Defaults to all columns. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Outputs</title> |
| |
| <para> |
| When <literal>VERBOSE</> is specified, <command>VACUUM</> 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</> can only be performed by a superuser.) |
| <command>VACUUM</> will skip over any tables that the calling user |
| does not have permission to vacuum. |
| </para> |
| |
| <para> |
| <command>VACUUM</> cannot be executed inside a transaction block. |
| </para> |
| |
| <para> |
| For tables with <acronym>GIN</> 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</> 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. The |
| <option>FULL</option> option does not shrink indexes; a periodic |
| <command>REINDEX</> is still recommended. In fact, it is often faster |
| to drop all indexes, <command>VACUUM FULL</>, and recreate the indexes. |
| </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. |
| See <xref linkend="runtime-config-resource-vacuum-cost"> for details. |
| </para> |
| |
| <para> |
| <productname>PostgreSQL</productname> includes an <quote>autovacuum</> |
| 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> |
| The following is an example from running <command>VACUUM</command> on a |
| table in the regression database: |
| |
| <programlisting> |
| regression=# VACUUM VERBOSE ANALYZE onek; |
| INFO: vacuuming "public.onek" |
| INFO: index "onek_unique1" now contains 1000 tuples in 14 pages |
| DETAIL: 3000 index tuples were removed. |
| 0 index pages have been deleted, 0 are currently reusable. |
| CPU 0.01s/0.08u sec elapsed 0.18 sec. |
| INFO: index "onek_unique2" now contains 1000 tuples in 16 pages |
| DETAIL: 3000 index tuples were removed. |
| 0 index pages have been deleted, 0 are currently reusable. |
| CPU 0.00s/0.07u sec elapsed 0.23 sec. |
| INFO: index "onek_hundred" now contains 1000 tuples in 13 pages |
| DETAIL: 3000 index tuples were removed. |
| 0 index pages have been deleted, 0 are currently reusable. |
| CPU 0.01s/0.08u sec elapsed 0.17 sec. |
| INFO: index "onek_stringu1" now contains 1000 tuples in 48 pages |
| DETAIL: 3000 index tuples were removed. |
| 0 index pages have been deleted, 0 are currently reusable. |
| CPU 0.01s/0.09u sec elapsed 0.59 sec. |
| INFO: "onek": removed 3000 tuples in 108 pages |
| DETAIL: CPU 0.01s/0.06u sec elapsed 0.07 sec. |
| INFO: "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages |
| DETAIL: 0 dead tuples cannot be removed yet. |
| There were 0 unused item pointers. |
| 0 pages are entirely empty. |
| CPU 0.07s/0.39u sec elapsed 1.56 sec. |
| INFO: analyzing "public.onek" |
| INFO: "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows |
| VACUUM |
| </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" endterm="app-vacuumdb-title"></member> |
| <member><xref linkend="runtime-config-resource-vacuum-cost" endterm="runtime-config-resource-vacuum-cost-title"></member> |
| <member><xref linkend="autovacuum" endterm="autovacuum-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |