Chapter 16 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "JSON Types" and "JSON Functions and Operators." The complete JSONB reference — operators, containment, path queries, jsonb_path_query, and indexing. https://www.postgresql.org/docs/current/datatype-json.html
  • PostgreSQL Docs: "Full Text Search." A whole chapter — tsvector/tsquery, dictionaries/stemming, ranking, websearch_to_tsquery, and GIN/GiST indexing. The authoritative guide for Case Study 2.
  • PostgreSQL Docs: "Arrays," "Range Types," "Pattern Matching" (regex), and "LATERAL." The other superpowers, each with examples.

JSONB in practice (💻 Developer)

  • "When to use JSONB in PostgreSQL" articles. The column-vs-JSONB line (Case Study 1) — and the warning against modeling relational data as documents (ties to Chapter 1's Lumen case).
  • GIN indexing for JSONB deep dives — jsonb_path_ops vs default jsonb_ops, and what each accelerates (preview of Chapter 23).

Full-text search (📊 Analyst · 💻 Developer)

  • "PostgreSQL full-text search" tutorials. Building a search feature end to end: generated tsvector column, GIN index, ranking, highlighting (ts_headline).
  • pg_trgm extension for fuzzy/typo-tolerant matching and accelerating LIKE/ILIKE with trigram indexes — a useful complement to FTS.

Specialized types (🔬 CS Student · 🏗️ DBA)

  • Range types + exclusion constraints writeups — the elegant "no overlapping reservations" pattern.
  • generate_series recipes — date spines, gap-filling reports, test-data generation (it powers this book's generate_data.sql).
  • LATERAL joins — "top N per group" and correlated FROM-clause subqueries (ties to Chapters 9 and 12).

When to go beyond PostgreSQL (forward to Part VI)

  • Chapter 33 (NoSQL), Chapter 36 (specialized DBs), Chapter 37 (the database decision) of this book — the honest treatment of when a document store, search engine, or vector database genuinely beats PostgreSQL.

Reference (this book)

  • Appendix C — SQL Quick Reference and Appendix I — SQL Cookbook: JSONB/FTS/array recipes.
  • Appendix D — Data Types: jsonb, arrays, ranges, tsvector.

Do, don't just read

  • Query Mercado's products.attributes with ->> and @>; add a GIN index and compare EXPLAIN (after Chapter 23).
  • Build a real search on product name/description with a generated tsvector + GIN, and compare results and speed to ILIKE '%...%'.
  • Write the column-vs-JSONB rule for your own project and apply it.

Next: Chapter 17 — Entity-Relationship Modeling — Part III begins: designing the schema your SQL deserves.