EC
XX002
PostgreSQLERRORCommonInternal ErrorHIGH confidence

index_corrupted

Production Risk

Critical — index corruption can cause wrong query results or query failures; REINDEX is required and storage hardware should be investigated

What this means

PostgreSQL detected corruption in an index structure. Index pages or internal index pointers have failed consistency checks, making the index unreliable for query planning.

Why it happens
  1. 1Storage hardware failure affecting index files
  2. 2Power failure during index write operation without battery-backed write cache
  3. 3Index file was modified outside of PostgreSQL (direct disk manipulation)
  4. 4Bug in PostgreSQL or extension that wrote invalid data to an index page
  5. 5RAM errors corrupting data before it was flushed to disk
  6. 6Concurrent unsafe operations on the index
How to reproduce

Any query that reads through a corrupted index, or REINDEX/VACUUM detecting corruption

trigger — this will error
trigger — this will error
SELECT * FROM my_table WHERE id = 1;  -- if the index on id is corrupted

expected output

ERROR:  XX002: index "my_table_id_idx" contains unexpected zero page at block 5

Fix 1

REINDEX the affected index

WHEN The underlying table data is intact and only the index is corrupted

REINDEX the affected index
REINDEX INDEX CONCURRENTLY my_table_id_idx;
-- Or rebuild all indexes on the table:
REINDEX TABLE CONCURRENTLY my_table;

Why this works

Drops and rebuilds the index from the heap data; resolves index corruption without touching table data

Fix 2

Run pg_check or amcheck to verify index integrity

WHEN Investigating the scope of corruption

Run pg_check or amcheck to verify index integrity
-- Using amcheck extension (PostgreSQL 10+):
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('my_table_id_idx');

Why this works

amcheck validates B-tree index structure without modifying data; identifies the extent of corruption

Fix 3

Restore from backup if table data is also corrupted

WHEN Both index and heap data are corrupted

Restore from backup if table data is also corrupted
-- Restore PostgreSQL data directory from a clean backup

Why this works

Safest recovery when both indexes and table data are affected

What not to do

✕ Do not drop and recreate the index without first verifying the heap data is intact

If the heap is also corrupted, the rebuilt index will also be corrupt

✕ Do not ignore index corruption — it can cause incorrect query results

A corrupt index may return wrong rows (or miss rows) without raising further errors once rebuilt from corrupt heap data

Version notes
10

amcheck extension added in PostgreSQL 10 for verifying index integrity

12

REINDEX CONCURRENTLY added in PostgreSQL 12, allowing online index rebuilds without locking

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors