Sample Header Ad - 728x90

Finding all the index corruptions in the entire Postgres DB

3 votes
1 answer
2540 views
There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY , 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.
Asked by KaliTheGreat (189 rep)
Feb 21, 2024, 08:00 AM
Last activity: Aug 9, 2025, 01:53 PM