Pg Migration

PostgreSQL Migration Safety Review

Quick Reference

| If you need to… | Go to | |----------------------------------------|-----------------------------------------| | Understand what this skill covers | §1 Scope | | Check mandatory prerequisites | §2 Mandatory Gates | | Choose review depth | §3 Depth Selection | | Handle incomplete context | §4 Degradation Modes | | Analyze DDL safety item by item | §5 DDL Safety Checklist | | Design a phased execution plan | §6 Execution Plan | | Avoid common migration mistakes | §7 Anti-Examples | | Score the review result | §8 Scorecard | | Format review output | §9 Output Contract | | Look up DDL lock levels by operation | references/pg-ddl-lock-matrix.md | | Plan a large-table (>10M rows) change | references/large-table-migration.md |

---

§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

| Item | Why it matters | If unknown | |------|----------------|------------| | PG version (12 / 13 / 14 / 15 / 16 / 17) | DDL behavior differs by version (e.g., PG 11+ non-rewriting NOT NULL DEFAULT) | Assume PG 12 (conservative) | | Table row count | Determines lock tolerance and tool choice | Ask, or estimate via pg_class.reltuples | | Table size (data + indexes) | Large tables need CONCURRENTLY / pgrepack | Estimate via pg_total_relation_size() | | Active QPS on table | High-traffic amplifies lock contention | Assume high-traffic | | Replication type | Streaming vs logical; DDL handling differs | Assume streaming replica | | Maintenance window | Some DDL needs low-traffic period | Assume none (zero-downtime required) | | Migration framework | Flyway/Alembic/golang-migrate affect transaction handling | Detect from project files | | Extensions in use | Some DDL depends on extensions (pgrepack, pgcrypto) | Check \dx |

If database access is available, run:

SELECT version();
SELECT relname, reltuples::bigint, pg_total_relation_size(oid) FROM pg_class WHERE relname = '';
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

| Mode | Trigger | Output | |------|---------|--------| | review | User provides existing migration SQL/file | Safety analysis of provided DDL | | generate | User describes desired schema change | Migration SQL + safety analysis | | plan | User describes goal without specifics | Phased migration plan + rationale |

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:

| Risk | Lock level | Required action | |------|-----------|-----------------| | SAFE | No lock or ShareUpdateExclusiveLock (e.g., CONCURRENTLY, NOT VALID) | Standard session guards | | WARN | ShareLock or ShareRowExclusiveLock | Off-peak window + monitoring | | UNSAFE | AccessExclusiveLock on table >1M rows, or full table rewrite | pg_repack + staged rollout |

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

| Depth | When to use | Gates | References to load | |-------|-------------|-------|-------------------| | Lite | ≤3 DDL statements, all non-blocking (ADD nullable column, CONCURRENTLY index) | 1–4 | None | | Standard | 4–15 statements, or any AccessExclusiveLock operation | 1–4 | pg-ddl-lock-matrix.md | | Deep | >15 statements, table >10M rows, or multi-step data migration | 1–4 | Both reference files |

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.

| Available context | Mode | What you can do | What you cannot do | |-------------------|------|-----------------|-------------------| | Full (version, size, QPS, replicas) | Full | All checklist items, precise lock-time estimates | — | | Version + size known, others unknown | Degraded | Full checklist with conservative assumptions | Precise lock-time estimates | | Only migration SQL, no context | Minimal | Static DDL analysis, flag all unknowns | Version-specific advice, replication assessment | | No SQL (planning request) | Planning | Generate migration plan from requirements | Review existing SQL |

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.

1. lock_timeout — set before every DDL session:

``sql SET LOCAL lock_timeout = '3s'; SET LOCAL statement_timeout = '30s'; `` Without lock_timeout, DDL waits indefinitely for AccessExclusiveLock, blocking all subsequent queries.

1. 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.

1. 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:

``sql 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

1. NOT NULL + DEFAULT — PostgreSQL 11+ can add NOT NULL column with DEFAULT without rewriting the table (metadata-only). PG N, and some numeric widenings. For large tables, use pg_repack or create-swap-rename pattern.

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

``sql 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 $$; ``

1. 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

1. 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.

1. 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

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

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

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

1. 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

| Condition | Load | |-----------|------| | Standard or Deep depth | references/pg-ddl-lock-matrix.md | | Deep depth, or table >10M rows | references/large-table-migration.md | | Extended anti-example matching | references/migration-anti-examples.md |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: johnqtcg
• Source: johnqtcg/awesome-skills
• License: MIT
• Homepage: https://johnqtcg.github.io/awesome-skills/

Install and usage instructions live in the source repository linked above.