Database Zero-Downtime Migration & Rollback Strategy
Plan high-volume, live table restructuring and large column migrations without application outages.
Interactive Prompt Playground
{{MIGRATION_GOAL}}{{SCHEMA_AND_SIZE}}{{TRAFFIC_LOAD}}You are a Principal Database Reliability Engineer and Zero-Downtime Migration Architect. Design a multi-phase zero-downtime migration for this high-traffic schema change: Proposed Migration Scope: ``` Migrate user authentication table from single password hash column to multi-factor credential JSONB structure without dropping customer sessions. ``` Current Schema & Table Size: ```sql CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- 18 Million rows, 400 writes/second peak ``` Traffic Load: Continuous 24/7 global traffic, max acceptable lock timeout = 50ms Design a phased Expand/Contract deployment plan: 1. **Phase 1: Expand (Additive Schema Change)**: - DDL statements to add new columns/tables safely without locking tables or breaking old application versions. 2. **Phase 2: Dual-Writing & Backfill**: - Application code changes to write to both old and new schema. - Safe, throttled background batch migration script in SQL/Python to backfill historical rows without causing CPU or replication lag. 3. **Phase 3: Switch Reads**: - Application code update to read exclusively from the new schema. 4. **Phase 4: Contract (Deprecation & Cleanup)**: - Safe removal of the old column/table after full verification. 5. **Rollback Contingency**: - Specific rollback steps for each individual phase if an alert triggers.
How to Use This Prompt
- Describe your schema modification goal and current table size.
- Specify your traffic constraints.
- Get a 4-phase rollout plan with backfill scripts and rollback contingencies.
Engineering Tips & Best Practices
- Always set statement_timeout and lock_timeout before executing any DDL on production PostgreSQL tables.
What This Prompt Inspects
Key failure modes, design principles, and quality standards evaluated during execution.
Expand/Contract Phasing
Separates schema rollout across multiple application deployments to prevent downtime.
Lock Timeout Guard
Sets SET lock_timeout = '50ms' before executing DDL to prevent queue blockage.
Throttled Backfills
Iterates backfills in batches of 1,000 rows with sleep intervals to avoid replication lag.
SprintKit Workflow Integrations
Complementary interactive tools and workflows across SprintKit to accelerate your engineering process.
Related Prompts
Explore related developer prompts in this workflow domain.
Database Migration Rollback & Data Integrity Test Plan
Validate database schema migrations for zero data loss, backward compatibility, and forward/backward rollbacks.
Database Schema & Relational Indexing Strategy
Design production PostgreSQL schemas with composite indexes, foreign key strategies, and partitioning.
Database Deadlock & Concurrency Race Condition Investigator
Diagnose Postgres/MySQL transaction deadlocks, lock contention, and concurrent update race conditions.