Postgresql Reindex

• Reindex is used to rebuild an index when the index undergoes hardware and software bugs. So by using reindex, you can prepare or rebuild the index again.
• Reindexing is very useful as the data of the old index which was corrupted, swollen or bloated in PostgreSQL server then you can use the rebuild function to gain the old data without losing the data and assign it to the new index.
• Reindex function used to eliminate the empty spaces or empty pages that can be removed and reduce the space.
• The point to be noted is that reindex can perform commands concurrently and also the index which failed was using simultaneously then it can be created again by reindex but not concurrently this time.
• To create an index about concurrently then you have to create index simultaneously after reindexing the old corrupted concurrent command.

Syntax:

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name

Index: recreate a specified index

Table: recreate all index of a table 

Database: all indexes of a particular database have to recreate.

System: all indexes of a current database have to recreate.

For example:

REINDEX INDEX my_index;

The above syntax used to rebuild the index (my_index)

REINDEX TABLE my_table;

The above syntax used to rebuild all the indexes of a particular table(my_table)