필사 모드: DB性能チューニング完全ガイド: パラメータを触る前に測定する順序

• はじめに
• 1. チューニングの順序
• 2. ワークロードのプロファイリング — pg_stat_statements
• 3. 待機イベント — ボトルネックの性質を切り分ける
• 4. キャッシュとI/Oの指標
• 5. メモリ予算
• 6. 書き込み経路 — チェックポイントとWAL
• 7. 保守経路 — autovacuum
• 8. 変更の前と後を比較する
• クイズ: 理解度を確認しましょう
• おわりに
• 参考資料
• 関連記事

はじめに

このブログにはPostgreSQL性能チューニングの記事がすでに何本かあります。PostgreSQL性能チューニング実戦ガイド、クエリ最適化と性能チューニング、PostgreSQL 17性能実験室がそれぞれ異なる角度から扱っています。

この記事はパラメータではなく順序を扱います。チューニング記事の問題はたいてい「何を変えられるか」の一覧であることです。一覧は有用ですが、実戦で必要なのはいま何をまず見るべきかです。遅くなったという報告を受けたとき shared_buffers から触るのと、どのクエリが時間を使っているかからまず確認するのとでは、まったく異なる結果になります。

そこでこの記事は診断の順序をたどります。ワークロードのプロファイル → 待機イベント → キャッシュとI/O → そのあとでようやくパラメータ。そして各段階で何を見て何を判断するのかを、ドキュメントに書かれた既定値とともに整理します。

基準エンジンは PostgreSQL 18 であり、既定値はすべてPostgreSQL 18のドキュメントで確認しました。PostgreSQL 18で変わった既定値がいくつかあるのでバージョンを必ず確認してください。

1. チューニングの順序

性能問題に出会ったとき投げる質問の順序がチューニングの半分です。

1. 何が遅いのか — 全体なのか特定のクエリなのか。常になのか特定の時間帯なのか。
2. どこで時間を使っているのか — 実行なのか待機なのか。待機なら何を待っているのか。
3. なぜそうなのか — 計画が悪いのか、データが多いのか、資源が足りないのか、競合なのか。
4. 何を変えるのか — クエリなのかスキーマなのかインデックスなのかパラメータなのか。

パラメータが4番にあるという点が重要です。 パラメータの調整で解決する問題は全体の少数であり、大部分は特定のクエリやインデックスやスキーマの問題です。そしてパラメータはサーバ全体に影響するので、誤って変えると別のワークロードが壊れます。

逆に、パラメータでしか解決しない問題も確かにあります。既定値が小さいサーバを想定して決まっているからです。shared_buffers の既定値は128MB、work_mem は4MB、max_wal_size は1GBです。メモリ256GBのサーバでこの値をそのままにしておけばハードウェアを遊ばせているのと同じです。

2. ワークロードのプロファイリング — pg_stat_statements

最初の段階は「どのクエリが時間を使っているか」です。答えは pg_stat_statements にあります。

導入は二段階です。shared_preload_libraries に登録し(サーバ再起動が必要)、拡張を作ります。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ドキュメント基準の既定値は pg_stat_statements.max が5000、track が top、track_utility がon、track_planning がoff、save がonです。track_planning は計画作成時間を別途記録しますが、ドキュメントが性能上の負担に言及しているので既定値のoffのままにし、必要なときだけ入れるほうが得策です。

読み方が重要です。平均時間ではなく総時間で並べ替えてください。

SELECT calls,
       round(total_exec_time::numeric, 1)          AS total_ms,
       round(mean_exec_time::numeric, 2)           AS mean_ms,
       rows,
       shared_blks_hit, shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

平均200msのクエリが一日100回回るより、平均3msのクエリが500万回回るほうがはるかに大きな負荷です。後者は「遅いクエリ一覧」には絶対に現れません。これがスロークエリログだけでは足りない理由です。

一緒に見るべき列です。

• rows を calls で割った値 — 一回に何行を返すか。この値が大きければアプリケーションが必要以上に取得している可能性があります。
• temp_blks_written — 一時ファイルへの書き込み。0でなければ work_mem が不足して整列やハッシュがディスクにあふれたということです。
• wal_bytes — このクエリが生成したWALの量。書き込み負荷の源を探すときに使います。

測定区間を明確にするには観察開始時点で初期化してください。関数のシグネチャは pg_stat_statements_reset(userid, dbid, queryid, minmax_only) であり、引数なしで呼ぶと全体を初期化します。

3. 待機イベント — ボトルネックの性質を切り分ける

二つ目の段階は「CPUを使っているのか待っているのか」です。これを区別できなければ見当違いのところをチューニングすることになります。

pg_stat_activity の wait_event_type が答えをくれます。ドキュメントが定義した値は次のとおりです。

サンプリングして分布を見るのが要領です。一度撮ったスナップショットは偶然かもしれません。

-- 1秒間隔で繰り返し実行して分布を集める
SELECT coalesce(wait_event_type, 'CPU') AS wait_type,
       wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;

解釈の基本線です。wait_event がNULLの活動中バックエンドは実際にCPUを使っています。この比率が高ければクエリ自体が重いか計画が悪いということであり、パラメータではなくクエリとインデックスを見るべきです。IO が支配的ならキャッシュ不足かストレージの限界です。Lock が支配的なら資源の問題ではなく設計とトランザクション境界の問題です。

Client が多ければデータベースは遊んでいるということです。このときデータベースをチューニングするのは時間の無駄です。

4. キャッシュとI/Oの指標

三つ目の段階は「読み取りがどこから来ているか」です。

-- テーブル別バッファ適中率、読み取りの多い順
SELECT relname,
       heap_blks_read, heap_blks_hit,
       round(100.0 * heap_blks_hit
             / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS heap_hit_pct,
       idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 15;

ここで気をつけるべき落とし穴があります。heap_blks_read は PostgreSQLのバッファプールになくてオペレーティングシステムに要求したブロックです。OSのページキャッシュにあったなら実際のディスクI/Oは発生していません。したがってこの数字だけでディスク負荷を判断すると過大評価になります。

PostgreSQL 16以降の pg_stat_io がより正確な絵をくれます。backend_type、object、context 別に reads、hits、evictions、fsyncs を分けて見せます。

SELECT backend_type, object, context,
       reads, hits, evictions, fsyncs
FROM pg_stat_io
WHERE reads > 0 OR evictions > 0
ORDER BY reads DESC
LIMIT 20;

context が bulkread や vacuum の項目が大きいのは正常です。大量スキャンとVACUUMはもともとバッファプールを汚さないよう制限されたリングバッファを使います。問題は context が normal なのに evictions が大きい場合です。 作業集合が shared_buffers に収まらずバッファを押し出し続けているという意味です。

テーブルのアクセスパターンも一緒に見ます。

SELECT relname, seq_scan, seq_tup_read, idx_scan,
       n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 15;

seq_scan が大きいからといって無条件に悪いわけではありません。小さいテーブルはシーケンシャルスキャンが正解です。見るべきは seq_tup_read を seq_scan で割った値、つまりシーケンシャルスキャン一回で何行読むかです。この値が大きく idx_scan が小さければインデックスが必要な候補です。

5. メモリ予算

いよいよパラメータです。三つのメモリパラメータが互いに異なる目的を持ちます。

shared_buffers — PostgreSQL自身のバッファプール。既定値128MB。ドキュメントの指針は「専用のデータベースサーバに1GB以上のRAMがあるなら shared_buffers の合理的な開始値はシステムメモリの25%である」であり、「PostgreSQLはオペレーティングシステムのキャッシュにも依存するので、RAMの40%を超えて割り当てることがより少ない量より良い可能性は低い」とされます。変更にはサーバの再起動が必要です。

work_mem — 整列とハッシュの作業領域。既定値4MB。この値の計算がもっとも頻繁に間違えられます。ドキュメントの原文はこうです。「複雑な問い合わせは複数の整列とハッシュ演算を同時に実行することがあり、各演算は一般に一時ファイルへ書き始める前にこの値が指定する分のメモリを使える。また複数のセッションがそのような演算を同時に実行しうる。したがって使用される総メモリは work_mem の値の何倍にもなりうるので、値を選ぶときはこの事実を念頭に置く必要がある。」

つまりコネクション数×work_mem は最悪を過小評価した値です。 ハッシュ系は hash_mem_multiplier(既定値2.0)まで掛けられます。安全なやり方は全域の値を保守的に置き、重い分析クエリでのみセッション単位で上げることです。

-- このセッションだけで、このクエリのために
SET LOCAL work_mem = '256MB';

maintenance_work_mem — 保守作業のメモリ。既定値64MB。ドキュメントはこの値を「VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY のような保守作業が使う最大メモリ」と定義します。同時に回る保守作業の数は少ないので work_mem よりはるかに大きく取っても安全です。

effective_cache_size — メモリを割り当てずプランナの仮定だけを変えます。既定値4GB。オペレーティングシステムのキャッシュを含む利用可能キャッシュの推定値であり、実際より小さいとプランナがインデックススキャンを過小評価します。

PostgreSQL 18でI/O関連の既定値が変わりました。effective_io_concurrency の既定値が 16 であり、新しく導入された io_method の既定値は worker、io_combine_limit の既定値は128kBです。以前のバージョンから上がってきたならこれらの項目を必ずドキュメントで確認してください。

6. 書き込み経路 — チェックポイントとWAL

書き込みが遅かったり周期的に遅延が跳ねたりするならチェックポイントを疑います。

ドキュメント基準の既定値です。checkpoint_timeout は5分、checkpoint_completion_target は0.9、max_wal_size は1GB、min_wal_size は80MBです。

症状と原因の対応はこうです。周期的に(数十秒間隔で)遅延が跳ねるなら max_wal_size が小さくてチェックポイントが時間ではなくWAL量で誘発されている可能性が高いです。 確認は log_checkpoints を入れてログでチェックポイントの理由を見ることです。time の代わりに xlog が理由として記録されればそうです。

ALTER SYSTEM SET log_checkpoints = on;
SELECT pg_reload_conf();

max_wal_size を増やせばチェックポイントの頻度が減り遅延のスパイクが緩和されます。代償はクラッシュ復旧時間が長くなることとディスク使用量が増えることです。この二つは事業要件(RTO)と一緒に決めるべきです。

synchronous_commit の既定値は on です。コミットのたびにWALがディスクに安全に記録されるまで待つという意味です。この値を切れば書き込みスループットは大きく増えますが、クラッシュ時に最後の数件のコミットが消えることがあります。 データの整合性そのものは壊れませんが、コミットしたと応答したトランザクションがなくなりえます。この取引を受け入れられるかはドメインが決めます。セッションやトランザクション単位で切り替えられるので、監査ログのような一部のテーブルの書き込みにだけ緩和する方式も可能です。

wal_compression の既定値は off です。入れると全ページイメージを圧縮してWAL量を減らしますがCPUを使います。レプリケーションの帯域がボトルネックなら検討する価値があります。

commit_delay の既定値は0、commit_siblings の既定値は5です。同時コミットが非常に多い環境でグループコミットの効果を狙うつまみですが、誤って使うと遅延だけ増えます。他の項目をすべて確認してから手を付けてください。

7. 保守経路 — autovacuum

遅くなる原因がbloatである場合が思ったより多いのです。autovacuumが追いつかなければ死んだ行が積み上がり、シーケンシャルスキャンが読むべきページが増え、インデックスが大きくなります。

ドキュメント基準の既定値です。autovacuum はon、autovacuum_max_workers は3、autovacuum_naptime は1分、autovacuum_vacuum_threshold は50タプル、autovacuum_vacuum_scale_factor は0.2(テーブルの20%)、autovacuum_analyze_threshold は50タプル、autovacuum_analyze_scale_factor は0.1(10%)、autovacuum_vacuum_cost_delay は2ミリ秒、autovacuum_freeze_max_age は2億トランザクションです。

PostgreSQL 18には autovacuum_vacuum_max_threshold が追加され既定値は1億タプルです。この上限のおかげで非常に大きなテーブルでもvacuumが無期限に先送りされません。

大きいテーブルで既定のscale factorが問題になります。 1億行のテーブルなら20%は2千万行です。それだけ死んではじめてvacuumが回ります。テーブル単位で下げるのが標準的な対応です。

-- 大きいテーブルは比率ではなく絶対量で管理する
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

この設定はテーブル格納パラメータの変更なので SHARE UPDATE EXCLUSIVE ロックしか取りません。読み取りも書き込みも塞ぎません。

autovacuumが回っているのに追いつかないなら速度制限を見ます。autovacuum_vacuum_cost_delay の既定値2ミリ秒はvacuumがサービスに与える影響を減らすための値ですが、書き込みの多いシステムではこれのせいでvacuumが永遠に追いつけません。autovacuum_max_workers を増やすのも方法ですが、ワーカー数を増やしても全体のコスト上限はワーカーどうしで分け合うので、遅延値の調整と一緒に見る必要があります。

進行状況はこうやって確認します。

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

警告: bloatがすでに深刻だからといって VACUUM FULL や CLUSTER を稼働中に実行してはいけません。二つのコマンドはどちらも ACCESS EXCLUSIVE ロックを取って読み取りまで遮断し、テーブル全体を新しく書くので元のサイズ分の空きディスクが必要です。PostgreSQLのドキュメント自体が「管理者は標準の VACUUM を使うよう努め、VACUUM FULL は避けるべきである」と勧告しています。インデックスだけが問題なら REINDEX INDEX CONCURRENTLY が安全な代替です。

8. 変更の前と後を比較する

最後の段階でありもっとも頻繁に省略される段階です。比較のない変更はチューニングではなく推測です。

手順を整理するとこうなります。

第1段階 — 基準線を記録します。 変更の直前に pg_stat_statements を初期化し、一定時間(最低でも一回の業務サイクル)観察した結果を保存します。待機イベントの分布と pg_stat_io のスナップショットも一緒に残します。

第2段階 — 一度に一つだけ変えます。 三つを同時に変えるとどれが効果だったのか分からず、悪くなったとき何を戻すべきかも分かりません。

第3段階 — 戻す方法をまず確認します。 ALTER SYSTEM で変えた値は ALTER SYSTEM RESET で戻します。再起動が必要なパラメータかもあらかじめ確認してください。shared_buffers は再起動が必要で work_mem は不要です。

-- 変更
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 確認: どこから来た値か
SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'max_wal_size',
               'effective_cache_size', 'random_page_cost');

-- 戻す
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

pg_settings の source 列は値がどこから来たのかを教えてくれます。設定ファイルを直したのに反映されない問題の大半がここで解けます。pending_restart がtrueなら再起動しなければ適用されません。

第4段階 — 同じ指標でもう一度測ります。 第1段階と同じクエリ、同じ観察期間でなければなりません。そして改善したかどうかを一つの代表指標で決めておいてください。上位20クエリの total_exec_time の合計、p95応答時間、毎秒処理件数のどれか一つで十分です。

第5段階 — 決定を記録します。 なぜ変えたのか、何が根拠だったのか、どれだけ改善したのかを残します。6か月後に「この値はなぜこうなっているのか」という質問が必ず出ます。

クイズ: 理解度を確認しましょう

おわりに

性能チューニングでもっとも大きな無駄は、誤ったパラメータではなく測定なしに始めることです。どのクエリが時間を使っているか分からない状態で shared_buffers を二倍に増やしても、良くなったのか悪くなったのかすら判断できません。

順序をもう一度整理します。pg_stat_statements で負荷の出どころを探し、待機イベントでボトルネックの性質を切り分け、キャッシュとI/Oの指標で資源の状況を確認し、そのあとでパラメータを一つずつ変えながら毎回測定します。この順序を守ればチューニングは技芸ではなく手順になり、手順はチームに伝授できます。

そして最後に、バージョンを確認してください。 PostgreSQL 18で effective_io_concurrency の既定値が16になり、io_method が新しくでき、EXPLAIN ANALYZE がバッファ情報を自動的に含むようになりました。インターネットに流れているチューニング値の半分は数年前のバージョン基準です。

この記事の診断クエリはPostgresプレイグラウンドで直接実行してみることができます。

参考資料

• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (2026-08-15 確認)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (2026-08-15 確認)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (2026-08-15 確認)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (2026-08-15 確認)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (2026-08-15 確認)
• PostgreSQL 18, pg_stat_statements: https://www.postgresql.org/docs/18/pgstatstatements.html (2026-08-15 確認)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (2026-08-15 確認)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 確認)

関連記事

• 前の記事: データモデリング完全ガイド — 論理モデルから物理モデルまで
• 最初の記事へ: PostgreSQLインデックス完全ガイド — インデックスのライフサイクル
• SQL実行計画完全ガイド — オプティマイザが計画を選ぶ過程
• PostgreSQL性能チューニング実戦ガイド — パラメータのカタログ
• PostgreSQL VACUUMとMVCCの内部構造 — bloatの原理
• Postgresプレイグラウンド — 診断クエリの実行