pg-migration

name: pg-migration description: > PostgreSQL schema migration safety reviewer and DDL generator. ALWAYS use when writing, reviewing, or planning PostgreSQL schema changes — ALTER TABLE, CREATE/DROP INDEX, column type changes, constraint additions, RLS policy changes, or any DDL touching production tables. Covers lock-level analysis, CREATE INDEX CONCURRENTLY, NOT VALID constraint patterns, transactional DDL rollback, pg_repack for table rewrites, phased rollout design, and backward compatibility. Use even for "simple" ADD COLUMN — PostgreSQL DDL lock behavior varies by operation and version, and AccessExclusiveLock on a hot table causes immediate outage.

PostgreSQL Migration Safety Review

Quick Reference

§1 Scope

In scope — schema migration safety for PostgreSQL 12–17 (primary focus 14+):

• ALTER TABLE (add/drop/modify column, add/drop constraint, rename)
• CREATE / DROP INDEX (including CONCURRENTLY)
• Constraint management (FK, CHECK, UNIQUE, NOT NULL with NOT VALID pattern)
• Data backfill and transformation migrations
• Table restructuring (partitioning, splitting, merging)
• RLS policy additions and modifications
• Extension management (CREATE/ALTER EXTENSION)
• Migration file review (Flyway, golang-migrate, Alembic, raw SQL)
• Rollback planning leveraging PostgreSQL's transactional DDL

Out of scope — delegate to dedicated skills:

• Query optimization, connection pooling, vacuum tuning → postgresql-best-practise
• Application code changes → go-code-reviewer or language-specific reviewer
• Security hardening, privilege management → security-review

§2 Mandatory Gates

Execute gates sequentially. Each gate has a STOP condition.

Gate 1: Context Collection

If database access is available, run:

SELECT version();
SELECT relname, reltuples::bigint, pg_total_relation_size(oid) FROM pg_class WHERE relname = '<table>';
SELECT * FROM pg_extension;

STOP: Cannot determine whether the target is PostgreSQL. Redirect to appropriate skill.

PROCEED: At least PG version and table name known or conservatively assumed. Record all assumptions.

Gate 2: Scope Classification

STOP: Request is not migration-related. Redirect to postgresql-best-practise.

PROCEED: Migration intent confirmed.

Gate 3: Risk Classification

For each DDL statement, classify by lock impact:

STOP: Any UNSAFE item has no mitigation plan.

PROCEED: Every DDL statement has risk level and mitigation.

Gate 4: Output Completeness

Before delivering output, verify all §9 Output Contract sections present. §9.9 Uncovered Risks must never be empty.

§3 Depth Selection

Force Standard or higher when any signal appears: column type change, NOT NULL addition, PK modification, FK/CHECK constraint, RLS policy change, partition restructuring, column removal, extension upgrade.

§4 Degradation Modes

When context is incomplete, degrade gracefully — never fabricate information.

Hard rule: Never claim "SAFE" without evidence. In Degraded/Minimal mode, mark items as "SAFE (assumed — verify against production)" and list all assumptions in §9.9 Uncovered Risks.

§5 DDL Safety Checklist

Execute every item for each DDL statement. Mark SAFE / WARN / UNSAFE with evidence.

5.1 Lock Level Assessment

1. Lock classification — determine lock level for each DDL. PostgreSQL DDL acquires various lock levels:

   • AccessExclusiveLock: blocks ALL operations (most ALTER TABLE variants). When uncertain → load references/pg-ddl-lock-matrix.md.
   • ShareLock: blocks writes but allows reads (e.g., CREATE INDEX non-concurrently).
   • ShareUpdateExclusiveLock: allows concurrent reads AND writes (e.g., CREATE INDEX CONCURRENTLY, VALIDATE CONSTRAINT).
   • Key difference from MySQL: PostgreSQL has no ALGORITHM= hint — the lock level is determined by the operation type.

2. lock_timeout — set before every DDL session:

   SET LOCAL lock_timeout = '3s';
   SET LOCAL statement_timeout = '30s';
   

   Without lock_timeout, DDL waits indefinitely for AccessExclusiveLock, blocking all subsequent queries.

3. CONCURRENTLY for indexes — CREATE INDEX CONCURRENTLY avoids ShareLock, allowing concurrent writes. Plain CREATE INDEX blocks all writes for the duration. Always use CONCURRENTLY on production tables. Caveat: CONCURRENTLY cannot run inside a transaction block.

4. NOT VALID for constraints — adding FK or CHECK constraint with NOT VALID skips row validation, taking only a brief AccessExclusiveLock. Follow up with VALIDATE CONSTRAINT which takes only ShareUpdateExclusiveLock (non-blocking). Two-step pattern:

   ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
   ALTER TABLE orders VALIDATE CONSTRAINT fk_user;  -- non-blocking
   

5.2 Data Integrity

5. NOT NULL + DEFAULT — PostgreSQL 11+ can add NOT NULL column with DEFAULT without rewriting the table (metadata-only). PG <11 requires full table rewrite. Version-gate this recommendation.

6. Column type change — most ALTER COLUMN TYPE operations require full table rewrite with AccessExclusiveLock. Exceptions: varchar(N) → varchar(M) where M > N, and some numeric widenings. For large tables, use pg_repack or create-swap-rename pattern.

7. Constraint idempotency — PostgreSQL lacks ADD CONSTRAINT IF NOT EXISTS. Use DO blocks:

   DO $$ BEGIN
     IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'my_fk') THEN
       ALTER TABLE t ADD CONSTRAINT my_fk FOREIGN KEY (col) REFERENCES other(id) NOT VALID;
     END IF;
   END $$;
   

8. FK cascade risk — ON DELETE CASCADE on large parent → uncontrolled write amplification. Ensure FK target columns are indexed (critical for CASCADE performance).

5.3 Backward Compatibility

9. Deployment ordering — same as MySQL: column add → schema first, then app; column remove → app first, then schema; column rename → create new + dual-write → drop old.

10. Rollback feasibility — PostgreSQL's transactional DDL means most DDL can be rolled back within a transaction. However:

    • CONCURRENTLY operations cannot run in transactions (no rollback)
    • DROP COLUMN data is not immediately recoverable even with ROLLBACK after COMMIT
    • Classify: transactional-rollback / manual-rollback / irreversible

5.4 Operational Safety

11. Session timeouts — every migration must set lock_timeout and statement_timeout. Use SET LOCAL (transaction-scoped) rather than SET SESSION.

12. Disk / WAL space — table rewrite creates new heap + indexes (~2× table size). CONCURRENTLY index build needs temporary disk. Check pg_total_relation_size().

13. Vacuum after migration — large backfills create dead tuples. Run ANALYZE <table> after migration; consider manual VACUUM if autovacuum lag is expected.

14. Statement granularity — wrap related DDL in a single transaction where possible (PostgreSQL advantage). Exception: CONCURRENTLY must be outside transactions.

§6 Execution Plan (Standard + Deep)

Standard phased pattern for zero-downtime migration:

1. Phase 1 — Additive schema: add nullable columns, constraints with NOT VALID, CONCURRENTLY indexes
2. Phase 2 — Backfill: populate new columns using cursor-based batches (see references/large-table-migration.md §3)
3. Phase 3 — App deploy: deploy code writing to both old and new schema
4. Phase 4 — Constraint validation: VALIDATE CONSTRAINT (non-blocking), add NOT NULL
5. Phase 5 — Cleanup (separate release): drop old columns, remove dual-write

Each phase: Pre-condition → SQL (with lock_timeout) → Validation → Rollback → Go/No-go.

For tables >10M rows needing rewrite, use pg_repack or create-swap-rename. Details in references/large-table-migration.md.

§7 Anti-Examples

AE-1: CREATE INDEX without CONCURRENTLY

-- WRONG: blocks all writes for entire index build duration (ShareLock)
CREATE INDEX idx_orders_date ON orders (created_at);
-- RIGHT: non-blocking index build
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (created_at);

AE-2: ADD CONSTRAINT without NOT VALID

-- WRONG: validates every row with AccessExclusiveLock held — locks table for minutes on large tables
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- RIGHT: two-step — brief lock then non-blocking validation
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

AE-3: DDL without lock_timeout

-- WRONG: waits indefinitely for AccessExclusiveLock, blocking all queries behind it
ALTER TABLE users ADD COLUMN bio TEXT;
-- RIGHT:
SET LOCAL lock_timeout = '3s';
ALTER TABLE users ADD COLUMN bio TEXT;

AE-4: ALTER COLUMN TYPE on large table without tool

-- WRONG: full table rewrite with AccessExclusiveLock on 50M-row table
ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;
-- RIGHT: use pg_repack or create-swap-rename (see references/large-table-migration.md)

AE-5: ADD CONSTRAINT IF NOT EXISTS (invalid syntax)

-- WRONG: PostgreSQL does NOT support IF NOT EXISTS for constraints — this is a syntax error
ALTER TABLE orders ADD CONSTRAINT IF NOT EXISTS fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- RIGHT: use DO block with pg_constraint check (see §5.2 item 7)

AE-6: Style nitpick reported as migration risk

-- WRONG: "WARN — table name 'OrderItems' uses CamelCase"
-- RIGHT: only flag naming if it causes functional problems (quoting issues, ORM conflicts)

Extended anti-examples (AE-7 through AE-13) in references/migration-anti-examples.md.

§8 Migration Scorecard

Critical — any FAIL means overall FAIL

• lock_timeout set before every DDL session (SET LOCAL lock_timeout)
• Indexes use CREATE INDEX CONCURRENTLY (not plain CREATE INDEX) on production tables
• Rollback path provided for every phase (transaction rollback, manual rollback, or irreversibility documented)

Standard — 4 of 5 must pass

• FK/CHECK constraints use NOT VALID + VALIDATE CONSTRAINT two-step on tables >100K rows
• Constraint additions use idempotent DO blocks (not bare ADD CONSTRAINT)
• Backward-compatible deployment order (additive before app, removal after app)
• Backfill uses cursor/keyset batching, not LIMIT/OFFSET
• Validation SQL provided for each phase

Hygiene — 3 of 4 must pass

• Disk/WAL impact estimated for rewrite operations
• statement_timeout set alongside lock_timeout
• Post-deploy monitoring specified (replication lag, dead tuple count, error rate)
• ANALYZE scheduled after large backfills

Verdict: X/12; Critical: Y/3; Standard: Z/5; Hygiene: W/4. PASS requires: Critical 3/3 AND Standard ≥4/5 AND Hygiene ≥3/4.

§9 Output Contract

Every migration review MUST produce these sections. Write "N/A — [reason]" if inapplicable.

### 9.1 Context Gate
| Item | Value | Source |

### 9.2 Depth & Mode
[Lite/Standard/Deep] × [review/generate/plan] — [rationale]

### 9.3 Risk Assessment Table
| # | DDL Statement | Lock Level | Risk | Notes |

### 9.4 Execution Plan (Standard/Deep; "N/A — Lite" for Lite)

### 9.5 Migration SQL (with lock_timeout, CONCURRENTLY, NOT VALID as applicable)

### 9.6 Validation SQL

### 9.7 Rollback Plan (per-phase; note transactional vs manual rollback)

### 9.8 Post-Deploy Checks

### 9.9 Uncovered Risks (MANDATORY — never empty)
| Area | Reason | Impact | Follow-up |

Volume rules:

• UNSAFE: always fully detailed with mitigation
• WARN: up to 10; overflow to §9.9
• SAFE: summary row only
• §9.9 minimum: document all conservative assumptions made

Scorecard summary (append after §9.9):

Scorecard: X/12 — Critical Y/3, Standard Z/5, Hygiene W/4 — PASS/FAIL
Data basis: [full context | degraded | minimal | planning]

§10 Reference Loading Guide