Database Index Corruption

When your database says a user doesn't exist, but they are right there in the table.

The idea

A database index is just a separate data structure (like a B-Tree) that acts like the index at the back of a textbook. It points to the actual data on disk. But what happens if the database crashes exactly halfway through an INSERT? The data might be written to the main table, but the B-Tree index might not be updated. This desynchronization is called Index Corruption. If you run SELECT * FROM users WHERE email='alice@foo.com', the database checks the corrupted index, doesn't find her, and returns 0 rows—even though she exists!

How it works (REINDEX)

Because an index is completely derived from the main table, you don't actually lose data when an index corrupts. You just lose the ability to query it quickly (or accurately). The fix is to rebuild the index from scratch by scanning the entire main table. In PostgreSQL, this is done via the REINDEX command.

-- 1. Notice weird behavior (Alice can't log in)
SELECT * FROM users WHERE email='alice@foo.com'; -- Returns 0 rows!

-- But a full table scan (ignoring the index) finds her:
SELECT * FROM users WHERE email LIKE '%alice%'; -- Returns 1 row!

-- 2. Fix the corruption without locking the table (Postgres 12+)
REINDEX INDEX CONCURRENTLY users_email_idx;

Cost

Running REINDEX on a 500GB table takes a massive amount of CPU and disk I/O. If you do a standard REINDEX, it locks the table, meaning no one can write to it for hours (a total outage). You must use REINDEX CONCURRENTLY, which builds a brand new index in the background while the old one is still used, and swaps them when finished. It takes longer but avoids downtime.

Watch out for

• Hardware faults: Index corruption shouldn't happen during normal crashes because of the Write-Ahead Log (WAL). If you see frequent index corruption, it almost always points to failing hardware (bad RAM flipping bits, or a failing SSD lying about fsync completion).