Creating indexes concurrently in Alembic migrations

Wrap op.create_index(..., postgresql_concurrently=True) in with op.get_context().autocommit_block(): — a concurrent build cannot run inside the transaction Alembic opens for every migration — and then confirm the index is valid before relying on it. This guide belongs to managing enums, constraints and indexes in migrations.

Quick Answer

Autogenerate emits a plain op.create_index(), which blocks writes to the table for the whole build. Adding postgresql_concurrently=True alone fails, because Alembic has already opened a transaction.

What each form of CREATE INDEX blocks Left: plain CREATE INDEX takes a SHARE lock for the entire build, so inserts, updates and deletes on the table queue behind it; reads continue. Right: CREATE INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock, which allows writes, and builds the index in two table scans; it takes longer, cannot run inside a transaction block, and leaves an invalid index behind if it fails. CREATE INDEX SHARE lock for the whole build INSERT / UPDATE / DELETE wait reads continue fine in a transaction CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE lock writes continue throughout two scans: slower to finish never inside a transaction block On a table taking writes, a ten-minute plain build is a ten-minute write outage for that table.

Before — the autogenerated index, and the first attempt to fix it:

from alembic import op


def upgrade() -> None:
    # Blocks INSERT/UPDATE/DELETE on orders until the build finishes.
    op.create_index("ix_orders_customer_id", "orders", ["customer_id"])


def upgrade_attempt_two() -> None:
    op.create_index(
        "ix_orders_customer_id", "orders", ["customer_id"], postgresql_concurrently=True
    )
# sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error)
# <class 'asyncpg.exceptions.ActiveSQLTransactionError'>:
# CREATE INDEX CONCURRENTLY cannot run inside a transaction block

After — concurrent, outside the transaction, idempotent:

"""index orders by customer

Revision ID: b41d0e9c2a77
Revises: 3f9a1c2d7b10
"""
from alembic import op

revision = "b41d0e9c2a77"
down_revision = "3f9a1c2d7b10"


def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_orders_customer_id",
            "orders",
            ["customer_id"],
            postgresql_concurrently=True,
            if_not_exists=True,
        )


def downgrade() -> None:
    with op.get_context().autocommit_block():
        op.drop_index(
            "ix_orders_customer_id",
            table_name="orders",
            postgresql_concurrently=True,
            if_exists=True,
        )

if_not_exists and if_exists need Alembic 1.12 or later. They make the revision safe to re-run after an interrupted deploy, which matters more here than anywhere else, because a concurrent build is the step most likely to be interrupted.

Execution Context & Async Workflow Integration

A plain CREATE INDEX takes a SHARE lock on the table: reads continue, but every insert, update and delete waits until the build completes. On a large table that is minutes of write outage. CREATE INDEX CONCURRENTLY takes the weaker SHARE UPDATE EXCLUSIVE lock, which allows writes, and pays for it with a more complicated build — it scans the table twice, and waits between phases for transactions that might not see the new index.

Inside autocommit_block() Five steps. Alembic has begun a transaction for the migration. Entering autocommit_block commits it, including any earlier operations in the same migration. The connection is set to autocommit and CREATE INDEX CONCURRENTLY runs as its own statement. Leaving the block begins a new transaction. The version table update at the end of the migration happens in that new transaction. migration transaction open earlier ops in this revision enter autocommit_block() COMMIT what came before CREATE INDEX CONCURRENTLY ... runs outside any transaction leave the block BEGIN a new transaction UPDATE alembic_version Because the earlier work is committed first, a failure inside the block cannot roll it back. Keep a concurrent index in a revision of its own.

Those waits are why it cannot run in a transaction block. The build commits internally between phases so that other sessions learn about the half-built index; inside an explicit transaction there is nothing to commit, so PostgreSQL refuses with CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Alembic runs each migration inside a transaction by default, and op.get_context().autocommit_block() is its escape hatch. On entry it commits whatever the migration has done so far, switches the connection to autocommit, and on exit begins a fresh transaction for the rest of the migration and the version-table update.

Under an async env.py, migrations execute inside connection.run_sync(), so op sees a synchronous Connection and the autocommit block behaves exactly as it does with psycopg — the setup is covered in setting up Alembic env.py for asyncpg. Set transaction_per_migration=True in context.configure(). Without it, one transaction spans every pending migration in the run, and entering the autocommit block commits all of them at that moment — so a failure in a later migration can no longer roll back the earlier ones, which is rarely what anyone expects.

Keep each concurrent index in a revision of its own. Anything placed before the autocommit block in the same revision is committed on entry and cannot be rolled back if the build fails, and a revision half-applied like that is awkward to reason about when you re-run it.

Resolving Warnings, Errors & Common Mistakes

Exact error or symptomRoot CauseProduction Fix
CREATE INDEX CONCURRENTLY cannot run inside a transaction blockpostgresql_concurrently=True without an autocommit block.Wrap the call in op.get_context().autocommit_block().
DROP INDEX CONCURRENTLY cannot run inside a transaction blockThe same, in downgrade().Wrap the drop too.
relation "ix_orders_customer_id" already exists on re-runA previous build failed and left an invalid index with that name.Drop it with if_exists=True, then rebuild; or use if_not_exists=True only after checking validity.
could not create unique index ... Key (email)=(...) is duplicatedA concurrent unique build found duplicates, and left an invalid index.Deduplicate, drop the invalid index, rebuild.
Migration hangs with no CPU usePhase one is waiting for an old transaction, often an idle-in-transaction session.Find it in pg_stat_activity; set idle_in_transaction_session_timeout.
canceling statement due to lock timeoutA lock_timeout set for the migration fired while waiting.Retry after the blocking session ends; keep the timeout.
Checking the result of a concurrent build Three tiles. Valid and ready: indisvalid true, the planner uses it, nothing to do. Invalid after a failure, such as a unique violation or a cancelled build: indisvalid false, it is maintained on every write but never used for reads; drop it concurrently and rebuild. Missing because the migration was interrupted before the statement ran: create it again, which if_not_exists makes idempotent. valid indisvalid = true used by the planner nothing to do invalid indisvalid = false costs writes, serves no reads DROP CONCURRENTLY, rebuild missing no row in pg_index build never started rerun with if_not_exists SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; — run it after every concurrent build.

The row about re-runs hides a trap. if_not_exists=True checks only that an index with that name exists, not that it is usable. After a failed concurrent build, an invalid index with the right name is sitting there, and the re-run skips the build and reports success. Before relying on idempotence, check validity:

import sqlalchemy as sa
from alembic import op


def _index_is_invalid(name: str) -> bool:
    bind = op.get_bind()
    return bool(
        bind.execute(
            sa.text(
                "SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid "
                "WHERE c.relname = :name AND NOT i.indisvalid"
            ),
            {"name": name},
        ).scalar()
    )


def upgrade() -> None:
    with op.get_context().autocommit_block():
        if _index_is_invalid("ix_orders_customer_id"):
            op.drop_index("ix_orders_customer_id", table_name="orders",
                          postgresql_concurrently=True)
        op.create_index("ix_orders_customer_id", "orders", ["customer_id"],
                        postgresql_concurrently=True, if_not_exists=True)

A hanging migration is the other common report. The first phase of a concurrent build waits for every transaction that started before it to finish, and a single session left idle in a transaction — a console, a crashed worker holding a connection — stalls the deploy indefinitely. Setting idle_in_transaction_session_timeout on the database turns that into a bounded wait.

Advanced: Unique Constraints and Foreign Keys Without Long Locks

Indexes are only half the story. Adding a unique constraint or a foreign key the obvious way takes locks that block writes for the length of a full table scan, and each has a two-step alternative.

Where a concurrent build spends its time Bar chart. Waiting for transactions older than the build to finish can be the longest phase if a long-running transaction is open. The first table scan and the validation scan take comparable time. The final wait for transactions that could still see the old snapshot is usually short. wait for older transactions (phase 1) unbounded — one idle-in-transaction session stalls it first scan and build proportional to table size validation scan catches rows written during the build wait for remaining snapshots usually short Illustrative. Set idle_in_transaction_session_timeout so an abandoned session cannot stall a deploy.

A unique constraint can be attached to a unique index that already exists. Build the index concurrently, then promote it — the promotion needs only a brief lock, because the index already guarantees uniqueness:

from alembic import op


def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.create_index(
            "uq_customers_email", "customers", ["email"],
            unique=True, postgresql_concurrently=True, if_not_exists=True,
        )
    op.execute(
        "ALTER TABLE customers "
        "ADD CONSTRAINT uq_customers_email UNIQUE USING INDEX uq_customers_email"
    )

The constraint takes over the index and its name. Using the same name for both keeps Alembic's autogenerate from proposing to drop one and create the other on the next run, and it lines up with a metadata naming convention that generates uq_customers_email in the model.

A foreign key is added NOT VALID first, which skips the check of existing rows and takes only a short lock, and validated afterwards with a lock that allows writes:

from alembic import op


def upgrade() -> None:
    op.execute(
        "ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id_customers "
        "FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID"
    )
    op.execute("ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer_id_customers")

Index the referencing column concurrently before adding the foreign key. PostgreSQL does not create that index automatically, and without it every delete from customers scans orders.

Guard all of this with a lock timeout. SET lock_timeout = '5s' at the start of a migration means a statement that cannot get its lock quickly fails instead of queueing — and a queued ALTER TABLE is worse than a slow one, because every query that arrives after it queues behind it too. The broader patterns are in zero-downtime schema migration strategies.

Keeping Autogenerate From Undoing the Work

Hand-editing a generated migration solves today's deploy; the model still has to describe the same index, or the next alembic revision --autogenerate will notice a difference and generate a revision that drops and recreates it — this time without CONCURRENTLY.

Three places the index must agree Three bands. The migration builds ix_orders_customer_id concurrently in an autocommit block. The model declares Index with the same name and columns, without any concurrently option because that describes the build rather than the index. A CI check fails new revisions that add an index to an existing table without the concurrent option. the migration CREATE INDEX CONCURRENTLY ix_orders_customer_id, in autocommit_block() the model Index('ix_orders_customer_id', 'customer_id') — same name, no build options the CI check fail new revisions that index an existing table without postgresql_concurrently

Declare the index in the model with the same name and columns the migration used. postgresql_concurrently is not a property of the index, only of how it is built, so it does not belong in the model and autogenerate does not compare it:

from sqlalchemy import ForeignKey, Index
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))

    __table_args__ = (Index("ix_orders_customer_id", "customer_id"),)

To stop the problem recurring, change what autogenerate writes rather than remembering to edit it. Alembic's process_revision_directives hook in env.py can rewrite every generated CreateIndexOp on PostgreSQL into the concurrent form, or at least fail loudly so a reviewer sees it. The simplest robust version is a CI check that scans new revision files for op.create_index( without postgresql_concurrently=True and fails unless the table is new in the same revision. Autogenerating and reviewing migration scripts covers the review checklist this fits into.

New tables are the exception. An index on a table created in the same revision has no rows and no concurrent writers, so a plain CREATE INDEX inside the transaction is correct and faster; the concurrency machinery is only for tables that already carry traffic.

Frequently Asked Questions

Does CREATE INDEX CONCURRENTLY block reads?

No, and neither does a plain CREATE INDEX. The difference is writes: the plain form blocks inserts, updates and deletes for the whole build, and the concurrent form allows them.

Can I build several indexes concurrently in one migration?

Yes, each in its own autocommit block or all in one, but they run sequentially and each waits for old transactions. For large tables, a revision per index makes a failure easier to recover from.

Why is my concurrent index build so slow?

It scans the table twice and waits for older transactions between phases. A long-running or idle-in-transaction session makes the wait unbounded; check pg_stat_activity for sessions older than the build.

Do I need CONCURRENTLY on a new table?

No. A table created in the same migration has no rows and no traffic, so a plain index inside the transaction is faster and fully transactional.