Zero-Downtime Schema Changes: Expand/Contract, Backfills & Online DDL

zero-downtime schema changes are the difference between shipping a NOT NULL column on a Friday afternoon and a 40-minute outage that pages the whole on-call rotation — and they are the single operation that separates engineers who have actually run a migration against a busy multi-billion-row table from those who have only ever ALTER-ed a laptop database with ten rows in it. Every column you add, every type you widen, every constraint you tighten, every column you rename or drop has to reach production while the application keeps reading and writing at full throughput, while a rolling deploy is swapping old pods for new ones, and without a single query queuing behind a lock long enough to trip a health check and cascade into a full outage. The hard part is never "write the ALTER" — the hard part is sequencing the schema change, the data migration, and the code deploy so that at no instant does the running system see a schema it cannot cope with.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "how would you add a NOT NULL column to a 500-million-row table without downtime," or "walk me through renaming a column that the application reads on every request," or "your migration is blocked in the lock queue and every query is now hanging — what happened and how do you prevent it." It walks through the whole discipline: why a naive ALTER TABLE takes production down, the expand contract pattern (also called parallel change) that turns every risky migration into a sequence of additive changes, the chunked backfill loop with dual writes that keeps old and new columns in sync, the online ddl toolkit — Postgres CREATE INDEX CONCURRENTLY / NOT VALID + VALIDATE / pg_repack, MySQL gh-ost and pt-online-schema-change, plus safe-migration linters — and how to orchestrate all of it against a rolling deploy with feature flags so that rename and drop happen safely. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the design practice library →, rehearse on the database practice library →, and sharpen the pipeline axis with the ETL practice library →.

---

On this page

• Why a naive ALTER TABLE takes production down
• The expand/contract parallel-change pattern
• Backfills — chunked, dual-written, and in sync
• Online DDL tooling — Postgres, MySQL, linters
• Orchestrating schema changes with app deploys
• Cheat sheet — zero-downtime migration recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why a naive ALTER TABLE takes production down

A single ALTER TABLE grabs an ACCESS EXCLUSIVE lock — and the lock queue behind it is what actually causes the outage

The one-sentence invariant: a zero-downtime schema changes discipline exists because most ALTER TABLE statements take an ACCESS EXCLUSIVE lock (Postgres) or a table-metadata / rebuild lock (MySQL) that conflicts with every concurrent read and write, some of them also rewrite the entire table under that lock in time proportional to table size, and — worst of all — a DDL that is blocked waiting for the lock will itself block every query that arrives after it, so a "one-second" migration can freeze the entire table for as long as your slowest in-flight query runs. The failure is rarely the DDL you wrote; it is the interaction between the DDL, the locks it needs, and the traffic already running against the table.

What ALTER TABLE actually locks.

• ACCESS EXCLUSIVE is the strongest lock. In Postgres, most forms of ALTER TABLE acquire ACCESS EXCLUSIVE, which conflicts with every other lock mode including the ACCESS SHARE that a plain SELECT takes. While it is held, no session can read or write the table — they all block.
• Some ALTERs rewrite the whole table. Changing a column type (ALTER COLUMN ... TYPE), adding a column with a volatile default (pre-Postgres 11), or SET LOGGED/SET UNLOGGED rewrite every row into a new file. The ACCESS EXCLUSIVE lock is held for the entire rewrite — minutes to hours on a large table.
• Some ALTERs scan the whole table. ALTER TABLE ... ADD CONSTRAINT ... CHECK, SET NOT NULL, and ADD FOREIGN KEY (validating) do not rewrite but must scan every row to verify the constraint, again under a strong lock.
• Some ALTERs are metadata-only. Postgres 11+ can ADD COLUMN ... DEFAULT <constant> instantly (the default is stored in catalog, not written to rows). Dropping a column, renaming a column, and adding a nullable column with no default are metadata-only. These are fast but still take ACCESS EXCLUSIVE for a brief instant — and that instant is enough to trigger the lock-queue pileup below.

The three ways a naive ALTER causes downtime.

• The rewrite/scan holds the lock too long. A SET NOT NULL on a 500M-row table scans every row while holding ACCESS EXCLUSIVE; the table is unavailable for the whole scan. This is the obvious failure.
• The lock-queue pileup — the non-obvious killer. Even a metadata-only DDL must wait for the ACCESS EXCLUSIVE lock. If a long-running SELECT (an analytics query, a pg_dump, a forgotten transaction) holds ACCESS SHARE, the DDL queues behind it. Crucially, once the DDL is queued, Postgres queues every new query behind the DDL — so a five-minute analytics query plus a "fast" ALTER freezes the table for five minutes for all traffic.
• Schema + deploy coupling. If you run the migration at the same instant you deploy new code, the old pods still running expect the old schema and the new pods expect the new schema; whichever the DB does not match starts throwing errors. The migration and the deploy must be decoupled in time.

The safe-migration mindset — the four rules that prevent all three.

• Prefer additive changes. Add a nullable column; add a new table; add an index concurrently. Additive changes are backward compatible — old code ignores what it does not know about.
• Never hold a strong lock for O(rows). Split rewrites and scans into a fast metadata step plus a background backfill or a concurrent build (sections 3 and 4).
• Always set lock_timeout. A migration that cannot get its lock within a few seconds should fail fast and retry, never wait behind a long query and pile up the queue.
• Decouple schema from code. Ship the schema change and the code that uses it as separate, ordered deploys (section 5).

Worked example — reproducing the lock-queue pileup

Detailed explanation. The scariest migration failure is not the slow rewrite — engineers expect that — it is the "instant" DDL that freezes a table because it queued behind a long read and then blocked everyone else. Reproducing it once, in a sandbox, permanently changes how you write migrations. Walk through the three-session reproduction on Postgres.

• Session A. Opens a transaction and runs a slow SELECT (simulating analytics), holding ACCESS SHARE.
• Session B. Runs an "instant" ALTER TABLE ... ADD COLUMN, which needs ACCESS EXCLUSIVE and queues behind Session A.
• Session C. Runs a normal SELECT — and blocks behind Session B, even though it only wants ACCESS SHARE that does not conflict with Session A.

Question. Demonstrate that a fast DDL, queued behind a long read, blocks all subsequent reads — and show the pg_locks view that proves it.

Input.

Session	Statement	Lock wanted	Outcome
A	BEGIN; SELECT pg_sleep(300) FROM orders LIMIT 1;	ACCESS SHARE	granted
B	ALTER TABLE orders ADD COLUMN note text;	ACCESS EXCLUSIVE	waits behind A
C	SELECT count(*) FROM orders;	ACCESS SHARE	waits behind B

Code.

-- Session A — a long read that holds ACCESS SHARE for 5 minutes
BEGIN;
SELECT id FROM public.orders ORDER BY id LIMIT 1;
SELECT pg_sleep(300);   -- simulate a slow analytics scan / forgotten txn
-- (do NOT commit yet)

-- Session B — a "metadata only" DDL that still needs ACCESS EXCLUSIVE
ALTER TABLE public.orders ADD COLUMN note text;   -- hangs, waiting for the lock

-- Session C — an ordinary query that should be unrelated to Session A
SELECT count(*) FROM public.orders;               -- ALSO hangs, stuck behind B

-- In a fourth session, prove what is happening:
SELECT a.pid,
       a.state,
       a.wait_event_type,
       l.mode,
       l.granted,
       left(a.query, 40) AS query
FROM   pg_stat_activity a
JOIN   pg_locks l ON l.pid = a.pid
WHERE  l.relation = 'public.orders'::regclass
ORDER  BY l.granted DESC, a.pid;

Step-by-step explanation.

1. Session A takes ACCESS SHARE on orders and holds it for the length of its transaction. ACCESS SHARE does not conflict with other ACCESS SHARE locks, so on its own it never blocks other readers.
2. Session B's ALTER TABLE needs ACCESS EXCLUSIVE, which does conflict with Session A's ACCESS SHARE. B cannot proceed, so it enters the lock queue and waits.
3. Session C wants only ACCESS SHARE — which is perfectly compatible with Session A's ACCESS SHARE. In isolation C would run instantly. But Postgres grants locks roughly in request order, and C arrived after B's ACCESS EXCLUSIVE request. To avoid starving the DDL, C is made to wait behind B.
4. The net effect: one slow read (A) plus one instant DDL (B) freezes the table for every subsequent query (C, D, E …) for the full duration of A. Your monitoring shows the table "hung" even though no statement is doing heavy work.
5. The pg_locks join shows the truth: A holds ACCESS SHARE (granted = true), B waits for ACCESS EXCLUSIVE (granted = false), and C waits for ACCESS SHARE (granted = false) — the smoking gun of a lock-queue pileup.

Output.

pid	mode	granted	Meaning
A	AccessShareLock	true	long read, holding the table
B	AccessExclusiveLock	false	DDL queued behind A
C	AccessShareLock	false	ordinary read queued behind B

Rule of thumb. A migration is only as safe as the longest transaction that can be running when it fires. Never run a DDL without a short lock_timeout, and never assume "instant" DDL is safe on a table that also serves long reads.

Worked example — which ALTERs rewrite the table and which are metadata-only

Detailed explanation. The single most useful thing to memorise is the boundary between metadata-only DDL (fast, safe if you set lock_timeout) and rewriting/scanning DDL (dangerous without expand/contract). The empirical test on Postgres is to watch the table's relfilenode — a change means the whole table was rewritten into a new file. Walk through probing several operations.

• Metadata-only. ADD COLUMN nullable / with constant default (PG11+), DROP COLUMN, RENAME COLUMN, DROP CONSTRAINT. relfilenode unchanged.
• Full rewrite. ALTER COLUMN ... TYPE (most cases), ADD COLUMN ... DEFAULT <volatile> (pre-11), SET LOGGED/UNLOGGED, ADD PRIMARY KEY (builds index + may rewrite). relfilenode changes.
• Full scan (no rewrite, still strong lock). SET NOT NULL, ADD CONSTRAINT ... CHECK, validating ADD FOREIGN KEY.

Question. Determine, for a set of common ALTERs, which rewrite the table, using relfilenode as the oracle.

Input.

Operation	Expectation
ADD COLUMN c text	metadata-only (no rewrite)
ADD COLUMN c int DEFAULT 0 (PG11+)	metadata-only (no rewrite)
ALTER COLUMN amount TYPE bigint	full rewrite
SET NOT NULL	full scan, strong lock

Code.

-- Helper: capture the on-disk file id before and after each ALTER.
-- A changed relfilenode == the whole table was rewritten.
SELECT relfilenode FROM pg_class WHERE relname = 'orders';   -- e.g. 165432

-- 1. Add a nullable column — metadata only in every modern Postgres
ALTER TABLE public.orders ADD COLUMN note text;
SELECT relfilenode FROM pg_class WHERE relname = 'orders';   -- 165432 (unchanged)

-- 2. Add a column with a CONSTANT default — metadata only on PG 11+
ALTER TABLE public.orders ADD COLUMN retries int NOT NULL DEFAULT 0;
SELECT relfilenode FROM pg_class WHERE relname = 'orders';   -- 165432 (unchanged)

-- 3. Change a column type — FULL REWRITE, ACCESS EXCLUSIVE for O(rows)
ALTER TABLE public.orders ALTER COLUMN amount TYPE bigint;
SELECT relfilenode FROM pg_class WHERE relname = 'orders';   -- 165999 (CHANGED!)

-- 4. Add a column with a VOLATILE default — rewrite even on PG 11+
ALTER TABLE public.orders ADD COLUMN token uuid DEFAULT gen_random_uuid();
SELECT relfilenode FROM pg_class WHERE relname = 'orders';   -- CHANGED (volatile default)

Step-by-step explanation.

1. Operation 1 adds a nullable column. Postgres records the new column in the catalog and touches no rows; relfilenode is unchanged, and the ACCESS EXCLUSIVE lock is held for microseconds. Safe with a lock_timeout.
2. Operation 2 adds a NOT NULL column with a constant default. Since Postgres 11 the default is stored once in pg_attribute and materialised lazily on read, so no rows are written — relfilenode stays the same. On Postgres 10 and older this same statement rewrites the table.
3. Operation 3 changes the column type. Every row must be rewritten to the new representation, so relfilenode changes and the lock is held for the whole rewrite. This is the operation you must convert into an expand/contract sequence (section 2).
4. Operation 4 uses a volatile default (gen_random_uuid()), which cannot be materialised lazily because each row needs a different value — so even on modern Postgres it rewrites. The lesson: "constant default = safe, volatile default = rewrite."
5. The general rule falls out: watch relfilenode. If a proposed migration changes it on a small copy of the table, it will rewrite the production table under a long lock — refactor it before shipping.

Output.

Operation	relfilenode	Verdict
ADD COLUMN nullable	unchanged	safe (metadata-only)
ADD COLUMN NOT NULL DEFAULT 0 (PG11+)	unchanged	safe (metadata-only)
ALTER COLUMN TYPE	changed	dangerous (full rewrite)
ADD COLUMN DEFAULT gen_random_uuid()	changed	dangerous (volatile default)

Rule of thumb. Before every migration, ask "does this rewrite or scan the table?" Test it on a copy and watch relfilenode and EXPLAIN. Metadata-only ALTERs are safe behind a lock_timeout; rewrites and scans must become expand/contract plus a background backfill.

Worked example — a lock_timeout + retry wrapper that refuses to pile up the queue

Detailed explanation. The one-line defense against the lock-queue pileup is lock_timeout: tell the DDL to give up if it cannot get its lock within a couple of seconds, then retry with backoff. A migration that fails fast never blocks the queue. Walk through the wrapper every migration should use.

• lock_timeout. Caps how long a statement waits for a lock (distinct from statement_timeout, which caps total run time). Set it low (2–5s) for DDL.
• Retry with backoff. If the DDL times out, wait and retry — eventually the long read finishes and the lock is free.
• Short transactions. Each attempt is its own transaction so a failed attempt releases immediately.

Question. Wrap an ADD COLUMN migration so it never waits more than 3 seconds for its lock and retries up to five times.

Input.

Parameter	Value
DDL	ALTER TABLE orders ADD COLUMN note text
lock_timeout	3 s
Max attempts	5
Backoff	5 s, doubling

Code.

# safe_ddl.py — run a DDL with lock_timeout and bounded retries
import time
import psycopg2
from psycopg2 import errors as pg_errors

def run_ddl_safely(dsn: str, ddl: str, *, lock_timeout_s: int = 3,
                   max_attempts: int = 5, backoff_s: float = 5.0) -> None:
    attempt = 0
    while True:
        attempt += 1
        conn = psycopg2.connect(dsn)
        try:
            conn.autocommit = False
            with conn.cursor() as cur:
                # Cap ONLY the lock wait; do not wait behind a long read.
                cur.execute("SET LOCAL lock_timeout = %s", (f"{lock_timeout_s}s",))
                cur.execute(ddl)
            conn.commit()
            print(f"DDL succeeded on attempt {attempt}")
            return
        except pg_errors.LockNotAvailable:
            conn.rollback()
            if attempt >= max_attempts:
                raise
            wait = backoff_s * (2 ** (attempt - 1))
            print(f"attempt {attempt}: lock not available; retrying in {wait:.0f}s")
            time.sleep(wait)
        finally:
            conn.close()

if __name__ == "__main__":
    run_ddl_safely(
        "host=db-primary dbname=production user=migrator",
        "ALTER TABLE public.orders ADD COLUMN note text",
    )

Step-by-step explanation.

1. SET LOCAL lock_timeout = '3s' limits how long this transaction's statements wait to acquire a lock. If the ALTER cannot get ACCESS EXCLUSIVE within 3 seconds, Postgres raises LockNotAvailable instead of queuing indefinitely.
2. Because the attempt is a short transaction, a timeout releases immediately — the DDL never sits in the queue blocking Session C-style ordinary reads. This is the entire point: fail fast, do not pile up.
3. On LockNotAvailable the wrapper rolls back and sleeps with exponential backoff (5s, 10s, 20s …). Each retry re-checks whether the blocking long read has finished.
4. After max_attempts the wrapper re-raises, surfacing the failure to the operator instead of silently hanging. That is the right behavior — a migration that cannot get a lock for 5 escalating attempts signals an unexpectedly busy table that a human should look at.
5. Every production migration tool worth using (Postgres migration frameworks, Rails strong_migrations, etc.) does exactly this internally. The pattern is universal: lock_timeout + retry + short transactions.

Output.

Attempt	lock_timeout	Result	Action
1	3 s	LockNotAvailable	sleep 5 s
2	3 s	LockNotAvailable	sleep 10 s
3	3 s	success (long read finished)	commit

Rule of thumb. Never run a DDL without lock_timeout. A 2–5 second lock_timeout plus retry-with-backoff converts "instant DDL that froze the whole table" into "migration that harmlessly retried until the lock was free."

Senior interview question on lock-safe DDL

A senior interviewer often opens with: "You need to add a NOT NULL column with a default to a 500-million-row orders table on Postgres 12, and the table serves 8,000 writes per second around the clock — there is no maintenance window. The junior on your team wrote ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'US' and it locked the table for 20 minutes in staging. Walk me through the safe version, the locks involved, and the guardrails you'd add."

Solution Using an additive nullable column, a backfill, and a NOT VALID constraint promoted to NOT NULL

-- Step 0 — always cap the lock wait for every DDL step
SET lock_timeout = '3s';

-- Step 1 — add the column as NULLABLE with a CONSTANT default.
--   On PG 11+ this is metadata-only: the default is stored in the catalog,
--   existing rows are NOT rewritten, the lock is held for microseconds.
ALTER TABLE public.orders ADD COLUMN region text DEFAULT 'US';

-- Step 2 — backfill existing rows in small batches (see section 3),
--   so no single statement locks or bloats the table. New rows already
--   get 'US' from the default, so we only touch historical NULLs.
--   (batched loop runs out-of-band; shown fully in section 3)

-- Step 3 — add a CHECK constraint as NOT VALID (instant: no full scan).
ALTER TABLE public.orders
  ADD CONSTRAINT orders_region_not_null CHECK (region IS NOT NULL) NOT VALID;

-- Step 4 — VALIDATE the constraint. This scans the table but takes only a
--   SHARE UPDATE EXCLUSIVE lock, which does NOT block reads or writes.
ALTER TABLE public.orders VALIDATE CONSTRAINT orders_region_not_null;

-- Step 5 — (PG 12+) promote to a real NOT NULL cheaply. Postgres uses the
--   already-validated CHECK constraint to skip the full scan.
ALTER TABLE public.orders ALTER COLUMN region SET NOT NULL;

-- Step 6 — drop the now-redundant CHECK constraint.
ALTER TABLE public.orders DROP CONSTRAINT orders_region_not_null;

Step-by-step trace.

Step	Lock taken	Duration	Blocks traffic?
1. ADD COLUMN nullable + const default	ACCESS EXCLUSIVE	microseconds	no (metadata-only)
2. Batched backfill	ROW EXCLUSIVE (per batch)	seconds/batch	no (short batches)
3. ADD CHECK ... NOT VALID	ACCESS EXCLUSIVE	microseconds	no (no scan)
4. VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	O(rows), background	no (reads + writes proceed)
5. SET NOT NULL (uses validated CHECK)	ACCESS EXCLUSIVE	microseconds	no (scan skipped, PG12+)
6. DROP CONSTRAINT	ACCESS EXCLUSIVE	microseconds	no

After the sequence, orders.region is NOT NULL with every existing row backfilled and every new row defaulting to 'US' — and at no step did any statement hold a table-blocking lock for longer than microseconds except the fully-concurrent VALIDATE scan. The naive one-liner's 20-minute ACCESS EXCLUSIVE rewrite-and-scan is gone.

Output:

Metric	Naive one-liner	Safe sequence
Longest blocking lock	~20 min (rewrite + scan)	microseconds
Reads blocked during migration	all, for 20 min	none
Writes blocked during migration	all, for 20 min	none
Rollback safety	all-or-nothing 20-min txn	each step independent
Replication lag risk	one huge WAL burst	smoothed by batches

Why this works — concept by concept:

• Constant default is metadata-only — since Postgres 11, ADD COLUMN ... DEFAULT <constant> records the default in the catalog and materialises it lazily on read, so existing rows are never rewritten. The ACCESS EXCLUSIVE lock is held only for the catalog update.
• NOT VALID skips the scan — ADD CONSTRAINT ... NOT VALID adds the constraint to the catalog without checking existing rows, so it takes a strong lock for only an instant. New writes are checked immediately; old rows are trusted until VALIDATE.
• VALIDATE takes a weak lock — VALIDATE CONSTRAINT scans the table under SHARE UPDATE EXCLUSIVE, which conflicts with other DDL but not with SELECT, INSERT, UPDATE, or DELETE. The full-table verification runs in the background without an outage.
• SET NOT NULL reuses the constraint — on Postgres 12+, SET NOT NULL sees an already-validated CHECK (col IS NOT NULL) and skips its own full scan, so the promotion is instant.
• Cost — six short DDLs (each microseconds under ACCESS EXCLUSIVE) plus one background VALIDATE scan (O(rows), non-blocking) plus a batched backfill (O(rows), throttled). Versus the naive one-liner's single O(rows) ACCESS EXCLUSIVE rewrite-and-scan that blocks all traffic. Net: same end state, zero downtime.

SQL
Topic — database
Database problems on locks, DDL, and constraints

Practice →

Design Topic — design Design problems on zero-downtime migrations

Practice →

---

2. The expand/contract parallel-change pattern

Ship every risky migration as a backward-compatible sequence: expand the schema, migrate the data and reads, then contract the old shape away

The mental model in one line: the expand contract pattern (also called parallel change) turns any breaking schema migration into three phases — expand (add the new schema additively, so old and new code both work), migrate (dual-write to both shapes, backfill history, then switch reads to the new shape), and contract (remove the now-unused old shape) — where every individual deploy is backward compatible with the version before it, so a rolling deploy never sees a schema it cannot cope with. You cannot rename, retype, or move a column that live code depends on in one step; you can do it as a sequence of additive steps, each safe on its own.

The three phases.

• Expand. Add the new column / table / index additively. Nothing is removed, so code that knows only the old schema keeps working. The database now supports both the old and new shape simultaneously — hence "parallel change" and "blue-green schema."
• Migrate. Two jobs run in this phase: (1) dual writes — application code (or a trigger) writes every new row to both the old and new column; (2) a backfill copies historical rows into the new column. When both are done, every row is populated in the new shape, and you flip reads over to the new column.
• Contract. Once no code reads or writes the old shape, drop it. This is a separate, later deploy — never bundled with the expand.

Why every step must be backward compatible.

• Rolling deploys run old + new code at once. During a deploy, some pods run version N and some run version N+1 for minutes. Both must work against whatever schema is live. So the schema must be compatible with the union of the two code versions.
• The N-1 rule. Each deploy is only allowed to depend on the schema shape that the previous deploy already guaranteed. You never make code and schema change in lockstep; the schema always leads (for expands) or trails (for contracts) the code.
• Rollback safety. Because each step is additive and backward compatible, you can roll back one deploy without a schema change. A dropped column, by contrast, cannot be un-dropped without data loss — which is exactly why contract is last and slow.

The canonical expand/contract operations.

• Rename a column. Add new column → dual-write → backfill → switch reads → stop writing old → drop old.
• Change a column type. Add a new correctly-typed column → dual-write with cast → backfill → switch reads → drop old (identical shape to a rename).
• Split or merge columns. name → first_name + last_name: add both, dual-write the split, backfill, switch reads, drop name.
• Move a column to a new table. Add the new table → dual-write → backfill → switch reads to a join → drop the old column.
• Add a NOT NULL column. Add nullable → backfill → validate → promote (the section-1 sequence is a degenerate expand/contract).

Worked example — renaming a column with zero downtime

Detailed explanation. The textbook expand/contract case is renaming a column the application reads on every request — say users.email → users.email_address. A raw ALTER TABLE ... RENAME COLUMN is metadata-only and fast, but it is instantly breaking: the moment it runs, every old pod still issuing SELECT email errors, and every new pod issuing SELECT email_address errored a moment before. The fix is to run both names in parallel. Walk through the full sequence.

• Expand. Add email_address as a nullable column (metadata-only).
• Migrate. Dual-write both columns from the app; backfill email_address = email for old rows; switch reads to email_address; stop writing email.
• Contract. Drop email.

Question. Rename users.email to users.email_address with no failed queries at any point in a rolling deploy.

Input.

Phase	DDL / code change	Compatible with
Expand	ADD COLUMN email_address text	old code (ignores it)
Migrate-write	app writes both columns	old + new readers
Migrate-backfill	email_address := email for NULLs	both
Migrate-read	app reads email_address	still writes both
Contract	DROP COLUMN email	only new code left

Code.

-- EXPAND (deploy 1, schema): add the new column, nullable, no rewrite
SET lock_timeout = '3s';
ALTER TABLE public.users ADD COLUMN email_address text;

-- MIGRATE-backfill (out-of-band, batched — see section 3):
--   copy history into the new column, only where it is still empty.
UPDATE public.users
SET    email_address = email
WHERE  email_address IS NULL
  AND  id BETWEEN :lo AND :hi;     -- run in PK-range batches

-- CONTRACT (deploy 4, schema, days later): remove the old column
SET lock_timeout = '3s';
ALTER TABLE public.users DROP COLUMN email;

# MIGRATE-write (deploy 2, code): write BOTH columns on every mutation.
def upsert_user(conn, user_id: int, email: str) -> None:
    with conn, conn.cursor() as cur:
        cur.execute("""
            INSERT INTO public.users (id, email, email_address)
            VALUES (%s, %s, %s)
            ON CONFLICT (id) DO UPDATE
              SET email         = EXCLUDED.email,
                  email_address = EXCLUDED.email_address
        """, (user_id, email, email))   # same value into both

# MIGRATE-read (deploy 3, code): now READ the new column, still WRITE both.
def get_user_email(conn, user_id: int) -> str:
    with conn.cursor() as cur:
        cur.execute("SELECT email_address FROM public.users WHERE id = %s", (user_id,))
        return cur.fetchone()[0]

# After deploy 3 is fully rolled out, a later deploy stops writing `email`,
# and only THEN does the CONTRACT DROP COLUMN run.

Step-by-step explanation.

1. Expand adds email_address as a nullable column — metadata-only, microsecond lock. Old pods that only know email are unaffected because the new column is optional. The schema now supports both names.
2. Migrate-write (deploy 2) ships code that writes the same value to both email and email_address on every insert/update. From this deploy onward, no new or updated row is stale in the new column. Old readers still read email; new column is being populated in parallel.
3. Migrate-backfill copies email into email_address for all historical rows, in PK-range batches, only where email_address IS NULL (so it is idempotent and does not fight the dual-writes). After it finishes, every row has email_address populated.
4. Migrate-read (deploy 3) flips reads to email_address. This is safe only because steps 2 and 3 guaranteed the column is fully populated. The app still writes both columns, so a rollback to deploy 2 is harmless.
5. Contract (deploy 4, only after a later deploy stops writing email) drops the old column. By now nothing reads or writes email, so the drop breaks nothing. The rename is complete with zero failed queries across four decoupled deploys.

Output.

Timeline	email	email_address	Failed queries
After expand	read+write	exists, empty	0
After dual-write deploy	read+write	written for new rows	0
After backfill	read+write	fully populated	0
After read-switch deploy	write-only	read+write	0
After contract	dropped	read+write	0

Rule of thumb. You never rename a live column in one step. Add the new name, dual-write, backfill, switch reads, then drop the old name — five safe steps beat one breaking RENAME.

Worked example — widening id from int to bigint before overflow

Detailed explanation. A four-byte int primary key overflows at ~2.1 billion rows; the day it does, every insert fails. You must widen it to bigint before that, and a direct ALTER COLUMN id TYPE bigint rewrites the whole table (and every index and foreign key) under ACCESS EXCLUSIVE — potentially hours of downtime on a hot table. Expand/contract solves it with a shadow column plus a sync trigger. Walk through it.

• Expand. Add id_big bigint, a sync trigger to keep it equal to id on writes, and (concurrently) a unique index on id_big.
• Migrate. Backfill id_big = id for history; verify counts match.
• Contract. In a brief maintenance step, swap the primary key to id_big (or rename columns) — the only short-lock moment, planned and fast.

Question. Widen orders.id to bigint without a multi-hour table rewrite.

Input.

Component	Purpose
id_big bigint	the wide shadow column
orders_sync_id_big()	trigger keeping id_big = id on write
idx_orders_id_big	unique index built CONCURRENTLY
batched backfill	fills id_big for historical rows

Code.

-- EXPAND: shadow column + keep-in-sync trigger
SET lock_timeout = '3s';
ALTER TABLE public.orders ADD COLUMN id_big bigint;   -- metadata-only

CREATE OR REPLACE FUNCTION public.orders_sync_id_big() RETURNS TRIGGER AS $$
BEGIN
    NEW.id_big := NEW.id;   -- every new/updated row gets the wide value
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_sync_id_big
BEFORE INSERT OR UPDATE ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.orders_sync_id_big();

-- Build the unique index WITHOUT a long lock (section 4)
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_id_big ON public.orders (id_big);

-- MIGRATE: backfill history in batches (only untouched rows)
UPDATE public.orders SET id_big = id
WHERE  id_big IS NULL AND id BETWEEN :lo AND :hi;   -- looped by PK range

-- Verify before contracting
SELECT count(*) AS unfilled FROM public.orders WHERE id_big IS NULL;  -- expect 0

-- CONTRACT: the one short, planned lock — swap the primary key.
-- Held only for microseconds because the unique index already exists.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE public.orders DROP CONSTRAINT orders_pkey;
ALTER TABLE public.orders ADD  CONSTRAINT orders_pkey
      PRIMARY KEY USING INDEX idx_orders_id_big;   -- reuse the prebuilt index
ALTER TABLE public.orders DROP COLUMN id;
ALTER TABLE public.orders RENAME COLUMN id_big TO id;
DROP TRIGGER trg_orders_sync_id_big ON public.orders;
DROP FUNCTION public.orders_sync_id_big();
COMMIT;

Step-by-step explanation.

1. Expand adds id_big bigint (metadata-only) and a BEFORE INSERT OR UPDATE trigger that copies id into id_big for every new or modified row. From now on, no fresh row is stale in the wide column — the dual-write is done by the trigger, not the app.
2. CREATE UNIQUE INDEX CONCURRENTLY builds the future primary-key index without an ACCESS EXCLUSIVE lock, so reads and writes continue during the (potentially long) index build. This is what makes the eventual PK swap microsecond-fast.
3. Migrate-backfill fills id_big for historical rows in PK-range batches, only where id_big IS NULL, so it never conflicts with the trigger's live writes. The verification query confirms zero unfilled rows before proceeding.
4. Contract is the only moment with a planned lock, and it is deliberately trivial: ADD CONSTRAINT ... PRIMARY KEY USING INDEX reuses the already-built unique index instead of building a new one, so the swap holds ACCESS EXCLUSIVE for microseconds. Dropping id, renaming id_big to id, and removing the trigger complete the widening.
5. The result: orders.id is now bigint with zero multi-hour rewrite. The expensive work (index build, backfill) happened concurrently; only the final catalog swap took a lock, and it took it for microseconds.

Output.

Approach	Longest lock	Downtime
ALTER COLUMN id TYPE bigint	hours (rewrite + all indexes)	hours
Expand/contract + USING INDEX	microseconds (PK swap)	none

Rule of thumb. Widen a primary key with a shadow column, a sync trigger, and a CONCURRENTLY-built index — then swap the constraint with USING INDEX. The only lock you take is the microsecond catalog swap at the very end.

Worked example — the deploy-ordering compatibility matrix

Detailed explanation. Expand/contract only works if the deploys are ordered correctly relative to the schema steps. Getting the order wrong (dropping email before all readers moved off it, or reading email_address before backfill) reintroduces the outage you were avoiding. The discipline is a compatibility matrix: for each deploy, list which schema shape it requires and confirm it is compatible with the deploy before and after it. Walk through the matrix for the email rename.

• Rule. Every deploy must run correctly against both the schema that existed before it and the schema it introduces (so rollback is safe).
• Ordering. Expand DDL ships before the code that uses it; contract DDL ships after the code that stops using the old shape.

Question. Lay out the ordered deploy plan for the email rename and verify each step is backward compatible.

Input.

Order	Kind	Change
1	schema	ADD COLUMN email_address (expand)
2	code	dual-write email + email_address
3	data	backfill email_address
4	code	read email_address, still dual-write
5	code	stop writing email
6	schema	DROP COLUMN email (contract)

Code.

Deploy plan — column rename (email -> email_address)
====================================================

D1  schema   ADD COLUMN email_address text (nullable)
             compatible: old code ignores it ................ SAFE
D2  code     write BOTH email and email_address
             compatible: readers still read email ........... SAFE (rollback: D1 only)
D3  data     backfill email_address = email (batched)
             compatible: no schema change ................... SAFE
D4  code     read email_address; still write both
             compatible: column fully populated by D2+D3 .... SAFE (rollback: D2)
D5  code     stop writing email (write only email_address)
             compatible: nothing reads email anymore ........ SAFE (rollback: D4)
D6  schema   DROP COLUMN email
             compatible: nothing reads or writes email ...... SAFE (irreversible)

Invariant checked at every step:
  live_schema is compatible with (deploy N-1 code) AND (deploy N code)

Step-by-step explanation.

1. D1 (expand) leads the code. Adding a nullable column is invisible to old code, so it is safe to ship before any code depends on it. If D1 needs rollback, it is a lone DROP COLUMN of an empty column — trivial.
2. D2 introduces dual writes. It is compatible with D1's schema and with D3's backfill; a rollback from D2 goes back to D1 with no schema change, because reading email still works.
3. D3 is pure data movement with no schema or code contract change, so it can run (and re-run) freely. It must complete before D4.
4. D4 flips reads. It is safe only because D2 (dual-write) and D3 (backfill) guarantee email_address is complete. Rolling back D4 to D2 is safe because D2 already reads email. This is the crux: reads move only after writes and history are in place.
5. D5 stops writing email; D6 drops it. D6 is the single irreversible step, deliberately placed last and gated on D5 being fully rolled out. The invariant live_schema ⊇ (code N-1 ∪ code N) holds at every transition — which is exactly what "zero-downtime" means.

Output.

Transition	Old code sees	New code sees	Broken?
→ D1	email	email (+ ignores new col)	no
→ D2	email	email + email_address	no
→ D4	email + email_address	email_address	no
→ D6	email_address	email_address	no

Rule of thumb. Write the deploy-ordering matrix before touching the database. Expand DDL leads the code; contract DDL trails it; reads switch only after writes and backfill are complete. If any transition breaks either the old or new code version, the ordering is wrong.

Senior interview question on expand/contract

A senior interviewer might ask: "You need to change orders.amount from NUMERIC(10,2) dollars to a BIGINT cents column named amount_cents, on a table doing 5,000 writes/sec, with zero downtime and a clean rollback story at every step. Walk me through the full expand/contract sequence — the schema steps, the dual-write, the backfill, the read cutover, and the contract."

Solution Using a shadow amount_cents column, a dual-write trigger, a batched backfill, and a gated contract

-- EXPAND: add the new column (metadata-only) and a sync trigger for new rows
SET lock_timeout = '3s';
ALTER TABLE public.orders ADD COLUMN amount_cents bigint;

CREATE OR REPLACE FUNCTION public.orders_sync_amount_cents() RETURNS TRIGGER AS $$
BEGIN
    -- keep the new column in lockstep with the old one on every write
    IF NEW.amount IS NOT NULL THEN
        NEW.amount_cents := round(NEW.amount * 100)::bigint;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_sync_amount_cents
BEFORE INSERT OR UPDATE ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.orders_sync_amount_cents();

-- MIGRATE-backfill: batched, idempotent, only untouched rows
DO $$
DECLARE
    lo bigint := 0;
    step bigint := 10000;
    max_id bigint;
BEGIN
    SELECT max(id) INTO max_id FROM public.orders;
    WHILE lo <= max_id LOOP
        UPDATE public.orders
        SET    amount_cents = round(amount * 100)::bigint
        WHERE  id > lo AND id <= lo + step
          AND  amount_cents IS NULL;
        COMMIT;                         -- commit each batch; release locks
        lo := lo + step;
        PERFORM pg_sleep(0.05);         -- throttle to protect replicas
    END LOOP;
END$$;

# READ cutover (deploy 3): the app now reads amount_cents; trigger still
# maintains it, so a rollback to the prior deploy is safe.
def get_order_total_cents(conn, order_id: int) -> int:
    with conn.cursor() as cur:
        cur.execute("SELECT amount_cents FROM public.orders WHERE id = %s", (order_id,))
        return cur.fetchone()[0]

# CONTRACT (deploy 4, schema, after read cutover is fully rolled out):
#   ALTER TABLE public.orders DROP COLUMN amount;
#   DROP TRIGGER trg_orders_sync_amount_cents ON public.orders;
#   ALTER TABLE public.orders ALTER COLUMN amount_cents SET NOT NULL;

Step-by-step trace.

Phase	Action	New rows correct?	Old rows correct?	Reads use
Expand	add column + trigger	yes (trigger)	no (empty)	amount
Backfill	batched UPDATE	yes	yes	amount
Read cutover	deploy reads amount_cents	yes	yes	amount_cents
Contract	drop amount, promote NOT NULL	yes	yes	amount_cents

After the sequence, orders.amount_cents is a NOT NULL bigint holding integer cents, amount is gone, and no query failed at any point. The trigger did the dual-write for live traffic; the batched loop backfilled history; the read cutover was safe because both were complete; the contract was gated on the read cutover being fully deployed.

Output:

Metric	Naive ALTER COLUMN TYPE	Expand/contract
Longest blocking lock	full rewrite (minutes–hours)	microseconds
Writes blocked	all, for the rewrite	none
Rollback at read cutover	impossible mid-rewrite	safe (trigger keeps both)
Replication lag	one huge WAL burst	throttled batches
Data-loss risk	none but outage	none, no outage

Why this works — concept by concept:

• Expand is additive — adding amount_cents as a nullable column is metadata-only, so both old code (reads amount) and new code (reads amount_cents) coexist. The database supports both shapes at once.
• The trigger is the dual-write — a BEFORE INSERT OR UPDATE trigger derives amount_cents from amount on every write, so live traffic never produces a stale new column, without changing every write path in the application.
• Batched backfill handles history — the DO loop updates 10k-row PK ranges, commits each batch, and sleeps to bound replication lag, touching only rows where amount_cents IS NULL so it is idempotent and race-free against the trigger.
• Contract is gated and last — dropping amount and promoting amount_cents to NOT NULL happens only after the read cutover is fully rolled out, so the one irreversible step breaks nothing.
• Cost — one metadata-only ADD COLUMN, one trigger (~microseconds per write), one throttled O(rows) backfill, and a microsecond contract. Versus the naive ALTER COLUMN TYPE full rewrite that blocks all writes for the duration. Same end state, zero downtime, safe rollback at every step.

SQL
Topic — database
Database problems on schema evolution

Practice →

SQL Topic — data-transformation Data-transformation problems on type and column changes

Practice →

---

3. Backfills — chunked, dual-written, and in sync

Never UPDATE a whole table at once — batch by primary-key ranges, let dual writes cover new rows, and throttle on replication lag

The mental model in one line: a backfill is the migrate-phase job that fills a new column (or table) for historical rows, and the only safe way to do it on a large busy table is a chunked loop over primary-key ranges — each batch is a short transaction that updates a few thousand rows, commits, and pauses — because a single UPDATE orders SET ... over a billion rows takes one enormous lock, writes a WAL record for every row at once, bloats the table with dead tuples, and spikes replication lag until replicas fall minutes behind. New rows are handled separately by dual writes (app or trigger), so the backfill only ever touches the shrinking set of rows the live writes have not already covered.

Why not a single UPDATE.

• One giant lock and transaction. A whole-table UPDATE runs in one transaction that holds row locks and cannot be interrupted without losing all progress. If it fails at 90%, you start over.
• WAL and replication-lag spike. Every updated row produces WAL; a billion-row update dumps gigabytes of WAL at once, and physical replicas must replay all of it, falling far behind and starving read-replica traffic.
• Table bloat. In Postgres (MVCC), an UPDATE writes a new row version and leaves the old one dead. A whole-table update doubles the table's size in dead tuples until VACUUM catches up — and autovacuum cannot keep up with a single massive burst.
• No throttle, no resume. A single statement cannot slow down when replicas lag or resume from where it stopped. Batching gives you both.

The batched backfill loop.

• Iterate by PK range. Walk the primary key in fixed-size windows (id > lo AND id <= lo + step). Range scans on the PK are cheap and gap-tolerant; OFFSET/LIMIT pagination is not (it re-scans).
• Short transaction per batch. Update a few thousand rows, COMMIT, move the window forward. Each commit releases locks and lets VACUUM reclaim dead tuples.
• Idempotent predicate. Add WHERE new_col IS NULL (or a version check) so re-running the loop, or overlapping the dual-writes, never double-processes or corrupts a row.
• Durable watermark. Persist the last processed lo so a crashed backfill resumes instead of restarting.

Dual writes vs backfill — dividing the work.

• Dual writes own new/updated rows. From the moment the expand deploy ships, application code or a trigger writes the new column on every mutation, so the "future" is always correct.
• Backfill owns historical rows. The batched loop fills rows that existed before dual-writes started. The WHERE new_col IS NULL predicate is the seam: dual-writes fill new rows (making them non-NULL), and the backfill skips anything already filled.
• No race. Because both use the same idempotent predicate and PK-scoped updates, a row touched by a live write during the backfill is simply skipped by the next batch — correctness holds without coordination.

Throttling + safety.

• Watch replication lag. Before each batch, check replica lag; if it exceeds a threshold, sleep until it recovers. This keeps read replicas usable during the backfill.
• Tune batch size. Start small (1k–5k rows), measure per-batch time and WAL, and raise it only if lag and duration stay comfortable. Bigger batches are faster but lag harder.
• Run off-peak, but survive peak. Schedule backfills for low-traffic windows, but the lag-aware throttle must let them survive a traffic spike by slowing down rather than piling up.

Worked example — a batched PK-range backfill in PL/pgSQL

Detailed explanation. The canonical Postgres backfill is a DO block (or a stored procedure) that walks the primary key in fixed windows, updates only unfilled rows, commits each batch, and sleeps. Procedures (not DO blocks) are preferred when you need COMMIT inside the loop on older versions, but PG11+ DO supports transaction control. Walk through the loop.

• Window. id > lo AND id <= lo + step.
• Predicate. WHERE new_col IS NULL for idempotency.
• Commit. After each batch, to release locks and let VACUUM run.
• Sleep. A short pause to bound replica lag.

Question. Backfill users.email_normalized = lower(email) across a 2-billion-row table without a whole-table lock.

Input.

Parameter	Value
Table	public.users (2B rows)
New column	email_normalized text
Batch size	10,000 rows
Predicate	email_normalized IS NULL
Sleep	50 ms per batch

Code.

-- Backfill email_normalized in 10k PK-range batches (PG 11+ DO with COMMIT)
DO $$
DECLARE
    lo      bigint := 0;
    step    bigint := 10000;
    max_id  bigint;
    touched int;
BEGIN
    SELECT max(id) INTO max_id FROM public.users;

    WHILE lo <= max_id LOOP
        UPDATE public.users
        SET    email_normalized = lower(email)
        WHERE  id > lo
          AND  id <= lo + step
          AND  email_normalized IS NULL;      -- idempotent seam

        GET DIAGNOSTICS touched = ROW_COUNT;
        COMMIT;                               -- release locks, allow VACUUM

        RAISE NOTICE 'window (% .. %] updated % rows', lo, lo + step, touched;
        lo := lo + step;
        PERFORM pg_sleep(0.05);               -- throttle for replicas
    END LOOP;
END$$;

Step-by-step explanation.

1. SELECT max(id) establishes the upper bound so the loop knows when to stop. The loop walks lo from 0 upward in step-sized windows, covering the whole key space including gaps (deleted rows) cheaply.
2. Each UPDATE touches at most step rows and filters on email_normalized IS NULL, so rows already filled by a live dual-write (or a previous run) are skipped. This makes the loop idempotent and safe to re-run after a crash.
3. COMMIT after every batch is the critical line: it ends the transaction, releases the row locks, and makes the dead tuples eligible for autovacuum — preventing the bloat and lock accumulation a single statement would cause.
4. pg_sleep(0.05) pauses 50 ms between batches. This caps the WAL production rate so physical replicas can keep up; without it, a fast loop can still outrun replication on a busy cluster.
5. GET DIAGNOSTICS ... ROW_COUNT and the RAISE NOTICE give per-window observability, so you can watch progress and spot a window that unexpectedly updated zero or too many rows.

Output.

Window	Rows updated	Cumulative	Lock held
(0 .. 10000]	10,000	10,000	1 batch
(10000 .. 20000]	9,997	19,997	1 batch
(20000 .. 30000]	10,000	29,997	1 batch
…	…	…	never table-wide

Rule of thumb. Backfill by PK range, commit every batch, filter on new_col IS NULL, and sleep between batches. Never UPDATE a whole large table in one statement — batch it or bring down the cluster.

Worked example — the dual-write trigger that keeps new rows current during the backfill

Detailed explanation. While the backfill grinds through history, live traffic keeps inserting and updating rows. If those new rows are not written to the new column, the backfill can "finish" and still leave a stream of NULLs behind it. The dual-write — implemented as a trigger so no application code changes — guarantees every live write populates the new column. Walk through it.

• Trigger. BEFORE INSERT OR UPDATE sets the new column from the source column.
• Idempotent with backfill. Both write the same derived value, so a row touched by either is correct.
• Zero app change. The trigger fires regardless of which service issues the write.

Question. Ensure every live insert/update populates email_normalized while the backfill runs.

Input.

Component	Behavior
Trigger timing	BEFORE INSERT OR UPDATE
Derivation	email_normalized = lower(email)
Interaction with backfill	same value; idempotent

Code.

-- Dual-write trigger: live writes fill email_normalized immediately.
CREATE OR REPLACE FUNCTION public.users_sync_email_normalized()
RETURNS TRIGGER AS $$
BEGIN
    NEW.email_normalized := lower(NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_sync_email_normalized
BEFORE INSERT OR UPDATE ON public.users
FOR EACH ROW EXECUTE FUNCTION public.users_sync_email_normalized();

-- Order of operations matters:
--   1. ADD COLUMN email_normalized  (expand)
--   2. CREATE the trigger above      (dual-write starts covering NEW rows)
--   3. run the batched backfill      (covers OLD rows)
-- Doing (2) before (3) guarantees no row slips through the seam.

Step-by-step explanation.

1. The trigger fires BEFORE each insert/update and sets email_normalized from the row's email. Because it is BEFORE, it modifies the row in place with no extra write — cheaper than an AFTER trigger that would issue a second UPDATE.
2. Installing the trigger before starting the backfill is essential: it guarantees that from that instant forward, every new or modified row is already correct, so the backfill only has to deal with rows that predate the trigger.
3. The trigger and the backfill compute the same value (lower(email)), so a row updated by live traffic during the backfill is correct either way; the backfill's IS NULL predicate then skips it. No coordination or locking between the two is needed.
4. Using a trigger rather than application code means every write path — the main service, an admin tool, a data-fix script — is covered automatically. There is no risk of one forgotten code path leaking NULLs.
5. In the contract phase, the trigger is dropped (the column is now maintained by real application logic or is itself the source of truth). During the migration window, the trigger is the safety net that makes the backfill's "finish line" real.

Output.

Event during backfill	email_normalized set by	Correct?
INSERT new user	trigger	yes
UPDATE existing user's email	trigger	yes
Historical row (untouched)	backfill batch	yes
Historical row touched by live UPDATE	trigger, then skipped by backfill	yes

Rule of thumb. Install the dual-write trigger before the backfill starts. New rows are the trigger's job; old rows are the backfill's job; the shared idempotent value means the two never fight.

Worked example — a replication-lag-aware, resumable Python backfill

Detailed explanation. For very large backfills you want an external driver that checks replication lag before each batch, persists a watermark so it can resume after a crash, and adapts its pace to the cluster's health. Walk through the Python driver.

• Lag check. Query pg_stat_replication (or replica-side pg_last_wal_replay_lsn) before each batch; sleep if lag is high.
• Watermark table. Persist last_id so a restart resumes from there.
• Adaptive sleep. Longer pause when lag is high, shorter when it is low.

Question. Drive the users.email_normalized backfill from Python with lag-aware throttling and crash-safe resume.

Input.

Parameter	Value
Batch size	10,000
Lag threshold	30 MB
Watermark store	backfill_progress table
Resume	from persisted last_id

Code.

# backfill_driver.py — lag-aware, resumable batched backfill
import time
import psycopg2

BATCH = 10_000
LAG_LIMIT_BYTES = 30 * 1024 * 1024   # pause if any replica lags > 30 MB

def replica_lag_bytes(cur) -> int:
    cur.execute("""
        SELECT COALESCE(max(pg_wal_lsn_diff(sent_lsn, replay_lsn)), 0)
        FROM   pg_stat_replication
    """)
    return cur.fetchone()[0]

def load_watermark(cur) -> int:
    cur.execute("SELECT last_id FROM backfill_progress WHERE job = 'users_email_norm'")
    row = cur.fetchone()
    return row[0] if row else 0

def save_watermark(cur, last_id: int) -> None:
    cur.execute("""
        INSERT INTO backfill_progress(job, last_id) VALUES ('users_email_norm', %s)
        ON CONFLICT (job) DO UPDATE SET last_id = EXCLUDED.last_id
    """, (last_id,))

def run():
    conn = psycopg2.connect("host=db-primary dbname=production user=migrator")
    conn.autocommit = False
    with conn.cursor() as cur:
        cur.execute("SELECT max(id) FROM public.users")
        max_id = cur.fetchone()[0] or 0
        lo = load_watermark(cur)
        conn.commit()

        while lo < max_id:
            # 1. throttle on replication lag before doing work
            with conn.cursor() as c2:
                lag = replica_lag_bytes(c2)
            if lag > LAG_LIMIT_BYTES:
                time.sleep(2.0)          # back off; recheck
                continue

            # 2. one batch as a short transaction
            cur.execute("""
                UPDATE public.users
                SET    email_normalized = lower(email)
                WHERE  id > %s AND id <= %s
                  AND  email_normalized IS NULL
            """, (lo, lo + BATCH))
            lo += BATCH
            save_watermark(cur, lo)      # watermark advances with the data
            conn.commit()                # atomic: data + watermark together

            time.sleep(0.02)             # gentle steady-state pace

    conn.close()
    print("backfill complete")

if __name__ == "__main__":
    run()

Step-by-step explanation.

1. Before each batch, replica_lag_bytes reads the maximum sent_lsn - replay_lsn across replicas. If any replica is more than 30 MB behind, the driver sleeps and re-checks instead of adding more WAL — protecting read-replica traffic.
2. The watermark is loaded once at startup and advanced with every committed batch. Because the UPDATE and the save_watermark are committed in the same transaction, "data written" and "progress recorded" are atomic — a crash leaves them consistent.
3. Each batch is a short transaction over a 10k PK window with the email_normalized IS NULL predicate, so it is idempotent and cooperates with the dual-write trigger exactly like the PL/pgSQL version.
4. On restart, load_watermark returns the last committed lo, so the driver resumes from there — no re-processing of already-filled ranges, no lost progress. A backfill that runs for days survives deploys and restarts.
5. The two sleeps encode the throttle: a long back-off when lag is high, a short steady-state pause otherwise. Batch size and sleeps are the two knobs you tune against measured per-batch time and lag.

Output.

Condition	Driver action
Replica lag < 30 MB	run next batch, advance watermark
Replica lag > 30 MB	sleep 2 s, re-check (no new WAL)
Process crash	resume from persisted watermark
Batch finds all rows filled	skips them (IS NULL predicate)

Rule of thumb. For multi-hour backfills, drive them from a script that checks replication lag before every batch and commits the watermark atomically with the data. Lag-aware throttling plus a durable watermark is the difference between a backfill that finishes quietly and one that pages the on-call.

Senior interview question on backfills

A senior interviewer might ask: "You added a nullable email_normalized column to a 2-billion-row users table and now need to backfill it without spiking replication lag past your 30 MB alert threshold or bloating the table. Walk me through the backfill design — batching strategy, how you keep new rows current, how you throttle, how you make it resumable, and how you verify completeness before contracting."

Solution Using a dual-write trigger, a lag-aware batched loop, a durable watermark, and a completeness check

-- 1. Dual-write trigger — new rows are correct from the start
CREATE OR REPLACE FUNCTION public.users_sync_email_normalized()
RETURNS TRIGGER AS $$
BEGIN
    NEW.email_normalized := lower(NEW.email);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_sync_email_normalized
BEFORE INSERT OR UPDATE ON public.users
FOR EACH ROW EXECUTE FUNCTION public.users_sync_email_normalized();

-- 2. Durable progress table for resumability
CREATE TABLE IF NOT EXISTS backfill_progress (
    job      text PRIMARY KEY,
    last_id  bigint NOT NULL DEFAULT 0,
    done_at  timestamptz
);

# 3. Lag-aware, resumable backfill driver (batched, throttled)
#    (identical structure to backfill_driver.py above:
#     check pg_stat_replication lag -> UPDATE 10k window WHERE IS NULL
#     -> advance + commit watermark atomically -> sleep)

-- 4. Completeness check BEFORE contract — must return 0
SELECT count(*) AS remaining_nulls
FROM   public.users
WHERE  email_normalized IS NULL;

-- 5. Only when remaining_nulls = 0, contract:
--    ALTER TABLE public.users ADD CONSTRAINT users_email_norm_nn
--        CHECK (email_normalized IS NOT NULL) NOT VALID;
--    ALTER TABLE public.users VALIDATE CONSTRAINT users_email_norm_nn;
--    ALTER TABLE public.users ALTER COLUMN email_normalized SET NOT NULL;
--    ALTER TABLE public.users DROP CONSTRAINT users_email_norm_nn;

Step-by-step trace.

Step	Mechanism	Effect
Dual-write trigger	BEFORE INSERT/UPDATE	new rows never NULL
Batched loop	10k PK windows, commit each	no whole-table lock
Lag guard	pg_stat_replication check	replicas stay < 30 MB behind
Watermark	backfill_progress row	crash-safe resume
Completeness	count NULLs = 0	gate before NOT NULL

After the backfill, every historical row is filled by the loop, every live row by the trigger, and the remaining_nulls = 0 check proves completeness before the NOT NULL promotion. Replicas never breached the 30 MB threshold because the loop paused whenever lag rose, and the table never bloated because each batch committed and freed dead tuples for autovacuum.

Output:

Metric	Whole-table UPDATE	Batched + throttled
Peak replication lag	GB (alert storm)	< 30 MB (throttled)
Table bloat	~2× until VACUUM	bounded per batch
Resumable after crash	no (restart)	yes (watermark)
New rows during backfill	may be missed	covered by trigger
Verifiable completeness	hard	count NULLs = 0

Why this works — concept by concept:

• Chunked PK-range batches — updating 10k-row windows and committing each keeps every lock short, every WAL burst small, and every dead-tuple set collectable by autovacuum, so the table never bloats or stalls.
• Dual-write trigger — installing the BEFORE INSERT OR UPDATE trigger before the loop guarantees live writes populate the new column, so the backfill's finish line is real and no row slips through the seam.
• Lag-aware throttle — checking pg_stat_replication before each batch and sleeping when lag is high bounds replica lag under the alert threshold, keeping read replicas usable throughout.
• Durable watermark — committing last_id atomically with the batch's data means a crash resumes exactly where it stopped, so a multi-day backfill survives deploys and restarts.
• Cost — O(rows) total work, but spread over short throttled batches with bounded lock, WAL, and lag — versus one O(rows) statement that locks, bloats, and lags the whole cluster at once. Plus an O(rows) verification scan and a cheap validated-constraint promotion. Same result, no incident.

ETL
Topic — etl
ETL problems on batched backfills and reprocessing

Practice →

SQL Topic — data-transformation Data-transformation problems on derived columns

Practice →

---

4. Online DDL tooling — Postgres, MySQL, linters

Postgres has lock-safe DDL primitives, pg_repack rewrites bloated tables online, MySQL has gh-ost and pt-online-schema-change, and linters catch unsafe migrations in CI

The mental model in one line: online ddl is the set of engine features and external tools that let you make a schema change without holding a table-blocking lock for O(rows) — Postgres ships lock-safe primitives (CREATE INDEX CONCURRENTLY, ADD CONSTRAINT ... NOT VALID then VALIDATE, and metadata-only ADD COLUMN), plus pg_repack for online table rewrites; MySQL ships in-engine ALGORITHM=INSTANT/INPLACE for some changes and the external gh-ost and pt-online-schema-change tools that build a shadow table, copy rows in chunks, keep it in sync, and swap it in with an atomic rename; and safe-migration linters reject unsafe DDL in CI before it ever reaches production. Knowing which primitive maps to which change is the difference between a lock-free migration and an outage.

Postgres lock-safe DDL toolkit.

• CREATE INDEX CONCURRENTLY. Builds an index without blocking writes. It takes longer (two table scans) and can leave an INVALID index if it fails (drop and retry), but it never holds ACCESS EXCLUSIVE. Always use it in production; never plain CREATE INDEX.
• ADD CONSTRAINT ... NOT VALID + VALIDATE. Add a CHECK or FOREIGN KEY as NOT VALID (instant, no scan), then VALIDATE CONSTRAINT (scans under a weak SHARE UPDATE EXCLUSIVE lock that does not block DML). This is how you add constraints to big tables safely.
• Metadata-only ADD COLUMN / DROP COLUMN. Nullable columns and constant defaults (PG11+) are metadata-only; drops are metadata-only (the column is marked dead, space reclaimed by later VACUUM).
• SET NOT NULL via a validated CHECK. Add CHECK (col IS NOT NULL) NOT VALID, VALIDATE, then SET NOT NULL — which reuses the validated constraint to skip its own scan (PG12+).
• DROP INDEX CONCURRENTLY. Removes an index without a long lock, the mirror of the concurrent build.

pg_repack — online table rewrites.

• What it does. Rebuilds a table or index to remove bloat, or applies a clustering, without the long ACCESS EXCLUSIVE lock that VACUUM FULL or a rewrite ALTER needs. It builds a copy, replays changes via triggers, and swaps with only brief locks.
• When to use. Reclaiming space after a big delete or a rewrite backfill, or physically reordering a table — cases where the native operation would need an outage.
• The catch. It needs roughly double the table's disk space during the rebuild and a superuser/extension install. It is the standard answer for "rewrite this table online."

MySQL online DDL + gh-ost / pt-online-schema-change.

• In-engine algorithms. ALTER TABLE ... ALGORITHM=INSTANT (metadata-only, e.g. add column at end, 8.0+), INPLACE (rebuilds in place, allows concurrent DML for many changes), and COPY (locks, rebuilds — the thing to avoid). Always specify the algorithm and LOCK=NONE so MySQL errors out instead of silently falling back to COPY.
• gh-ost (binlog-based). Creates a ghost table, copies rows in chunks, and tails the binlog (no triggers on the source) to apply live changes, then does an atomic rename. Triggerless design means no added write latency on the source; it can also throttle and pause.
• pt-online-schema-change (trigger-based). Creates a shadow table and installs triggers on the source to mirror writes while copying rows in chunks, then atomically renames. Simpler to run but adds trigger overhead to every write.

Safe-migration linters.

• What they check. Static analysis of migration files for patterns that lock or rewrite: ADD COLUMN ... DEFAULT on old versions, SET NOT NULL without a validated constraint, plain CREATE INDEX, changing a column type, adding a validating foreign key.
• Tools. squawk (Postgres migration linter), strong_migrations (Rails/ActiveRecord), and framework-specific guards. Run them as a CI gate that fails the build on an unsafe migration.
• Why in CI. The cheapest place to catch an unsafe migration is code review, not production. A linter turns "senior engineer remembers the rules" into "the pipeline enforces the rules."

Worked example — CREATE INDEX CONCURRENTLY and NOT VALID/VALIDATE for zero-lock constraints

Detailed explanation. The two most common "make this online" tasks in Postgres are adding an index and adding a foreign key to a big busy table. Both have lock-safe forms. Walk through adding an index and an FK to orders without blocking writes.

• Index. CREATE INDEX CONCURRENTLY — no write blocking, two scans.
• Foreign key. ADD CONSTRAINT ... NOT VALID (instant) then VALIDATE CONSTRAINT (weak lock).
• Failure handling. A failed concurrent index leaves an INVALID index to drop and retry.

Question. Add an index on orders.customer_id and a foreign key to customers(id) with no write-blocking lock.

Input.

Change	Naive form	Lock-safe form
Index	CREATE INDEX ... (ACCESS EXCLUSIVE-ish, blocks writes)	CREATE INDEX CONCURRENTLY
Foreign key	ADD FOREIGN KEY (scans under strong lock)	NOT VALID + VALIDATE

Code.

-- 1. Index without blocking writes (run OUTSIDE a transaction block)
CREATE INDEX CONCURRENTLY idx_orders_customer_id
    ON public.orders (customer_id);

-- If it failed midway, an invalid index remains — clean it up and retry:
--   SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
--   DROP INDEX CONCURRENTLY idx_orders_customer_id;

-- 2. Foreign key in two lock-safe steps
SET lock_timeout = '3s';
ALTER TABLE public.orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES public.customers (id)
    NOT VALID;                       -- instant: no scan of existing rows

-- Validate later; scans under SHARE UPDATE EXCLUSIVE (does not block DML)
ALTER TABLE public.orders
    VALIDATE CONSTRAINT fk_orders_customer;

Step-by-step explanation.

1. CREATE INDEX CONCURRENTLY builds the index in two passes while allowing concurrent INSERT/UPDATE/DELETE. It cannot run inside a transaction block and is slower than a plain build, but it never takes the write-blocking lock a plain CREATE INDEX does.
2. If a concurrent build is interrupted, Postgres leaves an INVALID index behind. The cleanup query finds it and DROP INDEX CONCURRENTLY removes it so you can retry — this is expected operational hygiene, not a failure of the approach.
3. ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID records the FK in the catalog and starts enforcing it on new writes immediately, but skips the scan of existing rows, so it takes ACCESS EXCLUSIVE for only an instant.
4. VALIDATE CONSTRAINT then scans existing rows to confirm they satisfy the FK, under SHARE UPDATE EXCLUSIVE — which blocks other DDL but not SELECT/INSERT/UPDATE/DELETE. The expensive verification runs without an outage.
5. The pair gives a fully-enforced foreign key on a huge table with no write-blocking lock, split into an instant catalog change plus a background validation.

Output.

Operation	Lock	Blocks writes?
CREATE INDEX (naive)	blocks writes for the whole build	yes
CREATE INDEX CONCURRENTLY	ShareUpdateExclusive	no
ADD FK (validating)	ACCESS EXCLUSIVE for full scan	yes
ADD FK NOT VALID	ACCESS EXCLUSIVE, instant	no (microseconds)
VALIDATE CONSTRAINT	ShareUpdateExclusive	no

Rule of thumb. In Postgres, always build indexes CONCURRENTLY and add constraints as NOT VALID then VALIDATE. These two primitives cover the majority of "add something to a big table" migrations without a blocking lock.

Worked example — gh-ost cut-over anatomy for a MySQL column add

Detailed explanation. MySQL's external online-DDL tools solve the case where in-engine ALGORITHM=INPLACE is not enough or you want throttling, pausing, and a testable cut-over. gh-ost is the modern, triggerless choice: it reads the binlog to track live changes instead of installing triggers, so it adds no write-latency to the source. Walk through what a gh-ost run does.

• Ghost table. gh-ost creates _orders_gho with the new schema.
• Chunked copy. It copies rows from orders into the ghost table in small chunks.
• Binlog apply. Concurrently it tails the binlog and applies live INSERT/UPDATE/DELETE to the ghost table.
• Atomic cut-over. When copy + binlog are caught up, it atomically renames orders → _orders_del and _orders_gho → orders.

Question. Add a region varchar(2) column to a large MySQL orders table online with gh-ost, with throttling and a safe cut-over.

Input.

Component	Value
Table	orders (MySQL 8.0)
Change	ADD COLUMN region varchar(2)
Sync mechanism	binlog (no triggers)
Throttle	on replica lag

Code.

# gh-ost — triggerless online schema change for MySQL
gh-ost \
  --host=mysql-primary.internal \
  --database=production \
  --table=orders \
  --alter="ADD COLUMN region varchar(2) NULL" \
  --max-load="Threads_running=25" \        # throttle if server busy
  --critical-load="Threads_running=100" \  # abort if overloaded
  --max-lag-millis=1500 \                  # throttle on replica lag
  --chunk-size=1000 \                      # rows per copy chunk
  --initially-drop-ghost-table \
  --allow-on-master \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \  # hold the swap
  --execute

# When ready to finish, remove the flag file to trigger the atomic rename:
rm -f /tmp/ghost.postpone

gh-ost lifecycle
================
1. create _orders_gho with new schema (region column added)
2. copy orders -> _orders_gho in 1000-row chunks (throttled)
3. tail binlog; apply live INSERT/UPDATE/DELETE to _orders_gho
4. wait until row-copy done AND binlog applied (lag ~0)
5. (postpone flag present -> wait, safe to abort here)
6. remove flag -> atomic: RENAME orders TO _orders_del,
                          RENAME _orders_gho TO orders
7. drop _orders_del

Step-by-step explanation.

1. gh-ost creates the ghost table _orders_gho with the desired schema (the new region column) and begins copying rows from orders in 1000-row chunks. The chunk size and --max-load throttle keep the copy from overwhelming the server.
2. Instead of triggers, gh-ost connects as a replica and reads the binlog, applying every live INSERT/UPDATE/DELETE to the ghost table. This triggerless design is its headline advantage — the source table's writes pay no trigger tax.
3. --max-lag-millis and --critical-load make the copy adaptive: it slows or pauses when replica lag or server load rises, and aborts entirely if the server is critically loaded. The migration yields to production traffic.
4. The --postpone-cut-over-flag-file holds the final rename until you remove the flag, so you can complete the row-copy during the day and schedule the (near-instant) cut-over deliberately. Until the flag is removed, the whole operation is safely abortable.
5. The cut-over is a single atomic pair of renames — orders becomes _orders_del, _orders_gho becomes orders — that MySQL performs under a brief metadata lock. Live traffic sees the new schema instantly; the old table is dropped afterward.

Output.

gh-ost feature	Benefit
Ghost table + chunked copy	no long lock on source
Binlog-based sync (no triggers)	zero write-latency tax
max-lag / max-load throttle	yields to production traffic
postpone-cut-over flag	schedule the swap; abortable
atomic rename cut-over	instant schema swap

Rule of thumb. For large MySQL tables, prefer gh-ost (triggerless, throttleable, testable cut-over) or pt-online-schema-change (trigger-based) over a raw ALTER — and always pass --max-lag-millis/--max-load so the copy yields to production load.

Worked example — a CI linter catching an unsafe migration

Detailed explanation. The cheapest defense is preventing the unsafe migration from merging. A linter like squawk (Postgres) statically analyzes a migration file and fails the build if it finds a dangerous pattern. Walk through wiring it into CI and the rules it enforces.

• Rule examples. Ban plain CREATE INDEX (require CONCURRENTLY), ban SET NOT NULL without a validated constraint, ban adding a column with a volatile default, ban changing a column type.
• CI gate. Run the linter on changed migration files; non-zero exit fails the pipeline.
• Escape hatch. An explicit, reviewed annotation to bypass a rule when the engineer knows better.

Question. Add a CI step that rejects an unsafe migration and show the failure it produces for a naive SET NOT NULL.

Input.

Migration	Verdict
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;	unsafe (full scan under strong lock)
CREATE INDEX idx ON orders (customer_id);	unsafe (non-concurrent)
ADD COLUMN note text;	safe

Code.

# .github/workflows/migrations.yml — fail the build on unsafe DDL
name: migration-lint
on: [pull_request]
jobs:
  squawk:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install squawk
        run: npm install -g squawk-cli
      - name: Lint changed migrations
        run: squawk migrations/*.sql

$ squawk migrations/0042_region_not_null.sql

migrations/0042_region_not_null.sql:1:1: warning: disallowed-unique-constraint
migrations/0042_region_not_null.sql:1:1: warning: constraint-missing-not-valid

  ALTER TABLE "orders" ALTER COLUMN "region" SET NOT NULL;
  ^ Setting NOT NULL requires a full table scan under ACCESS EXCLUSIVE.
    Instead: ADD CONSTRAINT ... CHECK (region IS NOT NULL) NOT VALID,
    VALIDATE CONSTRAINT, then SET NOT NULL (reuses the validated check).

Found 1 unsafe migration. Exit code: 1   # -> CI fails, PR blocked

Step-by-step explanation.

1. The CI job runs on every pull request and installs the linter, then points it at the changed migration files. A non-zero exit code fails the job, which blocks the merge — so an unsafe migration cannot reach main.
2. For the naive SET NOT NULL, squawk recognizes that Postgres will scan the whole table under ACCESS EXCLUSIVE and emits an error with the exact safe rewrite (the NOT VALID + VALIDATE + SET NOT NULL sequence from section 1).
3. The linter encodes the same rules a senior engineer would apply in review — non-concurrent index, volatile default, type change, unvalidated constraint — but applies them mechanically to every migration, so the knowledge does not depend on who happens to review the PR.
4. When an engineer genuinely needs to bypass a rule (e.g. the table is tiny and empty), squawk supports an explicit, reviewable annotation to opt out of a specific rule — visible in the diff so the exception is a deliberate, reviewed decision.
5. The payoff: unsafe migrations are caught at the cheapest possible point — before merge — instead of at the most expensive point — a production incident. The linter is the automation layer under the whole discipline.

Output.

Migration pattern	Linter verdict	CI result
SET NOT NULL (direct)	unsafe	build fails
CREATE INDEX (non-concurrent)	unsafe	build fails
ADD COLUMN nullable	safe	build passes
NOT VALID + VALIDATE	safe	build passes

Rule of thumb. Put a migration linter in CI so the pipeline — not the reviewer's memory — enforces the safe-DDL rules. It is the cheapest, most reliable guardrail in the entire zero-downtime toolkit.

Senior interview question on online DDL tooling

A senior interviewer might ask: "You maintain both a Postgres 14 service and a MySQL 8.0 service. On Postgres you need to add a NOT NULL foreign key and a supporting index to a 300-million-row table; on MySQL you need to add a column to a 400-million-row table. Both are hot, no maintenance window. Walk me through the online-DDL tooling you'd use for each, the locks involved, and how you'd throttle and gate the changes."

Solution Using Postgres CONCURRENTLY + NOT VALID/VALIDATE and MySQL gh-ost, gated by a CI linter

-- POSTGRES side — index + FK with no write-blocking lock
-- 1. supporting index, concurrently (outside a txn)
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON public.orders (customer_id);

-- 2. FK as NOT VALID (instant), then VALIDATE (weak lock)
SET lock_timeout = '3s';
ALTER TABLE public.orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES public.customers (id) NOT VALID;
ALTER TABLE public.orders VALIDATE CONSTRAINT fk_orders_customer;

-- 3. NOT NULL via validated CHECK (no full scan on PG12+)
ALTER TABLE public.orders
    ADD CONSTRAINT orders_customer_nn CHECK (customer_id IS NOT NULL) NOT VALID;
ALTER TABLE public.orders VALIDATE CONSTRAINT orders_customer_nn;
ALTER TABLE public.orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE public.orders DROP CONSTRAINT orders_customer_nn;

# MYSQL side — add a column online with gh-ost, throttled and gated
gh-ost \
  --host=mysql-primary.internal --database=production --table=orders \
  --alter="ADD COLUMN region varchar(2) NULL" \
  --max-lag-millis=1500 --max-load="Threads_running=25" \
  --critical-load="Threads_running=100" --chunk-size=1000 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone --execute
# remove the flag file during a quiet minute to trigger the atomic rename

# CI GATE — both engines' migrations are linted before merge
- run: squawk migrations/postgres/*.sql        # Postgres safe-DDL rules
- run: |                                        # MySQL: require online algorithm
    grep -L "ALGORITHM=INSTANT\|gh-ost\|pt-online" migrations/mysql/*.sql \
      && echo "unsafe MySQL migration" && exit 1 || true

Step-by-step trace.

Engine	Change	Tool / primitive	Longest lock
Postgres	index	CREATE INDEX CONCURRENTLY	ShareUpdateExclusive
Postgres	FK	NOT VALID + VALIDATE	microseconds + weak scan
Postgres	NOT NULL	validated CHECK + SET NOT NULL	microseconds
MySQL	add column	gh-ost ghost table + rename	brief metadata (cut-over)
Both	gate	squawk / grep in CI	— (pre-merge)

After the changes, Postgres has a fully-enforced NOT NULL foreign key plus its index with no write-blocking lock, and MySQL has the new column added by gh-ost's chunked copy and atomic rename. Both migrations were throttled to yield to production traffic, and the CI gate ensured neither could merge in an unsafe form.

Output:

Metric	Naive approach	Online-DDL approach
Postgres write blocking	full-scan + build locks	none (concurrent + weak)
MySQL write blocking	ALGORITHM=COPY lock	brief cut-over only
Throttle on load/lag	none	max-load / max-lag
Abortable mid-flight	no	yes (postpone flag)
Unsafe migration reaches prod	possible	blocked in CI

Why this works — concept by concept:

• CREATE INDEX CONCURRENTLY — builds the index in two passes without the write-blocking lock a plain CREATE INDEX takes, so writes continue throughout the (longer) build.
• NOT VALID then VALIDATE — splits constraint creation into an instant catalog change and a background scan under a weak lock, giving a fully-enforced FK and NOT NULL with no O(rows) blocking lock.
• gh-ost ghost table + binlog — copies rows in throttled chunks and syncs live writes via the binlog (no triggers, no write-latency tax), then swaps with an atomic rename, so MySQL never holds a COPY-algorithm lock.
• Throttle knobs — --max-lag-millis/--max-load (gh-ost) and batched VALIDATE (Postgres) make both migrations yield to production traffic instead of competing with it.
• Cost — extra scans (concurrent index, validate) and roughly double disk during the gh-ost copy, in exchange for zero write-blocking locks and a pre-merge CI gate. Versus naive DDL that blocks all writes for the full rewrite/scan. The tooling buys online-ness with time and disk, never with downtime.

SQL
Topic — database
Database problems on indexing and constraints

Practice →

Design Topic — design Design problems on online DDL and migration tooling

Practice →

---

5. Orchestrating schema changes with app deploys

Sequence the migration against a rolling deploy — expand before code, contract after, flip reads behind a feature flag, and drop columns only when nothing references them

The mental model in one line: orchestration is the discipline of ordering the schema step, the data migration, and the code deploy in your CI/CD pipeline so that a rolling deploy — which runs the old and new code versions simultaneously for minutes — never sees a schema it cannot handle: expand DDL ships before the code that uses it, contract DDL ships after the code that stops using the old shape, the read cutover hides behind a feature flag so it flips (and rolls back) without a deploy, and a column is dropped only once every referencing code path is gone. The migrations and the deploys are separate pipeline steps with a strict, enforced order.

Rolling deploy + N-1 compatibility.

• Old and new code coexist. A rolling deploy replaces pods gradually; for the duration, version N and version N+1 both serve traffic. The live schema must satisfy both.
• The N-1 rule restated. Deploy N+1 may only depend on schema that deploy N already guaranteed. Never ship code and the schema it newly requires in the same release.
• Rollback is a first-class case. Because a rolling deploy can roll back to the previous version at any time, every step's schema must also be compatible with the previous code — which additive expands and gated contracts guarantee.

Where the migration runs in the pipeline.

• Expand DDL before deploy. Additive schema changes (new column, new index, new table) run first, as their own step, because old code ignores them. Then the code that uses them deploys.
• Contract DDL after full rollout. Removals (drop column, drop trigger, drop old table) run only after the deploy that stopped referencing them has fully rolled out to every pod — usually a separate release, hours or days later.
• Backfills between. The data migration runs after the expand and dual-write are in place and before the read cutover, as its own throttled job.

Feature flags for the read cutover.

• Flip reads instantly. Guard the "read from the new column" logic behind a flag. Turning the flag on switches reads across the fleet in seconds — no deploy, no restart.
• Instant rollback. If the new column misbehaves, turn the flag off and reads revert immediately. This decouples the risky cutover from the deploy cycle.
• Percentage rollout. Ramp the flag from 1% → 10% → 100% to observe the new read path under real traffic before committing.

Renaming / dropping safely.

• Never drop a referenced column. An old pod running SELECT * or an ORM that lists all columns will error if a column vanishes mid-deploy. Drop only in the contract phase, after every referencing deploy is gone.
• Tell the ORM to ignore it first. Many ORMs SELECT every known column; before dropping, ship a release that marks the column "ignored" so the ORM stops selecting it, then drop it in a later release. This is a two-deploy drop.
• Renames are expand/contract, never RENAME. As in section 2, a live column rename is add-new + dual-write + backfill + switch + drop-old — never a raw ALTER ... RENAME COLUMN.

Interview signals — what senior interviewers listen for.

• Naming expand/contract unprompted and sequencing expand-before-code, contract-after-code. — senior signal.
• Saying "rolling deploy runs N-1 and N together" as the reason every step must be backward compatible. — required answer.
• Putting the read cutover behind a feature flag for instant rollback. — senior signal.
• The two-deploy drop (ORM ignore, then DDL) for removing a column. — senior signal.
• lock_timeout + online DDL + backfill throttling named as the mechanical guardrails. — required answer.

Worked example — the full deploy timeline for a column rename

Detailed explanation. Orchestration is best seen as a timeline: which pipeline step runs when, what state the schema and fleet are in, and where rollback lands. Walk through the timeline for the email → email_address rename across a 50-pod fleet.

• Steps. Expand DDL → dual-write deploy → backfill → read-cutover deploy (flag) → stop-writing-old deploy → contract DDL.
• Fleet state. At each transition, both code versions must work.

Question. Lay out the ordered pipeline for the rename and mark the rollback target of each step.

Input.

Step	Type	Rollback target
Expand	migration	drop empty column
Dual-write	deploy	expand only
Backfill	job	re-runnable
Read cutover	flag	flag off
Stop-old-write	deploy	read-cutover deploy
Contract	migration	none (irreversible)

Code.

Zero-downtime rename pipeline (email -> email_address), 50-pod fleet
====================================================================

T0  migration  ADD COLUMN email_address text            [expand]
               fleet: all pods read/write email .......... OK
T1  deploy     v+1 writes BOTH columns                    [dual-write]
               rolling: v and v+1 both write email ....... OK  (rollback -> T0)
T2  job        backfill email_address (throttled, resumable)
               fleet unchanged ........................... OK  (re-runnable)
T3  deploy     v+2 reads email_address behind flag OFF
               ramp flag 1% -> 100% ...................... OK  (rollback -> flag off)
T4  deploy     v+3 stops writing email
               fleet: nothing reads/writes email ......... OK  (rollback -> T3)
T5  migration  DROP COLUMN email                          [contract]
               irreversible; gated on T4 full rollout .... OK

Step-by-step explanation.

1. T0 (expand) adds the column as its own migration step, before any code depends on it. Old pods ignore it; rollback is dropping an empty column.
2. T1 rolls out dual-write code. During the rolling deploy, some pods are v and some v+1 — both write email, and v+1 also writes email_address, so the schema (which has both) satisfies both. Rollback returns to T0 safely.
3. T2 runs the throttled, resumable backfill as a separate job. It changes no schema or code contract, so it can run and re-run freely; it must finish before T3.
4. T3 deploys read-from-email_address guarded by a feature flag defaulting off, then ramps the flag 1%→100%. Because the column is fully populated (T1+T2) and the code still dual-writes, flipping the flag is safe and instantly reversible.
5. T4 stops writing email; T5 drops it. T5 is the single irreversible step, gated on T4 being fully rolled out across all 50 pods. At every transition the live schema is compatible with both adjacent code versions — the definition of zero downtime.

Output.

Transition	v-code works?	v+1-code works?	Downtime
T0→T1	yes (email)	yes (both)	none
T3 flag ramp	yes (dual-write)	yes (reads new)	none
T4→T5	n/a (v+3 only)	yes (new only)	none

Rule of thumb. Draw the pipeline timeline before you start: expand migration, dual-write deploy, backfill job, flag-guarded read deploy, stop-old deploy, contract migration — each a separate, ordered step with a known rollback target.

Worked example — a feature-flagged read cutover with instant rollback

Detailed explanation. The riskiest moment in an expand/contract is flipping reads to the new column — if the new data is subtly wrong, you want to revert in seconds, not wait for a deploy. A feature flag makes the cutover a runtime switch. Walk through the flag-guarded read path.

• Flag. read_email_address — checked per request.
• Dual-write continues. Both columns stay written, so flipping either way is safe.
• Ramp + rollback. Percentage ramp on the way up; instant flip to off on any problem.

Question. Implement the read path so the source column is chosen by a feature flag, enabling instant rollback.

Input.

State	Reads from	Writes
flag off	email	both
flag on	email_address	both

Code.

# Read path guarded by a runtime feature flag (dual-write stays on).
def get_user_email(conn, user_id: int, flags) -> str:
    column = "email_address" if flags.enabled("read_email_address", user_id) else "email"
    with conn.cursor() as cur:
        cur.execute(f"SELECT {column} FROM public.users WHERE id = %s", (user_id,))
        return cur.fetchone()[0]

# Write path — ALWAYS dual-writes, regardless of the read flag.
def set_user_email(conn, user_id: int, email: str) -> None:
    with conn, conn.cursor() as cur:
        cur.execute("""
            UPDATE public.users
            SET    email = %s, email_address = %s
            WHERE  id = %s
        """, (email, email, user_id))

# Rollout: ramp the flag, watching error rate + data-diff metrics.
#   flags.set_percentage("read_email_address", 1)    # canary
#   flags.set_percentage("read_email_address", 10)
#   flags.set_percentage("read_email_address", 100)
# Rollback on any anomaly (single call, no deploy):
#   flags.set_percentage("read_email_address", 0)

Step-by-step explanation.

1. The read path chooses its source column from the read_email_address flag at request time. Because the flag is evaluated per request (and can be percentage-scoped by user_id), the cutover is a gradual, observable ramp rather than a hard switch.
2. The write path always dual-writes both columns regardless of the flag. This is what makes the flag safely bidirectional: whether reads come from email or email_address, both are kept current, so flipping the flag never reads stale data.
3. Ramping the flag 1%→10%→100% exposes the new read path to a growing slice of real traffic while you watch error rates and a data-diff metric (does email equal email_address for served rows?). Problems surface at 1%, not 100%.
4. Rollback is a single flag call to 0% — no deploy, no restart, effective across the fleet in seconds. Decoupling the cutover from the deploy cycle is exactly why the flag exists.
5. Once the flag has been at 100% with clean metrics for long enough, the "stop writing email" deploy and the contract drop can proceed. Only then is email truly unused.

Output.

Flag value	Read source	Rollback speed
0%	email	n/a (baseline)
1–99%	mixed (ramp)	instant to 0%
100%	email_address	instant to 0%

Rule of thumb. Put the read cutover behind a feature flag and keep dual-writing until it is 100% and proven. The flag turns the scariest step of the migration into a runtime switch you can ramp and revert in seconds.

Worked example — dropping a column safely in two deploys

Detailed explanation. Dropping a column looks trivial but is the most dangerous contract step, because ORMs and SELECT * queries reference columns implicitly. A safe drop is two deploys: first make the code stop referencing the column, then drop it. Walk through it for users.legacy_flag.

• Deploy 1. Mark the column ignored in the ORM / remove all references; deploy across the whole fleet.
• Deploy 2 (migration). DROP COLUMN — safe because nothing references it.
• Why two. A single "drop + deploy" leaves old pods issuing SELECT on a vanished column mid-rollout.

Question. Remove users.legacy_flag with no failed query during the rolling deploy.

Input.

Deploy	Change	Fleet after
1	ORM ignores legacy_flag; no code reads/writes it	some pods still know it, none use it
2	DROP COLUMN legacy_flag	column gone; nothing referenced it

Code.

# DEPLOY 1 (code): tell the ORM to stop selecting/writing the column.
class User(Base):
    __tablename__ = "users"
    id    = Column(BigInteger, primary_key=True)
    email = Column(Text)
    # legacy_flag column is deliberately NOT mapped here anymore, so the
    # ORM stops emitting it in SELECT * / INSERT / UPDATE statements.
    # (Some ORMs: mark it `deferred`/`ignored` instead of removing.)

# Ship deploy 1 to 100% of pods FIRST. Verify no query references legacy_flag:
#   SELECT query FROM pg_stat_statements WHERE query ILIKE '%legacy_flag%';

-- DEPLOY 2 (migration): only after deploy 1 is fully rolled out.
SET lock_timeout = '3s';
ALTER TABLE public.users DROP COLUMN legacy_flag;   -- metadata-only, instant

Step-by-step explanation.

1. Deploy 1 removes every reference to legacy_flag from the code — critically, from the ORM model, because ORMs that SELECT all mapped columns would otherwise error the instant the column is dropped. After deploy 1, no code path reads or writes the column.
2. Deploy 1 must reach 100% of the fleet before the drop. Verifying via pg_stat_statements (no query mentions legacy_flag) confirms every pod has stopped referencing it — the gate for the drop.
3. Deploy 2 runs DROP COLUMN, which is metadata-only and instant (the column is marked dead; space is reclaimed by later VACUUM). Because nothing references the column, the drop breaks nothing.
4. Doing this in one step — dropping the column and deploying the code together — would leave old pods issuing SELECT against a column that no longer exists during the rolling window, producing a burst of errors. The two-deploy split closes that window.
5. The pattern generalizes to dropping tables, constraints, and indexes: first remove all references in code (deploy), verify, then remove the object (migration). Reference-removal always precedes object-removal.

Output.

Timeline	legacy_flag referenced?	legacy_flag exists?	Errors
Before deploy 1	yes	yes	0
After deploy 1 (100%)	no	yes	0
After deploy 2	no	no	0

Rule of thumb. Drop a column in two deploys: stop referencing it (code, including the ORM), verify across the whole fleet, then DROP COLUMN. Never drop a column and deploy the code that stops using it in the same release.

Senior interview question on orchestration

A senior interviewer might ask: "Walk me through orchestrating a zero-downtime rename of orders.status to orders.state across a 50-pod rolling-deployed service, including where each migration step sits in the CI/CD pipeline, how you use a feature flag for the read cutover, how you drop the old column safely, and exactly where rollback is possible at each step."

Solution Using ordered pipeline steps, a dual-write deploy, a flag-guarded read cutover, and a two-deploy drop

Pipeline order (each step is a separate, gated release)
=======================================================
R1  migration  ALTER TABLE orders ADD COLUMN state text;        [expand]
R2  deploy     write BOTH status and state on every mutation;   [dual-write]
               rolling deploy across 50 pods
R3  job        backfill state = status (throttled, resumable)
R4  deploy     read `state` behind flag read_order_state=OFF;   [read cutover]
               ramp flag 1% -> 100%, watch metrics
R5  deploy     stop writing status (ORM drops the mapping)      [stop-old]
R6  migration  ALTER TABLE orders DROP COLUMN status;           [contract]

# R2 dual-write; R4 flag-guarded read (dual-write continues through R4)
def save_order_state(conn, order_id, value):
    with conn, conn.cursor() as cur:
        cur.execute(
            "UPDATE orders SET status = %s, state = %s WHERE id = %s",
            (value, value, order_id))

def read_order_state(conn, order_id, flags):
    col = "state" if flags.enabled("read_order_state", order_id) else "status"
    with conn.cursor() as cur:
        cur.execute(f"SELECT {col} FROM orders WHERE id = %s", (order_id,))
        return cur.fetchone()[0]

-- R1 expand and R6 contract (R6 gated on R5 at 100%)
SET lock_timeout = '3s';
ALTER TABLE public.orders ADD COLUMN state text;    -- R1 (metadata-only)
-- ... R2..R5 ...
ALTER TABLE public.orders DROP COLUMN status;       -- R6 (metadata-only)

Step-by-step trace.

Step	Kind	Fleet compatibility	Rollback target
R1 expand	migration	old code ignores state	drop empty column
R2 dual-write	deploy	both versions write status	R1
R3 backfill	job	no contract change	re-run
R4 read cutover	deploy + flag	dual-write covers both	flag → 0%
R5 stop-old	deploy	nothing reads status	R4
R6 contract	migration	nothing references status	none

After the six ordered steps, orders.state fully replaces orders.status with no failed query across the 50-pod fleet. Rollback is available at every step except the final contract: R1 rolls back to an empty-column drop, R2–R4 to the previous release or the flag, R5 to R4 — and R6 is deliberately gated on R5 being fully rolled out so the irreversible step is safe.

Output:

Property	One-step RENAME	Orchestrated expand/contract
Failed queries during deploy	burst (old pods break)	zero
Read cutover reversible	no	yes (flag, seconds)
Column drop safe	no (SELECT * breaks)	yes (two-deploy drop)
Rollback points	none	every step but contract
Longest lock	metadata (but breaking)	microseconds, non-breaking

Why this works — concept by concept:

• Expand before code, contract after — the additive ADD COLUMN ships before any code uses it and the DROP COLUMN ships after every code path stops referencing it, so the live schema always satisfies both code versions a rolling deploy runs.
• Dual-write through the cutover — writing both status and state until the very end means reads can come from either column safely, which is what makes the flag reversible and the backfill's finish line real.
• Feature-flag read cutover — flipping reads behind a runtime flag turns the riskiest step into a ramp-and-revert switch effective in seconds, decoupled from the deploy cycle.
• Two-deploy drop — removing the ORM mapping (deploy) before DROP COLUMN (migration) closes the window where an old pod would SELECT a vanished column, so the contract breaks nothing.
• Cost — six ordered releases instead of one, plus a throttled backfill and a feature flag, in exchange for zero failed queries and a rollback target at every step but the last. The extra pipeline steps are the price of zero downtime, and they are cheap next to an outage.

Design
Topic — design
Design problems on deploy orchestration

Practice →

ETL
Topic — etl
ETL problems on migration pipelines

Practice →

---

Cheat sheet — zero-downtime migration recipes

• The lock hierarchy that matters. In Postgres, ALTER TABLE mostly takes ACCESS EXCLUSIVE, which blocks even SELECT. A DDL blocked waiting for that lock also blocks every query behind it — the lock-queue pileup — so a slow read plus a fast DDL freezes the whole table. Always SET lock_timeout = '3s' on DDL and retry with backoff so a migration fails fast instead of piling up the queue.
• Metadata-only vs rewrite (Postgres). Metadata-only (safe): ADD COLUMN nullable or with a constant default (PG11+), DROP COLUMN, RENAME COLUMN, ADD CONSTRAINT ... NOT VALID. Rewrite (dangerous): ALTER COLUMN ... TYPE, ADD COLUMN ... DEFAULT <volatile>, SET LOGGED/UNLOGGED. Full-scan-under-strong-lock (dangerous): SET NOT NULL, validating ADD CHECK/ADD FOREIGN KEY. Test on a copy and watch relfilenode.
• Add a NOT NULL column safely. ADD COLUMN col type DEFAULT <constant> (metadata-only) → batched backfill of history → ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT c (weak lock) → ALTER COLUMN col SET NOT NULL (reuses the validated check, PG12+) → DROP CONSTRAINT c.
• Expand/contract in three phases. Expand: add the new shape additively. Migrate: dual-write both shapes, backfill history, switch reads. Contract: drop the old shape. Every deploy stays backward compatible so a rolling deploy running N-1 and N never breaks.
• Rename a column. Never ALTER ... RENAME COLUMN on a live column. Add new column → dual-write → backfill → switch reads (flag) → stop writing old → drop old. Five safe steps beat one breaking rename.
• Widen a primary key (int → bigint). Add id_big bigint → BEFORE INSERT/UPDATE trigger copying id → CREATE UNIQUE INDEX CONCURRENTLY on it → batched backfill → swap the PK with ADD CONSTRAINT ... PRIMARY KEY USING INDEX (microsecond lock, reuses the prebuilt index) → drop old column and trigger.
• Batched backfill template. Loop over PK ranges id > lo AND id <= lo+step, WHERE new_col IS NULL (idempotent), COMMIT each batch (releases locks, frees dead tuples), pg_sleep between batches (throttle). Never UPDATE a whole large table in one statement — it locks, bloats, and spikes replication lag.
• Dual-write + backfill division. A BEFORE INSERT/UPDATE trigger (installed before the backfill) owns new rows; the batched loop owns historical rows; the shared IS NULL predicate is the seam so the two never race. Verify SELECT count(*) WHERE new_col IS NULL = 0 before promoting to NOT NULL.
• Throttle on replication lag. Before each backfill batch, check pg_stat_replication (pg_wal_lsn_diff(sent_lsn, replay_lsn)); sleep if any replica exceeds your alert threshold. Commit the watermark atomically with the batch so a crash resumes instead of restarting.
• Postgres online-DDL primitives. CREATE INDEX CONCURRENTLY (never plain CREATE INDEX), DROP INDEX CONCURRENTLY, NOT VALID + VALIDATE for constraints, and pg_repack for online table rewrites / bloat removal (needs ~2× disk). A failed concurrent index leaves an INVALID index — drop and retry.
• MySQL online-DDL. Prefer ALGORITHM=INSTANT/INPLACE with explicit LOCK=NONE (errors instead of silently falling back to COPY); for big changes use gh-ost (triggerless, binlog-based, throttleable, postpone-able cut-over) or pt-online-schema-change (trigger-based). Both build a shadow table, chunk-copy, sync, and atomic-rename.
• Gate with a linter in CI. squawk (Postgres) or strong_migrations (Rails) statically reject unsafe DDL — non-concurrent index, direct SET NOT NULL, volatile default, type change — on every PR. The pipeline enforces the rules so they do not depend on who reviews.
• Orchestrate against the deploy. Expand DDL before the code that uses it; contract DDL after the code that stops using it is at 100%; read cutover behind a feature flag (ramp 1%→100%, revert in seconds); drop a column in two deploys (ORM ignore, then DROP COLUMN). Draw the timeline with rollback targets before you start.

Frequently asked questions

What are zero-downtime schema changes?

zero-downtime schema changes are database migrations performed while the application keeps serving reads and writes at full throughput, with no maintenance window and no failed queries — even during a rolling deploy where old and new code versions run simultaneously. The discipline rests on three pillars: making changes additive so old code still works (the expand contract pattern), moving data with chunked backfills and dual writes instead of one giant UPDATE, and using online ddl primitives (CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE, gh-ost) so no statement holds a table-blocking lock for time proportional to table size. The goal is that at no instant does the running system encounter a schema it cannot handle.

Why does a normal ALTER TABLE cause downtime?

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock in Postgres (or a rebuild/metadata lock in MySQL) that conflicts with every read and write. Two things then go wrong: some ALTERs rewrite or scan the whole table under that lock (minutes to hours on a large table), and — more insidiously — even an "instant" DDL that is waiting for its lock behind a long-running query will block every query that arrives after it, freezing the entire table for as long as the slow query runs. This lock-queue pileup is why a "fast" migration can still take production down. The fixes are lock_timeout (fail fast instead of queuing), splitting rewrites into expand/contract plus a background backfill, and using concurrent/online primitives.

What is the expand/contract (parallel-change) pattern?

The expand contract pattern turns a breaking schema change into three backward-compatible phases. Expand: add the new column/table/index additively, so code that knows only the old schema keeps working and the database supports both shapes at once (a blue-green schema). Migrate: dual-write to both shapes, backfill historical rows, then switch reads to the new shape. Contract: once nothing reads or writes the old shape, drop it. Because every individual step is backward compatible, a rolling deploy running the old and new code versions together never sees a schema it cannot cope with, and you can roll back at every step except the final drop. Renames, type changes, column splits, and NOT NULL additions are all special cases of this pattern.

How do I backfill a huge table without downtime?

Never run a single UPDATE over the whole table — it takes one enormous lock, writes gigabytes of WAL at once, bloats the table with dead tuples, and spikes replication lag. Instead, run a chunked backfill: loop over primary-key ranges (id > lo AND id <= lo + step), update a few thousand rows per batch with a WHERE new_col IS NULL predicate for idempotency, COMMIT each batch to release locks and free dead tuples, and pg_sleep between batches to throttle. Keep new rows correct with a dual-write trigger installed before the backfill starts, check replication lag before each batch and pause if it is high, and commit a durable watermark atomically with each batch so a crash resumes instead of restarting. Verify count(*) WHERE new_col IS NULL = 0 before promoting the column to NOT NULL.

What online DDL tools should I use for Postgres and MySQL?

For Postgres, the engine itself is your toolkit: CREATE INDEX CONCURRENTLY (never plain CREATE INDEX), ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT for foreign keys and checks, metadata-only ADD COLUMN, and SET NOT NULL via a pre-validated CHECK. For online table rewrites and bloat removal without a long lock, use pg_repack. For MySQL, prefer in-engine ALGORITHM=INSTANT/INPLACE with explicit LOCK=NONE, and for large changes use gh-ost (triggerless, binlog-based, with throttling and a postpone-able atomic cut-over) or pt-online-schema-change (trigger-based shadow table). Across both, put a safe-migration linter (squawk, strong_migrations) in CI so unsafe DDL is blocked before it merges.

How do I rename or drop a column safely across a rolling deploy?

You never rename a live column with ALTER ... RENAME COLUMN, and you never drop a column in the same release that stops using it. To rename, run expand/contract: add the new column, dual-write both, backfill, switch reads (behind a feature flag for instant rollback), stop writing the old column, then drop it. To drop, use a two-deploy sequence: first ship code that removes every reference to the column — including the ORM mapping, since ORMs often SELECT all known columns — and roll it out to 100% of the fleet; then, in a later migration, run DROP COLUMN. Verifying via pg_stat_statements that no query references the column is the gate before the drop. This ordering guarantees no old pod ever issues a query against a column that has vanished mid-deploy.

Practice on PipeCode

• Drill the database practice library → for the locks, DDL, constraints, and indexing problems that zero-downtime migrations live and die on.
• Rehearse on the design practice library → for the expand/contract sequencing, deploy-orchestration, and rollout-safety topology questions senior interviewers open with when a migration is on the table.
• Sharpen the pipeline axis with the ETL practice library → for the chunked-backfill, dual-write, and reprocessing patterns that make a migration a real throttled job rather than a single reckless UPDATE.
• Layer in the data-transformation practice library → for the derived-column, type-change, and column-split drills that are the data half of every expand/contract migration.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the expand/contract decision matrix (metadata-only vs rewrite, lock class, online primitive, deploy order) against real graded inputs.

Lock in zero-downtime migration muscle memory

Docs explain the syntax. PipeCode drills explain the decision — when a metadata-only ALTER is safe and when it pileups the lock queue, when to reach for expand/contract instead of a raw rename, when a batched backfill needs a lag-aware throttle, when CREATE INDEX CONCURRENTLY and gh-ost earn their keep, and where in the deploy pipeline each step belongs. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice database problems →
Practice design problems →