Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
REINDEX(7)		 PostgreSQL 17.5 Documentation		    REINDEX(7)

NAME
       REINDEX - rebuild indexes

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

       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 -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(1) 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 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.

       While REINDEX is	running, the search_path is temporarily	changed	to
       pg_catalog, pg_temp.

       Reindexing a single index or table requires having the MAINTAIN
       privilege on the	table. Note that while REINDEX on a partitioned	index
       or table	requires having	the MAINTAIN 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
       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,	REINDEX	DATABASE, REINDEX SCHEMA, and
       REINDEX SYSTEM will skip	indexes	on shared catalogs unless the user has
       the MAINTAIN privilege on the catalog.

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

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

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

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

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

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

	6. 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 Section 27.4.4 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
       CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)),
       reindexdb(1), Section 27.4.4

PostgreSQL 17.5			     2025			    REINDEX(7)

Want to link to this manual page? Use this URL:
<https://man.freebsd.org/cgi/man.cgi?query=REINDEX&sektion=7&manpath=FreeBSD+Ports+14.3.quarterly>

home | help