pgbadger
pgbadgerは、PostgreSQLのログを集計して分析するツールである。 ログに出力されたSQLを正規化してまとめ、合計時間や実行回数の多いクエリを一覧にする。
pgbadgerのインストール
Ubuntuの場合はaptでインストールできる。
$ sudo apt install pgbadger
$ pgbadger --version
pgBadger version 12.4
PostgreSQLのスロークエリを出力する設定
pgbadgerで集計するには、PostgreSQLのログにSQLを出力しておく。
Ubuntuでは/etc/postgresql/[バージョン]/main/conf.d/に設定ファイルを置く。
log_min_duration_statement = 0
lc_messages = 'C'
log_min_duration_statement: 指定したミリ秒以上かかったSQLをログに出力する。0ならすべてのSQLを出力するlc_messages: ログのメッセージの言語。pgbadgerは英語のメッセージしか解析できないため、Cかen_US.UTF-8にする
参考: 【PostgreSQL】log_min_duration_statementでスロークエリをログに出力する
log_statementは有効にしない。
log_statement = 'all'にすると、log_min_duration_statementのログにSQLが出力されず、クエリごとの実行時間を集計できない。
設定を反映するには、PostgreSQLの設定を再読み込みする。
$ sudo pg_ctlcluster 16 main reload
上記の設定では、/var/log/postgresql/postgresql-16-main.logに次のようなログが出力される。
2026-11-22 14:31:15.830 UTC [5367] postgres@bench LOG: duration: 0.510 ms statement: SELECT name FROM users WHERE id = 53;
log_line_prefixはUbuntuのデフォルト値%m [%p] %q%u@%d のままでよい。
pgbadgerが標準で対応している形式のため、オプションの指定は不要である。
独自のlog_line_prefixを使う場合は、--prefixオプションで同じ値を渡す。
プレースホルダー付きのSQLで大きなデータを渡すと、パラメータの値がすべてログに出力され、ログファイルが大きくなる。
log_parameter_max_lengthで、ログに出力するパラメータの長さを制限できる。
参考: 【PostgreSQL】log_parameter_max_lengthでログに出るパラメータの長さを制限する
pgbadgerでスロークエリを集計する
pgbadgerでログを集計するには以下のコマンドを実行する。
ログファイルはpostgresユーザーとadmグループしか読めないため、sudoを付ける。
$ sudo pgbadger /var/log/postgresql/postgresql-16-main.log
デフォルトでは、カレントディレクトリにHTML形式のレポートout.htmlを出力する。
ブラウザで開くと、グラフ付きで結果を確認できる。
ターミナルで確認する場合は、-x textでテキスト形式を指定する。
-o -を指定すると、ファイルではなく標準出力に出力する。
$ sudo pgbadger -x text -o - /var/log/postgresql/postgresql-16-main.log
以下は、usersテーブルとordersテーブルに対してpgbenchで負荷をかけたときのログを集計した結果である。
- Global information ---------------------------------------------------
Generated on Sun Nov 22 14:31:47 2026
Log file: /var/log/postgresql/postgresql-16-main.log
Parsed 26,305 log entries in 2s
Log start from 2026-11-22 14:31:15 to 2026-11-22 14:31:45
- Overall statistics ---------------------------------------------------
Number of unique normalized queries: 3
Number of queries: 9,008
Total query duration: 7s592ms
First query: 2026-11-22 14:31:15
Last query: 2026-11-22 14:31:45
Query peak: 326 queries/s at 2026-11-22 14:31:39
.
.
.
- Queries that took up the most time (N) -------------------------------
Rank Total duration Times executed Min/Max/Avg duration (s) Query
1) 5s539ms - 342 - 10ms/51ms/16ms - select count(*) from orders where user_id = ?;
--
- Example 1: 51ms - SELECT count(*) FROM orders WHERE user_id = 486;
- Example 2: 36ms - SELECT count(*) FROM orders WHERE user_id = 976;
- Example 3: 35ms - SELECT count(*) FROM orders WHERE user_id = 690;
2) 1s220ms - 1,694 - 0ms/21ms/0ms - insert into orders (user_id, amount) values (?, ?);
--
- Example 1: 21ms - INSERT INTO orders (user_id, amount) VALUES (725, 100);
- Example 2: 20ms - INSERT INTO orders (user_id, amount) VALUES (225, 100);
- Example 3: 20ms - INSERT INTO orders (user_id, amount) VALUES (417, 100);
3) 832ms - 6,972 - 0ms/8ms/0ms - select name from users where id = ?;
--
- Example 1: 8ms - SELECT name FROM users WHERE id = 296;
- Example 2: 3ms - SELECT name FROM users WHERE id = 292;
- Example 3: 3ms - SELECT name FROM users WHERE id = 291;
- Most frequent queries (N) --------------------------------------------
Rank Times executed Total duration Min/Max/Avg duration (s) Query
1) 6,972 - 832ms - 0ms/8ms/0ms - select name from users where id = ?;
--
Example 1: 8ms - SELECT name FROM users WHERE id = 296;
Example 2: 3ms - SELECT name FROM users WHERE id = 292;
Example 3: 3ms - SELECT name FROM users WHERE id = 291;
.
.
.
pgbadgerの集計結果のみかた
以下の見出しはテキスト形式での名前である。 HTML形式のレポートでは、次の名前で表示される。
| テキスト形式 | HTML形式 |
|---|---|
Queries that took up the most time (N) | Time consuming queries (N) |
Most frequent queries (N) | Most frequent queries (N) |
Slowest queries (N) | Normalized slowest queries |
Slowest queries | Slowest individual queries |
Overall statistics
ログ全体の統計である。
Number of queriesはクエリの総数、Total query durationはクエリの実行時間の合計を表す。
Number of unique normalized queriesは、値を?に置き換えて同じ形にまとめたクエリの種類数を表す。
Queries that took up the most time
実行時間の合計が長い順にクエリを並べた一覧。 最初に確認する一覧であり、上位のクエリから改善を検討する。
各行の項目は以下のとおり。
Total duration: 実行時間の合計Times executed: 実行回数Min/Max/Avg duration: 1回あたりの実行時間の最小・最大・平均
Total durationが長いクエリは、Times executedもあわせて確認する。
Times executedが多い場合は、そのクエリの実行回数を減らすと改善が期待できる。
Times executedが少ない場合は、1回あたりの実行時間を短縮すると改善が期待できる。
上記の例では、1位のselect count(*) from orders where user_id = ?は342回と回数が少なく、平均16msかかっている。
1回あたりの実行時間を短くするのが効果的である。
Most frequent queries
実行回数が多い順にクエリを並べた一覧。 想像よりも回数の多いクエリがある場合、N+1問題が発生している可能性がある。
上記の例では、select name from users where id = ?が6,972回実行されている。
1回あたりの実行時間は短くても、ループの中で1件ずつ取得している可能性がある。
Slowest queries
1回あたりの実行時間が長い順の一覧。
Slowest queries (N)は平均の長い順、Slowest queriesは1回ごとの実行時間の長い順に並ぶ。
平均は短いのに最大だけ長いクエリは、ロック待ちなどで一時的に遅くなった可能性がある。
Example
各クエリの下のExampleには、実行時間の長かったSQLが表示されている。
SQLの先頭にEXPLAINをつけて実行し、インデックスが使われているか確認する。
bench=# EXPLAIN SELECT count(*) FROM orders WHERE user_id = 486;
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize Aggregate (cost=6810.72..6810.73 rows=1 width=8)
-> Gather (cost=6810.51..6810.72 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=5810.51..5810.52 rows=1 width=8)
-> Parallel Seq Scan on orders (cost=0.00..5809.99 rows=207 width=0)
Filter: (user_id = 486)
(6 rows)
Seq Scanは、テーブル全体を読む処理を表す。
orders.user_idにインデックスを作成して同じ負荷をかけ直すと、1位だったクエリは3位に下がった。
1) 1s794ms - 1,759 - 0ms/15ms/1ms - insert into orders (user_id, amount) values (?, ?);
2) 1s159ms - 6,759 - 0ms/8ms/0ms - select name from users where id = ?;
3) 107ms - 350 - 0ms/6ms/0ms - select count(*) from orders where user_id = ?;
繰り返し集計する場合の注意
PostgreSQLのログはファイルに追記されていく。 検証環境で集計し直す場合は、負荷をかける前にログファイルを空にする。 ログファイルを空にすると、今回の負荷で実行したクエリだけを集計できる。
$ sudo truncate -c -s 0 /var/log/postgresql/postgresql-16-main.log
Ubuntu 24.04では、-cを付けないtruncateはrootでもPermission deniedになる。
参考: 【Linux】rootでもtruncateがPermission deniedになる原因 (fs.protected_regular)
pt-query-digestとの違い
MySQLのスロークエリログを集計する場合はpt-query-digest を使う。 pt-query-digestはMySQLのスロークエリログを、pgbadgerはPostgreSQLのログを対象にする。 どちらもクエリを正規化して、実行時間の合計や回数でランキングにする。
動作確認した環境
Ubuntu 24.04のDockerコンテナに、aptでPostgreSQL 16とpgbadger 12.4を入れて確認した。
