/usr/man2/cat.l/reindex.l.Z(/usr/man2/cat.l/reindex.l.Z)

NAME

       REINDEX - rebuild indexes

SYNOPSIS

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

DESCRIPTION

       REINDEX  rebuilds  an index using the data stored in the index's 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 documentation for more information.

PARAMETERS

       INDEX  Recreate the specified index.

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

       DATABASE
              Recreate  all  indexes  within the current database.  Indexes on
              shared system catalogs are skipped except  in  stand-alone  mode
              (see below).

       SYSTEM Recreate all indexes on system catalogs within the current data-
              base.  Indexes on user tables are not processed.  Also,  indexes
              on shared system catalogs are skipped except in stand-alone mode
              (see below).

       name   The name of the specific index, table, or database to  be  rein-
              dexed.   Index   and   table   names  may  be  schema-qualified.
              Presently, REINDEX DATABASE and REINDEX SYSTEM can only  reindex
              the  current database, so their parameter must match the current
              database's name.

       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.

       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 SYSTEM, REINDEX TABLE, or  REIN-
       DEX INDEX can be issued, depending on how much you want to reconstruct.
       If in doubt, use REINDEX SYSTEM to select reconstruction of all  system
       indexes  in  the  database. Then quit the standalone server session and
       restart the regular 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  (which are pg_authid, pg_auth_members, pg_database, pg_pltem-
       plate, pg_shdepend, and pg_tablespace), then a standalone  server  must
       be  used  to  repair  it.  REINDEX  will not process shared catalogs 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.)

       REINDEX is similar to a drop and recreate of  the  index  in  that  the
       index contents are rebuilt from scratch. However, the locking consider-
       ations are rather different. REINDEX locks out writes but not reads  of
       the  index's  parent table. It also takes an exclusive lock on the spe-
       cific index being processed, which will block reads that attempt to use
       that index. In contrast, DROP INDEX momentarily takes exclusive lock on
       the parent table, blocking both writes and reads. The subsequent 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 REINDEX does not.

       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  any-
       thing.

       Prior  to  PostgreSQL  8.1,  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 avail-
       able as REINDEX SYSTEM.

       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 indexes in a particular database, without trusting the sys-
       tem indexes 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         2005-11-05                         REINDEX()