【実務・中級編】 temp_buffersの最適化 – PostgreSQL

「遅いな…」と思ったらここを見ろ。PostgreSQLの `temp_buffers` チューニングで一時処理を爆速にする方法

現場でPostgreSQLを触っていると、必ずと言っていいほどぶつかる壁がある。「なぜか複雑なJOINや集計処理だけが異常に遅い」というやつだ。

クエリプランナを確認して「よし、インデックスは効いているな」と思っても改善しない。そんな時、ベテランはまず `work_mem` を疑うけれど、実は `temp_buffers` がボトルネックになっているケースも少なくないんだ。

今日は、意外と見落とされがちなこの設定値について、現場の知見を交えて解説するよ。

—

`temp_buffers` って何者？

簡単に言うと、「一時テーブルや一時インデックスを構築するためのメモリ領域」だ。

PostgreSQLでは、巨大なデータの並べ替えや、複雑な集計を行う際、ディスクに書き出す前にメモリ上で処理を完結させようとする。その受け皿が `temp_buffers` なんだ。

もし、この領域が足りなくなるとどうなるか？答えは単純。PostgreSQLはディスク（一時ファイル）へデータを溢れさせる。ディスクI/Oが発生した瞬間に、クエリのレスポンスはガクンと落ちる。メモリ上の処理とディスク上の処理では、スピードが桁違いだからね。

どんな時に効いてくるのか？

具体的には、以下のような操作をする時に `temp_buffers` が消費される。

`CREATE TEMPORARY TABLE` で一時テーブルを作っている時
`SELECT INTO TEMP TABLE …` のような一時テーブルへの書き出し
一時テーブルに対するインデックス作成（`CREATE INDEX`）

「いや、うちは一時テーブルなんてそんなに使ってないよ」と思った人、ちょっと待って。実は、複雑なクエリの内部処理でPostgreSQLが暗黙的に一時テーブルを作成していることもあるんだ。

チューニングの目安と「罠」

PostgreSQLのデフォルト値は `8MB` だ。現代のサーバースペックから考えると、正直ちょっと心もとない。

1. 現在の状況を確認する

まずは、本当に足りていないのかをログから読み解くのが鉄則だ。`postgresql.conf` で `log_temp_files` を設定して、一時ファイルが作成されたときにログを吐くようにしてみよう。

0にすると全ての一時ファイル作成がログ出力される（開発環境推奨）
log_temp_files = 0

ログに「temporary file: …」という行が大量に出ていたら、それは `temp_buffers`（あるいは `work_mem`）の増量を検討すべきサインだ。

2. 設定の考え方

`temp_buffers` は セッションごと に割り当てられる。つまり、コネクション数が増えれば増えるほど、メモリ使用量も増えるということだ。

メモリが潤沢にある場合: 32MB〜64MB程度に引き上げてみる。
バッチ処理など特定のセッションだけでいい場合: 全体設定を変えるのではなく、セッション単位で設定を変えるのが賢いやり方だ。

— 特定の重い処理の直前で一時的に引き上げる
SET LOCAL temp_buffers = ‘128MB’;

— 重いクエリを実行
SELECT FROM complex_processing();

実務で注意すべき「落とし穴」

ここで、後輩からよく聞かれる質問に答えておこう。

Q. とりあえず大きくすれば速くなるの？
A. 「No」だ。メモリを確保しすぎると、今度はOSレベルでのメモリ不足（OOM Killer）のリスクが高まる。特にコネクションプーラー（PgBouncerなど）を使っている場合、接続数が跳ね上がるとメモリが枯渇してデータベースがクラッシュするリスクがある。

Q. `work_mem` と何が違うの？
A. 混同しやすいけど明確に役割が違う。

`work_mem`: ソートやハッシュ結合などの「演算」のためのメモリ。
`temp_buffers`: 一時的な「ストレージ（テーブル・インデックス）」のためのメモリ。

どっちも大事だけど、一時テーブルを多用するような設計をしているシステムなら `temp_buffers` が重要になるし、大規模な結合が多いなら `work_mem` が重要になる。まずはログを見て、どちらのメモリが足りていないのかを見極めるのが、エンジニアとしての腕の見せ所だよ。

まとめ：まずは「ログ」を見ることから

チューニングの基本は、勘に頼らないこと。

1. `log_temp_files` で一時ファイルの発生状況を確認する。
2. ディスクI/Oがボトルネックになっているか `iostat` 等で確認する。
3. 問題があれば、`SET LOCAL` を使って特定のクエリでテストしてみる。

この手順を踏めば、大きな事故を起こさずに性能を限界まで引き出せるはずだ。

データベースは嘘をつかない。君が正しくリソースを渡してあげれば、期待以上のパフォーマンスで応えてくれる。もし設定を変えてみて「おっ、速くなった！」という成功体験を積めたら、それはもう君が一人前のデータベースエンジニアになった証拠だよ。

それじゃ、また現場で会おう。健闘を祈る！