2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v 1.21 2003/09/24 18:54:01 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-REINDEX">
8 <refentrytitle id="SQL-REINDEX-TITLE">REINDEX</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>REINDEX</refname>
14 <refpurpose>rebuild indexes</refpurpose>
17 <indexterm zone="sql-reindex">
18 <primary>REINDEX</primary>
23 REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
28 <title>Description</title>
31 <command>REINDEX</command> rebuilds an index based on the data
32 stored in the table, replacing the old copy of the index. There are
33 two main reasons to use <command>REINDEX</command>:
38 An index has become corrupted, and no longer contains valid
39 data. Although in theory this should never happen, in
40 practice indexes may become corrupted due to software bugs or
41 hardware failures. <command>REINDEX</command> provides a
48 The index in question contains a lot of dead index pages that
49 are not being reclaimed. This can occur with B-tree indexes in
50 <productname>PostgreSQL</productname> under certain access
51 patterns. <command>REINDEX</command> provides a way to reduce
52 the space consumption of the index by writing a new version of
53 the index without the dead pages. See <xref
54 linkend="routine-reindex"> for more information.
62 <title>Parameters</title>
66 <term><literal>DATABASE</literal></term>
69 Recreate all system indexes of a specified database. Indexes on
70 user tables are not processed. Also, indexes on shared system
71 catalogs are skipped except in stand-alone mode (see below).
77 <term><literal>TABLE</literal></term>
80 Recreate all indexes of a specified table. If the table has a
81 secondary <quote>TOAST</> table, that is reindexed as well.
87 <term><literal>INDEX</literal></term>
90 Recreate a specified index.
96 <term><replaceable class="PARAMETER">name</replaceable></term>
99 The name of the specific database, table, or index to be
100 reindexed. Table and index names may be schema-qualified.
106 <term><literal>FORCE</literal></term>
109 This is an obsolete option; it is ignored if specified.
120 If you suspect corruption of an index on a user table, you can
121 simply rebuild that index, or all indexes on the table, using
122 <command>REINDEX INDEX</command> or <command>REINDEX
123 TABLE</command>. Another approach to dealing with a corrupted
124 user-table index is just to drop and recreate it. This may in fact
125 be preferable if you would like to maintain some semblance of
126 normal operation on the table meanwhile. <command>REINDEX</>
127 acquires exclusive lock on the table, while <command>CREATE
128 INDEX</> only locks out writes not reads of the table.
132 Things are more difficult if you need to recover from corruption of
133 an index on a system table. In this case it's important for the
134 system to not have used any of the suspect indexes itself.
135 (Indeed, in this sort of scenario you may find that server
136 processes are crashing immediately at start-up, due to reliance on
137 the corrupted indexes.) To recover safely, the server must be started
138 with the <option>-P</option> option, which prevents it from using
139 indexes for system catalog lookups.
143 One way to do this is to shut down the postmaster and start a stand-alone
144 <productname>PostgreSQL</productname> server
145 with the <option>-P</option> option included on its command line.
146 Then, <command>REINDEX DATABASE</>,
147 <command>REINDEX TABLE</>, or <command>REINDEX INDEX</> can be
148 issued, depending on how much you want to reconstruct. If in
149 doubt, use <command>REINDEX DATABASE</> to select
150 reconstruction of all system indexes in the database. Then quit
151 the standalone server session and restart the regular server.
152 See the <xref linkend="app-postgres"> reference page for more
153 information about how to interact with the stand-alone server
158 Alternatively, a regular server session can be started with
159 <option>-P</option> included in its command line options.
160 The method for doing this varies across clients, but in all
161 <application>libpq</>-based clients, it is possible to set
162 the <envar>PGOPTIONS</envar> environment variable to <literal>-P</>
163 before starting the client. Note that while this method does not
164 require locking out other clients, it may still be wise to prevent
165 other users from connecting to the damaged database until repairs
170 If corruption is suspected in the indexes of any of the shared
171 system catalogs (<structname>pg_database</structname>,
172 <structname>pg_group</structname>, or
173 <structname>pg_shadow</structname>), then a standalone server
174 must be used to repair it. <command>REINDEX</> will not process
175 shared catalogs in multiuser mode.
179 For all indexes except the shared system catalogs, <command>REINDEX</>
180 is crash-safe and transaction-safe. <command>REINDEX</> is not
181 crash-safe for shared indexes, which is why this case is disallowed
182 during normal operation. If a failure occurs while reindexing one
183 of these catalogs in standalone mode, it is important that the failure
184 be rectified and the <command>REINDEX</> operation redone
185 before attempting to restart the regular server.
189 Prior to <productname>PostgreSQL</productname> 7.4, <command>REINDEX
190 TABLE</> did not automatically process TOAST tables, and so those had
191 to be reindexed by separate commands. This is still possible, but
197 <title>Examples</title>
200 Recreate the indexes on the table <literal>my_table</literal>:
203 REINDEX TABLE my_table;
208 Rebuild a single index:
211 REINDEX INDEX my_index;
216 Rebuild all system indexes in a particular database, without trusting them
220 $ <userinput>export PGOPTIONS="-P"</userinput>
221 $ <userinput>psql broken_db</userinput>
223 broken_db=> REINDEX DATABASE broken_db;
230 <title>Compatibility</title>
233 There is no <command>REINDEX</command> command in the SQL standard.
238 <!-- Keep this comment at the end of the file
243 sgml-minimize-attributes:nil
244 sgml-always-quote-attributes:t
247 sgml-parent-document:nil
248 sgml-default-dtd-file:"../reference.ced"
249 sgml-exposed-tags:nil
250 sgml-local-catalogs:"/usr/lib/sgml/catalog"
251 sgml-local-ecat-files:nil