REINDEX SQL - Language Statements REINDEX rebuild corrupted indexes 2000-03-30 REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ] 2000-03-30 DATABASE Recreate all system indexes of a specified database. Indexes on user tables are not included. This form of REINDEX can only be used in standalone mode (see below). TABLE Recreate all indexes of a specified table. INDEX Recreate a specified index. name The name of the specific database/table/index to be reindexed. Table and index names may be schema-qualified. FORCE Force rebuild of system indexes. Without this keyword REINDEX skips system indexes that are not marked invalid. FORCE is irrelevant for REINDEX INDEX, or when reindexing user indexes. 2000-03-30 REINDEX Message returned if the table is successfully reindexed. 2000-03-30 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: An index has become corrupted, and no longer contains valid data. Although in theory this should never be necessary, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method. The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B+-tree indexes 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 for more information. The rest of this reference page mostly discusses how to use REINDEX to recover from index corruption. 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 backend doing the recovery to not have used any of the suspect indexes itself. (Indeed, in this sort of scenario you may find that backends are crashing immediately at start-up, due to reliance on the corrupted indexes.) To recover safely, the postmaster must be shut down and a stand-alone PostgreSQL backend must be started instead, giving it the command-line options -O and -P (these options allow system table modifications and prevent use of system indexes, respectively). Then issue REINDEX DATABASE, REINDEX TABLE, REINDEX INDEX, or depending on how much you want to reconstruct. If in doubt, use REINDEX DATABASE FORCE to force reconstruction of all system indexes in the database. Then quit the standalone backend and restart the postmaster. Since this is likely the only situation when most people will ever use a standalone backend, some usage notes might be in order: Start the backend with a command like postgres -D $PGDATA -O -P my_database Provide the correct path to the database area with -D, or make sure that the environment variable PGDATA is set. Also specify the name of the particular database you want to work in. You can issue any SQL command, not only REINDEX. Be aware that the standalone backend treats newline as the command entry terminator; there is no intelligence about semicolons, as there is in psql. To continue a command across multiple lines, you must type backslash just before each newline except the last one. Also, you won't have any of the conveniences of command-line editing (no command history, for example). To quit the backend, type EOF (ControlD, usually). See the reference page for more information. Recreate the indexes on the table mytable: REINDEX TABLE mytable; Rebuild a single index: REINDEX INDEX my_index; Rebuild all system indexes (this will only work in a standalone backend): REINDEX DATABASE my_database FORCE; 2000-03-30 There is no REINDEX in SQL92.