Postgresql Code Review

PostgreSQL code review

Review PostgreSQL-specific code for correctness, performance, type quality, security, and maintainability, then return a verdict, evidence-backed findings, and paste-ready corrected SQL.

When to invoke

• "Review this PostgreSQL migration for anti-patterns."
• "Audit our JSONB and array usage."
• "Is this schema using the right PostgreSQL types?"
• "Check this PL/pgSQL function and trigger before it merges."
• "Review this Row Level Security policy."

Criteria

JSONB and arrays

Schema design and data types

Example type objects to preserve: CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');, CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');, and CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);.

Functions, triggers, extensions, and security

PostgreSQL anti-patterns

• Avoiding PostgreSQL-specific indexes such as GIN and GiST for appropriate data types.
• Treating JSONB like a string field instead of using operators such as @> and ?.
• Ignoring array operators and indexes.
• Choosing poor partition keys or not leveraging partitioning where scale requires it.
• Using VARCHAR for limited value sets instead of ENUM, domain, lookup table, or CHECK.
• Missing validation constraints on data that application code assumes.
• Using TIMESTAMP for real-world instants where time zone correctness matters.
• Leaving unstructured JSONB without validation for fields the application depends on.

SQL vocabulary to preserve

Use exact PostgreSQL examples when relevant: UNIQUE, NULL, created_at, idx_users_metadata, valid_email, table_name, application_role, UUID, BEGIN, BEFORE, EACH, GOOD, JSONB/arrays, GIN/GiST, function/procedure, and built-in security or extension capabilities.

Example schema facts include email CITEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(), metadata JSONB DEFAULT '{}', CONSTRAINT valid_email CHECK (...), and CREATE INDEX idx_users_metadata ON users USING gin(metadata);.

Output template

## PostgreSQL review — <file or selection>

**Verdict:** Pass | Fix required | Reject

| # | Severity | Finding | Evidence | Fix |
| --- | --- | --- | --- | --- |
| 1 | High | User input concatenated into SQL | `<file:line or snippet>` | Bind through JPQL, a derived query, or a parameterized native query. |
| 2 | Medium | JSONB containment query has no GIN index | `Seq Scan on orders` | `CREATE INDEX idx_orders_data ON orders USING gin(data);` |
| 3 | Low | VARCHAR used for case-insensitive email | `email VARCHAR(255)` | Use `CITEXT` plus an appropriate `CHECK` constraint. |

### Corrected SQL
```sql
CREATE INDEX idx_orders_data ON orders USING gin(data);
-- Repository query stays parameterized: WHERE data @> :filter

## Quality gate

- [ ] A verdict is stated: Pass, Fix required, or Reject.
- [ ] Every finding carries severity and concrete evidence such as file/line, snippet, or plan output.
- [ ] No user input is concatenated into SQL; every parameter is bound.
- [ ] PostgreSQL-specific types including `CITEXT`, `JSONB`, arrays, `ENUM`, and domains were considered.
- [ ] Index types including `GIN`, `GiST`, and partial indexes were evaluated where relevant.
- [ ] `CHECK`, `ENUM`, and domain constraints were validated for constrained values.
- [ ] `RLS`, privileges, extension usage, functions, and triggers were checked when present.
- [ ] Corrected SQL is paste-ready and rollback-safe for schema changes.