PostgreSQLの「temporary file size exceeds temp_file_limit」エラーを修正する

エラーの内容

クエリを実行したとき — 大規模なソート、ハッシュ結合、数百万行への GROUP BY など — PostgreSQL が突然処理を打ち切ります：

ERROR: temporary file size exceeds temp_file_limit (1048576kB)

クエリが遅くなるのではありません。単純に死ぬのです。PostgreSQL が中間データをディスクにスピルしようとしたところ、そのスピルが temp_file_limit の上限を超えてしまい、処理が終了します。

根本原因

PostgreSQL はソートやハッシュ操作ごとに work_mem で設定された固定量の RAM を使用します。データが収まりきらない場合、一時ファイルとしてディスクにスピルします。temp_file_limit パラメータは、1 つのセッションがそれらのファイルに使用できるディスク容量の上限を設定します。

この上限を超えるとクエリは強制終了されます — 部分的な結果も、再試行もありません。デフォルトの制限は通常 1 GB（1048576 kB）ですが、インデックスなしで 500 MB のテーブルを 2 つ結合するまでもなく、あっという間に超えてしまいます。

典型的なトリガー：

インデックスのないカラムで数百万行をソートまたはグループ化する
フィルタリングされていない大きなテーブル同士のハッシュ結合
広い結果セットへの DISTINCT、ORDER BY、または UNION
それぞれが大きな中間結果をマテリアライズする複数の CTE

修正 1 — work_mem を増やす（まずここから）

work_mem を増やすと、ディスクに触れる前に RAM に収まるデータ量が増えます。まずセッションレベルのオーバーライドでテストしてみましょう — 再起動は不要です：

SET work_mem = '256MB';
-- その後クエリを再実行する

クエリが完了しましたか？ postgresql.conf に永続化しましょう：

# postgresql.conf
work_mem = 64MB   # デフォルトは 4MB

再起動なしで設定を再読み込みする：

SELECT pg_reload_conf();
-- または: sudo systemctl reload postgresql

グローバルに値を上げる前に計算に注意してください。 work_mem はソートノードごと、セッションごとに適用されます。100 の同時接続で 10 個のソートノードを持つクエリは 10 × 100 × work_mem の RAM を消費する可能性があります — work_mem = 256MB の場合、256 GB になります。グローバル値は総 RAM の 10〜20% 以下に抑えてください。大規模な分析クエリには、セッションまたはロールレベルでオーバーライドする方が適切です。

修正 2 — temp_file_limit を引き上げるか削除する

夜間の ETL やデータウェアハウスのエクスポートなど、大きなスピルが本当に必要なワークロードの場合は、制限と戦うより上限を引き上げましょう。値はキロバイト単位で、-1 は制限を無効にします。

-- セッションのみ:
SET temp_file_limit = '4GB';  -- または -1 で無制限

-- 特定のロールに永続的に設定:
ALTER ROLE etl_user SET temp_file_limit = '4GB';

-- postgresql.conf でグローバルに:
temp_file_limit = 4194304   # 4 GB（kB 単位）

postgresql.conf を編集した後：

SELECT pg_reload_conf();

グローバルに -1 を設定しないでください。20 人のアプリユーザーが共有するサーバーでは、暴走した 1 つのクエリがディスクを埋め尽くし、他のすべてのセッションを停止させる可能性があります。無制限は、信頼できる DBA または ETL ロールのみに限定してください。

修正 3 — クエリを最適化する

問題が制限ではなくクエリにある場合もあります。まず実行プランを確認しましょう：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

確認すべき 2 点：Sort Method: external merge Disk と Hash Batches: > 1。どちらかが表示されていれば、その操作がディスクにスピルしています。

ソートを回避するインデックスを追加する

-- インデックスのないカラムでのソートやグループ化は、フルソートパスを強制します。
CREATE INDEX idx_orders_created ON orders(created_at);

早い段階でフィルタリングする

-- 悪い例: 1000万行をソートしてから 999万行を破棄する
SELECT * FROM events ORDER BY ts DESC LIMIT 100;

-- 良い例: まず 1 テナントにフィルタし、データの一部だけをソートする
SELECT * FROM events WHERE tenant_id = 42 ORDER BY ts DESC LIMIT 100;

CTE 内の不要な DISTINCT や ORDER BY を削除する

-- ORDER BY や DISTINCT を持つすべての CTE は一時結果セットをマテリアライズします。
-- ロジックが実際に必要としない限り、CTE 内での並び替えはスキップしてください。
WITH recent AS (
  SELECT user_id, action FROM logs
  WHERE created_at > now() - interval '7 days'
  -- ここに ORDER BY は不要
)
SELECT ...;

大きな中間結果をステージングする

6 つのテーブルを結合して 5000 万行を集計する 1 つのクエリは問題を引き起こしやすいです。分割しましょう：

CREATE TEMP TABLE stage AS
  SELECT user_id, sum(amount) AS total
  FROM transactions
  WHERE status = 'completed'
  GROUP BY user_id;

CREATE INDEX ON stage(user_id);

SELECT u.name, s.total
FROM users u
JOIN stage s USING (user_id)
WHERE s.total > 1000;

各ステップは小さくなり、プランナーが処理しやすくなり、デバッグも容易になります。

確認方法

修正を適用した後、クエリが正常に完了し、一時ファイルの使用量が減少したことを確認します：

-- 現在の設定を確認する
SHOW work_mem;
SHOW temp_file_limit;

-- 現在のデータベースの累積一時使用量:
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

-- アクティブなクエリとその一時使用量（PostgreSQL 14 以降、pg_stat_statements 使用）:
SELECT pid, left(query, 80) AS query_snippet, wait_event_type, state
FROM pg_stat_activity
WHERE state = 'active';

クエリの前後で累積チェックを実行してください。temp_bytes がほとんど変化しなければ、データが RAM に収まっていたことになります。

予防策

temp_file_limit はグローバルではなくロールごとに設定しましょう。合理的な分け方：アプリケーションユーザーには 1GB、DBA と ETL ロールには -1。
ワークロードに合わせて work_mem を調整してください。OLAP データベースはグローバルに 64〜256MB を設定できます。OLTP データベースはグローバルを 4〜16MB に抑え、重いクエリにはセッションごとにオーバーライドしてください。
問題になる前に暴走クエリを検出するために log_temp_files を有効にしてください：

# postgresql.conf
log_temp_files = 0        # 0 = 一時ファイルへのすべての書き込みをログに記録する
# log_temp_files = 10240  # または 10 MB 以上のファイルのみログに記録する

数十万行以上に触れる新しいクエリのレビュープロセスに EXPLAIN ANALYZE を組み込んでください。開発中に external merge ソートを発見するのはタダです。本番環境で午前 2 時に発見するのはそうではありません。