Open In App

PostgreSQL – REINDEX

Improve
Improve
Like Article
Like
Save
Share
Report

In postgreSQL, the REINDEX statement is used to recover corrupt indexes. 
An index can get corrupted due to software bugs or hardware failures.

Syntax: REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;

in the above syntax the VERBOSE keyword is optional argument which is used to show the progress report while reindexing a table. To rebuild a single index, you need to set the index name after REINDEX INDEX clause as shown below:

Syntax: REINDEX INDEX index_name;

To rebuild all the indexes of a table, we can make use of the TABLE keyword as shown below:

Syntax: REINDEX TABLE table_name;

For rebuilding all indices in a schema, one can make use of the SCHEMA keyword as shown below:

Syntax: REINDEX SCHEMA schema_name;

To recover all indices in a specific database, you need to set the database name after the REINDEX DATABASE clause as shown below:

Syntax: REINDEX DATABASE database_name;

The following statement can be used to recover all indices on system catalogs from a specific database:

Syntax: REINDEX SYSTEM database_name;

Example:

In this example we will build a REINDEX for the customer table in the sample database:

REINDEX TABLE customer;

Output:


Last Updated : 08 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads