What I Wish Someone Told Me About Postgres

Author: Hazel Bachrach | Published: 2024-11-11 | Generated: 2026-08-19 | Domain: challahscript.com
Tags: ‘#postgres’ ‘#sql’ ‘#databases’ ‘#performance’ ‘#psql’ ‘#schema-design’

---

TLDR

Postgres’s documentation is excellent but immense—roughly 3,200 US-letter PDF pages for version 17—so this guide distills the operational lessons most useful to web developers. Normalize schemas by default, follow PostgreSQL’s type and naming conventions, and understand SQL’s three-valued NULL logic. Performance and reliability depend on examining actual query plans, designing indexes around query patterns, avoiding lock-queue incidents during migrations, keeping transactions short, and using JSONB only when its flexibility outweighs its weaker statistics and schema guarantees.

Key Takeaways

• Normalize first, denormalize deliberately: Store relationships with foreign keys instead of duplicating values such as user emails across document rows. Denormalization can speed reads or precompute aggregates, but introduces consistency risks and more complex writes.
• SQL and NULL have non-obvious semantics: SQL keywords are case-insensitive, but NULL represents “unknown”: NULL = NULL evaluates to NULL, not true. Use IS NULL, IS NOT NULL, IS [NOT] DISTINCT FROM, and COALESCE; ordinary WHERE predicates only retain rows whose condition is explicitly true.
• Make psql usable for investigation: Configure a pager such as less -S, enable expanded output with \x, display nulls explicitly via \pset null '[NULL]', use Tab completion, inspect schemas with \d, open an editor with \e, and export local CSVs with \copy ... CSV HEADER.
• An index is not a guarantee of speed: Postgres chooses between indexes and sequential scans using table statistics, so indexes may not be selected for small local datasets. Use EXPLAIN; multicolumn B-tree indexes favor their leading column—an (a, b) index does not replace an index on b—and prefix LIKE 'prefix%' queries may need text_pattern_ops.
• Locks and transactions can cascade into outages: Even a long-running SELECT holding ACCESS SHARE can block an ALTER TABLE requiring ACCESS EXCLUSIVE; later reads may queue behind that pending migration and time out. Keep transactions short because locks acquired inside them persist until COMMIT, and plan risky migrations such as type changes, uniqueness constraints, or non-constant defaults.
• JSONB is powerful but costly: JSONB can be indexed and queried, but lacks the planner statistics and self-documenting schema of regular columns; equivalent queries have been demonstrated as up to 2,000× slower. JSONB comparisons require JSON-compatible values—e.g., data['brand'] = '"JanSport"'—or text extraction via data->>'brand'.

Images & Media

• Postgres lock queue diagram — Illustrates how a slow SELECT, followed by an ALTER TABLE, can cause subsequent compatible reads to queue behind the exclusive-lock request.

Referenced Links

• PostgreSQL “Don’t Do This” — Official guidance on common Postgres schema, text, timestamp, and naming mistakes.
• PostgreSQL query planning with EXPLAIN — Official documentation for reading query execution plans.
• Reading an EXPLAIN ANALYZE query plan — Practical introduction to interpreting Postgres planner output.
• PostgreSQL explicit locking — Lock modes, conflicts, and table-lock behavior.
• Migrations and exclusive locks — Real-world explanation of migration-induced lock queues.
• When to avoid JSONB in a PostgreSQL schema — Benchmark and discussion of JSONB performance drawbacks.