Running `ALTER TABLE orders ADD COLUMN tax_jurisdiction VARCHAR(32) NOT NULL DEFAULT 'US'` or changing a column type on a 1.4 TB (1.8B-row) PostgreSQL 15 / MySQL 8 table at 4,200 writes/s caused a 47-minute total write outage. Even though PostgreSQL 11+ adds constant-default columns instantly, the `ACCESS EXCLUSIVE` lock queued behind a long-running BI query, piling up 18,000 blocked `INSERT`/`UPDATE` connections.
From patterns to decisions.
Running `ALTER TABLE orders ADD COLUMN tax_jurisdiction VARCHAR(32) NOT NULL DEFAULT 'US'` or changing a column type on a 1.4 TB (1.8B-row) PostgreSQL 15 / MySQL 8 table at 4,200 writes/s caused a 47-minute total write outage. Even though PostgreSQL 11+ adds constant-default columns instantly, the `ACCESS EXCLUSIVE` lock queued behind a long-running BI query, piling up 18,000 blocked `INSERT`/`UPDATE` connections.
The ALTER TABLE requested ACCESS.
The ALTER TABLE requested ACCESS EXCLUSIVE and queued behind a 12-minute SELECT, blocking all subsequent reads and writes in FIFO order.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Killing the blocker query aborts a BI report vs preserving 4,200 TPS OLTP. |
Step 1
Query pg_locks and pg_stat_activity for waiting ACCESS EXCLUSIVE locks.
Step 2
Identify the head-of-line blocker query.
Alternative 1
Route all BI queries to a dedicated read replica.
Failure Mode 1
PgBouncer connection pool exhaustion in <2 seconds.
SELECT pid, wait_event_type, wait_event, state, left(query, 80) FROM pg_stat_activity WHERE wait_event = 'Lock';
Add nullable amount_v2 with lock_timeout=1.
Add nullable amount_v2 with lock_timeout=1.5s, dual-write in app, backfill in 5,000-row batches throttled by replica lag <1.5s, and validate NOT NULL online.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | 7.5 hours backfill time vs zero write downtime. |
Expand
ADD COLUMN amount_v2 BIGINT NULL.
Step 2
Backfill with replica lag check + VALIDATE CONSTRAINT.
Alternative 1
pg_repack or MySQL gh-ost for shadow table cutover.
Failure Mode 1
Forgetting dual-write before backfill leaves newly inserted rows NULL.
SET lock_timeout = '1500ms';
ALTER TABLE orders ADD COLUMN amount_v2 BIGINT NULL;
ALTER TABLE orders ADD CONSTRAINT chk_v2_nn CHECK (amount_v2 IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_v2_nn;
SET NOT NULL scans the.
SET NOT NULL scans the entire 1.4 TB table under ACCESS EXCLUSIVE lock, whereas VALIDATE CONSTRAINT uses SHARE UPDATE EXCLUSIVE and allows concurrent writes.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Requires two DDL steps instead of one. |
Step 1
Compare lock levels in PostgreSQL documentation.
Step 2
Verify PostgreSQL 12+ can use a validated CHECK (col IS NOT NULL) to set NOT NULL without rescanning.
Alternative 1
Use pg_repack to rebuild table with NOT NULL.
Failure Mode 1
Running SET NOT NULL without the validated CHECK constraint triggers a multi-hour lock.
ALTER TABLE orders VALIDATE CONSTRAINT chk_v2_nn; -- SHARE UPDATE EXCLUSIVE (writes allowed)
Enable HOT updates (no index.
Enable HOT updates (no index on amount_v2 until backfill finishes), set aggressive per-table autovacuum cost limits, and run periodic VACUUM.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Higher background I/O during backfill. |
Step 1
Defer CREATE INDEX CONCURRENTLY until after backfill completes to enable Heap-Only Tuple (HOT) updates.
Step 2
Lower autovacuum_vacuum_scale_factor to 0.02 on orders.
Alternative 1
Run pg_repack after migration.
Failure Mode 1
Indexing amount_v2 before backfill disables HOT updates and doubles WAL + bloat.
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000);
Use a SQL linter (squawk.
Use a SQL linter (squawk / strong_migrations) in CI/CD and inject SET lock_timeout = '1500ms' at the migration runner session level.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Occasional CI false positives require explicit bypass annotation. |
Step 1
Add Squawk CLI to GitHub Actions to reject missing CONCURRENTLY or bare NOT NULL.
Step 2
Configure migration runner connection initSQL with lock_timeout.
Alternative 1
Database-level DDL event triggers.
Failure Mode 1
Manual psql hotfixes bypassing CI.
spring.datasource.hikari.connection-init-sql: "SET lock_timeout = '1500ms';"
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Enforce lock_timeout + statement_timeout on all DDL, use CONCURRENTLY / gh-ost online schema change, and apply Expand-Contract for non-trivial column changes.
- 02Trade-off tip: Requires two DDL steps instead of one.
- 03Watch out for: Indexing amount_v2 before backfill disables HOT updates and doubles WAL + bloat.
Online Ddl Large Table Migrations — Normal → Load / Fault → Recovery
Enforce locktimeout + statementtimeout on all DDL, use CONCURRENTLY / gh-ost online schema change, and apply Expand-Contract for non-trivial column changes.
Putting Zero-Downtime Online DDL for Large Tables Together
Enforce lock_timeout + statement_timeout on all DDL, use CONCURRENTLY / gh-ost online schema change, and apply Expand-Contract for non-trivial column changes.
Putting Zero-Downtime Online DDL for Large Tables Together
- Expand: ADD COLUMN amount_v2 BIGINT NULL.
- Backfill with replica lag check + VALIDATE CONSTRAINT.
Interview Readiness Check
Enable HOT updates (no index on amount_v2 until backfill finishes), set aggressive per-table autovacuum cost limits, and run periodic VACUUM.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Zero-Downtime Online DDL for Large Tables
Enforce lock_timeout + statement_timeout on all DDL, use CONCURRENTLY / gh-ost online schema change, and apply Expand-Contract for non-trivial column changes.
