Expand-Contract & Zero-Downtime Changes

## Why one step is impossible The naive plan — `ALTER TABLE`, update the code, ship — assumes a single instant where schema and code change together. Rolling deploys have no such instant: old and new application versions run concurrently against one database, and a rollback puts old code in front of the new schema. Every migration must therefore be decomposed into steps where **both** versions of the application work against **both** shapes of the schema. Expand-contract is that decomposition. The name describes the shape: the schema temporarily grows to hold both representations, the system migrates across, then the schema shrinks again. ## Phase 1 — Expand (additive DDL) Add the new columns nullable, with no constraints that current traffic could violate, and add any indexes they will need (built without blocking writes — see the concurrent/online index options of your engine). This deploy touches only the schema. Old code is oblivious. Do not add `NOT NULL` yet: existing rows have no values, and old code does not supply any. ## Phase 2 — Dual write Deploy application code that, on every write path, populates both the old and the new representation. Reads still come from the old column, so behaviour is unchanged and the change is low-risk. Things to get right here: - **All write paths.** Not just the main service — admin tools, batch jobs, imports, other services with their own connection, and anything writing directly via SQL. A missed path is the usual cause of a mismatch discovered weeks later. Where the engine supports it and the transform is trivial, a trigger or generated column can catch stragglers, at the cost of extra write work. - **One transaction.** Both writes must be in the same transaction, otherwise a crash between them leaves the row inconsistent. - **Deterministic transform.** `full_name` → `(first_name, last_name)` is lossy and ambiguous; decide the rule (last token is the surname? split on the first space? what about a single token?) and use the *same* rule in the application and in the backfill, or the two will disagree. ## Phase 3 — Backfill Only rows that existed before dual write need conversion, but you generally sweep the whole table because you cannot cheaply tell which is which. The backfill runs in bounded batches ordered by the primary key, tracking the last key processed so it can resume; it commits per batch, sleeps or throttles when replication lag or lock waits rise, and is written so that re-running a batch is harmless. It should skip or overwrite consistently with the dual-write rule — the common safe form is "set the new columns only where they are still NULL", which never clobbers a value dual write already produced. ## Phase 4 — Verify before you trust Run a comparison that counts rows where the two representations disagree, and keep running it while dual write continues. On a large table, sample by key range rather than scanning everything at once. This is the step teams skip, and it is the step that catches the write path nobody knew about. ## Phase 5 — Switch reads Deploy code that reads the new columns and still writes both. This is the moment behaviour actually changes, so it is worth putting behind a flag that can be flipped without a deploy: flag on, watch errors and business metrics, flag off if wrong. Because the old column is still being maintained, rolling back is a flag flip or a redeploy, not a data recovery exercise. ## Phase 6 — Contract Once reads have been on the new representation long enough that you will never roll back past it — and long enough that any long-lived process, cached deployment or offline job has cycled — deploy code that stops writing the old column, then drop it. Only now is it safe to tighten: `NOT NULL` on the new columns, check constraints, unique constraints. Adding constraints last is deliberate; you can only assert an invariant once every writer honours it, and validating it is itself a potentially blocking operation that should use the add-then-validate form where the engine offers it. Dropping is not optional bookkeeping. A half-finished expand-contract leaves two representations, dual-write code nobody dares remove, and the next engineer unsure which is authoritative. Track the contract step as work, not as cleanup. ## Ordering and rollback summary Schema-additive first, code that writes both, data migration, code that reads new, code that stops writing old, schema-subtractive last. Between any two consecutive steps the system is consistent and both application versions function; every step can be reverted by redeploying the previous version without touching data. ## Cost to acknowledge Six deploys instead of one, a period of doubled write cost and doubled storage on that column, temporary complexity in the code, and a verification job to build. That is the price of not taking the service down; for a table small enough or a system with a maintenance window, a single locked migration is a legitimate and much cheaper choice. Knowing when the ceremony is *not* warranted is part of the answer.