diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 7222665..6d2cc53 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
+REINDEX { INDEX | TABLE | DATABASE | SYSTEM } [ CONCURRENTLY ] name [ FORCE ]
@@ -68,9 +68,12 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } nam
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.
+ REINDEX will perform a concurrent build if
+ CONCURRENTLY is specified. To build the index without interfering
+ with production you should drop the index and reissue either the
+ CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY
+ command. Indexes of toast relations can be rebuilt with REINDEX
+ CONCURRENTLY.
@@ -139,6 +142,21 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } nam
+ 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 reindex build
+ 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 .
+
+
+
+
+
FORCE
@@ -231,6 +249,111 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } nam
to be reindexed by separate commands. This is still possible, but
redundant.
+
+
+
+ Rebuilding Indexes Concurrently
+
+
+ index
+ rebuilding 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 without locking
+ out 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 rebuild and in
+ addition it must wait for all existing transactions that could potentially
+ use the index to terminate. This method requires more total work than a
+ standard index rebuild and takes significantly longer to complete as it
+ needs to wait for unfinished transactiions that might modify the index.
+ However, since it allows normal operations to continue while the index
+ is 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 might slow other operations.
+
+
+
+ In a concurrent index build, a new index that will replace the one to
+ be rebuild is actually entered into the system catalogs in one transaction,
+ then two table scans occur in two more transactions and to make the new
+ index valid from the other backends. Once this is performed, the old
+ and fresh indexes are swapped in, and the old index is marked as invalid
+ in a third transaction. Finally two additional transactions are used to mark
+ the old index as not ready and then drop it.
+
+
+
+ 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 on top
+ of the 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_cct" btree (col) INVALID
+
+
+ The recommended recovery method in such cases is to drop the concurrent
+ index and try again to perform REINDEX CONCURRENTLY once again.
+ The concurrent index created during the processing has a name finishing by
+ the suffix cct. This works as well with indexes of toast relations.
+
+
+
+ Regular index builds permit other regular index builds on the
+ same table to occur in parallel, 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. REINDEX DATABASE is
+ by default not allowed to run inside a transaction block, so in this case
+ CONCURRENTLY is not supported.
+
+
+
+ Invalid indexes of toast relations can be dropped if a failure occurred
+ during REINDEX CONCURRENTLY. Live indexes of toast relations
+ cannot be dropped.
+
+
+
+ REINDEX DATABASE used with CONCURRENTLY
+ rebuilds concurrently only the non-system relations. System
+ relations are rebuilt with a non-concurrent context. Toast indexes are
+ rebuilt concurrently if the relation they depend on is a non-system
+ relation.
+
+
+
+ REINDEX SYSTEM does not support CONCURRENTLY
+ .
+
+
@@ -262,7 +385,17 @@ $ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q
-
+
+
+
+
+ Rebuild a table concurrently:
+
+
+REINDEX TABLE CONCURRENTLY my_broken_table;
+
+
+