--- name: postgres-strict description: > PostgreSQL strictness, schema design, indexing, migration safety, and operational rules. Use when designing schemas, writing queries, reviewing migrations, tuning performance, or hardening a Postgres deployment. Targets PostgreSQL 16-18, with notes on pgvector, partitioning, and RLS. Pairs with security-audit-standard and performance-audit-standard. --- # PostgreSQL Strict Standard Rules for production Postgres. Targets 16+ with notes for 17/18 features. ## Version Targets PostgreSQL 18 is GA (released Sept 2025). New deployments should target 18. Highlights worth designing around: - **Async I/O subsystem (AIO)**: sequential scans, bitmap heap scans, and `VACUUM` issue concurrent reads instead of blocking each one. Up to ~3x faster on bulk-read workloads. No code change required. - **Skip scan on B-tree indexes**: multicolumn indexes can now be used when the leading column is not in the predicate, reducing the need for redundant index variants. - **Parallel GIN index builds**: `CREATE INDEX ... USING gin` runs in parallel. Big win for JSONB and full-text builds on large tables. - **`uuidv7()` native**: no extension required (see PG-26 below). - **Virtual generated columns are the default**: stored columns require explicit `STORED`. Virtual columns compute on read, no write amplification. - **`OLD` and `NEW` in `RETURNING`**: capture the row state before and after `UPDATE`/`DELETE`/`MERGE` in one statement. - **OAuth 2.0 authentication in libpq**: easier SSO integration. - **Planner stats survive major version upgrades**: post-upgrade clusters reach steady-state performance much faster, no immediate `ANALYZE` storm. ## CRITICAL: Migration Safety A migration is a contract with a running system. The wrong migration takes prod down. ### PG-01: `CREATE INDEX CONCURRENTLY` on hot tables ```sql -- BAD: takes ACCESS EXCLUSIVE lock for the duration of the build CREATE INDEX events_user_id_idx ON events (user_id); -- GOOD: short ACCESS EXCLUSIVE on metadata, no row lock during build CREATE INDEX CONCURRENTLY events_user_id_idx ON events (user_id); ``` `CONCURRENTLY` cannot run inside a transaction block. Migration tools that wrap statements in BEGIN/COMMIT must opt out for these statements. Failed concurrent index builds leave an INVALID index, drop and retry. ### PG-02: Add NOT NULL columns in two steps ```sql -- BAD: rewrites the whole table while holding ACCESS EXCLUSIVE ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active'; -- GOOD on PG 11+ for defaults, but still problematic if you need NOT NULL on existing nulls ALTER TABLE users ADD COLUMN status text DEFAULT 'active'; -- Backfill in batches in app code UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN $1 AND $2; ALTER TABLE users ALTER COLUMN status SET NOT NULL; ``` PG 11+ added fast non-volatile defaults. The lock duration for `SET NOT NULL` still scans the table, schedule it for a quiet window or use `NOT VALID` constraint pattern below. ### PG-03: Add CHECK / FOREIGN KEY as `NOT VALID`, then `VALIDATE` ```sql ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID; -- Validates only new rows, completes instantly ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive; -- Scans the table without an ACCESS EXCLUSIVE lock ``` Same pattern for foreign keys. ### PG-04: Set `lock_timeout` and `statement_timeout` in migrations ```sql SET lock_timeout = '5s'; SET statement_timeout = '10min'; ``` A migration that waits forever on a lock is worse than a migration that fails fast. ## CRITICAL: Indexing ### PG-05: Index every column used in `WHERE`, `JOIN`, or `ORDER BY` on hot queries Use `EXPLAIN (ANALYZE, BUFFERS)` to verify plan. A `Seq Scan` on a million rows is not always wrong, but on a hot query it almost always is. ### PG-06: Composite index column order: equality first, then range ```sql -- Query: WHERE user_id = $1 AND created_at > $2 ORDER BY created_at DESC CREATE INDEX events_user_created_idx ON events (user_id, created_at DESC); ``` Equality on the leading column lets Postgres use the index for the range and the sort. ### PG-07: Partial indexes for skewed predicates ```sql -- Most rows are processed, only a few are pending CREATE INDEX jobs_pending_idx ON jobs (created_at) WHERE status = 'pending'; ``` Smaller index, faster scans for the common query. ### PG-08: GIN for JSONB / array / full text ```sql -- For @>, ?, ?| queries on JSONB CREATE INDEX docs_data_gin ON docs USING gin (data jsonb_path_ops); -- For arrays: WHERE tags @> ARRAY['urgent'] CREATE INDEX items_tags_gin ON items USING gin (tags); ``` `jsonb_path_ops` is smaller and faster than the default opclass when you only need containment. ### PG-09: HNSW (not IVFFlat) for new pgvector indexes ```sql CREATE EXTENSION IF NOT EXISTS vector; CREATE INDEX docs_embedding_hnsw ON docs USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64); -- Per-query recall/speed tradeoff SET hnsw.ef_search = 100; ``` HNSW is the default recommendation in pgvector 0.5+. IVFFlat needs retraining as data changes. ## HIGH: Query Patterns ### PG-10: Never `SELECT *` in application queries Selects every column even when only a few are needed. Breaks when columns are added (extra bandwidth, broken row decoders). List columns explicitly. ### PG-11: Parameterized queries always ```typescript // BAD: SQL injection db.query(`SELECT * FROM users WHERE email = '${email}'`); // GOOD: parameterized db.query("SELECT id, name FROM users WHERE email = $1", [email]); ``` Every driver supports `$1, $2, ...` placeholders. Use them. ### PG-12: `LIMIT` every unbounded query ```sql -- BAD: a single misclick returns 50M rows SELECT id, payload FROM events WHERE user_id = $1; -- GOOD: paginate SELECT id, payload FROM events WHERE user_id = $1 ORDER BY id LIMIT 100; ``` For pagination at scale, use keyset (`WHERE id > $last_id`), not `OFFSET`. ### PG-13: `MERGE` for upserts (PG 15+, with `RETURNING` in 17+) ```sql MERGE INTO users u USING (VALUES ($1, $2)) AS s(email, name) ON u.email = s.email WHEN MATCHED THEN UPDATE SET name = s.name WHEN NOT MATCHED THEN INSERT (email, name) VALUES (s.email, s.name) RETURNING u.id; -- PG 17+ ``` `INSERT ... ON CONFLICT DO UPDATE` still works and is fine for simple upserts. ### PG-14: `EXPLAIN (ANALYZE, BUFFERS)` before declaring a query fast ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... ; ``` `BUFFERS` shows how much you read from cache vs. disk. A query that is fast in dev with a hot cache can be slow in prod with a cold one. ## HIGH: Transactions ### PG-15: Default isolation level is `READ COMMITTED`, raise it deliberately | Level | When | |---|---| | READ COMMITTED | Default, fine for most CRUD | | REPEATABLE READ | Reports, batch jobs that need a stable view | | SERIALIZABLE | Money transfers, balance updates, anything where lost updates corrupt state | `SERIALIZABLE` can fail with `40001 serialization_failure`. Application must retry. ### PG-16: Lock rows you intend to update with `FOR UPDATE` ```sql BEGIN; SELECT balance FROM accounts WHERE id = $1 FOR UPDATE; -- compute new balance UPDATE accounts SET balance = $2 WHERE id = $1; COMMIT; ``` Without `FOR UPDATE`, two concurrent transactions both read, both compute, last writer wins. ### PG-17: Keep transactions short Every open transaction holds locks and prevents `VACUUM` from cleaning rows newer than its snapshot. Long-running transactions cause table bloat. No HTTP calls, no user-facing waits inside a transaction. ## HIGH: Connection Management ### PG-18: PgBouncer in transaction pooling mode for most apps Postgres connections are heavyweight (10-20 MB each, fork per backend). A serverless or busy app saturates the server without a pooler. | Pool mode | When | |---|---| | Session | Need session-state features (advisory locks, prepared statements, `SET`) | | Transaction | Default for stateless apps | | Statement | Rare, breaks transactions | Transaction pooling forbids `LISTEN/NOTIFY`, session advisory locks, and certain `SET` calls in client code. Use `SET LOCAL` inside transactions instead. ### PG-19: Set per-role timeouts ```sql ALTER ROLE app_user SET statement_timeout = '30s'; ALTER ROLE app_user SET lock_timeout = '5s'; ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s'; ``` A runaway query should never take down the database. ## HIGH: Security ### PG-20: `scram-sha-256` for password auth, never `md5` ```ini # postgresql.conf password_encryption = scram-sha-256 # pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256 ``` Rotate any md5-hashed passwords. They are downgrade-attackable. ### PG-21: TLS required for non-loopback connections ```ini # postgresql.conf ssl = on ssl_min_protocol_version = 'TLSv1.2' ``` Reject `host` (plain) lines in `pg_hba.conf` for anything but `127.0.0.1` / Unix sockets. ### PG-22: Least-privilege roles, no app-as-superuser ```sql CREATE ROLE app_readwrite; GRANT CONNECT ON DATABASE mydb TO app_readwrite; GRANT USAGE ON SCHEMA public TO app_readwrite; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite; CREATE ROLE app_user LOGIN PASSWORD '...' IN ROLE app_readwrite; ``` Migration role is separate and more privileged. Application role cannot DROP, ALTER, or CREATE. ### PG-23: Row-Level Security for multi-tenant tables ```sql ALTER TABLE projects ENABLE ROW LEVEL SECURITY; CREATE POLICY projects_tenant_isolation ON projects USING (tenant_id = current_setting('app.tenant_id')::uuid); -- Per request SET LOCAL app.tenant_id = '...'; ``` Defense in depth. Even an SQL injection that bypasses application checks cannot leak across tenants. ## MEDIUM: Observability ### PG-24: `pg_stat_statements` always enabled ```ini shared_preload_libraries = 'pg_stat_statements' ``` ```sql SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` Reveals the slow queries actually hitting the database. Read this before adding any optimization. ### PG-25: Track these baseline metrics | Metric | Source | Alert if | |---|---|---| | Replication lag | `pg_stat_replication.replay_lag` | > 10s sustained | | Connection count | `pg_stat_activity` | > 80% of `max_connections` | | Cache hit ratio | `pg_stat_database.blks_hit / (blks_hit + blks_read)` | < 0.99 | | Bloat | `pgstattuple` or pgbloat scripts | dead tuples > 20% of table | | Long transactions | `pg_stat_activity` where `xact_start < now() - interval '5min'` | any | ## MEDIUM: Schema Design ### PG-26: UUIDv7 for new PKs (PG 18 native, otherwise extension) ```sql -- PG 18+ CREATE TABLE events (id uuid PRIMARY KEY DEFAULT uuidv7(), ...); -- PG <18: use uuid-ossp or app-side generation CREATE TABLE events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), ...); ``` UUIDv7 is time-ordered, gives B-tree-friendly inserts and works across services without coordination. ### PG-27: Use `timestamptz`, never `timestamp` ```sql created_at timestamptz NOT NULL DEFAULT now() ``` `timestamp without time zone` discards offset and silently corrupts data when the server timezone changes. ### PG-28: Partition large append-only tables ```sql CREATE TABLE events ( id bigserial, created_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); ``` Drop a month by detaching a partition. `VACUUM` and indexes scale per-partition. ## Migration Tooling Baseline Required regardless of tool (sqlx, Atlas, Flyway, Prisma Migrate): - One forward migration per file, no destructive `DROP` without confirmation - File names sortable (`0001_`, `0002_`, or timestamps) - Migrations checked in, never edited after merge - Tool runs on a dedicated migration role, not the app role - CI runs migrations against a fresh DB on every PR ## Vulnerability Checklist - [ ] All connections use TLS (`ssl = on`) - [ ] Password auth is `scram-sha-256`, no `md5` - [ ] App role is not superuser, cannot DROP/ALTER - [ ] `pg_stat_statements` enabled and reviewed - [ ] `lock_timeout` and `statement_timeout` set per role - [ ] Long transactions monitored and alerted - [ ] Backups: PITR or daily full + WAL archive - [ ] Restore tested at least quarterly - [ ] Index-creating migrations use `CONCURRENTLY` - [ ] Multi-tenant tables use Row-Level Security - [ ] Connection pooler (PgBouncer) sits between app and Postgres