2.4その他の性能監視

ここまでのpg_stat_*ビューやEXPLAINは、「今動いているセッション」や「1回のクエリ」を対象にした監視でした。しかし実務では、過去にどのクエリが繰り返し重かったか、サーバーログに何を残すべきかといった、継続的・横断的な監視も欠かせません。この節はその仕上げにあたります。

2.4.1shared_preload_libraries と auto_explain・pg_stat_statements

• shared_preload_libraries＝サーバー起動時に読み込む共有ライブラリ（拡張機能）を指定するパラメータ。auto_explainやpg_stat_statementsのようにサーバー全体で常時フックする必要がある拡張は、ここに列挙してサーバー再起動しないと有効化できない（CREATE EXTENSIONだけでは不十分な拡張がある点に注意）。
• auto_explain＝shared_preload_librariesで読み込む拡張。指定時間を超えたクエリの実行計画を自動的にサーバーログへ出力する（auto_explain.log_min_durationで閾値を設定、auto_explain.log_analyzeでEXPLAIN ANALYZE相当の実測値も含めるかを制御）。毎回手動でEXPLAINを打たなくても、遅いクエリの計画を継続的に捕捉できるのが利点。
• pg_stat_statements＝shared_preload_librariesで読み込む拡張（CREATE EXTENSION pg_stat_statementsでビューも作成）。正規化されたクエリ文字列単位でcalls（実行回数）・total_exec_time（総実行時間）・mean_exec_time（平均実行時間）・rows（総行数）を累積記録する（PostgreSQL 12 では列名が total_time／mean_time。13 で total_exec_time／mean_exec_time に改称）。「どのクエリが累積で最も重いか」をランキングできるのが最大の価値で、単発の遅いクエリを見るEXPLAINや現在のセッションを見るpg_stat_activityとは補完関係にある。

2.4.2ログ関連パラメータ

• log_min_duration_statement＝指定ミリ秒を超えたSQL文をログへ記録するパラメータ（-1で無効・0で全文記録）。auto_explainと似るが、こちらは実行計画ではなくSQL文とその所要時間のみを記録する軽量な方式。log_autovacuum_min_duration＝指定時間を超えたautovacuum実行をログへ記録（autovacuumが実際にどれだけ時間をかけたかの追跡に使う）。
• log_lock_waits＝deadlock_timeoutを超えてロック待ちが続いたセッションをログへ記録（onにすると「長時間ロック待ちしている」ことがログから発見できる。pg_locks/pg_stat_activityのリアルタイム調査を補完する事後追跡の手段）。log_checkpoints＝チェックポイントの発生・所要時間・書き出しバッファ数をログへ記録（頻発や長時間化の兆候をログから追える）。
• log_temp_files＝指定サイズを超える一時ファイル使用をログへ記録（work_memを超えるソート/ハッシュ処理がディスク上に一時ファイルを作った際に検知でき、work_mem不足の兆候を掴む手掛かりになる）。

複数の監視手段を組み合わせた実務フローを整理しましょう。まずサーバー構築時点でpostgresql.confのshared_preload_libraries = 'pg_stat_statements,auto_explain'を設定し、サーバーを再起動しておきます（CREATE EXTENSIONだけを実行してもエラーにはなりませんが、この2つの拡張は起動時フックが必須なため機能しません）。次に、日常的な「累積で重いクエリの発見」にはpg_stat_statementsを使い、SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;のようなクエリで総実行時間上位10件を洗い出します。ここでmean_exec_timeは低いがcallsが極端に多い（＝軽いクエリの大量呼び出しでtotal_exec_timeが積み上がっている）のか、callsは少ないがmean_exec_timeが高い（＝個々が重い）のかを見分けることが対策の分岐点になります。後者のような「個別に重いクエリ」が判明したら、auto_explain.log_min_duration = 1000（1秒超のクエリの計画を自動ログ）を設定しておけば、次回そのクエリが再び遅くなったときに手動でEXPLAINを打たなくても計画がログに残っているため、事後の原因調査が格段に速くなります。より広く「何が起きているか分からない」段階では、log_min_duration_statement = 500（0.5秒超のSQL文をログ）・log_lock_waits = on・log_checkpoints = on・log_temp_files = 0（全一時ファイルを記録）を組み合わせて有効にし、しばらく運用してログを俯瞰します。例えばlog_temp_filesのログに特定のクエリ由来の一時ファイルが頻出していれば、それはそのクエリのソート/ハッシュがwork_memを超えている兆候であり、work_memの引き上げ（G3範囲）や該当クエリのインデックス見直しを検討する材料になります。pg_stat_statementsは「何が重いか」を継続的に見つける道具、auto_explain/各種logパラメータは「発生時に何が起きたか」を後から確認する道具という役割分担で理解しておくと、試験・実務の双方で迷いません。

2.4.3この節のまとめ

• shared_preload_libraries＝サーバー起動時ロード（auto_explain/pg_stat_statementsは登録＋再起動が必須）。pg_stat_statements＝クエリ単位の累積統計・auto_explain＝遅いクエリの計画を自動ログ
• log_min_duration_statement＝遅いSQL文（軽量）・log_autovacuum_min_duration＝遅いautovacuum・log_lock_waits＝ロック待ち検知・log_checkpoints＝チェックポイント記録・log_temp_files＝一時ファイル使用（work_mem不足の兆候）