PostgreSQL Design Patterns
summary
Shows concrete patterns and working SQL: use a timestamp range column (generated from start/end) plus an exclusion constraint (GiST + btree_gist) to prevent double-booking; use INT4RANGE for age buckets and JSONB + GIN for flexible filters; add PostGIS geometry (SRID 4326) and st_dwithin for 2 km radius searches; enforce single-active subscription with a partial unique index on status='active'; implement a reliable task queue using SELECT ... FOR UPDATE SKIP LOCKED with a partial index on not_processed and idempotency via unique idempotency_key + ON CONFLICT DO NOTHING.
AI-written summary · about
chapters (11)
- 0:00Introduction & Postgres design patterns overview
- 0:10Creative engineering & real-world context
- 2:14Modelling physical events (time & venue)
- 2:38Range types for time overlaps
- 4:19Generated columns & exclusion constraints
- 6:41Age ranges, GDPR & smart filtering
- 8:08Arrays & JSON for flexible filtering
- 11:16Adding geolocation with PostGIS
- 13:36Distance queries & geo indexing
- 16:06Enforcing business rules with partial indexes
- 18:19Task queues, idempotency & safe payments
related talks
42mPostgreSQL Tips & Tricks For App Devs
22mImplementing Slowly Changing Dimensions in Postgres
50mRange Types in PostgreSQL 9.2 - Your Life Will Never Be the Same
48mAdvanced Postgres Schema Design (For Scalable Application Development) bySehrope Sarkuni
53mEfficient Time Series with PostgreSQL
30mLesser known features of PostgreSQL
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
23mImplementing Temporal Features in PostgreSQL: SQL Standard and Beyond