Migration Safety Skill

-- Lock watch (run during staging migration)
SELECT pid, wait_event_type, wait_event, state, left(query, 80)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
 
SELECT l.mode, c.relname, a.query
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted;

## Risk scoring (summary)
| DDL | Default risk | Safer pattern |
|-----|--------------|---------------|
| CREATE INDEX | HIGH on >1M rows | CREATE INDEX CONCURRENTLY |
| ADD COLUMN NOT NULL no default | HIGH | add nullable → backfill → SET NOT NULL |
| ALTER TYPE | HIGH | expand-contract + new column |
| DROP COLUMN | MEDIUM | expand-contract deprecate first |
| ADD FK inline | HIGH | ADD NOT VALID → VALIDATE CONSTRAINT |

ALTER TABLE orders ADD COLUMN priority text NOT NULL DEFAULT 'normal';
CREATE INDEX idx_orders_priority ON orders (priority);

## Lock risk: HIGH
 
### Findings
1. `CREATE INDEX` without CONCURRENTLY takes ShareLock, blocks writes on large orders table
2. `ADD COLUMN ... DEFAULT` is metadata-only in PG 11+ for static default - OK
3. Index build is main blocker (~45M rows)
 
### Safer migration (split files)
-- V20260709_1__orders_priority_column.sql
ALTER TABLE orders ADD COLUMN IF NOT EXISTS priority text NOT NULL DEFAULT 'normal';
 
-- V20260709_2__orders_priority_index.sql (run in change window)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_priority ON orders (priority);

-- Rollback
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_priority;
ALTER TABLE orders DROP COLUMN IF EXISTS priority;

SELECT c.relname, c.reltuples::bigint AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'orders';

-- CONCURRENTLY cannot run inside flyway transaction block
-- Use flyway outOfOrder + executeInTransaction=false pattern per runner docs
CREATE INDEX CONCURRENTLY idx_orders_priority ON orders (priority);