Postgres corrupted index on update

Question

Sometimes, when doing an update on a specific table record, i get this error:

index "myIndexName" contains corrupted page at block 468
Please REINDEX it.

But on the next update it works, without having to do a reindex. What can cause this issue? Is there a way to prevent this error?

This index is only there to speed up performance on queries, but i'm seriously wanting to drop it to prevent information loss if i can't find a solution.

My postgres version is 9.3.9

• Craig Ringer: That really shouldn't happen. Can you supply more information about the queries that work / don't work, when this started happening, etc?

Answer 1

It looks like an on disk index corruption. If you can afford to have the table locked for a while you can solve it by running:

REINDEX INDEX "myIndexName";

The command will rebuild the index getting rid of the error.

If you cannot have the table locked for the time needed by the REINDEX command, you can concurrently create an additional index with the same definition. If the index is still usable, you can drop it after the CREATE INDEX CONCURRENTLY command finished and the index become valid, otherwise you can drop it before to start.

You can find more information here:

http://www.postgresql.org/docs/9.3/static/sql-reindex.html

http://www.postgresql.org/docs/9.3/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

• Erwin Brandstetter: If the index is corrupted it shouldn't be used at all until fixed. So, if the table can't be locked, I would drop the index immediately and CREATE INDEX CONCURRENTLY - risking some slow queries rather than incorrect results.
• mnencia: From the description of the OP it could be a not widespread corruption, maybe only a leaf block, however I agree that is safer to drop the index before concurrently recreate it.
• Mateus Viccari: Will do that, but isn't there a way to prevent things like this from happening?
• Erwin Brandstetter: @MateusViccari: Shouldn't happen at all. (Never happened to me, yet.) But there are many factors in play. Check your disk for hardware failures. Killing the Postgres server process or power outage without battery-backed storage system would be possible roots for the problem.
• mnencia: Computers are not perfect. Even in well written and well tested software there is so many things that can go wrong. Hardware defects, operating system bugs, memory bit-flips caused by cosmic rays and obviously subtle software bugs that have gone unnoticed during the estensive testing. My advice is to use good quality hardware with ECC memory, but more important, to implement a solid Disaster Recovery plan