PostgreSQL indexes can be corrupted for a variety of reasons, including
software bugs, hardware failures, or unexpected duplicated data.
you to rebuild the index in such situations.
You can rebuild corrupted indexes that do not have
UNIQUE in their definition.
You can run the
REINDEX command for all indexes of a table (
and for all indexes in the entire database (
For more information on the
REINDEX command, see the PostgreSQL documentation.
This command creates a new index that replaces the old one:
REINDEX INDEX <index-name>;
When you use
REINDEX, the tables are locked and you may not be able to use the database, until the operation is complete.
In some cases, you might need to manually build a second index concurrently with the old index, and then remove the old index:
CREATE INDEX CONCURRENTLY test_index_new ON table_a (...);DROP INDEX CONCURRENTLY test_index_old;ALTER INDEX test_index_new RENAME TO test_index;
UNIQUE index works on one or more columns where the combination is unique
in the table. When the index is corrupted or disabled, duplicated
physical rows appear in the table, breaking the uniqueness constraint of the
index. When you try to rebuild an index that is not unique, the
REINDEX command fails.
To resolve this issue, first remove the duplicate rows from the table and then
rebuild the index.
To identify conflicting duplicate rows, you need to run a query that counts the number of rows for each combination of columns included in the index definition.
For example, this
route table has a
unique_route_index index defining
unique rows based on the combination of the
CREATE TABLE route(source TEXT,destination TEXT,description TEXT);CREATE UNIQUE INDEX unique_route_indexON route (source, destination);
unique_route_index is corrupt, you can find duplicated rows in the
route table using this query:
SELECTsource,destination,countFROM(SELECTsource,destination,COUNT(*) AS countFROM routeGROUP BYsource,destination) AS fooWHERE count > 1;
The query groups the data by the same
destination fields defined
in the index, and filters any entries with more than one occurrence.
Resolve the problematic entries in the rows by manually deleting or merging the
entries until no duplicates exist. After all duplicate entries are removed, you
can use the
REINDEX command to rebuild the index.
Found an issue on this page?Report an issue!