Database Migration

Migration is an engineering practice that manages database schema changes through versioned SQL scripts, which are traceable, rollback-capable, and can be consistently replayed across different environments.

The expand-contract pattern has three steps: expand (add new fields while keeping the old structure), migrate (deploy code that reads and writes the new structure and backfill data), and contract (remove the old structure), making breaking changes rollback-able and observable.

Directly renaming a field or changing its type will inevitably cause one of the old and new code versions to error during a rolling release.

The combination of dry-run and manual review checkpoints is already a mature practice in database migration and infrastructure changes (such as Terraform's two-phase plan/apply).

Database migration is a typical high-risk change scenario: it may modify table structures, rewrite data, affect online query performance, and once something goes wrong it is often difficult to roll back quickly and may cause data loss or service interruption.

For long backfills on large tables, if they fail midway due to a dropped connection, lock timeout or lagging replica, the migration should be made safe to re-run, using idempotent backfills (copying rows whose target is still empty) and retries with backoff and jitter, rather than rolling back.

Backfills should be processed in batches, avoiding locking a large number of rows (such as fifty thousand) in a single transaction.

Renaming a column is actually two operations disguised as one, and the naive approach loses data on rollback; dropping a column has no honest down, because the data is already gone.

During deployment there is a window in which old code and new code coexist, and the database may serve both versions simultaneously for anywhere from thirty seconds to two minutes.

Distinguish between types of migration failure: a change with a structural error should be rolled back, while a change that is structurally fine but hit network jitter should be retried.

A statement timeout should be set for every migration; without one, a migration competing for locks will block all queries on the table and drag down the entire application.

The core contract of a migration comes in pairs: up defines how to advance the structure to the new version, and down defines how to return to the previous version.

A reliable set of migration conventions typically includes: timestamps in names to guarantee order, a ban on calling application model code inside migrations, explicitly declared lock strategies when operating on large production tables, and running a full up-down-up in the staging environment.

The single rule that avoids most 3 a.m. incidents: additive migrations run before deployment, destructive migrations run after deployment.

Rollback must take effect on the structure currently live, running no down migrations at all. If that is not possible, the migration order is wrong.

The prerequisite for rollback is that operations are idempotent or transactionally guaranteed; database changes should prioritize migration scripts over direct SQL, and file changes should be included in version control.

Each migration script typically contains an up (forward change) and a down (rollback) operation, sorted by timestamp or sequence number.

Schema changes in production environments are high-risk operations: field type changes can cause existing data to become unparseable, and deleting columns results in permanent data loss.