Running Alembic Migrations With Zero Downtime

Your alembic upgrade head step, which took milliseconds against a scratch database, hangs for thirty seconds in production and then either times out or — worse — completes while every write to orders queues behind it, spiking p99 latency and tripping your error budget. The migration was a single autogenerated revision that added a NOT NULL column with a default, or changed a column’s type, or built an index — the kind of change that looks like one op.* call but forces PostgreSQL to take an ACCESS EXCLUSIVE lock and rewrite the whole table row by row. Under live SQLAlchemy traffic the running application never stops writing, so that lock is not held for a moment; it is held for as long as the rewrite takes, and every concurrent transaction blocks behind it. The fix is not a faster server. It is to stop shipping the change as one revision and instead sequence it as an expand-and-contract series of revisions, each of which is individually lock-cheap and backward compatible with the code running around it.

Symptom / Error Signatures

The clearest signature is a deploy that stalls on the migration step and then the database cancels it. On PostgreSQL, with a lock_timeout set, you see the migration abort with:

sqlalchemy.exc.OperationalError: (psycopg2.errors.LockNotAvailable)
canceling statement due to lock timeout
[SQL: ALTER TABLE orders ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'pending']

Without a lock timeout the migration does not error at all — it silently blocks, and the symptom surfaces in the application instead as request latency and connection pool exhaustion while the ALTER TABLE waits at the head of the lock queue. Inspecting pg_stat_activity during the stall shows the migration session in state = active and a growing crowd of application sessions in wait_event_type = Lock. On MySQL 8.0 the equivalent tell is:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError)
(1205, 'Lock wait timeout exceeded; try restarting transaction')

or, when the metadata lock itself is contended, SHOW PROCESSLIST reports sessions parked in Waiting for table metadata lock. A second, quieter signature is a migration that succeeds in staging and fails only in production: staging tables are small, so the rewrite finishes before anyone notices, while the production table has fifty million rows and the same DDL holds its lock for minutes. If your revision contains ADD COLUMN ... NOT NULL, ALTER COLUMN ... TYPE, SET NOT NULL, or a plain op.create_index on a large hot table, treat it as guilty until proven lock-cheap.

Root Cause Analysis

The root cause is that a single Alembic op call can compile to DDL that PostgreSQL or MySQL cannot perform as a metadata-only change, forcing a full table rewrite under an exclusive lock. Alembic itself does nothing wrong — it faithfully emits the ALTER TABLE your model change implies — but it has no notion of table size or traffic, so it will happily generate a statement that is instant on an empty table and catastrophic on a live one. What separates a safe change from a locking one is whether the engine can satisfy it by editing the catalog alone, or whether it must touch every existing row.

The two engines diverge on exactly which changes are cheap, which is why a revision that is safe on one can be ruinous on the other. The table below is the decision surface for the changes that most often need splitting.

The unifying principle is that any change requiring a rewrite must be decomposed into steps that each touch the catalog cheaply and move the row-by-row work into a throttled background job. Adding a NOT NULL column becomes: add the column nullable (metadata-only), backfill it in batches, add a CHECK constraint marked NOT VALID then validate it (which scans without an exclusive lock), and finally promote to NOT NULL. This is the same additive, backward-compatible contract the Expand and Contract Methodology formalizes, and the reason the engine difference matters at all is rooted in how each database treats DDL inside a transaction, detailed in Transactional vs Non-Transactional Databases.

Immediate Mitigation

When a migration is hanging in production right now, stop it from taking the database down with it, then re-sequence the change.

1. Bound the lock so a stuck migration fails fast instead of freezing writes. Always give the migration session a lock_timeout so it releases the queue rather than holding every writer hostage. Pass it through Alembic’s -x argument and read it in env.py.

# env.py · applied to every migration connection before DDL runs.
# PostgreSQL: cap how long a migration will wait for a lock before aborting cleanly.
def run_migrations_online() -> None:
    connectable = engine_from_config(...)
    with connectable.connect() as connection:
        connection.execute(text("SET lock_timeout = '3s'"))
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()

2. Split the offending revision into an expand step that only adds nullable structure. Replace the single rewriting op call with a metadata-only add, so the deploy that ships new code can rely on the column existing without any rewrite happening.

# alembic/versions/xxxx_expand_add_status.py · upgrade()
# PostgreSQL & MySQL 8.0: nullable, no volatile default = metadata-only, brief lock. Safe under live traffic.
def upgrade() -> None:
    op.add_column("orders", sa.Column("status", sa.String(32), nullable=True))

3. Backfill the column in throttled batches, outside any single long transaction. Never UPDATE a large table in one statement — it locks every touched row for the duration. Chunk it by primary key so each batch commits and releases quickly.

-- PostgreSQL · run as the migration or a dedicated worker role · low-write windows preferred.
-- Repeat until zero rows update; each batch is its own transaction so locks stay short.
UPDATE orders SET status = 'pending'
WHERE id IN (
  SELECT id FROM orders WHERE status IS NULL ORDER BY id LIMIT 5000
);

4. Build any index concurrently, escaping Alembic’s wrapping transaction. A plain op.create_index takes a write-blocking lock; CREATE INDEX CONCURRENTLY does not, but PostgreSQL forbids it inside a transaction, so break out of Alembic’s.

# alembic/versions/xxxx_index_status.py · upgrade()
# PostgreSQL only · CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction block.
def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_orders_status", "orders", ["status"], postgresql_concurrently=True,
        )

5. Promote the constraint without a full exclusive scan. Add the NOT NULL guarantee as a CHECK ... NOT VALID first (instant, no scan), validate it under a lighter lock, then set the column NOT NULL, which PostgreSQL can now satisfy by trusting the validated constraint.

-- PostgreSQL · run as the migration role · NOT VALID skips the initial scan; VALIDATE takes only SHARE UPDATE EXCLUSIVE.
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Permanent Fix / Long-Term Pattern

The durable pattern is to make every schema change flow through three ordered Alembic revisions — expand, backfill, contract — that are deployed across separate releases rather than compressed into one. The expand revision adds new, nullable, backward-compatible structure and ships alongside code that can read the old and new shapes. Once that revision is applied everywhere and the new column exists, a backfill revision (or a standalone worker) populates it in throttled batches, keeping each transaction small so no lock is ever held long enough to matter under live traffic. Only after the backfill completes and every deployed application version writes the new column does a final contract revision tighten constraints and drop the legacy structure. Because each revision is individually additive and lock-cheap, alembic upgrade head at any single step is safe to run at peak, and the whole sequence never needs a maintenance window. The design contract for this decomposition lives in the parent Alembic & SQLAlchemy Migrations guide, which covers keeping the revision graph to a single head so the sequence applies in the order you intend.

Two disciplines make the pattern reliable. First, keep the backfill idempotent and resumable — it must survive being killed halfway and re-run without double-writing — which is the same batching and checkpoint discipline the backfill optimization guides tune for batch size against replication lag. Second, treat the contract revision as the one place data can be lost, and gate it behind proof that no running code reads the old structure, reverting the same additive way Rollback Automation describes: redeploy the previous image rather than dropping the new column under pressure. On MySQL, where DDL forces an implicit commit and cannot roll back, keep each revision to a single ALTER TABLE so a mid-sequence failure can never strand the schema between two states.

Verification Checklist

• Every revision in the sequence is either metadata-only or runs CONCURRENTLY / online — no bare ALTER TABLE that rewrites a large hot table.
• A lock_timeout is set on the migration connection so a stuck DDL aborts instead of freezing writers.
• The backfill runs in bounded batches, each its own transaction, and is safe to re-run after interruption.
• During a staging apply against a production-sized table, pg_stat_activity (or SHOW PROCESSLIST) shows no application session parked in a lock wait behind the migration.
• The expand revision is deployed and verified everywhere before the backfill starts, and the backfill completes before the contract revision runs.
• alembic current matches a single head after each step, and the column is present in information_schema.

Up one level: Alembic & SQLAlchemy Migrations.

Related

• Fixing Alembic Multiple Heads After a Merge
• Why Alembic Autogenerate Misses Changes
• Expand and Contract Methodology

Frequently Asked Questions

Why does adding a NOT NULL column lock my whole table? Because the engine cannot store a NOT NULL guarantee for rows that do not yet have a value, so it must write the new column into every existing row before the constraint can hold — a full table rewrite under an ACCESS EXCLUSIVE lock on PostgreSQL or a table rebuild on MySQL. Under live traffic that lock is held for the entire rewrite, blocking every concurrent write. Split it: add the column nullable (metadata-only), backfill in batches, add a CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL, which PostgreSQL satisfies by trusting the validated constraint instead of rescanning.

How do I run CREATE INDEX CONCURRENTLY from an Alembic revision? Alembic wraps each migration in a transaction by default, and PostgreSQL forbids CREATE INDEX CONCURRENTLY inside one, so it errors with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Wrap the index creation in op.get_context().autocommit_block() and pass postgresql_concurrently=True to op.create_index. The concurrent build takes no write-blocking lock, so writes continue while the index is created, at the cost of a slower build and the possibility of an INVALID index if it fails — which you drop and rebuild.

Should the backfill live inside the Alembic revision or in a separate worker? For anything but a small table, prefer a separate throttled worker over a backfill inside upgrade(). A revision runs inside one connection and, on PostgreSQL, one transaction, so a large in-revision UPDATE holds locks and bloats the transaction for its entire duration and cannot be paced against replication lag. A standalone worker commits each batch, can be throttled and resumed, and lets alembic upgrade head stay fast. Keep the revision to the schema change and move the row-by-row work outside it.