Module 5: Zero-Downtime Migrations

Module 5 deliverable: a live zero-downtime migration

Next →

What are you going to build and why?

You're going to run a live zero-downtime migration on a mini FastAPI app serving continuous traffic. The goal: add the column tasks.priority INTEGER NOT NULL DEFAULT 0 to a table with 1M rows, while wrk runs in the background hammering the GET /tasks endpoint, and at the end validate that zero requests failed. It's the exercise that internalizes the whole module in a single measurable deliverable.

This project is the rehearsal for the final deliverable of module 8 (TaskFlow API), where the same technique gets applied to a complete multi-tenant SaaS API with RLS, audit logs, and cursor pagination active. Here we work in isolation (no RLS, no multi-tenancy) so all your attention is on the migration. In module 8, you'll add the complications of a complete live system.

What you demonstrate by completing it:

  1. That you master expand-contract in practice, not just in theory: 3 real deploys with their Alembic files.
  2. That you know how to do batched backfills without blocking traffic: a standalone script with progress metrics.
  3. That you have the defensive timeout reflex: every migration opens with SET lock_timeout.
  4. That you can diagnose locks in real time: documented queries against pg_locks.
  5. That you produce measurable evidence: a wrk log with 0 5xx errors during the whole migration.
  6. That you leave a reusable runbook: a RUNBOOK-MIGRATION.md a teammate can follow.

If you complete it successfully, you have the exact portfolio piece that demonstrates "I can evolve a schema in 24/7 production with no downtime." It's what any senior SaaS interview wants to see.

Project objective

By completing this project:

  • You'll have written and run 3 concrete Alembic migrations (expand, backfill, contract) against a table with 1M rows.
  • You'll have run wrk during the migration and validated 0 requests with a 5xx error.
  • You'll have produced a RUNBOOK-MIGRATION.md with the exact steps of the process.
  • You'll have documented the timing of each phase and the intermediate checks in BENCHMARKS-MIGRATION.md.
  • You'll have the reflexes to repeat the flow in any FastAPI app with Alembic.

How it fits with what you learned

Module conceptWhere it's used in the project
Capsule 02: locks and dangerous operationsThe justification for why expand-contract is necessary, even for a literal DEFAULT
Capsule 03: the expand-contract patternThe 3 migrations you're going to write (expand, backfill, contract)
Capsule 04: CREATE INDEX CONCURRENTLYAn additional migration to create an index on priority without blocking traffic
Capsule 05: lock_timeout and statement_timeoutEvery migration opens with SET lock_timeout = '5s'
Capsule 06: production patternsThe backfill as a standalone script, a documented runbook, idempotence
Capsule 07: rollback strategiesA downgrade implemented for each migration, the rollback window documented

Think of the project as the module's practical exam: every technique you learned has its concrete place.

Technical specifications

Stack

  • Language: Python 3.12+
  • Framework: FastAPI 0.110+
  • ORM: SQLAlchemy 2.0+ (async with asyncpg)
  • Migrations: Alembic 1.13+
  • DB: PostgreSQL 16+
  • Simulated traffic: wrk (installation: brew install wrk on macOS, apt install wrk on Ubuntu)
  • Async driver: asyncpg 0.29+
  • Sync driver (for the backfill script): psycopg2-binary

Initial setup

mkdir migration-zerodowntime-project
cd migration-zerodowntime-project
python -m venv venv
source venv/bin/activate

pip install \
    "fastapi==0.110.0" \
    "uvicorn[standard]==0.29.0" \
    "sqlalchemy[asyncio]==2.0.30" \
    "asyncpg==0.29.0" \
    "psycopg2-binary==2.9.9" \
    "alembic==1.13.1" \
    "pydantic==2.7.0"

# Check the versions
python -c "import fastapi, sqlalchemy, alembic; print(fastapi.__version__, sqlalchemy.__version__, alembic.__version__)"
# Start PostgreSQL locally (if you don't have it running)
docker run -d --name pg-migration \
    -e POSTGRES_PASSWORD=postgres \
    -p 5432:5432 \
    postgres:16

# Create the DB
createdb -h localhost -U postgres migration_demo

# Set DATABASE_URL
export DATABASE_URL="postgresql+asyncpg://postgres:postgres@localhost:5432/migration_demo"
export DATABASE_URL_SYNC="postgresql://postgres:postgres@localhost:5432/migration_demo"

# Initialize Alembic
alembic init alembic

Project structure

migration-zerodowntime-project/
├── app/
│   ├── __init__.py
│   ├── main.py                 # FastAPI app with the /tasks endpoints
│   ├── db/
│   │   ├── __init__.py
│   │   ├── session.py          # Engine and SessionLocal
│   │   └── models.py           # The Task model
│   └── schemas.py              # Pydantic schemas
├── alembic/
│   ├── env.py                  # Configured with timeouts and NullPool
│   ├── versions/
│   │   ├── 001_create_tasks_table.py
│   │   ├── 002_seed_1m_rows.py             # Optional: as a migration or a script
│   │   ├── 003_expand_add_priority_nullable.py
│   │   ├── 004_create_index_priority_concurrently.py
│   │   └── 005_contract_priority_set_not_null.py
│   └── alembic.ini
├── scripts/
│   ├── backfill_priority.py    # The standalone backfill script
│   ├── seed_data.py             # Generates the initial 1M rows
│   └── check_invalid_indexes.py
├── benchmarks/
│   └── wrk_test.lua             # A lua script for wrk (if you need POSTs)
├── RUNBOOK-MIGRATION.md         # The runbook you generate
├── BENCHMARKS-MIGRATION.md      # The measured results
└── README.md

Required functionality

1. A FastAPI app with /tasks endpoints

Required endpoints:

GET  /tasks                  # Lists the 50 most recent tasks
GET  /tasks/{id}            # Gets a task by ID
POST /tasks                  # Creates a task
GET  /health                 # A healthcheck for wrk

Expected behavior during the migration:

  • Before Deploy 1 (expand): app v1.0 runs. It doesn't know the priority column.
  • After Deploy 1 and before Deploy 2: app v1.0 keeps running. The column exists but isn't used.
  • After Deploy 2 (the new code): app v1.1 writes priority in INSERTs.
  • After Deploy 3 (contract): app v1.1 runs with priority NOT NULL in the DB.

A minimal implementation of app v1.0 (with no priority):

# app/db/models.py — the initial version
from datetime import datetime
from sqlalchemy import BigInteger, DateTime, Integer, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
    )
# app/main.py — the initial version v1.0
from fastapi import FastAPI, HTTPException, Depends
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.session import get_session
from app.db.models import Task

app = FastAPI()


@app.get("/health")
async def health():
    return {"status": "ok"}


@app.get("/tasks")
async def list_tasks(db: AsyncSession = Depends(get_session)):
    result = await db.execute(
        select(Task).order_by(Task.created_at.desc()).limit(50)
    )
    return [
        {"id": t.id, "title": t.title, "created_at": t.created_at.isoformat()}
        for t in result.scalars().all()
    ]


@app.get("/tasks/{task_id}")
async def get_task(task_id: int, db: AsyncSession = Depends(get_session)):
    result = await db.execute(select(Task).where(Task.id == task_id))
    task = result.scalar_one_or_none()
    if task is None:
        raise HTTPException(status_code=404, detail="Task not found")
    return {"id": task.id, "title": task.title, "created_at": task.created_at.isoformat()}


@app.post("/tasks")
async def create_task(title: str, db: AsyncSession = Depends(get_session)):
    task = Task(title=title)
    db.add(task)
    await db.flush()
    return {"id": task.id, "title": task.title}
# app/db/session.py
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
import os

DATABASE_URL = os.environ["DATABASE_URL"]

engine = create_async_engine(DATABASE_URL, pool_size=10, max_overflow=20)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)


async def get_session():
    async with SessionLocal() as session:
        yield session

2. Seeding 1M rows

Before starting the migration, the table has to have 1M rows so the exercise is representative.

# scripts/seed_data.py
"""Initial seed of 1M rows into tasks."""
import os
import psycopg2

DATABASE_URL_SYNC = os.environ["DATABASE_URL_SYNC"]


def main():
    conn = psycopg2.connect(DATABASE_URL_SYNC)
    conn.autocommit = False

    try:
        with conn.cursor() as cur:
            cur.execute("SET LOCAL statement_timeout = '0'")

            # Insert 1M rows in batches using generate_series
            print("Inserting 1M rows...")
            cur.execute(
                """
                INSERT INTO tasks (title)
                SELECT 'Task ' || g
                FROM generate_series(1, 1000000) g
                """
            )
            conn.commit()

            cur.execute("SELECT COUNT(*) FROM tasks")
            count = cur.fetchone()[0]
            print(f"The tasks table has {count:,} rows")

            cur.execute("SELECT pg_size_pretty(pg_total_relation_size('tasks'))")
            size = cur.fetchone()[0]
            print(f"Total size: {size}")

            cur.execute("ANALYZE tasks")
            conn.commit()
            print("ANALYZE complete")
    finally:
        conn.close()


if __name__ == "__main__":
    main()

Running it:

# First run the initial migration to create the table
alembic upgrade head

# Then seed
python scripts/seed_data.py
# Expected output:
# Inserting 1M rows...
# The tasks table has 1,000,000 rows
# Total size: ~80 MB
# ANALYZE complete

3. The 3 Alembic migrations of the expand-contract

Migration 1 — Expand (add a nullable column):

# alembic/versions/003_expand_add_priority_nullable.py
"""expand: add priority column (nullable)

Revision ID: 003_expand_priority
Revises: 002_seed
Create Date: 2026-05-02 14:00:00
"""
from alembic import op
import sqlalchemy as sa


revision = "003_expand_priority"
down_revision = "002_seed"  # Or the previous revision
branch_labels = None
depends_on = None


def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '30s'")

    # Idempotent with IF NOT EXISTS
    op.execute(
        "ALTER TABLE tasks ADD COLUMN IF NOT EXISTS priority INTEGER NULL"
    )


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '30s'")
    op.execute("ALTER TABLE tasks DROP COLUMN IF EXISTS priority")

Migration 2 — A concurrent index (CREATE INDEX CONCURRENTLY):

# alembic/versions/004_create_index_priority_concurrently.py
"""create index on priority CONCURRENTLY

Revision ID: 004_idx_priority
Revises: 003_expand_priority
"""
from alembic import op


revision = "004_idx_priority"
down_revision = "003_expand_priority"


def upgrade():
    # CREATE INDEX CONCURRENTLY can't be inside a transaction
    with op.get_context().autocommit_block():
        op.execute("SET lock_timeout = '5s'")
        op.execute("SET LOCAL statement_timeout = '0'")  # It can take a while
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_priority "
            "ON tasks (priority) WHERE priority IS NOT NULL"
        )


def downgrade():
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_tasks_priority")

Migration 3 — Contract (SET NOT NULL with the NOT VALID trick):

# alembic/versions/005_contract_priority_set_not_null.py
"""contract: make priority NOT NULL using NOT VALID + VALIDATE trick

Revision ID: 005_contract_priority
Revises: 004_idx_priority
"""
from alembic import op


revision = "005_contract_priority"
down_revision = "004_idx_priority"


def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '120s'")

    # Verify there are no NULLs before continuing
    result = op.get_bind().execute(
        sa.text("SELECT COUNT(*) FROM tasks WHERE priority IS NULL")
    ).scalar()
    if result and result > 0:
        raise RuntimeError(
            f"Cannot apply the contract: {result} rows with priority IS NULL. "
            "Run the backfill first."
        )

    # Step 1: add a CHECK NOT VALID — a short lock
    op.execute(
        """
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'tasks_priority_not_null_check'
            ) THEN
                ALTER TABLE tasks
                ADD CONSTRAINT tasks_priority_not_null_check
                CHECK (priority IS NOT NULL) NOT VALID;
            END IF;
        END $$;
        """
    )

    # Step 2: VALIDATE — SHARE UPDATE EXCLUSIVE, compatible with writes
    op.execute(
        "ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_not_null_check"
    )

    # Step 3: SET NOT NULL — metadata-only now that the CHECK validated
    op.alter_column("tasks", "priority", nullable=False)

    # Step 4: clean up the redundant CHECK
    op.execute(
        "ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_priority_not_null_check"
    )

    # Step 5: add the DEFAULT at the DB level
    op.execute("ALTER TABLE tasks ALTER COLUMN priority SET DEFAULT 0")


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("ALTER TABLE tasks ALTER COLUMN priority DROP DEFAULT")
    op.alter_column("tasks", "priority", nullable=True)

You also need the sqlalchemy import:

import sqlalchemy as sa

at the top of migration 3's file.

4. The standalone backfill script

# scripts/backfill_priority.py
"""Backfill tasks.priority = 0 for rows with NULL.

Usage:
    python scripts/backfill_priority.py [--dry-run] [--batch-size N] [--sleep S]

The script is idempotent: re-running is safe (it filters WHERE priority IS NULL).
"""
import argparse
import logging
import os
import sys
import time

import psycopg2

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s",
)
log = logging.getLogger(__name__)


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--batch-size", type=int, default=10_000)
    parser.add_argument("--sleep", type=float, default=0.1)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    database_url = os.environ.get("DATABASE_URL_SYNC")
    if not database_url:
        log.error("DATABASE_URL_SYNC is not set")
        return 1

    conn = psycopg2.connect(database_url)
    conn.autocommit = False

    try:
        with conn.cursor() as cur:
            cur.execute("SET lock_timeout = '5s'")
            cur.execute("SET statement_timeout = '0'")

            cur.execute(
                "SELECT COALESCE(MAX(id), 0) FROM tasks WHERE priority IS NULL"
            )
            max_id = cur.fetchone()[0] or 0

            if max_id == 0:
                log.info("Nothing to backfill")
                return 0

            log.info(
                f"Backfill started. max_id={max_id}, "
                f"batch_size={args.batch_size}, dry_run={args.dry_run}"
            )

            batch_min = 0
            batches = 0
            rows_total = 0

            while batch_min <= max_id:
                batch_max = batch_min + args.batch_size - 1

                if args.dry_run:
                    cur.execute(
                        """
                        SELECT COUNT(*) FROM tasks
                        WHERE id BETWEEN %s AND %s AND priority IS NULL
                        """,
                        (batch_min, batch_max),
                    )
                    rows_in_batch = cur.fetchone()[0]
                else:
                    cur.execute(
                        """
                        UPDATE tasks SET priority = 0
                        WHERE id BETWEEN %s AND %s AND priority IS NULL
                        """,
                        (batch_min, batch_max),
                    )
                    rows_in_batch = cur.rowcount or 0
                    conn.commit()

                rows_total += rows_in_batch
                batches += 1

                if batches % 10 == 0:
                    pct = min(100, (batch_min / max_id) * 100)
                    log.info(
                        f"Batch {batches}: progress {pct:.1f}% "
                        f"(total rows: {rows_total:,})"
                    )

                batch_min = batch_max + 1
                time.sleep(args.sleep)

            cur.execute("SELECT COUNT(*) FROM tasks WHERE priority IS NULL")
            null_remaining = cur.fetchone()[0]

            log.info(
                f"Backfill complete. Batches: {batches}, "
                f"rows updated: {rows_total:,}, "
                f"NULLs remaining: {null_remaining}"
            )

            if null_remaining > 0 and not args.dry_run:
                log.warning(
                    f"WARNING: {null_remaining} rows still with NULL. "
                    "The old app may be inserting NULLs."
                )
                return 1

        return 0
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())

5. The wrk configuration for continuous traffic

# wrk_test.lua (optional, only if you want to mix GET and POST)
request = function()
    local methods = {"GET", "GET", "GET", "POST"}  -- 75% GET, 25% POST
    local method = methods[math.random(1, 4)]

    if method == "GET" then
        return wrk.format("GET", "/tasks", nil, nil)
    else
        return wrk.format(
            "POST",
            "/tasks?title=test_" .. math.random(),
            nil,
            nil
        )
    end
end

Running wrk during the migration:

# GETs only (simpler)
wrk -t4 -c50 -d600s http://localhost:8000/tasks > wrk_results.txt 2>&1 &

# A mix of GETs and POSTs (with the lua script)
wrk -t4 -c50 -d600s -s wrk_test.lua http://localhost:8000 > wrk_results.txt 2>&1 &

# See the progress in real time
tail -f wrk_results.txt

6. Required documentation

You have to produce two files at the project root:

RUNBOOK-MIGRATION.md — the reusable runbook a teammate could follow step by step. It has to include:

  • Prerequisites (the app's state, commands to have ready).
  • The exact steps in order (Deploy 1, backfill, Deploy 2, Deploy 3) with copyable commands.
  • Intermediate checks (which query to run to confirm each step worked).
  • What to do if something goes wrong (a reference to the "hung migration" runbook from capsule 06).

BENCHMARKS-MIGRATION.md — the report of your specific run with real numbers:

  • Timings per phase (how long each migration took, how long the backfill took).
  • The wrk results (p50/p99 latency, total requests, errors).
  • The output of the monitoring queries during the migration (active locks, connections).
  • A comparison against expectations: "we predicted lock X, it was Y. We predicted backfill Z, it was W."

Validation and error handling

What has to be validated

  • Before Deploy 3 (contract), the backfill script has to verify SELECT COUNT(*) FROM tasks WHERE priority IS NULL = 0.
  • Every migration has to fail if the lock_timeout fires (not hang).
  • The backfill script has to log progress and report at the end if there are rows that weren't backfilled.
  • The POST endpoint of app v1.1 has to always write priority (don't allow creating with NULL after the app's migration).

Errors that have to be handled

  • The Alembic migration fails from a lock_timeout: a clear error, the deploy doesn't advance, an opportunity to investigate and retry. NO automatic retry.
  • The backfill gets interrupted midway: the script is idempotent. Re-running continues from where it stopped (because WHERE priority IS NULL filters).
  • Rows inserted during the backfill by app v1.0: the backfill catches them (they fall in its ID range). The final check detects any residual.
  • The contract fails because there are NULLs: an explicit error indicating you should re-run the backfill before retrying.

Minimal implementation example

# app/main.py — the skeleton of app v1.0 (before the migration)
from fastapi import FastAPI, HTTPException, Depends
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.session import get_session
from app.db.models import Task

app = FastAPI()


@app.get("/health")
async def health():
    return {"status": "ok"}


@app.get("/tasks")
async def list_tasks(db: AsyncSession = Depends(get_session)):
    result = await db.execute(
        select(Task).order_by(Task.created_at.desc()).limit(50)
    )
    return [
        {
            "id": t.id,
            "title": t.title,
            "created_at": t.created_at.isoformat(),
        }
        for t in result.scalars().all()
    ]


@app.post("/tasks")
async def create_task(title: str, db: AsyncSession = Depends(get_session)):
    task = Task(title=title)
    db.add(task)
    await db.flush()
    return {"id": task.id, "title": task.title}

To run it:

uvicorn app.main:app --port 8000

App v1.1 (after Deploy 2 — you have to modify the code):

# app/db/models.py — updated for v1.1
class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    priority: Mapped[int] = mapped_column(Integer, nullable=True, default=0)  # NEW
    created_at: Mapped[datetime] = mapped_column(...)


# app/main.py — the updated endpoint
@app.post("/tasks")
async def create_task(
    title: str,
    priority: int = 0,
    db: AsyncSession = Depends(get_session),
):
    task = Task(title=title, priority=priority)  # Always writes priority
    db.add(task)
    await db.flush()
    return {"id": task.id, "title": task.title, "priority": task.priority}

This is a skeleton. Your job is to:

  • Complete the app with all the required endpoints.
  • Write the Alembic migrations following the patterns.
  • Write the backfill script.
  • Run the complete migration with wrk running.
  • Document the process.

Evaluation rubric (self-check)

Functionality (40 points)

  • (5 pts) FastAPI app v1.0 works correctly (the /tasks GET/POST/health endpoints).
  • (5 pts) The tasks table with 1M rows seeded correctly.
  • (5 pts) Migration 1 (expand) runs with no error and with lock_timeout configured.
  • (5 pts) Migration 2 (CREATE INDEX CONCURRENTLY) uses autocommit_block correctly.
  • (5 pts) The backfill script runs in batches with a sleep, idempotent, with a final check.
  • (5 pts) App v1.1 deployed before the contract phase.
  • (5 pts) Migration 3 (contract) uses the NOT VALID + VALIDATE trick.
  • (5 pts) wrk runs during the whole migration with 0 5xx errors (the most important result).

Correct code and techniques (30 points)

  • (5 pts) Every migration opens with SET lock_timeout = '5s'.
  • (5 pts) Every migration has downgrade() implemented.
  • (5 pts) The migrations are idempotent (IF NOT EXISTS, IF EXISTS, DO $$ blocks).
  • (5 pts) The backfill uses SET LOCAL statement_timeout = '0' correctly.
  • (5 pts) The backfill script has progress logging.
  • (5 pts) env.py configured with NullPool and connection-level timeouts.

Documentation (20 points)

  • (10 pts) RUNBOOK-MIGRATION.md with exact copyable steps, intermediate checks, and a reference to the incident runbook.
  • (10 pts) BENCHMARKS-MIGRATION.md with real measured timings, the wrk result, and an analysis of any difference from expectations.

Validation (10 points)

  • (5 pts) Before Deploy 3, the query SELECT COUNT(*) FROM tasks WHERE priority IS NULL returns 0.
  • (5 pts) After the complete migration, SELECT COUNT(*) FROM tasks WHERE priority IS NULL is still 0 and every row has a valid priority.

Extra credit (optional, up to +15 pts)

  • (+5 pts) Implement the "migration rollback" case at each phase, documenting the rollback window at each step.
  • (+5 pts) Capture locks in real time during the migration with queries against pg_locks and save the output as evidence.
  • (+5 pts) Compare wrk's p99 latency before / during / after the migration. Confirm the difference is <10ms.

Total: 100 points Passing: 70 / 100 (70%)

Common mistakes in this project

Mistake 1: forgetting the autocommit_block in CREATE INDEX CONCURRENTLY

Symptom: migration 2 fails with CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Why it happens: Alembic wraps each migration in a transaction by default.

How to fix it: wrap the operation in with op.get_context().autocommit_block(): (capsule 04).

Mistake 2: running the backfill as a single UPDATE

Symptom: the backfill blocks wrk for minutes. wrk reports very high latency or timeouts.

Why it happens: a single UPDATE tasks SET priority = 0 WHERE priority IS NULL takes ROW EXCLUSIVE on the whole table for the whole UPDATE.

How to fix it: a loop in batches of 10k with a sleep (capsules 03 and 06).

Mistake 3: running Migration 3 before the backfill finishes

Symptom: Migration 3 (contract) fails with column "priority" contains null values.

Why it happens: the VALIDATE finds rows with NULL.

How to fix it: verify SELECT COUNT(*) FROM tasks WHERE priority IS NULL = 0 before running Migration 3. If it isn't 0, re-run the backfill.

Mistake 4: changing the app's code before Deploy 1

Symptom: the app doesn't come up because the SQLAlchemy model mentions priority but the column doesn't exist yet in the DB.

Why it happens: confusion about the order. The column has to exist in the DB BEFORE the code mentions it.

How to fix it: the correct sequence is: (1) Migration 1 adds the column, (2) deploy app v1.1 with the code that uses the column. NOT the other way around.

Mistake 5: forgetting to run ANALYZE tasks after creating the index

Symptom: queries that should use the index don't. Performance doesn't improve.

Why it happens: PostgreSQL uses statistics to decide whether to use an index. After creating it, the stats are out of date.

How to fix it: after Migration 2, run ANALYZE tasks (it can go inside the migration itself with op.execute("ANALYZE tasks")).

Mistake 6: setting lock_timeout very generously "just in case"

Symptom: something blocks the migration for minutes without anyone noticing. wrk reports degradation.

Why it happens: the intuition of "more time = safer." But lock_timeout = '60s' means the migration waits 60s forming a queue that affects traffic.

How to fix it: keep lock_timeout = '5s'. If it doesn't take the lock in 5s, it fails and you diagnose (capsule 05).

What to do if you get stuck

  • The app doesn't come up: check that DATABASE_URL is set and the DB exists. Check that the initial migrations ran (alembic upgrade head).
  • The seed takes a long time: that's normal, 1M rows takes 30-60 seconds. If it takes much longer, check whether you have indexes (the created_at with a server_default may generate overhead). Consider disabling indexes during the seed.
  • wrk reports 4xx errors (not 5xx): the 4xx errors are expected (404s for IDs that don't exist, 422s for validations). The criterion is 0 5xx errors (server errors).
  • The migration takes longer than expected: check whether there's another hung transaction with queries against pg_locks. Apply the runbook from capsule 06.
  • The backfill hangs: check the script's stdout log. If it's been on a specific batch for a long time, there's an active lock. Apply the runbook.
  • The contract fails with "column contains null values": the backfill didn't finish. Re-run the script. Verify with the manual query before retrying.

What comes next

The project you just completed is exactly the same technique you're going to apply in module 8 (TaskFlow API) on a complete system with multi-tenancy, RLS, audit logs, and cursor pagination. The difference is one of complexity, not of pattern:

  • Here: a simple table with 1M rows, no RLS, no multi-tenancy.
  • Module 8: TaskFlow with 5M rows distributed across multiple tenants, RLS active, audit logs via trigger, a multi-tenant app in production.

The reflexes you built here (lock_timeout always, expand-contract in 3 phases, a batched backfill with a standalone script, a documented runbook) are the same ones you're going to use in the final integrative project. Module 8 is going to demand that you coordinate all those patterns with correct multi-tenancy and validate that the RLS isolation doesn't break during the migration.

Before moving on to module 6, make sure your project:

  • Has the RUNBOOK-MIGRATION.md a teammate could follow.
  • Has the real benchmarks documented.
  • Passed the "0 5xx errors in wrk during the whole migration" validation.
  • Leaves you clear on what each phase does and why (not just that you followed steps).

Resources for the project

  1. FastAPI documentation — the official reference, especially "Dependencies" and "Async Database".
  2. Alembic — Tutorial — Alembic fundamentals if you need a refresher.
  3. wrk — README — wrk's syntax and lua scripting.
  4. PostgreSQL — generate_series — for generating the 1M-row seed.
  5. psycopg2 — Connection and Cursor reference — for the standalone backfill script.
  6. The module's previous capsules — specific references:
    • Capsule 03 (expand-contract) → the foundation of the 3 migrations.
    • Capsule 04 (CONCURRENTLY) → for Migration 2.
    • Capsule 05 (timeouts) → for all the migrations.
    • Capsule 06 (production patterns) → for the standalone backfill and the runbook.
    • Capsule 07 (rollback) → for documenting the rollback window at each phase.

Module wrap-up

If you completed this project, you've learned to:

  • Think about schema evolution as a process, not an event. Migrations aren't alembic upgrade head; they're sequences of phases coordinated with code.
  • Distinguish dangerous operations from safe ones and apply the right technique to each.
  • Build defensive reflexes (lock_timeout, idempotence, a runbook) that become the default without thinking about it.
  • Produce measurable evidence that your migration is zero-downtime, not just "I think it's zero-downtime".
  • Document your process in a way that a teammate can repeat it or a future-you can remember it.

These are the skills that take you from "I can run migrations" to "I can run migrations in 24/7 multi-tenant production". Module 6 opens the "Concurrency and Versioning" block with optimistic locking and API schema versioning. The transition is direct: you already know how to evolve the schema with no downtime; now you're going to learn to handle conflicts in concurrent data and to evolve the API contract without breaking clients that haven't updated yet. Both are about "assuming something changed since the last time you saw the system, and handling the conflict instead of preventing it".

Module 5 — SQL Patterns for Production APIs Guide

Next module: Module 6 — Optimistic Locking + Schema Versioning. Handling concurrent changes in data and in API contracts without requiring prior coordination.

← PreviousBlue-green and database rollback strategiesNext →Module 6: Optimistic Locking + Schema Versioning