PostgreSQL Schema Reviewer
Review Postgres schemas and migrations with the eye of an engineer who has run them against
large production tables. Output concrete, prioritized findings — not generic advice.
When to use
- User shares a migration file,
CREATE TABLE/ALTER TABLE, or ORM model
- "Review this schema / migration / table design"
- Designing a new table or changing an existing one
Review checklist
Types
- Use
TIMESTAMPTZ for all points in time, never TIMESTAMP (which silently drops zone info).
- Money/amounts:
NUMERIC(precision, scale), never float/double.
- Prefer
BIGINT identity / UUID over INT for keys likely to grow; text over varchar(n) unless a real limit exists.
- Enums: prefer a lookup table or a
CHECK constraint over native ENUM (which is painful to alter).
Integrity & hygiene
- Every foreign key column should usually have a supporting index (PG does not auto-create it).
- Flag nullable columns that should be
NOT NULL; flag missing sensible DEFAULTs.
- Add
created_at TIMESTAMPTZ NOT NULL DEFAULT now() and updated_at where appropriate.
- Unique/business constraints expressed in the DB, not just app code.
Naming consistency
- Consistent casing (snake_case), consistent singular/plural table naming, consistent id naming
across services (
request_id vs correlation_id must mean the same thing everywhere).
Indexes & scale
- Indexes on columns used in
WHERE, JOIN, ORDER BY; composite index column order matches query predicates.
- Large append-only tables (audit, events, logs, notifications) → consider range/list partitioning
(e.g. monthly) and
pg_partman.
- Watch for redundant/duplicate indexes and over-indexing on write-heavy tables.
Migration safety (the dangerous part)
CREATE INDEX on a big table must be CREATE INDEX CONCURRENTLY (and cannot run in a txn block).- Adding a column with a volatile default, or
SET NOT NULL, can rewrite/lock the table on older PG —
prefer add-nullable → backfill in batches → add constraint NOT VALID → VALIDATE CONSTRAINT.
- Changing a column type rewrites the table and takes an
ACCESS EXCLUSIVE lock — call it out.
- Dropping a column/table: confirm it's safe; suggest a deprecation step first.
Output format
Give findings grouped by severity:
- Blocking — will cause downtime, data loss, or correctness bugs (e.g. naked
CREATE INDEX
on a hot table, TIMESTAMP for timestamps, money as float).
- Should fix — missing FK index, missing NOT NULL, naming inconsistency.
- Consider — partitioning, constraint tightening, index review.
For each finding: the exact line/object, why it matters in production, and the corrected SQL.
End with a one-line verdict: safe to ship / fix blockers first.
1---2name: postgres-schema-reviewer3description: Use when reviewing PostgreSQL schema changes, migrations, or table designs before they ship. Checks TIMESTAMP vs TIMESTAMPTZ correctness, missing indexes on foreign keys and common query predicates, naming consistency (e.g. request_id vs correlation_id, singular vs plural), nullability and default hygiene, money stored as numeric not float, partitioning candidates for large append-only tables, and unsafe migration operations that take heavy locks (adding NOT NULL with a default on old PG, non-CONCURRENT index creation, rewriting column types). Trigger when the user shares a SQL migration, a CREATE TABLE / ALTER TABLE statement, an ORM model, or asks to review a database schema or migration.4license: MIT5---67# PostgreSQL Schema Reviewer89Review Postgres schemas and migrations with the eye of an engineer who has run them against10large production tables. Output concrete, prioritized findings — not generic advice.1112## When to use13- User shares a migration file, `CREATE TABLE`/`ALTER TABLE`, or ORM model14- "Review this schema / migration / table design"15- Designing a new table or changing an existing one1617## Review checklist1819**Types**20- Use `TIMESTAMPTZ` for all points in time, never `TIMESTAMP` (which silently drops zone info).21- Money/amounts: `NUMERIC(precision, scale)`, never `float`/`double`.22- Prefer `BIGINT` identity / UUID over `INT` for keys likely to grow; `text` over `varchar(n)` unless a real limit exists.23- Enums: prefer a lookup table or a `CHECK` constraint over native `ENUM` (which is painful to alter).2425**Integrity & hygiene**26- Every foreign key column should usually have a supporting index (PG does **not** auto-create it).27- Flag nullable columns that should be `NOT NULL`; flag missing sensible `DEFAULT`s.28- Add `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` and `updated_at` where appropriate.29- Unique/business constraints expressed in the DB, not just app code.3031**Naming consistency**32- Consistent casing (snake_case), consistent singular/plural table naming, consistent id naming33 across services (`request_id` vs `correlation_id` must mean the same thing everywhere).3435**Indexes & scale**36- Indexes on columns used in `WHERE`, `JOIN`, `ORDER BY`; composite index column order matches query predicates.37- Large append-only tables (audit, events, logs, notifications) → consider **range/list partitioning**38 (e.g. monthly) and `pg_partman`.39- Watch for redundant/duplicate indexes and over-indexing on write-heavy tables.4041**Migration safety (the dangerous part)**42- `CREATE INDEX` on a big table must be `CREATE INDEX CONCURRENTLY` (and cannot run in a txn block).43- Adding a column with a volatile default, or `SET NOT NULL`, can rewrite/lock the table on older PG —44 prefer add-nullable → backfill in batches → add constraint `NOT VALID` → `VALIDATE CONSTRAINT`.45- Changing a column type rewrites the table and takes an `ACCESS EXCLUSIVE` lock — call it out.46- Dropping a column/table: confirm it's safe; suggest a deprecation step first.4748## Output format4950Give findings grouped by severity:511. **Blocking** — will cause downtime, data loss, or correctness bugs (e.g. naked `CREATE INDEX`52 on a hot table, `TIMESTAMP` for timestamps, money as float).532. **Should fix** — missing FK index, missing NOT NULL, naming inconsistency.543. **Consider** — partitioning, constraint tightening, index review.5556For each finding: the exact line/object, *why* it matters in production, and the corrected SQL.57End with a one-line verdict: safe to ship / fix blockers first.
Shravan-Amberkar