Schema changes that do not lock production tables

Schema changes on hot tables fail in two popular ways: a blocking ALTER that stalls writes, or a deploy that reads a column the database does not have yet. Online-safe migrations are mostly ordering discipline—expand, dual-write or backfill, contract—not clever one-liners.

The change

Goal: add orders.priority for a new sorting feature without multi-minute locks on a large orders table.

Why naive DDL hurts

ALTER TABLE orders
  ADD COLUMN priority VARCHAR(16) NOT NULL DEFAULT 'normal';

On large tables, depending on version and algorithm, this may rebuild the table, hold metadata locks, or spike I/O. Even “fast” defaults need verification on your MySQL/Postgres version with a replica or staging clone of production size.

Expand / contract pattern

Expand

1. Add column in a nullable form (or instant algorithm if proven):

ALTER TABLE orders ADD COLUMN priority VARCHAR(16) NULL;

1. Deploy application code that:

• reads with COALESCE(priority, 'normal')
• writes priority on new rows

1. Backfill in batches (lab sketch):

UPDATE orders
SET priority = 'normal'
WHERE priority IS NULL
  AND id > ? AND id <= ?;

Use primary key ranges, sleep between batches, watch replication lag and row lock time.

1. Validate:

SELECT COUNT(*) FROM orders WHERE priority IS NULL; -- expect 0

Contract

1. Enforce constraints when safe:

ALTER TABLE orders
  MODIFY priority VARCHAR(16) NOT NULL DEFAULT 'normal';

1. Deploy code that assumes NOT NULL.
2. Remove temporary COALESCE paths.

Details and checklist: content/labs/online-schema-changes/expand-contract.md.

Application compatibility windows

Phase	Old app	New app
Before expand	ok	must not select missing column
After expand, during backfill	ignore column	COALESCE + write
After contract	must be gone or compatible	full use

Expand/contract exists so old and new app versions can coexist during rolling deploys.

Tooling options

• Native online DDL (ALGORITHM=INPLACE/INSTANT) when supported—verify with EXPLAIN/SHOW PROCESSLIST on staging
• gh-ost / pt-online-schema-change for shadow-table approaches
• Never first-run an untested DDL on production Friday night

Regression guards

• Migration CI against a large-ish dataset
• Lag alerts during backfill
• Feature flag for code paths that depend on the new column

Summary

Step	Intent
Expand	Add capacity without requiring all rows immediately
Backfill	Move data without one giant transaction
Dual-compatible app	Survive rolling deploys
Contract	Enforce invariants and delete complexity

Online schema change is an operations protocol. The SQL is the easy part once the order of operations is written down.