arib-postgres

name	arib-postgres
argument-hint	[review <path> | index | tune]
description	Stack | PostgreSQL optimization & safety patterns — indexing, query plans, N+1, safe online migrations, connection pooling, JSONB, and Row-Level Security for multi-tenancy. Use when designing or reviewing Postgres-backed data access. Composes with database-guardian (migration safety) and performance (query budgets). Authored natively (MIT), not an ECC copy.

/arib-postgres — PostgreSQL optimization & safety

A reference + checklist for Postgres-backed services. Authored content (the ECC postgres-patterns graft was unsourceable here); composes with database-guardian (migration safety verdicts) and performance (latency budgets). For a full pass, dispatch them via /arib-build.

1. Indexing — the highest-leverage lever

• Index the columns in your WHERE, JOIN, and ORDER BY — but measure with EXPLAIN (ANALYZE, BUFFERS), don't guess. A seq-scan on a hot path is the usual culprit; an unused index is dead write-cost.
• Composite index column order matters: most-selective / equality columns first, range last ((tenant_id, created_at) not the reverse).
• Partial indexes for skewed predicates (WHERE deleted_at IS NULL).
• Foreign keys are not auto-indexed in Postgres — index the FK column or joins crawl.
• Drop indexes that pg_stat_user_indexes shows unused; each one taxes every write.

2. Query plans & the N+1 trap

• Read EXPLAIN ANALYZE: watch for Seq Scan on large tables, Nested Loop over big row counts, and a planner row-estimate far from actual (stale stats → ANALYZE).
• N+1: one query per row in a loop. Fix with a JOIN, IN (...), or a single set-based query. This is the #1 ORM-induced Postgres problem — audit every per-row query.
• Prefer set-based SQL over row-by-row procedural logic; let the planner work.
• Keyset/seek pagination (WHERE id > $last) over OFFSET for deep pages.

3. Safe online migrations (forward-only, lock-aware)

• Lock awareness is the whole game on a live DB: a naive ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT ... or an un-CONCURRENTLY index build can take an ACCESS EXCLUSIVE lock and stall the app. Use CREATE INDEX CONCURRENTLY; add nullable column → backfill in batches → set NOT NULL with a validated check constraint.
• Expand → migrate → contract for breaking changes (add new, dual-write/backfill, switch reads, drop old) so a rollback is always available.
• Set a short lock_timeout / statement_timeout on migration sessions so a blocked DDL fails fast instead of pile-up.
• Migrations are forward-only and reversible. Schema changes are a high-stakes class — route through database-guardian and hold the merge for a human (CONSTRAINTS #17).

4. Connection pooling & concurrency

• App pools exhaust Postgres max_connections fast under load — front with PgBouncer (transaction pooling) for serverless / high fan-out; size the app pool deliberately.
• Transaction pooling breaks session-level features (prepared statements, SET, advisory locks held across statements) — know the mode before relying on them.
• Keep transactions short; never hold one open across an external HTTP call.

5. JSONB, and when NOT to use it

• JSONB is great for genuinely schemaless/variable attributes; index access paths with a GIN index (jsonb_path_ops for containment). It is not a substitute for columns you query and constrain — those want real typed columns with FKs and checks.

6. Multi-tenancy & Row-Level Security

• For shared-schema multi-tenancy, Row-Level Security (RLS) is the defense-in-depth layer: a policy on tenant_id enforced by the database, so an app-layer bug can't leak across tenants. Pair it with the app guard (/arib-nestjs §5) — belt and suspenders.
• Set the tenant via a session GUC (SET LOCAL app.tenant_id) inside the request transaction; the policy reads it. Verify the policy with a cross-tenant test.
• Tenant isolation is a high-stakes class — its changes hold merge for a human.

7. Integrity & reliability

• Constraints in the DB, not just the app: NOT NULL, CHECK, UNIQUE, FKs with the right ON DELETE (cascade vs restrict — a deliberate decision, audited by database-guardian).
• Make money exact: NUMERIC, never float. Store timestamps as timestamptz (UTC).
• Idempotency keys for at-least-once external calls; unique partial index enforces them.

Output

For review <path>: per-section PASS / NEEDS-CHANGES → io/ledger/postgres-review-<date>.md. For index/tune: the concrete DDL + the EXPLAIN before/after that justifies it (no unverified index churn — show the plan).