Dealing with index corruption

Once in a while, an index may blow up. In most cases, broken indexes are caused by hardware failures. Once again, the most suspicious thing in the case of index corruption is the RAM. So, always check your system memory in case an index has blown up.

In general, the same rules apply: take a snapshot first, and then simply recreate those broken indexes. REINDEX can help in this case:

test=# \h REINDEX
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } 
  name [ FORCE ]

Keep in mind that REINDEX needs a SHARE lock, which ensures that no writes can happen. In some setups, this could turn out to be a problem. To avoid locking, it makes sense to turn to CREATE INDEX CONCURRENTLY. A concurrent index build takes longer than a normal build. However, it avoids a nasty table lock that blocks writes. It can be very beneficial to many applications.

In general, an index-related problem is usually not as bad as a bad block...