PostgreSQL invalid-index cleanup and REINDEX CONCURRENTLY
Parent: Resumable index builds · Published reference · snapshot 2026-10-30
↓ Facts as markdownall context files
Source-anchored research for PostgreSQL invalid-index cleanup and REINDEX CONCURRENTLY. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
How it works
- Invalid indexes are excluded from query plans. When indisready remains true, they can still receive writes and generate WAL despite providing no query benefit; the cost depends on their readiness state. (high confidence) [source]
How-to and procedures
- CREATE INDEX CONCURRENTLY IF NOT EXISTS is not an invalid-index repair strategy: an existing invalid name causes the retry to skip construction. Rails issue #52583 supplies a reproducer with a lock timeout followed by such a retry. (high confidence) [source]
Problems, failure modes and limitations
- Do not infer write maintenance solely from indisvalid=false. PostgreSQL's concurrent-reindex phases separately change indisready and indisvalid; failure before readiness can leave a different state from failure after readiness. (high confidence) [source]
- REINDEX CONCURRENTLY must run outside a transaction block, and only one concurrent index build can run per table. System catalogs and exclusion-constraint indexes cannot be rebuilt concurrently. (high confidence) [source]
- Inlet reports separate failure reproductions with indisready=false after a first-scan uniqueness failure and indisready=true after cancellation during a late wait. These observations scope blanket statements that all invalid indexes are maintained on every write. (low confidence) (verified as of 2026-10-09) [source]
- CREATE INDEX IF NOT EXISTS only suppresses an existing-name error; it does not guarantee the existing index matches the requested definition. Therefore it cannot be treated as validation or repair. (low confidence) [source]
Comparisons and alternatives
- REINDEX CONCURRENTLY creates a replacement, swaps names and dependencies, then drops the old index. This is rebuilding, rather than documented continuation of the failed scan. (high confidence) [source]
Facts and statements
- If an INVALID leftover has suffix _ccnew, drop that transient replacement and retry REINDEX CONCURRENTLY. If it has suffix _ccold, the replacement succeeded; drop the obsolete original. Numeric suffixes can distinguish duplicate names. (high confidence) [source]
- After a failed CREATE INDEX CONCURRENTLY, drop the invalid index and retry, or rebuild it with REINDEX INDEX CONCURRENTLY. The REINDEX documentation specifically limits concurrent rebuilding of an invalid index to REINDEX INDEX. (high confidence) [source]
- A unique concurrent index begins enforcing uniqueness before it becomes valid. If the build fails during its second scan, the invalid index continues to enforce uniqueness afterwards. (low confidence) [source]
- Concurrent rebuilding requires two table scans plus waits for existing transactions, trading additional work and elapsed time for continued ordinary writes. Monitor the phases through pg_stat_progress_create_index. (high confidence) [source]
- Find suspect indexes with SELECT indexrelid::regclass, indrelid::regclass, indisvalid, indisready FROM pg_index WHERE NOT indisvalid; inspect the table definition and dependencies before choosing cleanup. (high confidence) [source]
- PostgresAI's January 2026 overhead demonstrations manually change pg_index to indisvalid=false and indisready=true. Treat them as evidence for that explicit state, not evidence that every failed concurrent build leaves an identical state. (low confidence) [source]
- Failed concurrent builds can leave invalid indexes that queries ignore but writes may still maintain. (medium confidence) [source]
- Concurrent reindexing permits writes, but takes SHARE UPDATE EXCLUSIVE locks, scans twice, waits for transactions and adds resource load; it is not lock-free. (low confidence) [source]
- PostgresAI demonstrated maintenance, WAL generation, VACUUM work and blocked HOT updates with an index explicitly set to indisvalid=false and indisready=true. These experiments do not establish identical overhead for every invalid index state. (low confidence) [source]
- Inspect pg_index.indisvalid and indisready to find invalid artifacts and distinguish their readiness. Check constraint dependencies and the index definition before deciding whether to remove or rebuild an artifact. (low confidence) [source]
- Depesz independently demonstrated REINDEX INDEX CONCURRENTLY on a primary-key index referenced by a foreign key; the documented dependency swap explains why manual drop-and-recreate is less straightforward here. (medium confidence) [source]
Children
- No children recorded.