sairam0424 Skill — Migration Strategies
When this skill activates
Any task involving database schema migrations, data migrations, zero-downtime deployments requiring data changes, or expand-contract patterns.
Mandatory actions when this skill is active
Before writing any code
- Assess the migration risk (table size, lock duration, rollback complexity).
- Choose the migration pattern appropriate to the risk level.
- Design the rollback path before implementing the forward migration.
During implementation
- Ensure backward compatibility at every intermediate step.
- Implement batched processing with rate limiting for data backfills.
- Add verification queries to confirm migration correctness.
After implementation
- Run verification (row counts, checksums, sample validation).
- Confirm rollback path works in staging.
- Document the migration in the changelog with rollback instructions.
Expand-Contract Pattern
The safest approach for schema changes in production:
Phase 1: Expand
- Add new column/table (nullable or with default).
- Deploy code that writes to BOTH old and new locations.
- No reads from new location yet.
Phase 2: Migrate
- Backfill existing data from old location to new.
- Batched, rate-limited, resumable, idempotent.
- Verify all data migrated correctly.
Phase 3: Switch Reads
- Deploy code that reads from new location.
- Old location still being written (safety net).
- Monitor for correctness issues.
Phase 4: Contract
- Remove writes to old location.
- Drop old column/table after grace period.
- Final verification and cleanup.
Shadow Tables
- Create new table with desired schema.
- Dual-write: every mutation hits both old and new table.
- Compare outputs periodically to verify consistency.
- Switch reads to new table when confidence is high.
- Drop old table after grace period.
Zero-Downtime Online Schema Changes
For large tables where ALTER TABLE would lock:
Tools
- pt-online-schema-change (Percona): creates shadow table, syncs via triggers.
- gh-ost (GitHub): triggerless, uses binary log replication.
- pg_repack (PostgreSQL): repacks tables without locks.
Principles
- Never run ALTER on tables > 1M rows in production without online tools.
- Test migration duration in staging with production-sized data.
- Schedule during low-traffic windows even with online tools.
- Monitor replication lag during migration.
Backfill Patterns
Batched Processing
loop:
SELECT batch WHERE not_migrated LIMIT 1000
UPDATE batch SET new_column = transform(old_column)
SLEEP(rate_limit_interval)
IF no_more_rows: BREAK
Requirements
- Resumable: track progress (last processed ID), restart from checkpoint.
- Idempotent: running twice produces same result (use upserts).
- Rate-limited: don't overwhelm the database (pause between batches).
- Observable: log progress, ETA, error counts.
Migration Verification
Row Count Comparison
- Count rows in source and target (should match for 1:1 migrations).
- Account for in-flight writes during comparison.
Checksum Validation
- Hash critical columns in both source and target.
- Compare checksums batch by batch.
Sample Validation
- Randomly sample N rows from source.
- Verify each exists in target with correct values.
- Catch edge cases that aggregate checks miss.
Rollback Strategy
- Every migration must have a documented rollback path.
- Intermediate states must be backward-compatible (old code can still run).
- Keep old columns/tables for a grace period (minimum 7 days in production).
- Test rollback in staging before executing forward migration in production.
- Feature flags to switch between old and new code paths instantly.
Self-check before task completion
Before marking a task done when this skill was active:
- Did I read the full SKILL.md before starting? (Not just the triggers)
- Is the migration zero-downtime (no table locks on large tables)?
- Is there a documented rollback path?
- Are backfills batched, resumable, and idempotent?
- Did I verify with row counts, checksums, or sample validation?
- Is every intermediate state backward-compatible?