Running CREATE INDEX CONCURRENTLY From Alembic

Problem Statement

The revision adds a geometry column and needs a GiST index on it. Written the obvious way, the migration takes an exclusive lock for the forty minutes the build takes, and the deploy becomes an outage. CREATE INDEX CONCURRENTLY solves that — and cannot run inside the transaction Alembic wraps around every revision. This page covers the two supported escapes and the error handling that makes either one safe to re-run.

Why the Naive Approach Fails

python
def upgrade() -> None:
    op.execute("CREATE INDEX CONCURRENTLY idx_listings_geom "
               "ON listings USING gist (geom)")
sqlalchemy.exc.InternalError: (psycopg.errors.ActiveSqlTransaction)
CREATE INDEX CONCURRENTLY cannot run inside a transaction block

The error is precise and the cause is not in your code — Alembic opened the transaction before your revision’s upgrade() was called. Removing the CONCURRENTLY keyword makes the error go away and reintroduces the lock, which is how most teams end up shipping the locking version by accident.

Where Alembic's transaction begins and ends A timeline of an Alembic run. Alembic opens a transaction, runs the revision's upgrade function, records the version row and commits. The concurrent index build cannot sit inside that span; it must run on a connection in autocommit mode, either nested inside or with the surrounding transaction disabled. The transaction is opened before your code runs default: transaction wraps everything BEGIN … upgrade() … INSERT alembic_version … COMMIT a CONCURRENTLY statement anywhere inside this span is rejected with an autocommit block inside the revision BEGIN … op.add_column AUTOCOMMIT: CREATE INDEX … … version row … COMMIT the build runs outside any transaction; everything else stays atomic

Production-Ready Implementation

The autocommit block, with the two pieces of defensive handling that make it re-runnable:

python
"""add geom to listings and index it concurrently

Revision ID: 8c41f0a2e7b3
"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2                                    # noqa: F401
from geoalchemy2 import Geometry

revision = "8c41f0a2e7b3"
down_revision = "5f9b1c7d0a44"

INDEX = "idx_listings_geom"
TABLE = "listings"


def upgrade() -> None:
    # 1. the transactional part: the column itself is a catalogue-only change
    op.add_column(TABLE, sa.Column("geom", Geometry("POINT", srid=4326,
                                                    spatial_index=False),
                                   nullable=True))

    # 2. the concurrent part, on a connection with no open transaction
    conn = op.get_bind()
    conn.execute(sa.text("COMMIT"))                   # close Alembic's transaction

    # a previous interrupted run may have left an INVALID index behind
    conn.execute(sa.text(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX}"))
    conn.execute(sa.text(
        f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {INDEX} "
        f"ON {TABLE} USING gist (geom)"
    ))

    # 3. refuse to report success on a half-built index
    valid = conn.execute(sa.text(
        "SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass(:n)"
    ), {"n": INDEX}).scalar()
    if not valid:
        raise RuntimeError(f"{INDEX} was created but is INVALID — rebuild it")


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("COMMIT"))
    conn.execute(sa.text(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX}"))
    op.drop_column(TABLE, "geom")

Three details carry the weight. spatial_index=False on the column stops GeoAlchemy2 creating its own locking index as a side effect — without it you get the very lock you are trying to avoid, before the concurrent build even starts. The explicit COMMIT ends Alembic’s transaction so the following statements run in autocommit. And the indisvalid check turns a silent half-failure into a failed migration, which is the difference between finding out now and finding out when the table slows down next month.

The non-transactional revision alternative

When a whole revision consists of concurrent operations, disabling the wrapper is cleaner than committing inside it:

python
# alembic/env.py
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    transaction_per_migration=True,      # each revision gets its own transaction
    transactional_ddl=False,             # …and DDL is not wrapped
)

This is a global setting, so it changes the guarantees for every migration in the project: a revision that fails halfway no longer rolls back. That is acceptable when every revision is written to be idempotent and re-runnable, and dangerous otherwise. Choose it deliberately, at the project level, and document the expectation that all migrations must be safe to re-run.

Choosing between the two escapes The autocommit block keeps other revisions atomic, is scoped to one file, and needs no project configuration. The non-transactional setting is cleaner to read inside the revision but removes rollback for every migration in the project and requires every migration to be idempotent. Scoped to one file, or changed for the project autocommit block other revisions stay atomic no project configuration needed the COMMIT reads oddly — comment it the default choice for one concurrent operation transactional_ddl = False the revision body stays clean no rollback for any migration every revision must be re-runnable worth it when concurrent DDL is the norm rather than the exception

Configuration and Tuning Knobs

lock_timeout should be set inside the revision for the transactional half. The concurrent build does not need it — it takes only weak locks — but op.add_column does, and a blocked ALTER queueing behind a long transaction is exactly the outage the concurrent build was meant to prevent:

python
op.execute("SET lock_timeout = '3s'")

statement_timeout must be unset or generous for the build itself. A deployment pipeline that sets a global five-minute statement timeout will kill a forty-minute index build, leaving an invalid index and a failed deploy. Set it to zero explicitly in the revision if your environment imposes one:

python
op.execute("SET statement_timeout = 0")

maintenance_work_mem speeds the build materially — a GiST build is sort-heavy, and raising this session setting to a gigabyte on a machine that can spare it can halve the wall-clock time.

Deploy window by index strategy How long the deploy is blocked for each approach: a plain CREATE INDEX inside the revision blocks for 38 minutes, a concurrent build inside the revision blocks the pipeline for 94 minutes without blocking the database, and splitting the build out of the pipeline blocks for 3 seconds. How long the deploy pipeline is blocked CREATE INDEX in revision 38 min · and the table is locked throughout CONCURRENTLY in revision 94 min · nothing locked, but the deploy waits split out of the pipeline 3 s · the build runs as its own task The middle row is correct and often still wrong: a deploy that takes ninety minutes is a deploy nobody runs.

Verification Steps

sql
-- the index exists, is valid, and is the right access method
SELECT ic.relname, am.amname, x.indisvalid, x.indisready
FROM pg_index x
JOIN pg_class ic ON ic.oid = x.indexrelid
JOIN pg_am am ON am.oid = ic.relam
WHERE ic.relname = 'idx_listings_geom';

-- no invalid indexes anywhere in the schema
SELECT indexrelid::regclass AS invalid_index
FROM pg_index WHERE NOT indisvalid;

The second query belongs in monitoring, not just in a migration checklist. An invalid index costs write throughput indefinitely and is invisible to every read path, so nothing else will ever surface it.

Splitting the revision when the build is long

For an index build measured in hours, keeping the build inside the deployment pipeline is the wrong shape regardless of how the transaction is handled. The pipeline blocks, its timeout eventually fires, and the half-built index is left behind.

The alternative is two revisions and an operational step between them. The first revision adds the column and any constraints, and is fast. The index build runs as a standalone task — a one-off job, a maintenance script, a database administrator — with its own monitoring and no deadline. The second revision then asserts the index exists and fails the deploy if it does not:

python
def upgrade() -> None:
    valid = op.get_bind().execute(sa.text(
        "SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass('idx_listings_geom')"
    )).scalar()
    if not valid:
        raise RuntimeError("idx_listings_geom is missing or invalid — "
                           "run the index build before deploying this version")

That assertion is what keeps the two halves honest: the application version that depends on the index cannot ship until the index is genuinely there.

Gotchas Checklist

  • spatial_index=False on the column is not optional. GeoAlchemy2 creates a locking GiST index by default, and it runs before your concurrent build.
  • DROP INDEX CONCURRENTLY also cannot run in a transaction. The downgrade needs the same COMMIT treatment as the upgrade.
  • IF NOT EXISTS does not detect an invalid index. An invalid index exists, so the create is skipped and the migration succeeds with a useless index. Dropping first is what makes the sequence correct.
  • Offline mode cannot do any of this. alembic upgrade head --sql produces a script for a DBA to run, and the autocommit trick does not translate. Generate the concurrent statements as separate operational steps in that workflow.
  • Long builds outlive short deploy timeouts. If the pipeline caps a migration at ten minutes, the index build belongs outside the pipeline, with a lightweight revision that only asserts the index exists.