What I wish someone told me about Postgres

Original Article ↗ Hacker News Discussion ↗

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.