Rebuild index in PostgreSQL database REINDEX

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:


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;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.