postgres-agents-query-reviewer

Quick Reference :

This skill turns Claude into a SQL query reviewer. It is an orchestration skill, not a syntax reference: it defines a fixed 13-point review checklist, a severity grading scheme, and which sibling skill to cite for each finding. Run every check against every query. Never approve a query by inspection alone.

Review workflow :
  1. PARSE      -- identify statement type, target tables, WHERE/JOIN/ORDER, subqueries
  2. CHECK      -- run all 13 checks (C-1..C-13), record each hit
  3. EXPLAIN    -- for any index-related hit, recommend EXPLAIN (ANALYZE, BUFFERS)
  4. REPORT     -- emit structured findings + overall verdict

Severity grades :

CRITICAL : a correctness bug or data-loss risk. Verdict cannot be APPROVE.
WARNING : a performance defect that bites at scale (seqscan-forcing, N+1, OFFSET).
INFO : style or context-dependent; the author decides.

Verdict : APPROVE (no CRITICAL, no WARNING) / APPROVE WITH WARNINGS (warnings only) / REQUEST CHANGES (any CRITICAL).

ALWAYS run all 13 checks. NEVER call a Seq Scan a defect without knowing the table size. NEVER review syntax only: an index-related finding is unconfirmed until an EXPLAIN plan is seen.

When To Use This Skill :

ALWAYS use this skill when :

A user asks "review this query", "is this SQL good", "why is this query slow", or pastes SQL for feedback
Auditing existing queries in a codebase or a migration / PR diff
Another agent produced SQL and it must be checked before it ships

NEVER use this skill for :

Writing new SQL from scratch (use the relevant postgres-syntax-* skill)
Reviewing schema DDL only, with no queries (use postgres-agents-schema-reviewer)
Reviewing a migration's safety / locking (use postgres-agents-migration-reviewer)

Decision Trees :

Grading a finding :

A check fired. What severity?
  |
  |-- Does it change the RESULT SET (wrong rows, lost rows, data loss)?
  |     YES --> CRITICAL. Verdict becomes REQUEST CHANGES.
  |
  |-- Does it force a seqscan / extra round-trips / unbounded work AT SCALE?
  |     YES --> WARNING.
  |
  |-- Is it correct and fast enough, just not ideal style?
        YES --> INFO.

Is this Seq Scan actually a problem? :

EXPLAIN shows a Seq Scan. Flag it?
  |
  |-- Table is small (a few thousand rows, fits in a page handful)?
  |     --> NOT a defect. A Seq Scan beats an index scan here. Do NOT flag.
  |
  |-- Query returns most of the table (low selectivity)?
  |     --> NOT a defect. A Seq Scan is the correct plan. Do NOT flag.
  |
  |-- Large table, selective predicate, "Rows Removed by Filter" is huge?
        --> WARNING. A usable index is missing or suppressed. Flag it.

When to demand EXPLAIN before concluding :

Finding is about index usage (C-3, C-5, C-6, C-12, slow-query claims)?
  |-- YES --> Do NOT assert "this is slow". Recommend:
  |            EXPLAIN (ANALYZE, BUFFERS) <query>
  |           Report the finding as "suspected" until the plan confirms it.
  |-- NO  (C-2 missing WHERE, C-10 NOT IN NULL, C-13 fan-out) -->
            These are static correctness bugs. EXPLAIN not needed to flag them.

The Review Checklist :

Run every check. Each check has a trigger, a rule, a severity, and a cite-skill. Full per-check rationale and examples are in references/methods.md and references/examples.md.

C-1 : SELECT * in application code

ALWAYS flag SELECT * in code that ships. NEVER accept it outside ad-hoc psql. Severity : WARNING. Cite : postgres-syntax-dml-ddl. WHY : column drift breaks the app when columns are added or reordered, it over-fetches TOAST columns, and it blocks index-only scans. Fix : enumerate columns.

C-2 : UPDATE / DELETE without WHERE

ALWAYS flag an UPDATE or DELETE with no WHERE clause. NEVER assume it is intentional. Severity : CRITICAL. Cite : postgres-syntax-dml-ddl. WHY : it mutates every row in the table. Fix : add the intended WHERE, or confirm in writing that a full-table change is the goal.

C-3 : Implicit type cast in WHERE

ALWAYS flag a predicate that compares an indexed column to a literal of a different type (varchar_col = 123). NEVER cast the column. Severity : WARNING. Cite : postgres-impl-indexing-strategy. WHY : Postgres casts the column on every row, defeating the index. Fix : cast the literal instead (varchar_col = '123').

C-4 : JSONB @> / ? without a GIN index

ALWAYS flag jsonb_col @> ..., ?, ?|, ?& when no GIN index covers the column. Severity : WARNING. Cite : postgres-syntax-jsonb. WHY : containment without a GIN index is a Seq Scan. Fix : CREATE INDEX ... USING GIN (col jsonb_path_ops) for @>-only workloads, plain jsonb_ops if key-exists operators are needed.

C-5 : Function call on an indexed column in WHERE

ALWAYS flag lower(col) = ..., date(col) = ... etc. when no matching expression index exists. Severity : WARNING. Cite : postgres-impl-indexing-strategy. WHY : a plain B-tree on col cannot serve f(col). Fix : CREATE INDEX ON t ((lower(col))) with an IMMUTABLE function, or rewrite the predicate to be sargable.

C-6 : LIKE with a leading wildcard

ALWAYS flag LIKE '%term%' and LIKE '%term'. NEVER claim a B-tree index helps here. Severity : WARNING. Cite : postgres-syntax-full-text-search. WHY : a B-tree only serves anchored prefixes (LIKE 'term%'). Fix : a pg_trgm GIN index for substring search, or full-text search for word search.

C-7 : N+1 query pattern

ALWAYS flag application code that issues one query per row in a loop. Severity : WARNING. Cite : postgres-syntax-lateral-joins. WHY : N round-trips where one set-based query suffices. Fix : a JOIN, = ANY($array), or a LATERAL join for top-N-per-group.

C-8 : OFFSET pagination with large offsets

ALWAYS flag LIMIT n OFFSET m paging where m grows large. Severity : WARNING. Cite : postgres-impl-query-performance-toolkit. WHY : OFFSET m still reads and discards m rows, so deep pages get linearly slower. Fix : keyset / seek pagination (WHERE (sort_key) > ($last_key) ORDER BY sort_key LIMIT n).

C-9 : Window function vs GROUP BY mismatch

ALWAYS flag a window aggregate used where only a grouped total is needed, or a self-join used where a window function is clearer and cheaper. Severity : INFO. Cite : postgres-syntax-window-functions. WHY : SUM(x) OVER (PARTITION BY g) keeps every row; if per-row output is not used, GROUP BY g is cheaper. Fix : match the construct to whether per-row output is needed.

C-10 : NOT IN (subquery) over a nullable column

ALWAYS flag NOT IN (SELECT col FROM ...) when col can be NULL. Severity : CRITICAL. Cite : postgres-syntax-dml-ddl. WHY : one NULL in the subquery makes NOT IN return zero rows for everything (three-valued logic). This is a silent correctness bug. Fix : NOT EXISTS (SELECT 1 FROM ... WHERE ...).

C-11 : ORDER BY without LIMIT on a large result

ALWAYS flag ORDER BY with no LIMIT when the result set is expected to be large. Severity : WARNING. Cite : postgres-impl-query-performance-toolkit. WHY : the full result must be sorted, often spilling to disk (external merge), when the consumer reads only the first rows. Fix : add a LIMIT, or drop the ORDER BY if the order is unused.

C-12 : Missing index on a frequently-filtered column

ALWAYS flag a hot WHERE / JOIN column with no supporting index. Severity : WARNING. Cite : postgres-impl-query-performance-toolkit. WHY : every execution seqscans. Fix : recommend EXPLAIN (ANALYZE, BUFFERS) to confirm, then a B-tree (or partial / multicolumn) index sized to the predicate.

C-13 : DISTINCT / GROUP BY masking a join fan-out bug

ALWAYS flag a DISTINCT or GROUP BY that exists only to collapse duplicate rows produced by a join. Severity : WARNING (escalate to CRITICAL if it changes aggregate results). Cite : postgres-syntax-lateral-joins. WHY : the duplicates mean the join multiplies rows; DISTINCT hides the symptom and any SUM/COUNT is then wrong. Fix : correct the join condition or aggregate in a subquery before joining.

Output Format :

Emit findings as a list, then one verdict line. Per finding :

[SEVERITY] <location>
  Problem : <what is wrong>
  Fix     : <concrete change>
  Cite    : <sibling skill, if any>

End with exactly one verdict :

Verdict : REQUEST CHANGES   (1 CRITICAL, 2 WARNING, 0 INFO)

See references/methods.md for the full report schema and references/examples.md for complete worked reviews.

Anti-Patterns :

(Reviewer anti-patterns. Full cause + symptom + fix in references/anti-patterns.md)

Rubber-stamping : approving a query without running all 13 checks or seeing an EXPLAIN
Seq-Scan-phobia : flagging every Seq Scan, including the correct plan on a small table
Syntax-only review : checking SQL grammar but ignoring the execution plan and index usage
Severity inflation : grading a style nit as CRITICAL, or a silent correctness bug as INFO
Citing nothing : reporting a finding without the sibling skill that fixes it

Reference Links :

references/methods.md : full checklist table, severity rubric, output report schema, EXPLAIN guidance
references/examples.md : complete worked review examples, before/after fixes per check, version-annotated
references/anti-patterns.md : reviewer anti-patterns with cause + symptom + fix