WIP Overview of PostgreSQL query macroanalysis tools
In this issue, I'm going to collect info about various tools, libraries, and methodologies of Postgres query macroanalysis (analysis of the workload consisting of many queries running in parallel). I'm not sure what form we'll use to publish it – it might be a blog post, whitepaper, or smth else (like a slide deck for a conference talk). The first goal is to gather as much good material as possible, to have the best overview for Postgres ecosystem.
What I have right now:
Log-based analysis
Log-based analysis is a basic approach available everywhere. Benefits: ... Downsides: ...
- log_min_duration_statement – PostgreSQL parameter that allows logging queries with duration of execution exceeding the specified threshold. Available everywhere. Only successful queries are present (failed queries can be logged separately but, unfortunately, without duration).
- pgBadger (repository) – the most popular tool for Postgres log analysis, contains 50+ various reports.
Log-based analysis | |
---|---|
log_min_duration_statement | |
pgBadger | |
auto_explain |
Incremental statistics | |
---|---|
pg_stat_statements | |
pg_stat_kcache | |
pg_qualstats | |
postgres-checkup | |
pgCenter | |
bg_mon | https://github.com/CyberDem0n/bg_mon |
pg_stat_monitor |
Wait event sampling | |
---|---|
PASH Viewer | |
pgCenter | |
pgsentinel | |
pg_wait_sampling |
Monitoring / exporters | |
---|---|
postgres_exporter for Prometheus | |
pgwatch2 | |
PMM | |
pgmetrics | https://pgmetrics.io/ |
Edited by Nikolay Samokhvalov