begriffs open source - ai-pg/blob - full-docs/src/sgml/html/sql-reindex.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>REINDEX</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-refreshmaterializedview.html" title="REFRESH MATERIALIZED VIEW" /><link rel="next" href="sql-release-savepoint.html" title="RELEASE SAVEPOINT" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">REINDEX</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-refreshmaterializedview.html" title="REFRESH MATERIALIZED VIEW">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-release-savepoint.html" title="RELEASE SAVEPOINT">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-REINDEX"><div class="titlepage"></div><a id="id-1.9.3.163.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">REINDEX</span></h2><p>REINDEX — rebuild indexes</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 REINDEX [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] <em class="replaceable"><code>name</code></em>
   4 REINDEX [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ <em class="replaceable"><code>name</code></em> ]
   5
   6 <span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>
   7
   8     CONCURRENTLY [ <em class="replaceable"><code>boolean</code></em> ]
   9     TABLESPACE <em class="replaceable"><code>new_tablespace</code></em>
  10     VERBOSE [ <em class="replaceable"><code>boolean</code></em> ]
  11 </pre></div><div class="refsect1" id="id-1.9.3.163.5"><h2>Description</h2><p>
  12    <code class="command">REINDEX</code> rebuilds an index using the data
  13    stored in the index's table, replacing the old copy of the index. There are
  14    several scenarios in which to use <code class="command">REINDEX</code>:
  15
  16    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  17       An index has become corrupted, and no longer contains valid
  18       data. Although in theory this should never happen, in
  19       practice indexes can become corrupted due to software bugs or
  20       hardware failures.  <code class="command">REINDEX</code> provides a
  21       recovery method.
  22      </p></li><li class="listitem"><p>
  23       An index has become <span class="quote">“<span class="quote">bloated</span>”</span>, that is it contains many
  24       empty or nearly-empty pages.  This can occur with B-tree indexes in
  25       <span class="productname">PostgreSQL</span> under certain uncommon access
  26       patterns. <code class="command">REINDEX</code> provides a way to reduce
  27       the space consumption of the index by writing a new version of
  28       the index without the dead pages. See <a class="xref" href="routine-reindex.html" title="24.2. Routine Reindexing">Section 24.2</a> for more information.
  29      </p></li><li class="listitem"><p>
  30       You have altered a storage parameter (such as fillfactor)
  31       for an index, and wish to ensure that the change has taken full effect.
  32      </p></li><li class="listitem"><p>
  33       If an index build fails with the <code class="literal">CONCURRENTLY</code> option,
  34       this index is left as <span class="quote">“<span class="quote">invalid</span>”</span>. Such indexes are useless
  35       but it can be convenient to use <code class="command">REINDEX</code> to rebuild
  36       them. Note that only <code class="command">REINDEX INDEX</code> is able
  37       to perform a concurrent build on an invalid index.
  38      </p></li></ul></div></div><div class="refsect1" id="id-1.9.3.163.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">INDEX</code></span></dt><dd><p>
  39       Recreate the specified index. This form of <code class="command">REINDEX</code>
  40       cannot be executed inside a transaction block when used with a
  41       partitioned index.
  42      </p></dd><dt><span class="term"><code class="literal">TABLE</code></span></dt><dd><p>
  43       Recreate all indexes of the specified table.  If the table has a
  44       secondary <span class="quote">“<span class="quote">TOAST</span>”</span> table, that is reindexed as well.
  45       This form of <code class="command">REINDEX</code> cannot be executed inside a
  46       transaction block when used with a partitioned table.
  47      </p></dd><dt><span class="term"><code class="literal">SCHEMA</code></span></dt><dd><p>
  48       Recreate all indexes of the specified schema.  If a table of this
  49       schema has a secondary <span class="quote">“<span class="quote">TOAST</span>”</span> table, that is reindexed as
  50       well. Indexes on shared system catalogs are also processed.
  51       This form of <code class="command">REINDEX</code> cannot be executed inside a
  52       transaction block.
  53      </p></dd><dt><span class="term"><code class="literal">DATABASE</code></span></dt><dd><p>
  54       Recreate all indexes within the current database, except system
  55       catalogs.
  56       Indexes on system catalogs are not processed.
  57       This form of <code class="command">REINDEX</code> cannot be executed inside a
  58       transaction block.
  59      </p></dd><dt><span class="term"><code class="literal">SYSTEM</code></span></dt><dd><p>
  60       Recreate all indexes on system catalogs within the current database.
  61       Indexes on shared system catalogs are included.
  62       Indexes on user tables are not processed.
  63       This form of <code class="command">REINDEX</code> cannot be executed inside a
  64       transaction block.
  65      </p></dd><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
  66       The name of the specific index, table, or database to be
  67       reindexed.  Index and table names can be schema-qualified.
  68       Presently, <code class="command">REINDEX DATABASE</code> and <code class="command">REINDEX SYSTEM</code>
  69       can only reindex the current database. Their parameter is optional,
  70       and it must match the current database's name.
  71      </p></dd><dt><span class="term"><code class="literal">CONCURRENTLY</code></span></dt><dd><p>
  72       When this option is used, <span class="productname">PostgreSQL</span> will rebuild the
  73       index without taking any locks that prevent concurrent inserts,
  74       updates, or deletes on the table; whereas a standard index rebuild
  75       locks out writes (but not reads) on the table until it's done.
  76       There are several caveats to be aware of when using this option
  77       — see <a class="xref" href="sql-reindex.html#SQL-REINDEX-CONCURRENTLY" title="Rebuilding Indexes Concurrently">Rebuilding Indexes Concurrently</a> below.
  78      </p><p>
  79       For temporary tables, <code class="command">REINDEX</code> is always
  80       non-concurrent, as no other session can access them, and
  81       non-concurrent reindex is cheaper.
  82      </p></dd><dt><span class="term"><code class="literal">TABLESPACE</code></span></dt><dd><p>
  83       Specifies that indexes will be rebuilt on a new tablespace.
  84      </p></dd><dt><span class="term"><code class="literal">VERBOSE</code></span></dt><dd><p>
  85       Prints a progress report as each index is reindexed
  86       at <code class="literal">INFO</code> level.
  87      </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>
  88       Specifies whether the selected option should be turned on or off.
  89       You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or
  90       <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,
  91       <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it.  The
  92       <em class="replaceable"><code>boolean</code></em> value can also
  93       be omitted, in which case <code class="literal">TRUE</code> is assumed.
  94      </p></dd><dt><span class="term"><em class="replaceable"><code>new_tablespace</code></em></span></dt><dd><p>
  95       The tablespace where indexes will be rebuilt.
  96      </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.163.7"><h2>Notes</h2><p>
  97    If you suspect corruption of an index on a user table, you can
  98    simply rebuild that index, or all indexes on the table, using
  99    <code class="command">REINDEX INDEX</code> or <code class="command">REINDEX TABLE</code>.
 100   </p><p>
 101    Things are more difficult if you need to recover from corruption of
 102    an index on a system table.  In this case it's important for the
 103    system to not have used any of the suspect indexes itself.
 104    (Indeed, in this sort of scenario you might find that server
 105    processes are crashing immediately at start-up, due to reliance on
 106    the corrupted indexes.)  To recover safely, the server must be started
 107    with the <code class="option">-P</code> option, which prevents it from using
 108    indexes for system catalog lookups.
 109   </p><p>
 110    One way to do this is to shut down the server and start a single-user
 111    <span class="productname">PostgreSQL</span> server
 112    with the <code class="option">-P</code> option included on its command line.
 113    Then, <code class="command">REINDEX DATABASE</code>, <code class="command">REINDEX SYSTEM</code>,
 114    <code class="command">REINDEX TABLE</code>, or <code class="command">REINDEX INDEX</code> can be
 115    issued, depending on how much you want to reconstruct.  If in
 116    doubt, use <code class="command">REINDEX SYSTEM</code> to select
 117    reconstruction of all system indexes in the database.  Then quit
 118    the single-user server session and restart the regular server.
 119    See the <a class="xref" href="app-postgres.html" title="postgres"><span class="refentrytitle"><span class="application">postgres</span></span></a> reference page for more
 120    information about how to interact with the single-user server
 121    interface.
 122   </p><p>
 123    Alternatively, a regular server session can be started with
 124    <code class="option">-P</code> included in its command line options.
 125    The method for doing this varies across clients, but in all
 126    <span class="application">libpq</span>-based clients, it is possible to set
 127    the <code class="envar">PGOPTIONS</code> environment variable to <code class="literal">-P</code>
 128    before starting the client.  Note that while this method does not
 129    require locking out other clients, it might still be wise to prevent
 130    other users from connecting to the damaged database until repairs
 131    have been completed.
 132   </p><p>
 133    <code class="command">REINDEX</code> is similar to a drop and recreate of the index
 134    in that the index contents are rebuilt from scratch.  However, the locking
 135    considerations are rather different.  <code class="command">REINDEX</code> locks out writes
 136    but not reads of the index's parent table.  It also takes an
 137    <code class="literal">ACCESS EXCLUSIVE</code> lock on the specific index being processed,
 138    which will block reads that attempt to use that index. In particular,
 139    the query planner tries to take an <code class="literal">ACCESS SHARE</code>
 140    lock on every index of the table, regardless of the query, and so
 141    <code class="command">REINDEX</code> blocks virtually any queries except for some
 142    prepared queries whose plan has been cached and which don't use this very
 143    index. In contrast,
 144    <code class="command">DROP INDEX</code> momentarily takes an
 145    <code class="literal">ACCESS EXCLUSIVE</code> lock on the parent table, blocking both
 146    writes and reads.  The subsequent <code class="command">CREATE INDEX</code> locks out
 147    writes but not reads; since the index is not there, no read will attempt to
 148    use it, meaning that there will be no blocking but reads might be forced
 149    into expensive sequential scans.
 150   </p><p>
 151    While <code class="command">REINDEX</code> is running, the <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> is temporarily changed to <code class="literal">pg_catalog,
 152    pg_temp</code>.
 153   </p><p>
 154    Reindexing a single index or table requires
 155    having the <code class="literal">MAINTAIN</code> privilege on the
 156    table.  Note that while <code class="command">REINDEX</code> on a partitioned index or
 157    table requires having the <code class="literal">MAINTAIN</code> privilege on the
 158    partitioned table, such commands skip the privilege checks when processing
 159    the individual partitions.  Reindexing a schema or database requires being the
 160    owner of that schema or database or having privileges of the
 161    <a class="xref" href="predefined-roles.html#PREDEFINED-ROLE-PG-MAINTAIN">pg_maintain</a>
 162    role.  Note specifically that it's thus
 163    possible for non-superusers to rebuild indexes of tables owned by
 164    other users.  However, as a special exception,
 165    <code class="command">REINDEX DATABASE</code>, <code class="command">REINDEX SCHEMA</code>,
 166    and <code class="command">REINDEX SYSTEM</code> will skip indexes on shared catalogs
 167    unless the user has the <code class="literal">MAINTAIN</code> privilege on the
 168    catalog.
 169   </p><p>
 170    Reindexing partitioned indexes or partitioned tables is supported
 171    with <code class="command">REINDEX INDEX</code> or <code class="command">REINDEX TABLE</code>,
 172    respectively. Each partition of the specified partitioned relation is
 173    reindexed in a separate transaction. Those commands cannot be used inside
 174    a transaction block when working on a partitioned table or index.
 175   </p><p>
 176    When using the <code class="literal">TABLESPACE</code> clause with
 177    <code class="command">REINDEX</code> on a partitioned index or table, only the
 178    tablespace references of the leaf partitions are updated. As partitioned
 179    indexes are not updated, it is recommended to separately use
 180    <code class="command">ALTER TABLE ONLY</code> on them so as any new partitions
 181    attached inherit the new tablespace. On failure, it may not have moved
 182    all the indexes to the new tablespace. Re-running the command will rebuild
 183    all the leaf partitions and move previously-unprocessed indexes to the new
 184    tablespace.
 185   </p><p>
 186    If <code class="literal">SCHEMA</code>, <code class="literal">DATABASE</code> or
 187    <code class="literal">SYSTEM</code> is used with <code class="literal">TABLESPACE</code>,
 188    system relations are skipped and a single <code class="literal">WARNING</code>
 189    will be generated. Indexes on TOAST tables are rebuilt, but not moved
 190    to the new tablespace.
 191   </p><div class="refsect2" id="SQL-REINDEX-CONCURRENTLY"><h3>Rebuilding Indexes Concurrently</h3><a id="id-1.9.3.163.7.12.2" class="indexterm"></a><p>
 192     Rebuilding an index can interfere with regular operation of a database.
 193     Normally <span class="productname">PostgreSQL</span> locks the table whose index is rebuilt
 194     against writes and performs the entire index build with a single scan of the
 195     table. Other transactions can still read the table, but if they try to
 196     insert, update, or delete rows in the table they will block until the
 197     index rebuild is finished. This could have a severe effect if the system is
 198     a live production database. Very large tables can take many hours to be
 199     indexed, and even for smaller tables, an index rebuild can lock out writers
 200     for periods that are unacceptably long for a production system.
 201    </p><p>
 202     <span class="productname">PostgreSQL</span> supports rebuilding indexes with minimum locking
 203     of writes.  This method is invoked by specifying the
 204     <code class="literal">CONCURRENTLY</code> option of <code class="command">REINDEX</code>. When this option
 205     is used, <span class="productname">PostgreSQL</span> must perform two scans of the table
 206     for each index that needs to be rebuilt and wait for termination of
 207     all existing transactions that could potentially use the index.
 208     This method requires more total work than a standard index
 209     rebuild and takes significantly longer to complete as it needs to wait
 210     for unfinished transactions that might modify the index. However, since
 211     it allows normal operations to continue while the index is being rebuilt, this
 212     method is useful for rebuilding indexes in a production environment. Of
 213     course, the extra CPU, memory and I/O load imposed by the index rebuild
 214     may slow down other operations.
 215    </p><p>
 216     The following steps occur in a concurrent reindex.  Each step is run in a
 217     separate transaction.  If there are multiple indexes to be rebuilt, then
 218     each step loops through all the indexes before moving to the next step.
 219
 220     </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
 221        A new transient index definition is added to the catalog
 222        <code class="literal">pg_index</code>.  This definition will be used to replace
 223        the old index.  A <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock at
 224        session level is taken on the indexes being reindexed as well as their
 225        associated tables to prevent any schema modification while processing.
 226       </p></li><li class="listitem"><p>
 227        A first pass to build the index is done for each new index.  Once the
 228        index is built, its flag <code class="literal">pg_index.indisready</code> is
 229        switched to <span class="quote">“<span class="quote">true</span>”</span> to make it ready for inserts, making it
 230        visible to other sessions once the transaction that performed the build
 231        is finished.  This step is done in a separate transaction for each
 232        index.
 233       </p></li><li class="listitem"><p>
 234        Then a second pass is performed to add tuples that were added while the
 235        first pass was running.  This step is also done in a separate
 236        transaction for each index.
 237       </p></li><li class="listitem"><p>
 238        All the constraints that refer to the index are changed to refer to the
 239        new index definition, and the names of the indexes are changed.  At
 240        this point, <code class="literal">pg_index.indisvalid</code> is switched to
 241        <span class="quote">“<span class="quote">true</span>”</span> for the new index and to <span class="quote">“<span class="quote">false</span>”</span> for
 242        the old, and a cache invalidation is done causing all sessions that
 243        referenced the old index to be invalidated.
 244       </p></li><li class="listitem"><p>
 245        The old indexes have <code class="literal">pg_index.indisready</code> switched to
 246        <span class="quote">“<span class="quote">false</span>”</span> to prevent any new tuple insertions, after waiting
 247        for running queries that might reference the old index to complete.
 248       </p></li><li class="listitem"><p>
 249        The old indexes are dropped.  The <code class="literal">SHARE UPDATE
 250        EXCLUSIVE</code> session locks for the indexes and the table are
 251        released.
 252       </p></li></ol></div><p>
 253    </p><p>
 254     If a problem arises while rebuilding the indexes, such as a
 255     uniqueness violation in a unique index, the <code class="command">REINDEX</code>
 256     command will fail but leave behind an <span class="quote">“<span class="quote">invalid</span>”</span> new index in addition to
 257     the pre-existing one. This index will be ignored for querying purposes
 258     because it might be incomplete; however it will still consume update
 259     overhead. The <span class="application">psql</span> <code class="command">\d</code> command will report
 260     such an index as <code class="literal">INVALID</code>:
 261
 262 </p><pre class="programlisting">
 263 postgres=# \d tab
 264        Table "public.tab"
 265  Column |  Type   | Modifiers
 266 --------+---------+-----------
 267  col    | integer |
 268 Indexes:
 269     "idx" btree (col)
 270     "idx_ccnew" btree (col) INVALID
 271 </pre><p>
 272
 273     If the index marked <code class="literal">INVALID</code> is suffixed
 274     <code class="literal">_ccnew</code>, then it corresponds to the transient
 275     index created during the concurrent operation, and the recommended
 276     recovery method is to drop it using <code class="literal">DROP INDEX</code>,
 277     then attempt <code class="command">REINDEX CONCURRENTLY</code> again.
 278     If the invalid index is instead suffixed <code class="literal">_ccold</code>,
 279     it corresponds to the original index which could not be dropped;
 280     the recommended recovery method is to just drop said index, since the
 281     rebuild proper has been successful.
 282     A nonzero number may be appended to the suffix of the invalid index
 283     names to keep them unique, like <code class="literal">_ccnew1</code>,
 284     <code class="literal">_ccold2</code>, etc.
 285    </p><p>
 286     Regular index builds permit other regular index builds on the same table
 287     to occur simultaneously, but only one concurrent index build can occur on a
 288     table at a time. In both cases, no other types of schema modification on
 289     the table are allowed meanwhile.  Another difference is that a regular
 290     <code class="command">REINDEX TABLE</code> or <code class="command">REINDEX INDEX</code>
 291     command can be performed within a transaction block, but <code class="command">REINDEX
 292     CONCURRENTLY</code> cannot.
 293    </p><p>
 294     Like any long-running transaction, <code class="command">REINDEX</code> on a table
 295     can affect which tuples can be removed by concurrent
 296     <code class="command">VACUUM</code> on any other table.
 297    </p><p>
 298     <code class="command">REINDEX SYSTEM</code> does not support
 299     <code class="command">CONCURRENTLY</code> since system catalogs cannot be reindexed
 300     concurrently.
 301    </p><p>
 302     Furthermore, indexes for exclusion constraints cannot be reindexed
 303     concurrently.  If such an index is named directly in this command, an
 304     error is raised.  If a table or database with exclusion constraint indexes
 305     is reindexed concurrently, those indexes will be skipped.  (It is possible
 306     to reindex such indexes without the <code class="command">CONCURRENTLY</code> option.)
 307    </p><p>
 308     Each backend running <code class="command">REINDEX</code> will report its progress
 309     in the <code class="structname">pg_stat_progress_create_index</code> view. See
 310     <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="27.4.4. CREATE INDEX Progress Reporting">Section 27.4.4</a> for details.
 311   </p></div></div><div class="refsect1" id="id-1.9.3.163.8"><h2>Examples</h2><p>
 312    Rebuild a single index:
 313
 314 </p><pre class="programlisting">
 315 REINDEX INDEX my_index;
 316 </pre><p>
 317   </p><p>
 318    Rebuild all the indexes on the table <code class="literal">my_table</code>:
 319
 320 </p><pre class="programlisting">
 321 REINDEX TABLE my_table;
 322 </pre><p>
 323   </p><p>
 324    Rebuild all indexes in a particular database, without trusting the
 325    system indexes to be valid already:
 326
 327 </p><pre class="programlisting">
 328 $ <strong class="userinput"><code>export PGOPTIONS="-P"</code></strong>
 329 $ <strong class="userinput"><code>psql broken_db</code></strong>
 330 ...
 331 broken_db=&gt; REINDEX DATABASE broken_db;
 332 broken_db=&gt; \q
 333 </pre><p>
 334    Rebuild indexes for a table, without blocking read and write operations
 335    on involved relations while reindexing is in progress:
 336
 337 </p><pre class="programlisting">
 338 REINDEX TABLE CONCURRENTLY my_broken_table;
 339 </pre></div><div class="refsect1" id="id-1.9.3.163.9"><h2>Compatibility</h2><p>
 340    There is no <code class="command">REINDEX</code> command in the SQL standard.
 341   </p></div><div class="refsect1" id="id-1.9.3.163.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createindex.html" title="CREATE INDEX"><span class="refentrytitle">CREATE INDEX</span></a>, <a class="xref" href="sql-dropindex.html" title="DROP INDEX"><span class="refentrytitle">DROP INDEX</span></a>, <a class="xref" href="app-reindexdb.html" title="reindexdb"><span class="refentrytitle"><span class="application">reindexdb</span></span></a>, <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="27.4.4. CREATE INDEX Progress Reporting">Section 27.4.4</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-refreshmaterializedview.html" title="REFRESH MATERIALIZED VIEW">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-release-savepoint.html" title="RELEASE SAVEPOINT">Next</a></td></tr><tr><td width="40%" align="left" valign="top">REFRESH MATERIALIZED VIEW </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> RELEASE SAVEPOINT</td></tr></table></div></body></html>