Chapter 32: Schema Migrations Without Downtime
Production schemas evolve. Adding columns, changing types, creating indexes — all must happen without breaking running applications.
Safe vs Dangerous Operations
| Operation | Safe? | Why |
|---|---|---|
| ADD COLUMN (nullable, no default) | ✓ Safe | Instant metadata change |
| ADD COLUMN with DEFAULT | ✓ Safe (PG 11+) | Default stored in catalog, not rewritten |
| DROP COLUMN | ⚠️ Careful | Safe in PG (marks invisible), but app must not reference it |
| ALTER COLUMN TYPE | ✗ Dangerous | Rewrites entire table, locks it |
| ADD NOT NULL constraint | ⚠️ Careful | Scans entire table to validate |
| CREATE INDEX | ✗ Blocks writes | Use CONCURRENTLY instead |
| CREATE INDEX CONCURRENTLY | ✓ Safe | Doesn't lock table (slower to build) |
Safe Migration Pattern
-- Step 1: Add nullable column (instant, no lock)
ALTER TABLE users ADD COLUMN phone TEXT;
-- Step 2: Backfill data (in batches to avoid long transactions)
UPDATE users SET phone = 'unknown' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
UPDATE users SET phone = 'unknown' WHERE phone IS NULL AND id BETWEEN 10001 AND 20000;
-- Step 3: Add constraint (after all rows have values)
ALTER TABLE users ADD CONSTRAINT phone_not_null
CHECK (phone IS NOT NULL) NOT VALID; -- doesn't scan existing rows
ALTER TABLE users VALIDATE CONSTRAINT phone_not_null; -- validates without exclusive lockMigration Tools
- Flyway: versioned SQL files (V1__create_users.sql)
- Liquibase: XML/YAML/SQL changesets
- golang-migrate: simple up/down SQL files
- Alembic (Python/SQLAlchemy): Python migration scripts
Golden Rules
⚠️ Migration Rules
- Never run ALTER TABLE that rewrites data on large production tables
- Always use CREATE INDEX CONCURRENTLY
- Deploy app changes BEFORE removing columns (app must handle missing column)
- Backfill in batches, not one giant UPDATE
- Test migrations on a copy of production data first
Key Takeaways
- ADD COLUMN (nullable) is instant and safe
- CREATE INDEX CONCURRENTLY avoids write locks
- Type changes and NOT NULL on existing columns require careful multi-step approach
- Use migration tools for version control of schema changes
- Always test on production-size data before applying