PostgreSQL Index Corrupted (SQLSTATE XX002)

PostgreSQL raises ERROR: index "<index_name>" contains unexpected zero page at block <N> or similar messages with SQLSTATE XX002 (INDEX_CORRUPTED) when it detects that an index's on-disk structure is internally inconsistent or physically damaged. The condition name is index_corrupted, and it belongs to error class XX — Internal Error — meaning PostgreSQL itself identified data-level damage rather than a user mistake.

What This Error Means

SQLSTATE class XX covers internal errors that reflect damage or inconsistency within PostgreSQL's own storage structures. Unlike class 22 (data exceptions, which signal bad input values) or class 23 (integrity constraint violations), class XX errors mean the database files themselves are in a state PostgreSQL cannot reconcile. The two conditions in this class are XX000 (internal_error) and XX002 (index_corrupted); there is no XX001 in the public SQLSTATE table.

When PostgreSQL reads an index page during a sequential scan, index scan, or index build and finds that the page header, item offsets, or internal B-tree structure do not conform to expected invariants, it raises XX002. The detection can happen during SELECT, INSERT, UPDATE, DELETE, VACUUM, ANALYZE, or explicit REINDEX operations — essentially any code path that touches index pages.

After the error is raised, the current transaction is aborted. The index itself remains in place but is unreliable: subsequent reads may return wrong results (missing rows, phantom rows) or raise the same error again. The table data (heap) is unaffected — only the index structure is damaged.

Common Causes

1. Hardware or storage failure. Write errors on the disk, faulty RAID controllers, or SAN/NAS firmware bugs can silently corrupt pages written to disk. This is the most common root cause of persistent index corruption.
2. Unclean shutdown or power loss. If fsync is disabled (fsync = off in postgresql.conf) or the storage layer does not honour fsync guarantees, pages can be written in a partial state that survives a crash as a permanently corrupt block.
3. OS or filesystem bug. A kernel bug, filesystem driver defect, or incorrect nobarrier mount option can allow page cache flushes to be reordered, producing torn writes.
4. Memory corruption (RAM errors). ECC errors or uncorrected soft errors in RAM can corrupt pages in the shared buffer cache before they are flushed to disk.
5. Concurrent DDL or upgrade issues. Running pg_upgrade against an inconsistent cluster, or schema changes performed while a replica is being promoted, can leave index metadata out of sync.
6. Bugs in older PostgreSQL versions. Specific releases had known issues (e.g., GiST and GIN index bugs in pre-9.x versions, SP-GiST issues in 9.2) that produced corrupt structures without hardware involvement.

How to Fix index_corrupted

1. Identify the affected index. The error message names the index. Confirm which table it belongs to:

   SELECT schemaname, tablename, indexname
   FROM pg_indexes
   WHERE indexname = '<index_name>';
   

2. Rebuild the index with REINDEX. This is the primary fix. For a single index:

   REINDEX INDEX CONCURRENTLY schema_name.index_name;
   

   Use CONCURRENTLY (PostgreSQL 12+) to avoid locking the table during the rebuild. Without CONCURRENTLY, a full AccessExclusiveLock is taken:

   REINDEX INDEX schema_name.index_name;
   

   To rebuild all indexes on a table at once:

   REINDEX TABLE CONCURRENTLY schema_name.table_name;
   

3. Verify the heap (table data) is intact. Index corruption does not imply heap corruption, but it is worth checking:

   -- Requires pg_amcheck extension (PostgreSQL 14+)
   SELECT * FROM pg_amcheck(
     relation => 'schema_name.table_name',
     check_heap => true,
     check_indexes => true
   );
   

   Alternatively, a full sequential scan forces every heap page to be read:

   SET enable_indexscan = off;
   SET enable_bitmapscan = off;
   SELECT count(*) FROM schema_name.table_name;
   

4. Investigate the root cause. If REINDEX succeeds but the same index becomes corrupt again, the underlying storage or memory issue must be resolved first. Check kernel logs (dmesg, /var/log/syslog) for I/O errors, run smartctl -a /dev/<disk> on physical disks, and verify that fsync = on in postgresql.conf.

5. Restore from backup if the damage is widespread. If multiple indexes or heap pages are corrupt, a point-in-time restore from a known-good backup (using pg_basebackup + WAL replay) may be safer than attempting in-place repair.

Additional Information

• SQLSTATE XX002 is stable across all modern PostgreSQL versions; the class XX has existed since at least PostgreSQL 8.0.
• Related SQLSTATE codes: XX000 (internal_error) for general internal inconsistencies that do not fit a more specific category.
• The pg_amcheck extension (available since PostgreSQL 14) provides structured verification of heap and index pages and can detect corruption before it causes query failures.
• amcheck (contrib module, available since PostgreSQL 9.6) can verify B-tree indexes specifically: SELECT bt_index_check('schema_name.index_name', true).
• Most PostgreSQL drivers (libpq, psycopg2, JDBC, node-postgres) surface XX002 via their standard exception hierarchy. In psycopg2 it raises psycopg2.errors.IndexCorrupted; in JDBC it sets getSQLState() to "XX002".
• Enabling PostgreSQL's data_checksums feature (set at initdb time) causes every page read from disk to be verified against a stored checksum, catching silent corruption early — before it propagates or causes query errors. Without checksums, corruption can go undetected until a btree invariant check fires.

Frequently Asked Questions

Can I lose data when an index is corrupted? Typically no. Index corruption affects the index structure, not the underlying heap (table data). After a successful REINDEX, all rows should be accessible again. However, if the corruption is caused by a deeper storage problem, the heap may also be damaged — verify with pg_amcheck or a full sequential scan after reindexing.

Will VACUUM or AUTOVACUUM fix index corruption? No. VACUUM and AUTOVACUUM reclaim dead tuple space and update visibility maps, but they do not repair structural damage inside index pages. Only REINDEX rebuilds the index from scratch using the current heap data.

How do I prevent index corruption from happening again? Enable data_checksums (requires reinitializing the cluster or using pg_checksums offline in PostgreSQL 12+), ensure fsync = on, use ECC RAM, and verify that your storage hardware (especially RAID controllers and SSDs) correctly honours fsync/FUA semantics. Regularly run amcheck as a scheduled health check.

Does REINDEX CONCURRENTLY fully replace the old index? Yes. REINDEX CONCURRENTLY builds a new index in the background, waits for all existing transactions to finish, then atomically swaps the new index in and drops the old one. Queries continue to use the old (potentially corrupt) index until the swap completes, so there is a window during which you may still see XX002 errors — but the table remains fully accessible throughout.