Chapter 15 — Key Takeaways

The big idea

Database objects let you define logic once and reuse it: views, materialized views, functions, procedures, and triggers. Each has a clear best use — and a clear trap.

Views (virtual tables)

  • A named query; stores no data, always current, no performance magic (inlined + optimized with your query).
  • Three wins: simplification (hide joins), security (expose a subset — Ch. 32), abstraction (stable interface; refactor tables underneath).
  • Simple views are updatable; WITH CHECK OPTION blocks writes outside the view's WHERE.

Materialized views (cached results)

  • Store the query's results → fast reads on expensive, slowly-changing queries.
  • Stale until refreshed: REFRESH MATERIALIZED VIEW [CONCURRENTLY] mv. CONCURRENTLY avoids read downtime but needs a unique index.
  • A matview is a cache — you must refresh it (schedule it!) or it lies. Surface the data's age. (Case Study 1: fast-and-stale.)
  • View vs. matview is a freshness requirement decision.

Functions & procedures

  • Functions (SQL or PL/pgSQL) return values, usable in queries; encapsulate business rules in one place. Mark IMMUTABLE/STABLE/VOLATILE accurately.
  • Procedures (CALL) perform actions and can manage transactions (commit/rollback inside).

Triggers (automatic logic)

  • Run a function automatically BEFORE/AFTER INSERT/UPDATE/DELETE; NEW/OLD = post/pre-change row.
  • Good uses: audit trails, derived/denormalized columns, complex cross-row rules, INSTEAD OF for writable complex views.
  • Traps: they're invisible (document them!) and run per row inside the transaction (keep them lean; push heavy/external work to async jobs). Prefer constraints for rules constraints can express. (Case Study 2: the invisible trigger.)

Choosing the tool

Need Tool
Live abstraction / hide a join / restrict columns View
Fast reads on an expensive, slowly-changing aggregate Materialized view (+ scheduled refresh)
Reusable calculation/business rule Function
Multi-step action with transaction control Procedure
Automatic audit / derived column on change Trigger (lean, documented)
A simple data rule Constraint (CHECK/FK), not a trigger

You can now…

  • ☐ Create/use views (and updatable views with CHECK OPTION).
  • ☐ Build and refresh materialized views; reason about staleness.
  • ☐ Write SQL/PL-pgSQL functions and call procedures.
  • ☐ Write triggers for auditing/derived data — sparingly and documented.
  • ☐ Pick the right object for a given need.

Looking ahead

Chapter 16 — Advanced SQL. PostgreSQL's superpowers — JSONB, full-text search, arrays, and more — the features that often eliminate the need for a second database (theme #4), and the finale of Part II.

One sentence to carry forward: Views abstract, materialized views cache (so refresh them), functions reuse logic, and triggers automate invisibly — so document triggers, keep them lean, and prefer constraints when they suffice.