# Pathrule Pattern: PostgreSQL Schema & Migrations (1.0.0) # ::pathrule:package:postgres-schema ### [RULE] Migrations are forward-only and lock-safe (path: /db/migrations) Every migration moves the schema forward and acquires only weak locks, so deploys never block live traffic. - Write forward-only migrations. Do not author `down`/rollback steps; recover by shipping a new forward migration. - Build and drop indexes with `CREATE INDEX CONCURRENTLY` / `DROP INDEX CONCURRENTLY`, which cannot run inside a transaction block. - 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. - 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. --- ### [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 each deployed app version stays compatible. - 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. - Add new columns as nullable or with a constant `DEFAULT`; PostgreSQL stores constant defaults as metadata so no table rewrite occurs. - Backfill large tables in bounded batches (for example a few thousand rows per statement) inside their own transactions, not one giant `UPDATE`. - Drop the old structure only after every running app version has stopped reading or writing it. --- ### [MEMORY] Default column types for new tables (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 in PG 18) when ids are exposed externally or generated client-side. Never use `serial`/`bigserial`. - Timestamps: always `timestamptz`, never `timestamp` (without time zone). Default audit columns to `now()`. - Strings: use `text`; there is no performance gain from `varchar(n)`. Enforce length only with a `CHECK` constraint when a real limit exists. - Money and exact decimals: `numeric`, never `float`/`double precision`. Use `boolean` for flags and `jsonb` (not `json`) for semi-structured data. --- ### [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; PostgreSQL does not create that index automatically and unindexed FKs make parent deletes and joins slow. - Use partial indexes (`WHERE deleted_at IS NULL`) and expression indexes instead of indexing whole columns when queries filter on a subset. - Enforce business rules with `CHECK`, `NOT NULL`, `UNIQUE`, and foreign keys in the schema rather than trusting application code. - Add a unique constraint without a long lock by running `CREATE UNIQUE INDEX CONCURRENTLY` then `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX`. --- ### [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`. - [ ] 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`. ## 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. - [ ] Partial or expression indexes are used where queries filter a subset, instead of broad full-column indexes. ## Lock-safe migration - [ ] Migration is forward-only with no `down` step. - [ ] `lock_timeout` is set before DDL on any populated table. - [ ] Indexes are built/dropped `CONCURRENTLY` and that statement is outside a transaction block. - [ ] New foreign keys and check constraints are added `NOT VALID`, then validated separately. - [ ] New columns are nullable or use a constant `DEFAULT` so no table rewrite is triggered. ## Expand-contract - [ ] Renames and type changes are split into expand, backfill, and contract deploys. - [ ] Backfills run in bounded batches, each in its own transaction. - [ ] Old columns or tables are dropped only after no running app version uses them.