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