Blue-Green & Zero-Downtime Data Deployments: Shadow Tables, Swap & Reconciliation

A blue-green deployment is the release model that lets you swap a running system for a new version with no window where users see errors — and it is trivial to reason about when the system is stateless, because you just stand up an identical green fleet, warm it, and flip the load balancer. The problem is that databases are not stateless: you cannot instantly clone a multi-terabyte table, you cannot pause writes for the ten minutes an ALTER TABLE would lock the row, and you cannot afford a cutover instant where one query reads the old shape and the next reads the new one. That is why the hard, senior slice of zero-downtime release engineering lives on the data tier — schema changes, column-type migrations, table splits, and warehouse rebuilds — where the two environments cannot be duplicated for free and the moment of cutover has to be provably atomic.

This guide is the walkthrough for shipping stateful changes the blue-green way without an outage, framed the way interviewers actually probe it: the expand-contract (parallel-change) pattern that turns a scary in-place migration into a sequence of individually-safe steps, the shadow tables you build alongside the live table and fill with a throttled backfill, the single-transaction table swap that guarantees no query ever sees a half-migrated state, the tiered reconciliation that proves the green copy equals the blue one before anybody cuts over, and the rollback plan plus decommission gate that keeps the reverse swap as cheap as the forward one. 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 SQL practice library →, rehearse the migration mechanics on the design practice library →, and stress-test the checks on the data validation practice library →.

---

On this page

• Why blue-green is the hard problem on the data tier
• Shadow tables and backfill
• Atomic swap and cutover
• Reconciliation and the validation gate
• Rollback and release engineering
• Cheat sheet — blue-green data deployment recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why blue-green is the hard problem on the data tier

Two environments you cannot duplicate for free — the choice binds every downstream consumer

The one-sentence invariant: a zero-downtime data deployment is a picking exercise between changing the live object in place, building a shadow copy and swapping it, or evolving the schema through additive expand-contract steps — and each choice trades how much state you must duplicate against how atomic the cutover is, how you prove the new copy is correct, and how cheaply you can roll back — with the constraint that unlike a stateless service, you can never pause the writers while you decide. The pattern you pick in the migration plan becomes the pattern that pages you at 2am, because every consumer, dashboard, and replica hard-codes assumptions about the shape and identity of the object you are changing — whether a column exists, whether the primary key is INT or BIGINT, whether the table name still points at the data they expect.

The four axes interviewers actually probe.

• State-duplication cost. A stateless green fleet is a docker run away; a green table is a terabyte of rows you must copy while writers keep mutating the source. The entire difficulty of blue-green on data is that the second environment cannot be conjured instantly — you build it with a chunked backfill plus a keep-in-sync mechanism, and it costs 2× storage for the duration. Interviewers open here because a candidate who says "just spin up green" has never migrated a large table.
• Cutover atomicity. The instant you flip from blue to green must be atomic from every reader's perspective — no query may see the old table for one row and the new table for the next. In-place ALTER TABLE fails this because it holds a long exclusive lock; a naive "drop old, rename new" fails it because there is a window with no table at all. The senior answer is a single-transaction double-rename or a view swap where both changes commit together or neither does.
• Reconciliation proof. You do not cut over on faith. Before the swap, the green copy must be proven equal to blue — a tiered reconcile of row counts, then per-column aggregate checksums, then a full row-hash diff — because a backfill bug that drops 0.1% of rows is invisible until an auditor finds it. Weak candidates "spot-check a dashboard"; senior candidates gate the cutover on N clean reconcile cycles.
• Rollback reversibility. A blue-green deploy is only as safe as its reverse. If you drop blue the instant you promote green, you have a one-way door with no rollback; if you keep blue intact and dual-write during a soak window, rolling back is a reverse rename that takes minutes and loses no data. The senior answer never decommissions the old copy until a gate proves the new one is correct under real traffic.

The pattern family — three tools, one goal.

• Expand-contract (parallel change) is the additive discipline: never change a column in place. Expand by adding the new column / table / index, migrate by dual-writing and backfilling until old and new agree, then contract by removing the old one — each step individually deployable and reversible. This is the backbone of every zero-downtime schema change.
• Shadow table + swap is the heavy-migration tool: when the change is too big to do in place (column type change on a huge table, re-partitioning, storage-engine change), build a whole new table beside the live one, keep it in sync with triggers or CDC, backfill history, reconcile, then swap names atomically. gh-ost, pt-online-schema-change, and Postgres logical-replication cutovers all implement this.
• Blue-green at the connection tier is the whole-database version: two full database instances (blue serving, green upgraded), synced by logical replication, cut over by flipping a proxy/DNS alias — the model for major-version upgrades and cross-region moves.

What interviewers listen for.

• Do you name expand-contract as the default and reach for a shadow-table swap only when the change is too big to do additively? — senior signal.
• Do you insist the cutover is atomic — a single-transaction rename or view swap, never "drop then create"? — required answer.
• Do you reconcile before you cut, and gate the swap on repeated clean cycles rather than one spot-check? — required answer.
• Do you keep blue authoritative and rollback-ready through a soak window and decommission only behind a gate? — senior signal.
• Do you describe the change as "a sequence of individually-safe, reversible steps" rather than "a migration script we run in the maintenance window"? — senior signal.

Worked example — the four-strategy comparison table

Detailed explanation. The single most useful artifact for a zero-downtime-deployment interview is a memorised comparison of the ways to change a live table. Every senior migration discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for the canonical hard case: widening orders.id from INT to BIGINT on a live 2-billion-row Postgres table.

• The change. public.orders.id INT is about to overflow at 2.1 billion; it must become BIGINT.
• The constraint. The table serves live checkout traffic; no maintenance window is available.
• The consumers. The app writes it, three read replicas serve reports, and a Debezium connector streams it to the warehouse.

Question. Build the four-strategy comparison for the id widening and pick the one you would ship.

Input.

Strategy	State to duplicate	Cutover atomicity	Rollback	Downtime
In-place ALTER TABLE	none	N/A (holds lock)	hard (revert = another ALTER)	minutes–hours of lock
Expand-contract (new column)	one column + backfill	additive, per-step	drop the new column	none
Shadow table + swap	whole table + backfill	single-txn rename	reverse rename	none (brief lock)
Blue-green at connection tier	whole database	flip proxy alias	flip alias back	none

Code.

-- The naive in-place change — DO NOT run this on a live large table.
-- On Postgres a type change rewrites every row and holds ACCESS EXCLUSIVE
-- for the whole rewrite: every reader and writer blocks until it finishes.
ALTER TABLE public.orders
    ALTER COLUMN id TYPE BIGINT;      -- full table rewrite + long exclusive lock

-- Why it is unshippable: with 2e9 rows this locks the table for the entire
-- rewrite (potentially hours). Every SELECT, INSERT, UPDATE queues behind it.
-- That is a full outage, i.e. the opposite of a blue-green deployment.

Step-by-step explanation.

1. The in-place ALTER TABLE ... TYPE is the tempting one-liner and the wrong answer. On Postgres a column-type change that is not binary-coercible rewrites the entire heap and holds an ACCESS EXCLUSIVE lock for the duration, so the table is unavailable for the whole rewrite — an outage proportional to table size. It also cannot be rolled back cheaply; reverting is another full rewrite.
2. Expand-contract adds a new id_big BIGINT column, dual-writes it, backfills historical rows in batches, swaps the primary key, then drops the old column — every step individually safe and reversible, and none of them holds a long lock. This is the default for column-level changes.
3. The shadow-table swap builds a whole new orders_new table with id BIGINT from the start, keeps it in sync, backfills, reconciles, and renames it into place in a single transaction. It is heavier than expand-contract but is the right tool when the change touches the whole row shape or the primary key itself.
4. Blue-green at the connection tier duplicates the entire database, upgrades green, replicates blue→green, and flips a proxy alias. Reserve it for engine upgrades and cross-region moves — it is overkill for one column.
5. The choice is driven by blast radius: change a column → expand-contract; change the table's identity, partitioning, or PK → shadow swap; change the engine/version/region → connection-tier blue-green.

Output.

Scenario	Recommended strategy	Why
Widen a column type in place	expand-contract	additive, reversible, no lock
Change PK / re-partition / re-cluster	shadow table + swap	whole-row rewrite; atomic rename
Postgres major-version upgrade	connection-tier blue-green	whole instance; flip the alias
Add a nullable column	expand only (no contract)	trivially safe; no backfill of nulls

Rule of thumb. Never change a live table in place if the change rewrites rows or holds a long lock. Pick by blast radius: column → expand-contract, table identity → shadow swap, whole instance → connection-tier blue-green. Write the table on a whiteboard first; the strategy falls out of the constraints.

Worked example — what interviewers actually probe

Detailed explanation. The senior zero-downtime-deployment interview has a predictable structure: the interviewer opens with an ambiguous ask ("we need to change this column on a huge live table — how?"), then progressively narrows to test whether you know the axes. Candidates who name expand-contract and the atomic swap score highest; candidates who describe "a migration script in the maintenance window" score lowest. Walk through the grading rubric.

• Ambiguous opener. "How would you change orders.id from INT to BIGINT with no downtime?" — invites you to name a pattern.
• Follow-up 1. "How do you build the second copy while writes keep coming?" — probes state-duplication.
• Follow-up 2. "What exactly happens at the instant of cutover?" — probes atomicity.
• Follow-up 3. "How do you know the new copy is correct?" — probes reconciliation.
• Follow-up 4. "It's live and something's wrong — now what?" — probes rollback.

Question. Draft a 5-minute senior answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Pattern named	"run a migration script"	"expand-contract, or a shadow-table swap for a whole-row change"
Duplication	"copy the table"	"chunked backfill + trigger/CDC keep-in-sync, throttled on replica lag"
Cutover	"rename it"	"single-transaction double-rename with lock_timeout + retry"
Correctness	"check a dashboard"	"tiered reconcile: count → aggregate checksum → row-hash, gate on N clean cycles"
Rollback	"restore a backup"	"keep blue, dual-write during soak, reverse rename in minutes"

Code.

Senior zero-downtime deployment answer template (5 minutes)
===========================================================

Minute 1 — name the pattern up front
  "For a column change I'd use expand-contract; for a whole-row or
   PK change I'd build a shadow table and swap it atomically."

Minute 2 — build the green copy online
  "Create the shadow table at the target schema. Attach AFTER
   INSERT/UPDATE/DELETE triggers so every live write is mirrored.
   Backfill history in bounded batches, throttling on replica lag
   so the migration never starves production."

Minute 3 — atomic cutover
  "Swap inside one transaction: RENAME orders -> orders_old and
   orders_new -> orders together, under a short lock_timeout so we
   fail fast and retry instead of stampeding the lock queue.
   Both renames commit together; no reader sees a half-state."

Minute 4 — prove it first
  "Before the swap I gate on reconciliation: row counts, then
   per-column aggregate checksums, then a row-hash diff both
   directions. Cutover only after N consecutive clean cycles."

Minute 5 — rollback + decommission
  "Keep orders_old intact and dual-write during a soak window so
   rollback is a reverse rename that loses nothing. Decommission
   blue only behind a gate: N clean post-cutover cycles, sign-off,
   a soak period, and a restore-tested backup."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming expand-contract and the shadow-swap immediately signals you are a decision-maker, not a script-runner. Weak candidates dive into tools ("we'd use Flyway…") before naming the pattern.
2. Minute 2 addresses state-duplication before being asked. Saying "trigger keep-in-sync plus throttled backfill" pre-empts the trap where you claim zero-downtime but forget that writers keep mutating the source while you copy it.
3. Minute 3 is the atomicity probe. "Single-transaction double-rename under a short lock_timeout" is the exact senior phrasing; "just rename it" leaves a window and stampedes the lock queue.
4. Minute 4 is the correctness probe. Every serious migration reconciles before cutover; naming the three tiers and the "N clean cycles" gate shows you have shipped one that failed reconcile and caught it.
5. Minute 5 covers rollback and decommission — the reliability axis. "Keep blue, dual-write, reverse rename, gate the drop" is qualitatively different from "restore a backup," which implies you already took an outage.

Output.

Grading criterion	Weak score	Senior score
Names expand-contract / shadow swap in minute 1	rare	mandatory
Names online keep-in-sync + throttle	rare	required
Names single-txn atomic swap	occasional	mandatory
Names tiered reconcile gate	rare	senior signal
Names keep-blue rollback + gate	rare	senior signal

Rule of thumb. The senior zero-downtime answer is a 5-minute monologue that walks build → swap → reconcile → rollback without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the strategy" decision tree

Detailed explanation. Given a change request against a live table, the senior engineer runs a short decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a change and you can walk the tree out loud. Walk through the tree with three canonical scenarios: adding a nullable column, widening a primary key, and upgrading the Postgres major version.

• Q1. Is the change purely additive (new nullable column, new index built CONCURRENTLY)? → yes = expand only; no = go to Q2.
• Q2. Does the change rewrite the whole row shape, the primary key, or the partitioning? → yes = shadow table + swap; no = go to Q3.
• Q3. Is it a column-level change on existing data (type change, NOT NULL, default)? → yes = expand-contract (add column, dual-write, backfill, swap, drop); no = go to Q4.
• Q4. Is it an engine, major-version, or region change to the whole database? → yes = connection-tier blue-green; no = re-examine — it is probably additive after all.

Question. Walk the decision tree for the three scenarios and record the strategy each ends up with.

Input.

Scenario	Q1 (additive?)	Q2 (whole-row/PK?)	Q3 (column change?)	Q4 (whole instance?)
Add nullable notes column	yes	—	—	—
Widen PK INT → BIGINT	no	yes	—	—
Postgres 14 → 16 upgrade	no	no	no	yes

Code.

# Decision-tree helper (illustrative)
def pick_deployment_strategy(is_additive: bool,
                             rewrites_row_or_pk: bool,
                             is_column_change: bool,
                             is_whole_instance: bool) -> str:
    """Return the zero-downtime deployment strategy for a schema change."""
    if is_additive:
        return "expand only (add + build CONCURRENTLY)"
    if rewrites_row_or_pk:
        return "shadow table + atomic swap"
    if is_column_change:
        return "expand-contract (add col, dual-write, backfill, swap, drop)"
    if is_whole_instance:
        return "connection-tier blue-green (replicate + flip alias)"
    raise ValueError("re-examine: likely additive after all")


print(pick_deployment_strategy(True,  False, False, False))
# -> 'expand only (add + build CONCURRENTLY)'

print(pick_deployment_strategy(False, True,  False, False))
# -> 'shadow table + atomic swap'

print(pick_deployment_strategy(False, False, False, True))
# -> 'connection-tier blue-green (replicate + flip alias)'

Step-by-step explanation.

1. Scenario 1 — adding a nullable notes column. Q1 = yes → expand only. A nullable column with no default is a metadata-only change in modern Postgres/MySQL; no backfill, no contract phase, trivially reversible with DROP COLUMN.
2. Scenario 2 — widening the primary key from INT to BIGINT. Q1 = no, Q2 = yes (the PK is the row's identity, referenced by FKs and replicas) → shadow table + swap. You cannot safely mutate a live PK in place; build the whole table at BIGINT and swap.
3. Scenario 3 — upgrading Postgres 14 → 16. Q1/Q2/Q3 = no, Q4 = yes → connection-tier blue-green. Stand up a green 16 instance, logically replicate blue→green, reconcile, then flip the proxy alias; roll back by flipping it back.
4. The tree is ordered by increasing blast radius: additive is cheapest, expand-contract handles column data, shadow swap handles table identity, connection-tier handles the whole instance. Always take the cheapest branch that fully covers the change.
5. If nothing matches, the change is almost always additive in disguise (e.g. "change a default" is add-new-default + backfill + drop-old-default) — decompose it into expand-contract steps rather than reaching for an in-place rewrite.

Output.

Scenario	Strategy	Rollback
Add nullable column	expand only	DROP COLUMN
Widen PK INT → BIGINT	shadow table + swap	reverse rename
Postgres 14 → 16	connection-tier blue-green	flip alias back

Rule of thumb. The four-question decision tree is a whiteboard-friendly answer. Take the cheapest branch that covers the change, and decompose anything that looks like an in-place rewrite into additive expand-contract steps first.

Senior interview question on zero-downtime deployment strategy

A senior interviewer often opens with: "You inherit a live 2-billion-row orders table whose INT primary key is about to overflow. You must widen it to BIGINT with no downtime, no lost writes, and a clean rollback if anything looks wrong. Walk me through the strategy, the mechanism, and the failure modes you'd guard against."

Solution Using an expand-contract migration with a shadow PK column, dual-write, and a batched backfill

-- EXPAND: add the wide column and a backfill-progress marker. Nullable +
-- no default = fast metadata-only change, no table rewrite, no long lock.
ALTER TABLE public.orders ADD COLUMN id_big BIGINT;      -- instant, nullable

-- Dual-write trigger: every new/updated row fills id_big from id so new
-- writes are already correct while we backfill the history behind them.
CREATE OR REPLACE FUNCTION public.orders_fill_id_big() RETURNS TRIGGER AS $$
BEGIN
    NEW.id_big := NEW.id;          -- keep the wide column in lock-step
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

# MIGRATE: backfill history in bounded batches, throttling on replica lag
# so the migration never starves live checkout traffic.
import time
import psycopg2

BATCH = 20_000
LAG_CEILING_BYTES = 50 * 1024 * 1024   # pause if any replica is >50MB behind

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 int(cur.fetchone()[0])

def backfill_id_big(conn) -> None:
    lo = 0
    while True:
        with conn.cursor() as cur:
            # bounded, keyset-paged UPDATE — never a full-table scan
            cur.execute("""
                WITH batch AS (
                    SELECT id FROM public.orders
                    WHERE  id > %s AND id_big IS NULL
                    ORDER  BY id
                    LIMIT  %s
                )
                UPDATE public.orders o
                SET    id_big = o.id
                FROM   batch b
                WHERE  o.id = b.id
                RETURNING o.id
            """, (lo, BATCH))
            done = cur.fetchall()
        conn.commit()                      # short transaction per batch
        if not done:
            break                          # backfill complete
        lo = max(r[0] for r in done)

        with conn.cursor() as cur:         # be a good citizen: throttle on lag
            while replica_lag_bytes(cur) > LAG_CEILING_BYTES:
                time.sleep(1.0)

-- CONTRACT: promote id_big to be the primary key, then drop the old column.
-- Build the unique index CONCURRENTLY first (no long lock), then swap the PK
-- inside one short transaction, then drop the narrow column.
CREATE UNIQUE INDEX CONCURRENTLY orders_id_big_uk ON public.orders (id_big);

BEGIN;
SET lock_timeout = '3s';                                  -- fail fast, retry
ALTER TABLE public.orders DROP CONSTRAINT orders_pkey;
ALTER TABLE public.orders
    ALTER COLUMN id_big SET NOT NULL,
    ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_big_uk;
ALTER TABLE public.orders DROP COLUMN id;                 -- contract: old col gone
ALTER TABLE public.orders RENAME COLUMN id_big TO id;
COMMIT;

Step-by-step trace.

Step	State of orders	Live traffic impact
Expand	id INT PK + new id_big (null)	none (metadata-only add)
Trigger on	new writes fill id_big	~microseconds per write
Backfill	history fills batch by batch	throttled; pauses on replica lag
Reconcile	COUNT(*) WHERE id_big IS NULL = 0	read-only check
Contract	PK moves to id_big, old id dropped	one short lock under lock_timeout
Rename	id_big renamed to id	inside the same short txn

After the migration, every row has a BIGINT identity, no writer was ever blocked for more than the sub-second contract transaction, and at every intermediate moment the table was fully readable and writable. If the contract transaction cannot grab its lock within 3 seconds it aborts cleanly and is retried on a quieter moment — the rest of the migration is untouched.

Output:

Metric	In-place ALTER	Expand-contract
Longest lock held	full rewrite (hours)	< 1 s (contract txn)
Writes blocked	all, for hours	none
Rollback before contract	another full ALTER	DROP COLUMN id_big
Backfill impact on prod	N/A (offline)	throttled, lag-bounded
Downtime	hours	zero

Why this works — concept by concept:

• Expand-contract — never mutate a column in place; add the target column (expand), converge old and new (migrate), remove the source (contract). Each phase is independently deployable and reversible, so the migration is a sequence of individually-safe steps rather than one all-or-nothing rewrite.
• Dual-write trigger — the BEFORE INSERT OR UPDATE trigger keeps id_big correct for new writes the instant it is attached, so the backfill only has to chase historical rows. Without it the backfill would race live writes and never converge.
• Batched, keyset-paged backfill — updating in bounded LIMIT batches keyed on id > lo keeps each transaction short (no long lock, no bloat blowup) and never does a full-table scan, so the migration progresses in constant memory regardless of table size.
• Lag throttling — pausing the backfill whenever pg_stat_replication shows a replica falling behind makes the migration a background citizen: it yields to production instead of saturating IOPS and starving the checkout path.
• Cost — one extra column and index for the duration (≈ the column's width × rows, reclaimed at contract), plus O(rows / batch) short transactions. Versus the in-place ALTER, that trades a multi-hour outage for zero downtime at the price of transient 1-column storage and a longer wall-clock migration.

Design
Topic — design
Design problems on zero-downtime migrations

Practice →

SQL Topic — sql SQL schema-change and DDL problems

Practice →

---

2. Shadow tables and backfill

Build green beside blue — a full replica at the target schema, filled online while writers keep going

The mental model in one line: a shadow table is a brand-new table you create alongside the live one at the target schema, keep continuously in sync with every live write via a trigger (or a CDC tail), and fill with history through a throttled, chunked backfill — so that when it is complete it is a byte-for-byte-correct replica of blue at the new shape, ready to swap in, and at no point did any writer block. This is the engine inside gh-ost, pt-online-schema-change, and every Postgres logical-replication cutover; every senior DE has built one by hand at least once when the tool did not fit.

The four axes for shadow tables.

• Permission. CREATE TABLE plus CREATE TRIGGER on the source schema (or a CDC/replication grant if you sync that way). A higher bar than SELECT-only but lower than a full engine upgrade — most managed tiers allow it.
• Backfill throughput vs load. The backfill copies history in bounded batches; batch size and inter-batch sleep are the throttle. Too aggressive starves production IOPS; too timid and a large table takes days. The senior knob is "throttle on replica lag / active-session count," not a fixed sleep.
• Keeping in sync. While you backfill, live writes keep mutating blue. A trigger that mirrors every INSERT/UPDATE/DELETE into the shadow (dedup-safe via upsert) is the simplest; a CDC tail is the lower-write-amplification alternative for very hot tables.
• Storage. For the duration you carry 2× the table's storage plus the shadow's indexes. Bounded and temporary, reclaimed at the swap — but you must have the headroom before you start.

The shadow table shape — what it must get right.

• Target schema from the start. The shadow is created with the new column types, PK, partitioning, or clustering. That is the whole point — you never migrate the shadow, you build it already-correct.
• Same PK space. The shadow shares the source's primary-key values so the sync trigger can upsert by PK and the backfill can key-page by PK.
• Indexes built as you go, or after backfill. Building indexes before the backfill slows every batch; building them after is faster but delays readiness. For huge tables, build them after the bulk copy, then a final catch-up.
• No FKs to the shadow yet. Foreign keys and grants are re-attached to the shadow just before the swap, not during the build, so the build stays a pure copy.

The keep-in-sync mechanism — trigger vs CDC.

• Trigger dual-write. An AFTER INSERT OR UPDATE OR DELETE trigger on blue upserts/deletes the same row in green. Simple, transactional (the mirror commits with the source), and the tool-of-choice for pt-online-schema-change. Cost: a bounded per-DML write amplification.
• CDC tail. A logical-replication or Debezium reader tails blue's WAL and applies changes to green. Zero source-table trigger overhead, at the cost of a separate moving part and eventual-consistency lag you must drain before the swap.
• Idempotency. Whichever you pick, sync writes must be idempotent (upsert by PK, not blind insert) so that a row touched by both the backfill and a concurrent live write converges to the same value regardless of order.

Common interview probes on shadow tables.

• "How do you keep the shadow current while you backfill?" — trigger dual-write or CDC tail, idempotent by PK.
• "How do you stop the backfill from taking down production?" — bounded batches throttled on replica lag / load, not a fixed sleep.
• "What order — backfill then sync, or sync then backfill?" — attach the sync first, then backfill; otherwise writes during the gap are lost.
• "How much storage do you need?" — ~2× the table plus shadow indexes, for the duration.

Worked example — build the shadow table and attach the sync trigger

Detailed explanation. The canonical shadow build for the orders INT→BIGINT case: create orders_new at the target schema, attach an idempotent sync trigger to orders before backfilling, so no live write is ever missed. Order matters — sync first, backfill second. Build the whole thing from scratch.

• Shadow table. orders_new with id BIGINT PK, same columns otherwise.
• Sync trigger. AFTER INSERT OR UPDATE OR DELETE ON orders → upsert/delete into orders_new.
• Ordering. Trigger attached first; then the backfill copies history.

Question. Write the shadow DDL and the idempotent sync trigger, and explain why the trigger must be attached before the backfill starts.

Input.

Object	Purpose
orders_new	shadow table at target schema (id BIGINT)
orders_sync_to_new()	trigger fn: mirror every DML into the shadow
trg_orders_sync	trigger binding on orders

Code.

-- 1. Shadow table at the TARGET schema (id is BIGINT from birth)
CREATE TABLE public.orders_new (
    id           BIGINT       PRIMARY KEY,
    customer_id  BIGINT       NOT NULL,
    total_cents  BIGINT       NOT NULL,
    status       TEXT         NOT NULL,
    created_at   TIMESTAMPTZ  NOT NULL,
    updated_at   TIMESTAMPTZ  NOT NULL
);

-- 2. Idempotent sync trigger function — upsert by PK, never blind insert,
--    so a row touched by both the backfill and a live write converges.
CREATE OR REPLACE FUNCTION public.orders_sync_to_new() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        DELETE FROM public.orders_new WHERE id = OLD.id;
        RETURN OLD;
    ELSE   -- INSERT or UPDATE
        INSERT INTO public.orders_new
            (id, customer_id, total_cents, status, created_at, updated_at)
        VALUES
            (NEW.id, NEW.customer_id, NEW.total_cents, NEW.status,
             NEW.created_at, NEW.updated_at)
        ON CONFLICT (id) DO UPDATE SET
            customer_id = EXCLUDED.customer_id,
            total_cents = EXCLUDED.total_cents,
            status      = EXCLUDED.status,
            created_at  = EXCLUDED.created_at,
            updated_at  = EXCLUDED.updated_at;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 3. Attach the trigger FIRST — before any backfill runs
CREATE TRIGGER trg_orders_sync
AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.orders_sync_to_new();

Step-by-step explanation.

1. orders_new is created with id BIGINT as the primary key from the outset — the shadow is born at the target schema, so there is no second migration to run on it. Every other column matches blue exactly so the swap is a drop-in.
2. The sync trigger upserts by PK using ON CONFLICT (id) DO UPDATE. This idempotency is the crux: during the build, a given row may be written by the historical backfill and by a concurrent live UPDATE, in either order — the upsert guarantees the shadow ends with the latest values regardless of which ran last.
3. DELETE is handled explicitly — a physical delete on blue must remove the row from green, or the shadow accumulates ghosts that fail reconciliation later.
4. The trigger is attached before the backfill starts. This ordering is non-negotiable: if you backfilled first and attached the trigger second, every write that landed on blue during the gap would be absent from green and silently lost. Sync-first, backfill-second closes the window.
5. AFTER ... FOR EACH ROW means the mirror commits atomically with the source write — no dual-write inconsistency between blue and green for live traffic.

Output.

Live DML on orders	Effect on orders_new
INSERT (id=9001, …)	upsert inserts id=9001
UPDATE SET status WHERE id=9001	upsert overwrites id=9001
backfill copies id=42	upsert inserts id=42
concurrent UPDATE id=42	upsert overwrites id=42 (converges)
DELETE id=42	row removed from shadow

Rule of thumb. Attach the idempotent, upsert-by-PK sync trigger before the first backfill batch. Sync-first-backfill-second is the ordering that makes the shadow provably complete; reverse it and you lose every write in the gap.

Worked example — the throttled, chunked backfill

Detailed explanation. A 2-billion-row backfill that runs flat-out will saturate IOPS and page the on-call. The senior backfill is bounded per batch and throttles on a live signal — replica lag or active session count — so it yields to production. Walk through a backfill loop that copies history into the shadow and self-throttles.

• Batch. Keyset-paged by PK, LIMIT 20_000 per transaction.
• Throttle. Pause when replica lag exceeds a ceiling; resume when it drains.
• Idempotency. INSERT ... ON CONFLICT DO NOTHING so re-runs and trigger-races are safe.

Question. Implement the backfill loop with keyset paging and lag-based throttling, and quantify why bounded batches matter.

Input.

Parameter	Value
Source	public.orders (2e9 rows)
Target	public.orders_new
Batch size	20,000
Lag ceiling	50 MB
Conflict policy	DO NOTHING (trigger may have inserted)

Code.

# Throttled, keyset-paged backfill from orders -> orders_new
import time
import psycopg2

BATCH = 20_000
LAG_CEILING = 50 * 1024 * 1024   # bytes

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

def backfill(conn) -> int:
    lo, copied = 0, 0
    while True:
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO public.orders_new
                    (id, customer_id, total_cents, status, created_at, updated_at)
                SELECT id, customer_id, total_cents, status, created_at, updated_at
                FROM   public.orders
                WHERE  id > %s
                ORDER  BY id
                LIMIT  %s
                ON CONFLICT (id) DO NOTHING       -- trigger may already have it
                RETURNING id
            """, (lo, BATCH))
            ids = [r[0] for r in cur.fetchall()]
        conn.commit()                              # short txn per batch

        if not ids:
            # No rows > lo were *scanned*; advance past the copied range or stop.
            with conn.cursor() as cur:
                cur.execute("SELECT max(id) FROM public.orders WHERE id > %s", (lo,))
                nxt = cur.fetchone()[0]
            if nxt is None:
                break
            lo = nxt
            continue

        copied += len(ids)
        lo = max(ids)                              # keyset cursor advances

        with conn.cursor() as cur:                 # throttle: yield to production
            while max_replica_lag(cur) > LAG_CEILING:
                time.sleep(1.0)
    return copied

Step-by-step explanation.

1. The backfill inserts in bounded LIMIT 20_000 batches keyset-paged on id > lo. Keyset paging (not OFFSET) keeps every batch an index range scan of constant cost — OFFSET n would re-scan n rows each time and degrade to O(N²) over a 2-billion-row table.
2. ON CONFLICT (id) DO NOTHING makes each batch idempotent against the sync trigger: if a live write already inserted a row via the trigger, the backfill skips it rather than erroring or overwriting a fresher value. The trigger owns "latest"; the backfill owns "historical."
3. Each batch commits in its own short transaction. Short transactions keep locks brief, keep dead-tuple bloat bounded, and let the throttle interleave — a single giant INSERT ... SELECT would hold one long transaction and defeat the whole purpose.
4. After each batch the loop checks pg_stat_replication and sleeps while any replica is more than 50 MB behind. This makes the backfill a background citizen: on a busy afternoon it slows down automatically; at night it runs full speed. Throttling on a live signal beats a fixed sleep that is either too slow or too aggressive.
5. The keyset cursor lo = max(ids) advances monotonically, so the backfill is resumable — if it crashes, restart it with the last lo (or from 0, since conflicts are skipped) and it converges without redoing completed work.

Output.

Batch	id range copied	Replica lag after	Action
1	1 – 20,000	12 MB	continue
2	20,001 – 40,000	61 MB	sleep until < 50 MB
3	40,001 – 60,000	30 MB	continue
…	…	…	…
final	last 20k rows	8 MB	done

Rule of thumb. Backfill in bounded keyset-paged batches, one short transaction each, idempotent via ON CONFLICT DO NOTHING, and throttle on a live load signal (replica lag or active sessions) rather than a fixed sleep. That combination copies a billion rows without ever paging the on-call.

Worked example — deletes, the sync-race, and convergence

Detailed explanation. The subtle bug in every hand-rolled shadow build is the interleaving of a historical backfill with concurrent live DML on the same row. If the ordering is wrong, the shadow can end up with a stale value or a resurrected deleted row. Walk through the four interleavings and show why upsert-latest plus explicit-delete converges in all of them.

• Case A. Backfill copies row 42, then a live UPDATE bumps it → trigger upsert overwrites with the fresh value. ✅
• Case B. Live UPDATE bumps row 42 (trigger upsert), then backfill reaches row 42 → DO NOTHING skips it, keeping the fresh value. ✅
• Case C. Live DELETE removes row 42 (trigger deletes from shadow), then backfill reaches row 42 — but row 42 no longer exists in blue, so the backfill scan never sees it. ✅
• Case D. Backfill copies row 42, then a live DELETE removes it → trigger deletes it from the shadow. ✅

Question. Prove the shadow converges for all four backfill-vs-live interleavings, and identify the one policy that would break it.

Input.

Interleaving	Backfill action	Live action	Shadow end state
A	insert 42 (old)	update 42 (new) → upsert	new ✅
B	skip 42 (conflict)	update 42 (new) → upsert	new ✅
C	never sees 42	delete 42 → trigger delete	absent ✅
D	insert 42	delete 42 → trigger delete	absent ✅

Code.

-- The ONE policy that breaks convergence: a backfill that OVERWRITES on
-- conflict instead of DO NOTHING. Case B would then clobber the fresh
-- trigger value with the stale historical row. NEVER do this:
--
--   ON CONFLICT (id) DO UPDATE SET total_cents = EXCLUDED.total_cents  -- WRONG
--
-- Correct backfill policy: the backfill only fills GAPS; the trigger owns
-- the latest value. So the backfill must DO NOTHING on conflict.
INSERT INTO public.orders_new (id, customer_id, total_cents, status, created_at, updated_at)
SELECT id, customer_id, total_cents, status, created_at, updated_at
FROM   public.orders
WHERE  id > :lo ORDER BY id LIMIT :batch
ON CONFLICT (id) DO NOTHING;     -- backfill fills gaps only; trigger owns latest

-- Meanwhile the sync trigger DOES upsert-overwrite, because live writes ARE
-- the latest truth (shown in the previous worked example).

Step-by-step explanation.

1. The invariant that makes the shadow converge is a clean division of ownership: the trigger owns the latest value for any row that receives a live write; the backfill only fills rows the trigger has not touched. Encode that as backfill = DO NOTHING, trigger = upsert-overwrite.
2. Case A converges because the trigger runs after the backfill and overwrites with the fresh value. Case B converges because the backfill runs after the trigger and skips the already-present fresh row. Order-independence is exactly what the ownership split buys.
3. Cases C and D converge because the trigger's explicit DELETE removes the row from the shadow whenever blue deletes it — and a backfill scan of blue can never re-insert a row that no longer exists there.
4. The single policy that breaks this is a backfill that does ON CONFLICT DO UPDATE (overwrite). In Case B that would clobber the trigger's fresh value with the stale historical copy — a silent data-staleness bug that reconciliation might or might not catch depending on timing. Backfill must DO NOTHING.
5. This is why hand-rolled shadow migrations are dangerous and why gh-ost / pt-online-schema-change encode exactly this ordering internally: the correctness lives entirely in the interaction between the copy policy and the sync policy.

Output.

Backfill conflict policy	Trigger policy	Converges?
DO NOTHING	upsert-overwrite	yes (all four cases)
DO UPDATE (overwrite)	upsert-overwrite	no (Case B goes stale)
DO NOTHING	insert-only (no delete)	no (Cases C/D leave ghosts)

Rule of thumb. Split ownership: backfill fills gaps only (ON CONFLICT DO NOTHING), the sync trigger owns the latest value (upsert-overwrite) and mirrors deletes explicitly. Get that division right and the shadow converges no matter how backfill and live writes interleave.

Senior interview question on shadow tables and backfill

A senior interviewer might ask: "You're re-partitioning a 5-TB events table from a single heap into monthly range partitions, live, with no downtime. Walk me through the shadow build, how you keep it in sync while writers keep coming, how you throttle the backfill, and how you know when it's safe to swap."

Solution Using a partitioned shadow table, trigger sync, a throttled backfill, and a readiness check

-- Shadow = the target PARTITIONED table, built empty at the new shape.
CREATE TABLE public.events_new (
    id          BIGINT       NOT NULL,
    event_ts    TIMESTAMPTZ  NOT NULL,
    user_id     BIGINT       NOT NULL,
    event_type  TEXT         NOT NULL,
    payload     JSONB,
    PRIMARY KEY (id, event_ts)
) PARTITION BY RANGE (event_ts);

-- Create the month partitions the shadow will need (scripted).
CREATE TABLE public.events_new_2026_08 PARTITION OF public.events_new
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
-- … one per month …

-- Sync trigger: mirror every live DML into the partitioned shadow.
CREATE OR REPLACE FUNCTION public.events_sync_to_new() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        DELETE FROM public.events_new WHERE id = OLD.id AND event_ts = OLD.event_ts;
        RETURN OLD;
    ELSE
        INSERT INTO public.events_new (id, event_ts, user_id, event_type, payload)
        VALUES (NEW.id, NEW.event_ts, NEW.user_id, NEW.event_type, NEW.payload)
        ON CONFLICT (id, event_ts) DO UPDATE SET
            user_id    = EXCLUDED.user_id,
            event_type = EXCLUDED.event_type,
            payload    = EXCLUDED.payload;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_events_sync              -- attach BEFORE backfilling
AFTER INSERT OR UPDATE OR DELETE ON public.events
FOR EACH ROW EXECUTE FUNCTION public.events_sync_to_new();

# Throttled backfill + readiness check
import time, psycopg2

BATCH, LAG_CEILING = 50_000, 100 * 1024 * 1024

def backfill_events(conn) -> None:
    lo = ('1970-01-01', 0)     # keyset on (event_ts, id)
    while True:
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO public.events_new (id, event_ts, user_id, event_type, payload)
                SELECT id, event_ts, user_id, event_type, payload
                FROM   public.events
                WHERE  (event_ts, id) > (%s, %s)
                ORDER  BY event_ts, id
                LIMIT  %s
                ON CONFLICT (id, event_ts) DO NOTHING
                RETURNING event_ts, id
            """, (lo[0], lo[1], BATCH))
            rows = cur.fetchall()
        conn.commit()
        if not rows:
            break
        lo = (rows[-1][0], rows[-1][1])
        with conn.cursor() as cur:
            cur.execute("SELECT COALESCE(MAX(pg_wal_lsn_diff(sent_lsn, replay_lsn)),0) FROM pg_stat_replication")
            while cur.fetchone()[0] > LAG_CEILING:
                time.sleep(1.0)
                cur.execute("SELECT COALESCE(MAX(pg_wal_lsn_diff(sent_lsn, replay_lsn)),0) FROM pg_stat_replication")

def ready_to_swap(conn) -> bool:
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM public.events")
        blue = cur.fetchone()[0]
        cur.execute("SELECT count(*) FROM public.events_new")
        green = cur.fetchone()[0]
    return blue == green      # tier-1 gate; full reconcile in section 4

Step-by-step trace.

Step	Object state	Live impact
Create shadow	events_new partitioned, empty	none
Attach trigger	live writes mirrored	~µs per DML
Backfill	history copied, keyset-paged	throttled on lag
Sync-race	upsert-latest / DO-NOTHING	converges
Readiness	count(blue) == count(green)	read-only
(next)	reconcile + atomic swap	section 3 & 4

After the build, events_new is a partitioned replica of events that has tracked every live write since the trigger was attached, filled with all history, and passed a first count check. The heavy work happened in the background with the writers never blocked; the swap itself is deferred to a proven-equal green.

Output:

Metric	Single heap (blue)	Partitioned shadow (green)
Rows	5 TB heap	5 TB across month partitions
Writer blocking during build	none	none
Backfill duration	—	hours, lag-throttled
Sync mechanism	—	AFTER trigger, upsert by (id, ts)
Swap readiness	—	count match → full reconcile

Why this works — concept by concept:

• Shadow at target schema — building events_new already partitioned means there is no second migration to run on the shadow; the swap replaces an un-partitioned heap with a partitioned table in one atomic step.
• Sync-first ordering — attaching the trigger before the first backfill batch guarantees no live write falls into a gap; the trigger has captured every mutation since t=0 of the build.
• Composite-keyset backfill — paging on (event_ts, id) keeps each batch a constant-cost index range scan even across partition boundaries, so the 5-TB copy runs in bounded memory and is resumable.
• Readiness check — a cheap count(blue) == count(green) is the first gate, not the last; it catches gross backfill failures before you spend money on the full row-hash reconcile of section 4.
• Cost — 2× storage for the duration plus a bounded per-DML trigger write; in exchange the re-partitioning of a 5-TB table happens with zero writer downtime instead of a multi-hour offline pg_dump/reload.

SQL
Topic — sql
SQL backfill and batched-update problems

Practice →

ETL Topic — etl ETL problems on incremental table builds

Practice →

---

3. Atomic swap and cutover

One transaction, two renames — readers see blue, then green, never in between

The mental model in one line: the atomic table swap is the single instant where blue becomes green — and it must be atomic from every reader's perspective, which on Postgres means renaming orders → orders_old and orders_new → orders inside one transaction under a short lock_timeout, so both renames commit together or neither does, and no query is ever able to observe a moment where the live name is missing or points at a half-built table. Get the atomicity wrong — "drop the old, then create the new" — and you open a window where the table does not exist and every query errors; get it right and the cutover is invisible.

The four axes for the atomic swap.

• Atomicity. DDL is transactional on Postgres, so both ALTER TABLE ... RENAME statements in one BEGIN/COMMIT are all-or-nothing. A reader either sees the pre-swap orders or the post-swap orders — never neither, never both. (MySQL offers the even cleaner RENAME TABLE a TO tmp, b TO a, tmp TO b single-statement atomic swap.)
• Lock duration. The rename takes a brief ACCESS EXCLUSIVE lock. It is fast (metadata-only), but if a long-running query holds the table, the rename queues and blocks every query behind it. The senior guard is SET lock_timeout so the swap fails fast and retries instead of stampeding the lock queue.
• Object carry-over. Foreign keys pointing at the table, sequences owned by it, indexes, grants, and the row-triggers must all be present on green before the swap. A swap that forgets to re-point an inbound FK or re-grant SELECT breaks consumers the instant it commits.
• In-flight transactions. A transaction that opened orders before the swap keeps seeing the old table until it ends (Postgres resolves the name at first reference). New transactions after commit see green. This is correct and invisible — but it means the old table cannot be dropped immediately; drainage takes a moment.

The rename choreography — what commits together.

• Two renames, one transaction. orders → orders_old frees the live name; orders_new → orders claims it. Both inside BEGIN … COMMIT.
• lock_timeout, not statement_timeout. You want to bound how long you wait for the lock, then abort and retry — not kill a running rename. SET LOCAL lock_timeout = '3s' does exactly that.
• Retry loop around the swap. Wrap the transaction in an application retry: on lock_not_available, back off and try again in a few seconds when the blocking query has finished.
• Reattach inbound references. Any child table with FOREIGN KEY ... REFERENCES orders(id) must be re-pointed at green (validate the constraint NOT VALID first, then VALIDATE CONSTRAINT online) before the swap, or the FK dangles.

The view-indirection alternative — swap the pointer, not the table.

• A view as the stable name. Instead of renaming the physical table, expose orders as a view that selects from orders_blue. Cut over with CREATE OR REPLACE VIEW orders AS SELECT * FROM orders_green — a metadata swap that takes an even briefer lock and never touches the physical tables.
• Trade-off. A simple SELECT * view is updatable in Postgres for basic cases, but complex writes may need INSTEAD OF triggers. Views add an indirection layer; physical rename keeps the table a table.
• When to prefer it. Read-heavy cutovers, or when you want the app to keep using one stable name across many future swaps.

Common interview probes on the swap.

• "Why one transaction?" — so both renames are atomic; no window with a missing or half-built table.
• "What lock does it take, and how do you keep it from blocking everything?" — brief ACCESS EXCLUSIVE; bound the wait with lock_timeout + retry.
• "What about foreign keys and sequences?" — reattach/validate them on green before the swap.
• "Can you drop the old table right after?" — no; let in-flight transactions drain first.

Worked example — the single-transaction double-rename swap

Detailed explanation. The canonical Postgres swap: rename blue out of the way and green into place inside one transaction, guarded by lock_timeout so it fails fast under contention. Walk through the exact statements and what a concurrent reader sees at each instant.

• Free the name. orders → orders_old.
• Claim the name. orders_new → orders.
• Guard. SET LOCAL lock_timeout = '3s' so we abort-and-retry instead of blocking.

Question. Write the atomic swap transaction and trace what a reader querying orders sees before, during, and after.

Input.

Moment	Physical tables	orders resolves to
before	orders (blue), orders_new (green)	blue
mid-txn (uncommitted)	rename in progress, locked	blocked on lock
after commit	orders_old (blue), orders (green)	green

Code.

-- Atomic swap: both renames in ONE transaction, fail-fast on lock contention.
BEGIN;

SET LOCAL lock_timeout = '3s';        -- wait at most 3s for the lock, else abort

ALTER TABLE public.orders     RENAME TO orders_old;   -- free the live name
ALTER TABLE public.orders_new RENAME TO orders;       -- promote green to live

-- Move ownership of the sequence and re-point any inbound FKs here if needed
-- (validated online beforehand; see next worked example).

COMMIT;                               -- both renames become visible together

# Application-side retry around the swap: on lock contention, back off & retry.
import time, psycopg2

SWAP_SQL = open("swap.sql").read()

def run_swap(dsn: str, attempts: int = 10) -> None:
    for k in range(attempts):
        try:
            conn = psycopg2.connect(dsn)
            with conn, conn.cursor() as cur:
                cur.execute(SWAP_SQL)          # BEGIN…COMMIT block above
            print(f"swap committed on attempt {k+1}")
            return
        except psycopg2.errors.LockNotAvailable:
            wait = min(2 ** k, 30)
            print(f"lock busy; retrying in {wait}s")
            time.sleep(wait)
        finally:
            conn.close()
    raise RuntimeError("swap could not acquire lock; investigate long-running txns")

Step-by-step explanation.

1. Both ALTER TABLE ... RENAME statements live inside one BEGIN … COMMIT. Postgres DDL is transactional, so the two renames are a single atomic unit: at COMMIT they become visible together. There is no instant where the name orders is unbound.
2. SET LOCAL lock_timeout = '3s' bounds how long the swap waits for the ACCESS EXCLUSIVE lock. If a long analytics query is holding orders, the swap does not queue indefinitely (which would block every new query behind it) — it aborts after 3 s and the whole transaction rolls back cleanly, changing nothing.
3. The application retry loop catches LockNotAvailable, backs off exponentially, and tries again. This turns "the swap collided with a slow query" from an incident into a self-healing retry that succeeds seconds later when the query finishes.
4. A reader that queries orders before the swap commits sees blue. A reader whose statement starts after commit sees green. A reader mid-swap blocks only for the sub-second the lock is held (or the swap aborts first). No reader ever sees a missing or half-built table.
5. Crucially the old table is renamed to orders_old, not dropped — it is kept intact for rollback (section 5). Dropping it here would forfeit the cheap reverse swap.

Output.

Reader timing	Sees	Blocked?
statement started pre-swap	blue (orders_old data)	no
statement arrives during the lock	waits for lock	≤ 3 s then green
statement started post-commit	green (orders data)	no
swap vs long analytics query	swap aborts, retries	readers unaffected

Rule of thumb. Do both renames in one transaction under a short lock_timeout, wrap it in an exponential-backoff retry, and rename blue to orders_old rather than dropping it. That is the whole atomic cutover — fast, invisible, and reversible.

Worked example — carrying over FKs, sequences, and grants

Detailed explanation. A swap that only renames the table breaks the instant it commits if the table had inbound foreign keys, an owned sequence, or role grants — because those attach to the physical table, and green does not have them yet. The senior swap prepares green fully before the rename. Walk through re-pointing an inbound FK, moving sequence ownership, and copying grants.

• Inbound FK. order_items.order_id REFERENCES orders(id) must reference green after the swap.
• Sequence. orders.id's owned sequence must be attached to green so nextval keeps working.
• Grants. GRANT SELECT ON orders TO reporting must exist on green.

Question. Prepare green so the swap carries over the inbound FK, the sequence, and the grants with no post-swap breakage.

Input.

Object	On blue	Must exist on green
inbound FK from order_items	references orders(id)	re-point to green
owned sequence	orders_id_seq owned by orders.id	owned by green
grants	SELECT to reporting	same grant on green

Code.

-- BEFORE the swap: add the inbound FK to green as NOT VALID (no full-table
-- scan, brief lock), then validate it online (no exclusive lock).
ALTER TABLE public.order_items
    ADD CONSTRAINT order_items_order_fk_new
    FOREIGN KEY (order_id) REFERENCES public.orders_new (id) NOT VALID;

ALTER TABLE public.order_items
    VALIDATE CONSTRAINT order_items_order_fk_new;    -- online, share-lock only

-- Re-point the sequence ownership to green's column
ALTER SEQUENCE public.orders_id_seq OWNED BY public.orders_new.id;
ALTER TABLE public.orders_new
    ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq');

-- Mirror grants onto green
GRANT SELECT ON public.orders_new TO reporting;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders_new TO app_writer;

-- THEN the atomic swap (previous worked example). AFTER commit, drop the
-- now-stale FK that still points at the renamed-away old table:
ALTER TABLE public.order_items DROP CONSTRAINT order_items_order_fk;      -- old
ALTER TABLE public.order_items
    RENAME CONSTRAINT order_items_order_fk_new TO order_items_order_fk;

Step-by-step explanation.

1. The inbound foreign key is added to green as NOT VALID first. That takes only a brief lock and skips the full-table validation scan; the subsequent VALIDATE CONSTRAINT runs online under a share lock, so live traffic is never blocked while the FK is proven.
2. Adding the FK to green before the swap means the moment green becomes orders, its children already reference it correctly. If you waited until after the swap, there would be a window where order_items references a table that no longer holds the live data.
3. The sequence is re-owned by green's id column and set as its default, so nextval('orders_id_seq') keeps issuing gap-free IDs across the swap. Forgetting this makes inserts fail with a missing-default error the instant green goes live.
4. Grants are mirrored onto green explicitly because privileges attach to the physical table, not the name — a renamed table keeps its own grants, so green must be granted the same roles blue had or reporting loses SELECT at cutover.
5. After the swap, the old FK (still pointing at the renamed-away orders_old) is dropped and the new one renamed into its canonical name — housekeeping so the schema reads cleanly and the next migration is not confused by a stale constraint.

Output.

Consumer	Without carry-over	With carry-over
order_items FK	dangles → insert errors	references green, valid
nextval on insert	missing default → error	issues IDs normally
reporting role	loses SELECT	keeps SELECT
app_writer role	loses DML	keeps DML

Rule of thumb. A swap is not just a rename — prepare green with the inbound FKs (NOT VALID then VALIDATE), the owned sequence, and every grant before you swap. The rename is atomic; the breakage comes from the attachments you forgot.

Worked example — view-indirection cutover for zero-lock reads

Detailed explanation. When the table is read-hot and you want the cheapest possible read cutover, expose the stable name as a view over the active physical table and swap the view definition. CREATE OR REPLACE VIEW is a fast metadata change. Walk through the setup and the cutover, and note the write-path caveat.

• Stable name. orders is a view, not a table.
• Physical tables. orders_blue (live) and orders_green (shadow).
• Cutover. CREATE OR REPLACE VIEW orders AS SELECT * FROM orders_green.

Question. Set up the view indirection and perform the read cutover; explain when writes need INSTEAD OF triggers.

Input.

Object	Role
orders (view)	stable name the app queries
orders_blue	current physical table
orders_green	shadow at target schema

Code.

-- Setup: the app always queries the VIEW named orders; it selects from blue.
CREATE VIEW public.orders AS SELECT * FROM public.orders_blue;

-- A simple SELECT * view is auto-updatable in Postgres, so basic
-- INSERT/UPDATE/DELETE on the view flow through to orders_blue unchanged.

-- CUTOVER: repoint the view at green. Fast metadata swap, brief lock.
BEGIN;
SET LOCAL lock_timeout = '3s';
CREATE OR REPLACE VIEW public.orders AS SELECT * FROM public.orders_green;
COMMIT;

-- If the view is NOT trivially updatable (joins, computed columns), writes
-- need INSTEAD OF triggers routing DML to the active physical table:
CREATE OR REPLACE FUNCTION public.orders_view_write() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO public.orders_green VALUES (NEW.*); RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE public.orders_green SET status = NEW.status WHERE id = OLD.id; RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM public.orders_green WHERE id = OLD.id; RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

Step-by-step explanation.

1. The application queries the stable name orders, which is a view. During normal operation it selects from orders_blue; a simple SELECT * view is auto-updatable in Postgres, so ordinary DML flows straight through to blue with no extra machinery.
2. The cutover is CREATE OR REPLACE VIEW orders AS SELECT * FROM orders_green. This rewrites only the view's definition — a metadata change that takes a very brief lock and never rewrites or locks the physical tables, making it the lowest-impact read cutover available.
3. Because the swap is just a view redefinition, rollback is symmetric and equally cheap: CREATE OR REPLACE VIEW orders AS SELECT * FROM orders_blue puts reads back on blue in milliseconds.
4. The caveat is writes. A trivially-updatable SELECT * view passes DML through automatically, but a view with joins or computed columns is not auto-updatable — you must attach INSTEAD OF INSERT/UPDATE/DELETE triggers that route writes to the currently-active physical table, and update those triggers as part of the cutover.
5. View indirection shines for read-heavy cutovers and for tables you expect to swap repeatedly (the app never learns a new name), at the cost of an indirection layer and the write-routing triggers when the view is not simple.

Output.

Cutover mechanism	Read lock	Write handling	Rollback
physical double-rename	brief ACCESS EXCLUSIVE	native (it's a table)	reverse rename
view redefinition	brief, metadata-only	native if SELECT *, else INSTEAD OF	redefine view

Rule of thumb. For read-hot cutovers, hide the physical tables behind a view and swap the view definition — the cheapest read cutover there is. Just remember that a non-trivial view needs INSTEAD OF triggers to keep the write path working across the swap.

Senior interview question on the atomic swap

A senior interviewer might ask: "Your shadow table is built and reconciled. Walk me through the exact cutover — the statements, the lock you take, how you keep a slow analytics query from turning the swap into an outage, and how you make sure foreign keys and sequences survive the rename."

Solution Using a guarded single-transaction rename with pre-attached references and a retry loop

-- PRE-SWAP (run earlier, online): green already has indexes, grants,
-- the owned sequence, and the inbound FK validated NOT VALID -> VALIDATE.
-- (See the carry-over worked example.) Green is a drop-in for blue.

-- THE SWAP: one guarded transaction.
BEGIN;
SET LOCAL lock_timeout = '3s';                 -- fail fast, don't stampede

ALTER TABLE public.orders     RENAME TO orders_old;
ALTER TABLE public.orders_new RENAME TO orders;

-- point the sequence default at the promoted table (already owned by it)
ALTER TABLE public.orders ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq');

COMMIT;

# The operational wrapper: retry on lock contention, verify post-swap,
# and DO NOT drop orders_old (kept for rollback).
import time, psycopg2

def cutover(dsn: str, swap_sql: str, attempts: int = 12) -> None:
    for k in range(attempts):
        conn = psycopg2.connect(dsn)
        try:
            with conn, conn.cursor() as cur:
                cur.execute(swap_sql)
            break
        except psycopg2.errors.LockNotAvailable:
            time.sleep(min(2 ** k, 30))          # back off, a slow query will finish
            continue
        finally:
            conn.close()
    else:
        raise RuntimeError("cutover blocked; check pg_stat_activity for long txns")

    # Post-swap smoke check: the live name must now serve green's data.
    conn = psycopg2.connect(dsn)
    with conn, conn.cursor() as cur:
        cur.execute("SELECT to_regclass('public.orders'), to_regclass('public.orders_old')")
        live, kept = cur.fetchone()
        assert live is not None and kept is not None, "swap left the schema inconsistent"
    conn.close()
    print("cutover complete; orders_old retained for rollback")

Step-by-step trace.

Step	Action	Guarantee
pre-swap	green has indexes/FK/seq/grants	drop-in ready
BEGIN	open swap txn	atomic unit
lock_timeout	bound lock wait to 3 s	no stampede
rename ×2	blue→old, green→live	both or neither
COMMIT	changes visible together	no half-state
retry	back off on lock busy	self-healing
smoke check	orders + orders_old exist	consistency proven

After cutover, the live name orders resolves to green, orders_old still holds blue intact for rollback, and the sequence keeps issuing IDs. A slow analytics query never turned into an outage because the swap either grabbed its lock within 3 s or aborted-and-retried, never blocking the query queue behind it.

Output:

Metric	Naive drop-then-create	Guarded double-rename
Window with no table	yes (error storm)	none
Behavior under lock contention	blocks everything	aborts + retries
FK / sequence survival	broken	carried over
Old data after swap	gone	retained as orders_old
Rollback	restore backup	reverse rename (minutes)

Why this works — concept by concept:

• Transactional DDL — wrapping both renames in one BEGIN/COMMIT makes the cutover atomic: Postgres exposes the two renames to other sessions only at commit, so no query can observe a missing or half-built orders.
• lock_timeout guard — bounding the wait for the ACCESS EXCLUSIVE lock converts "swap collides with a slow query" from a queue-stampede outage into a clean abort that the retry loop resolves seconds later.
• Pre-attached references — validating the inbound FK online and pre-owning the sequence and grants on green means the atomic instant flips everything consistently; there is no post-swap scramble to reattach broken references.
• Retain, don't drop — renaming blue to orders_old instead of dropping it preserves the cheap reverse swap; the drop is deferred behind the decommission gate of section 5.
• Cost — a sub-second metadata lock plus a bounded retry loop; in exchange the cutover is invisible to readers, survives contention, and stays fully reversible — the exact properties an in-place ALTER cannot offer.

SQL
Topic — sql
SQL transaction and locking problems

Practice →

Design Topic — design Design problems on cutover and release safety

Practice →

---

4. Reconciliation and the validation gate

Prove green equals blue — count, then checksum, then row-hash — and gate the swap on N clean cycles

The mental model in one line: reconciliation is the discipline of proving the green copy equals blue before you cut over, using a tiered ladder — a cheap row-count check to catch gross failures, per-column aggregate checksums to catch value corruption a count would miss, and a full per-row hash diff to find exactly which rows differ — run every cycle during the build, with the cutover gated on N consecutive fully-clean cycles rather than a single spot-check. A backfill that silently drops 0.1% of rows is invisible to the eye and lethal to an auditor; reconciliation is what turns "the migration looks done" into "the migration is proven equal."

The four axes for reconciliation.

• Coverage. A row count proves cardinality but not content; aggregate checksums (SUM, MIN, MAX, COUNT(DISTINCT) per column) prove column-level values in aggregate; a per-row hash proves every single row. Each tier catches a class of bug the cheaper tier misses.
• Freshness. Green must be caught up to blue before a hash is meaningful. If green is 10 seconds behind on live writes, a row-hash diff will report false mismatches for rows that are merely in flight. Compare only up to a fenced point — a snapshot LSN or a "no writes newer than T" horizon — and confirm green has drained to it.
• Tolerance. For an exact copy the tolerance is zero — any mismatch blocks cutover. For a transforming migration (type change, timezone normalization) some columns compare under a rule (e.g. equal-after-cast) rather than byte-equal. Decide per column which is exact and which is ruled.
• Online-ness. Full hashes are expensive, so you run the cheap tiers every cycle on every table and rotate the expensive row-hash so each table is fully hashed at least weekly and on any tier-2 breach. Between full hashes, a sampled dual-read diff in production catches drift cheaply.

The tiered ladder — three rungs, increasing cost and precision.

• Tier 1 — row count. SELECT count(*) on both sides at the fenced horizon. Cheap, run always, catches a backfill that died halfway.
• Tier 2 — aggregate checksum. Per-column SUM/MIN/MAX/COUNT(DISTINCT) (and a checksum over text columns). Catches value corruption — a truncated string, a rounded number, a shifted timestamp — that a count is blind to.
• Tier 3 — row-hash diff. Hash every row (md5/xxhash of the concatenated columns), aggregate the hashes, and if the aggregate differs, diff the hash sets both directions to enumerate exactly which PKs differ. The only tier that proves per-row equality and pinpoints the offending rows.

The gate — evidence, not optimism.

• A reconcile ledger. Every cycle records (table, tier, result, checked_at). Cutover eligibility reads from the ledger, not from a human's memory.
• N consecutive clean cycles. A table becomes cutover-eligible only after N (say 3) back-to-back cycles with all tiers clean — one clean cycle can be luck; three in a row under live traffic is evidence.
• Breach handling. Any tier-2 or tier-3 breach resets the counter, triggers a targeted re-backfill of the differing PKs, and blocks cutover until the streak rebuilds.

Common interview probes on reconciliation.

• "Row counts match — are you done?" — no; counts miss value corruption. Escalate to aggregate checksums and a row-hash.
• "How do you avoid false mismatches from live writes?" — fence the comparison at a snapshot LSN / horizon and confirm green has drained to it.
• "The full hash is too expensive nightly — what do you do?" — cheap tiers every cycle, rotate the full hash weekly + on breach, sampled dual-read in between.
• "How do you know which rows differ?" — diff the per-row hash sets both directions to enumerate the PKs.

Worked example — the three-tier reconcile query

Detailed explanation. The reconcile harness runs three SQL checks of increasing cost against blue and green at a fenced horizon. Walk through all three for the orders table and show what each tier catches that the previous one misses.

• Tier 1. Row count at horizon.
• Tier 2. Per-column aggregate checksum.
• Tier 3. Aggregate of per-row hashes; if unequal, enumerate differing PKs.

Question. Write the three reconcile tiers and show a case each tier catches.

Input.

Tier	Catches	Cost
1 count	missing/extra rows	O(1) index
2 aggregate	value corruption in aggregate	O(N) scan
3 row-hash	any per-row difference + which PKs	O(N) scan + hash

Code.

-- Fence the comparison: only rows committed at/or before the horizon count,
-- and green must have drained live writes up to it (checked separately).
-- :horizon is a timestamp/LSN captured before the run.

-- TIER 1 — row count
SELECT
  (SELECT count(*) FROM public.orders_old WHERE updated_at <= :horizon) AS blue_n,
  (SELECT count(*) FROM public.orders     WHERE updated_at <= :horizon) AS green_n;

-- TIER 2 — per-column aggregate checksum (numbers + text digest)
SELECT
  count(*)                                   AS n,
  sum(total_cents)                           AS sum_total,
  min(created_at)                            AS min_created,
  max(updated_at)                            AS max_updated,
  count(DISTINCT customer_id)                AS distinct_customers,
  md5(string_agg(status, ',' ORDER BY id))   AS status_digest
FROM public.orders_old WHERE updated_at <= :horizon
UNION ALL
SELECT
  count(*), sum(total_cents), min(created_at), max(updated_at),
  count(DISTINCT customer_id),
  md5(string_agg(status, ',' ORDER BY id))
FROM public.orders WHERE updated_at <= :horizon;

-- TIER 3 — per-row hash aggregate; equal aggregates => rows identical
WITH blue AS (
  SELECT id, md5(id||'|'||customer_id||'|'||total_cents||'|'||status||'|'||
                 created_at||'|'||updated_at) AS h
  FROM public.orders_old WHERE updated_at <= :horizon
),
green AS (
  SELECT id, md5(id||'|'||customer_id||'|'||total_cents||'|'||status||'|'||
                 created_at||'|'||updated_at) AS h
  FROM public.orders WHERE updated_at <= :horizon
)
-- 3a: fast global check
SELECT md5(string_agg(h, '' ORDER BY id)) FROM blue      -- compare to green's
;
-- 3b: only if 3a differs — enumerate exactly which PKs differ, both directions
SELECT COALESCE(b.id, g.id) AS id,
       CASE WHEN g.id IS NULL THEN 'missing_in_green'
            WHEN b.id IS NULL THEN 'extra_in_green'
            ELSE 'value_differs' END AS kind
FROM blue b FULL OUTER JOIN green g ON b.id = g.id AND b.h = g.h
WHERE b.id IS NULL OR g.id IS NULL;

Step-by-step explanation.

1. Every tier fences on updated_at <= :horizon so the comparison ignores rows still in flight — this is what prevents false mismatches from live writes that green has not yet received. Freshness is enforced separately (next worked example).
2. Tier 1 compares row counts. It is O(1)-ish on an index and catches the catastrophic case — a backfill that died at 80% leaves green short by 20% and the counts diverge immediately. A count match is necessary but nowhere near sufficient.
3. Tier 2 compares per-column aggregates: sum(total_cents) catches a numeric truncation, min/max timestamps catch a timezone shift, count(distinct customer_id) catches a join that fanned out, and the md5(string_agg(status …)) digest catches a corrupted text column. These catch value corruption that leaves the count identical.
4. Tier 3a hashes every row and aggregates the hashes into one digest per side. If the digests match, every in-scope row is byte-identical — the strongest possible equality proof. If they differ, tier 3b runs a FULL OUTER JOIN on (id, hash) to enumerate exactly which PKs are missing, extra, or value-different — the drill-down that turns "something differs" into "these 37 rows differ."
5. The tiers are ordered by cost so the harness fails fast: it never pays for the row-hash if the count already diverges, and it never runs the expensive 3b enumeration unless the cheap 3a digest says there is something to find.

Output.

Injected bug	Tier that catches it
backfill stopped at 80%	tier 1 (count short)
total_cents truncated to INT	tier 2 (sum differs)
one status field corrupted	tier 2 (digest) or tier 3
37 specific rows never copied	tier 3 (enumerated PKs)
all rows identical	all tiers clean

Rule of thumb. Reconcile in tiers ordered by cost: count → aggregate checksum → row-hash. Fence every tier at a horizon, fail fast on the cheap tiers, and only run the row-by-row enumeration when the global hash says there is a difference to locate.

Worked example — the freshness gate before you trust a hash

Detailed explanation. A row-hash diff is only meaningful if green has caught up to blue's writes up to the comparison horizon. Comparing a green that is 10 seconds behind produces false mismatches for in-flight rows. The senior harness fences at a horizon and confirms green has drained to it before hashing. Walk through the fence-and-confirm.

• Fence. Capture a horizon (LSN or max updated_at) at the start.
• Confirm drain. Wait until green's synced position ≥ the horizon before comparing.
• Compare. Only then run tiers 2 and 3 up to the horizon.

Question. Implement the freshness gate that ensures green has drained to the horizon before the hash comparison runs.

Input.

Step	Check
capture horizon	SELECT pg_current_wal_lsn() (or max(updated_at))
confirm drain	green's applied position ≥ horizon
compare	run tiers up to horizon

Code.

# Freshness gate: don't hash until green has caught up to the fenced horizon.
import time, psycopg2

def reconcile_when_fresh(conn, poll_s: float = 1.0, timeout_s: float = 120) -> str:
    with conn.cursor() as cur:
        # 1. Fence: the horizon is the source's latest committed write time.
        cur.execute("SELECT max(updated_at) FROM public.orders_old")
        horizon = cur.fetchone()[0]

        # 2. Confirm the shadow has drained every write up to the horizon.
        deadline = time.time() + timeout_s
        while True:
            cur.execute("SELECT max(updated_at) FROM public.orders")
            green_hwm = cur.fetchone()[0]
            if green_hwm is not None and green_hwm >= horizon:
                break                      # green is caught up to the fence
            if time.time() > deadline:
                return "NOT_FRESH: green did not drain to horizon in time"
            time.sleep(poll_s)

        # 3. Now the hash is meaningful — run tiers up to :horizon.
        cur.execute("""
            WITH blue AS (
              SELECT md5(string_agg(row_h, '' ORDER BY id)) AS d FROM (
                SELECT id, md5(id||'|'||customer_id||'|'||total_cents||'|'||status) AS row_h
                FROM public.orders_old WHERE updated_at <= %(h)s) t
            ),
            green AS (
              SELECT md5(string_agg(row_h, '' ORDER BY id)) AS d FROM (
                SELECT id, md5(id||'|'||customer_id||'|'||total_cents||'|'||status) AS row_h
                FROM public.orders WHERE updated_at <= %(h)s) t
            )
            SELECT (SELECT d FROM blue) = (SELECT d FROM green)
        """, {"h": horizon})
        return "CLEAN" if cur.fetchone()[0] else "MISMATCH"

Step-by-step explanation.

1. Step 1 captures the horizon as blue's latest committed updated_at (in a WAL-based sync you would use pg_current_wal_lsn()). Everything newer than the horizon is deliberately excluded from this cycle's comparison — those rows are in flight.
2. Step 2 polls green's high-water mark until it is ≥ the horizon. This is the freshness gate: it guarantees green has already received and applied every live write that blue committed up to the fence, so any remaining difference is a real discrepancy, not a timing artifact.
3. If green never drains to the horizon within the timeout, the harness returns NOT_FRESH rather than a misleading MISMATCH — distinguishing "green is lagging" (fix the sync) from "green is wrong" (fix the backfill) is critical for the on-call.
4. Only after the drain is confirmed does step 3 run the hash comparison, fenced at the same horizon on both sides. Because both sides are frozen to the same point and green has caught up to it, an equal digest is a true equality proof.
5. This fence-and-confirm is what makes online reconciliation trustworthy: without it, every cycle on a busy table would flap between clean and mismatch purely from replication lag, and the gate's "N clean cycles" signal would be noise.

Output.

Green state at compare time	Result	Meaning
drained to horizon, identical	CLEAN	real equality
drained to horizon, differs	MISMATCH	real discrepancy → drill down
still lagging the horizon	NOT_FRESH	sync lag, retry — not a data bug

Rule of thumb. Never hash a moving target. Fence the comparison at a horizon, confirm green has drained to it, and only then compare — and report a lagging green as NOT_FRESH, never as a mismatch, so the on-call fixes the right thing.

Worked example — the N-clean-cycles gate and ledger

Detailed explanation. One clean reconcile can be luck; a streak under live traffic is evidence. The harness records every cycle in a ledger and only marks a table cutover-eligible after N consecutive fully-clean cycles, resetting the streak on any breach. Walk through the ledger and the eligibility logic.

• Ledger. reconcile_log(table, cycle_at, tier1, tier2, tier3, clean).
• Eligibility. Last N rows for the table all clean = true.
• Reset. Any breach appends a dirty row → streak restarts.

Question. Implement the ledger write and the cutover-eligibility check gated on N clean cycles.

Input.

Parameter	Value
Required streak	3 consecutive clean cycles
Ledger table	reconcile_log
Breach action	reset streak, re-backfill differing PKs

Code.

CREATE TABLE IF NOT EXISTS reconcile_log (
    id         BIGSERIAL PRIMARY KEY,
    tbl        TEXT        NOT NULL,
    cycle_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    tier1_ok   BOOLEAN     NOT NULL,
    tier2_ok   BOOLEAN     NOT NULL,
    tier3_ok   BOOLEAN     NOT NULL,
    clean      BOOLEAN GENERATED ALWAYS AS (tier1_ok AND tier2_ok AND tier3_ok) STORED
);

-- Cutover-eligible iff the most recent 3 cycles for the table are all clean.
WITH recent AS (
    SELECT clean
    FROM   reconcile_log
    WHERE  tbl = 'orders'
    ORDER  BY cycle_at DESC
    LIMIT  3
)
SELECT count(*) = 3 AND bool_and(clean) AS cutover_eligible
FROM   recent;

# One reconcile cycle: run tiers, record the ledger row, report eligibility.
def run_cycle(conn, tbl: str, streak: int = 3) -> bool:
    t1, t2, t3 = run_tier1(conn, tbl), run_tier2(conn, tbl), run_tier3(conn, tbl)
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO reconcile_log (tbl, tier1_ok, tier2_ok, tier3_ok)
            VALUES (%s, %s, %s, %s)
        """, (tbl, t1, t2, t3))
        cur.execute("""
            SELECT count(*) = %s AND bool_and(clean)
            FROM (SELECT clean FROM reconcile_log WHERE tbl = %s
                  ORDER BY cycle_at DESC LIMIT %s) r
        """, (streak, tbl, streak))
        eligible = cur.fetchone()[0]
    conn.commit()
    if not (t1 and t2 and t3):
        rebackfill_differing_pks(conn, tbl)     # breach → targeted repair
    return bool(eligible)

Step-by-step explanation.

1. The reconcile_log ledger records the outcome of every cycle, with a generated clean column that is true only when all three tiers passed. Persisting the evidence means cutover eligibility is an auditable query, not a claim someone makes in standup.
2. Eligibility is "the most recent 3 cycles are all clean" — count(*) = 3 AND bool_and(clean). Requiring a streak rather than a single pass defends against a lucky clean cycle that happened to fall between two transient discrepancies.
3. A breach (any tier false) appends a dirty ledger row, which immediately breaks the streak — the next eligibility check returns false because the most recent 3 are no longer all clean. The gate cannot be gamed by a later clean cycle until 3 fresh clean cycles accumulate.
4. On a breach the harness kicks a targeted re-backfill of exactly the PKs tier 3 enumerated, rather than re-copying the whole table — cheap, surgical repair that then has to earn the streak back from zero.
5. This ledger-plus-streak is the difference between "the migration looks done" and "the migration has proven itself equal for 3 consecutive cycles under production write load" — the second is what a senior engineer signs their name to.

Output.

Recent cycles (newest→oldest)	Eligible?
clean, clean, clean	yes
clean, clean, dirty	no
dirty, clean, clean	no (streak broken 2 ago)
clean, clean (only 2 exist)	no (need 3)

Rule of thumb. Gate cutover on N consecutive clean cycles recorded in a ledger, not a single spot-check. Any breach resets the streak and triggers a surgical re-backfill of the enumerated PKs — the cutover happens on accumulated evidence, never on a hopeful glance.

Senior interview question on reconciliation

A senior interviewer might ask: "You've backfilled a shadow copy of a 1-billion-row table and the row counts match. The interviewer says 'ship it.' Explain why a count match isn't enough, what else you'd check, how you avoid false mismatches from live traffic, and what gate you'd require before cutting over."

Solution Using a tiered reconcile, a freshness fence, and an N-clean-cycles ledger gate

# The full reconcile gate: fence -> confirm fresh -> tiered compare ->
# ledger -> eligibility. Returns True only when cutover is proven safe.
def reconcile_gate(conn, tbl: str = "orders", streak: int = 3) -> bool:
    # 1. Freshness fence — do not compare a moving target.
    state = reconcile_when_fresh(conn)          # from the freshness worked example
    if state == "NOT_FRESH":
        record_cycle(conn, tbl, t1=False, t2=False, t3=False, note="lag")
        return False

    # 2. Tiered compare, cheap-to-expensive, fail fast.
    t1 = tier1_count(conn, tbl)                 # count match at horizon
    t2 = t1 and tier2_aggregate(conn, tbl)      # per-column checksums
    t3 = t2 and tier3_rowhash(conn, tbl)        # global row-hash digest

    # 3. Record the cycle in the ledger.
    record_cycle(conn, tbl, t1, t2, t3)

    # 4. On breach, enumerate + re-backfill exactly the differing PKs.
    if not (t1 and t2 and t3):
        diffs = tier3_enumerate_diffs(conn, tbl)   # FULL OUTER JOIN on (id, hash)
        rebackfill(conn, tbl, [d["id"] for d in diffs])
        return False

    # 5. Eligible only after N consecutive clean cycles under live traffic.
    return last_n_clean(conn, tbl, streak)

-- The eligibility query the gate calls in step 5.
SELECT count(*) = :streak AND bool_and(clean) AS cutover_eligible
FROM (
  SELECT clean FROM reconcile_log
  WHERE tbl = :tbl ORDER BY cycle_at DESC LIMIT :streak
) r;

Step-by-step trace.

Step	Check	Blocks cutover if…
1 fence	green drained to horizon	green is lagging (NOT_FRESH)
2a tier1	count(blue)=count(green)	rows missing/extra
2b tier2	aggregate checksums equal	value corruption
2c tier3	global row-hash equal	any per-row difference
3 ledger	record outcome	— (evidence)
4 repair	re-backfill diff PKs	breach → streak reset
5 gate	last N cycles all clean	streak < N

Running this each cycle, a count match alone never authorizes cutover: the harness still demands aggregate and row-hash equality, still fences against live-write lag, and still requires three consecutive clean cycles before the gate opens. "Ship it" on a bare count match is exactly the trap the gate exists to reject.

Output:

Situation	Gate result	Why
counts match, values corrupted	blocked	tier 2 fails
identical but green lagging	blocked	NOT_FRESH
identical, 1 clean cycle	blocked	streak < 3
identical, 3 clean cycles	eligible	proven under traffic
37 rows differ	blocked + repaired	tier 3 enumerates, re-backfill

Why this works — concept by concept:

• Tiered coverage — count catches missing rows, aggregate checksums catch value corruption a count is blind to, and the row-hash proves per-row equality; each tier closes the blind spot of the cheaper one, so "counts match" is necessary but never sufficient.
• Freshness fence — comparing only up to a horizon that green has provably drained to removes false mismatches from in-flight writes, so a MISMATCH always means a real data bug and never mere replication lag.
• Enumerate-and-repair — the FULL OUTER JOIN on (id, hash) pinpoints the exact differing PKs so the fix is a surgical re-backfill of a handful of rows, not a re-copy of the whole billion-row table.
• Ledger + streak gate — requiring N consecutive clean cycles recorded in an auditable ledger turns cutover authorization into evidence under live traffic, defeating the lucky-single-pass failure mode.
• Cost — cheap tiers every cycle (O(1) count, O(N) aggregate) plus a rotated O(N)-hash and rare O(diff) enumeration; a small, bounded ongoing spend that buys a provable, auditable cutover instead of a hopeful one.

Data Validation
Topic — data-validation
Data validation and reconciliation problems

Practice →

SQL Topic — sql SQL checksum and row-hash diff problems

Practice →

---

5. Rollback and release engineering

Keep blue alive — a reverse swap in minutes, dual-write during the soak, decommission behind a gate

The mental model in one line: a blue-green data deployment is only safe if the rollback is as cheap as the forward cutover — which means you keep the old table (orders_old) intact and rollback-ready through a soak window, dual-write to both copies during the overlap so a reverse swap loses no writes, define explicit rollback triggers, and only decommission blue behind a gate that proves green is correct under real traffic — because the instant you drop blue, both your rollback and your reconciliation baseline disappear. The forward swap gets the applause; the retained blue and the decommission gate are what keep the 2am rollback a two-minute rename instead of a restore-from-backup outage.

The four axes for rollback and release.

• Reversibility window. After the swap, blue survives as orders_old for a soak period. As long as it exists, rollback is a reverse rename — minutes, no data loss. Drop it early and rollback degrades to a backup restore (an outage). The window length is a risk decision, not a default.
• Write gap on rollback. If green took writes for an hour and you roll back to a blue that stopped receiving them, those writes are lost. The fix is to dual-write during the overlap (a reverse sync trigger from green→blue) so blue stays current and rollback is gap-free.
• Rollback triggers. Rollback is not a vibe; it fires on explicit conditions — a failed post-cutover reconcile cycle, a consumer-reported discrepancy, or an SLA breach. Pre-defining the triggers turns a stressful judgment call into a runbook step.
• Decommission gate. The one-way door. Blue is dropped only after N clean post-cutover reconcile cycles, all consumers signed off, an incident-free soak covering the real business cycle, and a restore-tested backup. After the gate, rollback and the reconcile baseline are gone — so the gate is strict on purpose.

The keep-blue pattern — reversibility by construction.

• Rename, never drop, at cutover. The swap renames blue to orders_old; it stays a full, current table.
• Reverse sync during soak. A green→blue trigger mirrors post-cutover writes back to blue, so blue never goes stale while it is the rollback target.
• Reverse swap = forward swap, mirrored. Rollback is the same guarded double-rename transaction, in the other direction: orders → orders_bad, orders_old → orders.

Release-engineering discipline around the swap.

• Cut reads before writes. Move low-risk read consumers to green first (trivial rollback), soak, then move write ownership — blast radius grows only as confidence does.
• Post-cutover reconcile. Reconciliation does not stop at cutover; it keeps running green-vs-blue after the swap to prove green is correct while serving production, and to feed the decommission gate.
• Wave the estate. Cut over table by table (or consumer group by group), each behind its own gate, so no single swap risks the whole platform.

Common interview probes on rollback.

• "It's live and a consumer reports wrong numbers — now what?" — reverse rename to the retained, dual-written blue; minutes, no data loss.
• "How do you avoid losing writes on rollback?" — dual-write green→blue during the soak so blue stays current.
• "When do you drop the old table?" — only behind the decommission gate (clean post-cutover cycles + sign-off + soak + restore-tested backup).
• "Big-bang or waved cutover?" — waved, reads-before-writes, each wave gated and reversible.

Worked example — keep-blue soak and the reverse swap

Detailed explanation. After the forward swap, blue lives on as orders_old. To keep it a valid rollback target, a reverse trigger mirrors post-cutover writes back to it. Rollback is then the guarded double-rename in reverse. Walk through the reverse trigger and the rollback transaction.

• Reverse sync. AFTER ... ON orders (green, now live) → mirror into orders_old (blue).
• Rollback. Guarded double-rename: green out, blue back in.
• Guarantee. Blue is current, so rollback loses nothing.

Question. Write the reverse-sync trigger and the rollback transaction, and explain why blue must be dual-written during the soak.

Input.

Object	Role during soak
orders (green)	live, serving traffic
orders_old (blue)	retained rollback target
reverse trigger	mirror green writes → blue

Code.

-- During the soak, keep blue current so it stays a gap-free rollback target.
CREATE OR REPLACE FUNCTION public.orders_reverse_sync() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        DELETE FROM public.orders_old WHERE id = OLD.id; RETURN OLD;
    ELSE
        INSERT INTO public.orders_old
            (id, customer_id, total_cents, status, created_at, updated_at)
        VALUES (NEW.id, NEW.customer_id, NEW.total_cents, NEW.status,
                NEW.created_at, NEW.updated_at)
        ON CONFLICT (id) DO UPDATE SET
            customer_id = EXCLUDED.customer_id, total_cents = EXCLUDED.total_cents,
            status = EXCLUDED.status, updated_at = EXCLUDED.updated_at;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_reverse_sync
AFTER INSERT OR UPDATE OR DELETE ON public.orders     -- green is now 'orders'
FOR EACH ROW EXECUTE FUNCTION public.orders_reverse_sync();

-- ROLLBACK: the forward swap, mirrored. Guarded, atomic, minutes.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE public.orders     RENAME TO orders_bad;   -- demote green
ALTER TABLE public.orders_old RENAME TO orders;       -- restore blue as live
COMMIT;

Step-by-step explanation.

1. The reverse-sync trigger fires on the now-live green table and mirrors every write back into orders_old (blue), using the same idempotent upsert-by-PK and explicit-delete pattern as the forward sync. This keeps blue continuously current for as long as the soak lasts.
2. Dual-writing blue is what makes rollback gap-free. Without it, blue would freeze at the cutover instant; rolling back an hour later would silently discard every write green accepted in that hour — a data-loss incident dressed up as a rollback.
3. The rollback itself is the identical guarded double-rename transaction from section 3, just mirrored: demote green to orders_bad, promote blue back to orders, both under lock_timeout in one atomic transaction. Because blue is current, the app resumes on a complete dataset.
4. Rollback is therefore symmetric with cutover — same lock discipline, same retry loop, same atomicity — which is exactly why it takes minutes and is safe to execute under pressure. There is no restore, no replay, no downtime.
5. Green is renamed to orders_bad (not dropped) so the failed copy is available for post-incident forensics — you want to know why it was wrong before you throw it away.

Output.

Moment	Live table	Blue (orders_old) state
post-cutover	green	current via reverse sync
1h later, all fine	green	still current
rollback fires	blue (restored)	becomes live, no gap
after rollback	blue	green kept as orders_bad for forensics

Rule of thumb. Keep blue as a dual-written, current rollback target through the soak. Then rollback is the forward swap mirrored — a guarded, atomic reverse rename that takes minutes and loses not a single write.

Worked example — rollback triggers and the post-cutover reconcile

Detailed explanation. Rollback should fire on explicit, pre-agreed conditions, not on a panicked hunch. The post-cutover reconcile keeps comparing green against the dual-written blue and trips a rollback trigger on breach. Walk through the trigger conditions and the automated watcher.

• Trigger 1. A failed post-cutover reconcile cycle (green diverges from blue).
• Trigger 2. A consumer-reported discrepancy (a dashboard shows wrong numbers).
• Trigger 3. An SLA breach (latency/error-rate on green exceeds threshold).

Question. Implement the watcher that runs post-cutover reconcile and auto-recommends rollback on a trigger.

Input.

Trigger	Signal	Action
reconcile breach	tier fails post-cutover	recommend rollback
consumer report	manual flag	recommend rollback
SLA breach	error rate > threshold	recommend rollback

Code.

# Post-cutover watcher: reconcile green vs the dual-written blue and decide.
def post_cutover_watch(conn, sla_error_rate: float = 0.01) -> str:
    # Trigger 1 — reconcile the now-live green against the retained blue.
    #   (green is 'orders', blue is 'orders_old'; both current via reverse sync)
    t1 = tier1_count(conn, "orders")
    t2 = t1 and tier2_aggregate(conn, "orders")
    t3 = t2 and tier3_rowhash(conn, "orders")
    if not (t1 and t2 and t3):
        return "ROLLBACK: post-cutover reconcile breach"

    # Trigger 3 — SLA watch on the live table.
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COALESCE(sum(errors)::float / NULLIF(sum(requests),0), 0)
            FROM   service_metrics
            WHERE  target = 'orders' AND ts > now() - interval '5 min'
        """)
        err = cur.fetchone()[0]
    if err > sla_error_rate:
        return f"ROLLBACK: SLA breach error_rate={err:.3f}"

    # Trigger 2 — consumer-reported discrepancies (a flag table).
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM consumer_discrepancy_flags WHERE resolved = false")
        if cur.fetchone()[0] > 0:
            return "ROLLBACK: unresolved consumer discrepancy"

    return "HOLD: green healthy; continue soak toward decommission gate"

Step-by-step explanation.

1. Trigger 1 re-runs the full tiered reconcile after cutover, comparing the live green against the dual-written blue. A post-cutover breach means green is producing wrong data under real traffic — the strongest possible signal to roll back immediately.
2. Trigger 3 watches the operational SLA on the live table — error rate and latency over a short window. A green that is correct but slow (a missing index, a bad plan) can still breach the service contract, and that is a valid rollback reason distinct from data correctness.
3. Trigger 2 surfaces human signals: a consumer flags a dashboard showing wrong numbers. Even if the automated reconcile is clean, a trusted consumer report is a rollback trigger, because reconciliation only checks what it was told to check.
4. The watcher returns a clear recommendation string — ROLLBACK with a reason, or HOLD to continue the soak. Any rollback recommendation feeds straight into the reverse-swap runbook from the previous worked example; the decision is pre-made, so execution is mechanical.
5. Pre-defining these three triggers turns rollback from a stressful, subjective 2am judgment call into a deterministic runbook: if any trigger fires, reverse-swap. That determinism is what keeps rollbacks fast and blameless.

Output.

Watcher observation	Recommendation
reconcile clean, SLA ok, no flags	HOLD (continue soak)
tier-2 breach post-cutover	ROLLBACK (data wrong)
reconcile clean, error rate 3%	ROLLBACK (SLA)
consumer flags wrong dashboard	ROLLBACK (consumer report)

Rule of thumb. Pre-agree the rollback triggers — post-cutover reconcile breach, consumer discrepancy, SLA breach — and wire a watcher to recommend rollback deterministically. A rollback you defined in advance is a runbook step; one you improvise at 2am is an incident.

Worked example — the decommission gate

Detailed explanation. Dropping blue is the one-way door: after it, rollback and the reconcile baseline are gone. So blue is retired only when a strict gate passes. Walk through the four gate conditions and the guarded drop.

• Condition 1. N consecutive clean post-cutover reconcile cycles.
• Condition 2. 100% of consumers switched and signed off.
• Condition 3. Incident-free soak covering the real business cycle (≥ 2 weeks / a monthly close).
• Condition 4. A restore-tested backup of blue exists.

Question. Implement the decommission-gate check and the guarded drop that only runs when all four conditions hold.

Input.

Condition	Evidence source
clean cycles	reconcile_log post-cutover streak ≥ N
sign-off	consumer_signoff all true
soak	days since cutover ≥ 14
backup	backup_restore_tests.verified = true

Code.

# Decommission gate: ALL four conditions must hold before blue is dropped.
def can_decommission(conn, tbl: str = "orders_old", streak: int = 5,
                     min_soak_days: int = 14) -> tuple[bool, list[str]]:
    reasons = []
    with conn.cursor() as cur:
        # 1. N clean POST-CUTOVER reconcile cycles.
        cur.execute("""
            SELECT count(*) = %s AND bool_and(clean)
            FROM (SELECT clean FROM reconcile_log
                  WHERE tbl='orders' AND cycle_at > (SELECT cutover_at FROM deploys WHERE tbl='orders')
                  ORDER BY cycle_at DESC LIMIT %s) r
        """, (streak, streak))
        if not cur.fetchone()[0]:
            reasons.append("need %d clean post-cutover cycles" % streak)

        # 2. All consumers signed off.
        cur.execute("SELECT bool_and(signed_off) FROM consumer_signoff WHERE tbl='orders'")
        if not cur.fetchone()[0]:
            reasons.append("consumers not fully signed off")

        # 3. Soak long enough to cover the business cycle.
        cur.execute("SELECT (now() - cutover_at) >= make_interval(days => %s) FROM deploys WHERE tbl='orders'", (min_soak_days,))
        if not cur.fetchone()[0]:
            reasons.append("soak shorter than %d days" % min_soak_days)

        # 4. A restore-TESTED backup exists (an untested backup is not a backup).
        cur.execute("SELECT bool_or(verified) FROM backup_restore_tests WHERE tbl='orders' AND created_at > now() - interval '7 days'")
        if not cur.fetchone()[0]:
            reasons.append("no restore-tested backup in last 7 days")

    return (len(reasons) == 0, reasons)

def decommission(conn) -> None:
    ok, reasons = can_decommission(conn)
    if not ok:
        raise RuntimeError("decommission blocked: " + "; ".join(reasons))
    with conn, conn.cursor() as cur:
        cur.execute("DROP TRIGGER IF EXISTS trg_orders_reverse_sync ON public.orders")
        cur.execute("DROP TABLE public.orders_old")     # the one-way door
    print("blue decommissioned behind the gate")

Step-by-step explanation.

1. Condition 1 requires N clean reconcile cycles measured after cutover — proving green is correct while it serves production traffic, which is strictly stronger than clean cycles during the shadow build (when green took no live reads).
2. Condition 2 requires every consumer to have switched and explicitly signed off, so no forgotten downstream job is still reading the about-to-be-dropped blue.
3. Condition 3 requires the soak to span the real business cycle — at least two weeks, ideally across a monthly close — so the migration has been exercised by the workload's actual peaks (month-end reporting, batch jobs) and not just a quiet Tuesday.
4. Condition 4 requires a restore-tested backup, because an unverified backup is not a backup. This is the last safety net once blue is gone; if it has not been restore-tested, the gate stays shut.
5. Only when all four hold does decommission drop the reverse-sync trigger and DROP TABLE orders_old. This is deliberately the strictest gate in the whole pipeline, because it is the only irreversible step — after it, both rollback and the reconciliation baseline are gone forever.

Output.

Gate state	Decommission?
5 clean cycles, signed off, 16-day soak, backup verified	yes
only 3 clean cycles	blocked (need 5)
1 consumer not signed off	blocked
soak only 9 days	blocked
backup exists but never restore-tested	blocked

Rule of thumb. Decommission blue only behind a four-condition gate — clean post-cutover cycles, full sign-off, a business-cycle-length soak, and a restore-tested backup. Retire the old copy on evidence, never on a calendar date, because dropping it is the one step you cannot undo.

Senior interview question on rollback and release engineering

A senior interviewer might ask: "Your shadow migration is swapped in and live. Twenty minutes later a finance consumer reports the numbers look off. Walk me through how you roll back without losing the writes green has taken, what triggers that decision, and how you eventually decide it's safe to drop the old table for good."

Solution Using a dual-written keep-blue soak, deterministic rollback triggers, and a strict decommission gate

# The release runbook as code: forward state -> watch -> rollback OR gate.
def release_lifecycle(conn) -> str:
    # Post-swap, blue (orders_old) is retained and dual-written via reverse sync.
    decision = post_cutover_watch(conn)          # reconcile + SLA + consumer flags

    if decision.startswith("ROLLBACK"):
        # Reverse swap: guarded, atomic, gap-free because blue is dual-written.
        run_swap(conn, """
            BEGIN;
            SET LOCAL lock_timeout = '3s';
            ALTER TABLE public.orders     RENAME TO orders_bad;
            ALTER TABLE public.orders_old RENAME TO orders;
            COMMIT;
        """)
        return f"ROLLED BACK ({decision}); green kept as orders_bad for forensics"

    # No rollback trigger — keep soaking and test the decommission gate.
    ok, reasons = can_decommission(conn)
    if ok:
        decommission(conn)                       # the one-way door
        return "DECOMMISSIONED blue behind the gate"
    return "HOLD: soaking; gate not yet satisfied: " + "; ".join(reasons)

-- The evidence the runbook reads: cutover time, post-cutover clean streak.
SELECT d.tbl, d.cutover_at,
       (SELECT count(*) FROM reconcile_log r
        WHERE r.tbl = d.tbl AND r.cycle_at > d.cutover_at AND r.clean) AS clean_post_cutover
FROM   deploys d
WHERE  d.tbl = 'orders';

Step-by-step trace.

Step	Input	Outcome
watch	reconcile + SLA + flags	ROLLBACK or HOLD
rollback	reverse rename (blue current)	live on blue, no write lost
forensics	green kept as orders_bad	root-cause the discrepancy
gate	4 conditions	decommission only if all pass
decommission	drop reverse trigger + blue	irreversible; baseline gone

For the finance-report scenario, the consumer flag trips trigger 2, the runbook reverse-swaps to the dual-written blue in minutes with zero write loss, and green is retained as orders_bad so the team can find the bug before retrying. Blue is never dropped until, cycles later, all four gate conditions prove green is correct under real month-end traffic.

Output:

Scenario	Runbook action	Data loss
consumer flags wrong numbers	reverse-swap to blue	none (dual-written)
green slow, SLA breach	reverse-swap to blue	none
all healthy, gate unmet	HOLD, keep soaking	n/a
all healthy, gate met	decommission blue	n/a (proven correct)

Why this works — concept by concept:

• Keep-blue + reverse sync — retaining blue as a dual-written table makes rollback a symmetric reverse rename that loses no writes, converting the worst case from a backup-restore outage into a two-minute operation.
• Deterministic triggers — pre-agreed rollback conditions (reconcile breach, consumer report, SLA breach) mean the 2am decision is a runbook lookup, not a judgment call, so rollbacks are fast and blameless.
• Forensic retention — renaming the failed green to orders_bad instead of dropping it preserves the evidence needed to root-cause why it was wrong before any retry.
• Strict decommission gate — dropping blue only behind four independent conditions (post-cutover clean streak, sign-off, business-cycle soak, restore-tested backup) protects the one irreversible step, because after it the rollback path and the reconcile baseline are gone.
• Cost — a bounded reverse-sync write per live DML and 2× storage through the soak; in exchange the entire deployment stays reversible until evidence — not a calendar date — authorizes the one-way door.

Design
Topic — design
Design problems on rollback and release safety

Practice →

Data Validation
Topic — data-validation
Data validation problems on post-cutover checks

Practice →

---

Cheat sheet — blue-green data deployment recipes

• Which strategy when. Additive change (nullable column, CREATE INDEX CONCURRENTLY) → expand only. Column-data change (type, NOT NULL, default) → expand-contract: add new column, dual-write via trigger, throttled backfill, swap the constraint, drop the old. Whole-row / PK / partitioning change → shadow table + atomic swap. Engine / major-version / region change → connection-tier blue-green (replicate + flip the proxy alias). Take the cheapest branch that fully covers the change.
• Expand-contract steps. (1) Expand: add the target column/table/index, nullable and default-free so it is metadata-only. (2) Migrate: attach a BEFORE/AFTER trigger to dual-write the new shape, then backfill history in bounded batches. (3) Reconcile: prove old and new agree. (4) Swap: move the constraint/PK inside one short transaction. (5) Contract: drop the old column. Every step is independently deployable and reversible.
• Shadow-table + chunked backfill template. Create X_new at the target schema; attach an idempotent AFTER INSERT/UPDATE/DELETE sync trigger (ON CONFLICT (pk) DO UPDATE for writes, explicit DELETE for deletes) before backfilling; backfill with keyset paging (WHERE pk > :lo ORDER BY pk LIMIT :n), one short transaction per batch, ON CONFLICT DO NOTHING (backfill fills gaps, trigger owns latest), throttled on pg_stat_replication lag. Sync-first, backfill-second — otherwise gap writes are lost.
• Atomic swap snippet. BEGIN; SET LOCAL lock_timeout='3s'; ALTER TABLE orders RENAME TO orders_old; ALTER TABLE orders_new RENAME TO orders; COMMIT; wrapped in an exponential-backoff retry on LockNotAvailable. Pre-attach inbound FKs (NOT VALID then VALIDATE CONSTRAINT online), re-own the sequence, and mirror grants onto green before the swap. MySQL: single-statement RENAME TABLE a TO tmp, b TO a, tmp TO b. Rename blue to orders_old — never DROP — to preserve rollback.
• Tiered reconcile ladder. Fence at a horizon and confirm green drained to it. Tier 1: count(*) both sides. Tier 2: per-column SUM/MIN/MAX/COUNT(DISTINCT) + md5(string_agg(text_col ORDER BY pk)). Tier 3: md5(string_agg(md5(row) ORDER BY pk)) global digest; if it differs, FULL OUTER JOIN ON (pk, row_hash) to enumerate exactly which PKs differ. Cheap tiers every cycle; rotate the full hash weekly + on breach; gate cutover on N consecutive clean cycles recorded in a ledger.
• View-indirection cutover. Expose the stable name as CREATE VIEW orders AS SELECT * FROM orders_blue; cut over with CREATE OR REPLACE VIEW orders AS SELECT * FROM orders_green (metadata-only, cheapest read cutover). Simple SELECT * views are auto-updatable; non-trivial views need INSTEAD OF triggers routing writes to the active physical table. Rollback = redefine the view back to blue.
• Rollback + decommission gate checklist. Keep blue as orders_old, dual-written via a green→blue reverse trigger through the soak so rollback is gap-free. Rollback = the guarded double-rename mirrored (orders → orders_bad, orders_old → orders). Rollback triggers: failed post-cutover reconcile, consumer discrepancy, SLA breach. Decommission gate (all four): N clean post-cutover cycles, 100% consumer sign-off, soak ≥ 2 weeks (a business cycle), restore-tested backup. Only then DROP TABLE orders_old.
• Lock-safety reminders. lock_timeout (not statement_timeout) bounds the wait for a lock and aborts fast; always retry. Build indexes CONCURRENTLY. Add constraints NOT VALID then VALIDATE online. A nullable, default-free column add is metadata-only; a column add with a volatile default rewrites the table — avoid it. Never ALTER COLUMN ... TYPE in place on a large live table.
• Throughput vs safety knobs. Batch size trades speed for lock/bloat pressure; throttle on a live signal (replica lag, active sessions) not a fixed sleep; keep transactions short; VACUUM between large batches if bloat climbs. During the build you carry ~2× storage plus shadow indexes — verify headroom before starting.
• Pattern decision matrix. State to duplicate: additive = none, expand-contract = one column, shadow = whole table, connection-tier = whole DB. Atomicity: expand-contract = per-step, shadow/connection = single instant. Rollback: additive = DROP, expand-contract = drop new column, shadow = reverse rename, connection = flip alias. Downtime: all four = zero when done right. Print this and use it in every interview.
• What interviewers score. Names the strategy by blast radius; attaches sync before backfilling; swaps atomically under lock_timeout with retry; reconciles in tiers and gates on N clean cycles; keeps blue dual-written and rollback-ready; decommissions only behind a strict gate. Every one of these is a senior signal.

Frequently asked questions

What is a blue-green deployment for databases?

A blue-green deployment runs two environments — blue (the current live version) and green (the new version) — and cuts traffic from one to the other with no downtime. For stateless services it is trivial: stand up a green fleet, warm it, flip the load balancer. On the data tier it is hard, because the "environment" is state you cannot duplicate for free: a green table is a full copy of blue that you must build online while writers keep mutating the source, and the cutover instant must be provably atomic so no query ever sees a half-migrated state. In practice a database blue-green deployment means building a shadow table (or a whole shadow database) at the target schema, keeping it in sync via triggers or CDC, backfilling history, proving equality with reconciliation, then swapping the two in a single atomic transaction — with blue kept intact for rollback until a decommission gate proves green is correct under real traffic.

Blue-green vs expand-contract — are they the same thing?

They are related but not identical. expand-contract (also called parallel change) is a column-level discipline: never mutate a column in place — add the new one (expand), converge old and new by dual-writing and backfilling (migrate), then remove the old one (contract). Blue-green is the environment-level pattern of running two copies and swapping between them. On the data tier they compose: an expand-contract migration is effectively a small blue-green swap of one column, while a full shadow tables + atomic-swap migration is blue-green applied to an entire table. The rule of thumb is to reach for expand-contract for column changes (it is the lightest tool), a shadow-table swap when the change touches the whole row shape, primary key, or partitioning, and connection-tier blue-green when you are replacing the whole database (an engine or major-version upgrade). All three share the same DNA: additive, reversible steps and a provably safe cutover.

How do shadow tables enable zero-downtime schema change?

A shadow table is a new table you create alongside the live one at the target schema, so it is born already-correct and never needs its own migration. You attach an idempotent sync trigger (or a CDC tail) to the live table first, so every live write is mirrored into the shadow with no gap, then backfill history in bounded, throttled batches. Because the writers are never blocked — the trigger commits the mirror atomically with each write, and the backfill runs in short transactions that yield to production on replica lag — the whole build happens online. When the shadow is a proven-equal replica, you swap the two table names in a single atomic transaction. This is exactly the mechanism inside gh-ost and pt-online-schema-change: the shadow decouples "build the new shape" (slow, online, safe) from "cut over to it" (instant, atomic), which is what makes a heavy migration zero-downtime.

How do you make the table swap atomic?

On Postgres you put both renames in one transaction — BEGIN; ALTER TABLE orders RENAME TO orders_old; ALTER TABLE orders_new RENAME TO orders; COMMIT; — because DDL is transactional, so the two renames become visible together at commit and no session can ever observe a moment where the name orders is missing or points at a half-built table. The critical guard is SET LOCAL lock_timeout (say 3 s): the rename needs a brief ACCESS EXCLUSIVE lock, and if a long-running query is holding the table, an unbounded wait would queue and block every query behind it — so you bound the wait, abort fast, and retry with backoff when the blocker finishes. MySQL offers an even cleaner single-statement RENAME TABLE a TO tmp, b TO a, tmp TO b. Two more essentials: pre-attach the inbound foreign keys (NOT VALID then online VALIDATE), the owned sequence, and the grants onto green before the swap, and rename blue to orders_old rather than dropping it so the atomic table swap stays reversible.

How do you prove green equals blue before cutover?

With a tiered reconciliation, never a spot-check. Tier 1 compares row counts to catch a backfill that died halfway. Tier 2 compares per-column aggregate checksums — SUM, MIN, MAX, COUNT(DISTINCT), and a digest over text columns — to catch value corruption (a truncated number, a shifted timestamp) that leaves the count identical. Tier 3 hashes every row and compares the aggregate digest; if it differs, a FULL OUTER JOIN on (pk, row_hash) enumerates exactly which PKs are missing, extra, or value-different so the fix is a surgical re-backfill. Two disciplines make this trustworthy online: fence every comparison at a horizon and confirm green has drained to it (so live-write lag never shows up as a false mismatch), and gate cutover on N consecutive clean cycles recorded in a ledger rather than one lucky pass. Cheap tiers run every cycle; the expensive full hash rotates weekly and on any breach.

How do you roll back a data deployment after cutover?

You make rollback as cheap as the forward swap by keeping blue alive. At cutover you rename blue to orders_old rather than dropping it, and attach a reverse sync trigger so every post-cutover write to green is mirrored back into blue — which keeps blue current and makes rollback gap-free. If a rollback trigger fires — a failed post-cutover reconcile cycle, a consumer-reported discrepancy, or an SLA breach — you run the guarded double-rename in reverse (orders → orders_bad, orders_old → orders) in one atomic transaction; it takes minutes, loses no writes, and the app resumes on a complete dataset. The failed green is kept as orders_bad for forensics. You only drop blue behind a strict decommission gate: N clean post-cutover reconcile cycles under real traffic, 100% consumer sign-off, a soak long enough to cover the business cycle (≥ 2 weeks, ideally a monthly close), and a restore-tested backup — because dropping blue is the one step that is irreversible, taking your rollback path and your reconcile baseline with it.

Practice on PipeCode

• Drill the SQL practice library → for the schema-change, batched-backfill, transaction-locking, and checksum problems a zero-downtime migration interview loves.
• Rehearse on the design practice library → for the blue-green cutover, expand-contract sequencing, and rollback-safety scenarios interviewers use to probe release engineering.
• Sharpen the validation axis on the data validation practice library → for tiered reconciliation, row-hash diffs, freshness fencing, and post-cutover drill-down.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the build → swap → reconcile → rollback pipeline against real graded inputs.

Lock in blue-green deployment muscle memory

Docs explain the syntax. PipeCode drills explain the decision — when expand-contract beats an in-place ALTER, when a backfill must throttle on replica lag, when the swap needs a `lock_timeout` guard, when a row count is hiding a value-corruption bug, and when a wave has earned its decommission gate. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →