ERROR: index "idx_name" contains unexpected zero page at block 0 HINT: Please REINDEX it.
ID: database/pg-index-corruption
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 16 | active | — | — | — |
Root Cause
A PostgreSQL index has become corrupted, often due to hardware issues (failing disk, ECC memory errors), unclean shutdown, filesystem bugs, or known PostgreSQL bugs affecting specific index types. The index returns incorrect results or errors. The underlying table data is usually intact; only the index structure is damaged.
genericWorkarounds
-
92% success REINDEX the corrupted index to rebuild it from table data
Run: REINDEX INDEX idx_name; For all indexes on a table: REINDEX TABLE table_name; For all indexes in a database: REINDEX DATABASE dbname; Use CONCURRENTLY to avoid blocking: REINDEX INDEX CONCURRENTLY idx_name; (PostgreSQL 12+). Verify afterward: SELECT bt_index_check(idx_oid) using amcheck extension.
-
80% success Investigate and fix the root cause of corruption
Check system logs for hardware errors: dmesg | grep -i error. Verify disk health: smartctl -a /dev/sda. Check for unclean shutdowns: grep -i 'shutdown\|crash' /var/log/postgresql/. Enable data checksums if not already: pg_checksums --enable (requires downtime). Consider enabling full_page_writes = on (default) to protect against partial writes.
Dead Ends
Common approaches that don't work:
-
Dropping and recreating the table to fix index corruption
90% fail
The table data is usually not corrupted; only the index is. Dropping the table loses all data unnecessarily. REINDEX rebuilds the index from the intact table data without any data loss.
-
Ignoring the corruption warning and continuing to query the table
85% fail
A corrupted index returns incorrect query results silently. Queries using the corrupted index may return missing rows, duplicate rows, or wrong rows. This leads to application-level data integrity issues that are very difficult to trace back to index corruption.