ProjekinAja. Software Engineering Stage 4 · Architecture Lesson 4.8
● Stage 4 · Lesson 4.8

Migrations: Changing the Plane Mid-Flight

Schema changes on live data are the highest-stakes deploys you'll ever do — expand-contract, backfills, and finally paying off the v0 CSV debt.

⏱ ~22 min read 🏗 Architecture decision 🔥 High-stakes deploys

🎯 By the end you'll understand

Why the naive ALTER TABLE is a production outage waiting to happen. The expand-migrate-contract sequence that makes column renames safe under live traffic. How to backfill millions of rows without table locks. What backwards and forwards compatibility mean between a running application and a partially-migrated schema — and why they must coexist during the window between deploys. Finally: how TraceLab's v0 CSV format debt, catalogued in Lesson 1.1 and carried across the entire course, is paid off completely and correctly in this lesson.

KEY INSIGHTSchema migrations are concurrent programs, not database scripts

ALTER TABLE doesn't know about your users. They're writing rows while you rename columns. The schema migration that "worked fine in staging" can lock your table for four minutes at 9 a.m. Monday because staging has 3,000 rows and production has 30 million.

Most engineering teams treat schema migrations as a solved problem: write a migration file, run it at deployment time, done. This model is correct for greenfield databases with no live traffic. For production systems with concurrent reads and writes — which is everything you actually run — it's a live grenade. The moment a migration acquires a lock on a high-traffic table, every query stacks behind it. A 200 ms DDL on 500,000 rows becomes an eight-second DDL on 50 million rows, and your error rate spikes before the operation finishes.

🔑 Hold onto this

A safe migration on a live database is a three-phase concurrent program, not a single DDL statement. Phase 1 (Expand): add what the new code needs, touching nothing old code depends on. Phase 2 (Migrate): move existing data from old shape to new shape, in batches, while both the old and new columns are live. Phase 3 (Contract): remove what only old code needed, once old code is gone. Between every phase transition, a new version of the application is deployed. The migration takes days or weeks, not seconds — and the system stays up the entire time.

HOW IT WORKSThe naive ALTER TABLE: a production incident in four lines

Here is the TraceLab v2 runs table schema as we have it at the end of Stage 3. The metric_value column name is misleading — it holds a comma-separated string of all metrics, a direct descendant of the v0 CSV format. The team decides to rename it to metrics_json and change the type to a proper JSON column. A natural Monday morning task:

migrations/rename_naive.sqlthe wrong way
-- Runs table v2: metric_value holds a CSV string. We want metrics_json.
-- This looks reasonable. It is a production outage.

ALTER TABLE runs
  RENAME COLUMN metric_value TO metrics_json,
  CHANGE metrics_json TEXT NOT NULL DEFAULT '';

ALTER TABLE runs
  MODIFY COLUMN metrics_json JSON NOT NULL;

What actually happens when you run this against a table with 30 million rows under live traffic:

1

PostgreSQL/MySQL acquires an ACCESS EXCLUSIVE lock

DDL on most databases acquires the strongest possible table lock. No reads, no writes until the operation finishes. On 30 million rows, the rename itself may be instant (it's a catalog update), but the MODIFY COLUMN to change the type forces a full table rewrite — every row must be touched. Duration: seconds to minutes, depending on row size and I/O.

2

Every in-flight query queues behind the lock

Your application servers have open connections with queries in flight. They can't complete. Connection pools fill up. New requests start failing with "connection pool exhausted." The database's wait queue for the lock can itself become a secondary bottleneck — a long lock wait blocks subsequent lock requests, stacking them up like planes on a runway.

3

Application code breaks the instant the rename commits

Your running application instances were compiled against the old column name, metric_value. The moment the rename commits, every SELECT metric_value FROM runs becomes an error. You have a brief window — the time it takes to deploy a new app version — where the database is correct and the application is broken. That window is your error rate spike.

4

Rollback is another full table rewrite

If anything goes wrong — or if you just want to undo it — you run the same operation in reverse, paying the same lock cost again. Under PostgreSQL, you cannot ROLLBACK a DDL that finished; you must issue a new DDL. Some operations (like column type changes) cannot be undone without a full rewrite.

⚠ "But we run migrations during deployment, so there's no traffic"

This is the argument teams make before their first production incident from it. In practice: blue/green and rolling deployments mean old app instances are still running during migration. Even maintenance windows have monitoring queries and health-check connections. And the real test is timing: a migration that seems instant in your window becomes dangerous the first time production data grows past your test set. The safe sequence works even with traffic. Use it by default.

time ALTER TABLE ACCESS EXCLUSIVE lock error spike old app code reads "metric_value" — gone migration runs new app deployed requests queued
The naive migration produces two failure windows: the lock window (queries queue) and the compatibility window (old app code reads a column that no longer exists). The expand-contract sequence eliminates both.

CORE CONCEPTExpand-migrate-contract: the disciplined sequence

The expand-contract pattern (sometimes called parallel change or expand-contract-cleanup) decomposes a breaking schema change into a sequence of backwards-compatible steps. Each step can be deployed independently, rolled back safely, and leaves the system in a consistent state. The rename of metric_value to metrics_json takes five steps instead of one:

1

Expand: add the new column, nullable, no default required yet

migration_001_expand.sql
-- Add metrics_json alongside the old metric_value.
-- NULL is fine — old rows haven't been backfilled yet.
-- This is a metadata-only change on most modern databases.
ALTER TABLE runs
  ADD COLUMN metrics_json JSON;

Adding a nullable column is a metadata-only operation in PostgreSQL 11+ and MySQL 8.0.12+. No table rewrite, no long lock. The old column still exists. Old application code still works.

2

Deploy new application code: dual-write to both columns

tracelab/store.pyv3 transition — dual-write
def log_run(conn, run_id: str, metrics: dict[str, float]) -> None:
    # Write to both columns while the migration window is open.
    # Old readers still see metric_value; new readers get metrics_json.
    import json
    csv_str = ",".join(f"{k}={v}" for k, v in metrics.items())
    json_str = json.dumps(metrics)
    conn.execute(
        "INSERT INTO runs (run_id, metric_value, metrics_json) VALUES (?,?,?)",
        (run_id, csv_str, json_str)
    )

Reads still use metric_value; writes populate both. No old code breaks.

3

Backfill: populate the new column for all existing rows

See the next section for how to do this safely. The key: do it in batches, not as a single UPDATE statement.

4

Deploy new application code: read from the new column

tracelab/store.pyv3 stable — reads the new column
def get_run_metrics(conn, run_id: str) -> dict[str, float]:
    # All rows now have metrics_json. Old metric_value still present
    # as a safety net until the contract (cleanup) step.
    import json
    row = conn.execute(
        "SELECT metrics_json FROM runs WHERE run_id = ?", (run_id,)
    ).fetchone()
    return json.loads(row[0]) if row else {}

Once you confirm reads are correct and no old-code instances remain (check your deployment pipeline), proceed to cleanup.

5

Contract: drop the old column

migration_003_contract.sql
-- Drop metric_value only once no running code references it.
-- Verify with: SELECT count(*) FROM information_schema.columns
--   WHERE table_name='runs' AND column_name='metric_value';
ALTER TABLE runs
  DROP COLUMN metric_value;

Also a fast metadata operation. The rename is complete. Total downtime: zero seconds.

💡 Why "contract" and not "cleanup"?

The terminology comes from the contractual obligation the schema has to running code. During the expand phase, the schema adds to its contract — it now promises to provide both columns. During the contract phase, it removes an obligation — it no longer needs to provide metric_value, because no running code asks for it. A column that exists but nobody reads is an unused contract obligation. Dropping it reduces the schema's surface area, which is genuinely good engineering.

IN PRACTICEBackfills without table locks

Between the expand and the contract lies the most technically interesting phase: backfilling the existing rows. The naive approach is a single UPDATE:

backfill_naive.sqldon't do this
-- Updates every row in a single transaction.
-- On 30 million rows: minutes of lock, undo log the size of the table.
UPDATE runs
SET metrics_json = /* parse metric_value into JSON */
WHERE metrics_json IS NULL;

The single UPDATE holds a write lock for its entire duration. It also generates undo log entries for every row touched — on a large table, this can exhaust your undo tablespace mid-operation, leaving you with a failed migration and a corrupted undo log to clean up. The correct approach is batched backfill:

tracelab/migrations/backfill_metrics.pysafe batched backfill
import json, time

def parse_metric_value(csv_str: str) -> dict:
    # Convert "loss=0.31,reward=12.7" → {"loss": 0.31, "reward": 12.7}
    result = {}
    if not csv_str:
        return result
    for part in csv_str.split(","):
        if "=" in part:
            k, _, v = part.partition("=")
            try:
                result[k.strip()] = float(v.strip())
            except ValueError:
                result[k.strip()] = v.strip()
    return result

def backfill_in_batches(conn, batch_size: int = 1000, sleep_ms: int = 50) -> None:
    # Fetch one batch, update, commit, sleep. Repeat until done.
    # The sleep prevents the backfill from saturating the database.
    total = 0
    while True:
        rows = conn.execute(
            "SELECT id, metric_value FROM runs WHERE metrics_json IS NULL LIMIT ?",
            (batch_size,)
        ).fetchall()
        if not rows:
            break
        for row_id, csv_str in rows:
            metrics = parse_metric_value(csv_str or "")
            conn.execute(
                "UPDATE runs SET metrics_json = ? WHERE id = ?",
                (json.dumps(metrics), row_id)
            )
        conn.commit()
        total += len(rows)
        print(f"Backfilled {total} rows…")
        time.sleep(sleep_ms / 1000)
    print(f"Done. {total} rows backfilled.")

Key properties of the safe backfill: each batch commits independently (a failure mid-run loses at most one batch), the WHERE clause ensures idempotency (re-running picks up where it left off), the sleep provides back-pressure to protect the database under live traffic, and the batch size can be tuned based on observed query latency during the run.

📖 Idempotency and the retry safety guarantee

An idempotent backfill can be run twice and produce the same result as running it once. The guard condition (WHERE metrics_json IS NULL) is what makes this one idempotent: already-migrated rows are invisible to it. Idempotency is the single most important property of migration scripts — it means a failed run, a restart after a crash, and a deliberate re-run are all safe. If your migration is not idempotent, it is a liability.

💡 Online schema change tools

For tables too large for even batched backfills to be safe (very large tables, strict SLO budgets), purpose-built tools handle this: pt-online-schema-change (Percona Toolkit) and gh-ost (GitHub) work by creating a shadow table, copying data in the background with triggers, and swapping the table with a brief lock at cutover. GitHub runs gh-ost in production on tables with billions of rows. These are legitimate tools for a real problem — but the expand-contract sequence covers 95% of real migration cases without them.

CORE CONCEPTBackwards and forwards compatibility: the deployment window problem

Between every phase of the migration, you deploy a new version of the application. Modern deployments (rolling updates, blue-green, canary) mean multiple versions of the application run simultaneously against the same database during the deployment window. This is the compatibility requirement that every migration step must satisfy:

Migration phaseSchema stateOld app code must still work?New app code must still work?
Before expandonly metric_valueyes (it's unchanged)not yet deployed
After expandboth columns presentyes — reads old column, ignores newyes — dual-writes, reads old
During backfillboth columns, new being filledyes — still reads old columnyes — dual-writes, reads new if non-null
After new code deployedboth columns, new fully populatedrisky — old code still exists in old instancesyes — reads and writes new column
After contractonly metrics_jsonbroken (column gone)yes

The table makes the rule explicit: you can only execute the contract step after you have confirmed no old-code instances are running. In a rolling deployment, "confirm" means waiting for all old pods to drain, not just starting the new pods. In a blue-green deployment, it means actually cutting traffic and decommissioning the blue stack, not just switching the load balancer. This is often the step teams rush — and it is exactly the step that produces the 2 a.m. rollback.

⚠ Feature flags as an escape hatch — and their cost

Some teams add a feature flag that controls whether code reads from the old or new column, so the flag can be flipped atomically after backfill without a full deployment. This is legitimate engineering. The cost: the flag must be cleaned up, the old code path must be maintained until cleanup, and the flag system itself must be reliable. Feature flags that outlive the migration they were created for are technical debt with an unusually clean paper trail — and yet they accumulate anyway.

SEE ITMigration step-runner: safe vs naive, live vs ordered

⚙ Interactive · Zero-downtime migration simulator

Try the naive rename first — watch the lock window and error spike. Then switch to expand-contract and deploy the steps in order. Try deploying out of order and observe what breaks. Traffic shown as % success rate of concurrent requests to the runs table.

The single DDL statement approach. Click to run the migration and watch what happens to traffic.

# Waiting to run migration… # Table: runs | Rows: 31,400,000 # Active connections: 47 # Current p99 latency: 12 ms
Traffic success rate
97%
Lock duration
—
Error window
—
Rollback safe?
—

Two things to notice. First, in naive mode: the error window is not instantaneous — it spans the entire deployment time for the new app version, which in a rolling deploy can be minutes. Second, in expand-contract mode: clicking step 5 before step 4 produces an application error even in the new code, because the new code still references metric_value as a fallback during dual-write. The ordering constraint is real, not ceremony.

TRACELABPaying off the v0 CSV debt — completely

In Lesson 1.1, the debt ledger for TraceLab v0 contained this entry: "the CSV 'schema' lives in nobody's head — the great migration." Every subsequent lesson has carried that debt forward. The v0 format was a comma-separated string glued together with +. Lesson 1.5 repaid the encoding and float correctness issues. Lessons 1.6–1.7 addressed the O(n) scan. But the format itself — the fact that metrics are stored as a raw string with no schema — was never fixed. Until now.

Here is the complete migration sequence for TraceLab, from v0-descended CSV storage to the structured JSON column that v3 requires:

StepOperationDuration (real production)Downtime
1ALTER TABLE runs ADD COLUMN metrics_json JSON< 100 ms (metadata only)zero
2Deploy dual-write app code (v3-transition)~3 min rolling deployzero
3Backfill 31.4M rows in batches of 1,000 with 50 ms sleep~28 minuteszero
4Deploy read-new app code (v3-stable)~3 min rolling deployzero
5ALTER TABLE runs DROP COLUMN metric_value< 100 ms (metadata only)zero

Total wall-clock time: approximately 34 minutes. Total downtime: zero seconds. Every request served during those 34 minutes reads from a consistent, correct column. The TraceLab v3 schema, after the migration completes:

schema/v3_final.sqlthe v0 debt is paid
-- TraceLab v3 runs table — the CSV format is gone.
-- metrics_json is validated on write, queryable, typesafe.
-- Compare to v0: metric_value TEXT NOT NULL DEFAULT ''

CREATE TABLE runs (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    run_id      TEXT     NOT NULL UNIQUE,
    created_at  DATETIME NOT NULL DEFAULT (datetime('now')),
    tag         TEXT,
    status      TEXT     NOT NULL DEFAULT 'running',
    metrics_json JSON    NOT NULL DEFAULT '{}'    -- was: metric_value TEXT
);

-- Now possible: query individual metrics directly
-- SELECT run_id, json_extract(metrics_json, '$.loss') as loss
-- FROM runs WHERE json_extract(metrics_json, '$.loss') < 0.1;

The final state is worth dwelling on. The v0 format made it impossible to query a single metric without parsing the whole string in application code. The v3 format makes individual metrics first-class SQL expressions. This is not cosmetic — it is the difference between a TraceLab that can answer "show me all runs where reward exceeded 15" with an index scan, versus one that must load every row into Python and filter there. The debt was a performance ceiling, not just a code smell.

🔑 What the debt ledger actually taught

The v0 CSV debt ran for nine stages of lessons before it was paid. That is a realistic representation of how production technical debt works: not as an abstract concept, but as a specific constraint you bump into repeatedly at inopportune moments. You couldn't add per-metric queries in Stage 3 without parsing strings. You couldn't enforce metric types without a schema. You couldn't index a metric value without a column for it. Each constraint was a tax on every subsequent lesson. Paying it off in Stage 4 is not just a milestone — it is the point where the cost of carrying debt exceeds the cost of fixing it, which is the only time debt payoff makes economic sense.

WEAKNESSWhere this bites

✗ Skipping the contract step because "we'll get to it"

The single most common migration failure mode: the expand and backfill happen correctly, new code is deployed, and then the old column is never dropped. Months later, the table has a graveyard of deprecated columns, the schema is the only documentation (a comment-free one at that), and the next migration is more dangerous because nobody is sure which columns are still referenced. The contract step is not optional housekeeping — it is what makes the schema accurate. Automation helps: flag any column more than N days old that appears in no application code as a candidate for contract.

✗ Backfilling without measuring impact on live traffic

A batched backfill that is too aggressive can slow the production database enough to cause timeouts. The correct approach is to start with a small batch size and small sleep, measure query latency during the backfill, and tune the parameters until the backfill runs without degrading p99. This is not a one-time calibration — it should be re-done for any table whose size has grown significantly since the last backfill. A backfill that was safe at 1 million rows can be dangerous at 30 million with the same parameters.

✗ Assuming migration tools guarantee safety

Django's makemigrations, Alembic's autogenerate, and Flyway's sequential versioning are migration management tools — they handle ordering, history, and execution. None of them make your migrations safe. They will execute a table-locking ALTER TABLE as happily as they execute a safe nullable ADD COLUMN. The expand-contract discipline is a human decision about how to decompose a change, not a feature a tool provides. The tool runs whatever SQL you write.

KEY INSIGHTHow this connects

  • Databases: Transactions and the ACID Contract (3.4) — the transaction model that makes each migration step atomic. The backfill's batch commits are ACID transactions. The drop-column DDL is implicitly transactional in PostgreSQL (allowing true ROLLBACK on DDL). Understanding isolation levels is prerequisite to understanding what "safe" means for concurrent migration steps.
  • Schema Design and Normalization (3.6) — the schema we just migrated away from was a normalization failure (a repeating group stored as a delimited string). The v3 schema is a correct normalization — each metric as a named key in a typed JSON document. The migration is the runtime cost of fixing a design mistake.
  • TraceLab v3: The Architecture Review (4.9) — this migration is the final piece of the v3 milestone. The architecture review in the next lesson will include this migration as a design decision to defend: why expand-contract instead of a maintenance window, what the compatibility window means for rolling deployments, and how long the dual-write period should be.

The theme of Stage 4 has been distribution as a cost, not a promotion — and that theme applies to time as well as space. A migration that takes 34 minutes instead of 30 seconds is choosing temporal distribution of risk over a concentrated downtime event. Like service extraction, it is a tool that has a real cost and a real benefit, and the correct answer to "should we do this?" is "compared to what?" A maintenance window at 2 a.m. is also a real cost. Know both prices before you choose.

GLOSSARYKey terms introduced

Expand-migrate-contract
A three-phase pattern for safe schema changes under live traffic: add the new shape without removing the old (expand), move data from old to new (migrate), remove the old shape once no code depends on it (contract).
Backwards compatibility
A schema or API version that old code can still use correctly. During migration, the expanded schema must be backwards-compatible with deployed old code that reads the old column.
Forwards compatibility
A schema or API version that new code can use correctly even before all old code is gone. During migration, new dual-write code must remain correct against the old schema (before expand) and the new schema (after expand).
Batched backfill
Updating existing rows to match a new schema in small, independently-committed chunks with deliberate pacing, to avoid holding a long write lock or exhausting undo log space.
Idempotent migration
A migration that produces the same end state whether run once or multiple times. Critical for restart safety and for the backfill guard condition (WHERE new_column IS NULL).
ACCESS EXCLUSIVE lock
The strongest PostgreSQL lock mode, acquired by most DDL statements. Blocks all other operations — reads and writes — until released. The source of the "ALTER TABLE incident" pattern.
Online schema change
A family of tools (pt-online-schema-change, gh-ost) that perform DDL by copying the table to a shadow table in the background, applying changes incrementally with triggers, and swapping at cutover with a brief lock — used when even batched approaches are too disruptive.
Deployment window
The period between deploying new application code and fully decommissioning old application code. During this window, both versions run concurrently against the same database — the window that expand-contract compatibility must cover.

ACTIVE RECALLCheck your understanding

Answer in your own words first, then reveal.

1Why does a naive ALTER TABLE … RENAME COLUMN cause an outage even if the DDL itself completes quickly?›

Two separate problems. First, a rename that triggers a table rewrite (e.g., a type change) holds an ACCESS EXCLUSIVE lock for its entire duration — all concurrent queries queue behind it, connection pools fill up, and requests fail. Second, even if the rename is instant (metadata-only), running application code still references the old column name. Between the moment the rename commits and the moment all running app instances have been replaced with code that uses the new name, every query to the old column name fails. This is the compatibility window, and in a rolling deployment it can span minutes.

2What makes a batched backfill safer than a single UPDATE for large tables?›

Three things. Lock duration: each batch holds its write lock for only the time to update 1,000 rows; the single UPDATE holds it for the time to update all 30 million. Undo log pressure: the single UPDATE generates undo log entries for every row in one transaction, potentially exhausting undo tablespace mid-operation; the batched version generates a fixed maximum per transaction. Restart safety: if a batched backfill fails halfway, the idempotent guard (WHERE new_column IS NULL) makes restarting safe — already-migrated rows are skipped. A failed single UPDATE leaves the table in an unknown state.

3You need to rename user_name to username on a live table. Describe the sequence of deploys and DDL statements you would execute.›

Five steps. (1) Expand: ALTER TABLE users ADD COLUMN username TEXT (nullable, no rewrite). (2) Deploy new app code that dual-writes: writes to both user_name and username, reads from user_name. (3) Backfill: copy existing values from user_name to username in batches where username IS NULL. (4) Deploy new app code that reads from username; confirm all old instances are decommissioned. (5) Contract: ALTER TABLE users DROP COLUMN user_name. Total downtime: zero.

4Why was the v0 TraceLab CSV format a performance ceiling, not just a code smell?›

Because individual metrics were not addressable in SQL. A query like "find all runs where loss < 0.1" required loading every row into application memory, parsing the CSV string, and filtering in Python — O(n) in Python, which is orders of magnitude slower than an indexed column scan. The database optimizer could not help because there was no typed column to build an index on. The v3 migration to a JSON column (or individual metric columns) makes individual metrics first-class SQL expressions that can be indexed, aggregated, and filtered in the database layer, where that work belongs.

Next: defend the complete TraceLab v3 architecture against three reviewers →