PostgreSQL Tips & Tricks For App Devs
summary
Practical recipes for making PostgreSQL replace extra services: TSVector + GIN index turns a prototype full‑text scan from ~25 ms into ~200 µs for ranked search with weights, JSONB and text arrays store keywords and manufacturers for fast indexed filters, PostGIS finds nearby venues and intersects polygons for engineer zones, queue semantics use FOR UPDATE SKIP LOCKED for multiworker task processing, partial unique indexes enforce one active subscription, writable CTEs and lateral joins simplify invoicing and latest‑row queries, arrays store 49 half‑hour readings to cut row overhead and a custom aggregate returns the last value for interpolation.
AI-written summary · about
chapters (9)
- 0:00Introduction and speaker background
- 4:21Full text search in PostgreSQL
- 10:25Storing flexible data with JSONB
- 12:54Geospatial queries with PostGIS
- 18:00Handling dynamic data with arrays
- 22:15Preventing duplicate subscriptions
- 24:46Building a message queue in PostgreSQL
- 27:55Energy data analysis with arrays
- 34:10Q&A: Lateral joins and TTL
related talks
34mSimple and Awesome Database Tricks
33mPostgres, the Best Tool You're Already Using
46mUse PostgreSQL, not Rails, to make Rails Faster!
25mPostgreSQL performance tips you have never seen before
45mSELECT 'amazing_features' FROM "postgresql"
49mSELECT 'amazing features' FROM "postgresql"
50mImproving Postgres' Efficiency
24mPostgreSQL Design Patterns