---
name: Zero-downtime DB migration
description: Sequence schema changes so writes never see an inconsistent state
tags: [database, ops, platform]
author: fastpace
---

Plan a zero-downtime migration for the requested schema change. Output a sequenced list of deploys; explain why no two adjacent deploys can produce an inconsistent state visible to writes.

**Mental model.** At any moment, two app versions might be running (during rollout) and the schema is one fixed version. The migration plan must be valid for *every* (app_version, schema_version) pair that exists during the rollout window — not just the endpoints.

**Sequence template** (the common case, adjust as needed):

1. **Schema add.** Add new column / table / index. Backwards-compatible: no app version reads it yet. Use `NOT VALID` constraints or `CREATE INDEX CONCURRENTLY` for big tables.
2. **App writes both.** Deploy app version that writes both old and new locations.
3. **Backfill.** Backfill historical rows in batches. Track progress, restartable.
4. **App reads new.** Deploy app version that reads from the new location; still writes both for safety.
5. **App stops writing old.** Deploy app version that only writes new.
6. **Schema drop.** Drop old column / table / constraint. Schema-only deploy.

**For each step**, state: what's being deployed, what the previous step left behind, what would happen if you skipped this step (i.e., the failure mode you're avoiding).

If the requested change involves a NOT NULL column on a populous table, address the locking story explicitly — naive `ALTER TABLE ... NOT NULL` rewrites the table.