"Just add the column, it's a tiny ALTER." That sentence has a special place in incident retrospectives. The migration runs for 12 seconds in staging and 40 minutes in production — because production has 80 million rows and the ALTER takes a lock that every read and write now queues behind. Your deploy, which was supposed to be invisible, just became a full outage with a migration still mid-flight and no clean way back.
The problem isn't that schema changes are hard. It's that a schema change is a coordination problem between two versions of your code, and most teams treat it as a one-line SQL script that runs once and disappears.
Why a direct migration is a trap
An ALTER TABLE in production isn't free. Depending on the engine and the operation, it can take a table lock that blocks reads and writes, or it rewrites the whole table under the hood. Either way, the duration scales with your data size, not your change size. A column addition that's instant on a 10k-row dev DB can stall a 100M-row production table for minutes — and every request in the meantime is piling up.
But locking is only half the danger. The other half is that your old code is still running when the new schema lands. If you drop a column your current deployed code still reads, you just broke production. If you change a type in a way the old code doesn't expect, same result. And migrations are notoriously hard to roll back: you can't un-run a data transformation that already touched half the rows, and reverting the migration while the new code is live creates a second outage.
So a safe migration has to satisfy two constraints at once: the new schema must not break the currently deployed code, and the old schema must not break the new code you're about to deploy. That's a two-version problem, and it needs a two-version plan.
The pattern: expand, migrate, contract
The standard answer is the expand-contract (a.k.a. parallel-change) pattern. You split one breaking change into three non-breaking steps spread across multiple deploys:
- Expand — add the new structure alongside the old, with no behavior change. Old code keeps working untouched.
- Migrate — move data from old to new, in the background, in batches that don't lock the table.
- Contract — once every old reader/writer is gone, remove the old structure.
Each step is independently safe and reversible on its own. The breaking change never exists as a single moment; it's spread out so there's no instant where code and schema disagree.
Expand: add, don't change
In the expand phase you only add. New nullable column, new table, new optional field — something the existing code doesn't know about and therefore can't be broken by.
-- expand: add the new column, nullable, no default that forces a rewrite
ALTER TABLE orders ADD COLUMN total_cents BIGINT;
If your engine rewrites the table to apply a default, either omit the default (let the app handle nulls) or backfill in the migrate phase instead. The rule for expand: nothing deployed today should notice the new structure exists.
Migrate: backfill in batches
Now populate the new column from the old source, but never in one UPDATE that locks the table for the whole run. Page through the primary key and update small ranges, sleeping between batches to let production traffic breathe:
-- migrate: backfill in bounded batches, not one giant UPDATE
UPDATE orders
SET total_cents = price_cents + tax_cents
WHERE id BETWEEN :from AND :to AND total_cents IS NULL;
Keep the batches small and add a small pause. This spreads the load and keeps lock durations short. You can run it live because each statement touches only a slice.
Contract: delete only after the code is gone
The contract phase removes the old column or table. But you can only do this safely once no deployed code references it anymore — meaning the new code that reads/writes the new structure has been live long enough that the old version is fully rolled out and the old deploy is retired. Premature contract is how you delete a column your still-running old code needs.
-- contract: safe only after all readers/writers moved to total_cents
ALTER TABLE orders DROP COLUMN price_cents;
ALTER TABLE orders DROP COLUMN tax_cents;
Dual writes and the read transition
The tricky middle is when both old and new structures exist and you're switching code over. Two patterns help:
- Dual write during transition. New code writes both old and new (or new only, depending on direction). This keeps the old column correct while you finish the rollout. Accept that for a window, writes hit both.
- Read with fallback. New code reads the new structure first; if it's null (not yet backfilled for this row), fall back to computing from the old. This decouples "code is deployed" from "data is fully migrated."
# read path during transition
total = row.total_cents
if total is None:
total = row.price_cents + row.tax_cents # fallback to old source
The fallback is what lets you deploy the new code before the backfill finishes, which is the whole point — the deploy and the data move become independent.
The details that actually bite
A few footguns show up in real migrations:
-
Indexes. Adding an index on a big table can lock it just like an ALTER. Use
CREATE INDEX CONCURRENTLY(Postgres) so it builds without blocking writes. - Foreign keys. Dropping a referenced column needs the FK gone first; the dependency order matters and a half-applied contract migration will fail.
- NOT NULL without a default. If you eventually want the new column NOT NULL, you can't just flip it while nulls exist — backfill fully, then add the constraint in a separate, validated step.
- Triggers and views. Anything that reads the old column will quietly break on contract. Grep your codebase and stored objects before dropping.
Treat the migration as part of the deploy
The mental shift is this: a schema change is not a script you run and forget. It's a small distributed-systems problem — two code versions that must both agree with the data at every instant. Expand-contract makes that agreement explicit by never letting the disagreeing moment occur. Ship the expand, deploy the code that uses it, migrate the data at leisure, then contract once you're sure nothing old is left.
Your "tiny ALTER" becomes three boring, reversible, individually safe steps. That's the version that doesn't page you at 2am.
Top comments (0)