doc/src/sgml/ref/reindex.sgml

   1 <!--
   2 doc/src/sgml/ref/reindex.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-reindex">
   7  <indexterm zone="sql-reindex">
   8   <primary>REINDEX</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>REINDEX</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>REINDEX</refname>
  19   <refpurpose>rebuild indexes</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
  25 REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ]
  26
  27 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
  28
  29     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
  30     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
  31     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
  32 </synopsis>
  33  </refsynopsisdiv>
  34
  35  <refsect1>
  36   <title>Description</title>
  37
  38   <para>
  39    <command>REINDEX</command> rebuilds an index using the data
  40    stored in the index's table, replacing the old copy of the index. There are
  41    several scenarios in which to use <command>REINDEX</command>:
  42
  43    <itemizedlist>
  44     <listitem>
  45      <para>
  46       An index has become corrupted, and no longer contains valid
  47       data. Although in theory this should never happen, in
  48       practice indexes can become corrupted due to software bugs or
  49       hardware failures.  <command>REINDEX</command> provides a
  50       recovery method.
  51      </para>
  52     </listitem>
  53
  54     <listitem>
  55      <para>
  56       An index has become <quote>bloated</quote>, that is it contains many
  57       empty or nearly-empty pages.  This can occur with B-tree indexes in
  58       <productname>PostgreSQL</productname> under certain uncommon access
  59       patterns. <command>REINDEX</command> provides a way to reduce
  60       the space consumption of the index by writing a new version of
  61       the index without the dead pages. See <xref
  62       linkend="routine-reindex"/> for more information.
  63      </para>
  64     </listitem>
  65
  66     <listitem>
  67      <para>
  68       You have altered a storage parameter (such as fillfactor)
  69       for an index, and wish to ensure that the change has taken full effect.
  70      </para>
  71     </listitem>
  72
  73     <listitem>
  74      <para>
  75       If an index build fails with the <literal>CONCURRENTLY</literal> option,
  76       this index is left as <quote>invalid</quote>. Such indexes are useless
  77       but it can be convenient to use <command>REINDEX</command> to rebuild
  78       them. Note that only <command>REINDEX INDEX</command> is able
  79       to perform a concurrent build on an invalid index.
  80      </para>
  81     </listitem>
  82
  83    </itemizedlist></para>
  84  </refsect1>
  85
  86  <refsect1>
  87   <title>Parameters</title>
  88
  89   <variablelist>
  90    <varlistentry>
  91     <term><literal>INDEX</literal></term>
  92     <listitem>
  93      <para>
  94       Recreate the specified index. This form of <command>REINDEX</command>
  95       cannot be executed inside a transaction block when used with a
  96       partitioned index.
  97      </para>
  98     </listitem>
  99    </varlistentry>
 100
 101    <varlistentry>
 102     <term><literal>TABLE</literal></term>
 103     <listitem>
 104      <para>
 105       Recreate all indexes of the specified table.  If the table has a
 106       secondary <quote>TOAST</quote> table, that is reindexed as well.
 107       This form of <command>REINDEX</command> cannot be executed inside a
 108       transaction block when used with a partitioned table.
 109      </para>
 110     </listitem>
 111    </varlistentry>
 112
 113    <varlistentry>
 114     <term><literal>SCHEMA</literal></term>
 115     <listitem>
 116      <para>
 117       Recreate all indexes of the specified schema.  If a table of this
 118       schema has a secondary <quote>TOAST</quote> table, that is reindexed as
 119       well. Indexes on shared system catalogs are also processed.
 120       This form of <command>REINDEX</command> cannot be executed inside a
 121       transaction block.
 122      </para>
 123     </listitem>
 124    </varlistentry>
 125
 126    <varlistentry>
 127     <term><literal>DATABASE</literal></term>
 128     <listitem>
 129      <para>
 130       Recreate all indexes within the current database, except system
 131       catalogs.
 132       Indexes on system catalogs are not processed.
 133       This form of <command>REINDEX</command> cannot be executed inside a
 134       transaction block.
 135      </para>
 136     </listitem>
 137    </varlistentry>
 138
 139    <varlistentry>
 140     <term><literal>SYSTEM</literal></term>
 141     <listitem>
 142      <para>
 143       Recreate all indexes on system catalogs within the current database.
 144       Indexes on shared system catalogs are included.
 145       Indexes on user tables are not processed.
 146       This form of <command>REINDEX</command> cannot be executed inside a
 147       transaction block.
 148      </para>
 149     </listitem>
 150    </varlistentry>
 151
 152    <varlistentry>
 153     <term><replaceable class="parameter">name</replaceable></term>
 154     <listitem>
 155      <para>
 156       The name of the specific index, table, or database to be
 157       reindexed.  Index and table names can be schema-qualified.
 158       Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
 159       can only reindex the current database. Their parameter is optional,
 160       and it must match the current database's name.
 161      </para>
 162     </listitem>
 163    </varlistentry>
 164
 165    <varlistentry>
 166     <term><literal>CONCURRENTLY</literal></term>
 167     <listitem>
 168      <para>
 169       When this option is used, <productname>PostgreSQL</productname> will rebuild the
 170       index without taking any locks that prevent concurrent inserts,
 171       updates, or deletes on the table; whereas a standard index rebuild
 172       locks out writes (but not reads) on the table until it's done.
 173       There are several caveats to be aware of when using this option
 174       &mdash; see <xref linkend="sql-reindex-concurrently"/> below.
 175      </para>
 176      <para>
 177       For temporary tables, <command>REINDEX</command> is always
 178       non-concurrent, as no other session can access them, and
 179       non-concurrent reindex is cheaper.
 180      </para>
 181     </listitem>
 182    </varlistentry>
 183
 184    <varlistentry>
 185     <term><literal>TABLESPACE</literal></term>
 186     <listitem>
 187      <para>
 188       Specifies that indexes will be rebuilt on a new tablespace.
 189      </para>
 190     </listitem>
 191    </varlistentry>
 192
 193    <varlistentry>
 194     <term><literal>VERBOSE</literal></term>
 195     <listitem>
 196      <para>
 197       Prints a progress report as each index is reindexed
 198       at <literal>INFO</literal> level.
 199      </para>
 200     </listitem>
 201    </varlistentry>
 202
 203    <varlistentry>
 204     <term><replaceable class="parameter">boolean</replaceable></term>
 205     <listitem>
 206      <para>
 207       Specifies whether the selected option should be turned on or off.
 208       You can write <literal>TRUE</literal>, <literal>ON</literal>, or
 209       <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
 210       <literal>OFF</literal>, or <literal>0</literal> to disable it.  The
 211       <replaceable class="parameter">boolean</replaceable> value can also
 212       be omitted, in which case <literal>TRUE</literal> is assumed.
 213      </para>
 214     </listitem>
 215    </varlistentry>
 216
 217    <varlistentry>
 218     <term><replaceable class="parameter">new_tablespace</replaceable></term>
 219     <listitem>
 220      <para>
 221       The tablespace where indexes will be rebuilt.
 222      </para>
 223     </listitem>
 224    </varlistentry>
 225   </variablelist>
 226  </refsect1>
 227
 228  <refsect1>
 229   <title>Notes</title>
 230
 231   <para>
 232    If you suspect corruption of an index on a user table, you can
 233    simply rebuild that index, or all indexes on the table, using
 234    <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>.
 235   </para>
 236
 237   <para>
 238    Things are more difficult if you need to recover from corruption of
 239    an index on a system table.  In this case it's important for the
 240    system to not have used any of the suspect indexes itself.
 241    (Indeed, in this sort of scenario you might find that server
 242    processes are crashing immediately at start-up, due to reliance on
 243    the corrupted indexes.)  To recover safely, the server must be started
 244    with the <option>-P</option> option, which prevents it from using
 245    indexes for system catalog lookups.
 246   </para>
 247
 248   <para>
 249    One way to do this is to shut down the server and start a single-user
 250    <productname>PostgreSQL</productname> server
 251    with the <option>-P</option> option included on its command line.
 252    Then, <command>REINDEX DATABASE</command>, <command>REINDEX SYSTEM</command>,
 253    <command>REINDEX TABLE</command>, or <command>REINDEX INDEX</command> can be
 254    issued, depending on how much you want to reconstruct.  If in
 255    doubt, use <command>REINDEX SYSTEM</command> to select
 256    reconstruction of all system indexes in the database.  Then quit
 257    the single-user server session and restart the regular server.
 258    See the <xref linkend="app-postgres"/> reference page for more
 259    information about how to interact with the single-user server
 260    interface.
 261   </para>
 262
 263   <para>
 264    Alternatively, a regular server session can be started with
 265    <option>-P</option> included in its command line options.
 266    The method for doing this varies across clients, but in all
 267    <application>libpq</application>-based clients, it is possible to set
 268    the <envar>PGOPTIONS</envar> environment variable to <literal>-P</literal>
 269    before starting the client.  Note that while this method does not
 270    require locking out other clients, it might still be wise to prevent
 271    other users from connecting to the damaged database until repairs
 272    have been completed.
 273   </para>
 274
 275   <para>
 276    <command>REINDEX</command> is similar to a drop and recreate of the index
 277    in that the index contents are rebuilt from scratch.  However, the locking
 278    considerations are rather different.  <command>REINDEX</command> locks out writes
 279    but not reads of the index's parent table.  It also takes an
 280    <literal>ACCESS EXCLUSIVE</literal> lock on the specific index being processed,
 281    which will block reads that attempt to use that index. In particular,
 282    the query planner tries to take an <literal>ACCESS SHARE</literal>
 283    lock on every index of the table, regardless of the query, and so
 284    <command>REINDEX</command> blocks virtually any queries except for some
 285    prepared queries whose plan has been cached and which don't use this very
 286    index. In contrast,
 287    <command>DROP INDEX</command> momentarily takes an
 288    <literal>ACCESS EXCLUSIVE</literal> lock on the parent table, blocking both
 289    writes and reads.  The subsequent <command>CREATE INDEX</command> locks out
 290    writes but not reads; since the index is not there, no read will attempt to
 291    use it, meaning that there will be no blocking but reads might be forced
 292    into expensive sequential scans.
 293   </para>
 294
 295   <para>
 296    While <command>REINDEX</command> is running, the <xref
 297    linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
 298    pg_temp</literal>.
 299   </para>
 300
 301   <para>
 302    Reindexing a single index or table requires
 303    having the <literal>MAINTAIN</literal> privilege on the
 304    table.  Note that while <command>REINDEX</command> on a partitioned index or
 305    table requires having the <literal>MAINTAIN</literal> privilege on the
 306    partitioned table, such commands skip the privilege checks when processing
 307    the individual partitions.  Reindexing a schema or database requires being the
 308    owner of that schema or database or having privileges of the
 309    <xref linkend="predefined-role-pg-maintain"/>
 310    role.  Note specifically that it's thus
 311    possible for non-superusers to rebuild indexes of tables owned by
 312    other users.  However, as a special exception,
 313    <command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>,
 314    and <command>REINDEX SYSTEM</command> will skip indexes on shared catalogs
 315    unless the user has the <literal>MAINTAIN</literal> privilege on the
 316    catalog.
 317   </para>
 318
 319   <para>
 320    Reindexing partitioned indexes or partitioned tables is supported
 321    with <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>,
 322    respectively. Each partition of the specified partitioned relation is
 323    reindexed in a separate transaction. Those commands cannot be used inside
 324    a transaction block when working on a partitioned table or index.
 325   </para>
 326
 327   <para>
 328    When using the <literal>TABLESPACE</literal> clause with
 329    <command>REINDEX</command> on a partitioned index or table, only the
 330    tablespace references of the leaf partitions are updated. As partitioned
 331    indexes are not updated, it is recommended to separately use
 332    <command>ALTER TABLE ONLY</command> on them so as any new partitions
 333    attached inherit the new tablespace. On failure, it may not have moved
 334    all the indexes to the new tablespace. Re-running the command will rebuild
 335    all the leaf partitions and move previously-unprocessed indexes to the new
 336    tablespace.
 337   </para>
 338
 339   <para>
 340    If <literal>SCHEMA</literal>, <literal>DATABASE</literal> or
 341    <literal>SYSTEM</literal> is used with <literal>TABLESPACE</literal>,
 342    system relations are skipped and a single <literal>WARNING</literal>
 343    will be generated. Indexes on TOAST tables are rebuilt, but not moved
 344    to the new tablespace.
 345   </para>
 346
 347   <refsect2 id="sql-reindex-concurrently" xreflabel="Rebuilding Indexes Concurrently">
 348    <title>Rebuilding Indexes Concurrently</title>
 349
 350    <indexterm zone="sql-reindex-concurrently">
 351     <primary>index</primary>
 352     <secondary>rebuilding concurrently</secondary>
 353    </indexterm>
 354
 355    <para>
 356     Rebuilding an index can interfere with regular operation of a database.
 357     Normally <productname>PostgreSQL</productname> locks the table whose index is rebuilt
 358     against writes and performs the entire index build with a single scan of the
 359     table. Other transactions can still read the table, but if they try to
 360     insert, update, or delete rows in the table they will block until the
 361     index rebuild is finished. This could have a severe effect if the system is
 362     a live production database. Very large tables can take many hours to be
 363     indexed, and even for smaller tables, an index rebuild can lock out writers
 364     for periods that are unacceptably long for a production system.
 365    </para>
 366
 367    <para>
 368     <productname>PostgreSQL</productname> supports rebuilding indexes with minimum locking
 369     of writes.  This method is invoked by specifying the
 370     <literal>CONCURRENTLY</literal> option of <command>REINDEX</command>. When this option
 371     is used, <productname>PostgreSQL</productname> must perform two scans of the table
 372     for each index that needs to be rebuilt and wait for termination of
 373     all existing transactions that could potentially use the index.
 374     This method requires more total work than a standard index
 375     rebuild and takes significantly longer to complete as it needs to wait
 376     for unfinished transactions that might modify the index. However, since
 377     it allows normal operations to continue while the index is being rebuilt, this
 378     method is useful for rebuilding indexes in a production environment. Of
 379     course, the extra CPU, memory and I/O load imposed by the index rebuild
 380     may slow down other operations.
 381    </para>
 382
 383    <para>
 384     The following steps occur in a concurrent reindex.  Each step is run in a
 385     separate transaction.  If there are multiple indexes to be rebuilt, then
 386     each step loops through all the indexes before moving to the next step.
 387
 388     <orderedlist>
 389      <listitem>
 390       <para>
 391        A new transient index definition is added to the catalog
 392        <literal>pg_index</literal>.  This definition will be used to replace
 393        the old index.  A <literal>SHARE UPDATE EXCLUSIVE</literal> lock at
 394        session level is taken on the indexes being reindexed as well as their
 395        associated tables to prevent any schema modification while processing.
 396       </para>
 397      </listitem>
 398
 399      <listitem>
 400       <para>
 401        A first pass to build the index is done for each new index.  Once the
 402        index is built, its flag <literal>pg_index.indisready</literal> is
 403        switched to <quote>true</quote> to make it ready for inserts, making it
 404        visible to other sessions once the transaction that performed the build
 405        is finished.  This step is done in a separate transaction for each
 406        index.
 407       </para>
 408      </listitem>
 409
 410      <listitem>
 411       <para>
 412        Then a second pass is performed to add tuples that were added while the
 413        first pass was running.  This step is also done in a separate
 414        transaction for each index.
 415       </para>
 416      </listitem>
 417
 418      <listitem>
 419       <para>
 420        All the constraints that refer to the index are changed to refer to the
 421        new index definition, and the names of the indexes are changed.  At
 422        this point, <literal>pg_index.indisvalid</literal> is switched to
 423        <quote>true</quote> for the new index and to <quote>false</quote> for
 424        the old, and a cache invalidation is done causing all sessions that
 425        referenced the old index to be invalidated.
 426       </para>
 427      </listitem>
 428
 429      <listitem>
 430       <para>
 431        The old indexes have <literal>pg_index.indisready</literal> switched to
 432        <quote>false</quote> to prevent any new tuple insertions, after waiting
 433        for running queries that might reference the old index to complete.
 434       </para>
 435      </listitem>
 436
 437      <listitem>
 438       <para>
 439        The old indexes are dropped.  The <literal>SHARE UPDATE
 440        EXCLUSIVE</literal> session locks for the indexes and the table are
 441        released.
 442       </para>
 443      </listitem>
 444     </orderedlist>
 445    </para>
 446
 447    <para>
 448     If a problem arises while rebuilding the indexes, such as a
 449     uniqueness violation in a unique index, the <command>REINDEX</command>
 450     command will fail but leave behind an <quote>invalid</quote> new index in addition to
 451     the pre-existing one. This index will be ignored for querying purposes
 452     because it might be incomplete; however it will still consume update
 453     overhead. The <application>psql</application> <command>\d</command> command will report
 454     such an index as <literal>INVALID</literal>:
 455
 456 <programlisting>
 457 postgres=# \d tab
 458        Table "public.tab"
 459  Column |  Type   | Modifiers
 460 --------+---------+-----------
 461  col    | integer |
 462 Indexes:
 463     "idx" btree (col)
 464     "idx_ccnew" btree (col) INVALID
 465 </programlisting>
 466
 467     If the index marked <literal>INVALID</literal> is suffixed
 468     <literal>ccnew</literal>, then it corresponds to the transient
 469     index created during the concurrent operation, and the recommended
 470     recovery method is to drop it using <literal>DROP INDEX</literal>,
 471     then attempt <command>REINDEX CONCURRENTLY</command> again.
 472     If the invalid index is instead suffixed <literal>ccold</literal>,
 473     it corresponds to the original index which could not be dropped;
 474     the recommended recovery method is to just drop said index, since the
 475     rebuild proper has been successful.
 476    </para>
 477
 478    <para>
 479     Regular index builds permit other regular index builds on the same table
 480     to occur simultaneously, but only one concurrent index build can occur on a
 481     table at a time. In both cases, no other types of schema modification on
 482     the table are allowed meanwhile.  Another difference is that a regular
 483     <command>REINDEX TABLE</command> or <command>REINDEX INDEX</command>
 484     command can be performed within a transaction block, but <command>REINDEX
 485     CONCURRENTLY</command> cannot.
 486    </para>
 487
 488    <para>
 489     Like any long-running transaction, <command>REINDEX</command> on a table
 490     can affect which tuples can be removed by concurrent
 491     <command>VACUUM</command> on any other table.
 492    </para>
 493
 494    <para>
 495     <command>REINDEX SYSTEM</command> does not support
 496     <command>CONCURRENTLY</command> since system catalogs cannot be reindexed
 497     concurrently.
 498    </para>
 499
 500    <para>
 501     Furthermore, indexes for exclusion constraints cannot be reindexed
 502     concurrently.  If such an index is named directly in this command, an
 503     error is raised.  If a table or database with exclusion constraint indexes
 504     is reindexed concurrently, those indexes will be skipped.  (It is possible
 505     to reindex such indexes without the <command>CONCURRENTLY</command> option.)
 506    </para>
 507
 508   <para>
 509     Each backend running <command>REINDEX</command> will report its progress
 510     in the <structname>pg_stat_progress_create_index</structname> view. See
 511     <xref linkend="create-index-progress-reporting"/> for details.
 512   </para>
 513   </refsect2>
 514  </refsect1>
 515
 516  <refsect1>
 517   <title>Examples</title>
 518
 519   <para>
 520    Rebuild a single index:
 521
 522 <programlisting>
 523 REINDEX INDEX my_index;
 524 </programlisting>
 525   </para>
 526
 527   <para>
 528    Rebuild all the indexes on the table <literal>my_table</literal>:
 529
 530 <programlisting>
 531 REINDEX TABLE my_table;
 532 </programlisting>
 533   </para>
 534
 535   <para>
 536    Rebuild all indexes in a particular database, without trusting the
 537    system indexes to be valid already:
 538
 539 <programlisting>
 540 $ <userinput>export PGOPTIONS="-P"</userinput>
 541 $ <userinput>psql broken_db</userinput>
 542 ...
 543 broken_db=&gt; REINDEX DATABASE broken_db;
 544 broken_db=&gt; \q
 545 </programlisting></para>
 546
 547   <para>
 548    Rebuild indexes for a table, without blocking read and write operations
 549    on involved relations while reindexing is in progress:
 550
 551 <programlisting>
 552 REINDEX TABLE CONCURRENTLY my_broken_table;
 553 </programlisting></para>
 554  </refsect1>
 555
 556  <refsect1>
 557   <title>Compatibility</title>
 558
 559   <para>
 560    There is no <command>REINDEX</command> command in the SQL standard.
 561   </para>
 562  </refsect1>
 563
 564  <refsect1>
 565   <title>See Also</title>
 566
 567   <simplelist type="inline">
 568    <member><xref linkend="sql-createindex"/></member>
 569    <member><xref linkend="sql-dropindex"/></member>
 570    <member><xref linkend="app-reindexdb"/></member>
 571    <member><xref linkend="create-index-progress-reporting"/></member>
 572   </simplelist>
 573  </refsect1>
 574 </refentry>