<!--
doc/src/sgml/ref/reindex.sgml
PostgreSQL documentation
-->

<refentry id="sql-reindex">
 <indexterm zone="sql-reindex">
  <primary>REINDEX</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>REINDEX</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>REINDEX</refname>
  <refpurpose>rebuild indexes</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ]

<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>

    CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
    TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
    VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>REINDEX</command> rebuilds an index using the data
   stored in the index's table, replacing the old copy of the index. There are
   several scenarios in which to use <command>REINDEX</command>:

   <itemizedlist>
    <listitem>
     <para>
      An index has become corrupted, and no longer contains valid
      data. Although in theory this should never happen, in
      practice indexes can become corrupted due to software bugs or
      hardware failures.  <command>REINDEX</command> provides a
      recovery method.
     </para>
    </listitem>

    <listitem>
     <para>
      An index has become <quote>bloated</quote>, that is it contains many
      empty or nearly-empty pages.  This can occur with B-tree indexes in
      <productname>PostgreSQL</productname> under certain uncommon access
      patterns. <command>REINDEX</command> provides a way to reduce
      the space consumption of the index by writing a new version of
      the index without the dead pages. See <xref
      linkend="routine-reindex"/> for more information.
     </para>
    </listitem>

    <listitem>
     <para>
      You have altered a storage parameter (such as fillfactor)
      for an index, and wish to ensure that the change has taken full effect.
     </para>
    </listitem>

    <listitem>
     <para>
      If an index build fails with the <literal>CONCURRENTLY</literal> option,
      this index is left as <quote>invalid</quote>. Such indexes are useless
      but it can be convenient to use <command>REINDEX</command> to rebuild
      them. Note that only <command>REINDEX INDEX</command> is able
      to perform a concurrent build on an invalid index.
     </para>
    </listitem>

   </itemizedlist></para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>INDEX</literal></term>
    <listitem>
     <para>
      Recreate the specified index. This form of <command>REINDEX</command>
      cannot be executed inside a transaction block when used with a
      partitioned index.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>TABLE</literal></term>
    <listitem>
     <para>
      Recreate all indexes of the specified table.  If the table has a
      secondary <quote>TOAST</quote> table, that is reindexed as well.
      This form of <command>REINDEX</command> cannot be executed inside a
      transaction block when used with a partitioned table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>SCHEMA</literal></term>
    <listitem>
     <para>
      Recreate all indexes of the specified schema.  If a table of this
      schema has a secondary <quote>TOAST</quote> table, that is reindexed as
      well. Indexes on shared system catalogs are also processed.