How do you run a schema migration on a large production table without downtime?

#Why the naive migration is dangerous

The naive approach to "rename this column" or "make this field required" is a single migration that changes the column and a single deploy that changes the application to match. The problem is that a schema change and an application deploy are never atomic together: during a rolling deploy, old application pods (expecting the old schema) and new pods (expecting the new schema) run against the same database simultaneously for anywhere from seconds to minutes. If the migration already dropped the old column, the old pods start throwing errors on every request until they're fully replaced. On a large table, it's worse before you even get there — an operation like ALTER TABLE ... ALTER COLUMN ... TYPE ... or adding a NOT NULL column with a default, on older Postgres? versions, can require an ACCESS EXCLUSIVE lock and a full table rewrite, blocking every read and write on that table for the entire duration, which on hundreds of millions of rows can be minutes to hours.

#The expand/contract pattern

The standard fix is to split one "risky" migration into several small, individually-safe, individually-deployable steps, so that at every point in time the schema supports both the old and new application code simultaneously. This is usually called expand/contract (or "parallel change"):

Concretely, "rename email to email_address on a 300M-row users table":

-- Step 1 (EXPAND): add the new column, nullable — instant, metadata-only change
ALTER TABLE users ADD COLUMN email_address TEXT;

-- Step 2 (BACKFILL): populate it in small batches to avoid a long-running
-- transaction and long lock waits against concurrent writers
UPDATE users
SET email_address = email
WHERE id BETWEEN 1 AND 10000 AND email_address IS NULL;
-- repeat with the next id range, throttled, e.g. via a background job

# A batched backfill job — small transactions, a short pause between
# batches so it doesn't compete with production traffic for I/O and locks.
BATCH_SIZE = 10_000

def backfill_email_address(db):
    last_id = 0
    while True:
        rows = db.execute(
            """
            UPDATE users
            SET email_address = email
            WHERE id > %s AND email_address IS NULL
            ORDER BY id
            LIMIT %s
            RETURNING id
            """,
            (last_id, BATCH_SIZE),
        )
        if not rows:
            break
        last_id = rows[-1]["id"]
        time.sleep(0.1)  # yield to concurrent OLTP traffic

Then the application is deployed in two stages: first a version that dual-writes both email and email_address on every insert/update (so new rows stay consistent with the backfill), and once every pod runs that version and the backfill is confirmed complete and verified, a second version that reads and writes only email_address. Only after that second deploy is fully rolled out — and typically after a waiting period, in case of rollback — do you run the contract step: ALTER TABLE users DROP COLUMN email;, which on most engines is a cheap catalog-only operation once nothing references the column.

#Why every step is individually cheap

The whole point of decomposing the migration is that each SQL statement, in isolation, is fast and low-lock:

• Adding a nullable column with no default is metadata-only on Postgres and MySQL 8+ (InnoDB instant DDL) — no table rewrite, near-instant regardless of table size.
• Adding a column with a default used to force a full rewrite on older Postgres; Postgres 11+ stores the default as metadata for a constant value, making it instant too — but a computed/non-constant default still rewrites, so check your engine version rather than assuming.
• The backfill is just ordinary UPDATE statements in small batches, indistinguishable from normal application write traffic to the database's lock manager — no long-held locks, no long transactions.
• Dropping a column nobody reads is catalog metadata removal, not a rewrite.

The dangerous, single-step version fails because it tries to do steps 1, 2, and 6 as one blocking operation, holding a lock for as long as the rewrite of the whole table takes, instead of a lock held only for the metadata change.

#Rollback and safety

The reason to hold at each stage rather than rushing through is that dual-writing gives you a real rollback path: if the new column turns out to have a bug, you can roll the application back to reading the old column without losing data, because the old column was never dropped and is still being kept in sync. This is the core trade the pattern makes — more deploys and more calendar time, in exchange for zero-downtime and reversibility at every step. Tools like gh-ost and pt-online-schema-change for MySQL automate a similar shadow-table-plus-triggers version of this same idea for changes (like a data type change) that can't be expressed as an additive column at all.