begriffs open source - ai-pg/blob - full-docs/txt/sql-reindex.txt

   1
   2 REINDEX
   3
   4    REINDEX — rebuild indexes
   5
   6 Synopsis
   7
   8 REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
   9 REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]
  10
  11 where option can be one of:
  12
  13     CONCURRENTLY [ boolean ]
  14     TABLESPACE new_tablespace
  15     VERBOSE [ boolean ]
  16
  17 Description
  18
  19    REINDEX rebuilds an index using the data stored in the index's table,
  20    replacing the old copy of the index. There are several scenarios in
  21    which to use REINDEX:
  22      * An index has become corrupted, and no longer contains valid data.
  23        Although in theory this should never happen, in practice indexes
  24        can become corrupted due to software bugs or hardware failures.
  25        REINDEX provides a recovery method.
  26      * An index has become “bloated”, that is it contains many empty or
  27        nearly-empty pages. This can occur with B-tree indexes in
  28        PostgreSQL under certain uncommon access patterns. REINDEX provides
  29        a way to reduce the space consumption of the index by writing a new
  30        version of the index without the dead pages. See Section 24.2 for
  31        more information.
  32      * You have altered a storage parameter (such as fillfactor) for an
  33        index, and wish to ensure that the change has taken full effect.
  34      * If an index build fails with the CONCURRENTLY option, this index is
  35        left as “invalid”. Such indexes are useless but it can be
  36        convenient to use REINDEX to rebuild them. Note that only REINDEX
  37        INDEX is able to perform a concurrent build on an invalid index.
  38
  39 Parameters
  40
  41    INDEX
  42           Recreate the specified index. This form of REINDEX cannot be
  43           executed inside a transaction block when used with a partitioned
  44           index.
  45
  46    TABLE
  47           Recreate all indexes of the specified table. If the table has a
  48           secondary “TOAST” table, that is reindexed as well. This form of
  49           REINDEX cannot be executed inside a transaction block when used
  50           with a partitioned table.
  51
  52    SCHEMA
  53           Recreate all indexes of the specified schema. If a table of this
  54           schema has a secondary “TOAST” table, that is reindexed as well.
  55           Indexes on shared system catalogs are also processed. This form
  56           of REINDEX cannot be executed inside a transaction block.
  57
  58    DATABASE
  59           Recreate all indexes within the current database, except system
  60           catalogs. Indexes on system catalogs are not processed. This
  61           form of REINDEX cannot be executed inside a transaction block.
  62
  63    SYSTEM
  64           Recreate all indexes on system catalogs within the current
  65           database. Indexes on shared system catalogs are included.
  66           Indexes on user tables are not processed. This form of REINDEX
  67           cannot be executed inside a transaction block.
  68
  69    name
  70           The name of the specific index, table, or database to be
  71           reindexed. Index and table names can be schema-qualified.
  72           Presently, REINDEX DATABASE and REINDEX SYSTEM can only reindex
  73           the current database. Their parameter is optional, and it must
  74           match the current database's name.
  75
  76    CONCURRENTLY
  77           When this option is used, PostgreSQL will rebuild the index
  78           without taking any locks that prevent concurrent inserts,
  79           updates, or deletes on the table; whereas a standard index
  80           rebuild locks out writes (but not reads) on the table until it's
  81           done. There are several caveats to be aware of when using this
  82           option — see Rebuilding Indexes Concurrently below.
  83
  84           For temporary tables, REINDEX is always non-concurrent, as no
  85           other session can access them, and non-concurrent reindex is
  86           cheaper.
  87
  88    TABLESPACE
  89           Specifies that indexes will be rebuilt on a new tablespace.
  90
  91    VERBOSE
  92           Prints a progress report as each index is reindexed at INFO
  93           level.
  94
  95    boolean
  96           Specifies whether the selected option should be turned on or
  97           off. You can write TRUE, ON, or 1 to enable the option, and
  98           FALSE, OFF, or 0 to disable it. The boolean value can also be
  99           omitted, in which case TRUE is assumed.
 100
 101    new_tablespace
 102           The tablespace where indexes will be rebuilt.
 103
 104 Notes
 105
 106    If you suspect corruption of an index on a user table, you can simply
 107    rebuild that index, or all indexes on the table, using REINDEX INDEX or
 108    REINDEX TABLE.
 109
 110    Things are more difficult if you need to recover from corruption of an
 111    index on a system table. In this case it's important for the system to
 112    not have used any of the suspect indexes itself. (Indeed, in this sort
 113    of scenario you might find that server processes are crashing
 114    immediately at start-up, due to reliance on the corrupted indexes.) To
 115    recover safely, the server must be started with the -P option, which
 116    prevents it from using indexes for system catalog lookups.
 117
 118    One way to do this is to shut down the server and start a single-user
 119    PostgreSQL server with the -P option included on its command line.
 120    Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX
 121    can be issued, depending on how much you want to reconstruct. If in
 122    doubt, use REINDEX SYSTEM to select reconstruction of all system
 123    indexes in the database. Then quit the single-user server session and
 124    restart the regular server. See the postgres reference page for more
 125    information about how to interact with the single-user server
 126    interface.
 127
 128    Alternatively, a regular server session can be started with -P included
 129    in its command line options. The method for doing this varies across
 130    clients, but in all libpq-based clients, it is possible to set the
 131    PGOPTIONS environment variable to -P before starting the client. Note
 132    that while this method does not require locking out other clients, it
 133    might still be wise to prevent other users from connecting to the
 134    damaged database until repairs have been completed.
 135
 136    REINDEX is similar to a drop and recreate of the index in that the
 137    index contents are rebuilt from scratch. However, the locking
 138    considerations are rather different. REINDEX locks out writes but not
 139    reads of the index's parent table. It also takes an ACCESS EXCLUSIVE
 140    lock on the specific index being processed, which will block reads that
 141    attempt to use that index. In particular, the query planner tries to
 142    take an ACCESS SHARE lock on every index of the table, regardless of
 143    the query, and so REINDEX blocks virtually any queries except for some
 144    prepared queries whose plan has been cached and which don't use this
 145    very index. In contrast, DROP INDEX momentarily takes an ACCESS
 146    EXCLUSIVE lock on the parent table, blocking both writes and reads. The
 147    subsequent CREATE INDEX locks out writes but not reads; since the index
 148    is not there, no read will attempt to use it, meaning that there will
 149    be no blocking but reads might be forced into expensive sequential
 150    scans.
 151
 152    While REINDEX is running, the search_path is temporarily changed to
 153    pg_catalog, pg_temp.
 154
 155    Reindexing a single index or table requires having the MAINTAIN
 156    privilege on the table. Note that while REINDEX on a partitioned index
 157    or table requires having the MAINTAIN privilege on the partitioned
 158    table, such commands skip the privilege checks when processing the
 159    individual partitions. Reindexing a schema or database requires being
 160    the owner of that schema or database or having privileges of the
 161    pg_maintain role. Note specifically that it's thus possible for
 162    non-superusers to rebuild indexes of tables owned by other users.
 163    However, as a special exception, REINDEX DATABASE, REINDEX SCHEMA, and
 164    REINDEX SYSTEM will skip indexes on shared catalogs unless the user has
 165    the MAINTAIN privilege on the catalog.
 166
 167    Reindexing partitioned indexes or partitioned tables is supported with
 168    REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the
 169    specified partitioned relation is reindexed in a separate transaction.
 170    Those commands cannot be used inside a transaction block when working
 171    on a partitioned table or index.
 172
 173    When using the TABLESPACE clause with REINDEX on a partitioned index or
 174    table, only the tablespace references of the leaf partitions are
 175    updated. As partitioned indexes are not updated, it is recommended to
 176    separately use ALTER TABLE ONLY on them so as any new partitions
 177    attached inherit the new tablespace. On failure, it may not have moved
 178    all the indexes to the new tablespace. Re-running the command will
 179    rebuild all the leaf partitions and move previously-unprocessed indexes
 180    to the new tablespace.
 181
 182    If SCHEMA, DATABASE or SYSTEM is used with TABLESPACE, system relations
 183    are skipped and a single WARNING will be generated. Indexes on TOAST
 184    tables are rebuilt, but not moved to the new tablespace.
 185
 186 Rebuilding Indexes Concurrently
 187
 188    Rebuilding an index can interfere with regular operation of a database.
 189    Normally PostgreSQL locks the table whose index is rebuilt against
 190    writes and performs the entire index build with a single scan of the
 191    table. Other transactions can still read the table, but if they try to
 192    insert, update, or delete rows in the table they will block until the
 193    index rebuild is finished. This could have a severe effect if the
 194    system is a live production database. Very large tables can take many
 195    hours to be indexed, and even for smaller tables, an index rebuild can
 196    lock out writers for periods that are unacceptably long for a
 197    production system.
 198
 199    PostgreSQL supports rebuilding indexes with minimum locking of writes.
 200    This method is invoked by specifying the CONCURRENTLY option of
 201    REINDEX. When this option is used, PostgreSQL must perform two scans of
 202    the table for each index that needs to be rebuilt and wait for
 203    termination of all existing transactions that could potentially use the
 204    index. This method requires more total work than a standard index
 205    rebuild and takes significantly longer to complete as it needs to wait
 206    for unfinished transactions that might modify the index. However, since
 207    it allows normal operations to continue while the index is being
 208    rebuilt, this method is useful for rebuilding indexes in a production
 209    environment. Of course, the extra CPU, memory and I/O load imposed by
 210    the index rebuild may slow down other operations.
 211
 212    The following steps occur in a concurrent reindex. Each step is run in
 213    a separate transaction. If there are multiple indexes to be rebuilt,
 214    then each step loops through all the indexes before moving to the next
 215    step.
 216     1. A new transient index definition is added to the catalog pg_index.
 217        This definition will be used to replace the old index. A SHARE
 218        UPDATE EXCLUSIVE lock at session level is taken on the indexes
 219        being reindexed as well as their associated tables to prevent any
 220        schema modification while processing.
 221     2. A first pass to build the index is done for each new index. Once
 222        the index is built, its flag pg_index.indisready is switched to
 223        “true” to make it ready for inserts, making it visible to other
 224        sessions once the transaction that performed the build is finished.
 225        This step is done in a separate transaction for each index.
 226     3. Then a second pass is performed to add tuples that were added while
 227        the first pass was running. This step is also done in a separate
 228        transaction for each index.
 229     4. All the constraints that refer to the index are changed to refer to
 230        the new index definition, and the names of the indexes are changed.
 231        At this point, pg_index.indisvalid is switched to “true” for the
 232        new index and to “false” for the old, and a cache invalidation is
 233        done causing all sessions that referenced the old index to be
 234        invalidated.
 235     5. The old indexes have pg_index.indisready switched to “false” to
 236        prevent any new tuple insertions, after waiting for running queries
 237        that might reference the old index to complete.
 238     6. The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session
 239        locks for the indexes and the table are released.
 240
 241    If a problem arises while rebuilding the indexes, such as a uniqueness
 242    violation in a unique index, the REINDEX command will fail but leave
 243    behind an “invalid” new index in addition to the pre-existing one. This
 244    index will be ignored for querying purposes because it might be
 245    incomplete; however it will still consume update overhead. The psql \d
 246    command will report such an index as INVALID:
 247 postgres=# \d tab
 248        Table "public.tab"
 249  Column |  Type   | Modifiers
 250 --------+---------+-----------
 251  col    | integer |
 252 Indexes:
 253     "idx" btree (col)
 254     "idx_ccnew" btree (col) INVALID
 255
 256    If the index marked INVALID is suffixed _ccnew, then it corresponds to
 257    the transient index created during the concurrent operation, and the
 258    recommended recovery method is to drop it using DROP INDEX, then
 259    attempt REINDEX CONCURRENTLY again. If the invalid index is instead
 260    suffixed _ccold, it corresponds to the original index which could not
 261    be dropped; the recommended recovery method is to just drop said index,
 262    since the rebuild proper has been successful. A nonzero number may be
 263    appended to the suffix of the invalid index names to keep them unique,
 264    like _ccnew1, _ccold2, etc.
 265
 266    Regular index builds permit other regular index builds on the same
 267    table to occur simultaneously, but only one concurrent index build can
 268    occur on a table at a time. In both cases, no other types of schema
 269    modification on the table are allowed meanwhile. Another difference is
 270    that a regular REINDEX TABLE or REINDEX INDEX command can be performed
 271    within a transaction block, but REINDEX CONCURRENTLY cannot.
 272
 273    Like any long-running transaction, REINDEX on a table can affect which
 274    tuples can be removed by concurrent VACUUM on any other table.
 275
 276    REINDEX SYSTEM does not support CONCURRENTLY since system catalogs
 277    cannot be reindexed concurrently.
 278
 279    Furthermore, indexes for exclusion constraints cannot be reindexed
 280    concurrently. If such an index is named directly in this command, an
 281    error is raised. If a table or database with exclusion constraint
 282    indexes is reindexed concurrently, those indexes will be skipped. (It
 283    is possible to reindex such indexes without the CONCURRENTLY option.)
 284
 285    Each backend running REINDEX will report its progress in the
 286    pg_stat_progress_create_index view. See Section 27.4.4 for details.
 287
 288 Examples
 289
 290    Rebuild a single index:
 291 REINDEX INDEX my_index;
 292
 293    Rebuild all the indexes on the table my_table:
 294 REINDEX TABLE my_table;
 295
 296    Rebuild all indexes in a particular database, without trusting the
 297    system indexes to be valid already:
 298 $ export PGOPTIONS="-P"
 299 $ psql broken_db
 300 ...
 301 broken_db=> REINDEX DATABASE broken_db;
 302 broken_db=> \q
 303
 304    Rebuild indexes for a table, without blocking read and write operations
 305    on involved relations while reindexing is in progress:
 306 REINDEX TABLE CONCURRENTLY my_broken_table;
 307
 308 Compatibility
 309
 310    There is no REINDEX command in the SQL standard.
 311
 312 See Also
 313
 314    CREATE INDEX, DROP INDEX, reindexdb, Section 27.4.4