9. Handling Hardware and Software Disasters | Troubleshooting PostgreSQL

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...

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Dealing with index corruption

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Dealing with index corruption

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access