Database migrations

Database migrations

A schema migration on a live database is surgery on a running patient: the old code and new code both run during the transition, the change may lock a table users depend on, and a botched migration can lose data. Expand-contract and online techniques make it survivable.

Method

1. Split every breaking change into expand and contract. Never change schema and code in one incompatible step. Expand: add the new (column, table) in a backward- compatible way; deploy code that writes both old and new; backfill existing data; switch reads to new; only then, a release later, contract (drop the old). This is what makes deploys rollback-able across the change (see rollback-strategy, blue-green-deployments: the same compatibility window).
2. Make each step backward-compatible. During the migration, the currently-running (old) code must keep working against the new schema, and the new code must tolerate the old data: because both run simultaneously during a rolling deploy (see deployment-pipelines). A migration that breaks the running version turns a deploy into an outage.
3. Avoid locks that block production. A naive ALTER TABLE can lock the whole table for the duration (catastrophic on a large hot table); use online/ concurrent DDL (CREATE INDEX CONCURRENTLY, online schema-change tools) that build without blocking reads and writes. Know your database's locking behavior for each operation before running it on a big table.
4. Backfill in batches, not one statement. Updating millions of rows in a single transaction holds locks, bloats, and can time out; batch it (a few thousand rows at a time, with pauses, resumable: see script- idempotency, incremental-processing) so it does not overwhelm the database or block other work (see backpressure). Backfills are their own careful operation, separate from the DDL.
5. Run migrations forward-only, decoupled from code deploys. Migrations apply as their own step, additive and forward (see rollback-strategy: you roll back code, not schema); a migration framework tracks what has run so it applies once and in order. Down-migrations for destructive changes are often impossible (the data is gone): design so you never need them.
6. Test on production-like data, back up before destructive steps. A migration that works on an empty dev table can lock for an hour on production volume: test against a realistic copy (see test-environment-parity), and take a verified backup before any irreversible contract step (see backup-restore). The migration you cannot undo is the one you must be able to restore from.

Boundaries

• Migrations manage schema evolution within one database; changes to schemas other teams consume (events, shared contracts) are api-change-management and schema-evolution territory, with their own coordination.
• Expand-contract adds steps and calendar time (a breaking change spans multiple releases); this is the price of zero-downtime and rollback safety, and skipping it is how migrations cause outages.
• NoSQL "schemas" migrate differently (often lazily, in application code on read: see nosql-modeling); the expand-contract thinking transfers even where the DDL does not.