database-migrations

When to Use

Trigger this skill when modifying database schemas, executing data migrations, or planning zero-downtime releases.

Core Principles

Migration Order

1. Add column (nullable or with default) → deploy code that reads new column
2. Code writes to both old and new columns → migrate data
3. Code writes only to new column → drop old column

Safe Changes

• Safe: add nullable column, add column with default, add new table, add index (CONCURRENTLY)
• Unsafe: drop column (first make nullable → confirm no references → then drop), change column type (add new → migrate → drop old), rename column (add alias first)

Zero-Downtime Strategy

• Expand-Contract pattern: add first, remove later, intermediate state is compatible
• Use CREATE INDEX CONCURRENTLY for large tables, no table lock
• Batch data migration: 1000-5000 rows per batch, avoid long transactions

Rollback

• Write a down migration for every up migration
• No automatic rollback in production, require human review
• Validate on staging with production data snapshot before applying