┌─────────────────────────────────────────────────────────────────────────┐
│                     EXPAND-CONTRACT PATTERN                              │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  Phase 1: EXPAND              Phase 2: MIGRATE           Phase 3: CONTRACT│
│  ─────────────────           ──────────────────         ────────────────  │
│  Add new column              Backfill data              Remove old column │
│  (nullable)                  Update app to use new      (after app migrated)│
│                              Both versions work                           │
│                                                                          │
│  ┌─────────┐                 ┌─────────┐                ┌─────────┐      │
│  │old_col  │ ───────────────>│old_col  │ ─────────────> │new_col  │      │
│  │         │                 │new_col  │                │         │      │
│  └─────────┘                 └─────────┘                └─────────┘      │
│                                                                          │
│  Rollback: Drop new          Rollback: Use old          Rollback: N/A    │
│                              (dual-write in app)        (commit)         │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

# Install pgroll (2026 recommended tool)
brew install xataio/pgroll/pgroll
# or
go install github.com/xataio/pgroll@latest

# Initialize pgroll in your database
pgroll init --postgres-url "postgres://user:pass@localhost/db"

# Create a migration file (migrations/001_add_email_verified.json)

{
  "name": "001_add_email_verified",
  "operations": [
    {
      "add_column": {
        "table": "users",
        "column": {
          "name": "email_verified",
          "type": "boolean",
          "default": "false",
          "nullable": false
        },
        "up": "false"
      }
    }
  ]
}

# Start migration (creates versioned schema)
pgroll start migrations/001_add_email_verified.json

# App v1 uses: schema "public_001_add_email_verified"
# App v2 uses: schema "public" (new version)

# After verification, complete migration
pgroll complete

# Rollback if issues
pgroll rollback

-- Step 1: Add new column (nullable, no default constraint yet)
ALTER TABLE users ADD COLUMN display_name VARCHAR(200);

-- Step 2: Create trigger for dual-write (if app can't dual-write)
CREATE OR REPLACE FUNCTION sync_display_name() RETURNS TRIGGER AS $$
BEGIN
  NEW.display_name := CONCAT(NEW.first_name, ' ', NEW.last_name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_display_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_display_name();

-- Step 3: Backfill existing data (in batches)
UPDATE users SET display_name = CONCAT(first_name, ' ', last_name)
WHERE display_name IS NULL
AND id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000);

-- Step 1: Verify no readers of old column (check query logs)
SELECT * FROM pg_stat_statements
WHERE query LIKE '%first_name%' OR query LIKE '%last_name%';

-- Step 2: Drop trigger (if used)
DROP TRIGGER IF EXISTS trg_sync_display_name ON users;
DROP FUNCTION IF EXISTS sync_display_name();

-- Step 3: Drop old columns ONLY after app fully migrated
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- Step 4: Make new column NOT NULL if required
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- Create index without locking table (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_orders_customer_date
ON orders (customer_id, created_at DESC);

-- Drop index without locking (if recreation needed)
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_date;

-- IMPORTANT: CONCURRENTLY cannot run inside transaction block
-- Run outside of Alembic transaction or use raw connection

-- Step 1: Add constraint without validating existing rows (instant)
ALTER TABLE orders ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0) NOT VALID;

-- Step 2: Validate constraint (scans table but allows writes)
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;

-- Step 1: Add FK without validation (instant)
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate FK (scans but allows writes)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

-- Check for locks during migration
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state != 'idle';

-- Check replication lag (if using replicas)
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

-- Monitor backfill progress
SELECT
  COUNT(*) FILTER (WHERE display_name IS NOT NULL) as migrated,
  COUNT(*) FILTER (WHERE display_name IS NULL) as remaining,
  ROUND(100.0 * COUNT(*) FILTER (WHERE display_name IS NOT NULL) / COUNT(*), 2) as pct_complete
FROM users;

-- FORBIDDEN: Single-step ALTER that locks table
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Impact: Blocks ALL queries during metadata lock

-- FORBIDDEN: Add NOT NULL to existing column directly
ALTER TABLE orders ADD COLUMN org_id UUID NOT NULL;
-- Impact: Fails immediately if table has data

-- FORBIDDEN: Regular CREATE INDEX on large table
CREATE INDEX idx_big_table_col ON big_table(col);
-- Impact: Locks table for minutes/hours

-- FORBIDDEN: Drop column without verification period
ALTER TABLE users DROP COLUMN legacy_field;
-- Impact: No rollback if application still references it

-- FORBIDDEN: Constraint validation in same transaction as creation
ALTER TABLE orders ADD CONSTRAINT fk_org
FOREIGN KEY (org_id) REFERENCES orgs(id);
-- Impact: Full table scan with exclusive lock

-- FORBIDDEN: Backfill without batching
UPDATE users SET new_col = old_col;
-- Impact: Locks entire table, fills transaction log