Data Migration - Safe Schema Changes

When to use this skill

• Migrating database schemas and structures

• Transforming data between formats

• Moving data between database systems

• Implementing versioned database migrations

• Handling data transformations during migrations

• Ensuring data integrity and validation

• Planning zero-downtime migrations

• Rolling back failed migrations safely

• Migrating from legacy systems

• Implementing data backfill strategies

• Testing migrations in staging environments

• Creating migration rollback procedures

When to use this skill

• Migrating data between schemas, zero-downtime deployments.

• When working on related tasks or features

• During development that requires this expertise

Use when: Migrating data between schemas, zero-downtime deployments.

Process

• Add new column

• Dual-write to old & new

• Backfill historical data

• Switch reads to new column

• Remove old column

Example

-- Step 1: Add column
ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- Step 2: Backfill
UPDATE users SET email_new = email WHERE email_new IS NULL;

-- Step 3: Swap
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_new TO email;

Resources

• Database Migrations