Database Migration Management

Database migrations are the riskiest part of deployment. They touch data, can't always be undone, and affect running applications. Good migration management keeps data safe while enabling evolution. Poor management leads to outages and data loss.

Migration Safety

Migration Basics

Version Control

MIGRATION MANAGEMENT
════════════════════

VERSION CONTROLLED:
─────────────────────────────────────
migrations/
├── 001_create_users.sql
├── 002_add_email_to_users.sql
├── 003_create_orders.sql
├── 004_add_index_on_orders.sql
└── Sequential, tracked

MIGRATION FILE:
─────────────────────────────────────
-- Migration: 002_add_email_to_users
-- Created: 2024-01-15
-- Author: developer@example.com

-- Up
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Down
ALTER TABLE users DROP COLUMN email;

TOOLS:
─────────────────────────────────────
├── Flyway (SQL migrations)
├── Liquibase (XML/SQL)
├── Laravel Migrations (PHP)
├── Alembic (Python)
├── Knex (Node.js)
├── Rails Migrations (Ruby)
└── Framework-specific tools

EXECUTION:
─────────────────────────────────────
├── Run pending migrations
├── Track executed migrations
├── Never modify executed migrations
├── New changes = new migration
└── Immutable history

Zero-Downtime Patterns

Safe Changes

ZERO-DOWNTIME MIGRATIONS
════════════════════════

SAFE OPERATIONS:
─────────────────────────────────────
Always safe:
├── Add new table
├── Add nullable column
├── Add index (concurrent)
├── Add column with default
└── Additive changes

DANGEROUS OPERATIONS:
─────────────────────────────────────
Need careful handling:
├── Remove column
├── Rename column
├── Change column type
├── Add NOT NULL constraint
├── Require multi-step approach
└── Never in single migration

COLUMN RENAME PATTERN:
─────────────────────────────────────
Goal: Rename 'name' to 'full_name'

Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

Step 2: Deploy code that writes to both
old_column and new_column

Step 3: Backfill data
UPDATE users SET full_name = name WHERE full_name IS NULL;

Step 4: Deploy code that reads from new_column

Step 5: Stop writing to old column

Step 6: Remove old column (later)
ALTER TABLE users DROP COLUMN name;

Timeline:
├── Migration 1 (add column)
├── Deploy 1 (write both)
├── Migration 2 (backfill)
├── Deploy 2 (read new)
├── Deploy 3 (write new only)
├── Migration 3 (drop old)
└── Multiple deploys, safe change

ADDING NOT NULL:
─────────────────────────────────────
Goal: Make email required

Step 1: Add column nullable
ALTER TABLE users ADD COLUMN email VARCHAR(255);

Step 2: Deploy code requiring email

Step 3: Backfill existing rows
UPDATE users SET email = 'unknown@example.com' 
WHERE email IS NULL;

Step 4: Add constraint
ALTER TABLE users 
ALTER COLUMN email SET NOT NULL;

Testing Migrations

Before Production

MIGRATION TESTING
═════════════════

STAGING TESTING:
─────────────────────────────────────
Requirements:
├── Production-like data volume
├── Realistic data patterns
├── Same database version
├── Test migration time
├── Test rollback
└── Production simulation

CHECKLIST:
─────────────────────────────────────
Before production:
├── ☐ Tested in staging
├── ☐ Migration time acceptable
├── ☐ Rollback tested
├── ☐ Backup taken
├── ☐ Maintenance window if needed
├── ☐ Team on standby
└── Ready to execute

PERFORMANCE TESTING:
─────────────────────────────────────
For large tables:
├── Estimate migration time
├── Test on production-size data
├── Check lock duration
├── Plan for table size
├── Consider batching
└── No surprises

EXAMPLE:
─────────────────────────────────────
Table: orders (50M rows)

Test results:
├── Add column: 45 seconds
├── Backfill: 15 minutes (batched)
├── Add index: 3 minutes (concurrent)
├── Total: 20 minutes
├── Plan: Low-traffic window
└── Prepared

Rollback Strategy

Recovery Plan

ROLLBACK STRATEGY
═════════════════

REVERSIBLE MIGRATIONS:
─────────────────────────────────────
Write down migration:
-- Up
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Down
ALTER TABLE users DROP COLUMN email;

├── Every migration has reverse
├── Tested in staging
├── One command to undo
└── Easy rollback

NON-REVERSIBLE:
─────────────────────────────────────
Some migrations can't reverse:
├── DROP COLUMN (data gone)
├── Truncate data
├── Type changes with data loss
├── Need backup restore
└── Special handling

FOR NON-REVERSIBLE:
─────────────────────────────────────
├── Document explicitly
├── Backup before running
├── Test restore procedure
├── Manual rollback script
├── Extra caution
└── Plan B ready

BACKUP STRATEGY:
─────────────────────────────────────
Before migration:
├── Full backup or snapshot
├── Verify backup works
├── Know restore time
├── Restore procedure documented
├── Point-in-time recovery enabled
└── Safety net

Large Table Migrations

Handling Scale

LARGE TABLE MIGRATIONS
══════════════════════

PROBLEMS:
─────────────────────────────────────
Large tables:
├── Long lock times
├── Slow operations
├── Replication lag
├── Connection timeouts
├── Need special handling
└── Can't do simple ALTER

BATCHING:
─────────────────────────────────────
Backfill in batches:

DO $$
DECLARE
  batch_size INT := 10000;
  last_id INT := 0;
BEGIN
  LOOP
    UPDATE users
    SET full_name = name
    WHERE id > last_id
      AND id <= last_id + batch_size
      AND full_name IS NULL;
    
    IF NOT FOUND THEN EXIT; END IF;
    
    last_id := last_id + batch_size;
    COMMIT;
    PERFORM pg_sleep(0.1); -- Throttle
  END LOOP;
END $$;

├── Process in chunks
├── Commit between batches
├── Allow replication to catch up
├── Reduce lock duration
└── Controlled execution

ONLINE SCHEMA CHANGES:
─────────────────────────────────────
Tools:
├── pt-online-schema-change (MySQL)
├── gh-ost (MySQL)
├── pg_repack (PostgreSQL)
├── No locking changes
└── Production-safe

CONCURRENT INDEX:
─────────────────────────────────────
PostgreSQL:
CREATE INDEX CONCURRENTLY idx_users_email 
ON users(email);

├── Doesn't lock table
├── Takes longer
├── Can fail (retry)
├── Production-safe
└── Use for large tables

GitScrum Integration

Tracking Migrations

GITSCRUM FOR MIGRATIONS
═══════════════════════

MIGRATION TASKS:
─────────────────────────────────────
├── Label: database, migration
├── Linked to feature
├── Checklist for steps
├── Deployment coordination
└── Visible tracking

TASK EXAMPLE:
─────────────────────────────────────
Task: "Add email column to users"
Checklist:
├── ☐ Write migration
├── ☐ Write rollback
├── ☐ Test in staging
├── ☐ Time migration on prod-size data
├── ☐ Backup production
├── ☐ Execute migration
├── ☐ Verify success
└── Complete process

RUNBOOK:
─────────────────────────────────────
NoteVault:
├── Migration runbook
├── Common procedures
├── Rollback procedures
├── Emergency contacts
└── Documented process

Best Practices

For Migrations

1. Version control — All migrations tracked
2. Zero-downtime patterns — Multi-step changes
3. Test thoroughly — Staging with real data
4. Always have rollback — Down migrations
5. Backup first — Safety net

Anti-Patterns

MIGRATION MISTAKES:
✗ Untested migrations
✗ No rollback plan
✗ Rename in single step
✗ Remove column with DROP
✗ Long locks on production
✗ No backups
✗ Editing executed migrations
✗ Assuming it will work

Related Solutions

• Deployment automation strategies
• Continuous deployment strategies
• How to manage database migration projects