Chapter 16 — Key Takeaways

The big idea

PostgreSQL's advanced features let one database do the work of several (theme #4) — JSONB for documents, full-text search for search, arrays/ranges for specialized data. They complement good relational design (theme #1); they don't excuse skipping it. And they need the right index to be fast.

JSONB (document flexibility, in a relational DB)

  • Prefer jsonb over json (parsed/binary, faster, indexable).
  • Query: -> (as jsonb), ->> (as text), #>/#>> (path), @> (contains — indexable), ? (key exists).
  • Index with GIN for @> queries: CREATE INDEX ... USING GIN (attributes).
  • Use for genuinely variable/sparse data; keep universal, queried, related fields as real typed columns. (Case Study 1: one JSONB column vs. 40 nullable ones — and vs. MongoDB/EAV.)

Full-text search (real search, no Elasticsearch needed)

  • to_tsvector (normalize: lowercase, stem, drop stop words) @@ to_tsquery/websearch_to_tsquery; ts_rank for relevance.
  • GIN-index a stored/generated tsvector column for speed.
  • Beats LIKE '%x%' (which can't index a leading wildcard, stem, or rank). (Case Study 2.)
  • Outgrow it only for massive scale / advanced relevance / faceting (Chapter 36).

Arrays, ranges, and more

  • Arrays: = ANY(arr), @>, &&, unnest — for small, read-whole lists; prefer a junction table for heavily-queried values.
  • Range types + exclusion constraints: prevent overlapping bookings (EXCLUDE USING gist (... WITH &&)).
  • generate_series: build date spines / generate rows.
  • Regex (~, regexp_replace) and LATERAL (per-row subqueries, e.g., top-N-per-group).

The constant caveat

These features are only fast with the right index — GIN (JSONB, full-text, arrays), GiST (ranges). Without it, full scans (Chapter 23). And they're a complement to relational design, not a substitute (don't model relational data as documents — the Lumen mistake, Ch. 1).

Judgment (theme #4)

Reach for PostgreSQL's features first; add a separate database only for a concrete need it genuinely can't meet (Part VI / Chapter 37). One consistent system beats many drifting ones.

You can now…

  • ☐ Store, query (@>, ->>), and GIN-index JSONB.
  • ☐ Implement ranked, stemmed full-text search.
  • ☐ Use arrays, ranges/exclusion constraints, generate_series, regex, and LATERAL.
  • ☐ Judge when PostgreSQL replaces a second database — and when it doesn't.

Looking ahead — Part III

SQL Mastery is complete. But your SQL is only as good as the schema beneath it. Chapter 17 — Entity-Relationship Modeling opens Part III (Database Design), where theme #1 — design is the most important skill — takes center stage.

One sentence to carry forward: PostgreSQL's JSONB and full-text search often save you a second database — but only when you index them and use them to complement, not replace, sound relational design.