Postgresql – Postgres corrupted index on update

corruptionpostgresql

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

Best Answer

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

Related Question