Database Migration Patterns Skill

Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Activate

Creating or altering database tables
Adding/removing columns or indexes
Running data migrations (backfill, transform)
Planning zero-downtime schema changes

Core Principles

Every change is a migration — never alter production manually
Migrations are forward-only in production — rollbacks use new forward migrations
Schema and data migrations are separate — never mix DDL and DML
Test against production-sized data — 100 rows != 10M rows
Migrations are immutable once deployed — create new migration instead

Safety Checklist

[ ] Migration has both UP and DOWN (or marked irreversible)
[ ] No full table locks on large tables
[ ] New columns have defaults or are nullable
[ ] Indexes created concurrently
[ ] Data backfill is separate from schema change
[ ] Tested against production-sized data
[ ] Rollback plan documented

PostgreSQL Patterns

Adding a Column Safely

-- GOOD: Nullable, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: With default (Postgres 11+ instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default (full table rewrite + lock)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;

Index Without Downtime

-- BAD: Blocks writes
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Renaming a Column (Expand-Contract)

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill (separate migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Deploy app reading/writing both columns
-- Step 4: Drop old column (separate migration)
ALTER TABLE users DROP COLUMN username;

Large Data Migrations

-- BAD: One transaction, locks table
UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch with progress
DO $$
DECLARE batch_size INT := 10000; rows_updated INT;
BEGIN
  LOOP
    UPDATE users SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users WHERE normalized_email IS NULL
      LIMIT batch_size FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Zero-Downtime Strategy (Expand-Contract)

Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Deploy: app writes to BOTH old and new
  - Backfill existing data

Phase 2: MIGRATE
  - Deploy: app reads from NEW, writes to BOTH
  - Verify data consistency

Phase 3: CONTRACT
  - Deploy: app only uses NEW
  - Drop old column/table in separate migration

Anti-Patterns

| Anti-Pattern | Better Approach | |-------------|-----------------| | Manual SQL in production | Always use migration files | | Editing deployed migrations | Create new migration | | NOT NULL without default | Add nullable, backfill, then constrain | | Inline index on large table | CREATE INDEX CONCURRENTLY | | Schema + data in one migration | Separate migrations | | Drop column before removing code | Remove code first, drop next deploy |

Agent Skills: Database Migration Patterns

Install this agent skill to your local

Skill Files