Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug


       REINDEX - rebuild indexes




       REINDEX  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 REINDEX:

       · An index has become corrupted, and no longer contains valid  data.  Although  in  theory
         this  should never happen, in practice indexes can become corrupted due to software bugs
         or hardware failures. REINDEX provides a recovery method.

       · An index has become ``bloated'', that it is contains many empty or  nearly-empty  pages.
         This can occur with B-tree indexes in PostgreSQL under certain uncommon 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  in  the documentation for more

       · You have altered a storage parameter (such as fillfactor) for  an  index,  and  wish  to
         ensure that the change has taken full effect.

       · An  index  build with the CONCURRENTLY option failed, leaving an ``invalid'' index. Such
         indexes are useless but it can be convenient to use REINDEX to rebuild them.  Note  that
         REINDEX will not perform a concurrent build. To build the index without interfering with
         production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command.


       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.

              Recreate  all  indexes  within  the  current  database.   Indexes  on shared system
              catalogs are skipped except in stand-alone mode (see below). This form  of  REINDEX
              cannot be executed inside a transaction block.

       SYSTEM 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 REINDEX cannot be executed
              inside a transaction block.

       name   The name of the specific index, table, or database to be reindexed. Index and table
              names  can 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.


       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 might 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 server and start a single-user PostgreSQL server
       with the -P option included on its command line.  Then, REINDEX DATABASE, REINDEX  SYSTEM,
       REINDEX  TABLE,  or  REINDEX  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 single-user server session and restart the regular
       server.  See the postgres(1) reference page for more information  about  how  to  interact
       with the single-user 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
       might  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_pltemplate, pg_shdepend, pg_shdescription, 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
       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 ``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 considerations are rather different. 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, 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 might be forced into expensive sequential scans.

       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.

       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 available 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.


       Rebuild a single index:

       REINDEX INDEX my_index;

       Rebuild all the indexes on the table my_table:

       REINDEX TABLE my_table;

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

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


       There is no REINDEX command in the SQL standard.