Module 5: Zero-Downtime Migrations

Module overview

Every app that scales reaches the moment where an ALTER TABLE takes a minutes-long exclusive lock on a live table, takes production down, and costs the team a postmortem. The solution has existed for years — the expand-contract pattern, CREATE INDEX CONCURRENTLY, lock_timeout and statement_timeout, batched backfills, blue-green with a DB — but it's badly documented for Alembic. Most of the serious resources (the Strong Migrations gem, GitLab's database guide, PlanetScale's schema migration philosophy) are written for Rails or proprietary tooling. This module closes that gap for the path's stack: PostgreSQL 16+, Alembic 1.13+, SQLAlchemy 2.0 async.

This module is operational and nerve-wracking (in the good sense). Every technique you learn gets executed in production at 11pm with the app serving traffic. If it goes wrong, they wake you at 3am. That's why you're going to write real Alembic code (not pseudocode), you're going to configure lock_timeout as a habit in every dangerous migration, you're going to do batched backfills with progress metrics, and you're going to have a runbook for "what do I do if the migration hangs" with exact commands.

By the end of this module you'll have executed a real expand-contract on a table with continuous traffic and measured with wrk that no request failed during the migration. That same technique is the one module 8 (TaskFlow) is going to apply to the final capstone project.

Where are we? Where are we going?

What you already know:

Module 1: performant cursor pagination even on deep pages.
Module 2: soft deletes with partial indexes and their alternatives (archive tables, partitioning).
Module 3: audit logs with triggers, history tables, and lightweight event sourcing.
Module 4: multi-tenancy with RLS, schema-per-tenant, and a shared schema, integrated with FastAPI + SQLAlchemy async. You have an app that isolates tenants at the DB level, with automated isolation tests.

What you'll build here:

The operational techniques for evolving that same app's schema with no downtime, while the API keeps serving requests from every tenant. You're going to learn:

To recognize which schema operations take an exclusive lock and why that takes production down.
To break a dangerous operation (adding a NOT NULL column to a large table) into 3 safe deploys (expand → migrate → contract).
To create indexes on tables with active traffic without blocking writes (CREATE INDEX CONCURRENTLY with its Alembic gotchas).
To configure lock_timeout and statement_timeout so migrations fail fast instead of hanging.
To write idempotent migrations that are safe to re-run if the deploy retries.
To diagnose and resolve a hung migration in production with real psql commands over pg_locks and pg_stat_activity.
To understand blue-green with a DB and its real limits (additive-only schema changes).

What comes after:

Module 6 (Optimistic Locking + Schema Versioning) opens the "Concurrency and Versioning" block. The transition is direct: you already know how to evolve the schema with no downtime, but your API's contract also evolves — and the clients don't update at the same time. Optimistic locking handles conflicts in concurrent data; schema versioning handles conflicts in contracts. Both start from the same principle: "assume something changed since the last time you saw the system, and handle the conflict instead of preventing it."

Professional objective

By completing this module you'll be able to execute schema evolutions on PostgreSQL databases in production with no measurable downtime, using Alembic as the main tool and the expand-contract pattern as the operational mechanic.

Concretely:

Identify dangerous DDL operations (ADD COLUMN ... NOT NULL DEFAULT, ALTER COLUMN TYPE, ADD CONSTRAINT FOREIGN KEY, CREATE INDEX without CONCURRENTLY) and their safe alternatives.
Implement expand-contract in 3 phases with concrete Alembic files: expand (additive), migrate (backfill + swap), contract (cleanup).
Configure lock_timeout and statement_timeout as a habit in every migration.
Create online indexes with CREATE INDEX CONCURRENTLY, solving the gotcha that Alembic wraps every migration in a transaction.
Execute a zero-downtime migration live with measured traffic (wrk running in the background) and validate that zero requests failed.
Diagnose a hung migration with queries over pg_locks and pg_stat_activity, and decide between killing it or waiting.
Document the process in a reusable RUNBOOK-MIGRATION.md for the team.

Why does this module matter?

1. It's the module that saves you 3am postmortems. Every SaaS that scales reaches a moment where a junior dev runs alembic upgrade head with a dangerous ALTER TABLE on a production table, the app stops responding for minutes, and the incident channel kicks off. This module's techniques are what separate the team that executes DDL with confidence from the one that avoids migrations out of fear (and ends up with chronic schema rot).

2. It's what separates "I can do migrations" from "I can do migrations in 24/7 multi-tenant production." Knowing how to run alembic upgrade isn't the same as knowing how to run it when there are three enterprise tenants with a 99.95% SLA and the table has 50M rows. The difference is in this module's techniques.

3. It's the skill any senior SaaS interview asks about. "Tell me how you'd add a NOT NULL column to a table with 100M rows in production" is a standard question. The correct answer is expand-contract in 3 deploys with a batched backfill and lock_timeout. Coming out of this module with that answer articulated and real code to back it up is a direct advantage.

4. It's the technical prerequisite for the final project. Module 8's project (TaskFlow) includes a zero-downtime migration executed live as a measurable deliverable. Without this module's techniques, that deliverable is opaque. With them, it's the natural culmination.

5. It's the module that closes the "Live Operations" block. Together with module 4 (multi-tenancy), this module covers everything that happens when the DB is serving real traffic. Multi-tenancy defines how you separate data between customers; zero-downtime migrations define how you evolve that data's structure without any customer noticing the change.

A scenario that illustrates the module

Imagine you're the backend lead of a B2B SaaS with 200 active customers, peaks of 5k requests per minute, and three enterprise tenants with a contractual 99.95% SLA. Product asks you to add the column tasks.priority INTEGER NOT NULL DEFAULT 0 for a new "priority tasks" feature. The tasks table has 12M rows spread across the 200 tenants.

If you do it naively:

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

PostgreSQL takes an ACCESS EXCLUSIVE LOCK on the tasks table. On PostgreSQL 11+ with DEFAULT 0 the lock lasts seconds (not minutes) because it no longer rewrites the whole table, but it still blocks ALL access to the table during those seconds. If that ALTER coincides with the traffic peak, dozens of requests queue up, the timeouts fire, the customers start seeing 500 errors, and 5 minutes later they're calling your phone.

With this module's techniques you do it like this:

Deploy 1 (expand): ALTER TABLE tasks ADD COLUMN priority INTEGER NULL — adds the nullable column, with no DEFAULT requiring a rewrite. A millisecond lock. The old app doesn't know about the column; the new app uses it when it's available.
Batched backfill: a script (or a manual migration) that runs UPDATE tasks SET priority = 0 WHERE priority IS NULL AND id BETWEEN $1 AND $2 in batches of 10k rows with a 100ms sleep between batches. Each UPDATE takes a short lock, it doesn't block other writes to other rows. It takes minutes but it doesn't impact traffic.
Deploy 2 (migrate): the new app already always writes priority in INSERTs. Any new row has a priority. The old ones are already backfilled.
Deploy 3 (contract): ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL — it only scans to verify there are no NULLs (which you know because the backfill finished). A short lock. ALTER TABLE tasks ALTER COLUMN priority SET DEFAULT 0 (optional).

Throughout the whole process, the API kept serving requests. No customer noticed anything. Your 99.95% SLA stays intact. That's exactly what you're going to learn to do in this module, layer by layer, with real Alembic code.

Module map

Capsule	Topic	What you'll learn
02	Why migrations break production	PostgreSQL locks, which DDL operations are dangerous, maintenance windows vs zero-downtime
03	The expand-contract pattern	The fundamental mechanic in 4 phases (expand → migrate data → swap → contract) with runnable Alembic code and a rollback per phase
04	`CREATE INDEX CONCURRENTLY` and non-blocking operations	How to create online indexes, the "it can't run in a transaction" gotcha, `autocommit_block()` in Alembic
05	`lock_timeout` and `statement_timeout` in migrations	The defensive pattern: set timeouts at the start of every dangerous migration so it fails fast instead of hanging
06	Alembic in production: safe patterns	Separating the app deploy from the migration deploy, idempotence, batched backfills, a runbook for a hung migration
07	Blue-green and database rollback strategies	When blue-green works and when it doesn't (additive-only), the rollback window in each phase of expand-contract
08	Project: a live zero-downtime migration	Executing expand-contract on TaskFlow with `wrk` running in the background, validating 0 failed requests, documenting the runbook

The module's narrative flow:

We start by understanding the problem (capsule 02: why an ALTER TABLE takes production down). Then you learn the fundamental mechanic (capsule 03: expand-contract). Then you add the complementary techniques: online indexes (04) and defensive timeouts (05). You level up to production operation (06: idempotence, backfills, a runbook). You close by learning the limits (07: blue-green only applies in specific cases). And you consolidate everything in the project (08).

Connection with the integrative project

The final project (TaskFlow, module 8) includes as a measurable deliverable a zero-downtime migration executed live:

Operation: adding the column tasks.priority INTEGER NOT NULL DEFAULT 0 to a table with 1M rows (this module) or 5M rows (module 8).
Pattern: expand-contract in 3 separate deploys.
Validation: wrk -t4 -c50 -d600s running against GET /tasks during the whole migration. At the end, the count of failed requests = 0.
Documentation: a RUNBOOK-MIGRATION.md with the exact steps, times per phase, what to do if something fails.

Module 5 takes you to the point where you can execute this complete flow in your module mini-project (a simpler table, 1M rows). When you get to module 8, you're going to apply the same technique to TaskFlow's complete system with multi-tenancy + RLS + audit logs enabled. That's why this module comes first: you need to internalize the techniques in isolation before applying them in a system with all the pieces live.

What is NOT covered in this module

Soft deletes — covered in module 2.
Audit logs and history tables — covered in module 3.
Multi-tenancy (RLS, schema-per-tenant, shared schema) — covered in module 4. Here we assume your app is already multi-tenant; the migrations you're going to execute respect that isolation.
Optimistic locking and API schema versioning — covered in module 6. This module's migrations modify the DB schema; module 6's modify the API contract.
Bulk operations (COPY, batch inserts, upserts) — covered in module 7. The batched backfills you'll learn here are a simple form of bulk; module 7 goes deeper.
Partitioning as a scalability technique — mentioned in module 2 as an alternative to soft deletes; it goes deeper in guide #14 (Advanced PostgreSQL).
Physical replication (streaming replication, logical replication) — outside the guide's scope. That's DBA territory.
Migrations between engines (PostgreSQL → MySQL, etc.) — out of scope.

Traps to avoid while taking the module

1. "Zero-downtime migrations are magic." They aren't. They're a disciplined technique that requires planning more deploys, writing more code, and accepting that the schema lives in an intermediate state for days. The "magic" is the discipline. If you expect a single command that solves everything, you're going to get frustrated. The reality is: 3 deploys, a 2-week window, 1 batched backfill with metrics. Mastering that is the skill.

2. "If I have little traffic I don't need this." Sooner or later you're going to have traffic. And when you do, you're going to have tables with millions of rows. And the reflexes you build now (always CREATE INDEX CONCURRENTLY, always lock_timeout, never ADD COLUMN ... NOT NULL DEFAULT) are the ones that save you at that moment. Adopt the patterns now, even if your DB has 10k rows.

3. "Alembic will guide me." Alembic generates DDL but it has no opinion on operational safety. It lets you run ADD COLUMN ... NOT NULL DEFAULT 'foo' without blinking. The responsibility for the migration being safe is the author's. That's why this module teaches which patterns to avoid and how to express the safe ones.

4. "I'm going to skip expand-contract because it's laborious." It is laborious, yes. It's also the only thing that works for evolving a schema with no downtime on large tables. If you skip it, you're going to do a dangerous ALTER TABLE, you're going to take production down, and you're going to learn the lesson the expensive way. Better to invest the extra 2-3 hours of planning in every big migration.

5. "Blue-green is the universal solution." It isn't. Blue-green with a DB only works with additive-only changes (adding nullable columns, adding tables). Renames, drops, and type changes break blue-green. Capsule 07 debunks this myth explicitly.

6. "If the migration fails, I roll back with alembic downgrade." Sometimes yes, sometimes no. A migration that already wrote to NOT NULL doesn't get "un-NOT-NULLified" trivially without losing data. A migration that dropped a column doesn't "un-drop" it. Rollback in production is a planning process, not a command. Capsule 07 covers this.

Self-assessment question

Before starting this module, can you answer?

What is an ACCESS EXCLUSIVE LOCK and which PostgreSQL operations take it? (You saw it superficially in guide #8; here we apply it in depth.)
Do you know the difference between alembic upgrade head and alembic upgrade +1? (Necessary for controlled deploys.)
Are you clear on what a "rolling deploy" is and why it matters for zero-downtime migrations? (If not, read up on the topic first before capsule 03.)
Have you executed at least one migration with Alembic in your own FastAPI app? (If not, review guide #8's Alembic section.)
Do you understand why UPDATE table SET col = 0 on a 10M-row table can be problematic? (We cover it in capsule 06, but you should already suspect it.)

If you're unsure about several, go back to guide #8 (the Alembic and isolation levels section) and to guide #12 (the locks section). This module assumes that foundation.

Evidence of success

By the end of the module you'll know you succeeded if:

You can run alembic upgrade head on a 1M-row table in an app serving traffic, without a single request failing, validated with wrk in the background.
You can diagnose in under 5 minutes why a migration is hung using only psql and queries over pg_locks / pg_stat_activity.
You can explain to a teammate the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY and why the second can't run inside an Alembic transaction.
Your module mini-project includes a RUNBOOK-MIGRATION.md a new teammate could follow step by step to run the same migration in their environment.
You immediately recognize dangerous patterns in code review: ADD COLUMN ... NOT NULL DEFAULT 'foo', ALTER COLUMN ... TYPE, CREATE INDEX ON big_table, UPDATE table SET ... WHERE with no batching.
You've internalized the reflex of opening every dangerous migration with op.execute("SET lock_timeout = '5s'").

We start in the next capsule

Capsule 02 opens the module with the "why": what an exclusive lock is in PostgreSQL, which DDL operations take it, and why that takes production down. It's the capsule that lays the mental model for all the others. Without understanding it, the techniques that come (expand-contract, CONCURRENTLY, lock_timeout) look like arbitrary ceremony. With it, every technique has a concrete why.

Before moving on, make sure you have:

PostgreSQL 16+ running locally or in Docker.
A FastAPI app with Alembic 1.13+ configured (it can be module 4's if you completed it).
A test table with at least 100k rows to experiment with (it can be tasks with synthetic data).
wrk installed (brew install wrk on macOS, apt install wrk on Linux) for the continuous-traffic experiments.
Access to psql to inspect pg_locks and pg_stat_activity when we learn to diagnose locks.

Resources for the module

GitLab — Database migration guide — the best public reference on zero-downtime migrations. Based on years of operating GitLab in production at scale.
PostgreSQL — Explicit Locking — the official documentation on lock modes, which operations take each lock, and the conflict matrix.
Strong Migrations (Rails gem) — README — the patterns apply even though the tool is different. Its list of "unsafe operations" is the best public catalog.
Alembic — Operation Reference — the complete reference for the operations Alembic exposes (op.add_column, op.create_index, op.execute, etc.).
Squawk — PostgreSQL linter for migrations — an open-source linter that detects dangerous patterns in SQL migrations. Useful as a guardrail in CI.
PlanetScale — Schema migrations philosophy — a modern view of migrations as a continuous process, not one-off events.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: Why migrations break production — the PostgreSQL lock mental model that justifies all the module's techniques.

← PreviousProject: multi-tenant TaskFlow with RLS Next →Why migrations break production