How to Rebuild Index in PostgreSQL: Syntax and Examples

To rebuild an index in PostgreSQL, use the REINDEX command followed by the index name or table name. This command recreates the index to improve performance or fix corruption without dropping the table.

Syntax

The REINDEX command rebuilds corrupted or bloated indexes. You can specify an index, a table, a schema, or the entire database.

• REINDEX INDEX index_name; - Rebuilds a specific index.
• REINDEX TABLE table_name; - Rebuilds all indexes on a table.
• REINDEX SCHEMA schema_name; - Rebuilds all indexes in a schema.
• REINDEX DATABASE database_name; - Rebuilds all indexes in the database.

sql

REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX SCHEMA schema_name;
REINDEX DATABASE database_name;

Example

This example shows how to rebuild a specific index and all indexes on a table.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

CREATE INDEX idx_department ON employees(department);

-- Rebuild the specific index
REINDEX INDEX idx_department;

-- Rebuild all indexes on the employees table
REINDEX TABLE employees;

Output

CREATE TABLE
CREATE INDEX
REINDEX
REINDEX

Common Pitfalls

Common mistakes when rebuilding indexes include:

• Trying to REINDEX a non-existent index or table causes an error.
• Running REINDEX on a large table locks it, blocking writes during the operation.
• Using DROP INDEX and CREATE INDEX separately instead of REINDEX can cause downtime and data inconsistency.

Always ensure you have proper backups before rebuilding indexes on production systems.

sql

/* Wrong: Dropping and recreating index manually can cause downtime */
DROP INDEX idx_department;
CREATE INDEX idx_department ON employees(department);

/* Right: Use REINDEX to rebuild safely */
REINDEX INDEX idx_department;

Quick Reference

Command	Description
REINDEX INDEX index_name;	Rebuilds a specific index.
REINDEX TABLE table_name;	Rebuilds all indexes on a table.
REINDEX SCHEMA schema_name;	Rebuilds all indexes in a schema.
REINDEX DATABASE database_name;	Rebuilds all indexes in the database.

Key Takeaways

Use REINDEX to rebuild indexes safely without dropping tables.

REINDEX can target a single index, table, schema, or entire database.

Rebuilding indexes locks the affected objects, so plan for maintenance windows.

Avoid dropping and recreating indexes manually to prevent downtime.

Always backup your data before performing index maintenance.