Backend5 min read

Database Migrations

Manage database schema changes safely - zero-downtime migrations, expand-contract pattern, rollback strategies, and migration tooling.

DatabaseMigrationsSchema ChangesFlywayAlembic

skill: Database Migrations category: Backend tags: [Database, Migrations, Schema Changes, Flyway, Alembic] readingTime: 5

Database migrations are the most dangerous operation in production - a bad migration can lock your table for hours or permanently delete data. Zero-downtime migrations require thinking in phases: expand (add new thing while keeping old), migrate (move data), contract (remove old thing). Never combine all three in one deployment.

Workflow

Use migration tools, never manual SQLMigration tools track which migrations ran, provide rollback, and make the current schema reproducible from any machine.
Apply expand-contract pattern for zero downtimeAdding a NOT NULL column or dropping a column takes a full table lock in most databases. Expand-contract splits the migration into safe phases.
Add indexes concurrentlyAdding an index on a large table locks it for writes during build - often minutes. CONCURRENTLY builds without locking.
Write reversible migrationsMigrations need downgrade functions - if a deployment fails, you roll back the code and must roll back the schema too.
Validate and test migrationsA migration that passes staging may fail in production due to data edge cases, row count, or locks. Test with production-like data size.

Output Format

Migrations: versioned SQL files with tools, expand-contract for zero-downtime, CONCURRENTLY for indexes, reversible with downgrade, and CI validation against real data.

Expand: ADD COLUMN nullable (no lock)
Backfill: UPDATE in batches
Contract: SET NOT NULL + DROP old column
Index: CREATE INDEX CONCURRENTLY
Test: pg_restore snapshot + migrate + smoke test

Examples

Input

Output

Edge Cases & Gotchas

Long-running transactions block migrations - ALTER TABLE waits for all open transactions to finish before acquiring a lock. Find and kill long-running transactions before applying schema changes in production.
PostgreSQL pg_locks deadlock - Multiple simultaneous migration runners create deadlocks. Use an advisory lock or distributed mutex to ensure only one migration process runs at a time.
Migration checksum mismatch - Never edit an applied migration file - Flyway/Alembic check checksums and will fail. Create a new migration to fix the previous one.
Seed data in migrations - Don't put environment-specific seed data in migrations (it runs in every environment). Keep seed scripts separate from migrations.

When to Break These Rules

Skip expand-contract for low-traffic applications where a brief maintenance window (5 minutes) is acceptable - simplicity wins over complexity when downtime cost is low.
Use logical replication (pglogical, DMS) for major schema rewrites - replicate data to a new schema in parallel, cut over with milliseconds of downtime.

Reference Files

https://alembic.sqlalchemy.org/
https://www.prisma.io/docs/orm/prisma-migrate