CREATE INDEX CONCURRENTLY IF NOT EXISTS does not fix a broken index

A concurrent index build fails in CI. Deadlock, a dropped connection, a statement_timeout, it does not matter which. The next deploy runs the same migration again, this time with IF NOT EXISTS added so the retry does not error out on a duplicate name:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);

CI goes green. The migration is marked applied. Nobody looks at it again.

The index was never rebuilt. It is still the broken one from the failed run, and it will stay that way indefinitely because nothing in this flow ever produces an error.

What a failed concurrent build actually leaves behind

CREATE INDEX CONCURRENTLY builds the index in multiple transactions instead of one, so it can avoid holding a long-lived lock that blocks writes. That is what makes it safe to run against a live table. It is also what makes a partial failure possible in the first place: a normal CREATE INDEX either fully succeeds or fully rolls back, but CONCURRENTLY cannot roll back cleanly because it already committed intermediate work.

The Postgres documentation is direct about what happens instead:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index.

The index is not rolled back. It exists as a real row in pg_class and pg_index, just marked broken. The indisvalid column in pg_index is what tracks this:

If true, the index is currently valid for queries. False means the index is possibly incomplete: it must still be modified by INSERT/UPDATE operations, but it cannot safely be used for queries.

\d on the table shows it plainly:

Indexes:
    "idx_orders_customer_id" btree (customer_id) INVALID

Three things are true of that index at this point, and all three matter:

1. It is never used by the planner. The docs say it is “ignored for querying purposes because it might be incomplete.” Your query plans get nothing from it.
2. It still costs you on every write. The same sentence continues: “it will still consume update overhead.” Every INSERT, UPDATE, and DELETE on that table keeps maintaining an index that no query will ever use.
3. It still occupies disk space, exactly as if it were valid. pg_relation_size() on an invalid index returns its full size.

If it is a unique index, there is a fourth and nastier detail. The uniqueness check starts being enforced during the second table scan, before the build even finishes. Per the docs: “if a failure does occur in the second scan, the ‘invalid’ index continues to enforce its uniqueness constraint afterwards.” You can end up with a uniqueness constraint that rejects inserts in production, backed by an index that no EXPLAIN will ever show you, because it is invalid and therefore invisible to query planning.

Where IF NOT EXISTS goes wrong

IF NOT EXISTS is good advice for idempotent migrations in general. It is the right instinct that turns a footgun into a different, quieter footgun here.

The Postgres docs describe exactly what the clause checks, and it is narrower than most people assume:

Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing index is anything like the one that would have been created.

Read that literally: IF NOT EXISTS checks whether a relation of that name exists. It has no idea whether that relation is a complete, valid, correctly-built index, or a half-finished one left over from a build that failed last Tuesday. An invalid index still has a name, still lives in pg_class, and still satisfies the existence check.

So the retry that looks safe plays out like this:

1. First deploy: CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id); fails partway through (deadlock, dropped connection, CI job killed for taking too long). An invalid index named idx_orders_customer_id is left behind.
2. Someone adds IF NOT EXISTS so the migration can be safely retried without a “relation already exists” error.
3. Second deploy: CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders(customer_id); runs. Postgres sees a relation named idx_orders_customer_id already exists, emits a notice, and returns success without touching it.
4. Exit code 0. Migration marked applied. The index is exactly as invalid as it was after step 1, and nothing in the deploy pipeline says so.

There is no error at any point after step 1. That is the actual footgun: not that the build can fail, every concurrent build can fail, but that the standard advice for making the retry idempotent is the same clause that makes the failure permanent and silent.

Finding the ones you already have

You do not need to wait for the next failed deploy to check. Any invalid index sitting in a production database right now is dead weight: zero query benefit, full write overhead, full disk cost.

SELECT
  ns.nspname AS schema_name,
  tbl.relname AS table_name,
  cls.relname AS index_name,
  pg_size_pretty(pg_relation_size(cls.oid)) AS index_size,
  idx.indisunique AS is_unique
FROM pg_index idx
JOIN pg_class cls ON cls.oid = idx.indexrelid
JOIN pg_class tbl ON tbl.oid = idx.indrelid
JOIN pg_namespace ns ON ns.oid = cls.relnamespace
WHERE idx.indisvalid = false;

Run that against production after any migration that used CREATE INDEX CONCURRENTLY, and again as a routine health check independent of deploys. Invalid indexes do not fix themselves, and they do not expire.

The fix

The Postgres docs give the recommended recovery path directly, and it is not “run the same CREATE INDEX again”:

The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is to rebuild the index with REINDEX INDEX CONCURRENTLY.)

In practice, that means:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

Two details carry over from the transaction footgun covered earlier on this blog:

• DROP INDEX CONCURRENTLY cannot run inside a transaction block either, the DROP INDEX docs state it plainly: “regular DROP INDEX commands can be performed within a transaction block, but DROP INDEX CONCURRENTLY cannot.” Same rule, same reason: it needs its own transaction boundaries.
• DROP INDEX CONCURRENTLY also does not support CASCADE, so an invalid unique or primary key index built as part of a constraint cannot be dropped this way while the constraint still references it.

If dropping and rebuilding is too disruptive, REINDEX INDEX CONCURRENTLY on the existing (invalid) index name is the alternative the docs mention: it builds the replacement in a separate transaction, then swaps it in under the original name and drops the old one, so there is no window where the index is gone entirely and no new name for you to pick.

What pgfence catches today, and what it does not yet

pgfence’s create-index rule already flags CREATE INDEX without CONCURRENTLY, and its safe rewrite for that finding produces CREATE INDEX CONCURRENTLY IF NOT EXISTS by design, matching the standard idempotent-retry advice. A separate rule, prefer-robust-stmts, goes further and specifically recommends adding IF NOT EXISTS to a CONCURRENTLY build that is missing it, with a message that names the exact risk: a failed concurrent build leaves an invalid index behind.

So pgfence already tells you the right shape of SQL to write, and its own wording already warns that a failed build leaves an invalid index. What it cannot do from static analysis alone is the part that actually matters here: check whether an index with that name is already sitting in the target database, invalid, from a previous failed run. That is not a property of the SQL text. It is a property of live database state, and no linter reading a migration file in isolation can see it.

This is the same shape of problem as pgfence’s stats-aware risk scoring for table size: some questions cannot be answered from the AST, only from the cluster. The honest path is the same one pgfence already takes for size-aware risk, extend the optional stats snapshot (or a direct --db-url check in development) to also query pg_index.indisvalid for any index name a migration is about to CREATE ... CONCURRENTLY IF NOT EXISTS, and turn a name collision against an invalid index into a hard policy violation instead of a silent no-op. Trace mode already captures indisvalid for indexes it observes changing during a traced run; the gap is specifically the case where the index does not change at all, because IF NOT EXISTS never touched it. Closing that gap is on the roadmap rather than shipped today, and this post is the reason it is there: silently passing a permanently broken index is exactly the false-negative failure mode pgfence exists to prevent.

Review checklist

1. Did a CREATE INDEX CONCURRENTLY fail in any environment recently? Check pg_index.indisvalid there before assuming a retry will fix it.
2. Before adding IF NOT EXISTS to a CONCURRENTLY statement purely for retry safety, confirm no invalid index already holds that name.
3. Recovering from a failed build means DROP INDEX CONCURRENTLY (or REINDEX INDEX CONCURRENTLY) first, then rebuild. Re-running the original CREATE INDEX is not a fix.
4. If the index backs a UNIQUE constraint, treat an invalid state as urgent: it can still reject inserts while giving the planner nothing in return.
5. Run the indisvalid = false query as a standing health check, not only right after a migration.

Related

• CREATE INDEX CONCURRENTLY in a transaction is a silent footgun
• Postgres lock mode cheat sheet
• The DDL lock cheat sheet
• The lock_timeout death spiral