Database Migration Manager

Migration Safety Protocol

Before Writing the Migration

1. Classify the change — additive (safe), destructive (dangerous), or transformative (requires expand-contract)
2. Check table size — large tables need batched operations to avoid locks
3. Identify dependencies — foreign keys, views, triggers, stored procedures that reference the target
4. Plan rollback — every migration must have a reverse migration

Migration Classification

Zero-Downtime Pattern: Expand-Contract

For any breaking schema change:

Phase 1 — Expand (backward compatible)

-- Add new column, keep old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

Phase 2 — Migrate (backfill data)

-- Batch update to avoid long locks (Postgres has no UPDATE ... LIMIT; select the batch by key)
UPDATE users SET full_name = first_name || ' ' || last_name
WHERE id IN (
  SELECT id FROM users WHERE full_name IS NULL ORDER BY id LIMIT 1000
);

Phase 3 — Transition (code reads new, writes both)

• Deploy application code that writes to both columns
• Reads from new column with fallback to old

Phase 4 — Contract (remove old)

-- Only after all code uses new column
ALTER TABLE users DROP COLUMN first_name, DROP COLUMN last_name;

Migration File Template

-- Migration: [description]
-- Author: [name]
-- Date: [date]
-- Reversible: Yes/No
-- Estimated duration: [time on production data size]
-- Lock level: [none/row/table]

-- UP
[migration SQL]

-- DOWN (rollback)
[reverse SQL]

-- VERIFY (post-migration check)
[validation query that confirms success]

Pre-Flight Checklist

• Migration runs in a transaction (or is idempotent if it cannot)
• Rollback migration tested
• No full table locks on tables with >100K rows
• Indexes created CONCURRENTLY where supported
• Constant defaults use the engine's metadata-only ADD COLUMN path; volatile defaults (e.g. now(), random()) on large tables are backfilled in batches
• Verified on staging with production-sized dataset
• Backup confirmed before execution

Anti-Patterns

• Adding NOT NULL without a default on a large table (locks entire table)
• Running data migration in the same transaction as schema migration
• Dropping columns before all code paths stop referencing them
• Using ORM auto-migration in production without reviewing generated SQL
• Assuming migration duration from dev dataset size

Data Migration Patterns

• Backfill: Batch updates keyed on the primary key (keyset, not OFFSET), sleep between batches
• Transform: ETL pipeline with progress tracking and resumability
• Seed: Idempotent INSERT ... ON CONFLICT DO NOTHING