Database Migration | Planning & Rollback Guide
Execute database changes safely with proper planning, testing, and rollback strategies. GitScrum tracks migration tasks and coordinates deployment safely.
9 min read
Database migrations require careful planning and execution. GitScrum helps track migration tasks and coordinate deployments safely.
Migration Planning
Migration Task Structure
DATABASE MIGRATION STORY:
┌─────────────────────────────────────────────────────────────┐
│ DB-123: Add user_preferences Table │
├─────────────────────────────────────────────────────────────┤
│ │
│ PURPOSE: │
│ Store user notification and display preferences │
│ Required for: USER-456 (Notification settings feature) │
│ │
│ ═══════════════════════════════════════════════════════════ │
│ │
│ MIGRATION SCRIPT: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ CREATE TABLE user_preferences ( ││
│ │ id BIGSERIAL PRIMARY KEY, ││
│ │ user_id BIGINT NOT NULL REFERENCES users(id), ││
│ │ preference_key VARCHAR(100) NOT NULL, ││
│ │ preference_value JSONB NOT NULL DEFAULT '{}', ││
│ │ created_at TIMESTAMP DEFAULT NOW(), ││
│ │ updated_at TIMESTAMP DEFAULT NOW(), ││
│ │ UNIQUE(user_id, preference_key) ││
│ │ ); ││
│ │ ││
│ │ CREATE INDEX idx_user_prefs_user ON ││
│ │ user_preferences(user_id); ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ ROLLBACK SCRIPT: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ DROP TABLE IF EXISTS user_preferences; ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ ═══════════════════════════════════════════════════════════ │
│ │
│ CHECKLIST: │
│ ☐ Migration script reviewed │
│ ☐ Rollback script tested │
│ ☐ Run on staging successfully │
│ ☐ Performance impact assessed │
│ ☐ No table locks on large tables │
│ ☐ Deployment window scheduled │
│ ☐ Monitoring in place │
└─────────────────────────────────────────────────────────────┘
Risk Assessment
MIGRATION RISK LEVELS:
┌─────────────────────────────────────────────────────────────┐
│ │
│ LOW RISK (Green light): │
│ • Adding new table │
│ • Adding nullable column │
│ • Adding index (if table small) │
│ → Can run anytime │
│ │
│ MEDIUM RISK (Careful): │
│ • Adding index to large table │
│ • Modifying column type (compatible) │
│ • Adding NOT NULL with default │
│ → Run during low traffic │
│ → Monitor performance │
│ │
│ HIGH RISK (Extra caution): │
│ • Modifying column type (incompatible) │
│ • Removing column │
│ • Removing table │
│ • Large data migrations │
│ → Maintenance window required │
│ → Team standby for issues │
│ → Detailed rollback plan │
│ │
│ CRITICAL RISK (Full preparation): │
│ • Schema changes on huge tables (100M+ rows) │
│ • Foreign key changes │
│ • Primary key modifications │
│ → Extended maintenance window │
│ → Full backup before │
│ → Executive approval │
│ → War room during execution │
└─────────────────────────────────────────────────────────────┘
Safe Migration Patterns
Backward-Compatible Changes
SAFE MIGRATION PATTERNS:
┌─────────────────────────────────────────────────────────────┐
│ │
│ PATTERN 1: EXPAND-CONTRACT │
│ │
│ GOAL: Rename column "name" to "full_name" │
│ │
│ ❌ RISKY (one step): │
│ ALTER TABLE users RENAME name TO full_name; │
│ → Breaks old code immediately │
│ │
│ ✅ SAFE (expand-contract): │
│ │
│ Step 1 (Expand): Add new column │
│ ALTER TABLE users ADD COLUMN full_name VARCHAR(255); │
│ │
│ Step 2: Dual-write in code │
│ Write to both name and full_name │
│ │
│ Step 3: Backfill data │
│ UPDATE users SET full_name = name WHERE full_name IS NULL;│
│ │
│ Step 4: Switch reads │
│ Read from full_name │
│ │
│ Step 5 (Contract): Remove old column (later release) │
│ ALTER TABLE users DROP COLUMN name; │
│ │
│ Each step is reversible and non-breaking │
│ │
└─────────────────────────────────────────────────────────────┘
Adding NOT NULL Columns
SAFE NOT NULL COLUMN ADDITION:
┌─────────────────────────────────────────────────────────────┐
│ │
│ GOAL: Add required "status" column to users │
│ │
│ ❌ RISKY: │
│ ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL; │
│ → Fails if table has existing rows │
│ │
│ ✅ SAFE APPROACH: │
│ │
│ Step 1: Add nullable column with default │
│ ALTER TABLE users ADD COLUMN status VARCHAR(20) │
│ DEFAULT 'active'; │
│ │
│ Step 2: Backfill existing rows (in batches) │
│ UPDATE users SET status = 'active' │
│ WHERE status IS NULL │
│ AND id BETWEEN 1 AND 10000; │
│ │
│ Step 3: Add NOT NULL constraint │
│ ALTER TABLE users ALTER COLUMN status SET NOT NULL; │
│ │
│ Step 4: Optional - remove default │
│ ALTER TABLE users ALTER COLUMN status DROP DEFAULT; │
│ │
│ WHY BATCHES: │
│ Large UPDATE locks table │
│ Batches of 10k-50k rows minimize lock time │
│ Run during low-traffic periods │
└─────────────────────────────────────────────────────────────┘
Large Table Migrations
Batched Operations
HANDLING LARGE TABLES:
┌─────────────────────────────────────────────────────────────┐
│ │
│ PROBLEM: │
│ Table: events (500M rows) │
│ Need to: Update format of event_data column │
│ │
│ ❌ BAD: One giant UPDATE │
│ UPDATE events SET event_data = transform(event_data); │
│ → Locks table for hours │
│ → Transaction log explodes │
│ → Application times out │
│ │
│ ✅ GOOD: Batched updates │
│ │
│ APPROACH: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ -- Run in loop, 10k rows at a time ││
│ │ UPDATE events ││
│ │ SET event_data = transform(event_data) ││
│ │ WHERE id IN ( ││
│ │ SELECT id FROM events ││
│ │ WHERE needs_migration = true ││
│ │ LIMIT 10000 ││
│ │ ); ││
│ │ ││
│ │ -- Sleep between batches ││
│ │ SELECT pg_sleep(0.5); ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ MONITORING: │
│ • Rows processed: 150,000 / 500,000,000 │
│ • Estimated time: 8 hours │
│ • Database load: Normal │
│ • Errors: 0 │
│ │
│ RUN AS: Background job, not deployment │
└─────────────────────────────────────────────────────────────┘
Zero-Downtime Index Creation
CREATING INDEXES SAFELY:
┌─────────────────────────────────────────────────────────────┐
│ │
│ PROBLEM: │
│ Need index on large table (100M rows) │
│ CREATE INDEX locks table during creation │
│ │
│ ❌ BAD: │
│ CREATE INDEX idx_users_email ON users(email); │
│ → Locks table for minutes to hours │
│ │
│ ✅ GOOD (PostgreSQL): │
│ CREATE INDEX CONCURRENTLY idx_users_email ON users(email);│
│ → Builds in background │
│ → No lock on table │
│ → Slightly slower, but safe │
│ │
│ ✅ GOOD (MySQL 5.6+): │
│ ALTER TABLE users ADD INDEX idx_email(email), │
│ ALGORITHM=INPLACE, LOCK=NONE; │
│ │
│ ─────────────────────────────────────────────────────────── │
│ │
│ CAUTIONS: │
│ │
│ • CONCURRENTLY doesn't work in transactions │
│ • If it fails, index is invalid - must be dropped │
│ • Monitor disk space (builds index alongside) │
│ • May take longer than regular CREATE INDEX │
│ │
│ GITSCRUM TASK: │
│ Labels: database, low-risk │
│ Note: Using CONCURRENTLY, no downtime expected │
└─────────────────────────────────────────────────────────────┘
Rollback Planning
Every Migration Needs Rollback
ROLLBACK REQUIREMENTS:
┌─────────────────────────────────────────────────────────────┐
│ │
│ RULE: NO MIGRATION WITHOUT ROLLBACK │
│ │
│ MIGRATION FILE STRUCTURE: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ migrations/ ││
│ │ └── 2024_01_15_120000_add_preferences_table.sql ││
│ │ ├── up.sql (forward migration) ││
│ │ └── down.sql (rollback) ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ EXAMPLE: │
│ │
│ up.sql: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ CREATE TABLE user_preferences (...); ││
│ │ CREATE INDEX idx_prefs_user ON user_preferences(...); ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ down.sql: │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ DROP INDEX IF EXISTS idx_prefs_user; ││
│ │ DROP TABLE IF EXISTS user_preferences; ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ TESTING ROLLBACK: │
│ 1. Run migration │
│ 2. Verify success │
│ 3. Run rollback │
│ 4. Verify clean state │
│ 5. Run migration again │
│ 6. Must work cleanly │
│ │
│ If rollback is impossible (data loss), document it │
└─────────────────────────────────────────────────────────────┘
Deployment Coordination
Migration Deployment Flow
MIGRATION DEPLOYMENT PROCESS:
┌─────────────────────────────────────────────────────────────┐
│ │
│ PRE-DEPLOYMENT: │
│ │
│ ☐ Migration tested on staging │
│ ☐ Rollback tested on staging │
│ ☐ Performance impact measured │
│ ☐ Deployment window scheduled │
│ ☐ Team notified │
│ ☐ Backup confirmed │
│ │
│ ─────────────────────────────────────────────────────────── │
│ │
│ DEPLOYMENT ORDER: │
│ │
│ 1. BACKUP │
│ Snapshot database before migration │
│ │
│ 2. MIGRATE │
│ Run migration on production │
│ │
│ 3. VERIFY │
│ Confirm migration successful │
│ Check table structure │
│ Verify data if applicable │
│ │
│ 4. DEPLOY CODE │
│ Deploy application that uses new schema │
│ │
│ 5. MONITOR │
│ Watch for errors │
│ Check query performance │
│ Verify application behavior │
│ │
│ 6. MARK COMPLETE │
│ Update GitScrum task │
│ Document any issues │
│ │
│ IF PROBLEMS: │
│ → Roll back application │
│ → Roll back migration │
│ → Investigate in staging │
└─────────────────────────────────────────────────────────────┘