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

FreeBSD Manual Pages


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

       REINDEX - rebuild indexes


       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:

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

       o   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, "Routine Reindexing", in the documentation for	more

       o   You have altered a storage parameter	(such as fillfactor) for an
	   index, and wish to ensure that the change has taken full effect.

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

	   Recreate the	specified index.

	   Recreate all	indexes	of the specified table.	If the table has a
	   secondary "TOAST" table, that is reindexed as well.

	   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.

	   Recreate all	indexes	within the current database. Indexes on	shared
	   system catalogs are also processed. This form of REINDEX cannot be
	   executed inside a transaction block.

	   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.

	   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,
	   so their parameter must match the current database's	name.

	   Prints a progress report as each index is reindexed.

       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

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

       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 exclusive lock on
       the specific index being	processed, which will block reads that attempt
       to use that index. In contrast, DROP INDEX momentarily takes an
       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

       Reindexing a single index or table requires being the owner of that
       index or	table. Reindexing a database requires being the	owner of the
       database	(note that the owner can therefore rebuild indexes of tables
       owned by	other users). Of course, superusers can	always reindex

       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

       There is	no REINDEX command in the SQL standard.

PostgreSQL 9.6.20		     2020			    REINDEX(7)


Want to link to this manual page? Use this URL:

home | help