Rebuild the index of tables, database, and schema in PostgreSQL

Rebuild index is building the index as a new one because it first drops and then creates an index. Rebuild index will fixed performance issues in many cases like in transactional tables which have a lot of insert/ update / delete DML operations which causes them to be fragmented So rebuild will create the index as a new one.

Check the indexes of the table

SELECT tablename, indexname FROM pg_indexes WHERE tablename='table_name';

Syntax to rebuild indexes:

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

Rebuild the Index: Specify the name of the index you want to rebuild

 REINDEX INDEX index_name;

Rebuild all the indexes of a table:

REINDEX TABLE table_name;

Rebuild all the indexes present in a schema:

REINDEX SCHEMA schema_name;

Rebuild all the indexes in the database:

REINDEX DATABASE database_name;