Making Index Creation Idempotent Across Retries

The migration that adds an index looked harmless until the deploy runner timed out mid-build, restarted, and the retry died with ERROR: relation "idx_orders_status" already exists. Worse, on a PostgreSQL table large enough to matter you were using CREATE INDEX CONCURRENTLY to avoid a write lock — and the first, interrupted attempt did not disappear. It left an INVALID index behind that the planner will never use, and your naive IF NOT EXISTS guard now sees that broken stub, decides the index “exists,” and skips the rebuild forever. Index creation is the single most common place a migration is not safe to run twice, because indexes carry the messiest failure states: a duplicate-name error on one engine, a silently unusable leftover on the other. This page shows how to make CREATE INDEX converge on the same valid index no matter how many times a pipeline retries it, and it is the index-specific companion to the broader idempotent script design patterns.

Symptom / Error Signatures

You are on the right page if a retried or parallel migration produces one of these:

• PostgreSQL ERROR: relation "idx_orders_status" already exists
• PostgreSQL ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
• MySQL ERROR 1061 (42S21): Duplicate key name 'idx_orders_status'
• A CREATE INDEX IF NOT EXISTS statement that exits 0 but the index never speeds up any query — the planner ignores it
• pg_stat_activity shows a CREATE INDEX CONCURRENTLY still active from a killed prior run, blocking the retry
• A rollback DROP INDEX fails on an auto-generated name that differs between environments

The tell is the second occurrence: the first run created (or half-created) the object, and the retry now collides with it. The most dangerous case throws no error at all — an interrupted CONCURRENTLY build leaves an index whose indisvalid flag is false, and a plain existence check treats it as done.

Root Cause Analysis

A bare CREATE INDEX is an imperative instruction, not a desired-state declaration: it succeeds exactly once and errors on every run after. Retries are guaranteed in any pipeline that restarts pods or rolls across replicas, so the guard has to make the second run a no-op. Two engine-specific mechanics make indexes harder than columns.

On PostgreSQL, CREATE INDEX CONCURRENTLY builds the index without taking a lock that blocks writes, which is why it is the default for large tables — but it cannot run inside a transaction block, so it is exempt from the atomic rollback that protects the rest of your DDL. If it is interrupted, the partially built index is left in place with indisvalid = false. A subsequent CREATE INDEX IF NOT EXISTS with the same name sees a matching relation and does nothing, so the invalid stub is never rebuilt and never used. That is the trap: IF NOT EXISTS is safe for a normal index but unsafe on its own for the concurrent variant, because “exists” and “is usable” are not the same thing.

On MySQL there is no CREATE INDEX IF NOT EXISTS clause at all, so a retry throws ERROR 1061 Duplicate key name unless you guard by querying information_schema.statistics first. The engines diverge enough to be worth a side-by-side.

The deeper reason both diverge is transaction boundary behavior, which is worth mapping against transactional vs non-transactional databases before you decide how defensive each guard must be.

Immediate Mitigation

When a deploy is stuck on an index step, stabilize before re-running.

1. Halt CI/CD auto-retries so the orchestrator stops stacking CREATE INDEX attempts onto a table that may still hold a build from the previous run.

2. Probe what actually exists — and whether it is valid. On PostgreSQL, a leftover invalid index is the thing you must find before re-running.

-- PostgreSQL · read-only · safe against production
-- indisvalid = false means an interrupted CONCURRENTLY build left an unusable stub.
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_orders_status';

-- MySQL 8.0 · read-only · safe against production
-- A non-zero count means the named index already exists; a retry would throw 1061.
SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'orders' AND index_name = 'idx_orders_status';

3. Drop only a confirmed-invalid leftover before rebuilding. Never drop a valid index to “reset” — you would take a live query path away from production.

-- PostgreSQL · run as the migration role · CONCURRENTLY so the drop takes no blocking lock
-- Only run after the probe confirmed indisvalid = false.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

With the stale build cleared, the table is back to a known state and the permanent guarded pattern below can run cleanly.

Permanent Fix / Long-Term Pattern

Two invariants make index creation re-runnable: deterministic names so the guard can find the object, and a validity-aware guard so it rebuilds anything the last run left broken.

Name every index explicitly. Auto-generated names vary across environments and ORM versions, and a guard that cannot predict the name cannot check for it. An explicit name like idx_orders_status is the anchor every existence check and rollback depends on — the same deterministic-naming rule the parent idempotent script design section applies to constraints.

For a normal (locking) index, IF NOT EXISTS is enough because a normal build is atomic — it either fully succeeds or leaves nothing behind, so “exists” implies “valid.”

-- PostgreSQL · run as the migration role · brief write lock — use only on small tables or low-write windows
-- Atomic build: IF NOT EXISTS alone is a sufficient guard here.
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);

For CREATE INDEX CONCURRENTLY, IF NOT EXISTS alone is not safe — it would skip over an invalid leftover. Wrap the logic in a DO block that drops an invalid stub first, then builds. Because CONCURRENTLY cannot run inside a transaction, keep the drop and the create as separate top-level statements — do not put the CREATE INDEX CONCURRENTLY itself inside the DO block or a BEGIN.

-- PostgreSQL · run as the migration role · must run OUTSIDE a transaction · safe at any write volume
-- Step 1: drop only a leftover INVALID index from a prior interrupted run.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
    WHERE c.relname = 'idx_orders_status' AND i.indisvalid = false
  ) THEN
    EXECUTE 'DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status';
  END IF;
END $$;

-- Step 2: build only if no valid index is present. IF NOT EXISTS now sees a clean slate.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders (status);

On MySQL, guard with a prepared statement because there is no IF NOT EXISTS for indexes, and use ALGORITHM=INPLACE for a lock-light online build.

-- MySQL 8.0 · run as the migration role · DDL commits implicitly — each run is independently re-entrant
-- Build the ADD INDEX only when information_schema shows the named index is absent.
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.statistics
   WHERE table_schema = DATABASE()
     AND table_name = 'orders' AND index_name = 'idx_orders_status') = 0,
  'ALTER TABLE orders ADD INDEX idx_orders_status (status), ALGORITHM=INPLACE, LOCK=NONE',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Pair either pattern with a version ledger so the runner can skip a step it knows completed, exactly as the how to write idempotent SQL scripts for safe deploys guide describes — the inline guard and the ledger cover different failure points, and index builds need both because the CONCURRENTLY gap between “built” and “recorded” is exactly where an interrupted run lands.

Verification Checklist

• Every index in the migration has an explicit, deterministic name — no reliance on auto-generated identifiers
• Running the forward script twice against a schema-identical staging database leaves exactly one valid index and the second run exits 0
• On PostgreSQL, SELECT indisvalid FROM pg_index for the new index returns true after applying
• CREATE INDEX CONCURRENTLY statements sit outside any transaction block and outside the DO guard
• A simulated interrupted build (kill mid-CONCURRENTLY, then re-run) ends with a valid, usable index and no invalid stub
• The rollback DROP INDEX ... IF EXISTS re-runs cleanly against a schema where the index is already gone

Frequently Asked Questions

Why does CREATE INDEX CONCURRENTLY IF NOT EXISTS sometimes leave an unusable index? Because IF NOT EXISTS checks only for the presence of a relation with that name, not its validity. When a concurrent build is interrupted, PostgreSQL leaves the partial index in place with indisvalid = false. On the retry, IF NOT EXISTS sees the name is taken and skips — so the invalid stub is never rebuilt and the planner never uses it. You must explicitly check pg_index.indisvalid and drop the invalid index before re-creating it.

Can I just wrap CREATE INDEX CONCURRENTLY in a transaction to make it atomic? No. CREATE INDEX CONCURRENTLY cannot run inside a transaction block and will fail with ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. That is precisely why it is not atomic and can leave an invalid index behind. The safe idempotent pattern keeps the concurrent build as a standalone top-level statement and handles cleanup of a prior invalid build in a separate step before it.

How do I make index creation idempotent on MySQL, which has no IF NOT EXISTS for indexes? Query information_schema.statistics for the table schema, table name, and index name, and only run the ALTER TABLE ... ADD INDEX through a prepared statement when the count is zero. Because MySQL DDL commits implicitly there is no partial-index state to clean up, but each statement must still be independently re-entrant so a half-finished multi-index migration completes on the next run.