pg-migration Skill Evaluation Report¶

Evaluation date: 2026-04-18 | Method: A/B blind comparison | Total assertions: 23 | Scenarios: 3

The pg-migration skill reveals an interesting pattern: the baseline Claude already performs well at 87.0% (compared to 52% for mysql-migration), because PostgreSQL migration safety rules like CONCURRENTLY, NOT VALID, and lock_timeout are widely documented and thoroughly trained into the base model. The skill's core value, then, is not knowledge injection — it is structural enforcement: requiring all §9 sections on every review, enforcing the Data basis traceability label, and applying strict original-SQL scoring. It also cuts token consumption by 46.1% by eliminating exploratory reasoning and external tool calls.

1. Skill Overview¶

Core files:

Key safety rules the skill enforces:

• CREATE INDEX must use CONCURRENTLY to avoid ShareLock blocking writes
• Foreign keys must follow the NOT VALID → VALIDATE CONSTRAINT two-step pattern on live tables
• All DDL must be wrapped with SET lock_timeout to prevent indefinite waits
• ALTER COLUMN TYPE on large tables triggers a full table rewrite — must be replaced by pg_repack or shadow-table-swap
• Hard rule: Never classify a migration as SAFE without explicit evidence; conserve assumptions in degraded mode
• Original SQL scoring: the submitted SQL is scored independently from any corrected DDL produced during review

2. Test Design¶

2.1 Evaluation Method¶

Framework: A/B blind test. Each scenario runs two parallel sub-agents:

• Without Skill: receives only the scenario description and SQL — no SKILL.md content
• With Skill: receives the scenario description, SQL, complete SKILL.md, and depth-appropriate reference files

Scoring:

2.2 Scenarios¶

2.3 Assertion Matrix¶

Scenario 0 — Standard DDL Review (8 assertions)

Input SQL:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ NOT NULL DEFAULT now();
CREATE UNIQUE INDEX ON users(email);
ALTER TABLE sessions ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

Scenario 0 result: With Skill 8/8 (100%) — Without Skill 7/8 (87.5%). The only gap is the missing Data basis label.

Scenario 1 — Large-Table High-Risk Migration (9 assertions)

Input SQL:

ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;
CREATE INDEX ON events(user_id, created_at);
ALTER TABLE events DROP COLUMN deprecated_field;

Scenario 1 result: With Skill 9/9 (100%) — Without Skill 8.5/9 (94.4%). The gap is a scoring methodology flaw: Without-Skill counts its own reviewer-added rollback as a pass on the original submitted SQL.

The With-Skill agent correctly applies the "original SQL scored independently" rule:

"The original submitted SQL would score 0/3 Critical: no lock_timeout, no CONCURRENTLY, no rollback plan — overall FAIL."

Scenario 2 — Degraded Mode Boundary (6 assertions)

Input SQL:

ALTER TABLE products ADD COLUMN price_usd NUMERIC(10,2) NOT NULL;
ALTER TABLE products ALTER COLUMN description TYPE TEXT;

Scenario 2 result: With Skill 6/6 (100%) — Without Skill 5.5/6 (91.7%). The gap is the "Never claim SAFE" rule not being explicitly stated.

3. Pass Rate Summary¶

3.1 Overall¶

Delta: +13 percentage points (strict PASS)

3.2 By Scenario¶

3.3 Full Assertion Matrix¶

4. Key Differences¶

4.1 Behaviors unique to the With-Skill group¶

4.2 Technical knowledge comparison¶

The pattern is clear: all three failures are framework compliance gaps, not knowledge gaps. The baseline knows PostgreSQL migration safety — it just doesn't enforce the output contract or apply the stricter scoring rules.

5. Token Cost Analysis¶

5.1 Actual token consumption¶

5.2 Efficiency summary¶

The efficiency paradox: With-Skill agents receive a longer input context (SKILL.md ~4,500 tokens + reference files ~1,800–5,600 tokens depending on depth), yet their total token consumption is 46% lower overall. Three reasons:

1. Focused output: the structured §9 framework directs the model to fill sections rather than engage in exploratory reasoning before organizing a response
2. Eliminating tool calls: Without-Skill agents average 2–3 Web searches per scenario to retrieve PostgreSQL documentation; With-Skill agents embed that knowledge directly, requiring zero external calls
3. Avoiding re-derivation: Without-Skill agents "rediscover" best practices (CONCURRENTLY, NOT VALID, lock_timeout) from scratch each time; With-Skill agents retrieve them directly from the framework

5.3 ROI estimate¶

Based on Sonnet 4 API pricing; token cost only, excludes engineer time.

7. Findings¶

Finding 1: The pg-migration baseline is already strong¶

PostgreSQL migration safety rules — CONCURRENTLY, NOT VALID, lock_timeout — are extensively documented and deeply embedded in the base model. Without-Skill agents even spontaneously produce X/12 Scorecard formatting and §9.9 Uncovered Risks sections that match the skill's output contract. This contrasts sharply with mysql-migration, where the baseline gap is nearly five times larger.

Finding 2: The skill's value is framework enforcement, not knowledge delivery¶

All three points lost by Without-Skill are framework compliance failures, not knowledge failures:

Without the skill, a capable model reaches the right safety conclusions — but doesn't produce an auditable, traceable, consistently-formatted output. When reviews are used for compliance, CI gating, or cross-team comparison, format consistency is not cosmetic: it is the deliverable.

Finding 3: Tool-call dependency is a hidden cost¶

Without-Skill agents averaged 2.3 tool calls per scenario (inferred to be Web searches for PostgreSQL documentation). This introduces three compounding risks:

1. Token cost: search results accumulate in context, amplifying total consumption
2. Latency risk: network dependency adds unpredictable delay
3. Consistency risk: search results change over time; the skill's reference files are fixed and curated

Finding 4: Token efficiency is pg-migration's primary differentiator¶

Unlike mysql-migration — where the skill adds ~51% token overhead because the baseline needs extensive prompting to produce acceptable output — pg-migration achieves a 46% token reduction despite a longer input context. PostgreSQL DDL is inherently more complex (lock matrices, CONCURRENTLY restrictions, pg_repack strategies), which means Without-Skill agents do more exploratory reasoning; With-Skill agents skip that exploration and fill the framework directly.

Cross-skill comparison:

8. Conclusion¶

Rating: Production-ready. Recommended for all PostgreSQL DDL review workflows.

Three things the skill does well:

1. Structural enforcement: §9.1–§9.9 all nine sections are required on every review. Data basis labeling is mandatory. These constraints eliminate the "silent skip" risk — where a capable model produces a correct-looking but incomplete output.

2. Token efficiency: 46% token savings make pg-migration one of the highest-ROI migration skills evaluated. At 100 reviews/month, the savings compound meaningfully at scale.

3. Evaluation framework consistency: Gate analysis, independent original-SQL scoring, and the Scorecard format are enforced on every run — making review results repeatable, comparable, and auditable across teams and time.

Recommended use cases:

• All PostgreSQL production changes involving ALTER TABLE, CREATE INDEX, or CONSTRAINT modifications
• Large-table migration planning (>10M rows) as a decision framework for choosing between pg_repack and shadow-table-swap
• CI/CD pipeline migration file review — the token efficiency makes it practical to run on every PR at low cost

Not recommended for:

• Query optimization and connection pool tuning → use postgresql-best-practise
• Application code security review → use security-review