Migrations & Zero-Downtime Changes

Migrations are versioned, reviewed scripts that move a database schema forward. Zero-downtime changes are schema changes planned so old code and new code can both survive while a deploy is rolling out.

The hard part is not typing ALTER TABLE. You learned that in 9.4. The hard part is remembering that code and schema never change everywhere at the same instant.

Migration Files, Not Console Folklore

A migration is a source-controlled file with one job: change the database from version N to version N+1. Teams use tools such as Alembic, Flyway, dbmate, Prisma migrations, and Django migrations to track which files have run in each environment.

The file names vary, but the shape is recognizable:

migrations/
  018_add_customer_display_name.sql
  019_backfill_customer_display_name.sql
  020_require_customer_display_name.sql

Good migrations are reviewed like application code. They are usually irreversible-by-default in production: you may write a down migration for local development, but dropping a column, losing data, or undoing a backfill is not something a safe deploy runner should do casually. The rollback plan for a live migration is often "ship compatible code forward," not "pretend the schema never changed."

Tooling helps with ordering and bookkeeping. It does not make a dangerous schema change safe by itself.

The Deploy-Order Problem

The classic trap is a backwards-incompatible schema change:

-- tempting, but dangerous if app code still reads customer_name
ALTER TABLE app_customers
RENAME COLUMN customer_name TO display_name;

If one app server has new code and another still has old code, one of them is wrong. Old code asks for customer_name; new code asks for display_name; the database has only one of those names.

Expand, Migrate, Contract

The production pattern is expand → migrate → contract:

expand:   add the new nullable column / table / index / compatibility path
migrate:  backfill data and deploy code that reads or writes both shapes safely
contract: remove the old column / old write path after all callers moved

Here is the live, small version. It proves syntax, state, and the safe backfill rhythm; it does not prove production lock behavior.

CREATE TABLE app_customers (
  customer_id integer PRIMARY KEY,
  customer_name text NOT NULL,
  email text NOT NULL UNIQUE
);
 
INSERT INTO app_customers (customer_id, customer_name, email)
VALUES
  (1, 'Ada Lovelace', 'ada@example.com'),
  (2, 'Ken Thompson', 'ken@example.com'),
  (3, 'Priya Shah', 'priya@example.com'),
  (4, 'Tomas Ruiz', 'tomas@example.com');
 
ALTER TABLE app_customers
ADD COLUMN display_name text;
 
SELECT c.customer_id,
       c.customer_name
FROM app_customers c
WHERE c.display_name IS NULL
ORDER BY c.customer_id
LIMIT 2;
 
WITH batch AS (
  SELECT c.customer_id
  FROM app_customers c
  WHERE c.display_name IS NULL
  ORDER BY c.customer_id
  LIMIT 2
)
UPDATE app_customers c
SET display_name = c.customer_name
FROM batch b
WHERE b.customer_id = c.customer_id
RETURNING c.customer_id, c.display_name;
 
SELECT count(*) AS remaining_to_backfill
FROM app_customers c
WHERE c.display_name IS NULL;

CREATE TABLE
INSERT 0 4
ALTER TABLE
 customer_id | customer_name
-------------+---------------
           1 | Ada Lovelace
           2 | Ken Thompson
(2 rows)
 customer_id | display_name
-------------+--------------
           1 | Ada Lovelace
           2 | Ken Thompson
(2 rows)
 remaining_to_backfill
-----------------------
                     2
(1 row)

That rehearsal SELECT is not decoration. It is the 8.2 write ritual applied to migrations: prove the batch target, then update that batch. On a large table, a migration runner repeats small batches until no rows remain, rather than holding one broad transaction over the whole table.

After the backfill is complete, prove and enforce the new rule in steps:

SELECT c.customer_id,
       c.customer_name
FROM app_customers c
WHERE c.display_name IS NULL
ORDER BY c.customer_id;
 
UPDATE app_customers c
SET display_name = c.customer_name
WHERE c.display_name IS NULL
RETURNING c.customer_id, c.display_name;
 
ALTER TABLE app_customers
ADD CONSTRAINT app_customers_display_name_present
CHECK (display_name IS NOT NULL) NOT VALID;
 
ALTER TABLE app_customers
VALIDATE CONSTRAINT app_customers_display_name_present;
 
ALTER TABLE app_customers
ALTER COLUMN display_name SET NOT NULL;
 
SELECT cols.column_name,
       cols.is_nullable
FROM information_schema.columns cols
WHERE cols.table_name = 'app_customers'
  AND cols.column_name IN ('customer_name', 'display_name')
ORDER BY cols.column_name;

 customer_id | customer_name
-------------+---------------
           3 | Priya Shah
           4 | Tomas Ruiz
(2 rows)
 customer_id | display_name
-------------+--------------
           3 | Priya Shah
           4 | Tomas Ruiz
(2 rows)
ALTER TABLE
ALTER TABLE
ALTER TABLE
  column_name  | is_nullable
---------------+-------------
 customer_name | NO
 display_name  | NO
(2 rows)

This is the standard safe NOT NULL rollout: add the shape, backfill, add CHECK ... NOT VALID, VALIDATE, then set NOT NULL. The final contract step—dropping customer_name—waits until old code is gone. Do not combine it with the expand step.

Indexes, Lock Timeouts, And Retries

Indexes deserve the same deployment caution. PostgreSQL has CREATE INDEX CONCURRENTLY for building an index while ordinary reads and writes continue. The browser lab cannot prove the concurrency benefit, but a production migration file often looks like this:

-- migration 021_add_customer_email_lookup.sql
SET lock_timeout = '1s';
SET statement_timeout = '10min';
 
CREATE INDEX CONCURRENTLY app_customers_lower_email_idx
ON app_customers (lower(email));

The short lock_timeout is deliberate. If the migration cannot get the brief lock it needs, it fails quickly and the deploy runner retries later. That is better than sitting in a lock queue behind a busy table and then blocking application traffic behind itself. statement_timeout is a separate ceiling for the work once it starts.

Some migration tools wrap every file in one transaction by default. CREATE INDEX CONCURRENTLY cannot be run inside a transaction block, so the migration must be marked non-transactional in the tool's way. The exact flag differs by tool; the review question is stable: "Does this tool run this migration in a transaction, and is that allowed for this statement?"

Tooling Transcript

Migration tools differ in command names, not in the discipline they enforce:

# examples of the role these tools play, not install instructions
alembic revision --autogenerate -m "add customer display name"
flyway migrate
dbmate up
prisma migrate deploy
python manage.py migrate

The important facts to check in review:

• What SQL will run?
• Is the migration transactional or non-transactional?
• Does it take a lock that can block app traffic?
• Does it have a short lock_timeout and retry behavior?
• Has it been rehearsed on production-like data?

Rehearse On A Copy

A staging database with five rows does not tell you what a backfill does to a table with real size, skew, indexes, and old data. Before a risky migration, rehearse on a recent copy or snapshot of production data. You are checking the boring things that save incidents:

• dirty rows that break casts or constraints
• batch size that finishes predictably without giant transactions
• indexes and constraints built with the intended options
• application code that can run before, during, and after the migration
• rollback-forward plan if a deploy has to pause mid-pattern

Try the safe expansion below. The playground proves the sequence on a tiny table; production adds lock observation, retries, and rehearsal on a copy.

Section 18 put SQL inside application code: drivers, injection safety, ORMs, and migrations. Section 19 moves from building features to operating a database: users, permissions, backups, recovery, and monitoring.