How to fix Could not build index in PostgreSQL

Query pg_index to find all invalid indexes:

SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE indexname IN (
  SELECT indexrelname FROM pg_index WHERE indisvalid = false
);

This will show you which indexes failed to build and need attention.

Remove invalid indexes to clean up and free resources:

DROP INDEX CONCURRENTLY index_name;

Use CONCURRENTLY to avoid blocking reads. If the index was created with _ccnew suffix (transient), drop it. If it has _ccold suffix (original), also drop it since the rebuild succeeded.

Check PostgreSQL logs for specific error details:

tail -f /var/log/postgresql/postgresql.log

Common issues:
- Duplicate key error: Run SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1 to find duplicate values
- Disk full: Check available disk space with df -h
- Lock timeout: Terminate idle transactions with SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction'

Address the root cause before retrying:

- Duplicates: Remove duplicate rows or make the column unique: DELETE FROM table WHERE id NOT IN (SELECT MAX(id) FROM table GROUP BY column)
- Disk space: Free up space by archiving old data or adding storage
- Idle transactions: Educate developers to avoid leaving transactions open
- Locks: Ensure VACUUM isn't running during index creation

Once the issue is resolved, rebuild the index:

CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);

Use CONCURRENTLY (available in PostgreSQL 12+) to allow reads while building. For older versions:

REINDEX INDEX CONCURRENTLY index_name;

If no concurrent option is available, schedule during low-traffic periods.