Zero-Downtime Schema Migration AI Prompt

Design a zero-downtime schema migration strategy for this production database.

Change type: {{change}} (add column, rename column, change type, add index, split table)
Table size: {{table_size}} (rows, approximate GB)
Database: {{database}}
Max acceptable downtime: {{max_downtime}}

1. Safe operations (instant, no lock):
   - Adding a column with a default value (PostgreSQL 11+)
   - Adding a NOT NULL column with a default (PostgreSQL 11+)
   - Adding a foreign key with NOT VALID (deferred validation)
   - Creating an index CONCURRENTLY
   - Dropping an index CONCURRENTLY

2. Dangerous operations (requires full table lock):
   - Changing a column type (ALTER COLUMN ... TYPE)
   - Adding a NOT NULL constraint to an existing column
   - Setting a default that requires table rewrite
   - Adding a UNIQUE constraint (without using CONCURRENTLY)

3. Add column with default (zero-downtime, PostgreSQL 11+):
   ALTER TABLE orders ADD COLUMN is_flagged BOOLEAN DEFAULT FALSE;
   - In PostgreSQL 11+: this is instant (the default is stored in the catalog, not written to each row)
   - In PostgreSQL < 11: causes a full table rewrite — use a nullable column first, then backfill

4. Add index concurrently:
   CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
   - Does not hold a full table lock; runs in the background
   - Takes longer than a regular CREATE INDEX (2-3x)
   - May fail if there are duplicate violations; check pg_index.indisvalid after completion

5. Expand-contract pattern for column renames:
   Phase 1 (expand): Add new column, populate via trigger and backfill
   Phase 2 (contract): Update app to write to new column, stop writing to old
   Phase 3 (cleanup): Drop old column after verifying no reads remain

6. pg_repack for table rewrites online:
   - Rebuilds bloated or modified tables without a full lock
   - Useful for: changing column types, removing table bloat
   - Requires: pg_repack extension installed

Return: step-by-step migration plan for the specific change, DDL statements, rollback procedure, and validation steps.