Cleaning Up INVALID Indexes After a Failed Concurrent Build

You kicked off a CREATE INDEX CONCURRENTLY on a hundred-million-row orders table, the deploy timed out, and someone hit Ctrl-C — or the migration tool’s statement_timeout fired, or a deadlock killed the session mid-build. The command reported failure, the deploy rolled back, and everyone moved on. A day later write latency on that table is up ten percent for no obvious reason, and \d orders in psql shows an index you thought never got created, quietly annotated INVALID. That annotation is the whole story: PostgreSQL’s concurrent build is not atomic, so when its second pass dies it leaves a half-built index in place rather than rolling it back. The planner refuses to read from it because it may be incomplete, but the storage engine still maintains it on every INSERT, UPDATE, and DELETE. You are paying the full write cost of an index and getting none of its read benefit. This page is the cleanup: how to detect the state with pg_index.indisvalid, drop the dead index without blocking writers, and retry the build so it completes cleanly this time.

The good news is that an invalid index is inert as far as query results go — dropping it changes no answer any query returns, so the cleanup itself is safe to run at any hour. The recovery is a specific instance of the broader discipline in Online Index Management: build additively, verify before you trust, and remove nothing until you have proven it is safe to remove. Here the thing to remove has already proven itself safe — it was never valid to begin with.

Symptom / Error Signatures

The first signature is the one that opened this page: a CREATE INDEX CONCURRENTLY that did not report clean success. When the build is cancelled or times out you see the interrupt directly:

ERROR:  canceling statement due to user request

or, when a lock budget or statement budget fired instead:

ERROR:  canceling statement due to statement timeout
ERROR:  canceling statement due to lock timeout
ERROR:  deadlock detected

Any of these on a concurrent build is your cue to go looking for a leftover. The clinching signature comes from psql’s \d on the table, where the index appears flagged:

Indexes:
    "idx_orders_customer_created" btree (customer_id, created_at) INVALID

The authoritative check is the catalog itself. pg_index.indisvalid = false (often with indisready = false as well) is the definitive marker that a concurrent build did not finish:

-- PostgreSQL · run against the PRIMARY as any read role · read-only, safe anytime.
-- Context: any row returned is a failed-build leftover taxing writes while serving no reads.
SELECT c.relname AS index_name,
       t.relname AS table_name,
       i.indisvalid,
       i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE NOT i.indisvalid;

If that query returns any row, you have confirmed the problem: an index exists physically, occupies disk, is maintained on every write, and the planner will never choose it. Two subtler tells reinforce it — a duplicate-looking index name with a _ccnew suffix (left by a failed REINDEX INDEX CONCURRENTLY, which builds a shadow index before swapping), and write latency on the table that regressed without any corresponding read-path improvement, because the write tax landed but the read benefit never did.

Root Cause Analysis

A plain CREATE INDEX runs as a single atomic operation under a heavy lock: it either completes or rolls back, and a rollback leaves nothing behind. CREATE INDEX CONCURRENTLY (CIC) trades that atomicity for availability. To avoid blocking writes it splits the work into multiple transactions — an initial catalog entry marking the index indisready = false, indisvalid = false, a first table scan that builds the index from a snapshot, a wait for every transaction older than the build to finish, and a second scan that reconciles the rows written during the first. Only after the second scan succeeds does PostgreSQL flip indisvalid to true. Because those phases live in separate transactions, an interruption between them cannot be rolled back as a unit — the catalog row and the partially-populated index files are already committed. PostgreSQL deliberately leaves them in place rather than guessing, marking the index invalid so the planner ignores it while the storage engine keeps maintaining it for correctness (if it ever were validated, its entries must be current). The full mechanics of the two-pass build are covered in building indexes with CREATE INDEX CONCURRENTLY.

This is the sharpest divergence between the two engines, and it is worth stating side by side because the cleanup you owe depends entirely on which one you are on:

The practical upshot: MySQL engineers reading this page after a failed online DDL have nothing to clean — a failed ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE unwinds itself, and the fix is to address the cause (usually a too-small innodb_online_alter_log_max_size or a blocking transaction) and rerun. PostgreSQL engineers own a real cleanup, because the invalid index will sit there taxing writes indefinitely until someone drops it. It never self-repairs; there is no background process that retries or removes it.

Immediate Mitigation

The goal is to remove the write tax now and restore a working index. Run these against the primary as a role that owns the index (or a superuser); the drop must run outside a transaction block just like the build did.

1. Confirm the invalid index exists and capture its exact definition. You need the definition to rebuild it correctly after dropping it — do not skip this and lose the column list.

-- PostgreSQL · run against the PRIMARY as any read role · read-only, safe anytime.
-- Context: records the exact DDL so the retry rebuilds the same index; note the INVALID ones.
SELECT indexrelid::regclass AS index_name,
       indrelid::regclass  AS table_name,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE NOT indisvalid;

Verify before proceeding: you have the index name and its full CREATE INDEX definition recorded. If the name carries a _ccnew suffix, it is a failed REINDEX shadow — dropping it is still correct, and the original index it was rebuilding is untouched and still valid.

2. Drop the invalid index concurrently so the cleanup itself never blocks. A plain DROP INDEX takes an ACCESS EXCLUSIVE lock that queues behind in-flight queries; the concurrent form avoids that.

-- PostgreSQL · run as the index owner · MUST run OUTSIDE a transaction block (no BEGIN/COMMIT).
-- Context: safe anytime — an INVALID index is never read, so dropping it changes no query result.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

Verify before proceeding: re-run the detection query from the Symptom section and confirm it returns zero rows. Write latency on the table should begin to recover as the maintenance overhead disappears.

3. Resolve the cause before you retry, or you will land right back here. The most common causes are a statement_timeout too short for the build, a lock_timeout firing on the brief metadata lock, or a long-running transaction that stalled the wait phase until something killed the session. Check for the last one first.

-- PostgreSQL · run against the PRIMARY as a monitoring role · read-only, safe anytime.
-- Context: any transaction older than a few minutes will stall the next CIC's wait phase — resolve it first.
SELECT pid, state, now() - xact_start AS txn_age, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY txn_age DESC
LIMIT 10;

Verify before proceeding: the oldest transaction is seconds old, not minutes, and your migration session’s statement_timeout is either disabled or generously above the expected build duration for this table size.

4. Retry the build outside a transaction, with a lock budget but no statement budget. Rebuild from the definition you captured in step 1. Run it in its own connection with autocommit on — most migration tools need an explicit flag (executeInTransaction=false in Flyway, disable_ddl_transaction! in Rails).

-- PostgreSQL · run as the table owner · MUST run OUTSIDE a transaction block.
-- Context: no statement_timeout on this session; lock_timeout bounds only the brief metadata lock.
SET lock_timeout = '2s';
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
  ON orders (customer_id, created_at);

Verify before proceeding: the statement returns without error and pg_index.indisvalid is now true for the index (the check in the Verification Checklist confirms this). If it fails again, drop the new invalid leftover and diagnose before a third attempt.

Permanent Fix / Long-Term Pattern

The durable fix is to stop failed builds from silently accumulating in the first place, which is a matter of how the build is invoked, not how it is cleaned up. Three habits close the gap. First, never wrap a concurrent build in a statement_timeout inherited from ordinary migrations — an index build on a large table legitimately runs for many minutes, and a short timeout guarantees an invalid leftover every time. Set SET statement_timeout = 0 for the build’s session while keeping a small lock_timeout so only the brief metadata lock fails fast. Second, gate every deploy on a catalog scan for invalid indexes, so a leftover is caught by the pipeline instead of by a latency alert days later:

-- PostgreSQL · CI/CD post-migration gate · run against the PRIMARY as a read role · read-only.
-- Context: fail the deploy if any invalid index survives a migration step — catches leftovers immediately.
SELECT indexrelid::regclass AS invalid_index
FROM pg_index
WHERE NOT indisvalid;

Wire that into the pipeline so a non-empty result fails the build. Third, make the build idempotent and self-cleaning: because a concurrent build cannot resume a partial one, a re-runnable migration should drop any invalid index of the target name before rebuilding, so a retry starts clean. That guard is a direct application of idempotent script design to index DDL:

-- PostgreSQL · migration step · MUST run OUTSIDE a transaction block.
-- Context: drops a prior failed leftover (if any) before rebuilding, so the step is safely re-runnable.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
  ON orders (customer_id, created_at);

The deeper preventive context — running the build outside a transaction, sizing the lock budget, and watching progress through pg_stat_progress_create_index so you catch a stalled wait phase before it gets killed — lives in the parent Online Index Management guide and its companion on building indexes with CREATE INDEX CONCURRENTLY. Treating the invalid-index scan as a standing deploy gate, rather than a thing you remember to check, is what turns this from a recurring surprise into a non-event.

Verification Checklist

• The detection query on pg_index.indisvalid returns zero rows after the cleanup.
• \d orders in psql no longer shows any index flagged INVALID.
• The retried index reports indisvalid = true and indisready = true.
• No _ccnew-suffixed shadow index remains from a failed REINDEX.
• EXPLAIN on the target query now shows an Index Scan, not a Seq Scan.
• Write latency on the table has returned to its pre-build baseline.
• A CI/CD gate fails any deploy that leaves an invalid index behind.

Up one level: Online Index Management.

Related

• Online Index Management
• Building Indexes with CREATE INDEX CONCURRENTLY
• Dropping Indexes Online Without Blocking Queries

Frequently Asked Questions

Can I just re-run CREATE INDEX CONCURRENTLY to fix an invalid index? No — the retry will fail or, worse, silently create a second index. If you use the same name, CREATE INDEX CONCURRENTLY IF NOT EXISTS sees the invalid index already exists and skips the build entirely, leaving the invalid one in place; without IF NOT EXISTS it errors on the name collision. A concurrent build cannot resume or repair a partial one. You must DROP INDEX CONCURRENTLY the invalid index first, then build fresh. That is exactly why a re-runnable migration should drop the leftover before rebuilding.

Is it safe to drop an invalid index while the application is live? Yes. An invalid index is never used to answer a query — the planner ignores it because it may be incomplete — so dropping it cannot change any result the application sees. Use DROP INDEX CONCURRENTLY so the drop itself takes only a light lock and does not block writers, and the operation is safe at any traffic level. The only thing you remove is the write-maintenance overhead the invalid index was costing you, which is the outcome you want.

Why does MySQL not leave an invalid index after a failed online build? Because InnoDB’s online DDL is atomic where PostgreSQL’s concurrent build is not. A failed ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE rolls back as a unit, leaving no partial index and nothing to clean up — you simply address the cause and rerun the ALTER. PostgreSQL’s CREATE INDEX CONCURRENTLY splits its work across multiple committed transactions to avoid blocking writes, so an interruption cannot be unwound and the half-built index is deliberately left behind marked indisvalid = false. The availability guarantee is what costs you the cleanup.