Alembic

Schema migrations for SQLAlchemy. Interview-relevant because migration mistakes cause outages, and the questions are about the failure modes rather than the commands.

The model

Migrations form a linked list (or a DAG, with branches). Each revision knows its parent via down_revision, and the database records which revision it’s on in alembic_version.

"""add user status

Revision ID: a1b2c3d4
Revises: 9f8e7d6c
"""
revision = "a1b2c3d4"
down_revision = "9f8e7d6c"

def upgrade() -> None:
    op.add_column("users", sa.Column("status", sa.String(20), nullable=True))

def downgrade() -> None:
    op.drop_column("users", "status")

alembic revision --autogenerate -m "add user status"
alembic upgrade head
alembic downgrade -1
alembic current
alembic history --verbose

Autogenerate is a draft, not an answer

--autogenerate diffs your models against the database. Always read what it produced. It reliably misses:

• Column type changes on some backends, and server defaults.
• Renames — it emits a drop plus an add, which destroys data. This is the one that bites.
• Constraints without explicit names, check constraints, and most indexes on expressions.
• Anything outside its include_object filter — enums, views, functions, triggers, extensions.

A rename must be written by hand:

op.alter_column("users", "email_address", new_column_name="email")

Name your constraints via a naming convention in MetaData, or autogenerate produces migrations that can’t be reversed because the constraint has a backend-generated name.

Zero-downtime migrations

The core constraint: during a deploy, old and new application code run simultaneously. Every migration must be compatible with both.

That forces the expand/contract pattern:

Three deploys, not one. Attempting it in a single step means a window where old code writes a column the new schema removed, or new code reads a column that doesn’t exist yet.

Never in one step: rename a column, drop a column still referenced by running code, add a NOT NULL column without a default, or change a type incompatibly.

Locks

The failure that takes a service down:

# DANGER on Postgres: ACCESS EXCLUSIVE lock, rewrites the whole table
op.add_column("orders", sa.Column("status", sa.String, nullable=False, server_default="new"))

# Safe: add nullable, backfill in batches, then set NOT NULL
op.add_column("orders", sa.Column("status", sa.String, nullable=True))

Modern Postgres avoids a rewrite for a nullable add, and for a non-null add with a constant default since PG 11 — but a NOT NULL add on an older version or a volatile default still rewrites, holding an exclusive lock for the duration.

Indexes must be built concurrently, which requires leaving the transaction:

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

Alembic wraps migrations in a transaction by default; CREATE INDEX CONCURRENTLY cannot run inside one.

Set a lock timeout so a migration that can’t acquire its lock fails fast instead of queueing behind a long query and blocking every subsequent one:

SET lock_timeout = '5s';

That last point is the subtle killer: a blocked DDL statement blocks all later queries on that table, so a migration waiting behind a slow read can take the whole service down. See 13_zero_downtime_migrations.md.

Backfills belong outside migrations

# WRONG - one transaction, one lock, unbounded runtime
op.execute("UPDATE orders SET status = 'legacy' WHERE status IS NULL")

A migration touching millions of rows holds a transaction open for minutes. Do the schema change in the migration and the backfill in a separate batched job that commits as it goes and can be resumed.

Practical notes

• Migrations run once, in one place. Multiple app replicas starting simultaneously must not all run upgrade head — use a dedicated migration job or an advisory lock. See 20_advisory_locks.md.
• Branches happen when two developers create revisions from the same parent. alembic merge resolves it; catching it in CI (alembic heads returning more than one) is better.
• Downgrades are frequently untested fiction. Write them, but plan to roll forward. For anything destructive, a downgrade cannot restore dropped data anyway.
• Test migrations against a production-shaped copy. A migration that takes 200ms on an empty dev database can take 40 minutes on real data.

Interview angle

• “How do you add a NOT NULL column to a large table with no downtime?” — three deploys, expand/contract: add nullable, deploy code writing it, backfill in batches outside the migration, then set NOT NULL and drop the old path. A single-step add can rewrite the table under an exclusive lock.
• “Why can’t autogenerate be trusted?” — it diffs models against the schema and has no concept of intent. A rename appears as a drop plus an add, which loses data. It also misses type changes, server defaults, and objects outside its filter.
• “Your migration hung and the whole service went down. What happened?” — the DDL statement waited on a lock held by a long-running query, and every subsequent query on that table queued behind the pending exclusive lock. Set lock_timeout so it fails fast instead.
• “How do you create an index without blocking writes?” — CREATE INDEX CONCURRENTLY, which needs an autocommit block since Alembic wraps migrations in a transaction and the statement can’t run inside one.
• “Five replicas start at once and all run migrations. Problem?” — yes. Use a dedicated migration job or an advisory lock so exactly one runs. Concurrent upgrade head can conflict or double-apply.
• “Do you write downgrades?” — yes, but treat rolling forward as the real strategy. Downgrades are rarely tested and cannot restore data that a destructive migration removed.