REINDEX

An SQL command for rebuilding indexes

REINDEX is a utility command for rebuilding indexes.

REINDEX was added in PostgreSQL 7.0.

Change history

Examples

Reindexing a table

appdb=> REINDEX (VERBOSE) TABLE object;
INFO:  index "object_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO:  index "object_class_id_ix" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX

Reindexing a database

In PostgreSQL 15 and earlier the current database name must be provided explicitly:

postgres=# REINDEX DATABASE;
ERROR:  syntax error at or near ";"
LINE 1: REINDEX DATABASE;
                        ^
postgres=# REINDEX DATABASE postgres;
REINDEX

It is not possible to reindex a different database:

postgres=# REINDEX DATABASE foo;
ERROR:  can only reindex the currently open database
  • PostgreSQL documentation: REINDEX

Categories

Indexing, SQL command, Utility command

See also

reindexdb, CREATE INDEX, ALTER INDEX, DROP INDEX