How to fix Index corrupted in PostgreSQL

First, safely stop your application to prevent further writes. Then connect to your database and run a query using the suspected index to confirm the corruption:

SET enable_seqscan = off;  -- Force index usage
SELECT * FROM your_table WHERE indexed_column = value;
SET enable_seqscan = on;   -- Reset

If you get different results or errors, the index is likely corrupted.

Enable and run the amcheck extension to formally detect corruption:

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('your_index_name'::regclass);

If amcheck reports DUPLICATE or BACKWARDS entries, corruption is confirmed.

Create a full backup in case anything goes wrong:

pg_dump -U postgres database_name > backup.sql

Store this backup safely on another system.

For a single corrupted index (quickest, but locks writes):

REINDEX INDEX CONCURRENTLY your_index_name;

The CONCURRENTLY option allows reads and writes during rebuilding (slower but no downtime). For all indexes on a table:

REINDEX TABLE CONCURRENTLY your_table_name;

Wait for the command to complete. Progress may be slow depending on table size.

After REINDEX completes, verify normal operation:

SET enable_seqscan = off;
SELECT COUNT(*) FROM your_table WHERE indexed_column = value;
SET enable_seqscan = on;

Run the same query again without forcing the index. Both should return the same count.

Once verification is complete, restart your application:

# For systemd services
sudo systemctl restart your-app

# Or for Docker containers
docker-compose restart web

Monitor logs to ensure the application connects successfully.