Evolving a schema without downtime

You can't take the database offline to add a column or rename one — and during a rolling deploy old and new code run at once, so migrations must be backward compatible.

The constraint: nothing stops

Adding a column sounds trivial until you remember the database is serving live traffic and you deploy by rolling new instances in while old ones still run. For a window of minutes, two versions of your app talk to one schema at the same time. A migration that the new code needs but the old code can’t tolerate will break every still-running old instance. So schema change isn’t one step — it’s a sequence of individually-safe, backward-compatible steps.

The expand–contract pattern

Also called parallel change. Never mutate in place; expand the schema to support old and new simultaneously, migrate over, then contract away the old. Renaming name to full_name becomes:

1. expand:   add nullable column full_name           (old code ignores it)
2. backfill: copy name -> full_name for existing rows (batched, in background)
3. dual-write: new code writes BOTH columns           (old code still reads name)
4. switch:   deploy code that reads/writes full_name  (name now unused)
5. contract: drop column name                         (only after all code moved)

Each step is reversible and safe with mixed code running. The invariant: at no point does a deployed version depend on a column the schema lacks, or break on a column the schema added. Additive-first, destructive-last.

The migrations that bite

Some DDL is dangerous on a large, busy table:

Locking rewrites — older engines rewrite the whole table (and hold a lock) for an ADD COLUMN with a default, a type change, or adding an index. On a 100M-row table that’s an outage. Modern Postgres/MySQL make many of these online, but verify per operation.
NOT NULL with no default — rejects the old code’s inserts, which don’t supply the column. Add it nullable, backfill, then enforce the constraint.
Rename / drop — instantly breaks any running code referencing the old name. Always route these through expand–contract.

When the engine can’t do it online, use an online schema change tool (gh-ost, pt-online-schema-change): it builds a shadow copy, mirrors writes via triggers or the binlog, backfills, then swaps the tables in atomically.

Backfilling safely

Backfilling millions of rows in one UPDATE locks them and floods replication. Do it in batches (a few thousand rows), throttled (pause if replica lag climbs), and idempotently (re-runnable after a crash — only touch rows not yet migrated). Treat the backfill as a long-running, resumable job, not a single statement.

Where it shows up

Every long-lived service with a relational store: Rails/Django migrations, Flyway, Liquibase, gh-ost at scale. It’s also why event-driven and microservice designs favor additive, versioned message schemas — same backward-compatibility rule, applied to data on the wire.

The interview cue

When a design implies a schema change on a live system, don’t say “we’ll migrate the table.” Say: “I’d do an expand–contract migration — add the new column, backfill in throttled batches, dual-write while both code versions run, switch reads, then drop the old column — so there’s never a moment where deployed code and schema disagree. For a big table I’d use an online schema-change tool to avoid the locking rewrite.” Showing you account for mixed-version code during the rollout is the senior tell.