Database Migrations Without Downtime

The dangerous part of a database migration is rarely the SQL itself. It is the moment when old application code and a new schema, or new code and an old schema, run at the same time. During a rolling deploy that moment always exists, so migrations must be written to survive it.

The expand-and-contract pattern makes this explicit. First expand: add the new column, table or index without removing anything, and keep the old structure working. Deploy code that writes to both old and new places and reads from the old. Then backfill existing data in batches. Finally, switch reads to the new structure and, in a later release, contract by removing what is no longer used.

Avoid operations that lock large tables for a long time. Adding a column with a default, creating an index or changing a type can block writes on big tables depending on the database version. Use concurrent index creation where available, and split heavy backfills into small transactions with pauses.

Every migration should have a tested rollback path, even if the rollback is "deploy the previous code" because the schema change was additive. Destructive steps such as dropping a column belong in their own release, after the code that referenced them is confirmed gone.

Run migrations from a single, controlled place: a deploy step or a startup hook that records what has been applied. Run them against a copy of production data before release; the surprises are almost always in the data, not the schema.