Every growing product eventually hits the same wall: the schema that got you to your first thousand customers can’t support the next hundred thousand. A column needs to change type, a table needs to split, an index needs to be added to a table that’s grown too large to lock. And the business keeps running while you do it — for teams handling millions of daily users, taking the application offline for a migration window simply isn’t an option anymore.
We’ve run this kind of migration dozens of times across fintech, e-commerce, and SaaS platforms with strict uptime SLAs. The failures we’ve seen are rarely about the SQL itself — they’re about sequencing: deploying schema and application code together, skipping the backfill validation step, or having no way to reverse course once a migration is halfway through. This guide walks through the approach we use to change production schemas without an outage, a rollback plan, or a 2 a.m. incident call.
The Core Principle: Expand, Migrate, Contract
Zero-downtime migrations rest on a single idea: never make a change that both the old and new versions of your application can’t tolerate at the same time. Because you can’t deploy database and application changes atomically — there will always be a window where old code and new schema (or new code and old schema) coexist — every migration has to be broken into steps that are individually backward-compatible.
That’s the expand-contract pattern, sometimes called parallel change. You expand the schema by adding the new structure alongside the old one, migrate data and traffic over gradually, then contract by removing what’s no longer needed. Three phases, each one independently safe to deploy and, critically, independently safe to roll back.
Step 1: Expand the Schema Without Breaking Anything
The expand phase only adds — it never removes or renames in place. Add the new column, table, or index; leave every existing structure untouched. Because nothing existing changed, this deploy is safe by construction: old application code doesn’t know the new structure exists, and new application code hasn’t shipped yet.
- Add new columns as nullable first. A NOT NULL constraint on a new column requires a default value to be written to every existing row, which on a large table can mean a long-held lock. Add it nullable, backfill, then tighten the constraint once every row has a value.
- Add indexes concurrently. Postgres supports
CREATE INDEX CONCURRENTLY; MySQL’s default online DDL (or a tool like gh-ost) does the equivalent. A blocking index build on a hot table is one of the most common self-inflicted outages we see. - Never rename a column or table in the expand phase. Add the new one under its own name and treat the rename as a much later contract-phase cleanup, if you do it at all.
Step 2: Backfill Without Starving Production Traffic
Backfilling a new column or table on a table with hundreds of millions of rows is where most zero-downtime migrations actually go wrong — not because the logic is complex, but because a naive single UPDATE statement holds locks and generates replication lag that cascades into application timeouts.
We batch every backfill: update rows in chunks of a few thousand at a time, based on a primary key range, with a short sleep between batches to let replicas catch up and to leave headroom for live traffic. For very large tables we run the backfill as a background job with its own rate limiter, and we always backfill from a replica-lag-aware script that pauses automatically if replication falls behind a threshold — usually one to two seconds. On MySQL, tools like pt-online-schema-change or gh-ost automate this batching and lag-awareness for full table rewrites (adding a column with a new storage layout, for instance); for simple additive changes, native online DDL is usually sufficient and faster.
Whatever mechanism you use, validate the backfill before moving on: row counts should match, and a sample comparison between old and new representations of the data should agree. This is the step teams skip under deadline pressure, and it’s the one that turns a routine migration into a data-integrity incident three weeks later.
Step 3: Cutover — Dual Writes, Feature Flags, and Read Migration
Once the new structure is backfilled and validated, the application needs to start using it — but the golden rule holds: never deploy the migration and the code that depends on it in the same release. Deploy the migration first, let it bake, then deploy code that reads and writes the new structure. Or, for backward-compatible changes, deploy code that can handle both old and new schema first, then run the migration.
For structural changes (splitting a table, changing a data model, moving to a new storage engine) we use dual writes behind a feature flag: the application writes to both the old and new structures simultaneously while reads still come from the old one. Once we’re confident the dual-write path has been stable in production for a meaningful window — typically at least one full business cycle so we’ve seen peak and off-peak traffic — we flip reads over to the new structure behind the same flag, one traffic segment or one region at a time rather than globally in one shot.
Canary the cutover the same way you’d canary a code deploy: a small percentage of traffic first, watch error rates and latency, then ramp. Database cutovers deserve the same caution as application deploys — arguably more, since a bad database read path is harder to instantly roll back than a bad application binary.
Step 4: Contract — Removing the Old Path Safely
The contract phase is where teams get impatient and where we insist on discipline. Don’t drop the old column, table, or write path the day after cutover. Leave it in place, unused, for a full deprecation window — we typically hold for two to four weeks depending on how business-critical the data is — so there’s a fast, cheap way back if something surfaces that testing didn’t catch.
Only once you’ve confirmed nothing reads the old structure (query logs and application metrics are your friend here) do you drop it. Dropping a column or table is itself an operation worth doing carefully on a large table — it can still take a metadata lock — so schedule it during a low-traffic window even though the migration overall required no downtime.
A Rollback Plan Is Not Optional
Every phase of expand-contract should have a defined, tested rollback — not a plan you improvise under pressure. If the expand phase only adds structure, rolling back is simply not deploying the code that uses it. If dual writes reveal a bug, flip the feature flag back to the old read path; the old structure is still being written to, so no data is stale. This is the actual payoff of the pattern: because every step is additive and reversible in isolation, “roll back” is a flag flip or a redeploy, not an emergency restore from backup.
Teams that migrate schema and application logic in one atomic deploy don’t have this option — their only rollback is a full restore, which on a large production database can itself take hours and is precisely the outage the migration was supposed to avoid.
Getting This Right at Scale
None of these steps are exotic — expand-contract has been documented for over a decade. What separates a clean migration from an incident is almost always sequencing discipline and the willingness to move slower than the deadline wants you to. We build this into our clients’ delivery process as part of broader application modernization engagements: define the migration plan, the backfill batching strategy, and the rollback criteria before a single line of the new schema ships, so “zero downtime” is a property of the process, not a hope.