Schema Changes Without the Outage: Expand, Backfill, Contract

The most dangerous command in production isn't rm — you have backups (right?). It's ALTER TABLE on a hot table at noon. Schema migrations sit at the intersection of "must happen regularly" and "can lock a table your entire product stands on", and the difference between teams that fear deploy day and teams that ship schema changes hourly is one discipline: expand–migrate–contract. Here's the discipline, plus the database-specific landmines around it.

The prime directive: code and schema never jump together

During a rolling deploy, old code and new schema coexist — and on rollback, new schema meets old code again. Every migration must therefore be compatible with the code version on both sides of it. Once you internalize that single sentence, the whole pattern falls out: never rename, never change semantics in place, never drop what running code still touches. Instead, every risky change decomposes into safe phases:

Example: rename orders.state → orders.status (the "trivial" change that isn't)

EXPAND    1. Add nullable `status` column           (deploy: schema only)
          2. Code writes BOTH columns, reads old    (deploy: code)
BACKFILL  3. Copy state → status in batches         (job, not migration!)
SWITCH    4. Code reads `status`, still dual-writes (deploy: code)
          5. Soak. Verify. Sleep on it.
CONTRACT  6. Code stops touching `state`            (deploy: code)
          7. Drop `state`                           (deploy: schema — days later)

Seven steps where the naive version had one — and every step is individually boring, instantly deployable, and rollback-safe. That's the trade: you exchange one terrifying moment for seven trivial ones. (Feature-flagging the read-switch in step 4 makes even that reversible at runtime — flags and migrations are close friends.)

Backfills: the step that takes down more sites than DDL

Step 3 hides the real danger. UPDATE orders SET status = state on 80M rows is one giant transaction: lock pressure, replication lag that makes replicas serve ancient reads, and an undo log the size of a small moon. Backfills are jobs, not migrations:

// Batched, resumable, throttled — the only acceptable shape
Order::whereNull('status')
    ->chunkById(2_000, function ($orders) {
        DB::table('orders')
            ->whereIn('id', $orders->pluck('id'))
            ->update(['status' => DB::raw('state')]);
        usleep(50_000);                    // be polite to replication
    });

Rules: batch by primary key (not OFFSET — offset pagination degrades linearly), make it resumable (the whereNull IS the checkpoint), watch replication lag while it runs, and expect to pause it during peak. For dual-write consistency during the window, the write path owns new rows; the backfill owns history; the overlap is idempotent by construction.

Know your engine's locking personality

• Postgres: most ALTER TABLE ADD COLUMN (even with constant defaults, since v11) is metadata-only — instant. The two famous traps: CREATE INDEX without CONCURRENTLY takes a write-blocking lock (always use CONCURRENTLY in prod — note it can't run inside a transaction, so tell your migration tool), and adding a NOT NULL / foreign key constraint validates the whole table under lock — split it: add NOT VALID, then VALIDATE CONSTRAINT separately (takes a gentler lock). Also: even a metadata-only ALTER needs a brief exclusive lock, and a long-running query ahead of it in the queue makes everything behind wait — set lock_timeout = '5s' in migrations so they fail fast instead of queueing a pile-up.
• MySQL: InnoDB online DDL (ALGORITHM=INPLACE/INSTANT) handles a lot, but the matrix of what's instant vs rebuilding is version-specific — check it per change, and remember MySQL DDL implicitly commits (no transactional migrations — one more reason each migration file should contain exactly one change). For big tables where online DDL falls short, the community tools are superb: gh-ost or pt-online-schema-change build a shadow table and swap — this is precisely how the giants alter billion-row tables at lunch.

The destructive-change quarantine

Dropping columns/tables deserves its own paranoia tier: never in the same release that stops using them (rollback becomes impossible), soak for days with the column unused, verify with query logs — Postgres's pg_stat can't tell you nobody SELECTs a column, but your application logs and grep can — and rename-then-drop for tables (orders_deprecated_20260901 sitting for a week catches the forgotten cron job that a straight DROP would have detonated). Migration rollback methods, meanwhile, are mostly fiction past dev — you can't un-drop data — so invest in roll-forward discipline and backups you've actually restored instead.

The pre-flight checklist

□ Compatible with code on BOTH sides of the deploy?
□ One schema change per migration file?
□ Table size checked? (>1M rows: engine-specific plan; >50M: gh-ost/shadow territory)
□ Index creation CONCURRENTLY / online?
□ Backfill as a batched job, not in the migration?
□ lock_timeout set so we fail fast, not queue a pile-up?
□ Destructive step scheduled ≥1 release later?
□ Rehearsed against a production-sized copy? (staging's 400 rows prove nothing)

Teams that adopt this checklist stop scheduling migration windows entirely — schema changes become Tuesday-afternoon events that nobody Slacks about. Which is the actual goal: not heroic migrations, but the permanent absence of migration heroics.

Staring at an ALTER on your biggest table right now? Don't run it yet — this is exactly the hour of consulting with the best incident-prevention ratio I offer.