Menu
Today I encountered a case where a record was missing in query result while existed in the table. I traced back the problem to probably a corrupted index. After I did REINDEX the problem was solved.
The thing is that normally when I run reports I get more than a 10000 records. It is impossible for me to know if there are missing records. Today I was lucky to find the problem.
Is there a way to get a notification when an index is corrupted?
If not, how do I write a function that will REINDEX all indeces in my Schema?
454
Data checksums are a great feature in PostgreSQL. They are used to detect any corruption of the data that Postgres stores on disk. Every system we develop at Crunchy Data has this feature enabled by default. It's not only Postgres itself that can make use of these checksums.
The two main reasons. There are two main reasons that Postgres will not use an index. Either it can't use the index, or it doesn't think using the index will be faster.
REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. There are several scenarios in which to use REINDEX : An index has become corrupted, and no longer contains valid data.
Description. VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.
Index corruption is very bad problem. I think you need to diagnose and fix this fundamentally and not try to band aid it in your query. It might be caused by poor administrative actions like kill -9 of any of Postgres processes etc.
To your question: there is no verifier tool AFAIK. I believe that a query that scans the whole index e.g. sort on index will find damaged pages in indeces.
I recommend you to read this blog.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
