| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/reindex.sgml,v 1.34.2.1 2007/01/05 01:19:04 tgl Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-REINDEX"> |
| <refmeta> |
| <refentrytitle id="SQL-REINDEX-TITLE">REINDEX</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>REINDEX</refname> |
| <refpurpose>rebuild indexes</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-reindex"> |
| <primary>REINDEX</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>REINDEX</command> rebuilds an index using the data |
| stored in the index's table, replacing the old copy of the index. There are |
| several scenarios in which to use <command>REINDEX</command>: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| An index has become corrupted, and no longer contains valid |
| data. Although in theory this should never happen, in |
| practice indexes may become corrupted due to software bugs or |
| hardware failures. <command>REINDEX</command> provides a |
| recovery method. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| An index has become <quote>bloated</>, that it is contains many |
| empty or nearly-empty pages. This can occur with B-tree indexes in |
| <productname>PostgreSQL</productname> under certain uncommon access |
| patterns. <command>REINDEX</command> provides a way to reduce |
| the space consumption of the index by writing a new version of |
| the index without the dead pages. See <xref |
| linkend="routine-reindex"> for more information. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| You have altered a storage parameter (such as fillfactor) |
| for an index, and wish to ensure that the change has taken full effect. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| An index build with the <literal>CONCURRENTLY</> option failed, leaving |
| an <quote>invalid</> index. Such indexes are useless but it can be |
| convenient to use <command>REINDEX</> to rebuild them. Note that |
| <command>REINDEX</> will not perform a concurrent build. To build the |
| index without interfering with production you should drop the index and |
| reissue the <command>CREATE INDEX CONCURRENTLY</> command. |
| </para> |
| </listitem> |
| |
| </itemizedlist> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>INDEX</literal></term> |
| <listitem> |
| <para> |
| Recreate the specified index. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>TABLE</literal></term> |
| <listitem> |
| <para> |
| Recreate all indexes of the specified table. If the table has a |
| secondary <quote>TOAST</> table, that is reindexed as well. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>DATABASE</literal></term> |
| <listitem> |
| <para> |
| Recreate all indexes within the current database. |
| Indexes on shared system catalogs are skipped except in stand-alone mode |
| (see below). This form of <command>REINDEX</command> cannot be executed |
| inside a transaction block. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SYSTEM</literal></term> |
| <listitem> |
| <para> |
| Recreate all indexes on system catalogs within the current database. |
| Indexes on user tables are not processed. Also, indexes on shared |
| system catalogs are skipped except in stand-alone mode (see below). |
| This form of <command>REINDEX</command> cannot be executed inside a |
| transaction block. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">name</replaceable></term> |
| <listitem> |
| <para> |
| The name of the specific index, table, or database to be |
| reindexed. Index and table names may be schema-qualified. |
| Presently, <command>REINDEX DATABASE</> and <command>REINDEX SYSTEM</> |
| can only reindex the current database, so their parameter must match |
| the current database's name. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>FORCE</literal></term> |
| <listitem> |
| <para> |
| This is an obsolete option; it is ignored if specified. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| If you suspect corruption of an index on a user table, you can |
| simply rebuild that index, or all indexes on the table, using |
| <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>. |
| </para> |
| |
| <para> |
| Things are more difficult if you need to recover from corruption of |
| an index on a system table. In this case it's important for the |
| system to not have used any of the suspect indexes itself. |
| (Indeed, in this sort of scenario you may find that server |
| processes are crashing immediately at start-up, due to reliance on |
| the corrupted indexes.) To recover safely, the server must be started |
| with the <option>-P</option> option, which prevents it from using |
| indexes for system catalog lookups. |
| </para> |
| |
| <para> |
| One way to do this is to shut down the server and start a single-user |
| <productname>PostgreSQL</productname> server |
| with the <option>-P</option> option included on its command line. |
| Then, <command>REINDEX DATABASE</>, <command>REINDEX SYSTEM</>, |
| <command>REINDEX TABLE</>, or <command>REINDEX INDEX</> can be |
| issued, depending on how much you want to reconstruct. If in |
| doubt, use <command>REINDEX SYSTEM</> to select |
| reconstruction of all system indexes in the database. Then quit |
| the single-user server session and restart the regular server. |
| See the <xref linkend="app-postgres"> reference page for more |
| information about how to interact with the single-user server |
| interface. |
| </para> |
| |
| <para> |
| Alternatively, a regular server session can be started with |
| <option>-P</option> included in its command line options. |
| The method for doing this varies across clients, but in all |
| <application>libpq</>-based clients, it is possible to set |
| the <envar>PGOPTIONS</envar> environment variable to <literal>-P</> |
| before starting the client. Note that while this method does not |
| require locking out other clients, it may still be wise to prevent |
| other users from connecting to the damaged database until repairs |
| have been completed. |
| </para> |
| |
| <para> |
| If corruption is suspected in the indexes of any of the shared |
| system catalogs (which are <structname>pg_authid</structname>, |
| <structname>pg_auth_members</structname>, |
| <structname>pg_database</structname>, |
| <structname>pg_pltemplate</structname>, |
| <structname>pg_shdepend</structname>, |
| <structname>pg_shdescription</structname>, and |
| <structname>pg_tablespace</structname>), then a standalone server |
| must be used to repair it. <command>REINDEX</> will not process |
| shared catalogs in multiuser mode. |
| </para> |
| |
| <para> |
| For all indexes except the shared system catalogs, <command>REINDEX</> |
| is crash-safe and transaction-safe. <command>REINDEX</> is not |
| crash-safe for shared indexes, which is why this case is disallowed |
| during normal operation. If a failure occurs while reindexing one |
| of these catalogs in standalone mode, it will not be possible to |
| restart the regular server until the problem is rectified. (The |
| typical symptom of a partially rebuilt shared index is <quote>index is not |
| a btree</> errors.) |
| </para> |
| |
| <para> |
| <command>REINDEX</command> is similar to a drop and recreate of the index |
| in that the index contents are rebuilt from scratch. However, the locking |
| considerations are rather different. <command>REINDEX</> locks out writes |
| but not reads of the index's parent table. It also takes an exclusive lock |
| on the specific index being processed, which will block reads that attempt |
| to use that index. In contrast, <command>DROP INDEX</> momentarily takes |
| exclusive lock on the parent table, blocking both writes and reads. The |
| subsequent <command>CREATE INDEX</> locks out writes but not reads; since |
| the index is not there, no read will attempt to use it, meaning that there |
| will be no blocking but reads may be forced into expensive sequential |
| scans. Another important point is that the drop/create approach |
| invalidates any cached query plans that use the index, while |
| <command>REINDEX</> does not. |
| </para> |
| |
| <para> |
| Reindexing a single index or table requires being the owner of that |
| index or table. Reindexing a database requires being the owner of |
| the database (note that the owner can therefore rebuild indexes of |
| tables owned by other users). Of course, superusers can always |
| reindex anything. |
| </para> |
| |
| <para> |
| Prior to <productname>PostgreSQL</productname> 8.1, <command>REINDEX |
| DATABASE</> processed only system indexes, not all indexes as one would |
| expect from the name. This has been changed to reduce the surprise |
| factor. The old behavior is available as <command>REINDEX SYSTEM</>. |
| </para> |
| |
| <para> |
| Prior to <productname>PostgreSQL</productname> 7.4, <command>REINDEX |
| TABLE</> did not automatically process TOAST tables, and so those had |
| to be reindexed by separate commands. This is still possible, but |
| redundant. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| Rebuild a single index: |
| |
| <programlisting> |
| REINDEX INDEX my_index; |
| </programlisting> |
| </para> |
| |
| <para> |
| Rebuild all the indexes on the table <literal>my_table</literal>: |
| |
| <programlisting> |
| REINDEX TABLE my_table; |
| </programlisting> |
| </para> |
| |
| <para> |
| Rebuild all indexes in a particular database, without trusting the |
| system indexes to be valid already: |
| |
| <programlisting> |
| $ <userinput>export PGOPTIONS="-P"</userinput> |
| $ <userinput>psql broken_db</userinput> |
| ... |
| broken_db=> REINDEX DATABASE broken_db; |
| broken_db=> \q |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| There is no <command>REINDEX</command> command in the SQL standard. |
| </para> |
| </refsect1> |
| </refentry> |