SQL ALTER TABLE: 7 Safe Add Column Moves That Work

You need one new column — a loyalty tier, a consent flag, a retry counter. ALTER TABLE ADD COLUMN looks like a one-liner, and on your laptop it is. Then you run it on the 900M-row orders table and writes freeze.

The gap between small and big tables is a cliff. Newer Postgres versions add defaults instantly, older MySQL rebuilds the entire table, and NOT NULL constraints force validation scans everywhere. Same statement, wildly different blast radius.

Don't fear it. You'll learn seven moves — the safe expand-migrate-contract sequence, batched backfills, and the per-dialect instant paths — that make schema changes boring again.

Syntax You'll Use Weekly (and the NOT NULL Catch)

The base form is ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(20). Nullable by default, fast nearly everywhere, safe to run even on big tables. Variants add DEFAULT 'bronze', NOT NULL, CHECK constraints, and Postgres' IF NOT EXISTS guard.

The catch: NOT NULL on a populated table demands a value for every existing row. Without DEFAULT it errors; with DEFAULT old engines materialize it into all rows (the 22-minute rebuild). New Postgres shortcuts constant defaults as metadata — but validation scans for CHECK/NOT NULL still read the table.

Default habit: write the nullable add first, always. Constraints are a second migration after backfill, not part of the first statement. Two small deploys beat one heroic ALTER.

Why Engines Differ: Instant vs Rebuild in 2 Minutes

PostgreSQL 11+ stores a constant DEFAULT as table metadata — ADD COLUMN ... DEFAULT 'bronze' completes in ~5ms on any size because existing rows read the default lazily. Nullable adds were always metadata-only. Only volatile defaults (now(), random()) or later constraint validation force real work.

MySQL 8.0 with ALGORITHM=INPLACE handles many nullable adds and some defaulted adds without rebuilds — but specify the algorithm explicitly, or the engine may silently pick COPY. MySQL 5.7 rebuilds for NOT NULL + DEFAULT every time: 900M rows copied, indexes rebuilt, writes locked.

SQL Server adds nullable columns quickly but backfills defaults row-by-row on older versions (with ONLINE options on Enterprise). Know your version's row in this table before scheduling — the engine, not the SQL, sets the blast radius.

-- Step 1 (all engines): nullable add — fast, safe
ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(20);
-- Postgres idempotent variant:
-- ALTER TABLE orders ADD COLUMN IF NOT EXISTS loyalty_tier VARCHAR(20);

-- Step 2: backfill in batches (repeat with rising id windows)
UPDATE orders SET loyalty_tier = 'bronze'
WHERE id BETWEEN 1 AND 10000 AND loyalty_tier IS NULL;

-- Step 3 (after backfill + deploy): constrain
-- Postgres instant-ish path (11+):
-- ALTER TABLE orders ALTER COLUMN loyalty_tier SET DEFAULT 'bronze';
-- ALTER TABLE orders ALTER COLUMN loyalty_tier SET NOT NULL;
-- MySQL explicit algorithm:
-- ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(20),
--   ALGORITHM=INPLACE, LOCK=NONE;

The Expand-Migrate-Contract Sequence (Zero Downtime)

Rolling deploys mean old and new code coexist. Expand: add the nullable column — both versions run fine (old ignores it, new tolerates NULL). Migrate: backfill data in batches and deploy code writing both old and new paths (dual-write). Contract: once all hosts run new code and NULLs hit zero, add NOT NULL/DEFAULT and drop the old path.

Skipping steps causes the classic breakage: requiring the column before old hosts deploy crashes them on INSERT (unknown column), and backfilling before code tolerates NULL corrupts reads.

Write the three steps as three tickets with three deploy windows. Schema changes are release trains, not single commits — each phase verified before the next departs.

Batched Backfills That Don't Lock the Table

Backfill with keyset windows, not one giant UPDATE: UPDATE orders SET loyalty_tier='bronze' WHERE id BETWEEN $a AND $b AND loyalty_tier IS NULL LIMIT 10000, committing per batch with sleeps between. Each batch holds locks briefly; progress survives interruption.

Size batches by replication lag, not just speed: watch replica lag after each batch and pause when it exceeds 5s. A backfill that outruns replicas trades a fast migration for stale reads and failover risk.

Verify continuously: SELECT count(*) WHERE loyalty_tier IS NULL trending to zero, plus spot-checks on value distribution. The last 1% (new rows inserted mid-backfill) gets caught by the DEFAULT or a final sweep before the NOT NULL step.

-- Batched backfill: 10k rows per statement, committed per batch
UPDATE orders
SET loyalty_tier = 'bronze'
WHERE loyalty_tier IS NULL
ORDER BY id
LIMIT 10000;
-- Repeat (script loop) until affected-rows = 0, sleeping 0.5s between.
-- Monitor: SHOW SLAVE STATUS (lag) / pg_stat_replication.

-- Progress check between batches
SELECT count(*) AS remaining_nulls
FROM orders WHERE loyalty_tier IS NULL;

-- Catch concurrent inserts: ensure default before constraining
ALTER TABLE orders ALTER COLUMN loyalty_tier SET DEFAULT 'bronze';

Defaults, Checks, and Types That Bite

DEFAULT 'bronze' on the ADD is instant on modern Postgres but a rebuild trigger on old MySQL — prefer SET DEFAULT as a separate step after backfill so each engine takes its fast path. Volatile defaults (DEFAULT now()) always cost more; add them last with a maintenance window.

CHECK constraints (CHECK (tier IN (...))) validate every existing row on creation — a full scan even where the add was instant. Add the column first, backfill, then ADD CONSTRAINT with NOT VALID (Postgres) followed by VALIDATE CONSTRAINT to split the scan from the lock.

Type choice matters up front: VARCHAR(20) vs TEXT vs ENUM changes future ALTER costs. Prefer right-sized types now — widening later on 900M rows is its own migration saga.

Online Tools and Lock Clauses for Big MySQL Tables

For MySQL tables over ~10M rows, raw ALTER is disallowed by policy on serious teams. pt-online-schema-change and gh-ost apply changes via triggers/ghosts with near-zero locking: reads and writes continue while the shadow table syncs, then a brief atomic swap.

When INPLACE suffices, still declare intent: ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE fails fast if the engine can't comply, instead of silently rebuilding. LOCK=SHARED (reads ok, writes blocked) is the middle ground for changes that truly need it.

Postgres users aren't exempt: set statement_timeout and lock_timeout around DDL (SET lock_timeout = '5s') so a stuck ALTER aborts instead of queueing behind a report query and blocking everything behind it.

Rollback Plan Before You Run Anything

Every ALTER ticket states three things: the reverse statement (ALTER TABLE orders DROP COLUMN loyalty_tier), the snapshot/backup ID it can restore from, and the backfill resume point if killed mid-batch. No plan, no run.

Prefer additive-only changes: a new nullable column rolls back with a DROP; a renamed or dropped column doesn't roll back at all. Renames are two changes (add new, dual-write, drop old) — never one.

Test the rollback on staging at scale, not just the rollforward. The incident's 9-minute rollback-of-copy delay was discovered during the fire; it should have been a line in the ticket.