REINDEX(l)

NAME

       REINDEX - rebuild indexes

SYNOPSIS

       REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

DESCRIPTION

       REINDEX  rebuilds  an  index  based  on  the  data stored in the table,
       replacing the old copy of the index. There are two main reasons to  use
       REINDEX:

       o 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. REINDEX
         provides a recovery method.

       o The index in question contains a lot of dead index pages that are not
         being  reclaimed.  This  can  occur with B-tree indexes in PostgreSQL
         under certain access patterns. REINDEX provides a way to  reduce  the
         space  consumption of the index by writing a new version of the index
         without the dead pages. See the section called  ``Routine  Indexing''
         in the documentation for more information.

PARAMETERS

       DATABASE
              Recreate  all system indexes of a specified database. Indexes on
              user tables are not processed. Also, indexes  on  shared  system
              catalogs are skipped except in stand-alone mode (see below).

       TABLE  Recreate  all  indexes  of a specified table. If the table has a
              secondary ``TOAST'' table, that is reindexed as well.

       INDEX  Recreate a specified index.

       name   The name of the specific database, table, or index to  be  rein-
              dexed. Table and index names may be schema-qualified.

       FORCE  This is an obsolete option; it is ignored if specified.

NOTES

       If  you  suspect corruption of an index on a user table, you can simply
       rebuild that index, or all indexes on the table, using REINDEX INDEX or
       REINDEX  TABLE. Another approach to dealing with a corrupted user-table
       index is just to drop and recreate it. This may in fact  be  preferable
       if you would like to maintain some semblance of normal operation on the
       table meanwhile. REINDEX acquires exclusive lock on  the  table,  while
       CREATE INDEX only locks out writes not reads of the table.

       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 -P option,  which  prevents
       it from using indexes for system catalog lookups.

       One  way  to  do this is to shut down the postmaster and start a stand-
       alone PostgreSQL server with the -P  option  included  on  its  command
       line.   Then,  REINDEX DATABASE, REINDEX TABLE, or REINDEX INDEX can be
       issued, depending on how much you want to reconstruct. If in doubt, use
       REINDEX  DATABASE to select reconstruction of all system indexes in the
       database. Then quit the standalone server session and restart the regu-
       lar  server.   See  the postgres(1) reference page for more information
       about how to interact with the stand-alone server interface.

       Alternatively, a regular server session can be started with -P included
       in  its  command line options.  The method for doing this varies across
       clients, but in all libpq-based clients, it  is  possible  to  set  the
       PGOPTIONS  environment  variable to -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.

       If corruption is suspected in the indexes of any of the  shared  system
       catalogs  (pg_database,  pg_group,  or  pg_shadow),  then  a standalone
       server must be used to repair it. REINDEX will not process shared cata-
       logs in multiuser mode.

       For  all  indexes  except the shared system catalogs, REINDEX is crash-
       safe  and  transaction-safe.  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  stand-
       alone 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 ``index is not a btree'' errors.)

       Prior  to  PostgreSQL  7.4, REINDEX TABLE did not automatically process
       TOAST tables, and so those had to be reindexed  by  separate  commands.
       This is still possible, but redundant.

EXAMPLES

       Recreate the indexes on the table my_table:

       REINDEX TABLE my_table;

       Rebuild a single index:

       REINDEX INDEX my_index;

       Rebuild  all  system indexes in a particular database, without trusting
       them to be valid already:

       $ export PGOPTIONS="-P"
       $ psql broken_db
       broken_db=> REINDEX DATABASE broken_db;
       broken_db=> \q

COMPATIBILITY

       There is no REINDEX command in the SQL standard.

SQL - Language Statements         2003-11-02                        REINDEX(l)