Why migrations break production

Capsule overview

In module 4 you built a multi-tenant app with RLS protecting each customer's data. You already have DB-level guaranteed isolation. But if your next migration takes a 30-second lock on a table with 50M rows, every tenant sees downtime simultaneously. Zero downtime isn't optional when your app is 24/7 multi-tenant: a single enterprise tenant with a contractual SLA can penalize you for minutes of unavailability.

This capsule teaches you the mental model that justifies all the module's techniques. You're going to understand what an exclusive lock is in PostgreSQL, which DDL operations take it, why an apparently trivial ALTER TABLE can hang all of a table's queries, and the operational difference between a "maintenance window" (planned downtime, the old model) and "zero-downtime" (continuous deployment, the modern SaaS model).

By the end you'll be clear on which operations you can do without thinking and which require expand-contract. That classification is the filter you'll apply to every migration for the rest of your career.

Mental model: PostgreSQL as a library with shelves

Imagine the tasks table is a shelf in a library. Readers (SELECT queries) can read books simultaneously without getting in each other's way. Librarians (UPDATE/INSERT/DELETE queries) write in specific books without blocking readers who are in other books. That efficient coexistence is what PostgreSQL calls MVCC (Multi-Version Concurrency Control).

But sometimes the librarian needs to reorganize the entire shelf: add a new drawer, change the books' arrangement, repaint the structure. To do that safely, they need nobody else to be using the shelf while the reorganization lasts. That's an ACCESS EXCLUSIVE LOCK: the librarian kicks out all the readers and other librarians, does the work, and lets them back in when it's finished.

The problem is that while the librarian works, the readers and other librarians form a queue. If it takes 10 seconds, that's 10 seconds of queue. If it takes 5 minutes, that's 5 minutes of queue. The customers in the queue eventually give up (timeouts), your app starts returning 500 errors, and the customers complain.

Zero-downtime migrations are the techniques for reorganizing the shelf without kicking anyone out: make additive changes first (put an empty new drawer in without touching the existing ones), populate the new drawer gradually (while the readers keep using the old one), and only at the end change the "pointer" so everyone uses the new drawer. Each step is fast enough not to form a queue.

PostgreSQL's lock modes: the complete hierarchy

PostgreSQL has 8 levels of lock on tables, ordered from least restrictive to most restrictive. The conflict matrix defines which locks block which others.

The operational rule that matters:

• ACCESS EXCLUSIVE = blocks everything, including SELECTs. It's what takes production down. Almost every ALTER TABLE takes it.
• SHARE = blocks writes but allows reads. CREATE INDEX without CONCURRENTLY takes it. If your app is read-heavy, it can go unnoticed. If it's write-heavy, it also takes production down (on the writes).
• SHARE UPDATE EXCLUSIVE = compatible with writes and reads. CREATE INDEX CONCURRENTLY, normal VACUUM, and ANALYZE take it. It's what you want in production.

The query to see active locks in production (you're going to memorize it):

SELECT
    pg_class.relname AS table_name,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.pid,
    pg_stat_activity.query,
    age(now(), pg_stat_activity.query_start) AS waiting_for
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_class.relname = 'tasks'
ORDER BY pg_locks.granted DESC, pg_stat_activity.query_start;

Example output during a hung ALTER TABLE:

 table_name |        mode         | granted | pid  |              query               | waiting_for
------------+---------------------+---------+------+----------------------------------+-------------
 tasks      | ACCESS EXCLUSIVE    | t       | 1234 | ALTER TABLE tasks ADD COLUMN ... | 00:02:14
 tasks      | ACCESS SHARE        | f       | 5678 | SELECT * FROM tasks WHERE id=42  | 00:02:10
 tasks      | ROW EXCLUSIVE       | f       | 5679 | UPDATE tasks SET ... WHERE id=99 | 00:02:09
 tasks      | ACCESS SHARE        | f       | 5680 | SELECT count(*) FROM tasks       | 00:02:00

PID 1234 has the ACCESS EXCLUSIVE. The others have been waiting for minutes. Each one represents a real request that's hung. At 30s your customers start seeing errors. That's "the migration takes production down."

Worked example: when ALTER TABLE rewrites the table

There's a critical distinction many devs don't know: some DDL operations take a short lock (milliseconds), others take a long lock proportional to the table's size. The difference is whether the operation rewrites the table or only modifies metadata.

To illustrate it, let's set up a table with 1M rows and measure each kind of operation.

Setup: the test table

-- Create a table with 1M rows
CREATE TABLE migration_test (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO migration_test (tenant_id, title, description)
SELECT
    (random() * 100)::BIGINT,
    'Task ' || generate_series,
    'Description for task ' || generate_series
FROM generate_series(1, 1000000);

CREATE INDEX idx_migration_test_tenant ON migration_test(tenant_id);
ANALYZE migration_test;

-- Check the size
SELECT pg_size_pretty(pg_total_relation_size('migration_test'));
-- Output: ~120 MB (varies by version)

Operation 1: ADD COLUMN ... NULL (fast — metadata only)

-- Measure the time
\timing on

BEGIN;
ALTER TABLE migration_test ADD COLUMN priority INTEGER NULL;
COMMIT;
-- Time: ~5-15ms

Why it's fast: PostgreSQL only adds an entry in pg_attribute (metadata) marking the column as existing. The old rows don't get rewritten — when they get read, PostgreSQL returns NULL for that column by convention. A short lock, almost imperceptible.

Operation 2: ADD COLUMN ... NOT NULL DEFAULT 'foo' (depends on the PG version)

On PostgreSQL 10 and earlier:

ALTER TABLE migration_test ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
-- Time on PG 10: 30-90 seconds for 1M rows
-- Lock: ACCESS EXCLUSIVE for ALL that time

PostgreSQL rewrote the whole table to put the DEFAULT into every row. On a 50M-row table that was minutes of downtime. Catastrophic.

On PostgreSQL 11+ (a critical improvement):

ALTER TABLE migration_test ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
-- Time on PG 16: 5-20ms
-- Lock: a brief ACCESS EXCLUSIVE

Since PG 11, the DEFAULT doesn't get materialized physically in every row — it gets stored as metadata and returned dynamically for old rows. Only new rows write the real value. This changed the migration landscape.

The operational implication: if you're on PG 11+ (you should be), ADD COLUMN ... NOT NULL DEFAULT 'literal' is safe. But there are caveats:

• A DEFAULT with a volatile function (e.g. DEFAULT gen_random_uuid()) does rewrite the table, because each row needs a different value.
• A DEFAULT with an expression that changes (e.g. DEFAULT now()) also rewrites.

Check the caveat with this:

-- This does NOT rewrite (a constant literal)
ALTER TABLE migration_test ADD COLUMN flag1 BOOLEAN NOT NULL DEFAULT false;

-- This DOES rewrite (a volatile function)
ALTER TABLE migration_test ADD COLUMN uid UUID NOT NULL DEFAULT gen_random_uuid();
-- Time: 30-60s for 1M rows. Locked the whole time.

Operation 3: ALTER COLUMN TYPE (almost always rewrites)

ALTER TABLE migration_test ALTER COLUMN tenant_id TYPE BIGINT;
-- If the source type was already BIGINT: fast (it does nothing)
-- If the type changes (e.g. INTEGER → BIGINT): it rewrites the whole table.
-- Time: 60-120s for 1M rows. An ACCESS EXCLUSIVE lock.

Almost any ALTER COLUMN TYPE requires a rewrite. There are specific exceptions (e.g. VARCHAR(50) → VARCHAR(100) doesn't rewrite), but as a general rule, assume it rewrites.

Operation 4: CREATE INDEX without CONCURRENTLY

CREATE INDEX idx_test_title ON migration_test(title);
-- Time: 2-5s for 1M rows
-- Lock: SHARE — blocks writes (INSERT/UPDATE/DELETE) but allows reads

If your app is read-heavy, this lock goes unnoticed. If it's write-heavy, the INSERTs/UPDATEs queue up during those seconds. On a 100M-row table, those "seconds" are minutes. That's why the correct version is:

Operation 5: CREATE INDEX CONCURRENTLY (the production solution)

CREATE INDEX CONCURRENTLY idx_test_title_safe ON migration_test(title);
-- Time: 5-15s for 1M rows (slower than the blocking version)
-- Lock: SHARE UPDATE EXCLUSIVE — compatible with reads AND writes

The trade-off: slower (because it makes two passes over the table), but it doesn't block traffic. It's what you always want in production, except in an initial migration over an empty table.

The key distinction: maintenance window vs zero-downtime

There are two operational models for evolving the schema:

The old model: a maintenance window.

• You announce to the customers that the system will be down from 2am to 4am on Sunday.
• You stop the app, run all the migrations no matter how long they take, and bring up the updated app.
• It's operationally simple: there's no coexistence of schema/app versions.
• It works for internal apps, B2B with customers who accept windows, batch systems.
• It doesn't work for modern multi-tenant SaaS with global customers and a contractual SLA.

The modern model: zero-downtime.

• The app keeps serving traffic during the migration.
• The schema lives in an intermediate state (compatible with the old app AND the new app) for hours or days.
• The deploys are rolling (gradually: 10% of pods first, then 50%, then 100%).
• The migrations get broken down into safe atomic steps.
• It's what any production SaaS needs.

The operational distinction:

The choice isn't ideological, it's contextual. A B2B with 5 customers in a single time zone can live with windows. A SaaS with 1000 tenants distributed globally can't. This module's techniques are for the second case, which is where the current senior professional frontier lies.

Why does this matter in real work?

1. The difference between a senior and a junior in migrations is exactly this mental model. A junior runs alembic upgrade head and prays. A senior asks first "what kind of lock does this operation take?, does it rewrite the table?, how big is the table?, am I at peak hour?". That difference gets built by knowing what happens underneath.

2. Most serious DB incidents are badly-thought-out migrations. Public postmortems from GitLab, GitHub, Heroku, Notion — they all have stories of an ALTER TABLE that hung at peak hour and took the app down. The root cause is almost never "PostgreSQL is slow" — it's "the dev didn't compute the lock it was going to take."

3. The tools don't warn you. Alembic runs any DDL you ask it to, with no opinion on safety. PostgreSQL accepts any ALTER TABLE, with no warning that it's going to take a minutes-long lock. The responsibility for knowing is the code's author's.

4. In senior interviews it's a standard question. "What happens if I run ALTER TABLE users ADD COLUMN bio TEXT NOT NULL DEFAULT '' on a 100M-row table in production?" The correct answer involves: it depends on the PG version, on PG 11+ it's safe because a literal DEFAULT doesn't rewrite, on PG 10 and earlier it rewrites and blocks for minutes. Knowing that is a differentiator.

Traps and common mistakes

Mistake 1 (conceptual): assuming "a short lock" = "a safe deploy"

Symptom: a dev measures locally that their ALTER TABLE takes 50ms on a 10k-row table and decides to do it in production where the table has 50M rows. In production it takes 30 seconds. It takes the app down.

Why it happens: locks get held for the WHOLE operation. If the operation takes longer in production (from more data, from more concurrency), the lock lasts longer. Measuring locally doesn't extrapolate linearly.

How to tell: before any dangerous migration, ask: "does this operation rewrite the table? If so, time ≈ table_size / disk_throughput. On a 100GB table over SSD at 500MB/s, that's 200 seconds. A 200-second lock at peak hour = a catastrophe."

How to fix it: always measure against production data (or staging with a similar size) using EXPLAIN or by running it on a replica table of the real size. And even then, add lock_timeout to fail fast if the calculation was off.

Mistake 2 (operational): not knowing which PostgreSQL version has the DEFAULT improvement

Symptom: a dev reads a 2018 blog post saying "ADD COLUMN NOT NULL DEFAULT always rewrites the table." They design an elaborate expand-contract to avoid it. Unnecessarily: they're on PG 14 where it doesn't rewrite.

Why it happens: the improvement came in PG 11 (November 2018). A lot of prior documentation is obsolete. And blog posts don't always get updated.

How to tell: check your version: SELECT version();. If it says PG 11 or higher, ADD COLUMN col TYPE NOT NULL DEFAULT 'constant_literal' does NOT rewrite. If it says PG 10 or earlier, it does rewrite.

How to fix it: stay up to date with the release notes. And for doubtful cases, test in staging by measuring the lock with SELECT pg_sleep(5) in the background and monitoring pg_locks during the operation.

Mistake 3 (conceptual): confusing "it doesn't block reads" with "it doesn't impact production"

Symptom: a dev runs CREATE INDEX (without CONCURRENTLY) in production arguing "it only blocks writes, the reads keep working." The app is write-heavy (a CRM with lots of updates). In 30 seconds there are hundreds of UPDATEs queued, the timeouts fire, the customers see errors.

Why it happens: the "it only blocks X" logic sounds reassuring, but your app can depend critically on X. For a write-heavy app, "blocking writes" = "taking the app down."

How to tell: before any operation that takes any lock incompatible with production, look at your metrics: what % of your traffic is SELECTs vs writes? If writes are >10%, blocking them for seconds is problematic.

How to fix it: always use CREATE INDEX CONCURRENTLY except in initial migrations on empty tables. The simple rule: in production, avoid any lock more restrictive than SHARE UPDATE EXCLUSIVE.

Exercises

Exercise 1: classify operations by lock level

For each operation, indicate which lock it takes on the tasks table and whether it's safe to run in production with active traffic (assuming PostgreSQL 16):

1. SELECT * FROM tasks WHERE id = 42;
2. INSERT INTO tasks (title) VALUES ('foo');
3. ALTER TABLE tasks ADD COLUMN notes TEXT NULL;
4. ALTER TABLE tasks ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
5. ALTER TABLE tasks ADD COLUMN uid UUID NOT NULL DEFAULT gen_random_uuid();
6. CREATE INDEX idx_tasks_title ON tasks(title);
7. CREATE INDEX CONCURRENTLY idx_tasks_title ON tasks(title);
8. DROP TABLE tasks;
9. VACUUM tasks;
10. VACUUM FULL tasks;

Exercise 2: measure a real lock on a local table

On the migration_test table with 1M rows you created in the worked example, run each of these operations and measure the time. Before each one, open a second psql session and run SELECT count(*) FROM migration_test; repeatedly. Note when the SELECT hangs.

1. ALTER TABLE migration_test ADD COLUMN flag_a BOOLEAN NULL;
2. ALTER TABLE migration_test ADD COLUMN flag_b BOOLEAN NOT NULL DEFAULT false;
3. ALTER TABLE migration_test ADD COLUMN uid UUID NOT NULL DEFAULT gen_random_uuid();
4. CREATE INDEX idx_test_desc ON migration_test(description);
5. CREATE INDEX CONCURRENTLY idx_test_created ON migration_test(created_at);

SELECT
    pg_class.relname,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.query
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_class.relname = 'migration_test';

Exercise 3: design a safe migration plan

Product asks you: "add the column tasks.priority INTEGER NOT NULL DEFAULT 0 to the tasks table with 50M rows in production. The app is 24/7, multi-tenant, there are three enterprise tenants with a 99.95% SLA. You're on PostgreSQL 16."

Design the plan step by step. For each step, specify: the exact SQL, the lock it takes, the estimated time, and the corresponding deploy.

ALTER TABLE tasks ADD COLUMN priority INTEGER NULL;

-- In batches of 10k so as not to take a long lock
DO $$
DECLARE
    rows_updated INTEGER := 1;
    batch_min BIGINT := 0;
    batch_max BIGINT := 10000;
    max_id BIGINT;
BEGIN
    SELECT MAX(id) INTO max_id FROM tasks;
    WHILE batch_min <= max_id LOOP
        UPDATE tasks
        SET priority = 0
        WHERE id BETWEEN batch_min AND batch_max
          AND priority IS NULL;

        batch_min := batch_max + 1;
        batch_max := batch_max + 10000;
        PERFORM pg_sleep(0.1);  -- 100ms between batches
    END LOOP;
END $$;

-- Set lock_timeout to fail fast if something hangs
SET lock_timeout = '5s';

-- Make it NOT NULL (PostgreSQL only scans to verify there are no NULLs)
ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL;

-- Optional: add the DEFAULT too
ALTER TABLE tasks ALTER COLUMN priority SET DEFAULT 0;

SET lock_timeout = '5s';

-- Step 1: add a NOT VALID CHECK constraint (a short lock)
ALTER TABLE tasks ADD CONSTRAINT tasks_priority_not_null
    CHECK (priority IS NOT NULL) NOT VALID;

-- Step 2: validate the constraint (a SHARE UPDATE EXCLUSIVE lock, it doesn't block writes)
ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_not_null;

-- Step 3: now SET NOT NULL is metadata-only (PG already knows it holds)
ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL;

-- Step 4: clean up the redundant CHECK
ALTER TABLE tasks DROP CONSTRAINT tasks_priority_not_null;

Exercise 4: read a fictional postmortem and diagnose it

Your team shares this postmortem with you:

Incident: the API was returning 500 errors between 14:32 and 14:38 last Thursday. Root cause: the deployment of the add_user_preferences_column.py migration, which ran:

ALTER TABLE users ADD COLUMN preferences JSONB NOT NULL DEFAULT '{"theme": "light", "lang": "es"}';

on the users table with 28M rows. The operation took 4 minutes during peak hour. We're on PostgreSQL 14.

What are the two things the dev did wrong? How would you have done it?

-- Deploy 1: expand
ALTER TABLE users ADD COLUMN preferences JSONB NULL;

-- A batched backfill (5000 rows per batch)
-- Run as a standalone script, not as a migration
DO $$
DECLARE
    batch_min BIGINT := 0;
    batch_size INTEGER := 5000;
    max_id BIGINT;
BEGIN
    SET LOCAL statement_timeout = '0';  -- the backfill can take a while, we don't want a timeout
    SELECT MAX(id) INTO max_id FROM users;
    WHILE batch_min <= max_id LOOP
        UPDATE users
        SET preferences = '{"theme": "light", "lang": "es"}'::JSONB
        WHERE id BETWEEN batch_min AND batch_min + batch_size - 1
          AND preferences IS NULL;
        batch_min := batch_min + batch_size;
        PERFORM pg_sleep(0.05);
    END LOOP;
END $$;

-- Deploy 2: the new app writes preferences in INSERTs
-- (a code change, no DDL)

-- Deploy 3: contract with the NOT VALID trick
SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT users_preferences_not_null
    CHECK (preferences IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_preferences_not_null;
ALTER TABLE users ALTER COLUMN preferences SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_preferences_not_null;
ALTER TABLE users ALTER COLUMN preferences SET DEFAULT '{"theme": "light", "lang": "es"}';

Summary and next step

In this capsule you learned:

• PostgreSQL has 8 levels of lock, ordered from least to most restrictive. The conflict matrix defines which operations can coexist.
• ACCESS EXCLUSIVE blocks EVERYTHING, including SELECTs. Almost every ALTER TABLE takes it. It's what takes production down when it lasts more than a few seconds.
• The critical distinction is whether the operation rewrites the table. Metadata operations are fast (ms). Operations that rewrite are proportional to the table's size.
• PostgreSQL 11+ optimized ADD COLUMN ... NOT NULL DEFAULT literal so it does NOT rewrite. But DEFAULTs with a volatile function (gen_random_uuid, now) do rewrite.
• CREATE INDEX without CONCURRENTLY takes SHARE, which blocks writes. For production, always use CONCURRENTLY (it takes SHARE UPDATE EXCLUSIVE, compatible with everything).
• The choice between a "maintenance window" and "zero-downtime" isn't ideological, it's contextual. Modern multi-tenant SaaS requires zero-downtime.

Before moving on you should be able to:

• Classify any DDL operation by the lock it takes and decide whether it's safe to run in production.
• Use the pg_locks + pg_stat_activity query to inspect active locks in real time.
• Distinguish between "a short lock" (metadata-only) and "a lock proportional to the table's size" (a rewrite).
• Recognize when an operation rewrites the table (a DEFAULT with a volatile function, ALTER COLUMN TYPE in most cases).
• Articulate why a modern multi-tenant SaaS needs zero-downtime and what that means operationally.

Next capsule — The expand-contract pattern. You already understand the problem (locks that take production down) and the friction points (which operations are dangerous). Now you're going to learn the fundamental mechanic that solves the problem: breaking a dangerous operation into 3-4 safe steps, each one short enough not to form a queue, and keeping the app working with coexisting schema versions. It's the module's central piece. You're going to write the 3-4 concrete Alembic migrations, with their rollback per phase, over a real case (adding a NOT NULL column to the tasks table).

Resources

1. PostgreSQL — Explicit Locking (official) — the complete, definitive conflict matrix. A permanent bookmark.
2. PostgreSQL — ALTER TABLE notes — which operations rewrite the table and which don't, by version. Required reading.
3. Strong Migrations — Unsafe operations list — the most complete catalog of dangerous operations (written for Rails, the concepts apply universally).
4. GitLab — Avoiding downtime in migrations — the most detailed operational guide from a team operating PostgreSQL at enterprise scale.
5. Citus Data — Schema changes in PostgreSQL: a comprehensive guide — an analysis with real benchmarks of each lock's costs.
6. PostgreSQL — Monitoring locks — ready-made queries for diagnosing hung locks, maintained by the community.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: The expand-contract pattern — the fundamental mechanic that breaks a dangerous operation into safe steps.