doc/src/sgml/ref/vacuum.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-VACUUM">
   7  <refmeta>
   8   <refentrytitle id="sql-vacuum-title">VACUUM</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>VACUUM</refname>
  14   <refpurpose>garbage-collect and optionally analyze a database</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-vacuum">
  18   <primary>VACUUM</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> ]
  24 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ]
  25 </synopsis>
  26  </refsynopsisdiv>
  27
  28  <refsect1>
  29   <title>Description</title>
  30
  31   <para>
  32    <command>VACUUM</command> reclaims storage occupied by dead tuples.
  33    In normal <productname>PostgreSQL</productname> operation, tuples that
  34    are deleted or obsoleted by an update are not physically removed from
  35    their table; they remain present until a <command>VACUUM</command> is
  36    done.  Therefore it's necessary to do <command>VACUUM</command>
  37    periodically, especially on frequently-updated tables.
  38   </para>
  39
  40   <para>
  41    With no parameter, <command>VACUUM</command> processes every table in the
  42    current database.  With a parameter, <command>VACUUM</command> processes
  43    only that table.
  44   </para>
  45
  46   <para>
  47    <command>VACUUM ANALYZE</command> performs a <command>VACUUM</command>
  48    and then an <command>ANALYZE</command> for each selected table.  This
  49    is a handy combination form for routine maintenance scripts.  See
  50    <xref linkend="sql-analyze" endterm="sql-analyze-title">
  51    for more details about its processing.
  52   </para>
  53
  54   <para>
  55    Plain <command>VACUUM</command> (without <literal>FULL</>) simply reclaims
  56    space and makes it
  57    available for re-use.  This form of the command can operate in parallel
  58    with normal reading and writing of the table, as an exclusive lock
  59    is not obtained. <command>VACUUM
  60    FULL</command> does more extensive processing, including moving of tuples
  61    across blocks to try to compact the table to the minimum number of disk
  62    blocks.  This form is much slower and requires an exclusive lock on each
  63    table while it is being processed.
  64   </para>
  65  </refsect1>
  66
  67  <refsect1>
  68   <title>Parameters</title>
  69
  70   <variablelist>
  71    <varlistentry>
  72     <term><literal>FULL</literal></term>
  73     <listitem>
  74      <para>
  75       Selects <quote>full</quote> vacuum, which can reclaim more
  76       space, but takes much longer and exclusively locks the table.
  77      </para>
  78     </listitem>
  79    </varlistentry>
  80
  81    <varlistentry>
  82     <term><literal>FREEZE</literal></term>
  83     <listitem>
  84      <para>
  85       Selects aggressive <quote>freezing</quote> of tuples.
  86       Specifying <literal>FREEZE</literal> is equivalent to performing
  87       <command>VACUUM</command> with the
  88       <xref linkend="guc-vacuum-freeze-min-age"> parameter
  89       set to zero.  The <literal>FREEZE</literal> option is deprecated and
  90       will be removed in a future release; set the parameter instead.
  91      </para>
  92     </listitem>
  93    </varlistentry>
  94
  95    <varlistentry>
  96     <term><literal>VERBOSE</literal></term>
  97     <listitem>
  98      <para>
  99       Prints a detailed vacuum activity report for each table. Can be used
 100       to help determine appropriate settings for
 101       <xref linkend="guc-max-fsm-pages">,
 102       <xref linkend="guc-max-fsm-relations">, and
 103       <xref linkend="guc-default-statistics-target">.
 104      </para>
 105     </listitem>
 106    </varlistentry>
 107
 108    <varlistentry>
 109     <term><literal>ANALYZE</literal></term>
 110     <listitem>
 111      <para>
 112       Updates statistics used by the planner to determine the most
 113       efficient way to execute a query.
 114      </para>
 115     </listitem>
 116    </varlistentry>
 117
 118    <varlistentry>
 119     <term><replaceable class="PARAMETER">table</replaceable></term>
 120     <listitem>
 121      <para>
 122       The name (optionally schema-qualified) of a specific table to
 123       vacuum. Defaults to all tables in the current database.
 124      </para>
 125     </listitem>
 126    </varlistentry>
 127
 128    <varlistentry>
 129     <term><replaceable class="PARAMETER">column</replaceable></term>
 130     <listitem>
 131      <para>
 132       The name of a specific column to analyze. Defaults to all columns.
 133      </para>
 134     </listitem>
 135    </varlistentry>
 136   </variablelist>
 137  </refsect1>
 138
 139  <refsect1>
 140   <title>Outputs</title>
 141
 142    <para>
 143     When <literal>VERBOSE</> is specified, <command>VACUUM</> emits
 144     progress messages to indicate which table is currently being
 145     processed.  Various statistics about the tables are printed as well.
 146    </para>
 147  </refsect1>
 148
 149  <refsect1>
 150   <title>Notes</title>
 151
 152    <para>
 153     <command>VACUUM</> cannot be executed inside a transaction block.
 154    </para>
 155
 156    <para>
 157     We recommend that active production databases be
 158     vacuumed frequently (at least nightly), in order to
 159     remove dead rows. After adding or deleting a large number
 160     of rows, it might be a good idea to issue a <command>VACUUM
 161     ANALYZE</command> command for the affected table. This will update the
 162     system catalogs with
 163     the results of all recent changes, and allow the
 164     <productname>PostgreSQL</productname> query planner to make better
 165     choices in planning queries.
 166    </para>
 167
 168    <para>
 169     The <option>FULL</option> option is not recommended for routine use,
 170     but might be useful in special cases.  An example is when you have deleted
 171     or updated most of the rows in a table and would like the table to
 172     physically shrink to occupy less disk space and allow faster table
 173     scans. <command>VACUUM FULL</command> will usually shrink the table
 174     more than a plain <command>VACUUM</command> would.  The
 175     <option>FULL</option> option does not shrink indexes; a periodic
 176     <command>REINDEX</> is still recommended.  In fact, it is often faster
 177     to drop all indexes, <command>VACUUM FULL</>, and recreate the indexes.
 178    </para>
 179
 180    <para>
 181     <command>VACUUM</command> causes a substantial increase in I/O traffic,
 182     which might cause poor performance for other active sessions.  Therefore,
 183     it is sometimes advisable to use the cost-based vacuum delay feature.
 184     See <xref linkend="runtime-config-resource-vacuum-cost"> for details.
 185    </para>
 186
 187    <para>
 188     <productname>PostgreSQL</productname> includes an <quote>autovacuum</>
 189     facility which can automate routine vacuum maintenance.  For more
 190     information about automatic and manual vacuuming, see
 191     <xref linkend="routine-vacuuming">.
 192    </para>
 193  </refsect1>
 194
 195  <refsect1>
 196   <title>Examples</title>
 197
 198   <para>
 199    The following is an example from running <command>VACUUM</command> on a
 200    table in the regression database:
 201
 202 <programlisting>
 203 regression=# VACUUM VERBOSE ANALYZE onek;
 204 INFO:  vacuuming "public.onek"
 205 INFO:  index "onek_unique1" now contains 1000 tuples in 14 pages
 206 DETAIL:  3000 index tuples were removed.
 207 0 index pages have been deleted, 0 are currently reusable.
 208 CPU 0.01s/0.08u sec elapsed 0.18 sec.
 209 INFO:  index "onek_unique2" now contains 1000 tuples in 16 pages
 210 DETAIL:  3000 index tuples were removed.
 211 0 index pages have been deleted, 0 are currently reusable.
 212 CPU 0.00s/0.07u sec elapsed 0.23 sec.
 213 INFO:  index "onek_hundred" now contains 1000 tuples in 13 pages
 214 DETAIL:  3000 index tuples were removed.
 215 0 index pages have been deleted, 0 are currently reusable.
 216 CPU 0.01s/0.08u sec elapsed 0.17 sec.
 217 INFO:  index "onek_stringu1" now contains 1000 tuples in 48 pages
 218 DETAIL:  3000 index tuples were removed.
 219 0 index pages have been deleted, 0 are currently reusable.
 220 CPU 0.01s/0.09u sec elapsed 0.59 sec.
 221 INFO:  "onek": removed 3000 tuples in 108 pages
 222 DETAIL:  CPU 0.01s/0.06u sec elapsed 0.07 sec.
 223 INFO:  "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages
 224 DETAIL:  0 dead tuples cannot be removed yet.
 225 There were 0 unused item pointers.
 226 0 pages are entirely empty.
 227 CPU 0.07s/0.39u sec elapsed 1.56 sec.
 228 INFO:  analyzing "public.onek"
 229 INFO:  "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows
 230 VACUUM
 231 </programlisting>
 232   </para>
 233  </refsect1>
 234
 235  <refsect1>
 236   <title>Compatibility</title>
 237
 238   <para>
 239    There is no <command>VACUUM</command> statement in the SQL standard.
 240   </para>
 241  </refsect1>
 242
 243  <refsect1>
 244   <title>See Also</title>
 245
 246   <simplelist type="inline">
 247    <member><xref linkend="app-vacuumdb" endterm="app-vacuumdb-title"></member>
 248    <member><xref linkend="runtime-config-resource-vacuum-cost" endterm="runtime-config-resource-vacuum-cost-title"></member>
 249    <member><xref linkend="autovacuum" endterm="autovacuum-title"></member>
 250   </simplelist>
 251  </refsect1>
 252 </refentry>