「ソートが遅い…」と思ったらまず疑うべき。PostgreSQLの `work_mem` との付き合い方
現場でデータベースを触っていると、必ず一度は突き当たる壁があります。「最初は速かったクエリが、データが増えるにつれて急激に遅くなる」という現象です。
その犯人の多くは、実はクエリそのものよりも「PostgreSQLがメモリをケチりすぎていること」にあります。特にソート（ORDER BY）やハッシュ結合（JOIN）のパフォーマンスを左右する `work_mem` は、チューニングの登竜門であり、かつ最も事故りやすいポイントの一つ。
今日は、教科書には載っていない「現場の肌感覚」を交えて、この `work_mem` とどう向き合うべきかをお話しします。
—
work_mem が足りないと何が起きるのか？
PostgreSQLは、ソートやハッシュ結合を行う際、まず `work_mem` で指定されたメモリ領域を使おうとします。もしこの領域にデータが収まりきらなくなるとどうなるか？
答えは「一時ファイル（Temp File）の作成」です。
ディスク（SSDであっても）への書き出しが発生した瞬間、クエリの実行速度はガクッと落ちます。いわゆる「ディスクI/Oのボトルネック」です。これを防ぐためには、可能な限りメモリ上で処理を完結させる必要があります。
現場でよくある「work_mem」の罠
多くの初心者がやりがちなのは、「とりあえず `work_mem` をデカくしておけば速くなるだろ！」という勘違いです。
ここが一番の注意点ですが、`work_mem` は「クエリ全体」ではなく「操作ごと」に消費されます。
例えば、1つの複雑なクエリの中に、ソートが3箇所、ハッシュ結合が2箇所あるとします。この場合、PostgreSQLは最大で `work_mem × 5` のメモリを確保しようとします。もし `work_mem` を 1GB に設定し、同時に 50 人のユーザーがそのクエリを叩いたらどうなるでしょう？
`1GB × 5操作 × 50セッション = 25GB`
はい、物理メモリをあっという間に食い潰して OOM Killer（OSのメモリ保護機能）にプロセスを殺されるか、激しいスワップが発生してサーバー全体が応答不能になります。これが「work_mem を上げすぎてサーバーが落ちた」という悲劇の正体です。
—
実践：適切な値をどう導き出すか？
まず、一時ファイルが作成されているかを確認しましょう。PostgreSQLのログ設定で `log_temp_files` を 0 に設定すれば、一時ファイル作成のログがすべて出力されます。
— ログに出力された一時ファイルのサイズを確認し、目安にする
— 数MB程度なら許容範囲ですが、数百MB〜数GBなら要注意です
推奨されるアプローチ
1. 全体をいじらない: `postgresql.conf` でサーバー全体の設定を極端に上げるのは厳禁です。デフォルトの 4MB は今の時代少し小さいですが、せいぜい 16MB〜64MB 程度に留めるのが無難です。
2. クエリ単位でチューニングする: 特定の重いバッチ処理などがあるなら、そのセッションの中だけで `work_mem` を一時的に増やします。これがプロのやり方です。
— 重いバッチ処理の開始時にだけ適用する
BEGIN;
SET LOCAL work_mem = ‘256MB’;
— ここで重いクエリを実行
SELECT FROM large_table ORDER BY created_at DESC;
COMMIT;
こうすれば、他のセッションに悪影響を与えることなく、そのクエリだけを劇的に速くすることができます。
—
まとめ：メモリは「戦略的」に使うもの
`work_mem` のチューニングは、データベースの性格と、そこで動くクエリの性質を理解することから始まります。「なぜこのクエリはメモリを食うのか？」「インデックスで解決できないソートなのか？」を `EXPLAIN ANALYZE` で見てみてください。
今回のポイント:
- `work_mem` 不足は一時ファイルによるI/O待ちを招く。
- 全体設定を上げすぎると、同時実行数によってメモリ不足に陥る。
- 重いクエリがあるなら、`SET LOCAL` でその時だけメモリを奢る。
データベースエンジニアの仕事は、闇雲に数値をいじることではなく、限られたリソースを「一番必要な場所に、必要なだけ」割り当てることです。
まずは皆さんのサーバーのログを眺めてみてください。ひっそりと「一時ファイルを作っているクエリ」が見つかるはずですよ。そこが、パフォーマンスアップの第一歩です。
コメント