Skip to main content
← PROBLEMS / ENGINEERING
An end-to-end domain learning journey

Zero-Downtime Online DDL
for Large Tables.

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.
Start the journey5 stages
Loading diagram…
Zero-Downtime Online DDL for Large Tables architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

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.

01
ENGINEERING · STAGE 01L4 · FUNDAMENTALS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Killing 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';
02
ENGINEERING · STAGE 02L5 · APPLIED DESIGN

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.

ChoiceWhat it buysWhat it costs
Trade-off 17.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;
03
ENGINEERING · STAGE 03L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Requires 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)
04
ENGINEERING · STAGE 04L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Higher 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);
05
ENGINEERING · STAGE 05L7 · STAFF / PRINCIPAL

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Occasional 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.
Behavioral Progression · Online Ddl Large Table Migrations

Online Ddl Large Table Migrations — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Enforce locktimeout + statementtimeout on all DDL, use CONCURRENTLY / gh-ost online schema change, and apply Expand-Contract for non-trivial column changes.

Synthesis / One connected design

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.

A candidate architecture, not a universal answer

Putting Zero-Downtime Online DDL for Large Tables Together

Step 1
Step 2
  1. Expand: ADD COLUMN amount_v2 BIGINT NULL.
  2. Backfill with replica lag check + VALIDATE CONSTRAINT.
Enable HOT updates (no index on amount_v2 until backfill finishes), set aggressive per-table autovacuum cost limits, and run periodic VACUUM.
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.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

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.