Expand-and-Contract Database Migrations Without Downtime

A database migration is risky when old and new application versions run at the same time. Expand-and-contract separates a change into compatible phases: add the new structure, move reads and writes gradually, verify data, then remove the old structure only after every dependency has stopped using it.

The practical problem

A service replaces full_name with given_name and family_name. It first adds nullable columns and deploys code that can read either representation. A throttled job backfills records and records ambiguous names for review. New writes populate both representations. Metrics show the old read path falling to zero. After all workers, reports, exports, and rollback images are updated, the team stops dual writes, waits through the rollback window, and drops full_name in a separate release.

This scenario defines the boundary for expand contract database migration: the system of record, the component that enforces the decision, the asset or money at risk, and the condition that ends an exception. If one of those is unknown, discover it before changing production or presenting the number.

A step-by-step workflow

1. Scope the system. Map every reader and writer, including reports and jobs.
2. Establish the baseline. Add nullable or default-safe schema without rewriting the whole table.
3. Apply the control. Deploy backward-compatible application code.
4. Test failure paths. Backfill in small resumable batches ordered by stable key.
5. Verify the result. Verify counts and sampled values.
6. Record ownership. Move reads behind a flag.
7. Close the loop. Stop old writes only after observation.
8. Review the change. Retain the old field through the rollback window.
9. Complete the review. Remove constraints, indexes, code, and columns in separate reviewed steps..

Prove the failure paths

Start with a healthy case based on “Map every reader and writer, including reports and jobs”, then repeat the exercise while deliberately violating the assumption behind “backfill in small resumable batches ordered by stable key”. Capture the observable result, the alert that should fire, and the person who decides whether to stop or continue. Next, simulate the most credible failure in this guide: a default that rewrites a large table can lock production. The test passes only when the team detects the condition, limits its impact, and restores service or the decision model without relying on undocumented knowledge. A final replay should cover backfills that scan repeatedly compete with user traffic; this is the check most likely to reveal that a control works in a diagram but not at the actual boundary.

Evidence worth keeping

• Track migration phase
• Application versions
• Backfill cursor
• Rows per batch
• Lock time
• Replication lag
• Error rate
• Old-path reads
• Dual-write mismatch
• Null counts
• And cleanup owner. A migration is not complete until obsolete code and data are removed.

Attach each record to the concrete object under review: the relevant asset, release, customer, contract, key, query, or configuration. Preserve timestamps and version identifiers so another reviewer can reconstruct the sequence. The evidence list above should answer three questions: what was true before the change, what changed, and what proves the intended outcome. For this topic, the most useful cross-check is “verify counts and sampled values”. Keep sensitive fields out of screenshots and tickets; retain structured exports or signed records when they provide stronger provenance. Set retention according to incident, audit, privacy, and contractual needs rather than keeping everything indefinitely.

Common mistakes

• A default that rewrites a large table can lock production.
• dual writes without reconciliation allow divergence.
• backfills that scan repeatedly compete with user traffic.
• dropping the old field in the same release removes rollback.
• forgetting analytics or exports creates delayed failures..

A focused operating checklist

• Name the owner of the system and the owner of the decision.
• Verify “add nullable or default-safe schema without rewriting the whole table” from the actual production or financial source.
• Exercise the failure described by “A default that rewrites a large table can lock production”.
• Reconcile the observed result with the evidence list above.
• Put an expiry and rollback condition on every exception.
• Monitor the user, security, reliability, or cash outcome—not only activity.
• Revisit dependencies when the underlying product, contract, or standard changes.

Make this control sustainable

Assign the owner closest to the enforcement point described in “deploy backward-compatible application code”, plus a backup who can execute the rollback. Review the control after a material release, supplier change, incident, financing event, or policy change that affects its assumptions. An alert is useful only when it names an action: inspect, throttle, revoke, reconcile, fail over, or escalate. Use the evidence measurements above to set thresholds from observed workload or financial behavior. Treat dual writes without reconciliation allow divergence as a specific review trigger. Close temporary exceptions with an owner, expiry, compensating control, and proof that the normal path was restored.

Primary references and further verification

Use the database vendor’s current locking and online-DDL documentation. Test on production-like data volume because a statement that is instant on an empty staging database may be expensive on a large table.

For a current expand contract database migration decision, record the publication or effective date of the primary material and the version actually deployed or executed. Recheck vendor, standards-body, regulator, or accounting guidance when the product, protocol, vulnerability, or contract changes. Discovery summaries can point to an issue, but the decision record should link to the authoritative source and the organization’s own reproducible evidence.

Conclusion

The practical outcome is a control the team can explain and test: map every reader and writer, including reports and jobs, then remove constraints, indexes, code, and columns in separate reviewed steps.. Keep the assumptions and exception path visible. That is more useful than a generic checklist because the next reviewer can reproduce the decision, see where it can fail, and reverse it safely.