Index corruption shouldnt happen... But life can be messy:
okt.to/iAhu1s
Gonna be discussing this live at the top of the hour: okt.to/DMX2sl
Conversation
Replying to
We see Postgres index corruption a fair bit at Discourse. Pg10 but switching to pg12 very soon.
2
6
I work on the Postgres B-Tree code. Corruption simply should not happen with any regularity. E-mail me (address is pg@bowt.ie), or send a report to one of the community lists. Always interested in hearing problem reports, even when they do not involve bugs.
1
2
Thank you! We're currently upgrading to postgres 12 and we'll see how it goes. At we upgrade every other version, so we are on version 10 and skipped 11. cc
1
Cool! There are a bunch of B-Tree space utilization improvements in 12 that I worked on, but they're not expected to affect correctness or reliability. I strongly encourage root cause analysis of index corruption. It shouldn't be something you have to tolerate or accept.
1
2
I am not sure how relevant this is, but the index we saw corrupt was defined as: 'create unique index idx on tags using btree (lower((name)::text))'
1
1
Rails creates indexes that look like that for case-insensitive searches. My guess is that there is nothing special in general about that particular index. Thanks, though.
Note, this is not Rails, its a specific index we created. Interestingly there are 2 indexed on the table, one on name and another on lower(name). In our hosting I have never seen this corrupt index, but we are not done upgrading 10 to 12... will let you know if we find a pattern
1
note ... another interesting thing we found is that if you reindex concurrently one of these corrupt indexes ... a corrupt duplicate "_ccnew" index is left behind.
1
The invalid index that's left behind is not used, though. This behavior is annoying though not generally harmful. It is documented here: postgresql.org/docs/current/s
2