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 queriesSlowest 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を入れて確認した。

参考