The expand-contract pattern

Capsule overview

You already know which schema operations take an exclusive lock and why that takes production down (capsule 02). Now you're going to learn the fundamental mechanic that solves the problem with no downtime: the expand-contract pattern. It's the technique that lets you break a dangerous operation (adding a NOT NULL column to a table with 50M rows, renaming a column the app uses, changing a column's type) into a sequence of safe steps, each one short enough not to form a queue in production.

The central idea has a very descriptive name: expand first (you add the schema's new form, without touching the old one), migrate the data into that new form, swap the app's code to use the new one, and contract at the end (you remove the old form). Throughout the process, the schema lives in an intermediate state where the old app AND the new app can coexist, which is what enables rolling deploys with no downtime.

In this capsule you're going to write three concrete Alembic migrations (not pseudocode) over a real case: adding the column tasks.priority INTEGER NOT NULL DEFAULT 0 to a table with 1M rows. You're going to see the versions/xxx_expand.py file, the versions/yyy_backfill.py file, and the versions/zzz_contract.py file with their respective downgrade. And you're going to learn the "column rename in 4 deploys" pattern (more complex than an add) as a variant of the same principle.

By the end you'll have internalized the pattern that is the central piece of this whole module. The following capsules (CONCURRENTLY, lock_timeout, idempotence, the runbook) are complementary techniques that add to this base mechanic.

Mental model: the app and the schema live in different versions

In a world without rolling deploys, the app and the schema change at the same time: you stop the app, run the migration, bring up the new app. Schema and code are always in sync.

In the zero-downtime world, that's impossible. Rolling deploys deploy the app gradually: first 10% of pods, then 50%, then 100%. During that rollout, pods with the old app and pods with the new app coexist simultaneously, hitting the same DB. If the schema changes "all at once," the old pods break because they no longer understand the new schema.

The solution is to decouple the schema change from the code change. Think of it as building a new bridge parallel to the old one:

1. Expand: you build the new bridge next to the old one. Both bridges are operational. Cars can go over either. (The new schema added, the old schema intact.)
2. Migrate: you redirect traffic gradually to the new bridge (deploying the new app that writes to the new schema). The old cars can still use the old one. (The data copied into the new schema. The new app uses the new schema. The old app uses the old schema.)
3. Swap: all the traffic is on the new bridge. (All the pods are the new app.)
4. Contract: you demolish the old bridge, now unused. (You remove the old schema.)

Each step is granular enough not to break anyone. And each step is rollback-able with no data loss as long as you haven't reached the contract. That last part is crucial: the rollback window only closes on the last deploy.

The case study: adding priority to tasks (1M rows)

Let's go to the concrete case you're going to execute in this module and that reappears in module 8's project (TaskFlow).

The initial state:

# app/db/models/task.py
from sqlalchemy.orm import Mapped, mapped_column
from app.db.base import Base

class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int] = mapped_column(index=True)
    title: Mapped[str]
    created_at: Mapped["datetime"]

The final state we want:

class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int] = mapped_column(index=True)
    title: Mapped[str]
    priority: Mapped[int] = mapped_column(default=0)  # NOT NULL DEFAULT 0
    created_at: Mapped["datetime"]

The naive SQL operation:

ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

As we saw in capsule 02, in PostgreSQL 16 with a literal DEFAULT this does NOT rewrite the table. A short lock. Technically "safe" for a small table. But there are operational reasons for doing it in 3 deploys even though it's "safe":

1. The rollback window. If after the deploy you detect a bug in the new priority feature, we want to be able to roll back the code without having to roll back the schema. Expand-contract allows this.
2. A large table. The table can be small today and enormous in 6 months. Adopting the pattern now makes the operation scale with no surprises.
3. Team discipline. Reflexes get built by repetition. If you do expand-contract whenever the operation includes NOT NULL, you'll never get it wrong when it really matters.
4. The backfill isn't trivial on large tables. For 50M rows, the backfill takes minutes. That time needs to be its own operation, not part of a deploy.

The 4 phases of expand-contract

Now let's write each phase as a real Alembic migration. Assume your Alembic directory has the standard structure:

alembic/
├── env.py
├── script.py.mako
└── versions/
    ├── 001_initial_tasks_table.py
    ├── 002_add_priority_expand.py    ← We're going to write it
    ├── 003_backfill_priority.py      ← We're going to write it
    └── 004_priority_set_not_null_contract.py  ← We're going to write it

Phase 1 — Expand: add a nullable column

We add the column as NULL first. A short lock, no traffic impact, the old app keeps working (it doesn't know about the column and doesn't need it).

# alembic/versions/002_add_priority_expand.py
"""add priority column (expand)

Revision ID: 002_add_priority_expand
Revises: 001_initial_tasks_table
Create Date: 2026-05-02 14:30:00
"""
from alembic import op
import sqlalchemy as sa

revision = "002_add_priority_expand"
down_revision = "001_initial_tasks_table"
branch_labels = None
depends_on = None


def upgrade():
    # Set lock_timeout to fail fast if something hangs
    op.execute("SET lock_timeout = '5s'")

    # Add the column as NULL — a brief ACCESS EXCLUSIVE lock (~10ms)
    # NULL allowed during the rollback window (deploy 1-2)
    op.add_column(
        "tasks",
        sa.Column("priority", sa.Integer(), nullable=True),
    )


def downgrade():
    # If we need to roll back this deploy, we drop the column.
    # Rollback window: up until the backfill starts (the next deploy).
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "priority")

This phase's characteristics:

• A brief ACCESS EXCLUSIVE lock because ADD COLUMN NULL is metadata only.
• SET lock_timeout = '5s' at the start: if for some reason there's another operation holding a lock on tasks, we fail fast instead of hanging (capsule 05 goes deeper into this pattern).
• The old app keeps working: it doesn't know about the priority column. Its INSERTs don't mention it. Its SELECTs ignore it (SQLAlchemy only materializes columns declared in the model).
• The new app (which would be deployed after this migration) can start using the column. It tolerates the old rows having NULL.
• A clean downgrade: dropping the column restores the original state. A strong rollback window.

Phase 2 — Migrate (backfill): populate the column

We fill the priority column with the default value (0) for every old row. We do this in batches so as not to take a long lock.

Why not a single UPDATE:

-- ❌ WRONG for a large table: a minutes-long lock
UPDATE tasks SET priority = 0 WHERE priority IS NULL;

On a 1M-row table, this UPDATE takes ROW EXCLUSIVE on the whole table for however long it lasts. On a 50M-row table that's minutes. During that time, other UPDATEs/DELETEs on the same table queue up.

The correct pattern: batches with a sleep.

# alembic/versions/003_backfill_priority.py
"""backfill priority column (migrate phase)

Revision ID: 003_backfill_priority
Revises: 002_add_priority_expand
Create Date: 2026-05-02 14:35:00
"""
import time
from alembic import op
import sqlalchemy as sa

revision = "003_backfill_priority"
down_revision = "002_add_priority_expand"
branch_labels = None
depends_on = None

BATCH_SIZE = 10_000
SLEEP_BETWEEN_BATCHES_SEC = 0.1


def upgrade():
    # Backfills can be long. Disable the local statement_timeout
    # so the connection doesn't die if the global one is 30s.
    op.execute("SET LOCAL statement_timeout = '0'")

    connection = op.get_bind()

    # Get the range of IDs to process
    max_id_result = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE priority IS NULL")
    )
    max_id = max_id_result.scalar() or 0

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

    print(f"Backfilling priority in batches of {BATCH_SIZE}, max_id={max_id}")

    batch_min = 0
    rows_total = 0
    batches = 0

    while batch_min <= max_id:
        batch_max = batch_min + BATCH_SIZE - 1

        # UPDATE over a specific ID range (the lock is bounded to those rows)
        result = connection.execute(
            sa.text(
                """
                UPDATE tasks
                SET priority = 0
                WHERE id BETWEEN :batch_min AND :batch_max
                  AND priority IS NULL
                """
            ),
            {"batch_min": batch_min, "batch_max": batch_max},
        )
        rows_in_batch = result.rowcount or 0
        rows_total += rows_in_batch
        batches += 1

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

        batch_min = batch_max + 1

        # Sleep between batches to let other queries breathe
        time.sleep(SLEEP_BETWEEN_BATCHES_SEC)

    print(
        f"Backfill complete. Batches: {batches}, "
        f"rows updated: {rows_total}"
    )

    # A final check: 0 rows with priority NULL
    null_count = connection.execute(
        sa.text("SELECT COUNT(*) FROM tasks WHERE priority IS NULL")
    ).scalar()
    assert null_count == 0, (
        f"Incomplete backfill: there are still {null_count} rows with priority IS NULL"
    )


def downgrade():
    # Reset the column to NULL.
    # This only makes sense if we're going to roll back to before the expand.
    op.execute("UPDATE tasks SET priority = NULL")

This phase's characteristics:

• Batches of 10k rows: the lock is bounded to those rows, not the table. Other writes to other rows don't get blocked.
• UPDATE ... WHERE id BETWEEN :batch_min AND :batch_max: it uses the PK's index. Efficient access, no full scan.
• A 100ms sleep between batches: it gives other queries room so as not to saturate the DB. On very hot tables, consider 200-500ms.
• SET LOCAL statement_timeout = '0': the backfill can take minutes. If the global one is 30s, without this the operation dies.
• Progress logging: critical for long operations. If the backfill breaks halfway, the log tells you where to resume.
• A final check: assert null_count == 0 fails the migration if any NULL is left. This is defensive: if for some reason the backfill didn't cover every row (rows inserted during the process by the old app), you find out before Deploy 3.

A consideration: what about rows inserted DURING the backfill?

If the old app keeps inserting rows with no priority while the backfill runs, those rows will arrive as NULL. The backfill's loop catches them when it reaches their ID range. At the end, the null_count == 0 check confirms they're all covered. If the assert fails, it means the old app is still inserting and the backfill fell behind — you need to run the backfill again or deploy the new app first.

To avoid the race condition completely: deploy the new app (which always writes priority) BEFORE the backfill. That way, during the backfill, the new rows already have priority correctly and the backfill only covers the historical ones. That's the "Deploy 2 between phase 1 and phase 2" you see in some schemes.

Phase 3 — Swap: deploying the new app (not DDL)

This is NOT an Alembic migration. It's the deploy of the app's code that now always writes priority in INSERTs.

Before the deploy (the old app):

# app/api/tasks.py
async def create_task(title: str, db: AsyncSession = Depends(...)):
    task = Task(title=title)
    # priority doesn't get set — it goes as NULL
    db.add(task)

After the deploy (the new app):

# app/api/tasks.py
async def create_task(
    title: str,
    priority: int = 0,
    db: AsyncSession = Depends(...),
):
    task = Task(title=title, priority=priority)  # priority always set
    db.add(task)

Verification before moving on to Deploy 3:

-- Every row has to have a non-NULL priority
SELECT COUNT(*) FROM tasks WHERE priority IS NULL;
-- Expected: 0

If the count isn't 0, there's a bug in the app's deploy (some endpoint is still creating with NULL) or the rollout isn't complete (an old pod remains). Do NOT move on to Deploy 3 until you resolve it.

Phase 4 — Contract: the final SET NOT NULL

We now have a guarantee that no row has NULL. We can harden the constraint:

# alembic/versions/004_priority_set_not_null_contract.py
"""make priority NOT NULL (contract phase)

Revision ID: 004_priority_set_not_null_contract
Revises: 003_backfill_priority
Create Date: 2026-05-02 14:50:00
"""
from alembic import op


revision = "004_priority_set_not_null_contract"
down_revision = "003_backfill_priority"
branch_labels = None
depends_on = None


def upgrade():
    # Set lock_timeout to fail fast
    op.execute("SET lock_timeout = '5s'")

    # The "NOT VALID trick" pattern to avoid the blocking full scan
    # Step 1: add a NOT VALID CHECK constraint — a short lock
    op.execute(
        """
        ALTER TABLE tasks
        ADD CONSTRAINT tasks_priority_check
        CHECK (priority IS NOT NULL) NOT VALID
        """
    )

    # Step 2: validate the constraint — a SHARE UPDATE EXCLUSIVE lock
    # (compatible with writes and reads, it only blocks other DDL)
    op.execute("ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_check")

    # Step 3: now SET NOT NULL is metadata-only (PG knows it holds)
    op.alter_column("tasks", "priority", nullable=False)

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

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


def downgrade():
    # Roll back from NOT NULL to NULL (a fast operation)
    op.execute("SET lock_timeout = '5s'")
    op.execute("ALTER TABLE tasks ALTER COLUMN priority DROP DEFAULT")
    op.alter_column("tasks", "priority", nullable=True)

This phase's characteristics:

• The NOT VALID trick avoids the blocking full scan a direct ALTER COLUMN ... SET NOT NULL would take. The idea: adding the CHECK as NOT VALID is metadata-only (instant). Then VALIDATE reads the table with a lighter lock (compatible with writes). After that SET NOT NULL is instant because PostgreSQL already knows the constraint holds.
• The downgrade goes back to nullable, which is metadata-only. The rollback window here is limited: if after this deploy the app starts depending on NOT NULL (e.g. logic assuming priority is not None), a rollback leaves you in a state where NULLs could get into new rows. Careful.

The more complex case: renaming a column in 4 deploys

Adding a nullable column is the simple case. Renaming a column is where the pattern gets more interesting because the old column AND the new one have to coexist while the app rolls out.

The case: renaming tasks.title to tasks.name.

Why not ALTER TABLE tasks RENAME COLUMN title TO name:

• A brief ACCESS EXCLUSIVE lock (it's metadata only), but...
• Immediately after the rename, the old app breaks: its queries say SELECT title FROM tasks and the column no longer exists.
• In a rolling deploy, that means the old pods break while the new ones work. 500 errors until the rollout completes.

The correct pattern: 4 deploys.

Deploy 1 — Add the new column (expand)

# alembic/versions/00X_rename_title_to_name_step1_add_new.py
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    # Add the new nullable column
    op.add_column("tasks", sa.Column("name", sa.String(), nullable=True))


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "name")

Deploy 2 — Write to both columns (the app changes)

A code change: the new app writes to BOTH columns (title and name) on INSERT/UPDATE. It reads only from title (so as not to break the queries).

# app/db/models/task.py
class Task(Base):
    title: Mapped[str]
    name: Mapped[str | None]  # New, temporarily nullable

    @property
    def display_name(self) -> str:
        return self.name or self.title  # A fallback to title

# When a task gets created or updated:
task.title = "foo"
task.name = "foo"  # A temporary duplicate write

After this deploy, every new row has both title and name. The old ones still have name = NULL.

Deploy 2.5 — Backfill name from title

# alembic/versions/00X_rename_title_to_name_step2_backfill.py
def upgrade():
    op.execute("SET LOCAL statement_timeout = '0'")
    connection = op.get_bind()

    # A batched backfill (the same pattern as the previous example)
    BATCH_SIZE = 10_000
    SLEEP_SEC = 0.1

    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE name IS NULL")
    ).scalar()

    batch_min = 0
    while batch_min <= max_id:
        batch_max = batch_min + BATCH_SIZE - 1
        connection.execute(
            sa.text(
                """
                UPDATE tasks SET name = title
                WHERE id BETWEEN :batch_min AND :batch_max
                  AND name IS NULL
                """
            ),
            {"batch_min": batch_min, "batch_max": batch_max},
        )
        batch_min = batch_max + 1
        time.sleep(SLEEP_SEC)

    null_count = connection.execute(
        sa.text("SELECT COUNT(*) FROM tasks WHERE name IS NULL")
    ).scalar()
    assert null_count == 0

Deploy 3 — Swap reads to the new column

A code change: the new app now reads from name (instead of title), but keeps writing to both. This lets any rollback from Deploy 3 to Deploy 2 work (Deploy 2 reads from title, which is still up to date).

class Task(Base):
    title: Mapped[str]  # Still exists
    name: Mapped[str]  # Already NOT NULL after the backfill

    @property
    def display_name(self) -> str:
        return self.name  # Now the primary

And every query that used Task.title now uses Task.name.

Deploy 4 — Stop writing to the old column (contract step 1)

A code change: the app stops writing to title. It only writes to name.

# No longer:
task.title = "foo"
# Only:
task.name = "foo"

This closes the rename's rollback window: if you roll back after this deploy, the old app would write to title (which still exists in the DB) and read from title... but name wouldn't get updated with the new code in the rollback. You need to plan this point well.

Deploy 5 — Drop the old column (the final contract)

# alembic/versions/00X_rename_title_to_name_step5_drop_old.py
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "title")


def downgrade():
    # Recreating a dropped column is a destructive operation without the data.
    # It only makes sense if the previous deploy restores the code that writes to title.
    op.execute("SET lock_timeout = '5s'")
    op.add_column("tasks", sa.Column("title", sa.String(), nullable=True))
    # Backfill from name
    op.execute("UPDATE tasks SET title = name")
    op.alter_column("tasks", "title", nullable=False)

A summary of the rename:

• 5 deploys (3 DB, 2 code), over 1-2 weeks ideally.
• At any point you can roll back to the previous deploy with no data loss.
• The old column exists for the whole window, ensuring compatibility with the old app.

It's laborious. But it's the only way to do it with no downtime when the column is being actively used by the app.

Why does this matter in real work?

1. It's the pattern that separates the senior from the junior in production migrations. Any dev can run an ALTER TABLE that works locally. But only the one who understands expand-contract can execute schema evolutions in 24/7 multi-tenant production without anyone noticing.

2. It's exactly what module 8's project asks of you. The expand-contract for tasks.priority we're seeing here is the same flow the final project executes live. If you master this capsule, the final project is application.

3. It saves you permanent technical debt. Teams that don't master expand-contract accumulate "schema rot": columns that stuck around because renaming them was risky, wrong types that never got changed, indexes that never got reorganized. With expand-contract, the friction of evolving the schema goes down, and the schema stays healthy.

4. It's part of the common language of DBAs and senior backend engineers. In postmortems, RFCs, and architecture meetings, "let's do an expand-contract" is understood shorthand. Knowing the pattern gives you fluency to participate in those conversations.

Traps and common mistakes

Mistake 1 (conceptual): assuming expand-contract is "for large tables only"

Symptom: a dev decides to do expand-contract only when the table has >10M rows. On small tables they do ADD COLUMN ... NOT NULL DEFAULT directly. It works... until the table grows and one day the operation takes longer than expected.

Why it happens: the pattern seems "unnecessary" for small tables. But the cost of adopting it is low (3 deploys vs 1) and the benefit is robustness against growth.

How to tell: does the operation include NOT NULL for existing rows? Does the operation change a type? Does the operation rename/drop? If so, expand-contract is the default, regardless of the size.

How to fix it: adopt the pattern as the default. The exception is obviously safe operations (ADD COLUMN nullable, CREATE INDEX CONCURRENTLY) that don't require version coexistence.

Mistake 2 (operational): doing everything in one migration "to simplify"

Symptom: a dev sees they have to do 3 deploys for one operation and decides to "group them" into a single migration with all the steps. They do expand + backfill + contract in the same upgrade(). The migration takes 10 minutes and blocks production.

Why it happens: the 3 deploys seem redundant. Grouping them seems efficient. But the point of expand-contract is that each step happens with the app deployed in its corresponding version. If you run them all together, there's no coexistence, and the pattern loses its value.

How to tell: if your migration executes more than one "phase" (expand AND backfill, or backfill AND contract), it's wrong. Each phase is its own migration, in its own deploy.

How to fix it: physically separate the migrations. If Alembic's autogenerate produces everything together, edit the files manually to split them.

Mistake 3 (conceptual): not understanding that the backfill can run DURING traffic

Symptom: a dev schedules the backfill in a "maintenance window" because they're afraid it will affect production. They announce downtime for the backfill. But the batch pattern is designed precisely not to affect production.

Why it happens: the word "backfill" sounds heavy. Intuition says "this is going to take a long lock, better do it in a window."

How to tell: if your backfill is well designed (10k batches with a sleep, a lock bounded per batch), it doesn't take long locks. It can run at peak hour with no measurable impact. Capsule 06 goes deeper into the pattern.

How to fix it: trust the batch pattern. Measure in staging first (with wrk running) to validate it really doesn't impact latency. Then run it in prod with no declared downtime.

Mistake 4 (operational): not verifying the backfill finished before the contract

Symptom: a dev runs the 3 deploys in a rapid chain. The backfill doesn't finish (for some reason: the old app keeps inserting NULLs, there was an error halfway). The contract's SET NOT NULL fails because there are rows with NULL.

Why it happens: the migrations run on autopilot. There's no "stop" between them to verify.

How to tell: does your deploy pipeline run migrations with no intermediate verification? Do you trust that each migration finishes successfully with no checks?

How to fix it: between Deploy 2 (backfill) and Deploy 3 (contract), add a manual verification step:

SELECT COUNT(*) FROM tasks WHERE priority IS NULL;
-- It has to be 0 before moving on

If you automate the deploy, add the check as a precondition in the pipeline.

Mistake 5 (conceptual): thinking rollback is always simple

Symptom: a dev assumes "if something fails, I run alembic downgrade -1 and go back to the previous state." In the contract phase, the downgrade doesn't fully restore the original state without losing data (the NOT NULL column would go back to NULL, but the rows inserted during the contract don't get rolled back trivially).

Why it happens: the mental model of "a downgrade symmetric to the upgrade" doesn't apply universally. Some operations are irreversible without loss.

How to tell: for each migration, ask: "if I want to roll back after this deploy, is there data loss? what happens to rows created after the upgrade?"

How to fix it: document each phase's rollback window explicitly. Capsule 07 covers this in depth.

Exercises

Exercise 1: identify the phases in a real case

Given this product requirement: "add the column users.email_verified BOOLEAN NOT NULL DEFAULT false to the users table which has 8M rows, in 24/7 multi-tenant production."

Specify the expand-contract phases for this operation. For each phase, indicate:

1. Whether it's DDL (Alembic) or an app code change.
2. The exact SQL if it applies.
3. The lock it takes.
4. Whether the rollback window is still open afterward.

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN email_verified BOOLEAN NULL;

# A batched backfill
BATCH_SIZE = 10_000
SLEEP_SEC = 0.1
batch_min = 0
max_id = SELECT MAX(id) FROM users WHERE email_verified IS NULL
while batch_min <= max_id:
    UPDATE users SET email_verified = false
    WHERE id BETWEEN batch_min AND batch_min + BATCH_SIZE - 1
      AND email_verified IS NULL
    sleep(SLEEP_SEC)

SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT users_email_verified_not_null
    CHECK (email_verified IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_verified_not_null;
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_verified_not_null;
ALTER TABLE users ALTER COLUMN email_verified SET DEFAULT false;

Exercise 2: detect bugs in a badly written expand-contract

This migration tries to do expand-contract to add tasks.priority. It has three problems. Identify them and propose a fix.

# alembic/versions/wrong_expand_contract.py
def upgrade():
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=False, server_default="0"))
    op.execute("UPDATE tasks SET priority = 0")
    op.alter_column("tasks", "priority", server_default=None)


def downgrade():
    op.drop_column("tasks", "priority")

# Migration 1: expand
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=True))


def downgrade():
    op.drop_column("tasks", "priority")


# Migration 2: backfill (with batches)
def upgrade():
    op.execute("SET LOCAL statement_timeout = '0'")
    connection = op.get_bind()
    BATCH_SIZE = 10_000
    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE priority IS NULL")
    ).scalar()
    batch_min = 0
    while batch_min <= max_id:
        connection.execute(
            sa.text(
                "UPDATE tasks SET priority = 0 "
                "WHERE id BETWEEN :a AND :b AND priority IS NULL"
            ),
            {"a": batch_min, "b": batch_min + BATCH_SIZE - 1},
        )
        batch_min += BATCH_SIZE
        time.sleep(0.1)


def downgrade():
    op.execute("UPDATE tasks SET priority = NULL")


# Migration 3: contract
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute(
        "ALTER TABLE tasks ADD CONSTRAINT tasks_priority_check "
        "CHECK (priority IS NOT NULL) NOT VALID"
    )
    op.execute("ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_check")
    op.alter_column("tasks", "priority", nullable=False)
    op.execute("ALTER TABLE tasks DROP CONSTRAINT tasks_priority_check")
    op.execute("ALTER TABLE tasks ALTER COLUMN priority SET DEFAULT 0")


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

Exercise 3: implement an expand-contract for "rename a column"

On your FastAPI app with the tasks table, implement the rename of tasks.title to tasks.name. Write the complete Alembic files for the DDL steps (Deploy 1, Deploy 2.5 backfill, Deploy 5 drop). For the code steps (Deploy 2, 3, 4), describe in comments what changes.

# alembic/versions/00A_rename_title_to_name_step1_add_new.py
"""Rename tasks.title -> tasks.name (step 1: add new column)"""
from alembic import op
import sqlalchemy as sa

revision = "00A_rename_step1"
down_revision = "previous_revision"


def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.add_column("tasks", sa.Column("name", sa.String(), nullable=True))


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "name")


# ─────────────────────────────────────────────
# Deploy 2 (app code, no DDL):
#   Code change:
#     - The Task model adds `name: Mapped[str | None]`.
#     - INSERTs/UPDATEs write simultaneously to title and name.
#     - SELECTs keep reading from title.
#   Result: new rows have both fields. The old ones have name=NULL.
# ─────────────────────────────────────────────


# alembic/versions/00B_rename_title_to_name_step2_backfill.py
"""Rename tasks.title -> tasks.name (step 2: backfill name from title)"""
import time
from alembic import op
import sqlalchemy as sa

revision = "00B_rename_step2"
down_revision = "00A_rename_step1"


def upgrade():
    op.execute("SET LOCAL statement_timeout = '0'")
    connection = op.get_bind()
    BATCH_SIZE = 10_000
    SLEEP_SEC = 0.1

    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE name IS NULL")
    ).scalar() or 0

    batch_min = 0
    while batch_min <= max_id:
        connection.execute(
            sa.text(
                "UPDATE tasks SET name = title "
                "WHERE id BETWEEN :a AND :b AND name IS NULL"
            ),
            {"a": batch_min, "b": batch_min + BATCH_SIZE - 1},
        )
        batch_min += BATCH_SIZE
        time.sleep(SLEEP_SEC)

    null_count = connection.execute(
        sa.text("SELECT COUNT(*) FROM tasks WHERE name IS NULL")
    ).scalar()
    assert null_count == 0, f"Incomplete backfill: {null_count} rows with name IS NULL"


def downgrade():
    op.execute("UPDATE tasks SET name = NULL")


# ─────────────────────────────────────────────
# Deploy 3 (app code):
#   Code change:
#     - SELECTs now read from `name` (instead of title).
#     - INSERTs/UPDATEs keep writing to both.
#   Rollback window: if you roll back to Deploy 2, reads go back to title (which is still up to date).
# ─────────────────────────────────────────────


# ─────────────────────────────────────────────
# Deploy 4 (app code):
#   Code change:
#     - INSERTs/UPDATEs only write to `name`. They stop writing to title.
#     - The Task model no longer exposes `title`.
#   Rollback window: closing. After this deploy, title is obsolete.
# ─────────────────────────────────────────────


# alembic/versions/00C_rename_title_to_name_step5_drop_old.py
"""Rename tasks.title -> tasks.name (step 5: drop old column)"""
from alembic import op
import sqlalchemy as sa

revision = "00C_rename_step5"
down_revision = "00B_rename_step2"


def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "title")


def downgrade():
    # Recreate title — the data gets recovered from name
    op.execute("SET lock_timeout = '5s'")
    op.add_column("tasks", sa.Column("title", sa.String(), nullable=True))
    op.execute("UPDATE tasks SET title = name")
    op.alter_column("tasks", "title", nullable=False)

Exercise 4: in which phase can you roll back with no loss?

For the tasks.priority expand-contract case (3 deploys), describe the rollback window in each phase. Specifically:

1. After Deploy 1 (expand: ADD COLUMN priority INTEGER NULL), what happens if I roll back?
2. After Deploy 2 (the new app always writes priority), what happens if I roll back to Deploy 1?
3. After Deploy 3 (contract: SET NOT NULL), what happens if I roll back to Deploy 2?

Exercise 5: dropping a column with no downtime

Product asks you to remove the tasks.legacy_status column (deprecated 6 months ago, nobody uses it anymore). The table has 50M rows. Is it safe to do ALTER TABLE tasks DROP COLUMN legacy_status directly? If not, what pattern do you apply?

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "legacy_status")


def downgrade():
    # Recreating is destructive: the data was lost when it got dropped.
    # It only recreates the empty column, with no data recovery.
    op.execute("SET lock_timeout = '5s'")
    op.add_column(
        "tasks",
        sa.Column("legacy_status", sa.String(), nullable=True),
    )

Exercise 6: a hard case — changing a column's type

You have a column tasks.points INTEGER. Product asks you to change it to BIGINT because some tenants are hitting INTEGER's limits. The table has 30M rows. ALTER COLUMN TYPE INTEGER → BIGINT requires a rewrite.

Design the expand-contract.

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.add_column("tasks", sa.Column("points_v2", sa.BigInteger(), nullable=True))


def downgrade():
    op.drop_column("tasks", "points_v2")

class Task(Base):
    points: Mapped[int] = mapped_column(sa.Integer)  # legacy
    points_v2: Mapped[int | None] = mapped_column(sa.BigInteger)

    def set_points(self, val: int) -> None:
        self.points = val if val < 2_147_483_647 else None  # cap to INTEGER on overflow
        self.points_v2 = val

def upgrade():
    op.execute("SET LOCAL statement_timeout = '0'")
    connection = op.get_bind()
    BATCH_SIZE = 10_000
    SLEEP_SEC = 0.1
    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE points_v2 IS NULL")
    ).scalar() or 0
    batch_min = 0
    while batch_min <= max_id:
        connection.execute(
            sa.text(
                "UPDATE tasks SET points_v2 = points "
                "WHERE id BETWEEN :a AND :b AND points_v2 IS NULL"
            ),
            {"a": batch_min, "b": batch_min + BATCH_SIZE - 1},
        )
        batch_min += BATCH_SIZE
        time.sleep(SLEEP_SEC)

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.drop_column("tasks", "points")

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    # ALTER TABLE ... RENAME COLUMN is metadata-only, instantaneous
    op.alter_column("tasks", "points_v2", new_column_name="points")

Summary and next step

In this capsule you learned:

• Expand-contract is the fundamental pattern for evolving a schema with no downtime: add the new form without touching the old, migrate the data, swap the code, remove the old one at the end.
• The 3 canonical phases for ADD COLUMN NOT NULL: expand (add nullable), backfill (in batches), contract (set NOT NULL with the NOT VALID trick).
• A column rename requires 4-5 deploys because the old column has to coexist with the new one during the rolling deploy.
• Each Alembic migration has an upgrade and a downgrade that defines that phase's rollback window.
• The backfill gets done in batches (10k rows with a 100ms sleep) so as not to take a prolonged lock.
• The NOT VALID + VALIDATE trick avoids SET NOT NULL's blocking full scan on a large table.
• The rollback window closes at the contract: the first phases are reversible, the last one isn't.

Before moving on you should be able to:

• Design the expand-contract for any ADD COLUMN NOT NULL on a large table.
• Write the 3 concrete Alembic migrations with each one's upgrade and downgrade.
• Design the 4-5 deploy pattern for renaming a column with no downtime.
• Implement a batched backfill with a final check.
• Articulate the rollback window in each phase of the process.
• Recognize that ALTER COLUMN TYPE almost always requires a "parallel column" instead of an in-place transformation.

Next capsule — CREATE INDEX CONCURRENTLY and other non-blocking operations. You now master expand-contract's fundamental mechanic. Now you're going to learn the most important operation that runs without blocking production: CREATE INDEX CONCURRENTLY. You're going to understand the critical gotcha (it can't run inside a transaction) and how to solve it in Alembic with op.get_context().autocommit_block(). Without this, expand-contracts on large tables requiring new indexes are going to fail on you in production.

Resources

1. GitLab — Adding columns with default values — GitLab's detailed playbook on ADD COLUMN with a DEFAULT.
2. Strong Migrations — Backfilling data — the catalog of patterns for safe backfills.
3. PostgreSQL — ALTER TABLE: notes on NOT NULL — the official explanation of the NOT VALID + VALIDATE trick.
4. PlanetScale — Online schema migrations: rename column — how PlanetScale solves the rename with shadow tables (an alternative perspective).
5. Alembic — Operation Reference: alter_column — the complete alter_column syntax in Alembic.
6. Tinybird — How to safely drop columns in PostgreSQL — the specific case of a safe DROP COLUMN.
7. PostgreSQL Wiki — Slow Query Questions — diagnosing slow queries that can interfere with migrations.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: CREATE INDEX CONCURRENTLY and other non-blocking operations — the most important operation that runs without blocking production, with Alembic's critical gotcha.