A schema migration is a distributed deployment: old and new application versions overlap while data changes underneath them. This production runbook renames customers.name to display_name without downtime. It expands compatibility, backfills in bounded batches, verifies continuously, switches reads, stops old writes, and contracts only when rollback no longer depends on the old column.
The core idea
Expand and contract converts one breaking schema change into several compatible states. Each state must support the application versions still running, provide a measurable completion gate, and preserve a data-safe rollback. Destructive cleanup happens only after evidence makes the old representation unnecessary.
Data flow: versions overlap
Follow one logical operation. Old writers know name; new writers prefer display_name. At each arrow, map reads and writes for every deployed version before DDL
flowchart LR
Old[Old app]-->N[name]
New[New app]-->N
New-->D[display_name]
N-->B[Backfill]
B-->DThe temporary model supports both versions; ownership prevents indefinite dual-write drift.
The diagram is a map of failure and ownership boundaries, not decoration. Annotate where data becomes durable, where it can be duplicated, and where ordering can change. Validate it with mixed-version deployment and rollback. The model is wrong if a rolling instance still requires the old column when it is dropped. Correlate writes and nulls by column/application version across the flow using a safe operation identifier.
Runbook phase: expand without a table surprise
Objective. Add the new nullable column using engine/version behavior understood in advance.
Procedure. inspect lock level, table rewrite risk, free space, replicas, and long transactions; set a short lock timeout
SET lock_timeout = '2s';
ALTER TABLE customers ADD COLUMN display_name text;If lock acquisition fails, abort and retry later rather than blocking application traffic behind DDL.
Name the person authorized to stop the phase and make every command repeatable. Before proceeding, complete production-sized clone and intentional lock conflict. Stop rather than improvise if a metadata change queues behind a long transaction and then blocks new work. The dashboard for this phase should expose lock waits, statement phase, replica lag, and free space. Capture start and finish checkpoints so another operator can determine exactly how far the run advanced.
Step: backfill in restartable batches
Start with a small, reversible implementation. One huge update creates long locks, log volume, and replication lag. The immediate action is to update ordered key ranges, commit each batch, throttle from health, and make reruns harmless
UPDATE customers SET display_name=name
WHERE id>$1 AND id<=$2 AND display_name IS NULL;Persist the high-water mark, but rely on the predicate for idempotence. Concurrent new writes must populate both columns.
Read the example from top to bottom and identify which line establishes the guarantee and which lines merely implement it. In a real system, repeat the exercise with worker restart, concurrent writes, and lag throttling. The step is incomplete if offset paging skips rows or batch speed ignores replica capacity. During rollout, record rows remaining, batch time, WAL/redo, locks, and lag; compare it with a baseline before claiming success.
Failure reconstruction: dual writes disagree
Imagine the alert has fired. A retry or old code updates only one column, producing two customer names. The first response should be to stop rollout, identify authoritative precedence, repair through an audited idempotent job, and fix the write path, preserving evidence before retries or manual repair change the state.
SELECT count(*) FROM customers
WHERE display_name IS DISTINCT FROM name;The comparison becomes a release gate; sample mismatches with safe identifiers, not sensitive values.
Walk the timeline twice: once from the caller's view and once from the durable system of record. The two stories often diverge at a timeout, commit, queue acknowledgement, or cache boundary. Recreate all writers, retries, imports, and rollback versions. The likely trap is that a trigger and application write race or null semantics are compared incorrectly. Confirm recovery by watching mismatch count/rate and writer version, not merely by seeing one successful request.
Release gate: switch reads, enforce, then contract
Cleanup is last because it removes rollback options. Ship it by canary reads from the new column, stop old writers, enforce constraints with low-risk engine techniques, wait, then drop old data
| Gate | Evidence |
|---|---|
| Backfill complete | zero eligible rows |
| Dual-write correct | zero unexplained mismatch |
| Read cutover | no cohort regression |
| Old writers gone | deployment + telemetry |
| Drop safe | rollback no longer reads old column |
A rollback is useful only while old code can safely understand new data and side effects already produced. Prove restore, rollback, and schema-reference scans before increasing exposure. Pause the rollout when rollback binaries or forgotten jobs still reference name. Compare query errors, old-column access, cohort SLOs, and constraint validation between cohorts and keep the change reversible until the evidence covers normal load, retries, and at least one dependency disturbance.
Migration incident rehearsal
1. Rehearse versions overlap
Before sign-off, restate this article-specific claim in one sentence: Old writers know name; new writers prefer display_name. Build a controlled exercise that includes mixed-version deployment and rollback. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a rolling instance still requires the old column when it is dropped. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve writes and nulls by column/application version with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
2. Rehearse expand without a table surprise
Before sign-off, restate this article-specific claim in one sentence: Add the new nullable column using engine/version behavior understood in advance. Build a controlled exercise that includes production-sized clone and intentional lock conflict. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a metadata change queues behind a long transaction and then blocks new work. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve lock waits, statement phase, replica lag, and free space with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
3. Rehearse backfill in restartable batches
Before sign-off, restate this article-specific claim in one sentence: One huge update creates long locks, log volume, and replication lag. Build a controlled exercise that includes worker restart, concurrent writes, and lag throttling. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where offset paging skips rows or batch speed ignores replica capacity. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve rows remaining, batch time, WAL/redo, locks, and lag with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
4. Rehearse dual writes disagree
Before sign-off, restate this article-specific claim in one sentence: A retry or old code updates only one column, producing two customer names. Build a controlled exercise that includes all writers, retries, imports, and rollback versions. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where a trigger and application write race or null semantics are compared incorrectly. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve mismatch count/rate and writer version with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
5. Rehearse switch reads, enforce, then contract
Before sign-off, restate this article-specific claim in one sentence: Cleanup is last because it removes rollback options. Build a controlled exercise that includes restore, rollback, and schema-reference scans. Record the expected durable state, response, and operator action before running it. During the exercise, deliberately introduce the condition where rollback binaries or forgotten jobs still reference name. The team must explain whether to retry, reject, compensate, roll back, or wait. Preserve query errors, old-column access, cohort SLOs, and constraint validation with enough dimensions to compare the expected and observed path. Finish by assigning ownership and documenting the threshold that would force the design to be reconsidered.
Conclusion: compatibility before cleanup
Expand and contract turns one breaking step into observable reversible phases. The price is temporary dual behavior and operational discipline. Know lock semantics, make backfills restartable, compare old and new representations, gate each phase with metrics, and delay destructive cleanup. A rollback plan must include data and side effects, not only application binaries. The durable lesson is to state guarantees in language clients and operators can verify, then build the smallest mechanism that satisfies them under retries, concurrency, deployment, and partial failure. Revisit the decision when traffic, risk, or ownership changes.
Continue with Database Transactions and Isolation Levels Explained to connect this topic to another production boundary. The Prime Axiom resource library collects practical implementation material.
Authoritative references
DDL locking and rewrite behavior is version-specific. Rehearse the exact statement on a production-sized copy before scheduling it.
