Creating Indexes Concurrently in Alembic

Alembic’s autogenerate noticed the new index=True on Order.customer_ref and wrote op.create_index(op.f("ix_orders_customer_ref"), "orders", ["customer_ref"]) into the revision. On the production database, with sixty million orders, that plain CREATE INDEX blocked every write to the table for five minutes. Alembic and SQLAlchemy can build the index online — PostgreSQL’s CREATE INDEX CONCURRENTLY is exposed through the postgresql_concurrently=True dialect option — but Alembic runs migrations inside a transaction, and concurrent builds refuse to run in one. The piece that joins them is op.get_context().autocommit_block(), which temporarily commits the migration transaction and runs a block in autocommit mode. This guide shows the complete pattern, its failure modes and the env.py settings that make it robust. It extends Alembic & SQLAlchemy Migrations.

Symptom / Error Signatures

These are the failures that lead here:

sqlalchemy.exc.InternalError: (psycopg2.errors.ActiveSqlTransaction) CREATE INDEX CONCURRENTLY cannot run inside a transaction block
[SQL: CREATE INDEX CONCURRENTLY ix_orders_customer_ref ON orders (customer_ref)]

That appears when postgresql_concurrently=True is used without autocommit_block. Without the option, the symptom is the blocking build itself — application sessions waiting on Lock behind CREATE INDEX ix_orders_customer_ref ... in pg_stat_activity. After a failed concurrent build, a rerun fails with psycopg2.errors.DuplicateTable: relation "ix_orders_customer_ref" already exists, or the index exists but is INVALID and unused.

Root Cause Analysis

Alembic’s env.py normally calls context.run_migrations() inside context.begin_transaction(), so each upgrade (or each revision, with transaction_per_migration=True) runs in a transaction. That is what makes PostgreSQL migrations atomic, and it is exactly what CREATE INDEX CONCURRENTLY cannot tolerate, because the build commits internally between phases.

autocommit_block() resolves the conflict locally. On entry it commits the current migration transaction and puts the connection into autocommit; on exit it begins a new transaction. Everything before the block is committed at that point, so a revision that does other DDL and then a concurrent build is no longer atomic as a whole — which is why the concurrent build should live in its own revision.

Immediate Mitigation

1. Cancel a blocking build if one is running. A plain CREATE INDEX is transactional, so cancelling it rolls back cleanly and releases writes.

-- PostgreSQL · requires pg_signal_backend · safe for a non-concurrent build
SELECT pid, now() - query_start AS running FROM pg_stat_activity
WHERE query LIKE 'CREATE INDEX ix_orders_customer_ref%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Rewrite the revision as a concurrent build.

# Python · alembic/versions/20260918_add_ix_orders_customer_ref.py · PostgreSQL
# WARNING: autocommit_block commits the current transaction; keep this revision to this one operation.
from alembic import op

revision = "20260918_ix_cref"
down_revision = "20260917_region"

def upgrade():
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_orders_customer_ref", "orders", ["customer_ref"],
            postgresql_concurrently=True, if_not_exists=True,
        )

def downgrade():
    with op.get_context().autocommit_block():
        op.drop_index(
            "ix_orders_customer_ref", table_name="orders",
            postgresql_concurrently=True, if_exists=True,
        )
# ROLLBACK PATH: alembic downgrade -1 drops the index concurrently.

3. Clean up an invalid index before rerunning. if_not_exists=True makes the rerun a no-op even if the existing index is invalid, so check first:

-- PostgreSQL · migration role · the DROP must run outside a transaction
SELECT c.relname, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'ix_orders_customer_ref';
-- if indisvalid = false:
DROP INDEX CONCURRENTLY IF EXISTS ix_orders_customer_ref;

4. Bound the lock waits. The concurrent build waits for a lock at its start and for older transactions during the build. Set lock_timeout for the migration connection — for example via connect_args={"options": "-c lock_timeout=3000"} in the engine used by env.py — and keep statement_timeout high enough for the whole build.

Permanent Fix / Long-Term Pattern

Configure env.py for per-revision transactions so that an autocommit_block in one revision never commits the work of another, and keep every concurrent operation in a revision of its own:

# Python · alembic/env.py (online mode excerpt) · migration role connection
# WARNING: transaction_per_migration changes failure granularity to one revision at a time.
with connectable.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        transaction_per_migration=True,
    )
    with context.begin_transaction():
        context.run_migrations()

Then make the online form the default for your team. Autogenerate will keep emitting plain op.create_index; a review rule or a small check over new revision files — fail when op.create_index or op.drop_index appears without postgresql_concurrently=True for an existing table — enforces the convention, in the spirit of Migration Linting & Static Analysis. Add a post-deploy query for invalid indexes to the pipeline. Autogenerate has other blind spots worth knowing, collected in why Alembic autogenerate misses changes, and the general zero-downtime sequencing for Alembic is in running Alembic migrations with zero downtime.

Two operational details round this out. First, time the build before production does it for you: restore a recent snapshot into staging and run the revision there, noting how long the concurrent build takes and how much WAL it generates, so you can schedule it away from peak traffic and size the deploy job’s timeout accordingly — the rehearsal technique in testing migrations against production-like snapshots. Second, watch replicas while it runs; a large build produces a burst of WAL that replicas must replay, and lag during the build is normal but should stay inside the budget your read routing tolerates.

If the index is meant to back a unique constraint, check for duplicates before starting: a concurrent unique build fails only at its very end if duplicates exist, after doing all of the work, and leaves an invalid unique index that still rejects new duplicates. A SELECT customer_ref, count(*) FROM orders GROUP BY 1 HAVING count(*) > 1 beforehand saves a wasted hour.

Verification Checklist

• The revision wraps op.create_index in op.get_context().autocommit_block() with postgresql_concurrently=True.
• alembic upgrade head --sql (offline mode) shows CREATE INDEX CONCURRENTLY preceded by a COMMIT.
• env.py sets transaction_per_migration=True, and the concurrent revision contains no other operations.
• After deploy, SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid returns nothing.
• The downgrade drops the index with postgresql_concurrently=True.

Frequently Asked Questions

What does autocommit_block() actually do? It commits the migration’s current transaction, switches the connection to autocommit for the duration of the with block, and begins a new transaction afterwards. Statements inside the block run without a surrounding transaction, which is what concurrent index operations require.

Is if_not_exists=True enough to make the revision idempotent? It prevents a duplicate-name error on rerun, but it also skips over an invalid index left by a failed build. Check indisvalid and drop an invalid leftover before relying on the rerun.

Does this work in offline (--sql) mode? Yes. Offline mode emits the SQL script with a COMMIT before the concurrent statement and a new BEGIN after it, so the generated script also runs the build outside a transaction.

What about MySQL? postgresql_concurrently is PostgreSQL-only. On MySQL, InnoDB builds most secondary indexes online by default; write the statement with op.execute("ALTER TABLE orders ADD INDEX ix_orders_customer_ref (customer_ref), ALGORITHM=INPLACE, LOCK=NONE") if you want it to fail rather than block when an online build is not possible.