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 inside a table. Remember, however, that the index itself does not contain data and data cannot be recreated easily.
3.133.133.233