【実務・中級編】 temp_buffers – PostgreSQL

やあ、元気にしてるか？最近、PostgreSQLのパフォーマンス周りで「これ、どうなってるんですか？」って質問、よく来るんだよね。みんな熱心で嬉しい限りだよ。

今日は、そんなパフォーマンスチューニングの話の中でも、意外と見落とされがちなんだけど、実は結構大事なパラメータ「`temp_buffers`」について、みっちり解説していくぞ。一時テーブルを日常的に使うようなシステムでは、このパラメータの理解がパフォーマンス改善の鍵になることもあるんだ。

教科書的な説明だけじゃなくて、現場で僕らがどう考えて、どうチューニングしてるのか、その「肌感覚」も含めて伝えていくから、しっかりついてきてくれよな！

—

`temp_buffers`って、そもそも何者？

まず、`temp_buffers`が一体何なのか、基本的なところから押さえていこうか。

一言で言うとね、`temp_buffers`は「セッションごとの一時テーブル用ローカルバッファ領域」なんだ。

うん、ちょっと専門用語が多いね（笑）。分解して説明するよ。

PostgreSQLには、みんなよく知ってる「共有バッファ（`shared_buffers`）」っていう、データベース全体で共有されるメモリ領域があるよね。これは、永続的なテーブルのデータやインデックスをキャッシュして、ディスクI/Oを減らすためのものだ。これはPostgreSQLインスタンス全体で共有されてて、みんなで使う高速なキャッシュだと思ってくれればいい。

それに対して、`temp_buffers`は全然違う。

セッションローカル: これが一番大きな違いだ。`temp_buffers`は、PostgreSQLに接続してきた各セッションがそれぞれ独立して持つメモリ領域なんだ。つまり、君のセッションが使う`temp_buffers`と、隣のセッションが使う`temp_buffers`は、全く別物ってこと。
一時テーブル専用: その名の通り、`CREATE TEMPORARY TABLE`で作成された一時テーブルや、複雑なクエリを実行する際にPostgreSQLが内部的に使う一時ファイル（ソートやハッシュなどのため）のデータをキャッシュするために使われる。

イメージとしては、君が作業中に使う「一時的なメモ帳」みたいなものだ。他の人はそのメモ帳を見ないし、君の作業が終わればそのメモ帳は捨てられる。そんな感覚に近いかな。

なぜ`temp_buffers`が大事なのか？

じゃあ、なんでこの`temp_buffers`がそんなに大事なんだろうね？

僕らがシステムを運用していると、一時テーブルを使う場面って結構あるんだ。

例えば、

複雑な集計処理の途中で、中間結果を一時的に保存しておきたい時
大量のデータを読み込んで、特定のキーでグループ化・ソートしてから次の処理に移りたい時
複数のテーブルからデータを結合して、さらに加工が必要な時

こんな時、一時テーブルは非常に便利なんだ。SQLの可読性も上がるし、ステップバイステップで処理を進められるからね。

でも、この一時テーブルのデータが、`temp_buffers`に収まりきらなかったらどうなると思う？

そう、「ディスクに書き出される」んだ。

メモリは高速だけど、ディスクは遅い。これはもう、揺るぎない事実だ。一時テーブルのデータがメモリに収まらず、頻繁にディスクへの書き込み（I/O）が発生しだすと、途端にクエリのパフォーマンスはガタ落ちする。

`temp_buffers`が十分にあれば、一時テーブルへのアクセスはほぼメモリ上で行われるから、爆速で処理が進む。でも、足りないと「ディスク待ち」が発生して、処理時間が何倍にも跳ね上がる可能性があるんだ。

特に、書き込みと読み込みが頻繁に発生するような複雑なクエリやバッチ処理では、この影響は顕著に出てくるから注意が必要だよ。

具体的な設定方法と効果

じゃあ、実際にどうやって`temp_buffers`を設定するのか、見ていこうか。

1. `postgresql.conf`でインスタンス全体に設定

これは、PostgreSQLサーバー全体でデフォルト値を設定する方法だね。

postgresql.conf
temp_buffers = 256MB

こんな感じで設定する。単位はKB、MB、GBが使えるよ。
設定を変更したら、PostgreSQLの再起動（または設定のリロード）が必要だ。

2. セッション単位で一時的に設定

これが現場では結構使うテクニックなんだ。特定のクエリやバッチ処理を実行するセッションだけ、`temp_buffers`を増やしたい、なんて時に便利だよ。

— 現在の設定値を確認
SHOW temp_buffers;

— このセッションだけ、temp_buffersを512MBに設定
SET temp_buffers = ‘512MB’;

— 一時テーブルを使った処理を実行…
CREATE TEMPORARY TABLE my_temp_table AS
SELECT
id,
data,
current_timestamp AS created_at
FROM
large_source_table
WHERE
condition = true;

— …
— セッションが終了するか、RESETで元の値に戻る
RESET temp_buffers;

`SET`コマンドで変更した値は、そのセッションが終了するか、`RESET`コマンドを実行するまで有効だよ。他のセッションには影響しないから安心して使える。

3. 効果の確認: `EXPLAIN (ANALYZE, BUFFERS)`

「じゃあ、自分のクエリが一時テーブルをどれくらい使ってて、`temp_buffers`が効いてるのか、足りてないのか、どうやって判断すればいいんですか？」って思うよね。

そこで使うのが、おなじみの`EXPLAIN (ANALYZE, BUFFERS)`だ。

例えば、こんなクエリを実行してみよう。

EXPLAIN (ANALYZE, BUFFERS)
CREATE TEMPORARY TABLE temp_large_data AS
SELECT
generate_series(1, 1000000) AS id,
md5(random()::text) AS random_string,
repeat(‘a’, 100) AS padding
ORDER BY random_string;

この結果の出力に注目してほしい。

QUERY PLAN
————————————————————————————–
Sort (cost=18833.00..18833.00 rows=1 width=137) (actual time=133.053..144.375 rows=1000000 loops=1)
Buffers: temp read=1240 written=1240 <-- ここが重要！ Sort Key: random_string -> Result (cost=0.00..15000.00 rows=1000000 width=137) (actual time=0.008..54.743 rows=1000000 loops=1)
Buffers: shared hit=0 read=0 dirtied=0
-> ProjectSet (cost=0.00..10000.00 rows=1000000 width=137) (actual time=0.005..35.986 rows=1000000 loops=1)
Planning Time: 0.057 ms
Execution Time: 147.288 ms
(8 rows)

上記の例だと、`Buffers: temp read=1240 written=1240`という行があるよね。
これは「一時ファイルから1240ブロック読み込み、一時ファイルに1240ブロック書き込んだ」という意味だ。
つまり、`temp_buffers`では足りずに、ディスクに一時ファイルが生成され、そこにデータが書き込まれたり読み込まれたりしている、ということなんだ。

もし、`temp_buffers`が十分に確保されていれば、この `temp read` や `temp written` の値は「0」になるか、非常に小さい値になるはずだ。

重要ポイント:

`temp read` や `temp written` が0なら、一時ファイルへのI/Oは発生していない。
これらの値が大きい場合、`temp_buffers`が不足している可能性が高い。

この出力を見ながら、`temp_buffers`の値を調整していくのが、現場でのチューニングの基本パターンだよ。

どのくらい設定すればいいの？先輩からのアドバイス

「じゃあ、結局どれくらい設定すればいいんですか？」って聞かれると、正直言って「ワークロードによる」としか言えないんだ。ごめんね、これが現実なんだ。

でも、いくつかのヒントは与えられるよ。

1. 小さすぎるとダメ、大きすぎてもダメ:

小さすぎると、一時テーブルがすぐにディスクにスピル（こぼれる）してしまって、パフォーマンスが劣化する。
大きすぎると、各セッションが大きなメモリ領域を確保することになるから、サーバー全体のメモリを圧迫してしまう。結果的に、他のプロセスやOSの動作に影響が出たり、スワップが発生してしまったりする可能性もあるんだ。

2. まず現在の状況を把握する:

`EXPLAIN (ANALYZE, BUFFERS)` を使って、頻繁に実行されるクエリや、処理に時間がかかっているクエリが一時ファイルを使っているかどうかを確認する。
OSのツール（`vmstat`, `iostat`など）で、一時ファイルが作成されるディレクトリのI/O負荷を監視するのも有効だよ。

3. 徐々に増やして効果を測る:

もし`temp read`や`temp written`が多いクエリを見つけたら、まずはそのクエリを実行するセッションに対して`SET temp_buffers`で値を増やしてみて、どれくらい効果が出るか試してみるのがいい。
いきなり`postgresql.conf`で大きくするより、セッション単位で試す方がリスクが少ない。
効果が見られたら、その値をデフォルトにするか検討する、という流れだ。

4. `work_mem`との関係性も頭に入れておく:
`work_mem`もソートやハッシュなどの一時的なメモリ領域だけど、これは「クエリプランの各ノードが個別に使うメモリ」なんだ。`temp_buffers`は「一時テーブルのバッファ」だから、用途が違う。でも、どちらもメモリを使うので、これらのパラメータと合わせて、サーバー全体のメモリ使用量を考慮する必要があるよ。

5. 一時テーブルを本当に使うべきか？:
そもそも論になっちゃうけど、一時テーブルを多用しているなら、その設計自体を見直せないか、って視点も大事だ。例えば、CTE（Common Table Expressions）で代用できないか、もっと効率的なクエリパスはないか、インデックスは適切か、などね。`temp_buffers`はあくまでも「一時テーブルを使う場合の補助輪」だから、根本的なクエリの最適化が一番効果的だったりするんだ。

僕の経験上、デフォルトの`temp_buffers = 8MB`ってのは、最近のシステムではかなり小さいことが多いんだよね。複雑な分析クエリなんかを動かすシステムなら、128MBとか256MB、場合によってはもっと大きく設定することもある。でも、それはあくまで「必要に応じて」だ。安易に大きくしすぎると、思わぬメモリ枯渇に繋がるから、そこは慎重にね。

まとめ

今日は、PostgreSQLの`temp_buffers`について、その役割からチューニング方法まで、実践的な視点で解説してきたけど、どうだったかな？

`temp_buffers`はセッションごとに持つ一時テーブル専用のローカルバッファであること。
これが足りないと、一時テーブルのデータがディスクにスピルして、パフォーマンスが大きく劣化する可能性があること。
`SET temp_buffers`でセッション単位で設定したり、`EXPLAIN (ANALYZE, BUFFERS)`で効果を確認できること。
チューニングは「ワークロードによる」が、現在の状況把握と段階的な調整が重要であること。

これらを頭に入れておけば、君が担当するシステムのパフォーマンス改善に、きっと役立つはずだ。

データベースチューニングって、一見地味な作業に見えるかもしれないけど、こうやって一つ一つのパラメータの意味を理解して、システム全体の挙動を想像しながら調整していくのは、まるでパズルを解くみたいで、なかなか面白いもんだろ？

また何か困ったことがあったら、いつでも声かけてくれよな！じゃあ、また。