The outage is almost never the ALTER
A team plans a schema change around the one statement that worries them. They run it against a restored copy, time it at four seconds, write the runbook, and pick a Tuesday. Then production stalls for nineteen minutes and nobody can explain it, because the four-second statement really did take four seconds. What happened is that it spent nineteen minutes waiting for an exclusive lock behind a long analytics query, and every read that arrived during that wait queued behind the pending lock request. The schema change was correct. The lock protocol around it was never designed, and nothing in the rehearsal could have shown it, because the rehearsal ran on an idle database.
You are probably here because
- An ALTER that timed at a few seconds on a restored copy stalled production for minutes, and the statement itself was never slow
- A backfill has been running for days, replica lag climbs every afternoon, and nobody is certain it can be stopped and resumed where it left off
- A column was dropped in the same release that stopped using it, and the pods still running the old code threw errors until the deploy finished
- There is a schema change sitting in the queue that everyone agrees is necessary and nobody wants to be the person who runs it
The lock section and the deploy-ordering section below deal with the first three directly, and all four come from the same root cause: the change was planned as one statement instead of a sequence of steps that is safe to stop at.
Zero-downtime migration is not one technique. It is a discipline applied to three separate risks that happen to arrive together, and most failed migrations we get asked to review confused them. Naming the three is most of the work.
The DDL itself. Does the statement rewrite every row, rebuild every index, or only touch the catalog? What lock does it take, how long does it hold it, and what does that lock block? These are answerable from documentation and measurable on a copy.
The data movement. Populating a new column, splitting a table, moving to a new store. This runs for hours or days against live traffic, competes with your own workload for I/O, and pushes on replication and on the vacuum or purge machinery that keeps the database healthy.
The version skew. During a rolling deploy, two versions of your application talk to one schema at the same time. On a large fleet with a slow canary, that window is not seconds. It can be an entire business day. Every intermediate schema state has to be valid for both versions, and this is the risk teams skip because it lives in the application, not the database.
Every technique below aims at exactly one of those three. That is why "we used an online schema change tool" is not by itself an answer. Those tools address the first risk well and the other two not at all.
Expand and contract, and why nothing else holds up
The pattern has several names. Parallel change is the one you will find in the refactoring literature; expand and contract is what most database teams say. The idea is that you never transform a schema in one step. You add the new shape alongside the old one, move readers and writers across in stages while both shapes work, then remove the old shape once nothing references it.
Take the change that looks trivial and breaks the most production systems: renaming a column. In place, that rename is atomic in the database and catastrophic in the application, because at the instant it commits, every process still running the old code is issuing statements against a column that no longer exists. Done as expand and contract, the same change is five deploys, each one boring:
Renaming a Column on a Live System
Five deploys over a week or two to accomplish what a rename does in one statement. Teams push back on that cost until the first time they need to stop halfway, and then it pays for itself permanently. The property that makes it work is not the number of steps. It is that every intermediate state is a valid resting place. You can leave the system at step 4 for a month with no consequence beyond a slightly wider row.
The same shape covers the harder changes. Splitting one table into two, changing a column type, moving a foreign key, normalizing a JSON blob into real columns: all of them are add the new thing, write both, backfill, cut reads, remove the old thing. The details differ. The skeleton does not.
The lock is the whole game
In PostgreSQL, most schema changes take an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode, including the one a plain SELECT takes. That is expected and usually fine, because for many operations the lock is held for milliseconds. The part that surprises people is the queue. When your ALTER cannot get its lock because an old reporting query is holding a weaker one, the ALTER waits, and every request that arrives after it waits behind it. One long-running query plus one instant DDL statement equals a full table stall for the length of the query.
The fix is two lines of session configuration and a loop. Set a short lock_timeout on the migration connection, two to five seconds, so a blocked statement gives up quickly instead of camping in the queue. Then retry with backoff. Most attempts fail, one gets a clean window, and the table is never stalled for more than the timeout. Set statement_timeout separately, higher, to bound the operation itself once it starts. Run both on the migration role rather than globally, so a badly chosen value cannot leak into application traffic.
Look at what is already holding locks
Check for sessions that are idle in transaction before starting. An application connection that opened a transaction, read one row, and then went to lunch will hold a lock indefinitely and block the migration and everything queued behind it. Long analytics queries, an ORM leaking transactions on an error path, and an interactive session someone left open in a terminal are the three sources we find most often. Setting idle_in_transaction_session_timeout on the application role closes off the worst of them permanently.
MySQL has the same shape with different names. InnoDB online DDL can do a great deal in place without blocking writes, and MySQL 8.0 added instant column addition so that some ALTERs touch only metadata. But every ALTER still needs a brief exclusive metadata lock at the start and the end, and a metadata lock request queues the same way, so a long transaction touching the table can turn a metadata-only change into a stall. Tools like gh-ost and pt-online-schema-change exist because of exactly this: gh-ost builds a shadow table and follows the binlog rather than installing triggers, pt-online-schema-change uses triggers and an atomic rename, and both end with a cutover that still needs a moment of exclusivity.
Which operations are cheap, and which ones only look cheap
Two statements can be one word apart and three orders of magnitude apart in cost. The table below is the reference we work from on PostgreSQL, which is where most of this work lands. Check your own version, because these behaviors have changed across releases and the improvements are significant.
| Operation | What it actually does | The safe shape |
|---|---|---|
| Add a nullable column | Catalog change only. Fast at any table size | Run it directly, with a lock timeout and retry |
| Add a column with a default | Since PostgreSQL 11 a constant default is stored in the catalog and existing rows are not rewritten. A volatile default still rewrites every row | Constant defaults are safe. Anything computed per row becomes a backfill |
| Add an index | A plain CREATE INDEX blocks writes for the entire build, which is minutes to hours on a large table | CREATE INDEX CONCURRENTLY. It costs two table scans, cannot run inside a transaction block, and can leave an invalid index behind if it fails, so check for one and drop it before retrying |
| Make a column NOT NULL | A full table scan under a blocking lock | Add a CHECK constraint as NOT VALID, VALIDATE it under a weak lock, then SET NOT NULL, which on modern versions uses the validated constraint instead of rescanning |
| Add a foreign key | Locks both tables against writes while it validates every existing row | Add the constraint NOT VALID first so new rows are enforced immediately, then VALIDATE CONSTRAINT as a separate statement |
| Change a column type | Rewrites the whole table and rebuilds every index on it. Widening a varchar or moving varchar to text is the exception and is catalog-only | Treat it as a new column and run the full expand-and-contract sequence |
| Drop a column | Catalog-only and instant. Space is reclaimed later by vacuum | Cheap in the database, dangerous in the application. Stop referencing it in code first, deploy, soak, then drop |
The last row is the one that catches good teams. Dropping a column is the cheapest statement in the list and the most common cause of a post-migration incident, because an old process somewhere is still naming that column in a SELECT. Frameworks that build explicit column lists from the model will do this on your behalf without anyone writing the column name in code, which is why the fix is to mark the column ignored in the model, deploy that, and only then drop.
Risk Weighting We Use When Reviewing a Migration Plan
Weights we score a plan against, summing to 100. They are a review rubric, not measured incident frequencies.
Backfilling a hundred million rows without waking anyone
The naive backfill is a single UPDATE with a WHERE clause. On a large table it holds one transaction open for hours, which is the worst thing you can do to a busy PostgreSQL instance. Dead tuples pile up and autovacuum cannot clean them, because the long-running transaction holds back the horizon that determines what is safe to remove. Replication slots retain write-ahead log the standbys have not consumed. Disk fills. If the statement fails at hour six, you get all of the cost and none of the result. MySQL has the same failure with different symptoms: undo log growth and a history list that will not shrink.
A backfill that behaves is a small piece of production software, not a statement. It has a cursor, it commits per batch, and it can be stopped and restarted from where it left off:
Iterate by key, not by offset. Walk the primary key in order and remember the last key processed. OFFSET makes each batch more expensive than the last, so a job that starts fine gets slower until it never finishes.
Commit every batch. A thousand to ten thousand rows per transaction is the range that works on most schemas. Wide rows with many indexes want the low end. Measure once, then fix the number.
Throttle on a signal that means something. Replica lag is the best one. Pause when it exceeds a threshold you set, resume when it recovers. A fixed sleep between batches is a guess that is wrong at 3 a.m. and wrong again at peak.
Make it idempotent. The WHERE clause should exclude rows already done, so the job shrinks its own workload and restarting costs nothing. That also makes it safe to run twice by accident, which will happen.
Give it one stop command and a progress metric. Rows remaining, rate, and estimated completion, emitted where your on-call can see them without reading a log file.
Verification is part of the job, not a follow-up ticket. Row counts prove almost nothing. Compare the actual values on a random sample large enough to be meaningful, and separately run a query for rows that should have been filled and were not. Do that before you move reads, because after the read cutover a gap is a customer-visible bug rather than a rerun.
Dual writes are a distributed systems problem in disguise
Writing two columns in one table inside one transaction is not a dual write in any interesting sense. It either commits or it does not, and the database guarantees you never see half of it. Do that freely, and prefer doing it in application code rather than a trigger so the behavior is visible to whoever reads the code next.
Writing to two stores is a different animal. Database plus search index, old table plus new service, primary plus cache: there is no shared transaction, so every write has a window where one side succeeded and the other did not. Retries help and do not solve it. Two strategies actually hold up. Write to one source of truth and derive the other from its change stream, using logical replication or binlog capture, which gives you ordering and replay. Or use a transactional outbox, where the write and the intent to propagate commit together and a worker drains the outbox.
Whichever you choose, run a reconciliation job that measures divergence and reports it as a number. An unaudited dual write is an assumption, and the assumption is usually wrong in a small percentage of rows that nobody notices until a customer does.
Two versions of your code will read the same table
This is the risk that lives outside the database and gets the least attention. During a rolling deploy, old pods and new pods serve traffic simultaneously. With a canary and a soak period, that overlap is deliberately long. So the rule is not that the new code must work with the new schema. The rule is that every schema state must work with the code currently deployed and with the code about to be deployed.
That rule dictates the ordering, and the ordering is different in each direction. Additive changes go schema first, then code, because old code ignores a column it does not know about. Destructive changes go code first, then schema, because the schema change is the thing that breaks the old code. Getting this backwards is the single most common way a careful team takes an outage on a migration they understood perfectly.
Two application-layer details deserve a look before you sign off. Prepared statement caching means a connection can hold a plan built against the old schema; after a DDL change some drivers surface an error about a cached plan changing result type, and the cure is to recycle connections at the pool. And if you sit behind a transaction-pooling proxy, confirm what your migration tooling assumes about session state, because advisory locks and session-scoped settings do not behave the way they do on a direct connection.
Send it over and we will tell you what we would change.
Email the migration file, the row count and index list for the table it touches, and one line about how you deploy — all at once, rolling, or canary — to contact@precisionfederal.com. You get back a short written note naming the three things we would change and why. One business day. No charge, no meeting, no deck.
contact@precisionfederal.comMoving the whole database
Sometimes the migration is not a column. It is a major version upgrade, a move between providers, or a change of engine. Three shapes cover nearly all of it.
In-place upgrade. The fastest path for a major version bump, and downtime is minutes rather than hours because data files are reused rather than dumped and reloaded. The catch is the rollback story: once the upgrade has started, going back means restoring, so you need a tested restore and a decision point before the point of no return.
Logical replication cutover. Stand up the target, replicate into it while the source keeps serving, then cut over in a short read-only window. This is the technique with the best downtime profile and the most sharp edges. Logical replication does not carry DDL, so schema changes during the migration must be applied on both sides by hand. Sequences are not replicated and must be advanced at cutover, which is the classic way to end up issuing duplicate primary keys on the new primary. Tables need a usable replica identity. Large objects and some object types do not come along.
Application-level dual write and backfill. The fallback when the source and target are different engines, or when the transformation is too involved for replication. It is the most work and the most control, and it is expand and contract at the level of a whole datastore.
| Cutover step | What goes wrong | What to check first |
|---|---|---|
| Stop writes | Permissions are revoked instead, and the application throws driver-level errors that are slow to recover from | A read-only mode in the application that you have exercised in a rehearsal |
| Drain replication | The cutover proceeds while the target is still behind, so the last minutes of writes are missing | Lag measured at zero on the target, held there for a full interval before you move |
| Advance sequences | The new primary starts issuing identifiers that already exist, across many tables at once | Every sequence set above the maximum value present in its table, verified by query |
| Verify | Row counts match and values do not, because a type or encoding differed on the target | A value-level comparison on a sample, plus counts on every table |
| Flip traffic | Cached connection strings or a long DNS record send part of the fleet to the old primary | Connection configuration reloaded, and the old primary readable but not writable |
For the cutover itself, put the application in a read-only mode you built and tested rather than revoking database permissions, which produces uglier errors and a slower recovery. Then: stop writes, wait for lag to reach zero, advance sequences, run the comparison, flip the connection configuration, let traffic in. Keep the old primary running and readable for a rollback window you decide in advance. Rehearse the whole sequence including the abort, because the abort is the branch nobody has practiced and the one you will need under pressure.
The migration runner deserves a design review
The tool that applies migrations is production infrastructure and usually gets less scrutiny than a logging change. A few things are worth settling once, for every service.
Most frameworks wrap each migration in a transaction, which is the right default and incompatible with the operations that cannot run inside one. Concurrent index builds are the common case, and every mature framework has an escape hatch for it. Know which one yours is before you need it at 11 p.m.
MySQL DDL is not transactional, so a migration containing several statements can fail halfway and leave the schema in a state no version file describes. One logical change per migration keeps that recoverable.
Two application instances starting at once should not both run migrations. Some frameworks take an advisory lock or a lock table for you. If yours does not, take one yourself. And log the statements actually executed with their durations. A runner that reports success with no timing gives you nothing to compare against next quarter, when the table is four times larger.
Rollback is a plan, not a down migration
Down migrations are useful in development and mostly fiction in production. A down migration for a dropped column cannot restore the data. What actually gives you a rollback is the pattern: at every step of expand and contract, the previous state is still valid and still populated, so backing out means deploying the previous version of the application and nothing else.
Write the abort criteria before you start, in numbers, and give whoever is running it the authority to use them without a meeting. Lock waits exceeding the timeout more than a set number of times in a row. Replica lag past a threshold. Error rate on the canary above baseline. Backfill throughput below the level that would finish inside the window. Any of those means stop, and stopping is cheap precisely because you built the migration so that it is.
Have a restore path and know its real duration. Not the documented duration, the one you measured this quarter on a database this size. A backup nobody has restored is a hypothesis, and the middle of a failed cutover is a poor place to test it.
Testing a migration where it counts
A migration tested against a development database with ten thousand rows has been tested for syntax. Nothing else. Two properties that matter change completely with size and concurrency, and both are the properties you care about.
Run it against a restored copy of production at production size, and time each statement. Then run it again while a load generator drives representative traffic at the same tables, because a lock is only interesting when something else wants it. That second run is where the lock timeout and retry loop earns its place, and where you find out that an index build you budgeted twenty minutes for takes ninety when the disk is also serving queries.
Then run the old application code against the new schema in CI. Almost nobody does this, and it is the direct test of the version-skew risk. If your test suite from the previous release passes against the migrated schema, your rolling deploy is safe. If it does not, you have found the incident in CI, which is where you want to find it.
Pre-flight checklist
- Each step is safe to stop at, and the previous state still works with deployed code
- lock_timeout and statement_timeout are set on the migration role, with a retry loop around blocking DDL
- No session is idle in transaction on the target tables, and a timeout prevents new ones
- Index builds are concurrent, and the runner is configured to allow non-transactional migrations
- The backfill is batched, keyed, resumable, throttled on replica lag, and stoppable in one command
- Verification compares values on a sample, not just row counts, and runs before the read cutover
- The previous release of the application passes its tests against the new schema
- Abort criteria are written as numbers, and the person running it can act on them alone
- Statement timings from a production-size rehearsal under load are recorded in the runbook
The mistakes we find most often
- No lock timeout. The migration is correct and the queue behind it takes the site down. This is the most common single cause we see, and the cheapest to fix.
- One giant UPDATE. Hours in a single transaction, bloat that outlives the migration, and nothing to show if it fails at the end.
- Dropping a column in the same release that stops using it. The old pods are still selecting it while the new schema no longer has it.
- Deploy ordering reversed. Destructive schema change first, code second, which guarantees a window where deployed code is wrong.
- Sequences forgotten at cutover. The new primary starts issuing primary keys that already exist, and the damage is spread across many tables before anyone notices.
- Rehearsal on an idle database. Every timing in the runbook is optimistic by a factor nobody can predict.
- A dual write with no reconciliation. Divergence accumulates quietly and is discovered by a customer rather than a dashboard.
Where the Effort Goes on a Migration We Would Sign Off
Typical effort split on a migration plan we would put our name on. The statements are the smallest line.
Bottom line
Zero downtime is not a property of a clever statement. It comes from breaking the change into steps that are each individually safe, bounding every lock so a blocked statement fails fast instead of stalling the table, treating the backfill as a production job with a throttle and a stop button, and ordering the deploys so no version of your code ever meets a schema it cannot handle. None of that is exotic. It is a week of planning for something the team wanted to do in an afternoon, and it is the difference between a migration nobody notices and a status page nobody wanted to write.
If a migration plan cannot answer three questions in writing, it is not ready. What lock does each statement take and for how long. What happens to the currently deployed code at every intermediate state. What exactly you do if you have to stop at 40 percent.
Frequently asked questions
Usually lock contention rather than the schema change itself. A blocking DDL statement that cannot get its lock will queue, and every subsequent request queues behind it, so a fast statement can stall a table for the length of whatever was already running. The other two common causes are an unthrottled backfill saturating I/O or pushing replica lag, and application code that no longer matches the schema during a rolling deploy.
Add the new schema shape alongside the old one, write to both while backfilling historical data, move reads across, stop writing the old shape, and only then remove it. It replaces one risky statement with a series of individually reversible deploys, and every intermediate state is a valid place to stop indefinitely.
Build it concurrently. In PostgreSQL that is CREATE INDEX CONCURRENTLY, which avoids the blocking lock at the cost of two table scans and cannot run inside a transaction block, so your migration runner needs to be told to skip its transaction wrapper. If a concurrent build fails it can leave an invalid index behind, so check for one and drop it before retrying. On MySQL, online DDL or a tool such as gh-ost covers the same ground.
A thousand to ten thousand rows per transaction suits most schemas, with wide rows and heavily indexed tables at the low end. Iterate by primary key with a saved cursor rather than using OFFSET, commit each batch, and throttle on replica lag rather than a fixed sleep. Measure one batch under production load and set the number from that instead of guessing.
It depends on direction. Additive changes go first, because code that does not know about a new column is unaffected by it. Destructive changes go last, after every deployed instance has stopped referencing what you are about to remove. Reversing that ordering is the most common way an otherwise careful migration causes an incident.