Jonas Weber·
Zero-downtime migration on a 40M-row table. No drama.
Generates safe, zero-downtime database migration scripts with rollback strategies and data backfill logic.
Safe Database Migration Generator
You are a senior DevOps engineer writing a safe, production-grade database migration. Zero downtime is required.
## Migration Goal
{{migration_goal}}
## Current Schema
```sql
{{current_schema}}
```
## Target Schema
```sql
{{target_schema}}
```
## Constraints
- Database: {{database_type}}
- Migration tool: {{migration_tool}}
- Downtime allowed: NONE (online migration)
- Table size: {{table_size_estimate}}
- Concurrent writes during migration: YES
## Required Output
1. **Migration Plan** - Step-by-step execution order with estimated duration
2. **Forward Migration** - Complete up-migration with:
- CREATE INDEX CONCURRENTLY where supported
- Column additions with DEFAULT values handled safely
- Backfill strategy for existing data (batched if large)
- Foreign key additions in separate transactions
- Progressive rollout steps (if applicable)
3. **Rollback Migration** - Complete down-migration reversing ALL changes
4. **Verification Queries** - Pre and post-migration checks
5. **Risk Assessment** - Potential issues and mitigation strategies
6. **Runbook** - Exact commands and order to execute, with monitoring checkpoints
Prioritize safety over speed. Include LOCK TIMEOUT settings and statement timeouts.
Ergebnisse
## Zero-Downtime Migration: add `users.email_normalized`
40M rows, concurrent writes, no maintenance window. Three deploys, never a long lock.
**Step 1 — add nullable column (instant, no rewrite):**
```sql
ALTER TABLE users ADD COLUMN email_normalized TEXT; -- NULL default = metadata-only
```
**Step 2 — backfill in batches (run as a job, not a transaction):**
```sql
SET statement_timeout = '30s';
-- repeat until 0 rows affected:
WITH batch AS (
SELECT id FROM users
WHERE email_normalized IS NULL
ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE users u SET email_normalized = lower(btrim(u.email))
FROM batch WHERE u.id = batch.id;
```
**Step 3 — index concurrently, then enforce:**
```sql
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_norm ON users(email_normalized);
ALTER TABLE users ADD CONSTRAINT users_email_norm_nn
CHECK (email_normalized IS NOT NULL) NOT VALID; -- skips full scan
ALTER TABLE users VALIDATE CONSTRAINT users_email_norm_nn; -- light, non-blocking
```
**Rollback:** `DROP INDEX CONCURRENTLY idx_users_email_norm;` then `ALTER TABLE users DROP COLUMN email_normalized;`.
`FOR UPDATE SKIP LOCKED` lets the backfill coexist with live writes — it simply skips rows another transaction holds. Risk assessment and runbook checkpoints attached below in the full version.
Modell: Claude Sonnet 4
6 Likes2 SavesScore: 5