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
Last activity: Aug 9, 2025, 01:53 PM