DevOps, CI/CD & Cloud
Target: Claude 3.7, Cursor, Windsurf, ChatGPT

Database Zero-Downtime Migration & Rollback Strategy

Plan high-volume, live table restructuring and large column migrations without application outages.

DatabasePostgreSQLDevOpsMigrationsZero-Downtime

Interactive Prompt Playground

Customize Template Fields
Live replacement
{{MIGRATION_GOAL}}
{{SCHEMA_AND_SIZE}}
{{TRAFFIC_LOAD}}
Rendered Prompt (Ready to paste)1467 characters
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

  1. Describe your schema modification goal and current table size.
  2. Specify your traffic constraints.
  3. 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.

All Prompts

Database Migration Rollback & Data Integrity Test Plan

Validate database schema migrations for zero data loss, backward compatibility, and forward/backward rollbacks.

SQLDatabaseMigrationsLiquibase+2
3 variablesCustomize prompt

Database Schema & Relational Indexing Strategy

Design production PostgreSQL schemas with composite indexes, foreign key strategies, and partitioning.

PostgreSQLSQLDatabaseIndexing+1
4 variablesCustomize prompt

Database Deadlock & Concurrency Race Condition Investigator

Diagnose Postgres/MySQL transaction deadlocks, lock contention, and concurrent update race conditions.

SQLPostgreSQLMySQLConcurrency+2
4 variablesCustomize prompt