MigrationsDATABASE-SPECIFICSCALE-SPECIFICGENERAL
Why Migrations Are the Dangerous Change
Five distinct risks hide under the word "migration", and every one of them scales with data you do not have in staging.
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.
The production question
Why is a schema change more dangerous than a code change of the same size, and what exactly is the danger?
The problem
A bad deploy is undone by putting the previous artifact back. A migration changes a single shared copy of state that every instance of both the old and the new code is talking to, and some of its effects have no inverse at all.
What teams do first
Migrations are files in the pull request. The ORM generates them, review reads them like any other diff, CI applies them to a test database and they pass, and the deploy runs them automatically on the way up.
How it breaks
CI applied the migration to a table with a few hundred rows. Production has hundreds of millions, and every risk in a migration scales with row count: how long a lock is held, how long a rewrite takes, how much WAL or binlog it generates, how far replicas fall behind.
How it breaks in production
- CI applied the migration to a table with a few hundred rows. Production has hundreds of millions, and every risk in a migration scales with row count: how long a lock is held, how long a rewrite takes, how much WAL or binlog it generates, how far replicas fall behind.
- The lock that hurts is usually not the one the migration takes but the one it waits for. A DDL statement queued behind a long-running transaction blocks every query that arrives after it, so a table can become unavailable while the migration has done no work at all.
- The migration is reviewed against the new code, but for the entire duration of the rollout the code running against the new schema is the old code (Version Coexistence: N and N+1, in Both Directions).
- Dropping a column or a table is a one-way door. There is no previous artifact to redeploy, and restoring from backup is a different operation with its own outage and its own data loss window (Restore Drills).
- A backfill that updates every row is a long-running write workload, not a schema change, and running it as one statement holds a transaction open for as long as it takes (Backfills).
- The migration runner is often the least operable component in the pipeline: it prints nothing while it works, cannot be resumed, and on some engines cannot roll back a multi-statement failure.
Code→Build→Test→Artifact→Release→Deploy→Run→Observe→Operate→Incident→Recover→Learn→Improve
What is actually happening
Underneath the tooling, which is the part that survives a change of tool.
- Five independent risks travel under one word. A given migration may carry none, one or all five, and they are worth naming separately because they have different mitigations.
- Locking. DDL takes a lock on the table. In PostgreSQL most
ALTER TABLEforms takeACCESS EXCLUSIVE, which conflicts with everything including plain reads. In MySQL every DDL takes a metadata lock. The dangerous property is not the lock itself but the queue: a waiter blocks everything behind it (Locks and Deadlocks). - Rewriting. Some changes require every row to be physically rewritten. The duration is proportional to table size, and the write amplification lands on WAL or binlog, disk and replication at the same time (Write-Ahead Logging).
- Compatibility. The schema and the code are versioned separately and roll out at different speeds. Any moment where the schema requires code that is not everywhere yet — or forbids code that is still running — is an outage waiting for the next request.
- Destruction. Dropping, truncating, narrowing a type or renaming removes information. Rollback does not exist for these; only recovery does (Partial and Logical Data Recovery).
- Duration. Backfills and validations run for a long time against live traffic, competing for I/O, generating row versions, and lagging replicas (Replication and Read Scaling).
- The reason this module exists is that only the first two are visible in the SQL. The other three are properties of the rollout, and no schema review catches them.
Five risks, one word
Reviewing a migration means asking which of these it carries. Most carry one. The dangerous ones carry several, and the combination is what produces an outage rather than a slow query.
What actually goes wrong, by risk
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Exclusive lock on a busy table | Queries against one table hang, then time out; the rest of the app is fine | DDL waiting for a lock blocks every query that arrives behind it | Cancel the statement; retry with a lock timeout so it fails instead of queueing |
| Table rewrite | Sustained I/O saturation, replication lag climbing, disk filling | Every row is physically rewritten and the change is journalled | Abort if the engine allows it; otherwise let it finish and shed load. Prefer a rewrite-free formulation next time |
| Old code, new schema | Errors from the instances not yet replaced, on one table, disappearing as the rollout completes | The schema changed in a way the running code cannot tolerate | Complete the rollout or revert the schema — whichever is faster and safer (Destructive Migrations) |
| New code, old schema | Errors from the canary only, immediately on deploy | The deploy ran before the migration | Roll the deploy back; the schema was never the problem (Rollback: Only Useful If It Is Actually Safe) |
| Destructive statement | Data is gone; queries succeed and return nothing | A drop, truncate or narrowing type change | Stop writes, assess the loss window, restore from backup. There is no undo (Partial and Logical Data Recovery) |
| Long backfill in one statement | Table bloat, replication lag, and a transaction that cannot be safely killed near the end | A single UPDATE across every row holds one transaction open for its whole duration | Kill it and restart batched; the work done so far may or may not survive (Backfills) |
The same statement, two engines
DATABASE-SPECIFICEvery cell is engine- and version-specific and is stated as of the versions named. Check your engine's documentation for your exact version before relying on any of it — this table is a map of where the differences are, not a substitute for the manual.
This is the single most common source of confidently wrong migration advice. Rules learned on one engine are repeated as universal facts, and the version matters as much as the engine.
Read the last column rather than memorising the middle two: what transfers is the shape — that some formulations avoid a rewrite, that there is usually a staged path, and that the boundaries of any online operation still take a short exclusive lock.
| Change | PostgreSQL | MySQL / InnoDB | What transfers |
|---|---|---|---|
| Add nullable column, no default | Catalog change; ACCESS EXCLUSIVE held briefly | ALGORITHM=INSTANT since 8.0.12; INPLACE otherwise | Cheap on both — and both still queue behind a long transaction |
| Add column with a default | No rewrite since 11 for a non-volatile default; full rewrite before that | INSTANT for a literal default in 8.0; a copy on older versions | The version decides whether this is metadata or a full rewrite |
| Create an index | CREATE INDEX blocks writes; CONCURRENTLY does not, at the cost of two passes, no transaction block, and an invalid index left behind on failure | ALGORITHM=INPLACE, LOCK=NONE permits concurrent DML; brief metadata lock at start and end | Both have a non-blocking path, and both take a short exclusive lock at the boundaries |
| Change a column type | Rewrite, unless provably no-op such as widening varchar(n) | Usually a table copy, which blocks writes | This is the change that pushes MySQL teams to external tooling |
Add NOT NULL to an existing column | Full scan under ACCESS EXCLUSIVE, unless a validated CHECK (col IS NOT NULL) already exists (12+) | Generally a table rebuild | Both have a two-step workaround; the two workarounds are completely different |
| Add a foreign key | NOT VALID, then VALIDATE CONSTRAINT under a weaker lock | Validated as part of the statement; disabling checks to skip it is unsafe under live writes | PostgreSQL has a supported staged path; MySQL does not |
| Several DDL statements as one unit | Transactional DDL — they commit or roll back together | Implicit commit per statement; a failure leaves partial state | On MySQL every migration must be independently re-runnable |
| Effect on replicas | Long DDL can cancel conflicting queries on physical standbys | The statement replays on each replica, so lag tracks its duration | A long DDL is also a replication event, by different mechanisms (Replication Internals: WAL Shipping, LSNs, Lag and Failover) |
The lock you should fear is the one you are waiting for
A migration that executes in milliseconds can still take a table offline for minutes, because the wait happens in a queue that new queries join behind. This is the mechanism most teams discover during their first migration incident.
The fix is not a faster statement. It is refusing to wait: bound the lock acquisition, fail, and retry when the table is quiet.
An `ALTER TABLE` that never ran, and the outage it caused anyway
- T+0changeA reporting query opens a long transaction that reads
orders. - T+30schangeThe migration job issues
ALTER TABLE orders ADD COLUMN fulfilment_state text. It cannot acquire the exclusive lock, so it waits. - T+31ssignalEvery new query touching
orders— including plain reads that would not have conflicted with the reporting query — queues behind the waitingALTER. - T+45ssignalConnection pools saturate. Latency climbs, then requests fail with pool acquisition timeouts (Connection Pool Exhaustion).
- T+60ssignalError-rate alert fires. The migration has still not modified a single byte.
- T+90srecoveryOperator cancels the
ALTER. The queue drains within seconds and latency returns to baseline. - Next attemptactionRe-run with a lock timeout so the statement gives up rather than holding the queue open, and retry until it lands.
The statement was never the problem, the table was never rewritten, and no data changed. The entire incident was a queue. This is why "how long does the statement take" is the wrong safety question.
changesignalactionrecovery
Fail fast instead of queueing
1-- PostgreSQL: bound the wait, not the work2SET lock_timeout = '2s';3ALTER TABLE orders ADD COLUMN fulfilment_state text;4-- if this raises a lock_not_available error, retry later. That is the success case.5 6-- MySQL: the equivalent bound is on the metadata lock7SET SESSION lock_wait_timeout = 2;8ALTER TABLE orders ADD COLUMN fulfilment_state varchar(32),9 ALGORITHM=INSTANT;Without the timeout the statement waits indefinitely and holds a queue of blocked queries behind it. With it, a busy table produces a failed migration — an outcome with no user impact, which you retry. Naming ALGORITHM explicitly on MySQL is the other half: it makes the statement fail if the online path is unavailable, rather than silently falling back to a blocking table copy.
How to do it properly
Most important first.
- Classify every migration by which of the five risks it carries, before reviewing the SQL. Most migrations carry one; the ones that carry three are the ones that need a plan rather than a review.
- Set a lock timeout on every migration session so a busy table makes the migration fail rather than block. A failed migration you retry is a far better outcome than a table nobody can read.
- Split any change that is not purely additive into separate, individually deployable steps (Expand, Migrate, Contract).
- Run migrations as a deliberate, observable step with a named operator and a visible result — not as a side effect of a container starting, where it runs once per replica in a race.
- Rehearse against production-scale data: a restored copy, or a shadow of the real table. Volume is the variable staging cannot model (Why Local Success Predicts So Little).
- Separate the schema change from the data movement. Schema changes should be short; backfills should be batched, throttled and resumable.
- Know, before you start, what the abort action is: cancel the statement, kill the session, or nothing.
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.
Blast radius if this is wrongEveryone
One testEveryone
What contains it
Usually nothing. A canary contains a bad artifact because two versions can run side by side; there is one database, so a lock or a rewrite reaches every instance and every user simultaneously. The only real containment is making the change in smaller steps that are each individually reversible.
What can go wrong
Failure modes, including of the mitigation
- A lock timeout set so aggressively that the migration never succeeds during business hours, and someone eventually removes it — reintroducing the original risk with extra confidence.
- Migrations run from application startup, so N replicas race to apply the same change. Advisory locks usually save you; a partial apply on an engine without transactional DDL does not.
- The mitigation failing: in PostgreSQL a failed
CREATE INDEX CONCURRENTLYleaves an invalid index behind that must be dropped explicitly, and the retry fails on the name collision. - A migration that succeeds on the primary and lags every replica, so read traffic sees stale data or read queries are cancelled (Replication Lag: Reads That Are Correct and Stale).
- A separate migration job that is never actually invoked in one environment, so the schema silently diverges and the failure appears as an application error weeks later (Environment Drift).
- Rolling back the application without rolling back the schema, or the reverse — the two are one coupled event and only one of them was reverted (A Migration and a Deploy Are One Event).
Misreads this invites
- "It took two seconds in staging, so it is fast." Duration in staging measures the statement against a small table. It measures none of lock contention, rewrite volume or replication load.
- "
ADD COLUMNis always safe." Whether it rewrites the table depends on the engine, the version and whether there is a default — and even the metadata-only form takes an exclusive lock that can queue behind a long transaction. - "The ORM generated it, so it is correct." Correct and safe are different properties. The generator knows the desired end state and nothing about your table size, your traffic or your rollout.
- "We have backups, so data loss is recoverable." A restore is an outage plus the write window since the backup. It is a disaster response, not a rollback (RTO and RPO).
- "Run it at 3am and it is fine." Off-peak reduces the number of affected users; it does not change lock semantics, and it puts the riskiest change in the hands of the most tired person.
Operating it
Evidence is the signal, not the intention. Rollback is sometimes 'you cannot, and that is the point'.
How you know it worked
- During the migration window: no rise in lock waits, no rise in connection pool saturation, no rise in error rate. The absence of these is the signal, so it must be on a dashboard before you start (Dashboards an Operator Can Act On).
- Replication lag stayed inside its normal band throughout, and returned to baseline afterwards.
- The schema version reported by the application matches the version the migration tool recorded, on every instance — not just on the one you checked.
- For a backfill: a query that counts the rows still in the old shape, run to completion and returning zero.
- For an index: the index exists, is valid, and the planner is actually using it (Should I Add an Index?).
How you get back
- Additive migrations are rolled back by doing nothing. An unused column costs almost nothing and can be removed later, deliberately, as its own change.
- A destructive migration has no rollback. The recovery path is a restore, which means an outage plus every write since the backup, and it is only real if it has been rehearsed (Backup Operations).
- Down-migrations generated by an ORM are dangerous precisely where they look most useful: the
downforADD COLUMNisDROP COLUMN, which will destroy whatever the new code wrote in the meantime. - The safest rollback plan for a schema change is a schema that does not need one — every intermediate state is valid for both code versions (Version Coexistence: N and N+1, in Both Directions).
What to automate, and what stays human
- Automate applying, recording and verifying migrations: one runner, one ordering, one place that says which version each environment is on.
- Automate the guardrails, not the judgement: lock timeouts, a linter that rejects the known-dangerous statement forms, a required review label for anything destructive (Policy as Code).
- Keep human: the decision to run a destructive step, the decision to run a rewrite during peak traffic, and the decision to abort mid-migration (The Automation Trap).
What this costs
- Doing this properly turns a one-line change into a sequence spanning several deploys and, for a large table, several days of calendar time. That is a real cost in engineer attention and in schema clutter while it is in flight.
- The intermediate schema is uglier than either end state — two columns holding the same thing, code writing both — and someone has to remember to finish.
- Rehearsing against production-scale data requires a production-scale copy, which costs money and creates a data-protection obligation (Production Data in Lower Environments).
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-SPECIFICWhich statements block, which rewrite and which are metadata-only differs substantially between PostgreSQL and MySQL, and between versions of each. PostgreSQL has transactional DDL and
CREATE INDEX CONCURRENTLY; MySQL/InnoDB hasALGORITHM=INSTANT/INPLACEand implicit commit per DDL statement. Neither engine's rules transfer to the other, and MySQL DDL also replays on every replica. - SCALE-SPECIFICBelow roughly a million rows on a table with modest write traffic, most migrations are genuinely a single statement and the ceremony here is waste. The techniques become mandatory when the lock duration exceeds a request timeout, which is a function of row count and write rate, not of company size.
- GENERALThe five risks — lock, rewrite, compatibility, destruction, duration — apply to any engine including document and wide-column stores, where a "schemaless" write path simply moves the compatibility risk into the application (Document Databases: Embed or Reference).
Where the depth lives
This domain teaches delivery and operation, and hands the mechanism off to the domain that owns it.
Cloudmanaged-databases
Databaselocks-and-deadlocksmvccwrite-ahead-loggingindex-typespostgresql-operationstransactions-and-acid
Domains that do not exist yet
- — Testing & Reliability Engineering — rehearsing a migration against production-scale data is a testing problem before it is an operations one.