PostgreSQL Schema & Migrations

Pathrule2 Rules • 2 Memories • 1 Skill

A pattern bundle that teaches AI agents how to model PostgreSQL tables with correct types and constraints, and how to evolve them without downtime. It enforces forward-only, expand-contract migrations, concurrent index builds, and validated constraints so schema changes never lock production traffic. Tuned for PostgreSQL 18 with native UUIDv7 and identity columns.

Suggested path map

Pathrule places each piece on the matching path, so your assistant only sees it where it belongs. This is the scoping you get on import; you can adjust it in your workspace.

/ workspace root

postgres-schema-review

db/

Default column types for new tables

Index and constraint design checklist

migrations/

Migrations are forward-only and lock-safe

Evolve columns with expand-contract, never in place

Rules

Migrations are forward-only and lock-safe/db/migrationshighstrictNever write down migrations; never take ACCESS EXCLUSIVE locks on hot tables.

1	Every migration moves the schema forward and acquires only weak locks, so deploys never block live traffic.
2
3	- Write forward-only migrations. Do not author `down`/rollback steps; recover by shipping a new forward migration.
4	- Build and drop indexes with `CREATE INDEX CONCURRENTLY` / `DROP INDEX CONCURRENTLY`, which cannot run inside a transaction block.
5	- Set `SET lock_timeout = '5s'` before any DDL on a populated table so a blocked statement fails fast instead of queueing all traffic behind an `ACCESS EXCLUSIVE` lock.
6	- Add foreign keys and check constraints as `NOT VALID` first, then `VALIDATE CONSTRAINT` in a separate statement to avoid a full-table scan under a strong lock.

Evolve columns with expand-contract, never in place/db/migrationshighstrictRename and retype across multiple deploys so old and new app versions both keep working.

1	Schema changes that affect existing data run as separate expand, backfill, and contract migrations so each deployed app version stays compatible.
2
3	- Never `RENAME` or `ALTER ... TYPE` a column in a single step. Add the new column, dual-write from the app, backfill in batches, switch reads, then drop the old column in a later deploy.
4	- Add new columns as nullable or with a constant `DEFAULT`; PostgreSQL stores constant defaults as metadata so no table rewrite occurs.
5	- Backfill large tables in bounded batches (for example a few thousand rows per statement) inside their own transactions, not one giant `UPDATE`.
6	- Drop the old structure only after every running app version has stopped reading or writing it.

Memories

Default column types for new tables/dbThe canonical type and key choices for PostgreSQL 18 tables.

1	Pick the same correct types on every new table so the schema stays consistent and bug-free on PostgreSQL 18.
2
3	- Primary keys: `bigint GENERATED ALWAYS AS IDENTITY` for internal rows, or `uuid DEFAULT uuidv7()` (native in PG 18) when ids are exposed externally or generated client-side. Never use `serial`/`bigserial`.
4	- Timestamps: always `timestamptz`, never `timestamp` (without time zone). Default audit columns to `now()`.
5	- Strings: use `text`; there is no performance gain from `varchar(n)`. Enforce length only with a `CHECK` constraint when a real limit exists.
6	- Money and exact decimals: `numeric`, never `float`/`double precision`. Use `boolean` for flags and `jsonb` (not `json`) for semi-structured data.

Index and constraint design checklist/dbWhere indexes and constraints belong, and how to add unique safely.

1	Model integrity at the database, and index for the queries that actually run.
2
3	- Index every foreign key column; PostgreSQL does not create that index automatically and unindexed FKs make parent deletes and joins slow.
4	- Use partial indexes (`WHERE deleted_at IS NULL`) and expression indexes instead of indexing whole columns when queries filter on a subset.
5	- Enforce business rules with `CHECK`, `NOT NULL`, `UNIQUE`, and foreign keys in the schema rather than trusting application code.
6	- Add a unique constraint without a long lock by running `CREATE UNIQUE INDEX CONCURRENTLY` then `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX`.

Skills

postgres-schema-review/rootPre-merge checklist for any PostgreSQL schema change or migration.

1	---
2	name: postgres-schema-review
3	description: Review a PostgreSQL schema change or migration before merge. Use when adding or altering tables, columns, indexes, or constraints, or when writing a migration file, to confirm correct types, sound integrity, and lock-safe forward-only DDL on PostgreSQL 18.
4	---
5
6	# PostgreSQL schema and migration review
7
8	## Types and keys
9
10	- [ ] Primary key is `bigint GENERATED ALWAYS AS IDENTITY` or `uuid DEFAULT uuidv7()`, not `serial`.
11	- [ ] All point-in-time columns are `timestamptz`, not `timestamp`.
12	- [ ] Strings use `text` (length enforced via `CHECK` only when a real limit exists); money/decimals use `numeric`; structured data uses `jsonb`.
13
14	## Integrity and indexes
15
16	- [ ] `NOT NULL`, `UNIQUE`, `CHECK`, and foreign keys express the real business rules at the database level.
17	- [ ] Every foreign key column has a covering index.
18	- [ ] Partial or expression indexes are used where queries filter a subset, instead of broad full-column indexes.
19
20	## Lock-safe migration
21
22	- [ ] Migration is forward-only with no `down` step.
23	- [ ] `lock_timeout` is set before DDL on any populated table.
24	- [ ] Indexes are built/dropped `CONCURRENTLY` and that statement is outside a transaction block.
25	- [ ] New foreign keys and check constraints are added `NOT VALID`, then validated separately.
26	- [ ] New columns are nullable or use a constant `DEFAULT` so no table rewrite is triggered.
27
28	## Expand-contract
29
30	- [ ] Renames and type changes are split into expand, backfill, and contract deploys.
31	- [ ] Backfills run in bounded batches, each in its own transaction.
32	- [ ] Old columns or tables are dropped only after no running app version uses them.

Why this pattern

AI agents write migrations that rename columns in place, build indexes that lock the table, and pick types that cause silent data and timezone bugs.

Built for Backend and platform teams running PostgreSQL in production.

Keeps your assistant from:

Blocking ACCESS EXCLUSIVE locks from rewriting tables or building indexes inline during deploys
Destructive in-place column renames or retypes that break the previous app version mid-rollout
Wrong column types like timestamp without time zone, varchar(n), or serial that cause timezone and overflow bugs

View as markdown

License: Apache-2.0
Version: 1.0.0
Updated: 2026-06-09