Chapter 36 — Exercises

Mostly conceptual/judgment (a survey chapter); some pgvector/PostGIS sketches. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Time-series

36.1 What characterizes time-series data (ingestion, query, retention)? Name two dedicated tools and the PostgreSQL extension. (answer in Appendix)

36.2 Write a time-bucketed aggregation (avg per 5 minutes over the last day) with date_trunc or time_bucket.

36.3 ⭐ Why does plain PostgreSQL + partitioning (Ch. 25) handle moderate time-series, and what does TimescaleDB add?

Group B — Vectors / AI

36.4 What is an embedding, and what does vector (nearest-neighbor) search find that keyword search doesn't? (answer in Appendix)

36.5 Name three AI features powered by vector search (incl. RAG). (answer in Appendix)

36.6 Sketch using pgvector: add a vector column, store embeddings, and query the 5 nearest to a query embedding.

36.7 ⭐ When would you choose a dedicated vector DB (Pinecone) over pgvector?

36.8 Why can't a plain numeric lat/long + B-tree index efficiently answer "within 5 km"? What does PostGIS provide? (answer in Appendix)

36.9 When is Elasticsearch warranted over PostgreSQL full-text search (Ch. 16)?

Group D — Theme #4

36.10 For each specialized need (time-series, vector, spatial, search), name the PostgreSQL extension that often suffices. (answer in Appendix)

36.11 ⭐ Why is "try the PostgreSQL extension first" good default advice? What does adopting a dedicated specialized DB cost?

Group E — Progressive project

36.12 Identify any specialized-shaped data in your domain (time-series? geographic? semantically-searchable text?).

36.13 For each, name the PostgreSQL extension (or built-in) that could handle it; decide whether a dedicated DB is justified.

36.14 ⭐ Sketch an AI feature (semantic search or recommendations) for your domain using pgvector + embeddings.

Self-check. If you can recognize specialized data shapes, explain embeddings/vector search, and map each need to a PostgreSQL extension (trying it before a dedicated system) — you have the judgment. Next: tying it all together.