Running Concurrent Index Builds Outside Migration Transactions

You did the right thing: the new index on a busy table uses CREATE INDEX CONCURRENTLY, so writes keep flowing while it builds. The migration fails instantly with ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Your migration tool wraps every migration in BEGIN ... COMMIT — normally a feature, since PostgreSQL DDL is transactional — and a concurrent build is one of the handful of statements that refuse to run that way. The fix is not to drop CONCURRENTLY; it is to tell the tool that this one migration runs without a transaction, and to structure it so that running without one is safe. This guide shows how in each major tool, and why the non-transactional migration needs different safety rules. The background on which engines and statements are transactional is in Transactional vs Non-Transactional DDL.

Symptom / Error Signatures

The failure is immediate and unambiguous:

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ERROR:  DROP INDEX CONCURRENTLY cannot run inside a transaction block
ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block
ERROR:  ALTER TYPE ... ADD VALUE cannot run inside a transaction block      -- PostgreSQL 11 and earlier

Tool-specific wrappers add their own context: Flyway reports the SQL state 25001 (active_sql_transaction), Django shows it as django.db.utils.InternalError, and Rails as ActiveRecord::StatementInvalid: PG::ActiveSqlTransaction. A subtler symptom appears when someone “fixes” the error by removing CONCURRENTLY: the migration succeeds, and the table is locked against writes for the entire build — see building indexes with CREATE INDEX CONCURRENTLY for why that is an outage on a large table.

Root Cause Analysis

A concurrent index build works in phases separated by waits: it registers the index, commits, waits for all transactions that could see the table without the index to finish, scans the table, commits again, waits again, and validates. Those internal commits are impossible inside an outer transaction block, so PostgreSQL rejects the statement up front. The same applies to DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, CREATE DATABASE, and — before PostgreSQL 12 — ALTER TYPE ... ADD VALUE.

Every migration tool has an escape hatch that runs one migration without the wrapping transaction, but they are spelled differently:

Running without a transaction changes the failure model. If the build fails partway — a lock timeout during one of its waits, a unique violation, a cancelled deploy — PostgreSQL leaves an index marked INVALID behind. It is not used for queries, but it is maintained on every write and it blocks a plain retry, because CREATE INDEX CONCURRENTLY IF NOT EXISTS sees the name as taken and does nothing, leaving you with a permanently invalid index.

Immediate Mitigation

1. Move the concurrent statement into its own migration. Never mix it with transactional DDL in the same file; tools that detect non-transactional statements refuse mixed files, and those that do not will fail at runtime.

2. Mark that migration as non-transactional in your tool. Examples for the most common tools:

-- PostgreSQL · Flyway V43__index_orders_region.sql · keep this statement alone in the script
-- WARNING: runs outside a transaction; a failure can leave an INVALID index (see step 3).
-- lock_timeout comes from the migration role's default (ALTER ROLE migrator SET lock_timeout = '2s'),
-- because Flyway rejects scripts that mix transactional and non-transactional statements.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_region ON orders (region);
-- ROLLBACK PATH: DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region;

# Python · Django migration · PostgreSQL only · runs outside a transaction because atomic = False
# WARNING: never combine with other operations in this migration; a partial failure is not rolled back.
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0041_order_region")]
    operations = [
        AddIndexConcurrently("order", models.Index(fields=["region"], name="idx_orders_region")),
    ]
# ROLLBACK PATH: the reverse operation is RemoveIndexConcurrently, also with atomic = False.

# Ruby · Rails migration · PostgreSQL · disable_ddl_transaction! removes the wrapping transaction
# WARNING: keep only the concurrent index in this migration.
class IndexOrdersOnRegion < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
  def change
    add_index :orders, :region, algorithm: :concurrently, if_not_exists: true
  end
end
# ROLLBACK PATH: bin/rails db:rollback runs remove_index with algorithm: :concurrently.

3. Make the migration self-healing on retry. Before creating, drop an invalid leftover of the same name. This makes the migration safe to rerun after any failure.

# Shell · pre-step in the deploy job, before the migration tool runs · migration role
# WARNING: only drops the index if it is INVALID; a valid index is left alone.
invalid=$(psql "$DATABASE_URL" -Atc "SELECT count(*) FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  WHERE c.relname = 'idx_orders_region' AND NOT i.indisvalid")
if [ "$invalid" -gt 0 ]; then
  # separate -c flags: one string with both statements would run as a single implicit transaction
  psql "$DATABASE_URL" -c "SET lock_timeout = '2s'" -c "DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region"
fi

The check lives in the runner because DROP INDEX CONCURRENTLY cannot run inside a DO block or any other transaction. Making index creation idempotent across retries gives a complete script.

4. Run with a lock timeout and no statement timeout. The build needs brief locks at its start and end, which lock_timeout protects, but its total runtime can be long; a low statement_timeout will cancel it and leave an invalid index.

Permanent Fix / Long-Term Pattern

Adopt a house rule: every statement that cannot run in a transaction lives alone in a migration that is explicitly marked non-transactional, is idempotent, and handles invalid leftovers. Enforce it with a lint rule that fails any migration containing CONCURRENTLY unless the file carries the tool’s non-transactional marker — the kind of check covered in Migration Linting & Static Analysis. Keep a pre-deploy query in the pipeline that fails if any invalid indexes exist, so a leftover from last week’s failure is noticed before it confuses the next deploy.

Framework-specific guides cover the details for each ORM: adding indexes concurrently in Rails, creating Postgres indexes concurrently in Django, and creating indexes concurrently in Alembic. The underlying rule is the same everywhere: separate what must be atomic from what must be online.

Verification Checklist

• Every migration containing CONCURRENTLY contains no other DDL and is marked non-transactional for its tool.
• The migration can be run twice in a row in staging without error and without creating a duplicate index.
• A staging test that cancels the build midway, then reruns the migration, ends with one valid index.
• SELECT count(*) FROM pg_index WHERE NOT indisvalid returns zero after the deploy.
• statement_timeout for the migration session is high enough (or disabled) for the build to complete.

Frequently Asked Questions

Why not just remove CONCURRENTLY so the migration can run in a transaction? Because a plain CREATE INDEX blocks every write to the table for the entire build. On a small table that may be milliseconds; on a large, busy one it is an outage. Keep CONCURRENTLY and run the migration outside a transaction instead.

Is it safe to put several concurrent index builds in one non-transactional migration? It works, but a failure on the second leaves the first built and the second invalid, and the retry must handle both. One index per migration keeps failures easy to reason about and retries trivial.

Does MySQL have the same problem? No, in a different way. MySQL DDL is never transactional: each statement commits implicitly, and online index builds use ALGORITHM=INPLACE, LOCK=NONE without any special transaction handling. The issues there are implicit commits and metadata locks, covered in avoiding implicit commits in MySQL DDL migrations.

Can Prisma run a migration outside a transaction? Prisma Migrate does not offer a per-migration transaction switch. Put the CREATE INDEX CONCURRENTLY statement alone in its own migration file; when a migration consists of a single statement, it can run without being wrapped in a multi-statement transaction block. Verify the behaviour against your Prisma version in staging before relying on it.