Optimizing your app by understanding your PostgreSQL database
summary
Uses Postgres internals and concrete queries to find why an app is slow: check buffer cache hit ratio (aim for >95–99%), inspect bloat and autovacuum (recommend autovacuum at ~2% for large tables), and identify unused indexes. Run pg_stat_statements, query pg_stat_user_tables and index stats, then use EXPLAIN ANALYZE and multi‑column CREATE STATISTICS to fix misestimates (one user sees ~1000× speedup). Mentions tools for IO counts, tuple counts, and practical tuning steps; sponsored by Citus Data.
AI-written summary · about
chapters (18)
- 0:00Start
- 2:39Challenge: Relating issues to the database
- 3:15Solution: Using Postgres' statistics
- 4:51Problem: My application is slow
- 5:24Cache hit ratio
- 6:22How do I measure?
- 8:38Digging further
- 18:30Uses for pg_stat_user_tables
- 21:35Back to cache hit rate
- 23:07Generic vs specific optimizations
- 23:54What is pg_stat_statements ?
- 25:40Examples
- 28:31EXPLAIN ANALYZE and tune them!
- 30:02Interesting things about EXPLAIN
- 33:28CREATE STATISTICS
- 34:03Example of impact
- 34:40Summary of specific query optimization
- 38:11Approximate count
related talks
23mBest Practices for Tuning Slow Postgres Queries
43mOptimize Python & Django apps with Postgres superpowers
47mIdentifying Slow Queries and Fixing Them!
39mDeep dive into PostgreSQL internal statistics
50mImproving Postgres' Efficiency
51mPostgreSQL: Practical Examples of SQL Query Optimization
31mPostgres Performance for Humans
34mSimple and Awesome Database Tricks