There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY <db_name>, handle the specific duplication violation (manipulating data, mainly delete the duplications using scripts), reindex the specific table or index, and continue to the next index (again only right after I discover it using REINDEX DATABASE CONCURRENTLY).

Not mentioning that each time I get indexes with the '_ccnew' suffix, that AFAIK are indexes that were tried to be created by a previous reindex concurrently but couldn’t be done, usually because they are violating a uniqueness check. The failed attempts to reindex concurrently will sit in there and should be dropped manually.

Concurrent reindex is used in order to prevent a shutdown.

I want to reduce those "roundtrips" of searching the next violation and wonder if there is a more efficient way to get a general data about the status of all the index corruptions or unique violations that a Postgres DB has.

1 Answer 1


You can use the amcheck extension to check a B-tree index for corruption. For other index types, there is no alternative to rebuilding the index.

You can run the following to check an index for corruption:

SELECT bt_index_check('indexname'::regclass);

That can be automatized to run against all the indexes in your database:

   indid oid;
   msg text;
   FOR indid IN
      SELECT i.oid
      FROM pg_class AS i
         JOIN pg_am ON pg_am.oid = i.relam
      WHERE pg_am.amname = 'btree'
        AND i.relkind = 'i'
         PERFORM bt_index_check(indid);
      EXCEPTION WHEN index_corrupted THEN
         RAISE WARNING 'Index "%" is currupted: %', indid::regclass, msg;

bt_index_check() has an optional second parameter; if you supply TRUE, the check will take much longer, but verify that all table rows are indexed.

Note that bt_index_check() does not cover all kinds of index corruption. There is also bt_index_parent_check(), which tests more thoroughly, but requires a SHARE lock that prevents concurrent data modification.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.