/ia-postgresql

name: ia-postgresql
class: language
description: >-
  PostgreSQL schema design, query optimization, indexing, and administration.
  Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window
  functions, or EXPLAIN ANALYZE.
paths: "**/*.sql"

PostgreSQL

Data Type Defaults

| Need | Use | Avoid | |------|-----|-------| | Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` | | Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) | | Text | `TEXT` | `VARCHAR(n)` unless constraint needed | | Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` | | Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans | | JSON | `JSONB` | `JSON` (no indexing), text JSON | | UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension | | IP addresses | `INET` / `CIDR` | text | | Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns |

Schema Rules

• Every FK column gets an index (PG does NOT auto-create these)
• `NOT NULL` on every column unless NULL has business meaning
• `CHECK` constraints for domain rules at DB level
• `EXCLUDE` constraints for range overlaps: `EXCLUDE USING gist (room WITH =, during WITH &&)`
• Default `created_at TIMESTAMPTZ NOT NULL DEFAULT now()`
• Separate `updated_at` with trigger, never trust app layer alone
• Use `BIGINT` PKs -- cheaper JOINs than UUID, better index locality
• Safe migrations: `CREATE INDEX CONCURRENTLY`, add columns with `DEFAULT` (instant add). Never `ALTER TYPE` on large tables in-place.
• `NULLS NOT DISTINCT` on unique indexes (PG15+) -- treats NULLs as equal for uniqueness
• Under `NULLS NOT DISTINCT`, a pre-flight duplicate check written with SQL `=` misses NULL/NULL collisions -- the index rejects the second row, but `NULL = NULL` evaluates to NULL (not true), so a self-join or `WHERE a.col = b.col` probe silently skips exactly the pairs the index will reject. Write the probe with `IS NOT DISTINCT FROM` so NULL/NULL compares as equal.
• Revoke default public schema access: `REVOKE ALL ON SCHEMA public FROM public`

Migration Safety

**Core rules:**

• Every schema change is a migration. No ad-hoc DDL in production.
• Migrations are immutable once deployed -- never edit a migration that has run in any shared environment.
• Schema migrations and data migrations are separate files. Schema changes are fast and transactional; data backfills are slow and may need batching.
• Forward-only in production. Rollback = a new forward migration that reverses the change.

**Expand-contract pattern** for zero-downtime renames and removals:

1. **Expand**: add the new column/table, backfill data, update writes to populate both old and new 2. **Migrate**: switch reads to the new column/table, verify in production 3. **Contract**: remove the old column/table in a later deploy

Never rename or remove a column in a single migration -- callers reading the old name will break between deploy and code rollout.

**Dangerous operations:**

• `NOT NULL` without a `DEFAULT` on an existing table locks and rewrites every row. Add the column nullable first, backfill, then add the constraint.
• `CREATE INDEX` (without `CONCURRENTLY`) locks writes for the duration. Always use `CONCURRENTLY`, which cannot run inside a transaction block -- keep it in its own migration.
• Large data backfills: batch with `FOR UPDATE SKIP LOCKED` to avoid locking the entire table:

UPDATE target SET new_col = compute(old_col)
WHERE id IN (
  SELECT id FROM target
  WHERE new_col IS NULL
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
);

Run in a loop until zero rows affected.

**Full-replace clobber on read-modify-write loops.** A migration that loops `SELECT col → mutate in app → UPDATE SET col = new_full_value WHERE id = ?` silently drops concurrent writes that landed between SELECT and UPDATE. Any column written by live traffic is exposed: `jsonb` documents, comma-separated tag fields, denormalized counters, JSON-encoded attribute blobs. Mitigations, in order of preference:

• **In-place atomic update** when the edit is expressible as SQL: `UPDATE t SET col = jsonb_set(col, '{path}', :value) WHERE ...`, or `UPDATE t SET tags = array_append(tags, :tag) WHERE ...` — no read-modify-write window.
• **Row-level lock during the loop:** wrap each iteration in a transaction, `SELECT ... WHERE id = ? FOR UPDATE`, then mutate and write. Cheaper to author, accepts more lock contention.
• **Compare-and-swap retry:** include the original snapshot in `WHERE col = :original_value`, check the affected-row count; on 0, re-read and retry. Robust under contention, requires explicit retry-loop handling.

Default chunked decode-encode loops are only safe during a maintenance window with writes blocked. ORM "chunkById + load + mutate + save" patterns hit this same trap.

Index Strategy

| Type | Use When | |------|----------| | B-tree (default) | Equality, range, sorting, `LIKE 'prefix%'` | | GIN | JSONB (`@>`, `?`, `?&`), arrays, full-text (`tsvector`) | | GiST | Geometry, ranges, full-text (smaller but slower than GIN) | | BRIN | Large tables with natural ordering (timestamps, serial IDs) |

**Index rules:**

• Composite: most selective column first, max 3-4 columns
• Partial: `WHERE status = 'active'` -- smaller, faster
• Covering: `INCLUDE (col)` -- avoids heap lookup
• Expression: `ON (lower(email))` -- for function-based WHERE
• `fillfactor = 70-90` on write-heavy tables -- reserves space for HOT updates, reducing index bloat
• Drop unused indexes (only after one full business cycle since last restart -- check `pg_stat_database.stats_reset` first, otherwise you may drop a primary key on a freshly restarted DB or read replica): `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0`

**Detect unindexed foreign keys:**

SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid