Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-reindex.html329 linesDownload Raw Back to 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 16.3 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">3REINDEX [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] <em class="replaceable"><code>name</code></em>4REINDEX [ ( <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 data13   stored in the index's table, replacing the old copy of the index. There are14   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 valid18      data. Although in theory this should never happen, in19      practice indexes can become corrupted due to software bugs or20      hardware failures.  <code class="command">REINDEX</code> provides a21      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 many24      empty or nearly-empty pages.  This can occur with B-tree indexes in25      <span class="productname">PostgreSQL</span> under certain uncommon access26      patterns. <code class="command">REINDEX</code> provides a way to reduce27      the space consumption of the index by writing a new version of28      the index without the dead pages. See <a class="xref" href="routine-reindex.html" title="25.2. Routine Reindexing">Section 25.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 useless35      but it can be convenient to use <code class="command">REINDEX</code> to rebuild36      them. Note that only <code class="command">REINDEX INDEX</code> is able37      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 a41      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 a44      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 a46      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 this49      schema has a secondary <span class="quote">“<span class="quote">TOAST</span>”</span> table, that is reindexed as50      well. Indexes on shared system catalogs are also processed.51      This form of <code class="command">REINDEX</code> cannot be executed inside a52      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 system55      catalogs.56      Indexes on system catalogs are not processed.57      This form of <code class="command">REINDEX</code> cannot be executed inside a58      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 a64      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 be67      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 the73      index without taking any locks that prevent concurrent inserts,74      updates, or deletes on the table; whereas a standard index rebuild75      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 option77      — 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 always80      non-concurrent, as no other session can access them, and81      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     </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>87      Specifies whether the selected option should be turned on or off.88      You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or89      <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,90      <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it.  The91      <em class="replaceable"><code>boolean</code></em> value can also92      be omitted, in which case <code class="literal">TRUE</code> is assumed.93     </p></dd><dt><span class="term"><em class="replaceable"><code>new_tablespace</code></em></span></dt><dd><p>94      The tablespace where indexes will be rebuilt.95     </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.163.7"><h2>Notes</h2><p>96   If you suspect corruption of an index on a user table, you can97   simply rebuild that index, or all indexes on the table, using98   <code class="command">REINDEX INDEX</code> or <code class="command">REINDEX TABLE</code>.99  </p><p>100   Things are more difficult if you need to recover from corruption of101   an index on a system table.  In this case it's important for the102   system to not have used any of the suspect indexes itself.103   (Indeed, in this sort of scenario you might find that server104   processes are crashing immediately at start-up, due to reliance on105   the corrupted indexes.)  To recover safely, the server must be started106   with the <code class="option">-P</code> option, which prevents it from using107   indexes for system catalog lookups.108  </p><p>109   One way to do this is to shut down the server and start a single-user110   <span class="productname">PostgreSQL</span> server111   with the <code class="option">-P</code> option included on its command line.112   Then, <code class="command">REINDEX DATABASE</code>, <code class="command">REINDEX SYSTEM</code>,113   <code class="command">REINDEX TABLE</code>, or <code class="command">REINDEX INDEX</code> can be114   issued, depending on how much you want to reconstruct.  If in115   doubt, use <code class="command">REINDEX SYSTEM</code> to select116   reconstruction of all system indexes in the database.  Then quit117   the single-user server session and restart the regular server.118   See the <a class="xref" href="app-postgres.html" title="postgres"><span class="refentrytitle"><span class="application">postgres</span></span></a> reference page for more119   information about how to interact with the single-user server120   interface.121  </p><p>122   Alternatively, a regular server session can be started with123   <code class="option">-P</code> included in its command line options.124   The method for doing this varies across clients, but in all125   <span class="application">libpq</span>-based clients, it is possible to set126   the <code class="envar">PGOPTIONS</code> environment variable to <code class="literal">-P</code>127   before starting the client.  Note that while this method does not128   require locking out other clients, it might still be wise to prevent129   other users from connecting to the damaged database until repairs130   have been completed.131  </p><p>132   <code class="command">REINDEX</code> is similar to a drop and recreate of the index133   in that the index contents are rebuilt from scratch.  However, the locking134   considerations are rather different.  <code class="command">REINDEX</code> locks out writes135   but not reads of the index's parent table.  It also takes an136   <code class="literal">ACCESS EXCLUSIVE</code> lock on the specific index being processed,137   which will block reads that attempt to use that index. In particular,138   the query planner tries to take an <code class="literal">ACCESS SHARE</code>139   lock on every index of the table, regardless of the query, and so140   <code class="command">REINDEX</code> blocks virtually any queries except for some141   prepared queries whose plan has been cached and which don't use this very142   index. In contrast,143   <code class="command">DROP INDEX</code> momentarily takes an144   <code class="literal">ACCESS EXCLUSIVE</code> lock on the parent table, blocking both145   writes and reads.  The subsequent <code class="command">CREATE INDEX</code> locks out146   writes but not reads; since the index is not there, no read will attempt to147   use it, meaning that there will be no blocking but reads might be forced148   into expensive sequential scans.149  </p><p>150   Reindexing a single index or table requires being the owner of that151   index or table.  Reindexing a schema or database requires being the152   owner of that schema or database.  Note specifically that it's thus153   possible for non-superusers to rebuild indexes of tables owned by154   other users.  However, as a special exception, when155   <code class="command">REINDEX DATABASE</code>, <code class="command">REINDEX SCHEMA</code>156   or <code class="command">REINDEX SYSTEM</code> is issued by a non-superuser,157   indexes on shared catalogs will be skipped unless the user owns the158   catalog (which typically won't be the case).  Of course, superusers159   can always reindex anything.160  </p><p>161   Reindexing partitioned indexes or partitioned tables is supported162   with <code class="command">REINDEX INDEX</code> or <code class="command">REINDEX TABLE</code>,163   respectively. Each partition of the specified partitioned relation is164   reindexed in a separate transaction. Those commands cannot be used inside165   a transaction block when working on a partitioned table or index.166  </p><p>167   When using the <code class="literal">TABLESPACE</code> clause with168   <code class="command">REINDEX</code> on a partitioned index or table, only the169   tablespace references of the leaf partitions are updated. As partitioned170   indexes are not updated, it is recommended to separately use171   <code class="command">ALTER TABLE ONLY</code> on them so as any new partitions172   attached inherit the new tablespace. On failure, it may not have moved173   all the indexes to the new tablespace. Re-running the command will rebuild174   all the leaf partitions and move previously-unprocessed indexes to the new175   tablespace.176  </p><p>177   If <code class="literal">SCHEMA</code>, <code class="literal">DATABASE</code> or178   <code class="literal">SYSTEM</code> is used with <code class="literal">TABLESPACE</code>,179   system relations are skipped and a single <code class="literal">WARNING</code>180   will be generated. Indexes on TOAST tables are rebuilt, but not moved181   to the new tablespace.182  </p><div class="refsect2" id="SQL-REINDEX-CONCURRENTLY"><h3>Rebuilding Indexes Concurrently</h3><a id="id-1.9.3.163.7.11.2" class="indexterm"></a><p>183    Rebuilding an index can interfere with regular operation of a database.184    Normally <span class="productname">PostgreSQL</span> locks the table whose index is rebuilt185    against writes and performs the entire index build with a single scan of the186    table. Other transactions can still read the table, but if they try to187    insert, update, or delete rows in the table they will block until the188    index rebuild is finished. This could have a severe effect if the system is189    a live production database. Very large tables can take many hours to be190    indexed, and even for smaller tables, an index rebuild can lock out writers191    for periods that are unacceptably long for a production system.192   </p><p>193    <span class="productname">PostgreSQL</span> supports rebuilding indexes with minimum locking194    of writes.  This method is invoked by specifying the195    <code class="literal">CONCURRENTLY</code> option of <code class="command">REINDEX</code>. When this option196    is used, <span class="productname">PostgreSQL</span> must perform two scans of the table197    for each index that needs to be rebuilt and wait for termination of198    all existing transactions that could potentially use the index.199    This method requires more total work than a standard index200    rebuild and takes significantly longer to complete as it needs to wait201    for unfinished transactions that might modify the index. However, since202    it allows normal operations to continue while the index is being rebuilt, this203    method is useful for rebuilding indexes in a production environment. Of204    course, the extra CPU, memory and I/O load imposed by the index rebuild205    may slow down other operations.206   </p><p>207    The following steps occur in a concurrent reindex.  Each step is run in a208    separate transaction.  If there are multiple indexes to be rebuilt, then209    each step loops through all the indexes before moving to the next step.210 211    </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>212       A new transient index definition is added to the catalog213       <code class="literal">pg_index</code>.  This definition will be used to replace214       the old index.  A <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock at215       session level is taken on the indexes being reindexed as well as their216       associated tables to prevent any schema modification while processing.217      </p></li><li class="listitem"><p>218       A first pass to build the index is done for each new index.  Once the219       index is built, its flag <code class="literal">pg_index.indisready</code> is220       switched to <span class="quote">“<span class="quote">true</span>”</span> to make it ready for inserts, making it221       visible to other sessions once the transaction that performed the build222       is finished.  This step is done in a separate transaction for each223       index.224      </p></li><li class="listitem"><p>225       Then a second pass is performed to add tuples that were added while the226       first pass was running.  This step is also done in a separate227       transaction for each index.228      </p></li><li class="listitem"><p>229       All the constraints that refer to the index are changed to refer to the230       new index definition, and the names of the indexes are changed.  At231       this point, <code class="literal">pg_index.indisvalid</code> is switched to232       <span class="quote">“<span class="quote">true</span>”</span> for the new index and to <span class="quote">“<span class="quote">false</span>”</span> for233       the old, and a cache invalidation is done causing all sessions that234       referenced the old index to be invalidated.235      </p></li><li class="listitem"><p>236       The old indexes have <code class="literal">pg_index.indisready</code> switched to237       <span class="quote">“<span class="quote">false</span>”</span> to prevent any new tuple insertions, after waiting238       for running queries that might reference the old index to complete.239      </p></li><li class="listitem"><p>240       The old indexes are dropped.  The <code class="literal">SHARE UPDATE241       EXCLUSIVE</code> session locks for the indexes and the table are242       released.243      </p></li></ol></div><p>244   </p><p>245    If a problem arises while rebuilding the indexes, such as a246    uniqueness violation in a unique index, the <code class="command">REINDEX</code>247    command will fail but leave behind an <span class="quote">“<span class="quote">invalid</span>”</span> new index in addition to248    the pre-existing one. This index will be ignored for querying purposes249    because it might be incomplete; however it will still consume update250    overhead. The <span class="application">psql</span> <code class="command">\d</code> command will report251    such an index as <code class="literal">INVALID</code>:252 253</p><pre class="programlisting">254postgres=# \d tab255       Table "public.tab"256 Column |  Type   | Modifiers257--------+---------+-----------258 col    | integer |259Indexes:260    "idx" btree (col)261    "idx_ccnew" btree (col) INVALID262</pre><p>263 264    If the index marked <code class="literal">INVALID</code> is suffixed265    <code class="literal">ccnew</code>, then it corresponds to the transient266    index created during the concurrent operation, and the recommended267    recovery method is to drop it using <code class="literal">DROP INDEX</code>,268    then attempt <code class="command">REINDEX CONCURRENTLY</code> again.269    If the invalid index is instead suffixed <code class="literal">ccold</code>,270    it corresponds to the original index which could not be dropped;271    the recommended recovery method is to just drop said index, since the272    rebuild proper has been successful.273   </p><p>274    Regular index builds permit other regular index builds on the same table275    to occur simultaneously, but only one concurrent index build can occur on a276    table at a time. In both cases, no other types of schema modification on277    the table are allowed meanwhile.  Another difference is that a regular278    <code class="command">REINDEX TABLE</code> or <code class="command">REINDEX INDEX</code>279    command can be performed within a transaction block, but <code class="command">REINDEX280    CONCURRENTLY</code> cannot.281   </p><p>282    Like any long-running transaction, <code class="command">REINDEX</code> on a table283    can affect which tuples can be removed by concurrent284    <code class="command">VACUUM</code> on any other table.285   </p><p>286    <code class="command">REINDEX SYSTEM</code> does not support287    <code class="command">CONCURRENTLY</code> since system catalogs cannot be reindexed288    concurrently.289   </p><p>290    Furthermore, indexes for exclusion constraints cannot be reindexed291    concurrently.  If such an index is named directly in this command, an292    error is raised.  If a table or database with exclusion constraint indexes293    is reindexed concurrently, those indexes will be skipped.  (It is possible294    to reindex such indexes without the <code class="command">CONCURRENTLY</code> option.)295   </p><p>296    Each backend running <code class="command">REINDEX</code> will report its progress297    in the <code class="structname">pg_stat_progress_create_index</code> view. See298    <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="28.4.4. CREATE INDEX Progress Reporting">Section 28.4.4</a> for details.299  </p></div></div><div class="refsect1" id="id-1.9.3.163.8"><h2>Examples</h2><p>300   Rebuild a single index:301 302</p><pre class="programlisting">303REINDEX INDEX my_index;304</pre><p>305  </p><p>306   Rebuild all the indexes on the table <code class="literal">my_table</code>:307 308</p><pre class="programlisting">309REINDEX TABLE my_table;310</pre><p>311  </p><p>312   Rebuild all indexes in a particular database, without trusting the313   system indexes to be valid already:314 315</p><pre class="programlisting">316$ <strong class="userinput"><code>export PGOPTIONS="-P"</code></strong>317$ <strong class="userinput"><code>psql broken_db</code></strong>318...319broken_db=&gt; REINDEX DATABASE broken_db;320broken_db=&gt; \q321</pre><p>322   Rebuild indexes for a table, without blocking read and write operations323   on involved relations while reindexing is in progress:324 325</p><pre class="programlisting">326REINDEX TABLE CONCURRENTLY my_broken_table;327</pre></div><div class="refsect1" id="id-1.9.3.163.9"><h2>Compatibility</h2><p>328   There is no <code class="command">REINDEX</code> command in the SQL standard.329  </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="28.4.4. CREATE INDEX Progress Reporting">Section 28.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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> RELEASE SAVEPOINT</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai