work_memという名の「諸刃の剣」と、その適正解を求めて
PostgreSQLのチューニングにおいて、`work_mem`ほどエンジニアを悩ませ、かつ奥が深いパラメータはないかもしれません。
「とりあえず値を大きくしておけばソートが速くなるだろう」という安易な期待は、往々にして深刻なメモリ枯渇とOOM Killerの咆哮を招きます。逆に、ケチりすぎて一時ファイル（Temp File）の書き出しが頻発すれば、I/O待ちでクエリは泥沼化する。
今回は、この「諸刃の剣」をどう制御し、PostgreSQLのパフォーマンスを極限まで引き出すか。その内部アーキテクチャに触れながら、少し踏み込んだ話をしましょう。
1. なぜ「一時ファイル」は我々の敵なのか
まず、前提を共有しておきましょう。`work_mem`は、クエリ内のソート操作（ORDER BY, DISTINCT）やハッシュ結合（Hash Join）のために割り当てられる「メモリの作業領域」です。
重要なのは、このメモリは「クエリ単位」ではなく「操作単位」で割り当てられるという点です。
例えば、1つのクエリの中に3つのソートノードがあり、`work_mem`を100MBに設定していたとします。理論上、そのクエリは最大で300MB（あるいはそれ以上）のメモリを消費します。さらに、並列クエリ（Parallel Query）が走れば、ワーカープロセスごとにこのメモリが消費される。
ここを理解していないと、コネクション数が増えた瞬間にサーバー全体がスワップアウトを起こし、システムが停止する。現場で「メモリは余っているはずなのに！」と叫ぶエンジニアが陥る典型的な罠です。
2. 「ログを信じる」：最適化の第一歩
最適化の最初のアプローチは、推測ではなく観測です。PostgreSQLのログ設定に以下を加えることを強く推奨します。
log_temp_files = 0
これを有効にしておくと、一時ファイルが生成された瞬間にそのサイズがログに記録されます。これが「最適化の宝の地図」です。
- 1MB程度の一時ファイル: 無視していいレベルです。OSのキャッシュが効きます。
- 数百MB〜GB単位の一時ファイル: ここがボトルネックです。明らかに`work_mem`が不足しています。
ここで注意すべきは、すべてのクエリを救おうとしないことです。頻繁に走るクリティカルなクエリには手厚く、たまにしか走らない巨大なバッチ処理には別の戦略を。`work_mem`をグローバルで大きくしすぎないのが、熟練者の鉄則です。
3. 動的な最適化：SET文の活用
特定の重いクエリに対してだけ`work_mem`を増やしたい場合、私たちはセッション単位で値を変更する手法をとります。
BEGIN;
SET LOCAL work_mem = ‘256MB’;
SELECT … — ここで重いソートや結合を行う
COMMIT;
アプリケーション側でこのハンドリングを行うのは少し手間ですが、サーバー全体のメモリ安全性を守りつつ、パフォーマンスを劇的に向上させる最も安全な手段です。
4. 内部アーキテクチャから見た「ハッシュ結合」の最適化
`work_mem`を語る上で避けて通れないのが、Hash Joinの挙動です。PostgreSQLのオプティマイザは、統計情報から「ハッシュテーブルをメモリに収められるか」を予測します。
もし`work_mem`が不足してハッシュテーブルがメモリに収まりきらないと、PostgreSQLはハッシュバケットを分割する「マルチパス・ハッシュ結合」へと切り替わります。これが始まると性能は急降下します。
もし実行計画（EXPLAIN ANALYZE）を見て `Batches: 2` や `Batches: 8` といった表示が出ていたら、それは「メモリが足りなくて計算を分割しました」という悲鳴です。この場合、そのクエリに対してのみ`work_mem`を増やすか、あるいはインデックスを整備してそもそもソートやハッシュ結合を回避する道を検討すべきです。
最後に：銀の弾丸はない
結論として、`work_mem`に魔法の数字は存在しません。
1. まずは `log_temp_files` で現状を把握する。
2. 実行計画の `Batches` 数を監視する。
3. グローバル値は控えめに、セッション値で調整する。
結局のところ、データベースのチューニングとは、マシンの物理的な制約という現実と、クエリの複雑さという理想の間の「妥協点」を探る作業です。
皆さんのPostgreSQLが、今日もI/Oの海に溺れることなく、メモリの上を軽快に駆け抜けることを願っています。次は、`effective_cache_size` との微妙な距離感について話しましょうか。
それでは、良いクエリライフを。
コメント