How to Run Zero-Downtime Database Migrations with Alembic

Of every kind of deploy, a schema change is the one that can take the whole site down — not because the migration failed, but because it succeeded at the wrong moment. A migration that holds a lock on your busiest table stalls every request behind it. A column rename that autogenerate quietly turned into a drop-and-add takes your data with it. And a schema the newly-deployed code expects but the still-running old code doesn't understand throws errors for the sixty seconds it takes the rollout to finish. Zero-downtime migrations are the discipline of never letting any of that happen — of changing the database in small, backward-compatible steps that the live application never notices. This is a vendor-neutral guide to doing it with SQLAlchemy and Alembic: where the tooling helps, where it lies, and the handful of patterns that make schema changes boring.

Alembic in Two Minutes

Alembic is SQLAlchemy's migration tool: it records an ordered chain of revision scripts, each with an upgrade() and a downgrade(), and tracks which one your database is currently on. Setup is small — initialize it, then point it at your models' metadata so it can compare the schema you declared against the schema that exists:

alembic init alembic          # creates alembic.ini + the versions/ directory

# in alembic/env.py — wire autogenerate to your SQLAlchemy models
from app.models import Base
target_metadata = Base.metadata

# then, per change:
alembic revision --autogenerate -m "add archived flag to orders"
alembic upgrade head          # apply everything up to the latest revision

That's the whole loop: change your models, autogenerate a revision, review it, apply it. The trap is trusting the middle step, so let's start there.

Autogenerate Is a Draft, Not a Diff

Autogenerate is genuinely useful and genuinely not to be trusted unread. It compares your model metadata to the live schema and writes a first draft — but it sees only what it's built to see. It reliably catches added and dropped tables, columns, indexes, and constraints. It is blind or wrong on a predictable list: it doesn't compare column types unless you enable compare_type, doesn't compare server defaults unless you enable compare_server_default, and — the dangerous one — it has no concept of a rename.

The habit that saves you: after every autogenerate, open the script and confirm it does what you meant, add the things it missed (a data backfill, a rename, a concurrent index), and only then check it in. Autogenerate writes the boilerplate; you own the correctness.

The Expand-Contract Pattern

Here is the single idea that makes migrations zero-downtime: never change a thing in place while code is reading it. During any rollout there is a window where old and new code run simultaneously — the new pods are up before the old ones drain. If the schema only works for one of them, that window is an outage. Expand-contract (also called the parallel-change pattern) removes the window by splitting every breaking change into additive steps that are each backward-compatible. Renaming that column becomes five separate, safe deploys:

# 1. EXPAND — add the new column, nullable. Old code ignores it; nothing breaks.
op.add_column('users', sa.Column('full_name', sa.String(255), nullable=True))

# 2. BACKFILL — copy existing data in batches, not one giant UPDATE (see below).
UPDATE users SET full_name = name WHERE full_name IS NULL LIMIT 5000;  # repeat

# 3. DUAL-WRITE — deploy code that writes BOTH columns and reads the old one.
user.name = value; user.full_name = value

# 4. FLIP READS — deploy code that reads full_name. Old column now write-only.

# 5. CONTRACT — once nothing references `name`, drop it. Separate, final deploy.
op.drop_column('users', 'name')

Each step ships on its own and is safe next to the version before it: the expand is invisible to old code, the dual-write keeps both columns valid during the read-flip, and the contract only runs once you've confirmed nothing touches the old column. It's more deploys than a reckless ALTER, and that's the point — you've traded one risky moment for five boring ones. The same logic is why you version a REST API instead of breaking its shape: additive change first, removal only after every consumer has moved on.

Run the Migration Before the Code — If It's Additive

Ordering is where zero-downtime is won or lost, and the rule is asymmetric. Additive (expand) migrations run before the new code deploys. Adding a nullable column or a new table is invisible to the currently-running old code, so applying it first means the new code arrives to a schema that's already ready — and if the deploy stalls with both versions live, both are fine. Destructive (contract) migrations run after the old code is fully gone. You can't drop a column while anything still writes it, so removal waits until the last old pod has drained. Additive-before, destructive-after — hold that line and the dangerous overlap window simply never contains a schema mismatch.

The practical consequence is that migrations belong in your pipeline as an explicit, ordered step, not as something your app runs on startup. Running alembic upgrade head from application boot means every replica races to migrate at once, and a long migration blocks the app from becoming healthy. Make it a discrete deploy stage — expand step, then code, then (later, in its own release) the contract step — so the ordering above is something your pipeline enforces rather than something you hope for.

MySQL vs Postgres: The DDL Gotchas That Bite

Zero-downtime has a database-specific layer, because the two most common engines behave very differently the moment a migration goes wrong or runs long.

MySQL DDL is non-transactional. This is the one that surprises people coming from Postgres: MySQL implicitly commits before and after each DDL statement, so you cannot wrap a multi-step migration in a transaction and roll it back. If your revision runs three ALTERs and the second fails, the first is already permanent and you're half-migrated with no automatic undo. Defend against it by keeping migrations small and ideally one DDL statement each, writing genuine downgrade() paths you've actually tested, and knowing your manual recovery before you run anything in production. MySQL's online DDL helps with the locking half — many ALTERs support ALGORITHM=INSTANT or INPLACE so they don't copy the whole table under a lock — but check that your specific change qualifies, because the ones that fall back to COPY will lock a big table for a long time.

Postgres DDL is transactional — but locks are the trap. Postgres lets you wrap DDL in a transaction and roll back cleanly, which Alembic does by default. The catch is locking: most ALTER TABLE operations take an ACCESS EXCLUSIVE lock, and even a fast change will queue behind a long-running query and then make every new query queue behind it — a brief lock during a traffic spike is still a stall. Two habits tame it: set a lock_timeout so a migration that can't get its lock quickly fails fast instead of forming a queue, and build large indexes with CREATE INDEX CONCURRENTLY, which doesn't block writes. Note the sharp edge there — CONCURRENTLY can't run inside a transaction block, so it needs Alembic's autocommit escape hatch:

with op.get_context().autocommit_block():
    op.create_index(
        'ix_orders_customer', 'orders', ['customer_id'],
        postgresql_concurrently=True,
    )

The meta-point: “safe on Postgres” and “safe on MySQL” are different checklists. Know which engine you're on and what it does under a lock and under a failure, because the migration that's routine on one can be an outage on the other.

Keep the Revision History Linear

Alembic tracks migrations as a chain, each pointing at the revision before it — and two developers who both branch a new migration off the same parent create two heads. Now alembic upgrade head is ambiguous and your deploy fails with “multiple head revisions.” It's a merge conflict wearing a database costume, and the fix is the same energy: catch it in code review, and rebase one migration onto the other (or run alembic merge) so history stays a single line. A linear chain means upgrade head is always unambiguous, and the order things ran in production matches the order in your repo. When a migration does misbehave at 3 a.m., you'll want the good structured logs around your deploy step to tell you exactly which revision stalled and on what lock.

The Bottom Line

Migrations are only frightening when you change the database in place underneath live traffic. Take that away and the fear goes with it. Read every autogenerated script instead of trusting it — especially for renames, which it turns into data loss. Split every breaking change into expand-contract steps so old and new code are always both valid. Run additive migrations before the code and destructive ones after, as explicit pipeline stages rather than app-startup surprises. And respect your engine's DDL rules: MySQL won't roll a failed migration back, and Postgres will happily lock your busiest table if you let it. None of these are exotic — they're just the difference between a schema change that's a deploy and one that's an incident.