PostgreSQL Rebuild Indexes

This article describes how to rebuild indexes in PostgreSQL.

You can rebuild indexes when they are damaged or contain bad data. PostgreSQL provides the REINDEX statement to rebuild one or more indexes.

PostgreSQL REINDEX syntax

Here is the syntax of the PostgreSQL REINDEX statement:

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

Explanation:

  • The VERBOSE keyword are optional. If this keyword is provided, progress will be displayed while the index is being rebuilt.
  • The CONCURRENTLY keyword is optional. With it, PostgreSQL will not block any operations on the table while the index is being rebuilt.
  • The TABLESPACE new_tablespace is optional. With it， PostgreSQL will rebuild indexes on the new tablespace.
  • The { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name is used to specify the index object to rebuild.
    • INDEX: Rebuild the specified index.
    • TABLE: Rebuild all indexes in the specified table.
    • SCHEMA: Rebuild all indexes in the specified schema.
    • DATABASE: Rebuild all indexes in the specified database.
    • SYSTEM: Rebuild all indexes in the system catalog of the specified database.
    • The name specifies the name of the object.

Here are some specific usages:

  • To rebuild a single index, use the INDEX keyword and specify the index name:

    REINDEX INDEX index_name;

  • To rebuild all indexes in a table, use the TABLE keyword and specify the name of the table:

    REINDEX TABLE table_name;

  • To rebuild all indexes in a schema, use the SCHEMA keyword and specify the name of the schema:

    REINDEX SCHEMA schema_name;

  • To rebuild all indexes in a database, use the DATABASE keyword and specify the database name:

    REINDEX DATABASE database_name;

  • To rebuild all indexes on the system catalog in one data, use the SYSTEM keyword and specify the database name:

    REINDEX SYSTEM database_name;

REINDEX vs DROP INDEX and CREATE INDEX

The process of rebuilding an index is equivalent to dropping the index first and creating an index with the same definition. That is: REINDEX equivalent to a combination of DROP INDEX and CREATE INDEX statements. It also has some differences between them:

  • REINDEX Statement:

    • Locks writes but not reads of the table to which the index belongs.
    • Takes an exclusive lock on the index being processed, which prevents reads attempting to use the index. unless you specify CONCURRENTLY keywords.

  • DROP INDEX and CREATE INDEX statements:

    • The DROP INDEX statement locks writes and reads of the table to which the index belongs by acquiring an exclusive lock on the table.
    • The CREATE INDEX statement locks writes but not reads in the index’s parent table. However, reads during index creation is expensive.

Conclusion

This article describes how to use the REINDEX statement in PostgreSQL.