Change history
- Reindexing a table
- Reindexing a database
Examples
References
Categories
See also

REINDEX

An SQL command for rebuilding indexes

REINDEX is a utility command for rebuilding indexes.

REINDEX was added in PostgreSQL 7.0.

Change history

PostgreSQL 16
- database name no longer mandatory (commit 2cbc3c17)
- system catalog tables will only be reindexed with REINDEX SYSTEM (commit 2cbc3c17)
PostgreSQL 14
- TABLESPACE clause added (commit c5b28604)
- support for partitioned tables and indexes added (commit a6642b3a)
PostgreSQL 12
- CONCURRENTLY option added (5dc92b84)
- progress of REINDEX can be tracked with pg_stat_progress_create_index (commit ab0dfc96)
PostgreSQL 9.5
- REINDEX SCHEMA ... option added (commit fe263d11)
- VERBOSE option added (commit ecd222e7)
PostgreSQL 8.1
- REINDEX SYSTEM option added (commit 4cc7a93d)
PostgreSQL 7.0
- added (commit e3a97b37)

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

References

PostgreSQL documentation: REINDEX

Version charts

Useful overviews

Useful links

Contents