9 min read • Guide 753 of 877
Database Migration Best Practices
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 │
└─────────────────────────────────────────────────────────────┘