Corrupted indexes that do not have
UNIQUE in their definition can be simply rebuilt with the
REINDEX INDEX <index-name>; command. This will create a new index from scratch and replace the old one with it.
REINDEX command can also be performed for all indexes of a table
REINDEX TABLE or even for all indexes in the entire database
Note that reindexing takes locks to the table and may interfere with the normal use of the database. In some cases it may be useful to use another approach of building a second index concurrently alongside with the old index and the remove the index:
CREATE INDEX CONCURRENTLY foo_index_new ON table_a (...);
DROP INDEX CONCURRENTLY foo_index; -- remove the old corrupted index now that we have a new one
ALTER INDEX foo_index_new RENAME TO foo_index;
More information about the
REINDEX command can be found on the PostgreSQL documentation page.
It is possible that an UNIQUE index cannot be rebuilt with the REINDEX command when the index has been corrupted or been disabled in such way that there are now duplicate physical rows in the source table that break the uniqueness constraint of the index. In such cases the repair procedure requires removing the duplicate rows and then rebuilding the index with the
Identifying conflicting duplicate rows
Locating conflicting duplicate rows can be done by running a query that counts the number of instances.
We'll use the following database schema as an example:
CREATE TABLE route (source TEXT, destination TEXT, description TEXT);
CREATE UNIQUE INDEX unique_route_index ON route (source, destination);
When an index corruption that allows duplicate rows in the table is detected, we can find the offending rows with the following type of query:
SELECT source, destination, count
FROM (SELECT source, destination, COUNT(*) AS count FROM route GROUP BY source, destination) AS foo
WHERE count > 1;
We are using the same fields that form the index, grouping by them and looking for any entries that appear more than once.
The resulting rows will identify the problematic entries and they will need to be resolved manually by deleting or merging the entries until duplicates no longer exist. Once this is achieved, the
REINDEX command can be used to rebuild the index.
Got here by accident? Learn how Aiven simplifies Postgres: