From 293913568e6a7a86fd1479e1cff8e2ecb58d6568 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 15:44:03 +0200 Subject: Adding upstream version 16.2. Signed-off-by: Daniel Baumann --- doc/src/sgml/ref/reindex.sgml | 562 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 562 insertions(+) create mode 100644 doc/src/sgml/ref/reindex.sgml (limited to 'doc/src/sgml/ref/reindex.sgml') diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml new file mode 100644 index 0000000..21e2e91 --- /dev/null +++ b/doc/src/sgml/ref/reindex.sgml @@ -0,0 +1,562 @@ + + + + + REINDEX + + + + REINDEX + 7 + SQL - Language Statements + + + + REINDEX + rebuild indexes + + + + +REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name +REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ] + +where option can be one of: + + CONCURRENTLY [ boolean ] + TABLESPACE new_tablespace + VERBOSE [ boolean ] + + + + + 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 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. This form of REINDEX + cannot be executed inside a transaction block when used with a + partitioned index. + + + + + + TABLE + + + Recreate all indexes of the specified table. If the table has a + secondary TOAST table, that is reindexed as well. + This form of REINDEX cannot be executed inside a + transaction block when used with a partitioned table. + + + + + + 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, except system + catalogs. + Indexes on system catalogs are not 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. Their parameter is optional, + and it 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 below. + + + For temporary tables, REINDEX is always + non-concurrent, as no other session can access them, and + non-concurrent reindex is cheaper. + + + + + + TABLESPACE + + + Specifies that indexes will be rebuilt on a new tablespace. + + + + + + VERBOSE + + + Prints a progress report as each index is reindexed. + + + + + + boolean + + + Specifies whether the selected option should be turned on or off. + You can write TRUE, ON, or + 1 to enable the option, and FALSE, + OFF, or 0 to disable it. The + boolean value can also + be omitted, in which case TRUE is assumed. + + + + + + new_tablespace + + + The tablespace where indexes will be rebuilt. + + + + + + + + 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 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 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 reference page for more + information about how to interact with the single-user server + interface. + + + + Alternatively, a regular server session can be started with + 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 particular, + the query planner tries to take an ACCESS SHARE + lock on every index of the table, regardless of the query, and so + REINDEX blocks virtually any queries except for some + prepared queries whose plan has been cached and which don't use this very + 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 indexes or partitioned tables is supported + with REINDEX INDEX or REINDEX TABLE, + respectively. Each partition of the specified partitioned relation is + reindexed in a separate transaction. Those commands cannot be used inside + a transaction block when working on a partitioned table or index. + + + + When using the TABLESPACE clause with + REINDEX on a partitioned index or table, only the + tablespace references of the leaf partitions are updated. As partitioned + indexes are not updated, it is recommended to separately use + ALTER TABLE ONLY on them so as any new partitions + attached inherit the new tablespace. On failure, it may not have moved + all the indexes to the new tablespace. Re-running the command will rebuild + all the leaf partitions and move previously-unprocessed indexes to the new + tablespace. + + + + If SCHEMA, DATABASE or + SYSTEM is used with TABLESPACE, + system relations are skipped and a single WARNING + will be generated. Indexes on TOAST tables are rebuilt, but not moved + to the new tablespace. + + + + 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 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.) + + + + Each backend running REINDEX will report its progress + in the pg_stat_progress_create_index view. See + for details. + + + + + + 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. + + + + + See Also + + + + + + + + + -- cgit v1.2.3