Zero-downtime schema changes: adding columns and tables safely
zero-downtime migration, expand and contract pattern, non-blocking ALTER TABLE, adding nullable columns, backfilling data, removing old columns, gh-ost tool, pt-online-schema-change
Why ALTER TABLE Is Dangerous
On large tables, ALTER TABLE can lock the table for minutes or hours while it rewrites every row. On a live production system, this means downtime. The expand and contract pattern avoids this.
The Pattern
Step 1 — Expand: Add the new column as nullable. No backfill yet. Deploy this immediately — it takes milliseconds.
-- Step 1: safe, instant
ALTER TABLE orders ADD COLUMN shipping_address_id INT NULL;Step 2 — Backfill: Fill in values for existing rows in small batches, off-peak.
-- Step 2: batch backfill
UPDATE orders SET shipping_address_id = default_address_id
WHERE shipping_address_id IS NULL LIMIT 10000;Step 3 — Contract: Add NOT NULL constraint and drop the old column after all rows are backfilled and the old code path is removed.
-- Step 3: after backfill is 100% complete
ALTER TABLE orders MODIFY COLUMN shipping_address_id INT NOT NULL;Online Schema Change Tools
gh-ost (GitHub) and pt-online-schema-change (Percona) perform MySQL schema changes by creating a shadow table, copying rows, and doing a near-atomic swap — no table lock on production.