Chapter 9 — Migrations & Schema Evolution

The why

Your database schema will change. It has to — the product evolves, requirements shift, you discover a better design. But changing a live database with millions of rows and thousands of concurrent users is like replacing the engine of a plane while it's in the air. Migrations are the procedure for doing that safely.

Core concepts

The migration workflow

Every migration tool (Prisma Migrate, Drizzle Kit, Flyway, golang-migrate) follows the same pattern:

1. Modify the schema definition (schema.prisma, TypeScript, SQL)
2. Generate a migration file (timestamped SQL file)
3. Review the migration SQL (the most important step!)
4. Apply the migration to development → staging → production
5. Commit the migration file to Git

-- prisma/migrations/20250115000000_add_user_bio/migration.sql
-- This is what Prisma generates. READ IT before applying.
 
ALTER TABLE "users" ADD COLUMN "bio" TEXT;
 
-- Every migration file is immutable once applied to production.
-- Never edit a migration that's already been deployed.

The expand-contract pattern (zero-downtime migrations)

Any schema change that would block reads or writes must be done in three phases:

Phase 1 — Expand: Add new columns/tables. Old code ignores them. New code can start using them. Phase 2 — Migrate: Backfill data, run both old and new code paths. Phase 3 — Contract: Remove old columns/tables once all code has been updated.

-- Goal: Rename "full_name" to "display_name" without downtime
 
-- Phase 1: EXPAND — add the new column (nullable)
ALTER TABLE users ADD COLUMN display_name TEXT;
 
-- Deploy code that writes to BOTH columns:
-- INSERT INTO users (full_name, display_name) VALUES ($1, $1);
-- UPDATE users SET full_name = $1, display_name = $1 WHERE id = $2;
 
-- Phase 2: MIGRATE — backfill existing rows
UPDATE users SET display_name = full_name WHERE

Adding a NOT NULL column to a massive table

This is the scariest migration. Adding a NOT NULL column to a 50M-row table without a default value requires locking the entire table while PostgreSQL checks that every row satisfies the constraint. The safe approach:

-- Step 1: Add as nullable (instant in PG 11+, metadata-only)
ALTER TABLE users ADD COLUMN preferred_language TEXT;
 
-- Step 2: Add a DEFAULT (instant — new rows get the default. Old rows stay NULL.)
ALTER TABLE users ALTER COLUMN preferred_language SET DEFAULT 'en';
 
-- Step 3: Backfill existing rows in batches
-- Run this in a script, not inside a migration (could take hours)
UPDATE users SET preferred_language =

Migration anti-patterns

Rolling back a bad migration

Rollback procedures, from best to worst:

1. Rollback migration exists. You wrote a down migration (or Prisma's migrate diff can generate one). Apply it. Tested? Good.
2. No rollback migration, but expand-contract was used. The old column still exists. Deploy a revert of the code change. Old code picks up the old column. You're fine.
3. No rollback, destructive change. You dropped a column and the old code references it. Restore from backup. This is why backups exist — and why you test them.

Real-world example: How Shopify handles 100,000+ migrations

Shopify runs thousands of database migrations per year across hundreds of databases. Their process:

1. Every migration is reviewed by a database reliability engineer.
2. Migrations that lock tables are automatically rejected by CI — only CONCURRENTLY and batched operations are allowed.
3. Large data changes run through a background job system (Shopify's "Large Data Migrations" framework), not in migration files.
4. Every migration has a tested rollback. If rollback is impossible, it's flagged and requires VP approval.
5. Schema changes are dark-launched: the code writes to both old and new columns for a week before the old column is removed.

Common mistakes

Production notes

• Run migrations as a CI/CD step, not as part of application startup. This prevents concurrent migration attempts and handles failures cleanly.
• Use lock_timeout for migrations. If a migration can't acquire a lock within N seconds, it fails instead of blocking other queries.
• Monitor migration duration. If a migration that normally takes 5 seconds takes 5 minutes, something is wrong — likely waiting for a lock.
• Have a rollback plan before you migrate. If you can't explain how to undo it in 2 sentences, don't run it.

Performance notes

• Batch large data changes. Never run UPDATE on 10M rows in one query. Use LIMIT and a cursor (WHERE id > $lastId), with brief sleeps between batches to avoid overwhelming replication.
• CREATE INDEX CONCURRENTLY is ~2x slower but doesn't block writes. Always use it in production.
• VACUUM after large UPDATE/DELETE migrations to reclaim space and update statistics.

Security notes

• Migrations run with elevated database permissions. A malicious migration can drop tables, create backdoor users, or exfiltrate data. Review every migration in code review.
• Never hardcode secrets in migration files. Use environment variables or a secrets manager.

Exercises

Beginner

1. Create a simple Prisma schema with a User model. Generate and apply a migration. Inspect the generated SQL. Then add a bio column, generate a new migration, and apply it.
2. Using raw SQL, write an UP migration that adds a column and a DOWN migration that drops it. Apply both and verify.

Intermediate

1. Simulate a zero-downtime column rename: create a users table with full_name. Add display_name, dual-write, backfill, switch reads, drop full_name. Write the script for each phase.
2. Add a NOT NULL column with a default to a table with 1M rows (use generate_series). Time the migration with and without the safe approach (add nullable → backfill → set NOT NULL).

Advanced

1. Set up a CI pipeline that runs migrations against a staging database, runs a smoke test, and only then promotes to production. Use GitHub Actions or similar.
2. Write a migration linter that rejects migrations containing: DROP TABLE, RENAME COLUMN, CREATE INDEX (without CONCURRENTLY), or ALTER TABLE ... ADD COLUMN ... NOT NULL (without a DEFAULT).

Interview questions

Beginner

1. What is a database migration? A version-controlled SQL script that changes the database schema — adding/removing tables, columns, indexes, or constraints. Migrations are applied in order, and each one is tracked so you never apply the same migration twice.

2. Why shouldn't you edit a migration file that's already been applied to production? The migration tool tracks which migrations have run via a hash or checksum. Editing a deployed migration changes the hash, causing the tool to detect "drift" and either fail or re-apply changes that may already exist.

3. What does CREATE INDEX CONCURRENTLY do differently? It builds the index without locking the table for writes, allowing normal operations to continue. It takes longer and can't run inside a transaction.

Senior

4. Explain the expand-contract pattern for zero-downtime migrations. Three phases: (1) Expand — add new columns/tables without removing old ones. Deploy code that writes to both old and new. (2) Migrate — backfill data from old to new. Deploy code that reads from new with old as fallback. (3) Contract — remove old columns/tables only after all code references have been updated and verified. This ensures that at no point does the schema change break the currently running code.

5. How do you add a NOT NULL column to a 50-million-row table without downtime? (1) Add as nullable (instant in PG 11+). (2) Set a DEFAULT value (instant). (3) Backfill existing rows in batches using a cursor-based loop with LIMIT. (4) Once all rows have values, set NOT NULL (validates the constraint). This avoids locking the table while checking 50M rows.

6. What's your rollback strategy if a migration fails halfway through? Ideally: the migration is designed to be reversible — an up and down script exist. If partially applied (e.g., column added but backfill failed), determine if the partial state is safe. If safe, complete the backfill manually. If not safe, write and apply a compensating migration. Last resort: restore from backup. This is why you test backups before migrating.

Summary

Migrations are how databases evolve safely. The expand-contract pattern lets you change schemas without downtime: add first, migrate data, remove later. Always review generated SQL. Always have a rollback plan. Never edit deployed migrations. Add NOT NULL columns in phases. Use CONCURRENTLY for indexes. Batch large data changes. Test your backups.

Quick recall

• Expand first, contract last. Every migration must be backward-compatible with running code.
• Never edit a deployed migration. Never run migrations manually on production.
• CREATE INDEX CONCURRENTLY — always in production.
• Add NOT NULL: nullable → default → backfill → NOT NULL.
• Batch large UPDATEs with LIMIT. Test backups before migrating.
• Migrations as CI/CD step, not app startup. Set lock_timeout and statement_timeout.

Quiz

1. What are the three phases of the expand-contract pattern? Expand (add new), Migrate (backfill data), Contract (remove old).
2. Why is renaming a column in a single migration dangerous? The currently deployed code still references the old column name — causing errors.
3. What does CREATE INDEX CONCURRENTLY avoid? Table write locks — it builds the index without blocking INSERTs, UPDATEs, or DELETEs.
4. How do you safely add a NOT NULL column to a large table? Add nullable, set default, backfill in batches, then set NOT NULL.
5. Why shouldn't migrations run on application startup? If the migration fails, the app crashes. Multiple instances would race to migrate simultaneously.
6. What should you do before running a production migration? Review the SQL, test on staging, verify backups are restorable, have a rollback plan, set lock_timeout.
7. What does statement_timeout protect against during migrations? A migration that hangs waiting for a lock — after the timeout, it fails instead of blocking all other queries indefinitely.