Home › PostgreSQL › PostgreSQL schema & SQL AGENTS.md

PostgreSQL schema & SQL AGENTS.md

AGENTS.md for working on PostgreSQL 16+ schemas and SQL: safe migrations, indexes, constraints, and query plans.

AGENTS.mdPostgreSQL

How to use

Save this as AGENTS.md in your repository root, commit it, and your agent will pick it up automatically. Edit the commands to match your project.

# AGENTS.md: PostgreSQL work

## Migrations
- Every change is a new migration file in the repo's tool (`migrations/`, Flyway, sqitch, Prisma, etc.). Never edit an applied migration.
- Make migrations safe on live tables:
  - `CREATE INDEX CONCURRENTLY` (outside a transaction) for big tables.
  - Add columns nullable or with a constant default; backfill in batches; then add `NOT NULL` (via a `CHECK ... NOT VALID` + `VALIDATE CONSTRAINT`).
  - Rename or drop in two deploys: stop using it first, remove later.
  - Set `lock_timeout` (e.g. `SET lock_timeout = '5s'`) in migrations that take locks.

## Schema
- Primary keys `bigint generated always as identity` or `uuid` (v7 if available); be consistent with the existing tables.
- `timestamptz` for time, `text` over `varchar(n)`, `numeric` for money, `jsonb` only for truly unstructured data.
- Enforce rules in the database: `NOT NULL`, `UNIQUE`, `CHECK`, foreign keys with an explicit `ON DELETE`.
- Index every foreign key column used in joins.

## Queries
- Parameterized queries only. Check new queries with `EXPLAIN (ANALYZE, BUFFERS)` on realistic data and paste the plan in the PR.
- Paginate with keyset (`WHERE id > $1 ORDER BY id LIMIT 50`) instead of large `OFFSET`.
- No `SELECT *` in application code.

## Never
Run `DROP`, `TRUNCATE` or an unbounded `DELETE`/`UPDATE` against a shared database without explicit approval.