PostgreSQL anti-patterns

The recurring Postgres mistakes list — the field guide this vault’s Kurs administracji PostgreSQL lane (63 lessons + “100 problemów z PostgreSQL”) deserves as a one-screen summary. Seed entry: 0xlelouch_ (tweet captured, 2026-10-19), annotated with vault kin where a lesson already exists.

The top 10 (each with the why)

1. SELECT * in hot paths — 20 columns pulled to render 2: extra TOAST reads, more network, worse cache hit.
2. No EXPLAIN (ANALYZE, BUFFERS) — guessing at plans instead of measuring scans vs seeks. Vault lane: Things I want to dive deeper names EXPLAIN ANALYZE the first labs-session candidate.
3. Missing the right index — filtering on (user_id, created_at) but indexing only user_id, then sorting 5M rows.
4. Wrong index shape — composite order backwards; no partial index for WHERE deleted_at IS NULL; no covering index for common queries.
5. OFFSET pagination — page 500 reads-and-skips 50k rows; use keyset (created_at, id) instead.
6. Long transactions — “idle in transaction” holds locks, bloats tables, and VACUUM can’t clean dead tuples under it. (Course lane: checkpointy/WAL lessons.)
7. Untuned autovacuum — defaults + write-heavy tables = bloat, slow reads, surprise disk growth.
8. No connection pooling — 500 pods × 20 conns = 10k backends: context-switch storm, memory burn. (Course lane: HAProxy + PgBouncer lessons already captured.)
9. Weak retry logic — retrying everything on timeouts without idempotency keys + unique constraints = duplicated writes. (Kin: Change Data Capture’s outbox pattern — the durable answer to exactly-once-shaped questions.)
10. No observability — no pg_stat_statements, no slow-query logs, no p95 latency by query fingerprint. (Kin: Open source APM — Traceway’s OTLP lane; the USE-method instinct.)

The meta-pattern: every item is measure-before-shape (2, 10) or shape-matches-access (1, 3, 4, 5) or lifecycle-discipline (6, 7, 9) — the same three families as any storage engine’s failure modes.

Growing

• Cross-map each item to its jsystems course lesson (PL) where one exists — items 6 and 8 already have them.