PostgreSQL: Index Marked INVALID After Failed CREATE INDEX CONCURRENTLY

SQLSTATE XX001 condition data_corrupted class XX — XX — Internal Error severity ERROR
Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.
Last reviewed 28 May 2026 · Reproduced live with the SQL on this page.

! Symptoms Free

Conditions:

  • SELECT indexname FROM pg_indexes WHERE NOT indisvalid returns rows
  • Or: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid
  • EXPLAIN on queries that should use the index shows Seq Scan instead
  • Index exists in \di output but has indisvalid = false
  • Disk space is used by the INVALID index file

1 Environment & reproduce Free

Difficulty: Intermediate  |  PostgreSQL versions: 12, 13, 14, 15, 16, 17

? Root cause Free

  • INVALID indexes still receive DML updates — they consume write I/O without providing query benefit.
  • Cannot use REINDEX INDEX CONCURRENTLY on an INVALID index — must DROP and recreate.
  • DROP INDEX CONCURRENTLY is safe for production (does not block reads/writes). Use it, not plain DROP INDEX.
  • A new CREATE INDEX CONCURRENTLY for the same name will fail if the INVALID index still exists — must drop first.
  • CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
  • If cancelled during second pass: the index may remain INVALID even though it looks complete. Always verify indisvalid = true before trusting the index.

Find all INVALID indexes with pg_index.indisvalid. Drop them with DROP INDEX CONCURRENTLY. Recreate with CREATE INDEX CONCURRENTLY. Verify indisvalid = true after completion.

2 Diagnose Free

RESULT
-- Find all INVALID indexes:
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes i
JOIN pg_index pi ON pi.indexrelid = (
    SELECT oid FROM pg_class WHERE relname = i.indexname
)
WHERE NOT pi.indisvalid
ORDER BY schemaname, tablename;

-- Simpler:
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE NOT (
    SELECT indisvalid FROM pg_index
    WHERE indexrelid = (SELECT oid FROM pg_class WHERE relname = indexname LIMIT 1)
)
ORDER BY tablename;

🔒 Diagnose deeper Pro

🔒

Find every latent occurrence before it ships

The steps above clear this incident. Pro adds the executed, verified depth that stops the whole class of bug across your fleet.

  • ✓Catalog & log queries that surface every at-risk object before a migration ships.
  • ✓Inspect-without-running tricks (\gdesc and friends).
  • ✓Exactly where the log line surfaces on RDS, Azure & Cloud SQL.
  • ✓Cross-version gotchas, verified on PostgreSQL 14–18.
Unlock Pro from $24.99/mo · or $199/yr — unlocks every Pro section.

Every Pro query on this site is executed against real PostgreSQL and verified — we never publish an untested snippet.

3 Recovery & verify Free

Find all INVALID indexes with pg_index.indisvalid. Drop them with DROP INDEX CONCURRENTLY. Recreate with CREATE INDEX CONCURRENTLY. Verify indisvalid = true after completion.