codekingpro/portable-devtools
115k
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=> REINDEX DATABASE broken_db;320broken_db=> \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>