Zero-Downtime Migrations
The techniques that let a schema change land while the service keeps serving — and the engine-specific rules that decide which ones are available to you.
The question, the obvious approach, and why it breaks
Every lesson starts where the work starts: an operational problem, a first attempt that is entirely reasonable, and the way production disagrees with it.
How do I apply a schema change without taking a maintenance window?
Most schema changes have a naive form that blocks the table and a less obvious form that does not. Which is which depends on the engine and its version, and the naive form is what every tutorial and every ORM generator produces.
Write the change you want, run it, and if it turns out to be slow, schedule a maintenance window. Downtime is the honest answer for a big change.
A maintenance window is a poor substitute for a safe formulation: it stops user traffic but it does not stop the change taking hours on a large table, and now the outage duration is the migration duration.
- A maintenance window is a poor substitute for a safe formulation: it stops user traffic but it does not stop the change taking hours on a large table, and now the outage duration is the migration duration.
- Windows train the team to batch schema changes. Batched changes are bigger, riskier, and harder to attribute when something breaks afterwards (Change Size: Why Small Changes Are Safer, and When They Are Not).
- For most changes, the blocking form and the non-blocking form differ by one clause. Accepting downtime for a change that has an online path is paying a large cost to avoid learning a small thing.
- Some changes genuinely have no online path on your engine. Not knowing which ones is how a "quick ALTER" becomes an unplanned outage.
What is actually happening
Underneath the tooling, which is the part that survives a change of tool.
- Three separate properties decide whether a change is online, and they are often confused: does it need a lock that conflicts with traffic, does it need a table rewrite, and does it need a full validation scan.
- A change can rewrite without blocking (an online rebuild), block without rewriting (a metadata change queued behind a long transaction), or scan without rewriting (constraint validation).
- The general escape from all three is the same: replace one long exclusive operation with a short exclusive operation plus a long concurrent one.
CREATE INDEX CONCURRENTLY,ADD CONSTRAINT ... NOT VALIDfollowed byVALIDATE CONSTRAINT, and shadow-table tools are three instances of the same move. - Where the engine offers no online path — most type changes on MySQL, for example — the equivalent is done outside the engine: build a shadow table with the new definition, copy rows in batches, keep it current from triggers or the binlog, then swap it in with an atomic rename (Expand, Migrate, Contract applied to the whole table).
- Every online operation still has boundaries. Both PostgreSQL's concurrent index build and MySQL's in-place DDL take a brief exclusive or metadata lock at the start and the end, so both are still vulnerable to the lock-queue problem (Why Migrations Are the Dangerous Change).
Three properties, not one
"Is this migration safe" is really three questions, and a change can be dangerous on any one of them independently. Sorting a proposed migration into this table is most of the analysis.
| Technique | Which property it removes | What it costs | Availability |
|---|---|---|---|
| Lock timeout + retry | Blocking wait — the statement gives up instead of queueing | The migration may need several attempts | Both engines, always applicable |
| Concurrent index build | Write-blocking during the build | Extra passes; cannot run in a transaction; leaves an invalid index on failure | PostgreSQL CONCURRENTLY; MySQL ALGORITHM=INPLACE, LOCK=NONE |
NOT VALID then VALIDATE | The full validation scan under an exclusive lock | A window where the constraint is enforced for new rows only | PostgreSQL; MySQL has no direct equivalent |
| Instant / in-place DDL | The table rewrite | Restricted to a documented subset of changes and versions | MySQL ALGORITHM=INSTANT; PostgreSQL achieves the same for some cases by catalog-only changes |
| Shadow table + swap | Rewrite and blocking together, for changes with no in-place path | Doubles storage; adds a tool; cutover rename is a real moment | Usually MySQL tooling; the pattern is engine-independent |
| Expand/migrate/contract | The compatibility risk, which none of the above touch | Multiple releases and a long intermediate state | Always, and orthogonal to the rest (Expand, Migrate, Contract) |
| Batched backfill | The duration risk of a single long transaction | Slower in wall-clock time; needs a resume point | Always (Backfills) |
Which path, for this change
The decision is rarely "online or not". It is which of several online-ish paths fits the change, the engine and the table — and whether the table is small enough that the question does not arise.
The change needs to land on a table serving live traffic. How do you apply it?
when The table is small, or the change is genuinely catalog-only on your engine and version.
cost You must actually know it is catalog-only on your version. Being wrong means a full rewrite under an exclusive lock.
when The change is in your engine's documented online subset — most index work, many column additions.
cost Longer wall-clock duration and more I/O; short exclusive locks remain at the boundaries; failure cleanup can be manual.
when Adding a check, a foreign key or NOT NULL to a large table on PostgreSQL.
cost A window where existing rows are unvalidated, and a second operation someone has to remember to run.
when The change has no in-place path — most type changes on MySQL, large table restructures.
cost Doubles storage, adds a tool and its failure modes, and concentrates risk into the cutover rename.
when The change is semantic rather than physical — a rename, a split, a re-model.
cost Several releases, dual writes, and a long intermediate state (Expand, Migrate, Contract).
when The change is genuinely unavoidable, huge, and the business can tolerate scheduled downtime.
cost Downtime, plus the batching behaviour that windows encourage. Honest, occasionally correct, and worth avoiding as a habit.
The two-step forms, in full
These are the two most useful concrete patterns, and both are instances of the same move: acquire the strong lock for a moment, do the long work under a weak one.
Note that neither addresses compatibility. A validated NOT NULL on a column the old code does not populate will reject the old code's inserts — which is a compatibility failure wearing an availability technique's clothing.
1-- 1. Constraint first, unvalidated: short ACCESS EXCLUSIVE lock, no scan.2SET lock_timeout = '2s';3ALTER TABLE orders4 ADD CONSTRAINT orders_state_not_null CHECK (fulfilment_state IS NOT NULL) NOT VALID;5 6-- 2. Validate: scans the table under a lock that permits reads and writes.7SET lock_timeout = '0'; -- the scan is long; the lock it takes is weak8ALTER TABLE orders VALIDATE CONSTRAINT orders_state_not_null;9 10-- 3. Only now is SET NOT NULL cheap: from 12, a validated CHECK lets it skip the scan.11SET lock_timeout = '2s';12ALTER TABLE orders ALTER COLUMN fulfilment_state SET NOT NULL;13 14-- Index, separately, and never inside a transaction block:15CREATE INDEX CONCURRENTLY idx_orders_state ON orders (fulfilment_state);16-- if it fails: the index exists and is INVALID. Drop it before retrying.17-- DROP INDEX CONCURRENTLY idx_orders_state;Step 2 deliberately removes the lock timeout: it is the long step, but the lock it holds does not conflict with traffic. Steps 1 and 3 keep it, because those are the short steps that take the strong lock and can therefore build a queue. Getting the timeouts the wrong way round is a common and expensive inversion.
How to do it properly
Most important first.
- Name the algorithm explicitly where the engine lets you, so an unavailable online path is a failed statement rather than a silent fallback to a blocking copy.
- Always set a lock timeout, including on operations you believe are instant. Instant operations still need the lock.
- Prefer the two-step form for anything requiring validation: create it unvalidated under a short lock, validate it under a weak one.
- For a change with no online path, use a shadow-table tool rather than inventing the procedure during an incident. The tools exist because the procedure has many sharp edges — foreign keys, triggers, replica lag, the cutover rename.
- Split the schema change from the data change always. A short DDL plus a long throttled backfill is two manageable operations; one long DDL is not.
- Run the migration from a session you can find and cancel, with a known abort action written down before you start (Runbooks).
- Watch replication lag as the primary throttle signal for anything long-running: it is the earliest indicator that the change is outrunning the cluster (Replication Lag: Reads That Are Correct and Stale).
How much can this affect
Every production change has a blast radius. Stated as a scale so it is comparable between changes rather than adjectival — and paired with what actually contains it, because a wide scope with a real containment mechanism is a different situation from a wide scope with none.
A lock timeout and a throttle, which together convert most failures into a stopped migration rather than a degraded database. Nothing contains a rewrite that has already saturated the disk.
What can go wrong
- A concurrent index build that fails partway leaves an invalid index that still costs write overhead and is not used by the planner, and the retry collides on the name until it is dropped.
- An online operation that succeeds on the primary and saturates the replicas, so read traffic degrades even though the primary looked fine.
- A shadow-table tool cutover that takes a rename lock at the worst moment, or leaves triggers behind after an aborted run.
- Naming the online algorithm and having the statement rejected — which is the tool working correctly, and is frequently misread as a bug and "fixed" by removing the clause.
- A migration run inside an application deploy's startup hook, where it cannot be cancelled independently and its failure crash-loops the container (The Container Lifecycle).
- Assuming "online" means "free": an online rebuild of a large table still consumes I/O and doubles disk usage while it runs (Capacity Management).
- "Zero downtime means zero impact." It means the service keeps serving. A large online rebuild still competes for I/O and can degrade latency throughout (Saturation: The Reading Utilization Cannot Give You).
- "Online DDL means no locks." Every online path takes short exclusive locks at its boundaries, and those are still enough to cause a queue on a busy table.
- "
CONCURRENTLYis always the right choice." It cannot run inside a transaction block, takes longer, and fails messily. On a small table the plain form is finished before anyone noticed. - "MySQL and PostgreSQL both support online DDL, so the same migration works on both." They support different subsets by different mechanisms, and the statement that is instant on one may copy the table on the other.
- "The tool says it is online, so it is safe." Online is about locking. It says nothing about replication lag, disk headroom, or whether the running application code tolerates the new schema.
Operating it
Evidence is the signal, not the intention. Rollback is sometimes 'you cannot, and that is the point'.
- Throughout the operation: error rate flat, p99 latency within its normal band, lock wait count not elevated.
- Replication lag stayed inside its band; if it climbed, the throttle responded and it recovered without intervention.
- For an index: it exists, is valid, and appears in the plan for the query it was built for (Reading EXPLAIN ANALYZE).
- For a constraint: it is marked validated, and inserting a violating row is rejected.
- For a shadow-table swap: row counts match between old and new immediately before the cutover, and the application's error rate does not move across it.
- Disk usage returned to an expected level after the operation, rather than staying at the doubled peak.
- Adding an index: drop it — concurrently, where the engine supports that, since a plain drop takes the same exclusive lock the build avoided.
- Adding an unvalidated constraint: drop it. The unvalidated state is deliberately cheap to reverse, which is why the two-step form is safer in both directions.
- A shadow-table migration before cutover: discard the shadow table. After cutover, the old table often still exists under a renamed name for a period — that retention window is the rollback, and it has an expiry.
- A rewrite in progress: cancelling is usually safe, but leaves the table in its original form having consumed the I/O. Whether space is reclaimed depends on the engine.
- Automate the guardrails: a migration linter in CI that rejects statement forms known to block on your engine is the highest-value automation in this module (Required Checks).
- Automate the throttling. A backfill or rebuild that watches replication lag and pauses itself is strictly better than one a human watches.
- Keep human: choosing between an in-place path and a shadow-table tool, and deciding to proceed on a table large enough that the operation will outlive the person who started it (The Automation Trap).
- Online operations are slower in wall-clock terms than their blocking equivalents, sometimes considerably. You are trading total duration for availability during that duration.
- They consume more resources: a concurrent index build does more passes, a shadow table doubles storage for the change.
- Shadow-table tooling adds a dependency with its own failure modes and its own operational learning curve — worth it when your engine leaves you no in-place path, expensive when it does.
- The two-step constraint form leaves a window where the constraint exists but is not enforced for existing rows, which someone will misread as a bug.
Where this applies
This domain is unusually tool- and organisation-dependent. These labels say what each claim is specific to, and what a different platform, provider or organisation does instead.
- DATABASE-SPECIFICPostgreSQL and MySQL/InnoDB provide different online paths by different mechanisms. PostgreSQL offers
CONCURRENTLYindex operations andNOT VALID/VALIDATEfor constraints, and has transactional DDL. MySQL offersALGORITHM=INSTANT/INPLACEwithLOCK=NONEfor a documented subset of changes, commits each DDL implicitly, and replays the statement on replicas — which is why external shadow-table tooling is far more common in MySQL shops than in PostgreSQL ones. - SCALE-SPECIFICOn a table small enough that the blocking form completes inside a request timeout, the plain statement is the correct choice and the online form is added risk. The threshold is set by row count, row width and write rate together, not by any single number.
- TOOL-SPECIFICShadow-table tools (gh-ost, pt-online-schema-change for MySQL; comparable tooling for PostgreSQL) differ in how they keep the copy current — binlog tailing versus triggers — which changes their write overhead, their replica behaviour and how a failed run must be cleaned up.
Where the depth lives
This domain teaches delivery and operation, and hands the mechanism off to the domain that owns it.