Backward-Compatible Database Migrations

Backward-compatible migrations let old and new application versions safely share a database during a deployment transition. They usually stage additive schema changes, compatible code and data movement, and delayed cleanup.

Backward-compatible migrations let application versions on both sides of a deployment transition use the database safely. This matters when instances are replaced gradually, background workers lag behind, or a rollback restores older code. Compatibility must cover actual reads and writes, not merely whether a migration command succeeds.

For a typical column rename, use an expand-and-contract sequence. First add the new column while retaining the old one. Deploy code that can read both representations and keeps them consistent when writing; depending on deployment order, it may initially continue writing the old column as well. Backfill existing rows, verify the new data, and then deploy code that relies only on the new column.

Only after old application versions and workers are gone, and rollback requirements are understood, remove the old column in a later migration. The intermediate schema should support every application version that can still run. For large tables, plan for lock duration, transaction behavior, batching, and database-specific online DDL rather than assuming every schema change is instant.

The same principle applies to changes beyond renames. Adding a nullable column or a column with a safe default can be compatible, while tightening constraints, changing types, or altering semantics may need staged code and data changes. Review the migration, application behavior, deployment order, and rollback path together.

Key points

• Keep schemas usable by every application version that may overlap during deployment.
• Prefer additive changes first; remove obsolete schema only after old code is retired.
• Plan separately for historical backfills and writes that occur while the backfill runs.
• Verify data invariants and account for rollback, workers, locks, and database-specific DDL behavior.

Interview questions

What does backward-compatible mean for a database migration?

A migration is backward-compatible when application versions that may overlap during deployment can all operate correctly against the schema they encounter. This includes query behavior, write behavior, constraints, and data meaning. It is not enough for the migration to complete successfully: old workers or a rollback may still use the previous shape. Compatibility is therefore a property of the whole transition, not just the DDL statement.

How would you safely rename a database column in a service with a rolling deployment?

Do not rename the column and deploy code expecting the new name in one step if old instances may still run. Add the new column, deploy code that supports both schemas and keeps the values consistent, then backfill existing records and verify the result. After all old code is retired, deploy code that uses only the new column. Drop the old column in a later migration once rollback and worker lifetimes have been considered.

How would you add a required column to a large, actively written table?

Adding a non-null column can fail because existing rows need a valid value, and concurrent old code may omit the column on inserts. A safer plan may add it as nullable, deploy code that supplies or tolerates it, backfill rows in controlled batches, and then enforce non-null after verifying completeness. Some databases support particular defaults or DDL operations efficiently, but their locking and rewrite behavior is engine- and version-dependent. Validate the plan against production-scale data and the target database.

Why is a backfill alone insufficient when migrating data in a live system?

Backfilling and dual-writing solve different problems. A backfill populates rows that existed before the new representation was introduced, while dual-writing (or an equivalent synchronization mechanism) handles changes made during the transition. Without synchronization, a row can be updated after it was backfilled and leave the representations inconsistent. A robust plan defines which representation is authoritative at each phase and verifies that the destination is complete before switching reads.

How do you review a migration for production safety beyond checking compatibility?

I would first identify the change's compatibility window: application instances, workers, scheduled jobs, and rollback versions that may touch the affected schema. Then I would split the transition into additive schema changes, compatible code and data movement, and delayed cleanup. For large tables I would assess locks, transaction duration, batching, replication lag, and database-specific DDL behavior, with monitoring and a pause or recovery plan. Finally, I would verify data invariants and rehearse the deployment order, including what happens if a stage is rolled back.