Home Explore Blog CI



postgresql

4th chunk of `doc/src/sgml/ref/reindex.sgml`
40ffb383cf27ebac74a888d0ad4311734c6a8f4849d92b010000000100000fa0
 information about how to interact with the single-user server
   interface.
  </para>

  <para>
   Alternatively, a regular server session can be started with
   <option>-P</option> included in its command line options.
   The method for doing this varies across clients, but in all
   <application>libpq</application>-based clients, it is possible to set
   the <envar>PGOPTIONS</envar> environment variable to <literal>-P</literal>
   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.
  </para>

  <para>
   <command>REINDEX</command> 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.  <command>REINDEX</command> locks out writes
   but not reads of the index's parent table.  It also takes an
   <literal>ACCESS EXCLUSIVE</literal> 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 <literal>ACCESS SHARE</literal>
   lock on every index of the table, regardless of the query, and so
   <command>REINDEX</command> blocks virtually any queries except for some
   prepared queries whose plan has been cached and which don't use this very
   index. In contrast,
   <command>DROP INDEX</command> momentarily takes an
   <literal>ACCESS EXCLUSIVE</literal> lock on the parent table, blocking both
   writes and reads.  The subsequent <command>CREATE INDEX</command> 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.
  </para>

  <para>
   While <command>REINDEX</command> is running, the <xref
   linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
   pg_temp</literal>.
  </para>

  <para>
   Reindexing a single index or table requires
   having the <literal>MAINTAIN</literal> privilege on the
   table.  Note that while <command>REINDEX</command> on a partitioned index or
   table requires having the <literal>MAINTAIN</literal> privilege on the
   partitioned table, such commands skip the privilege checks when processing
   the individual partitions.  Reindexing a schema or database requires being the
   owner of that schema or database or having privileges of the
   <xref linkend="predefined-role-pg-maintain"/>
   role.  Note specifically that it's thus
   possible for non-superusers to rebuild indexes of tables owned by
   other users.  However, as a special exception,
   <command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>,
   and <command>REINDEX SYSTEM</command> will skip indexes on shared catalogs
   unless the user has the <literal>MAINTAIN</literal> privilege on the
   catalog.
  </para>

  <para>
   Reindexing partitioned indexes or partitioned tables is supported
   with <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>,
   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.
  </para>

  <para>
   When using the <literal>TABLESPACE</literal> clause with
   <command>REINDEX</command> 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
   <command>ALTER TABLE ONLY</command> 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.
  </para>

Title: REINDEX Notes: Access Locks, Search Path, Privileges, and Partitioned Tables
Summary
This section delves into the locking behavior of REINDEX compared to DROP INDEX and CREATE INDEX, noting that REINDEX locks writes but not reads, while DROP INDEX temporarily blocks both. It also mentions that the search path is temporarily changed to pg_catalog, pg_temp while REINDEX is running. Furthermore, it explains the necessary privileges for reindexing different objects (tables, schemas, databases) and discusses support for reindexing partitioned indexes and tables, including considerations for the TABLESPACE clause and inherited tablespaces.