The migration that taught me this lesson looked completely safe: add a NOT NULL column with a default value to a table with a few million rows. It ran locally in under a second against a test database with a few hundred rows. Against production it took long enough to hold a lock that queued every other write to that table, and the app started timing out on anything that touched it. The migration itself wasn’t wrong. The assumption that “it worked in dev” meant “it’s safe in production” was.

Why naive migrations break things

Most of the danger comes from locks and from the gap between when a schema change lands and when every running instance of your app has picked it up. Adding a column with a default in older Postgres versions used to rewrite the entire table under an exclusive lock; MySQL’s ALTER TABLE has similar rewrite behavior for many operations depending on storage engine and version. Renaming or dropping a column that running application code still references breaks requests the moment the migration commits, regardless of how fast it runs, because old app code and new schema are now out of sync for however long your deploy takes to roll out.

Postgres has improved a lot here – since version 11, adding a column with a constant default no longer rewrites the table – but the general problem doesn’t disappear: any change that a currently-running version of your app doesn’t expect is a landmine, independent of how the database executes it.

The expand/contract pattern

The reliable approach is to split what feels like one migration into several independent, backward-compatible steps deployed separately:

  • Expand: add the new column, table, or index without removing anything old. Make it nullable or give it a safe default so existing code that doesn’t know about it keeps working.
  • Backfill: populate the new column for existing rows, in batches, without touching the write path old code depends on.
  • Migrate reads and writes: deploy application code that writes to both old and new locations, then code that reads from the new one, confirming correctness along the way.
  • Contract: once nothing references the old column or table anymore, drop it in its own migration.

This is more deploys than a single ALTER TABLE, but each step is small enough to reason about and to roll back independently. If step three reveals a data problem, you haven’t already dropped the column you’d need to fall back to.

Backfilling large tables safely

A single UPDATE touching millions of rows takes the same kind of lock as the bad column-add above, just for longer. Batch it instead – update a few thousand rows at a time, in a loop, with a short pause between batches so replication and other traffic have room to breathe:

DO $$
DECLARE
  rows_updated int;
BEGIN
  LOOP
    UPDATE orders SET status_v2 = map_status(status)
    WHERE status_v2 IS NULL
    AND id IN (SELECT id FROM orders WHERE status_v2 IS NULL LIMIT 5000);

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.25);
  END LOOP;
END $$;

For MySQL, tools like gh-ost automate this batching for full schema changes, applying them via a shadow table and cutting over with minimal locking – worth reaching for once tables get large enough that even batched manual updates feel risky.

Indexes deserve the same care

Adding an index on a large table with a plain CREATE INDEX locks out writes for the duration of the build. Postgres’s CREATE INDEX CONCURRENTLY builds the index without holding that lock, at the cost of taking longer and needing a retry if it fails partway through – a trade worth making on any table your app is actively writing to.

Framework tooling helps, but doesn’t remove the thinking

Django, Rails, and most modern migration frameworks will happily generate a migration that adds a non-nullable column with a default in one step, and it’ll work fine in development. None of them know your production table has forty million rows or that a background job is writing to it every second. The framework gets you a correct migration; it’s still on you to decide whether it needs to be split into expand/backfill/contract before it touches a table anyone depends on. The rule of thumb I use: if the table is small and the app is rarely deployed at that exact moment, one step is fine. If either of those isn’t true, split it.

Set a lock timeout, always

One habit that’s saved me more than once, independent of everything above: set a short statement or lock timeout for migration sessions specifically, so a migration that’s about to block the whole table fails fast and loudly instead of quietly queuing up every other query behind it for minutes.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status_v2 text;

If the ALTER TABLE can’t grab its lock within two seconds because something else is holding a long-running transaction against that table, it errors out immediately. That’s a migration you can retry at a quieter moment, rather than one that silently piles up a queue of blocked queries behind it until something upstream starts timing out and pages someone. A failed migration is an easy problem. A migration that succeeded but took the app down for four minutes on the way is a much harder one to diagnose after the fact.