PostgreSQL Schema & Migrations

Pathrule3 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 (PostgreSQL 18)

Index and constraint design checklist

migrations/

Migrations are forward-only, no down steps

DDL on populated tables must be lock-safe

Evolve columns with expand-contract, never in place

Rules

Migrations are forward-only, no down steps/db/migrationshighstrictNever author down/rollback migrations; recover by shipping a new forward migration.

1	Every migration moves the schema forward only. There are no `down` or rollback steps in this codebase.
2
3	- Do not write a `down`, `rollback`, or reverse step in any migration. A migration that has touched production data cannot be cleanly reversed, and a `down` that drops a column destroys data the previous app version may still need.
4	- Recover from a bad migration by shipping a new forward migration that corrects the state, not by reverting.
5	- Keep each migration small and single-purpose so a follow-up fix is easy to reason about.
6	- Migrations are immutable once merged. Never edit a migration file that has already run anywhere; add a new one.

DDL on populated tables must be lock-safe/db/migrationshighstrictUse CONCURRENTLY, lock_timeout, and NOT VALID so DDL never takes a long ACCESS EXCLUSIVE lock on a hot table.

1	DDL against a table that already holds production rows must acquire only weak, short locks, so a deploy never blocks live traffic behind an `ACCESS EXCLUSIVE` lock.
2
3	- Build and drop indexes with `CREATE INDEX CONCURRENTLY` / `DROP INDEX CONCURRENTLY`. These take only a `SHARE UPDATE EXCLUSIVE` lock but cannot run inside a transaction block, so the migration tool must run that statement outside any wrapping transaction.
4	- Set `SET lock_timeout = '5s'` (or similar) before DDL on a populated table. A statement that cannot get its lock then fails fast instead of queuing every subsequent query behind it.
5	- Add foreign keys and `CHECK` constraints as `NOT VALID` first, then run `VALIDATE CONSTRAINT` in a separate statement. The `NOT VALID` step takes a brief strong lock to add the catalog entry; `VALIDATE` scans the table under a weaker `SHARE UPDATE EXCLUSIVE` lock that allows concurrent reads and writes.
6	- Add a unique constraint without a long lock by running `CREATE UNIQUE INDEX CONCURRENTLY`, then `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX <name>`.
7	- If a `CREATE INDEX CONCURRENTLY` fails, it leaves an `INVALID` index behind; drop it (`DROP INDEX CONCURRENTLY`) before retrying.

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 every deployed app version stays compatible during a rolling deploy.
2
3	- Never `RENAME` or `ALTER ... TYPE` a column in a single step while the old app version is still running. Add the new column, dual-write from the app, backfill, switch reads to the new column, then drop the old column in a later deploy.
4	- Add new columns as nullable or with a NON-volatile constant `DEFAULT`. PostgreSQL stores a constant default in catalog metadata (`pg_attribute.attmissingval`) so the `ALTER TABLE` is instant with no table rewrite. A VOLATILE default (such as `clock_timestamp()`), a stored generated column, or an identity column DOES force a full rewrite under a strong lock; avoid those on large populated tables.
5	- Backfill large tables in bounded batches (for example a few thousand rows per statement), each batch in its own transaction, not one giant `UPDATE` that holds locks and bloats WAL.
6	- Apply `NOT NULL` on a backfilled column safely: add a `CHECK (col IS NOT NULL) NOT VALID`, `VALIDATE` it, then `SET NOT NULL` (which can use the validated check to skip a scan), instead of `SET NOT NULL` directly on a large table.
7	- Drop the old column or table only after every running app version has stopped reading and writing it.

Memories

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

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 function added in PG 18) when ids are exposed externally or generated client-side. UUIDv7 is time-ordered so it indexes far better than random UUIDv4. Never use `serial`/`bigserial` (they leave the sequence ownership and grants in a surprising state and are effectively legacy).
4	- Timestamps: always `timestamptz`, never `timestamp` (without time zone). `timestamptz` records a single absolute moment; `timestamp` silently drops the offset and causes timezone bugs. Default audit columns to `now()`.
5	- Strings: use `text`. `text` and `varchar(n)` share identical storage and performance in PostgreSQL; `varchar(n)` only adds a length check. Enforce a real maximum with a `CHECK` constraint when the limit is a genuine business rule, not a guess.
6	- Money and exact decimals: `numeric`, never `float`/`double precision` (binary floats cannot represent decimal cents exactly). Use `boolean` for flags and `jsonb` (not `json`) for semi-structured data, since `jsonb` is indexable and `json` only stores reparsed text.
7
8	See /db/migrations for the lock-safe and expand-contract rules that govern how these tables are changed after creation.

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

1	Model integrity at the database, and index for the queries that actually run.
2
3	- Index every foreign key column on the CHILD side. PostgreSQL does NOT create that index automatically, and without it a delete or update of a parent row scans the whole child table to check references, which is the classic cause of mysteriously slow parent deletes and `ON DELETE CASCADE` operations.
4	- Use partial indexes (for example `WHERE deleted_at IS NULL`) and expression indexes when queries only filter a subset or a computed value, instead of indexing the whole column. Smaller indexes mean less write amplification and a higher chance the planner uses them.
5	- Enforce business rules with `NOT NULL`, `UNIQUE`, `CHECK`, and foreign keys in the schema rather than trusting application code; the database is the only layer every writer goes through.
6	- Avoid redundant indexes: a B-tree on `(a, b)` already serves queries filtering on `a` alone, so a separate index on `(a)` is usually wasted write cost.
7	- For low-cardinality columns the planner will ignore the index, so do not add one just because a column is in a WHERE clause.
8
9	See /db/migrations for how to add these indexes and constraints concurrently without locking production.

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`/`bigserial`.
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`, not `json`.
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 on the child side.
18	- [ ] Partial or expression indexes are used where queries filter a subset, instead of broad full-column indexes; no redundant indexes were added.
19
20	## Forward-only
21
22	- [ ] Migration has no `down`/rollback step.
23	- [ ] No already-merged migration file was edited; this is a new file.
24
25	## Lock-safe DDL
26
27	- [ ] `lock_timeout` is set before DDL on any populated table.
28	- [ ] Indexes are built/dropped `CONCURRENTLY`, and that statement runs outside a transaction block.
29	- [ ] New foreign keys and check constraints are added `NOT VALID`, then `VALIDATE`d in a separate statement.
30	- [ ] New columns are nullable or use a NON-volatile constant `DEFAULT` so no table rewrite is triggered (no volatile default, identity, or stored generated column added to a large table).
31	- [ ] A unique constraint is added via `CREATE UNIQUE INDEX CONCURRENTLY` + `ADD CONSTRAINT ... USING INDEX`.
32
33	## Expand-contract
34
35	- [ ] Renames and type changes are split into expand, backfill, and contract deploys; nothing is renamed/retyped in place.
36	- [ ] Backfills run in bounded batches, each in its own transaction.
37	- [ ] `NOT NULL` on a backfilled column is applied via a validated `CHECK`, not a direct `SET NOT NULL` scan on a large table.
38	- [ ] 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