2 doc/src/sgml/ref/reindex.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-REINDEX">
8 <refentrytitle>REINDEX</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>REINDEX</refname>
15 <refpurpose>rebuild indexes</refpurpose>
18 <indexterm zone="sql-reindex">
19 <primary>REINDEX</primary>
24 REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
29 <title>Description</title>
32 <command>REINDEX</command> rebuilds an index using the data
33 stored in the index's table, replacing the old copy of the index. There are
34 several scenarios in which to use <command>REINDEX</command>:
39 An index has become corrupted, and no longer contains valid
40 data. Although in theory this should never happen, in
41 practice indexes can become corrupted due to software bugs or
42 hardware failures. <command>REINDEX</command> provides a
49 An index has become <quote>bloated</>, that it is contains many
50 empty or nearly-empty pages. This can occur with B-tree indexes in
51 <productname>PostgreSQL</productname> under certain uncommon access
52 patterns. <command>REINDEX</command> provides a way to reduce
53 the space consumption of the index by writing a new version of
54 the index without the dead pages. See <xref
55 linkend="routine-reindex"> for more information.
61 You have altered a storage parameter (such as fillfactor)
62 for an index, and wish to ensure that the change has taken full effect.
68 An index build with the <literal>CONCURRENTLY</> option failed, leaving
69 an <quote>invalid</> index. Such indexes are useless but it can be
70 convenient to use <command>REINDEX</> to rebuild them. Note that
71 <command>REINDEX</> will not perform a concurrent build. To build the
72 index without interfering with production you should drop the index and
73 reissue the <command>CREATE INDEX CONCURRENTLY</> command.
77 </itemizedlist></para>
81 <title>Parameters</title>
85 <term><literal>INDEX</literal></term>
88 Recreate the specified index.
94 <term><literal>TABLE</literal></term>
97 Recreate all indexes of the specified table. If the table has a
98 secondary <quote>TOAST</> table, that is reindexed as well.
104 <term><literal>DATABASE</literal></term>
107 Recreate all indexes within the current database.
108 Indexes on shared system catalogs are also processed.
109 This form of <command>REINDEX</command> cannot be executed inside a
116 <term><literal>SYSTEM</literal></term>
119 Recreate all indexes on system catalogs within the current database.
120 Indexes on shared system catalogs are included.
121 Indexes on user tables are not processed.
122 This form of <command>REINDEX</command> cannot be executed inside a
129 <term><replaceable class="PARAMETER">name</replaceable></term>
132 The name of the specific index, table, or database to be
133 reindexed. Index and table names can be schema-qualified.
134 Presently, <command>REINDEX DATABASE</> and <command>REINDEX SYSTEM</>
135 can only reindex the current database, so their parameter must match
136 the current database's name.
142 <term><literal>FORCE</literal></term>
145 This is an obsolete option; it is ignored if specified.
156 If you suspect corruption of an index on a user table, you can
157 simply rebuild that index, or all indexes on the table, using
158 <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>.
162 Things are more difficult if you need to recover from corruption of
163 an index on a system table. In this case it's important for the
164 system to not have used any of the suspect indexes itself.
165 (Indeed, in this sort of scenario you might find that server
166 processes are crashing immediately at start-up, due to reliance on
167 the corrupted indexes.) To recover safely, the server must be started
168 with the <option>-P</option> option, which prevents it from using
169 indexes for system catalog lookups.
173 One way to do this is to shut down the server and start a single-user
174 <productname>PostgreSQL</productname> server
175 with the <option>-P</option> option included on its command line.
176 Then, <command>REINDEX DATABASE</>, <command>REINDEX SYSTEM</>,
177 <command>REINDEX TABLE</>, or <command>REINDEX INDEX</> can be
178 issued, depending on how much you want to reconstruct. If in
179 doubt, use <command>REINDEX SYSTEM</> to select
180 reconstruction of all system indexes in the database. Then quit
181 the single-user server session and restart the regular server.
182 See the <xref linkend="app-postgres"> reference page for more
183 information about how to interact with the single-user server
188 Alternatively, a regular server session can be started with
189 <option>-P</option> included in its command line options.
190 The method for doing this varies across clients, but in all
191 <application>libpq</>-based clients, it is possible to set
192 the <envar>PGOPTIONS</envar> environment variable to <literal>-P</>
193 before starting the client. Note that while this method does not
194 require locking out other clients, it might still be wise to prevent
195 other users from connecting to the damaged database until repairs
200 <command>REINDEX</command> is similar to a drop and recreate of the index
201 in that the index contents are rebuilt from scratch. However, the locking
202 considerations are rather different. <command>REINDEX</> locks out writes
203 but not reads of the index's parent table. It also takes an exclusive lock
204 on the specific index being processed, which will block reads that attempt
205 to use that index. In contrast, <command>DROP INDEX</> momentarily takes
206 exclusive lock on the parent table, blocking both writes and reads. The
207 subsequent <command>CREATE INDEX</> locks out writes but not reads; since
208 the index is not there, no read will attempt to use it, meaning that there
209 will be no blocking but reads might be forced into expensive sequential
214 Reindexing a single index or table requires being the owner of that
215 index or table. Reindexing a database requires being the owner of
216 the database (note that the owner can therefore rebuild indexes of
217 tables owned by other users). Of course, superusers can always
222 Prior to <productname>PostgreSQL</productname> 8.1, <command>REINDEX
223 DATABASE</> processed only system indexes, not all indexes as one would
224 expect from the name. This has been changed to reduce the surprise
225 factor. The old behavior is available as <command>REINDEX SYSTEM</>.
229 Prior to <productname>PostgreSQL</productname> 7.4, <command>REINDEX
230 TABLE</> did not automatically process TOAST tables, and so those had
231 to be reindexed by separate commands. This is still possible, but
237 <title>Examples</title>
240 Rebuild a single index:
243 REINDEX INDEX my_index;
248 Rebuild all the indexes on the table <literal>my_table</literal>:
251 REINDEX TABLE my_table;
256 Rebuild all indexes in a particular database, without trusting the
257 system indexes to be valid already:
260 $ <userinput>export PGOPTIONS="-P"</userinput>
261 $ <userinput>psql broken_db</userinput>
263 broken_db=> REINDEX DATABASE broken_db;
265 </programlisting></para>
269 <title>Compatibility</title>
272 There is no <command>REINDEX</command> command in the SQL standard.