What is this issue?
An invalid index is one where the indisvalid flag is false in pg_index.
This typically happens when:
- A CREATE INDEX CONCURRENTLY operation was interrupted or failed
- The index build ran out of disk space
- A unique constraint violation occurred during concurrent index creation
- The database crashed during index creation
Invalid indexes are completely ignored by the query planner but still:
- Consume disk space
- Require maintenance by autovacuum
- May block other operations
Why it matters
Wasted Resources
Invalid indexes consume disk space and maintenance resources without providing any benefit
Failed Constraints
If the index was for a unique constraint, the constraint is not being enforced
Missing Performance
Queries that should use this index are doing sequential scans instead
Confusion
Developers may assume the index exists and is working
How PG Pilot detects it
```sql SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_indexes JOIN pg_index ON indexrelid = (schemaname || '.' || indexname)::regclass WHERE NOT indisvalid; ```
How to fix it
Drop the invalid index
First, remove the failed index:
DROP INDEX CONCURRENTLY schema_name.invalid_index_name;If CONCURRENTLY fails, you may need to use a regular DROP INDEX
(which will lock the table briefly).
Investigate the failure cause
Before recreating, understand why it failed:
- Check PostgreSQL logs around the time of creation
- Verify sufficient disk space
- For unique indexes, check for duplicate values
Recreate the index
Use CONCURRENTLY to avoid locking the table:
CREATE INDEX CONCURRENTLY index_name ON table_name (columns);Monitor the creation progress with:
SELECT * FROM pg_stat_progress_create_index;Prevention
- Ensure sufficient disk space before creating large indexes
- Monitor concurrent index creation for errors
- Test unique constraints with existing data before creating
- Use monitoring to detect invalid indexes immediately