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.

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.

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.

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.

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.

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 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.