Application releases are not instantaneous. During a rolling deploy, old and new code may connect to the same database at once; a background worker may lag behind web processes; a rollback may restore code but cannot undo every data transformation. Treat schema changes as a compatibility rollout across versions, not a one-line DDL statement attached to a deploy.
| Change | Safer sequence | Risk to check |
|---|---|---|
| Rename a column | Add new, dual-write/read, backfill, switch, later drop old | Old workers and reports still reference the original |
| Change data meaning | Add versioned field and transform with validation | Units, null semantics or rounding differ |
| Add required field | Add nullable, populate, validate, then enforce not-null | Large-table scan or lock during constraint change |
| Remove a table | Stop writes, observe reads, archive if needed, drop later | Undocumented jobs or exports depend on it |
Write down which application versions can use each migration stage. Include scheduled jobs, reporting tools and admin scripts, not only the main API. A code rollback is safe only if the expanded schema remains compatible with the prior version and the data has not crossed an irreversible boundary.
Backfills should be idempotent, resumable and bounded by rows or time. Use a stable cursor, record checkpoints and expose progress, failure count and remaining work. Throttle based on database load; pause when latency or replication lag crosses a limit. Verify transformed values with counts and sampled comparisons before switching reads.
For large tables, avoid one giant transaction that holds locks, generates excessive WAL or blocks vacuum. Choose batch size from measurements and test against production-like volume. Make old and new writes consistent during the overlap, often through application dual-write or a carefully designed database mechanism, and have a reconciliation query to catch drift.
“Online” does not mean lock-free. The required lock depends on the database and exact subcommand; a metadata change may still wait behind long transactions or briefly block requests. Inspect the engine’s current documentation, set lock and statement timeouts where available, schedule risky operations and watch lock queues. Split unrelated changes so one expensive operation does not enlarge the blast radius.
Separate schema migration from data backfill when their runtime and failure modes differ. Decide whether a failed migration can be retried, rolled forward or requires restore. Many production data changes are safer to roll forward than to reverse blindly. Backups are useful only when restore time and data-loss window meet the recovery objective; test them.
Run migrations once through a controlled deployment step, not independently from every application replica. Serialize migration execution, log version and duration, and alert on failure. Keep migration files immutable after they have run; add a new corrective migration instead of editing history. Review generated SQL before production and test both upgrade from the current schema and fresh-database setup.
Safe migrations respect overlapping code versions, production traffic and the fact that data changes may be irreversible. Expand compatibly, backfill in bounded batches, verify before switching, contract only when old consumers are gone and choose recovery deliberately. Speed comes from small predictable steps, not from combining schema, transformation and cleanup into one deployment.