Zero-Downtime Schema Migrations.

At 03:00 you ship a migration that adds one column to the orders table. It ran in 8 milliseconds in staging. In production it takes the whole site down for four minutes, and the confusing part is that the migration itself still finishes in 8 milliseconds. Nothing in your code changed. You added a column. And every checkout on the site timed out.

Here is what happened. ADD COLUMN needs an ACCESS EXCLUSIVE lock on the table, the strongest lock Postgres has, held for those 8 milliseconds. But a nightly report was midway through a slow SELECT over orders and already held a weaker lock. Your ALTER could not get its lock, so it waited. And in Postgres, a statement waiting for a strong lock does not step aside politely, it holds its place at the front of the queue. Every SELECT and INSERT that arrived after it queued up behind it. One slow report plus one instant migration equals a full outage, and the migration was never the slow part.

orders
  ● held by   nightly_report    slow SELECT, still running
  ○ waiting   ALTER TABLE ...   wants ACCESS EXCLUSIVE, blocks on the SELECT
  ○ waiting   SELECT ...        your users, now stuck behind the ALTER
  ○ waiting   INSERT ...        checkout, stuck too
  ○ waiting   SELECT ...
  ▼
  everything queues behind the ALTER — the site is down

That is one way a migration breaks prod. The other is quieter and just as common: you drop a column, or rename one, and the app code that is still running from before the deploy does SELECT * or references the old name, and starts throwing 500s. Or the reverse, you deploy code that reads a new column before the migration that adds it has run. Either way, some version of your app is talking to a schema it does not understand.

Both failures come from the same root fact, and it is the one thing to hold onto for this entire post: the schema and the code never change at the same instant. A deploy rolls out over seconds or minutes, so for a while the old pods and the new pods are both live, hitting one shared database with one live schema. You can never make a change that only the new code understands, because the old code is right there using it too.

              rolling deploy window
  old  ████████████████░░░░░░░░░░░░░░░░
  new  ░░░░░░░░░░░░░░░░████████████████
               └──── both live ────┘
           one database, one schema

So the whole discipline reduces to three rules that every safe migration obeys at once:

• Additive first. A single step only adds. It never removes or tightens something the running code still depends on.
• Compatible always. At every instant, whatever code is live, old or new, works against the schema exactly as it is right now.
• Reversible until committed. Until the very last step, you can roll the app back without a data disaster.

Break any one and you are back at 03:00. The rest of this post is the pattern that keeps all three true, and the specific Postgres operations that quietly violate them.

Expand and contract: never change, only add then remove

The move that makes migrations safe is to stop thinking of a change as a change. A rename is not "rename the column." It is: add the new column, teach the code to use both, copy the data over, switch reads, stop writing the old one, and finally drop it. Six safe steps instead of one dangerous one. This is the expand/contract pattern (some people call it parallel change), and it runs in three phases.

  EXPAND ─────▶ MIGRATE ─────▶ CONTRACT
  add new        dual-write      drop old
  keep old       backfill        new only
                 flip reads
  └──── old schema works at every instant ────┘

Expand adds the new shape alongside the old one and changes nothing about how the old shape works. You add a nullable column, or a whole new table, and you deploy code that keeps reading the old field but starts writing to both. Nothing reads the new field yet, so if this deploy is wrong you roll it straight back, the new column just sits there unused.

Migrate is where the data moves. New rows already land in both places because of the dual-write. Old rows need a backfill to copy the historical data across. Once the new column is fully populated and verified, you flip reads over to it, ideally behind a feature flag so the switch is a config change and not a deploy.

Contract is the cleanup, and it is the only irreversible part. Once nothing reads the old field and you are certain, you stop writing it, then drop it. Everything before this point could be undone by rolling back the app. This step cannot. Which is exactly why it comes last, and often days later.

The reason this works is the invariant from the intro: at no point does any running version of the code disagree with the live schema. During expand, old code sees the old column untouched. During migrate, both are present and both are written. During contract, the old code that needed the old column is long gone. You never ask a running app to cope with a shape it was not built for.

Dual-writes: keeping two copies honest

The bridge across the migrate phase is the dual-write: while both the old and new fields exist, every write updates both, and reads pick one behind a flag. That way the new column is never stale, and the day you flip reads, the data is already correct.

               ┌──▶ old_column     always written
  write ───────┤
               └──▶ new_column     always written

               ┌──▶ new_column     after backfill verified
  read ──flag──┤
               └──▶ old_column     until then

// During MIGRATE: write both columns, read behind a flag.
fun saveOrder(order: Order) {
    // Same table, same row: set both in one UPDATE -> one row version, not two.
    db.update(
        "UPDATE orders SET status = ?, status_v2 = ? WHERE id = ?",
        order.status, order.status.toV2(), order.id,
    )
}

fun readStatus(id: Long): Status =
    if (flags.enabled("orders.status_v2"))
        db.query("SELECT status_v2 FROM orders WHERE id = ?", id)
    else
        db.query("SELECT status FROM orders WHERE id = ?", id)

The one thing you cannot allow is for the two copies to drift apart. If the old write lands and the new one fails, the row is now a lie, and you will only find out when you flip reads. When both columns live in the same row, one UPDATE sets both and they can never disagree. When the new copy lives in a separate table in the same database, wrap both writes in one transaction so they commit or fail together. When it lives in a different store, a search index, another service, there is no shared transaction to lean on, and you need the outbox pattern or an idempotent replay to guarantee the second write eventually happens. A backfill that also acts as a read-repair, fixing any row it finds mismatched, is a good safety net either way.

Reads get a flag for a reason: it decouples "the data is ready" from "we trust it." You can turn reads on for one percent of traffic, compare old versus new in the background, and roll it out gradually. If the new column is wrong, flipping the flag back is instant and touches no code. A bad deploy is a rollback and a scramble; a bad flag is a click.

Backfilling at scale: never one big UPDATE

Dual-writes handle new rows. The millions of old rows need copying, and this is the step that most often takes down a database that was otherwise migrated perfectly. The tempting version is one statement:

UPDATE orders SET region = lookup_region(shipping_zip) WHERE region IS NULL;

On a forty-million-row table that single UPDATE locks every row it touches, holds one enormous transaction open for minutes, bloats the table with dead row versions, and floods replication. The first time I ran an unbatched backfill on a table that size, replica lag climbed past ninety seconds, reads that had been routed to those replicas started returning stale or failing outright, and a "background" data fix became a customer-facing incident. The fix is to stop treating a backfill as a migration at all. It is a background job that walks the table in small, committed batches.

-- Walk the primary key in chunks. One small, committed UPDATE at a time.
UPDATE orders
SET    region = lookup_region(shipping_zip)
WHERE  id > $last_id
  AND  id <= $last_id + 1000
  AND  region IS NULL;   -- idempotent: a re-run skips rows already done

  scan the primary key in order, one committed batch at a time:

  │ 1..1000 │ 1001..2000 │ 2001..3000 │ 3001..4000 │ ...

  each batch → update → commit → sleep → check replica lag → next
  dies mid-run? just resume from the last committed id

Four properties make a backfill safe, and you want all four. It is batched by primary key so no single transaction is huge and locks release between chunks. It is throttled, sleeping between batches and backing off when replica lag climbs, so it never outruns the rest of the system. It is idempotent: the region IS NULL guard means running a batch twice changes nothing (this assumes the backfilled value is never itself NULL — if it can be, track progress with a backfilled_at marker instead), so a crash mid-run is harmless. And it is resumable, checkpointing the last committed id so a job that dies at row thirty million restarts at row thirty million, not row one.

// Resumable, throttled backfill. A background job, not a migration step.
var lastId = loadCheckpoint()
while (lastId < maxId) {
    db.update(
        """UPDATE orders SET region = lookup_region(shipping_zip)
           WHERE id > ? AND id <= ? AND region IS NULL""",
        lastId, lastId + BATCH,
    )
    lastId += BATCH
    saveCheckpoint(lastId)
    Thread.sleep(50)                                    // let real traffic through
    while (replicaLagSeconds() > 5) Thread.sleep(1000)  // never outrun the replicas
}

When it finishes, do not trust it blindly, but pick a check that can actually reach zero. A count of rows still NULL works only if NULL is never a legitimate result; if lookup_region() can return NULL, those rows re-run every pass and the count never settles. When the value can be null, track progress with a marker (backfilled_at) or by the checkpoint reaching the max id, and verify with a checksum comparing old and new. A backfill that silently skipped a batch is a landmine that goes off the moment the old column is gone.

The dangerous operations, one at a time

Expand/contract tells you the shape of a safe migration. But you still have to know which specific operations are cheap and which quietly take the strong lock. In Postgres the difference is not obvious from the SQL, so here are the ones that bite, and the safe form of each.

Adding a nullable column is instant, a metadata-only change with no table scan — the whole reason expand is cheap. But instant means no rewrite, not no lock: it still grabs ACCESS EXCLUSIVE for that instant, which is exactly how the 8-millisecond migration in the intro took the site down. On a busy table, wrap even the fast operations in lock_timeout and retry.

Adding a column with a default used to rewrite the entire table. Since Postgres 11 a constant default is also instant. But a volatile default still rewrites every row under the strong lock, so watch for that.

-- Fast on PG 11+: a constant default is metadata only.
ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'us';

-- Slow: a volatile default forces a full table rewrite under ACCESS EXCLUSIVE.
ALTER TABLE orders ADD COLUMN token uuid NOT NULL DEFAULT gen_random_uuid();

Adding NOT NULL to an existing column scans the whole table under a strong lock to prove no nulls exist. The trick is to add the constraint as an unvalidated CHECK first, which is instant, validate it under a gentle lock that lets writes continue, then flip.

ALTER TABLE orders ADD CONSTRAINT orders_region_nn
  CHECK (region IS NOT NULL) NOT VALID;                 -- instant, no scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn; -- scans, but writes continue
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;    -- PG 12+ skips its own scan
ALTER TABLE orders DROP CONSTRAINT orders_region_nn;    -- redundant now, drop it

Dropping a column is metadata-only and fast, but it is a contract step, so the order matters more than the lock. Remove every reference from the code and deploy that first, and remember SELECT * counts as a reference, an ORM materializing every column will break the instant the column is gone. Only once no running code touches it do you drop it.

Renaming a column in place is the classic trap. It is one statement, it looks harmless, and it breaks every running old pod the moment it lands, because their queries still name the old column. Never rename in place. Add the new name, dual-write, backfill, cut reads over, then drop the old, the full expand/contract dance. The same goes for changing a column's type: add a new column of the new type, backfill, cut over, drop the old.

Adding an index with a bare CREATE INDEX locks the table against writes for the entire build, which on a large table is minutes of no writes. CONCURRENTLY builds it without blocking writes. It cannot run inside a transaction block, and if it fails it leaves an invalid index behind that you drop and retry, but it is the only safe way on a live table.

CREATE INDEX idx_orders_region ON orders (region);               -- locks writes
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);  -- safe

Adding a foreign key in one statement locks both tables while it checks every existing row. Split it: add the constraint NOT VALID, which takes only a brief lock — on both the referencing table and the referenced customers, so a busy customers can still block it and lock_timeout applies here too — and skips the scan, then VALIDATE it separately under a lock that lets writes through.

ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

And underneath all of them, the lesson from the intro: set lock_timeout before any DDL. Without it a blocked ALTER will happily queue prod behind itself. With it, the statement gives up in a few seconds and you retry, turning a four-minute outage into a no-op you run again. One caveat: lock_timeout limits how long you wait for the lock, not how long you hold it. It protects the fast, no-rewrite operations; it will not save you from a slow rewrite once the lock is yours. That is what expand/contract is for.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN region text;   -- fails fast instead of queuing the site

-- Wrap it in a retry loop so a busy moment just means "try again", not "fail".
for attempt in 1..N:
    SET lock_timeout = '3s'
    run the DDL
    success            -> done
    lock_timeout error -> wait a few seconds, loop

Rollback safety: know your point of no return

Zero-downtime and safe-to-roll-back are the same property viewed from two angles, and expand/contract gives you both, but only if you respect the ordering. The expand and migrate phases are reversible essentially for free. Added a nullable column and dual-writes, then realized the design is wrong? Roll the app back to the version before, and the extra column sits there harmless. Nothing read it, so nothing breaks. You can take your time.

Contract is the point of no return, and naming it explicitly is the whole discipline. Once you drop the old column, rolling the app back to a version that expects it will fail. So contract comes last, after the new code has been fully rolled out and soaked long enough that you are confident you will not need to go back. Days, not minutes. There is no rush to drop a column; leaving it around one more week costs nothing, and dropping it one deploy too early costs an incident.

For data, prefer forward-fix over rollback. If a backfill wrote bad values, a rollback of the code does not un-write them; you fix forward with a corrected backfill. This is why every backfill is idempotent and re-runnable, it is your repair tool as much as your migration tool. And the cardinal rule that ties it all together: never pair a breaking schema change with the code change that depends on it in the same deploy. The moment they ship together, there is an instant where one is live and the other is not, and that instant is your outage. Separate them into steps, and each step is safe alone.

Where migrations break

Every failure here has a name, and knowing the names is how you catch them in review instead of at 03:00.

A bare CREATE INDEX on a live table. It locks writes for the whole build. Someone forgets CONCURRENTLY, and writes stall for minutes. Always concurrent on a table that takes traffic.

The lock queue from a slow query. Your instant DDL waits behind one long-running SELECT, and everything queues behind your DDL. Set lock_timeout, and never run migrations while a heavy report is holding locks.

No eyes on it while it runs. You shipped the migration blind and found out from users. During any DDL, watch pg_stat_activity and pg_locks for a growing wait queue; during a backfill, watch replica lag. If you cannot see the lock waits building, you cannot catch the pileup before it becomes an outage.

One giant backfill UPDATE. Long lock, replica lag, table bloat, all at once. Batch it, throttle it, watch the replicas.

SELECT * meeting a schema change. Add a column an old pod does not expect, or drop one it still selects, and an ORM materializing every column throws. Name your columns, and drop from the code before the schema.

The ORM's auto-migration. Frameworks that generate DDL from model diffs will happily emit the naive, table-locking form of every operation above. Read the SQL it produces before it runs, or write the migration by hand.

The migration tool's one big transaction. Some tools wrap a whole migration file in a single transaction, which means CREATE INDEX CONCURRENTLY errors out (it cannot run in one) and a long step holds locks the entire time. Know which of your steps must run outside a transaction.

The forgotten contract step. The migration "worked," reads flipped, everyone moved on, and the old column lives forever, half-written, confusing the next engineer who has no idea which of status and status_v2 is real. Schedule the drop, or it never happens.

When to reach for all this, and when not to

This is real work, so be honest about when it earns its keep. You need the full expand/contract dance when the table is large, the traffic is constant, and there is no maintenance window, which describes most production databases behind a live product. There, every change is a sequence of safe steps, no exceptions.

Skip it when the situation genuinely allows. A small table, a real maintenance window, or a system nobody is hitting right now, and you can just run the ALTER and move on, the lock is over before anyone notices. A brand-new table no code references yet has nothing to be compatible with. The machinery exists to protect a live schema that running code depends on; when there is no live traffic and no old code, you are paying for a guarantee you do not need. The skill is not applying the pattern everywhere, it is recognizing which of your changes actually put prod at risk.

The cheat-sheet

Screenshot this for the next time someone says "it's just one column."

Before you ship a migration, check:
  [ ] Additive only?  (adds columns/tables, removes nothing this step)
  [ ] Does the CURRENTLY deployed code still work after it runs?
  [ ] Does the NEW code work both before AND after it runs?
  [ ] Any table rewrite or long lock?  (volatile default, SET NOT NULL, type change)
  [ ] Index built CONCURRENTLY, outside a transaction?
  [ ] Backfill batched, throttled, idempotent, resumable?
  [ ] lock_timeout set, so a blocked DDL fails fast instead of queuing prod?
  [ ] Is the contract step (drop old) scheduled AFTER new code fully rolls out?
  [ ] Can you roll the app back without a data disaster?

operation                safe way
----------------------   ---------------------------------------------------------
add nullable column      instant, metadata only. just do it.
add column w/ default    PG 11+ constant default: instant. volatile default: rewrites.
add NOT NULL             CHECK ... NOT VALID -> VALIDATE -> SET NOT NULL
drop column              drop from code first (watch SELECT *), then drop the column
rename column            NEVER in place. add new -> dual-write -> backfill -> cut -> drop
change column type       new column -> backfill -> cut over -> drop old
add index                CREATE INDEX CONCURRENTLY (bare CREATE INDEX locks writes)
add foreign key          ADD ... NOT VALID, then VALIDATE CONSTRAINT
big backfill             batch by PK, throttle, watch replica lag, make it resumable
any DDL                  SET lock_timeout first, then retry on failure

A schema migration goes wrong because two clocks never line up: the code deploys over minutes, the schema changes in an instant, and for a while old and new code share one live database. So you never make a change that only one version understands. You expand, adding the new shape beside the old and writing to both. You migrate, backfilling the history in small throttled batches and flipping reads behind a flag. You contract, dropping the old shape only once nothing living still needs it. Every step is additive, compatible with whatever code is running, and reversible right up until the last one. There is no atomic "change the schema and the code" over a rolling deploy. There is only a sequence of steps, each safe on its own, run one at a time.