Man Linux: Main Page and Category List


       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

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

       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

       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

       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.