CREATE INDEX CONCURRENTLY failure within transaction blocks

The short answer

You cannot wrap CREATE INDEX CONCURRENTLY in a transaction, so full atomicity across a migration that mixes it with transactional DDL is not achievable in PostgreSQL. The recommended pattern is to split the deployment into ordered, separately committed steps: run all transactional DDL inside one BEGIN…COMMIT block, commit it, then run CREATE INDEX CONCURRENTLY as its own step. Atomicity is replaced by an explicit forward-only or compensating-rollback plan.

Why the conflict exists

Confirmed behavior: CREATE INDEX CONCURRENTLY performs multiple table scans and waits for old transactions in phases, managing its own internal transaction state. PostgreSQL therefore rejects it inside a user transaction block with an error such as CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Regular DDL like ALTER TABLE is fully transactional, which is why the mismatch only appears with the concurrent variant.

Recommended migration structure

1. Step 1 (transactional): Apply all schema changes in one transaction. If anything fails here, the whole step rolls back cleanly and nothing needs compensating.
2. Step 2 (non-transactional): Run CREATE INDEX CONCURRENTLY after step 1 commits, ideally in a separate migration file or deployment phase.
3. Step 3 (verify): Confirm the index is valid before declaring the deployment done.

-- migration_001_up.sql (runs in one transaction)
BEGIN;
ALTER TABLE orders ADD COLUMN status text;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
COMMIT;

-- migration_002_up.sql (must run outside a transaction)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Handling failure of the concurrent step

If step 2 fails, step 1 is already committed — there is no automatic rollback. Two acceptable strategies:

• Forward-only (preferred): Treat the schema change as safe on its own (adding a nullable column or a default is usually backward-compatible), clean up, and retry the index. A failed CREATE INDEX CONCURRENTLY can leave an invalid index entry behind; it does not get used by the planner but must be removed before retrying.
• Compensating rollback: Run a reverse migration that drops the index remnants and then undoes step 1's DDL in a new transaction.

-- Check for an invalid leftover index
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'idx_orders_status'::regclass;

-- Clean up if indisvalid = false
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

Tooling notes

Migration frameworks handle this with explicit non-transactional scopes: Flyway supports it via per-migration configuration or by placing the concurrent index in a migration that runs outside a transaction; Liquibase offers runInTransaction="false" on a changeset. In both cases, define a separate rollback/changeset that drops the index and reverses the DDL so a failed deployment has a scripted recovery path.

Verification before production

Rehearse in staging: commit the DDL step, then deliberately fail the index step (for example, by cancelling it mid-build) and confirm that pg_index.indisvalid shows the leftover, that DROP INDEX CONCURRENTLY removes it, and that your reverse migration restores the prior schema. Also check pg_locks during the concurrent build to confirm no exclusive lock is held on the table. One caveat: on write-heavy tables, schedule the concurrent build during low-traffic windows, since it still takes brief locks and can be slowed by long-running transactions.