IntermediateSingle prompt
01
Data Migration Pipeline
Design a safe, reversible data migration pipeline for this schema change or data movement. Migration: {{migration_description}} (e.g. split a table, merge schemas, move to new d...
Prompt text
Design a safe, reversible data migration pipeline for this schema change or data movement.
Migration: {{migration_description}} (e.g. split a table, merge schemas, move to new database)
Data volume: {{volume}}
Max downtime: {{max_downtime}}
Database: {{database}}
1. Migration principles:
- Never modify data in place without a backup
- Test in staging with a production-size data clone first
- Build in a rollback path for every step
- Migrate data in small batches, not one massive transaction
2. Batch migration pattern:
DO $$
DECLARE
batch_size INT := 10000;
last_id BIGINT := 0;
max_id BIGINT;
BEGIN
SELECT MAX(id) INTO max_id FROM source_table;
WHILE last_id < max_id LOOP
INSERT INTO target_table
SELECT ... FROM source_table
WHERE id > last_id AND id <= last_id + batch_size;
last_id := last_id + batch_size;
PERFORM pg_sleep(0.1); -- throttle to avoid I/O saturation
END LOOP;
END $$;
3. Online migration with dual-write:
Phase 1: Add new table/column; application writes to both old and new
Phase 2: Backfill old data from old to new in batches
Phase 3: Verify consistency (compare row counts and key values)
Phase 4: Switch reads to new structure; stop writing to old
Phase 5: Remove old structure after validation period
4. Validation queries:
-- Row count match
SELECT (
(SELECT COUNT(*) FROM source_table) =
(SELECT COUNT(*) FROM target_table)
) AS counts_match;
-- Checksum of key columns
SELECT MD5(STRING_AGG(id::text || amount::text, ',' ORDER BY id))
FROM source_table;
5. Emergency rollback:
- Keep the old table or column intact until migration is fully validated
- Use a feature flag to switch between old and new data paths
- Drop old structures only after: 24h monitoring, zero errors, stakeholder sign-off
Return: batch migration script, dual-write pattern, validation queries, and rollback procedure.