PostgreSQL gotchas for app developers
summary
Covers concrete PostgreSQL pitfalls: connection pools multiplying into 5,000 connections and why pools are often too large; never grant superuser to the app and disable trust auth; JSON is for truly unstructured fields not for replacing columns; migrations that loop can turn into 400 million queries instead of a single UPDATE; pre‑v12 CTE optimization barrier can bork plans; ORMs causing N+1 queries and over‑selects, use array binding and JSON aggregates, prefer pushing work into the database and avoid per‑row loops.
AI-written summary · about
chapters (23)
- 0:00Intro
- 0:17PostgreSQL Gotchas for App Developers
- 3:00So what's a gotcha?
- 3:39So, let's get started?
- 4:32Connection pooler
- 5:36Pooler issues
- 8:16Speaking of users
- 8:29Superuser
- 10:22Trust authentication
- 11:20JSON
- 14:39Migrations
- 23:05Representative data
- 25:36Generating data
- 28:53ORM overselect
- 30:35Simple table example
- 33:47Avoiding loops
- 34:33Forced joins
- 35:44Collecting aggregates
- 39:02What about structure
- 41:23Avoiding ORM loops Acting on multiple objects
- 44:08Binding arrays
- 45:33Deadlocks
- 47:55Thank you!
related talks
47mPostgreSQL worst practices
41mApplication Development Challenges with Postgres
36mPostgreSQL worst practices
42mPostgreSQL Tips & Tricks For App Devs
46mAwesome SQL – tips and tricks for application developers
01h 00mThe 10 Deadly PostgreSQL Sins
29m10 Features your Developers are missing when 'Stuck' with proprietary database!
30mPuny to Powerful PostgreSQL Rails Apps