Zero downtime database migrations: how to ship schema changes without downtime

You can have a perfect deployment pipeline and still take your app down with one migration. The atomic release model keeps the old code serving traffic until the new release is healthy, which means that for a few seconds, sometimes a few minutes, two versions of your application are talking to the same database. Every schema change has to survive that overlap.

This is the part most deployment guides skip. They explain the symlink flip and stop. So here is the practical version: what actually breaks, the pattern that fixes it, and which specific operations are safe to run against a live table in Postgres and MySQL.

Why a migration breaks a zero downtime deploy

Two failure modes account for almost all of it.

The first is schema and code disagreeing. Suppose you rename users.name to users.full_name and ship the migration with the code that uses it. The instant the migration commits, every request still being served by the old release starts querying a column that no longer exists. Those requests 500. It does not matter how clean your release process is. You created a window where the running code and the schema were incompatible.

The second is locking. Some DDL statements take an exclusive lock and hold it while the table is rewritten. On a small table nobody notices. On a table with fifty million rows, that lock can block every read and write for minutes, and worse, it queues behind and in front of other queries. Your app is technically up and answering nothing.

Fix both and migrations stop being the scary part of shipping.

The expand and contract pattern

Expand and contract, sometimes called parallel change, is the standard answer. Instead of one breaking change, you make a series of additive ones, and you accept that a rename takes more than one deploy.

The shape is always the same:

1. Expand. Add the new structure. Do not touch or remove anything the current code depends on. This deploy is safe because the old code simply ignores the new column.
2. Migrate. Write to both the old and the new structure, then backfill existing rows in batches. Now both shapes are correct and either version of the code can serve a request.
3. Switch. Move reads to the new structure and ship it. The old column is still there, still populated, so a rollback is harmless.
4. Contract. In a later deploy, once nothing reads or writes the old structure, drop it.

The important property is that at no point does a running version of your code depend on something that is missing. You can roll back at any step. That is what makes it genuinely zero downtime rather than merely fast.

Which operations are safe on a live table

Not everything needs the full four-step dance. Some operations are metadata-only in modern Postgres and MySQL and can go out in a single deploy. Here is the practical split.

One caveat on MySQL instant DDL: each INSTANT change adds a row version, and the engine allows a maximum of 64 before it refuses. If a table gets a lot of instant column changes over time, rebuild it during a quiet period to reset the row format.

How to rename a column without downtime

This is the case people get wrong most often, so it is worth walking through concretely. Renaming name to full_name takes four deploys, not one.

Deploy 1. Add full_name as a nullable column. Ship nothing else. The old code never sees it.

Deploy 2. Change the application so every write sets both name and full_name. Reads still come from name. Then backfill: copy name into full_name for existing rows, in batches, outside the deploy itself. A single UPDATE across the whole table will hold locks and bloat your write-ahead log, so run it in chunks of a few thousand rows with a pause between them.

Deploy 3. Switch reads to full_name. Keep writing both. This is the deploy where the behavior actually changes, and it is completely reversible because name is still accurate.

Deploy 4. Once you are confident, stop writing name and drop it. Before you do, confirm nothing else reads that column: a background job, an export, a report, a read replica consumer. Mapping which downstream jobs and dashboards still depend on a given column before you drop it is the step that turns a clean migration into an incident when it gets skipped.

Four deploys sounds heavy for a rename. In practice each one is small, none of them can take the site down, and you can batch them with other work. Compare that to a rollback at 3 p.m. with customers watching.

Backfilling large tables safely

Backfills cause more real-world outages than DDL does, because they look harmless. The rules that matter:

• Batch it. Update a bounded number of rows per statement, ordered by primary key, and commit each batch. Never one transaction across the whole table.
• Throttle. Put a short sleep between batches so replication can keep up. Replica lag is how a backfill becomes a user-visible problem on a read-heavy app.
• Run it outside the deploy. A backfill that takes two hours should not be sitting in your deploy pipeline. Run it as a job you can pause, resume, and monitor.
• Make it idempotent. Filter on the rows that still need work rather than tracking an offset, so a restart picks up where it left off.

Migrations in the deploy pipeline

Because every migration in this model is additive and backward compatible, it is safe to run migrations as part of the deploy, before the new release takes traffic. That ordering matters: the schema should be ready when the release goes live, not after.

On DeployManage the migration step runs against the new release directory while the current version is still serving requests, and the release only takes traffic after its health check passes. If a migration fails, the deploy aborts and the old release keeps serving, untouched. That is the whole point of the atomic model applied to schema changes, and it is covered in more depth on our zero downtime deployment page.

Two operational details worth getting right. First, restart your queue workers after the release flips, not before, or a long-running worker will execute old code against the new schema. Second, if you deploy to more than one server, run migrations exactly once rather than once per node.

A checklist before you merge a migration

• Can the currently deployed code still run correctly against this schema? If no, split it.
• Does this statement rewrite the table or take an exclusive lock? Check the operation against the table above.
• Is there a backfill? If yes, is it batched, throttled, and outside the deploy?
• If this deploy is rolled back in five minutes, does the app still work? It should.
• For a drop: has the code that reads that column been in production long enough to be sure?

Migrations that pass all five are boring, which is exactly what you want from a deploy. The rest of the release process, atomic releases, health checks, and instant rollback, is covered in zero downtime deployment explained, and if you are choosing between release strategies, blue green vs rolling deployment compares how each one handles the two-versions-at-once problem this article is really about.