How Do Schema Migrations Stay Safe?

Four properties, and all four have to hold.

First, every intermediate state is a state the running application can work with. The old code is still running while you migrate, so the schema must never be in a state the old code cannot handle. That is the constraint that drives everything else, and it is why the safe pattern is expand and contract rather than change in place.

Second, the migration is idempotent. Running it twice has the same effect as running it once, and running it after a partial failure converges on the intended state rather than erroring out or making things worse. That is what lets you retry a failed migration without a human unpicking it.

Third, it is reversible, or you know it is not. Some migrations genuinely cannot be undone — a column type narrowing that drops information, for instance — and that has to be a conscious decision rather than a surprise. For everything else, write the down migration, and test it.

Fourth, it is observable. You want to know how far it got, how long it is taking, and whether it is still making progress. A migration that updates a million rows should be batched so that each batch commits, and so that the progress is visible rather than one enormous transaction that is either fully applied or fully rolled back after an hour of blocking.

The mechanics follow from those four. Use a versioned migration table so the system knows what has been applied. Take a lock so two deploys cannot run the same migration at once. Run inside a transaction where the engine supports transactional DDL — SQLite and PostgreSQL do, MySQL does not for most DDL. And test the migration against a copy of production data before you run it, because a migration that assumes the data is clean and is not is the most common failure of all.

Three steps in one script: add the column with a default so existing rows stay valid, backfill in a bounded way, and add the index the new column will be queried by.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new'
);

INSERT INTO orders (id, customer_id, amount_cents) VALUES
  (1, 1, 12000),
  (2, 2, 34050),
  (3, 1,  7500);

-- Step 1: add the column with a constant default, so old inserts still work.
ALTER TABLE orders ADD COLUMN channel VARCHAR(20) NOT NULL DEFAULT 'web';

-- Step 2: backfill only the rows that need it, in bounded batches.
UPDATE orders
SET channel = 'import'
WHERE id IN (SELECT id FROM orders WHERE channel = 'web' LIMIT 1000);

-- Step 3: index the new column for the queries that will filter on it.
CREATE INDEX idx_orders_channel ON orders (channel);

SELECT id, customer_id, amount_cents, status, channel FROM orders ORDER BY id;

Three rows come back. Order 1 has channel 'web' from the default, order 2 has 'web', and order 3 has 'import' because the backfill targeted it. The default did the work for the rows that were not touched, which is exactly why it is in the ALTER statement.

The backfill is written to be repeatable. The subquery selects at most a thousand rows that still hold the default value, and the outer UPDATE only touches those, so running the statement again picks up the next batch rather than corrupting anything. In a real migration you would run that statement in a loop until it affects zero rows, with a small pause between batches so the table stays responsive.

Three things that make this safe in production. The ADD COLUMN with a constant default is a metadata operation on modern engines, so it does not rewrite the table or lock it. The backfill runs in small batches rather than one UPDATE over the whole table, so it never holds a lock long enough to stall other traffic. And the index is created after the backfill, because building an index on a table that is simultaneously being updated is slower and more disruptive than building it on a settled table.

The order of the deploy matters too: add the column first, then deploy the code that writes to it, then backfill, then deploy the code that reads it, then add the NOT NULL constraint if one is needed. Each of those is separately reversible, and none of them requires downtime.

Because migrations fail. Networks drop, the deploy is interrupted, a statement times out halfway, somebody runs the script by hand and then the deploy runs it too. Idempotence means the migration converges on the intended state no matter how many times it runs, so a failure is recovered by running it again rather than by writing a repair script under pressure.

Idempotence in practice means every statement checks whether it still needs to do its work. Adding a column means checking the catalogue first — in PostgreSQL the information_schema, in SQLite the pragma_table_info function, in MySQL the information_schema — and only issuing the ALTER if the column is absent. Adding an index means using CREATE INDEX IF NOT EXISTS, which SQLite and PostgreSQL both support. Backfilling means a WHERE clause that only matches rows that have not been backfilled yet, so a second run affects zero rows.

The backfill is the one that most often is not idempotent, and it is worth being explicit about. A statement like UPDATE orders SET channel = 'import' WHERE channel IS NULL is idempotent, because after the first run there are no NULLs left. A statement like UPDATE orders SET channel = 'import' WHERE id < 1000 is not, because it sets the same rows again every time — harmless in this case, but if the update were an increment it would double-count. If you find yourself writing an update that increments a counter, add a condition that means it only applies once, and record which rows have been processed in a tracking table.

Two related properties worth naming. A migration should be transactional where the engine allows it, so a mid-script failure leaves nothing behind — SQLite and PostgreSQL give you that for DDL, MySQL does not for most DDL statements, which means a MySQL migration has to be written so that each statement is independently safe. And a migration should record itself in a version table in the same transaction, so that a crash between the work and the bookkeeping does not leave the system thinking the migration never ran.