Postgres Performance for Humans
summary
Targets concrete metrics and actions: aim for 99% cache hit and ~95% index hit rate, use pg_stat_statements to find the queries that consume the most time, and fix a 295 ms query by adding an index to drop it to ~1.7 ms. Enable extensions like jsonb in 9.4 or hstore with GIN indexes, create indexes concurrently in production, use pgbouncer past ~200 connections, prefer physical backups with wally/barman at scale, and add RAM as the simplest performance lever.
AI-written summary · about
chapters (16)
- 0:00Introduction to Postgres
- 1:11The history of the project
- 2:13Setup and configuration
- 4:50Optimizing cache and memory
- 6:11Index basics
- 7:16Tooling and utilities
- 8:21How data is retrieved
- 9:52Query analysis with EXPLAIN
- 12:35Monitoring with pg_stat_statements
- 14:20Advanced index types
- 17:29Functional and conditional indexes
- 20:21Hstore and JSON support
- 23:39Connection pooling
- 24:55Replication strategies
- 26:37Backups and recovery
- 28:56Recap and conclusion
related talks
43mPostgres Performance: From Slow to Pro
27mPostgres Performance in 15 Minutes
50mImproving Postgres' Efficiency
41mOptimizing your app by understanding your PostgreSQL database
45mProper PostgreSQL Parameters to Prevent Poor Performance
34mPostgres 10, Performance, and You
56mPerformance Improvements in PostgreSQL9 2
50mPerformance Archaeology