# Pathrule Pattern: PostgreSQL Schema & Migrations (1.0.0) # ::pathrule:package:postgres-schema ### [RULE] Migrations are forward-only, no down steps (path: /db/migrations) Every migration moves the schema forward only. There are no `down` or rollback steps in this codebase. - 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. - Recover from a bad migration by shipping a new forward migration that corrects the state, not by reverting. - Keep each migration small and single-purpose so a follow-up fix is easy to reason about. - Migrations are immutable once merged. Never edit a migration file that has already run anywhere; add a new one. --- ### [RULE] DDL on populated tables must be lock-safe (path: /db/migrations) 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. - 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. - 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. - 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. - Add a unique constraint without a long lock by running `CREATE UNIQUE INDEX CONCURRENTLY`, then `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX `. - If a `CREATE INDEX CONCURRENTLY` fails, it leaves an `INVALID` index behind; drop it (`DROP INDEX CONCURRENTLY`) before retrying. --- ### [RULE] Evolve columns with expand-contract, never in place (path: /db/migrations) 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. - 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. - 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. - 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. - 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. - Drop the old column or table only after every running app version has stopped reading and writing it. --- ### [MEMORY] Default column types for new tables (PostgreSQL 18) (path: /db) Pick the same correct types on every new table so the schema stays consistent and bug-free on PostgreSQL 18. - 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). - 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()`. - 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. - 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. See /db/migrations for the lock-safe and expand-contract rules that govern how these tables are changed after creation. --- ### [MEMORY] Index and constraint design checklist (path: /db) Model integrity at the database, and index for the queries that actually run. - 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. - 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. - 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. - 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. - For low-cardinality columns the planner will ignore the index, so do not add one just because a column is in a WHERE clause. See /db/migrations for how to add these indexes and constraints concurrently without locking production. --- ### [SKILL] postgres-schema-review (path: /) --- name: postgres-schema-review 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. --- # PostgreSQL schema and migration review ## Types and keys - [ ] Primary key is `bigint GENERATED ALWAYS AS IDENTITY` or `uuid DEFAULT uuidv7()`, not `serial`/`bigserial`. - [ ] All point-in-time columns are `timestamptz`, not `timestamp`. - [ ] Strings use `text` (length enforced via `CHECK` only when a real limit exists); money/decimals use `numeric`; structured data uses `jsonb`, not `json`. ## Integrity and indexes - [ ] `NOT NULL`, `UNIQUE`, `CHECK`, and foreign keys express the real business rules at the database level. - [ ] Every foreign key column has a covering index on the child side. - [ ] Partial or expression indexes are used where queries filter a subset, instead of broad full-column indexes; no redundant indexes were added. ## Forward-only - [ ] Migration has no `down`/rollback step. - [ ] No already-merged migration file was edited; this is a new file. ## Lock-safe DDL - [ ] `lock_timeout` is set before DDL on any populated table. - [ ] Indexes are built/dropped `CONCURRENTLY`, and that statement runs outside a transaction block. - [ ] New foreign keys and check constraints are added `NOT VALID`, then `VALIDATE`d in a separate statement. - [ ] 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). - [ ] A unique constraint is added via `CREATE UNIQUE INDEX CONCURRENTLY` + `ADD CONSTRAINT ... USING INDEX`. ## Expand-contract - [ ] Renames and type changes are split into expand, backfill, and contract deploys; nothing is renamed/retyped in place. - [ ] Backfills run in bounded batches, each in its own transaction. - [ ] `NOT NULL` on a backfilled column is applied via a validated `CHECK`, not a direct `SET NOT NULL` scan on a large table. - [ ] Old columns or tables are dropped only after no running app version uses them.