REINDEX

REINDEX
Prev	Up	SQL Commands	Home	Next

REINDEX — rebuild indexes

Synopsis

+REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
+
+where option can be one of:
+
+    VERBOSE
+

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

+ 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 is it 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 Section 24.2 for more information. +
+ You have altered a storage parameter (such as fillfactor) + for an index, and wish to ensure that the change has taken full effect. +
+ If an index build fails with the CONCURRENTLY option, + this index is left as “invalid”. Such indexes are useless + but it can be convenient to use REINDEX to rebuild + them. Note that only REINDEX INDEX is able + to perform a concurrent build on an invalid index. +

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

SCHEMA

+ Recreate all indexes of the specified schema. If a table of this + schema has a secondary “TOAST” table, that is reindexed as + well. Indexes on shared system catalogs are also processed. + This form of REINDEX cannot be executed inside a + transaction block. +

DATABASE

+ Recreate all indexes within the current database. + Indexes on shared system catalogs are also processed. + This form of REINDEX cannot be executed inside a + transaction block. +

SYSTEM

+ Recreate all indexes on system catalogs within the current database. + Indexes on shared system catalogs are included. + Indexes on user tables are not processed. + 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. +

CONCURRENTLY

+ When this option is used, PostgreSQL will rebuild the + index without taking any locks that prevent concurrent inserts, + updates, or deletes on the table; whereas a standard index rebuild + locks out writes (but not reads) on the table until it's done. + There are several caveats to be aware of when using this option + — see Rebuilding Indexes Concurrently below. +

+ For temporary tables, REINDEX is always + non-concurrent, as no other session can access them, and + non-concurrent reindex is cheaper. +

VERBOSE

+ Prints a progress report as each index is reindexed. +

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

+ 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 + ACCESS EXCLUSIVE lock on the specific index being processed, + which will block reads that attempt to use that index. In contrast, + DROP INDEX momentarily takes an + ACCESS 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 schema or database requires being the + owner of that schema or database. Note specifically that it's thus + possible for non-superusers to rebuild indexes of tables owned by + other users. However, as a special exception, when + REINDEX DATABASE, REINDEX SCHEMA + or REINDEX SYSTEM is issued by a non-superuser, + indexes on shared catalogs will be skipped unless the user owns the + catalog (which typically won't be the case). Of course, superusers + can always reindex anything. +

+ Reindexing partitioned tables or partitioned indexes is not supported. + Each individual partition can be reindexed separately instead. +

Rebuilding Indexes Concurrently

+ Rebuilding an index can interfere with regular operation of a database. + Normally PostgreSQL locks the table whose index is rebuilt + against writes and performs the entire index build with a single scan of the + table. Other transactions can still read the table, but if they try to + insert, update, or delete rows in the table they will block until the + index rebuild is finished. This could have a severe effect if the system is + a live production database. Very large tables can take many hours to be + indexed, and even for smaller tables, an index rebuild can lock out writers + for periods that are unacceptably long for a production system. +

+ PostgreSQL supports rebuilding indexes with minimum locking + of writes. This method is invoked by specifying the + CONCURRENTLY option of REINDEX. When this option + is used, PostgreSQL must perform two scans of the table + for each index that needs to be rebuilt and wait for termination of + all existing transactions that could potentially use the index. + This method requires more total work than a standard index + rebuild and takes significantly longer to complete as it needs to wait + for unfinished transactions that might modify the index. However, since + it allows normal operations to continue while the index is being rebuilt, this + method is useful for rebuilding indexes in a production environment. Of + course, the extra CPU, memory and I/O load imposed by the index rebuild + may slow down other operations. +

+ The following steps occur in a concurrent reindex. Each step is run in a + separate transaction. If there are multiple indexes to be rebuilt, then + each step loops through all the indexes before moving to the next step. + +

+ A new transient index definition is added to the catalog + pg_index. This definition will be used to replace + the old index. A SHARE UPDATE EXCLUSIVE lock at + session level is taken on the indexes being reindexed as well as their + associated tables to prevent any schema modification while processing. +
+ A first pass to build the index is done for each new index. Once the + index is built, its flag pg_index.indisready is + switched to “true” to make it ready for inserts, making it + visible to other sessions once the transaction that performed the build + is finished. This step is done in a separate transaction for each + index. +
+ Then a second pass is performed to add tuples that were added while the + first pass was running. This step is also done in a separate + transaction for each index. +
+ All the constraints that refer to the index are changed to refer to the + new index definition, and the names of the indexes are changed. At + this point, pg_index.indisvalid is switched to + “true” for the new index and to “false” for + the old, and a cache invalidation is done causing all sessions that + referenced the old index to be invalidated. +
+ The old indexes have pg_index.indisready switched to + “false” to prevent any new tuple insertions, after waiting + for running queries that might reference the old index to complete. +
+ The old indexes are dropped. The SHARE UPDATE + EXCLUSIVE session locks for the indexes and the table are + released. +

+ If a problem arises while rebuilding the indexes, such as a + uniqueness violation in a unique index, the REINDEX + command will fail but leave behind an “invalid” new index in addition to + the pre-existing one. This index will be ignored for querying purposes + because it might be incomplete; however it will still consume update + overhead. The psql \d command will report + such an index as INVALID: + +

+postgres=# \d tab
+       Table "public.tab"
+ Column |  Type   | Modifiers
+--------+---------+-----------
+ col    | integer |
+Indexes:
+    "idx" btree (col)
+    "idx_ccnew" btree (col) INVALID
+

+ + If the index marked INVALID is suffixed + ccnew, then it corresponds to the transient + index created during the concurrent operation, and the recommended + recovery method is to drop it using DROP INDEX, + then attempt REINDEX CONCURRENTLY again. + If the invalid index is instead suffixed ccold, + it corresponds to the original index which could not be dropped; + the recommended recovery method is to just drop said index, since the + rebuild proper has been successful. +

+ Regular index builds permit other regular index builds on the same table + to occur simultaneously, but only one concurrent index build can occur on a + table at a time. In both cases, no other types of schema modification on + the table are allowed meanwhile. Another difference is that a regular + REINDEX TABLE or REINDEX INDEX + command can be performed within a transaction block, but REINDEX + CONCURRENTLY cannot. +

+ Like any long-running transaction, REINDEX on a table + can affect which tuples can be removed by concurrent + VACUUM on any other table. +

+ REINDEX SYSTEM does not support + CONCURRENTLY since system catalogs cannot be reindexed + concurrently. +

+ Furthermore, indexes for exclusion constraints cannot be reindexed + concurrently. If such an index is named directly in this command, an + error is raised. If a table or database with exclusion constraint indexes + is reindexed concurrently, those indexes will be skipped. (It is possible + to reindex such indexes without the CONCURRENTLY option.) +

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
+

+ Rebuild indexes for a table, without blocking read and write operations + on involved relations while reindexing is in progress: + +

+REINDEX TABLE CONCURRENTLY my_broken_table;
+

Compatibility

+ There is no REINDEX command in the SQL standard. +

Prev	Up	Next
REFRESH MATERIALIZED VIEW	Home	RELEASE SAVEPOINT