PostgreSQL indexes can become corrupted due to hardware failures, software bugs, or unexpected data issues. Use REINDEX or CREATE INDEX CONCURRENTLY to rebuild the corrupted index and restore database integrity.
An index corruption occurs when the internal structure of a PostgreSQL index becomes invalid or inconsistent with the actual table data. This happens rarely but can be caused by hardware failures (RAID controller issues, power loss), software bugs, or filesystem errors. When an index is corrupted, database queries may return incorrect results, missing rows, or fail entirely. The optimizer may stop using the corrupted index, falling back to slow sequential scans. In critical cases, corrupted system indexes can prevent the entire database from starting.
First, enable the amcheck extension and test your indexes:
CREATE EXTENSION IF NOT EXISTS amcheck;Then check specific indexes:
SELECT * FROM bt_index_check('index_name'::regclass);For a comprehensive check including heap verification:
SELECT * FROM bt_index_parent_check('index_name'::regclass);If amcheck returns errors, the index is corrupted and needs rebuilding.
Always back up your database before attempting index repairs:
pg_dump dbname > dbname_backup.sqlIf you have pg_basebackup available:
pg_basebackup -D /backup/path -Ft -zThis ensures you can recover if the repair process encounters unexpected issues.
For a single index (simplest approach, but locks the table):
REINDEX INDEX index_name;To reindex all indexes on a table:
REINDEX TABLE table_name;Note: REINDEX blocks writes and indexes-based reads during execution. For large indexes, this can cause significant downtime.
PostgreSQL 12 and later support non-blocking index rebuilds:
REINDEX INDEX CONCURRENTLY index_name;This creates a new index in the background, then swaps it with the corrupted one. The table remains fully accessible during this operation, though it uses more disk space temporarily.
If running PostgreSQL 11 or earlier, use this non-blocking approach:
CREATE INDEX CONCURRENTLY index_name_new ON table_name (...) WHERE ...;
DROP INDEX CONCURRENTLY index_name;
ALTER INDEX index_name_new RENAME TO index_name;Replace the index definition with your original index definition. This allows concurrent access throughout the operation.
If the corrupted index is UNIQUE, you may have duplicate rows. REINDEX will fail in this case. First, identify and remove duplicates:
WITH duplicates AS (
SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) as rn
FROM table_name
)
DELETE FROM table_name WHERE id IN (
SELECT id FROM duplicates WHERE rn > 1
);After removing duplicates, proceed with REINDEX.
If a system catalog index is corrupted and preventing PostgreSQL from starting:
1. Stop the PostgreSQL server
2. Start with recovery mode:
postgres --single -D /var/lib/postgresql/data -c ignore_system_indexes=onOr edit postgresql.conf:
ignore_system_indexes = on3. Connect and rebuild the corrupted system index:
REINDEX INDEX system_index_name;4. Disable the recovery setting and restart normally.
Index corruption is extremely rare in modern PostgreSQL. Most cases are caused by hardware failures—investigate your storage subsystem if indexes keep becoming corrupted. Some hardware vendors (RAID controllers) are notorious for data corruption issues. Consider using pg_repack utility for automated concurrent reindexing in production environments. Monitor index bloat with pg_stat_user_indexes and maintain healthy vacuum settings to prevent unrelated performance issues. CREATE INDEX CONCURRENTLY can itself create corrupted indexes if interrupted—always monitor the operation. For hosted PostgreSQL services (AWS RDS, Google Cloud SQL, Heroku), check their specific procedures as some automatically detect and repair index corruption.
vacuum failsafe triggered
How to fix "vacuum failsafe triggered" in PostgreSQL
PANIC: could not write to file
How to fix PANIC: could not write to file in PostgreSQL
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL