The most dangerous minute in a database backed service is the minute an ALTER TABLE runs on production. The statement that worked instantly on the developer's laptop meets a table with a hundred million rows and holds locks the application is queuing behind, and the deploy that was supposed to be routine becomes an outage with a migration id in the timeline. The danger is not carelessness, it is the gap between what the SQL syntax suggests, a quick schema edit, and what the engine must actually do, rewrite or lock. This article builds the practice that closes the gap: the lock reality of common changes, the expand and contract pattern that splits every migration into safe steps, batched backfills that respect replication, index and constraint creation without blocking, and the tooling and rollback discipline that turns schema change from an event into a routine.
The lock reality behind every ALTER
Engines state their locking in documentation, and the migration discipline starts with reading it per statement. In PostgreSQL every ALTER TABLE takes an ACCESS EXCLUSIVE lock, blocking reads and writes, but the hold time varies enormously. Adding a nullable column is a catalog update, milliseconds, because no row is touched. Adding a column with a default is metadata only since PostgreSQL 11, the default lives in the catalog, and existing rows materialize the value on read. Changing a column type, or adding one with a volatile default on older versions, is a full table rewrite under the exclusive lock, which on a large table is minutes to hours of total outage delivered by one command.
Indexes have a split path. CREATE INDEX takes a lock that blocks writes for the build duration. CREATE INDEX CONCURRENTLY builds without blocking, at the cost of two table scans and, on failure, an index left in the invalid state that must be dropped and retried. MySQL tells the same story in its own vocabulary: the online DDL framework classifies operations as ALGORITHM=INSTANT, metadata only, INPLACE, rebuilds without blocking writes, and COPY, a full blocking rebuild, and the operational habit is the same one: look up the classification before the statement, because instant and copy hold the same syntax and opposite consequences.
The rule that survives every version is the preflight. Every migration against production volume is first run in staging with production sized data, timed, and observed in the lock or processlist view. A migration that holds a rewrite lock for four minutes in staging will not hold it for four seconds in production, and the team that knows the staging time knows the production risk before the deploy starts.
The expand and contract pattern that splits every change into safe steps
The pattern that makes schema evolution boring is to never do in one deploy what can be done in three. Every breaking change becomes a sequence, and each step is independently deployable, independently safe, and independently reversible. The canonical example is renaming a column, but the pattern covers type changes, constraint additions and table splits identically. First, expand: add the new column nullable beside the old one, an instant catalog change. The application writes both during a transition release, or a trigger maintains the mirror. Second, backfill: the historical rows receive values in bounded batches, while the application reads with a fallback to the old column. Third, switch: the application reads the new column, the old column stops being written. Fourth, contract: a later release drops the old column, by then unused and unmourned.
The pattern's value is that each step can stop. A deploy can sit between expansion and switch for a week while the backfill completes and the dashboards confirm both columns agree. A defect discovered after the switch is fixed by falling back to reads from the old column, which is still there, because contract steps always trail. The pattern costs a transition period where the schema carries both shapes, and the teams that resent the cost should price the alternative, which is the single deploy that rewrites a hundred million rows under a lock with the whole application queuing behind it.
Backfilling millions of rows without the replication lag
The backfill step has its own failure mode: a naive script updates every row in one transaction, which holds locks, bloats the transaction log, and streams gigabytes of changes to every replica, whose lag grows until the failover monitoring pages and the reporting queries read ancient data. The batch discipline is the standard remedy, and the shape is always the same:
last_id = 0
while True:
rows = fetch_batch("SELECT id FROM orders
WHERE id > :last_id
ORDER BY id LIMIT 10000")
if not rows:
break
UPDATE orders SET new_column = old_column
WHERE id > :last_id AND id <= :max(rows)
COMMIT
last_id = max(rows)
sleep_for_replica_lag()Each batch is a short transaction, the loop keys on the primary key so it walks forward without rescans, and the sleep between batches is paced by the replica lag itself, read from the replication statistics, so the loop slows when the replicas slow and never outruns them. The session that runs it should carry a statement timeout and a lock timeout, so an unexpected lock waits fail fast and retry instead of queueing silently behind a long transaction.
At serious scale the engine ecosystem provides the specialized versions of the same idea. For MySQL, gh-ost and the Percona toolkit's online schema change tools build a shadow table with triggers or the binlog, stream rows into it, and swap it in with a short lock at the end. For PostgreSQL, logical replication supports a variant: publish the table to a new table with the new shape and cut over the application. The tools are the batch loop with the edges handled, and the choice between them and a hand written loop is a choice about how much of the edge handling the team wants to own.
Indexes constraints and the long lock avoidance
The expand phase creates indexes, and the online forms are mandatory vocabulary. In PostgreSQL:
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at);The statement fails rather than blocks, on timeout or on conflict, and the failure leaves an invalid index, visible in pg_index with indisvalid false, which the follow up must DROP INDEX CONCURRENTLY and retry. The monitoring habit is checking pg_index for invalid entries after every migration window, because an invalid index is not a slow index, it is no index, wearing the name of one.
Unique constraints on populated tables deserve the two step that PostgreSQL provides: create the constraint NOT VALID, which catalogs it without scanning history, then VALIDATE CONSTRAINT in a second statement, which scans with a weaker lock that tolerates concurrent writes. The pattern enforces uniqueness for every new write immediately while the historical audit proceeds, and the teams that skip it discover that adding a unique constraint to a live table is a full table scan under a share lock, which is the exact outage this article exists to prevent. MySQL's online forms cover the same ground with INPLACE index builds, and the preflight rule from the first section applies identically: classify, stage, time, then run.
Versioned migrations and the tools that run them
Schema changes reach production through a migration tool, and the tool is a discipline more than a convenience. Flyway, Liquibase, Alembic, and the migrate libraries in Go and Node all share the core contract: migrations are versioned files, applied in order, recorded in a schema history table, and never edited after application. The never edited rule is the load bearing one. A migration that has run in staging and production is history, and fixing a mistake means a new migration, not a rewrite of the old file, because the rewritten file either fails a checksum or silently diverges the environments, both worse than the mistake it was fixing.
The review practice around the files is where this article's rules live. A migration pull request shows the preflight classification of every statement, the staging timing, the rollback note, and the coordination plan for any step that needs a deploy order. Migrations that ship with application code follow the compatibility rule: the migration runs first and is compatible with both the old and the new application version, because deploys overlap and the old version is still serving while the new one starts. The DDL that requires the application to change first, the type change with no transition, is the migration that needs the expand and contract sequence, and the tooling simply makes the sequence explicit, reviewable, and re-runnable.
The rollback plan that survives the deploy
Every migration window opens with the same question: if this fails halfway, what is the state and what happens next. The honest answer is prepared before the window, and the preparations differ by step kind. Additive steps roll back by dropping what was added, and dropping is instant for nullable columns and cheap for indexes. Backfills roll back by clearing the new column, batched like the backfill itself. Contract steps, the drops, rarely need rollback, which is exactly why they are last, running after the application has proven it no longer reads the old shape. The truly breaking step, the one whose rollback would itself be a rewrite, is the step that should not exist, and its appearance in a review is the request to expand and contract it.
The window itself has a protocol worth writing down once:
- Classify every statement's lock behavior and record it in the migration notes;
- Stage with production volume, time every step, and record the timings;
- Ship additive steps first, compatible with both application versions;
- Backfill in key ordered batches, paced by replica lag, with timeouts set;
- Create indexes and constraints through their online and two step forms;
- Schedule contract steps for a later release, after the dashboards agree.
Schema change stops being an event when every step is small, classified, and reversible, and the tooling plus the pattern is how the team gets there. The database that evolves in reviewable increments keeps serving while it changes, and the deploy window that once needed a change freeze becomes an ordinary Tuesday with a migration id nobody remembers afterwards.
One rename walked through the whole pattern
The pattern reads best as one concrete change walked end to end, and the rename is the honest example because it is trivial on a laptop and dangerous on production. The task, rename orders.total_amount to orders.grand_total, releases across four deploys. Release one expands: the migration adds grand_total as a nullable column, instant, and the application writes both columns on every new order and update. Release two backfills, the batched loop from the earlier section walks the table in primary key order over two nights, with the replica lag pacing the batches and a follow up query confirming the two columns agree on every backfilled row.
Release three switches, the application reads grand_total and stops writing total_amount, while the old column stays in place, and a defect discovered here is fixed by reverting the read, not the schema, because both columns are still live and identical. Release four contracts, a migration three weeks later drops total_amount, by then provably unwritten and unobserved, and the contract step is itself instant. The four releases ship independently, the rollback at any stage is the previous release, and the total wall clock of user visible risk is near zero, spread across four Tuesdays that each contain one boring deployment.
The example is the pattern's cost statement as well as its proof: it took four releases to do what one statement pretends to do, and the team that chooses the single statement is choosing to bundle the whole risk into one lock window on the biggest table it owns. The pattern is not caution for its own sake, it is the recognition that the schema is the one artifact in the system that every deployed version touches simultaneously, and the only safe way to change it is in pieces small enough to survive failing.