data-migration

Data migration

Migrations are the most irreversible thing most engineers do routinely. Code can be rolled back; a dropped column cannot, and a botched backfill can corrupt data in ways no deploy fixes.

Two properties make a migration safe, and both must hold:

• Backwards compatible: the currently deployed code works before and after it runs
• Reversible, or backed up: you can get back, or you have a copy you have tested restoring

If a migration has neither, it needs a maintenance window and a rehearsal.

1. Never combine schema change with data change

Separate migrations, separate deploys:

1. Add the new structure
2. Backfill it
3. Switch the code to use it
4. Remove the old structure — later, as its own change

Combining these makes rollback impossible: reverting the code leaves the data half-moved, and reverting the data loses whatever the new code wrote.

Done when: each step is independently deployable and revertible.

2. Use expand-then-contract for anything breaking

Renaming a column is not a rename in production. It is:

1. Expand — add new_name, nullable
2. Dual-write — deploy code writing both; reads still use the old
3. Backfill — populate new_name for existing rows, in batches
4. Switch reads — deploy code reading the new column
5. Stop writing the old
6. Contract — drop old_name, well after everything is stable

Slow and boring, and it is the only way to do it without a window. Each step is individually safe to roll back.

Done when: no single step breaks the currently running code.

3. Know which operations take locks

The operation that takes an exclusive lock on a large table stops all writes for its duration. This is the most common cause of migration incidents, and the details are engine-specific — check yours.

Common traps in Postgres:

• Adding an index without CONCURRENTLY blocks writes for the build
• Adding a column with a volatile default rewrites the table on older versions
• Changing a column type usually rewrites
• Adding a foreign key takes a lock on both tables while validating — add NOT VALID, then validate separately
• A migration waiting behind a long transaction queues everything behind it, so a "fast" migration can still cause an outage