Query Logging and Workload Analysis
summary
Argues that collecting slow queries is not enough and that PostgreSQL 9.2’s pg_stat_statements and related tooling make real workload analysis practical: switch to syslog/CIS log, try PG Badger or pgFouine for parsing, and capture normalized statements so trends and counts are meaningful. Shows a painful real example where lowering log_min_duration_statement from 2s to 500ms explodes I/O and logging overhead, needs big RAM to process logs (example requiring ~64GB), and demonstrates a pg_stat_plans prototype that fingerprints plans, supports realtime five‑minute snapshots, and runs with single‑digit percent overhead. pgFouine PG Badger pg_stat_plans
AI-written summary · about
chapters (10)
- 0:00The state of query logging
- 3:38Limitations of current tools
- 5:31Operational challenges
- 10:20The need for real-time data
- 14:34PG stat statements explained
- 21:06Query fingerprinting
- 27:37Visualizing workload data
- 33:23Evolution of database tools
- 38:45PG stat plans and regression
- 50:01Conclusion and future outlook
related talks
47mIdentifying Slow Queries and Fixing Them!
50mPostgreSQL Query Performance Insights
51mPostgreSQL Query Performance Insights
25mPoint-in-time query tuning and observability with pg_stat_statements
23mBest Practices for Tuning Slow Postgres Queries
49mLeveraging pgBadger for Effective PostgreSQL Troubleshooting
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
26mBe Good Friends with Query Planner