In this lesson, you will learn how to leverage PostgreSQL logs to pinpoint exactly which queries are consuming the most time and resources in your cluster.
The Foundation: log_min_duration_statement
While tools like pg_stat_statements provide a high-level summary of performance, actual logs are the "ground truth" for debugging spikes or intermittent issues. To capture slow queries, you must first configure PostgreSQL to record them. By setting log_min_duration_statement in your postgresql.conf (e.g., to 250ms), any query that exceeds that duration will be written to your log file, complete with its execution time and the actual query text.
Analyzing Logs with pgbadger
Manually scanning thousands of lines of text is inefficient. The industry-standard tool for this task is pgBadger. It acts as a log analyzer, parsing your raw PostgreSQL logs to generate a comprehensive HTML report.
When you run pgbadger against your log file, it creates a visual dashboard categorized by:
- Top 20 most frequent queries: Useful for spotting N+1 problems.
- Top 20 slowest queries: Identifies those that are "expensive" in terms of total duration.
- Query group analysis: Shows which patterns are consuming the most cumulative time.
For example, a typical command to process your logs looks like this:
pgbadger /var/lib/postgresql/data/log/postgresql.log -o report.html
Interpreting the Findings
Once you have the report, focus on the "Slowest Queries" section. Because you previously studied Bloat-Induced Plan Degradation and Custom Operator Costing, you are uniquely qualified to interpret these logs. When you find a query that is consistently slow, look for:
- High execution counts: Even a "fast" query can kill performance if it runs 10,000 times a second.
- Outliers: If a query has a high "max time" but a low "average time," it may be suffering from parameter sniffing or plan changes due to fluctuating data distribution.
By combining log analysis with the execution plans you’ve learned to tune, you can move from reactive firefighting to proactive database health management.
Key Takeaway: Use log_min_duration_statement to capture slow execution patterns and leverage pgbadger to aggregate these into actionable insights for query optimization.