What I wish someone told me about Postgres

Original Article ↗ Hacker News Discussion ↗

Practical lessons for working with PostgreSQL dominate the exchange, from understanding normalization, NULL semantics, indexing, and MVCC to avoiding overuse of JSONB for schema design. Commenters highlight the value—and limits—of tools like LLMs, formatters, and enhanced CLIs, debate style conventions such as ALL-CAPS SQL and case handling, and share war stories about autovacuum, performance traps, and time zone types. A recurring theme is that while many pitfalls are not unique to Postgres, learning the relational model properly and reading the (sometimes arcane) docs pays off far more than relying on ORMs or opaque abstractions.

Use of LLMs for SQL

  • Several commenters praise LLMs (ChatGPT/Claude) for generating and refactoring SQL, especially complex queries and business logic in Postgres.
  • Others warn about IP exposure when sharing schema, and about hallucinations and weak optimization advice; they argue you still need to learn SQL fundamentals and query planning.

Documentation, Learning Resources, and Syntax Diagrams

  • Some find official Postgres docs hard to read due to formal syntax notation; others point out they do include examples and that learning the notation pays off.
  • SQLite’s syntax diagrams (“railroad diagrams”) are frequently cited as an ideal, though they can be out of sync with code.
  • Books like PostgreSQL Administration Cookbook and introductory tutorials are recommended.

SQL Style, Capitalization, and Tooling

  • Strong debate over ALL-CAPS keywords vs lowercase vs “sentence case.”
  • Pro-CAPS: easier visual pattern matching, especially without color, and for color-blind users.
  • Anti-CAPS: harder to read word shapes, particularly for dyslexic readers; most languages don’t need caps.
  • Consensus: pick a consistent style, enforce via prettifiers/linters (sqlfluff, pgFormatter, IDE formatters).
  • Many mention editor/IDE support: JetBrains “language injection,” VS Code, Vim tricks; CLI enhancements like pgcli and pspg are praised.

JSON/JSONB and Normalization

  • Strong sentiment that developers overuse JSONB and neglect normalization.
  • Others note valid uses: audit storage of third‑party API responses; sum types; user-defined extra fields; transient or low-criticality state; better than opaque TEXT blobs.
  • Rule of thumb repeated: “normalize as much as possible, then denormalize for performance.”
  • Discussion touches on normalization forms (mostly 3NF or BCNF in practice) and extreme 6NF/EAV patterns, with skepticism about practicality.

NULL Semantics and Constraints

  • NULL handling is flagged as non-intuitive, especially in unique constraints and composite keys.
  • Postgres-specific note: since v15, unique constraints can control NULL behavior with NULLS [NOT] DISTINCT.
  • JSON null vs SQL NULL difference is highlighted as a common newcomer trap.

Time Zones and timestamptz

  • Lengthy argument about how Postgres stores timestamptz (as an instant / microseconds since epoch) vs how docs describe it (“in UTC”).
  • Concern that misleading documentation and driver mappings cause real bugs, especially around future timezone rule changes.
  • Some suggest separating timestamp and timezone columns, or using generated columns to recompute UTC values.

Operations, Performance, and “Don’t Do This”

  • Autovacuum and regular vacuuming are emphasized; failure to vacuum at scale can cause severe outages.
  • Skewed statistics can mislead the planner into ignoring indexes; manual workarounds (rewriting queries, rebuilding indexes) are sometimes needed.
  • Advice surfaces to avoid views-on-views and to read Postgres’s “Don’t Do This” wiki and SQL anti-patterns literature.

Multi-Tenancy and Schema Design

  • For multi-tenant apps, some recommend putting tenant IDs on every row, or even using separate databases per tenant; trade-offs discussed around migrations, reporting, and performance isolation.
  • Some report success with per-tenant databases plus automation for schema updates.

Article/UX Feedback

  • Multiple users report mobile issues: code blocks and tables hard to scroll; sidebar TOC overlaps content.
  • Suggestions include using sticky positioning and fixing horizontal scroll behavior.