reindex(5)
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 several scenarios in which 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 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 information.
o You have altered a storage parameter (such as fillfac-
tor) for an index, and wish to ensure that the change
has taken full effect.
o 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.
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). 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 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 speci-
fied.
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 cor-
ruption of an index on a system table. In this case it's
important for the system to not have used any of the sus-
pect indexes itself. (Indeed, in this sort of scenario
you may find that server processes are crashing immedi-
ately 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 SYS-
TEM, 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 envi-
ronment 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_mem-
bers, pg_database, pg_pltemplate, pg_shdepend, pg_shde-
scription, 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 disal-
lowed 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 par-
tially 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 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
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 automati-
cally process TOAST tables, and so those had to be rein-
dexed by separate commands. This is still possible, but
redundant.
EXAMPLES
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
COMPATIBILITY
There is no REINDEX command in the SQL standard.
SQL - Language Statements 2008-01-03 REINDEX()
Man(1) output converted with
man2html