【実務・中級編】 work_mem – PostgreSQL

よお、元気か？今日はPostgreSQLのちょっと奥深い話、特に「`work_mem`」について、現場のリアルな視点から語ってやろうと思う。

「`work_mem`」って聞くと、「あ、あれね。ソートとかハッシュで使うメモリでしょ？」くらいには知ってるかもしれない。でも、その「ちょっと」が、パフォーマンスの明暗を分けることもあるんだ。今回は、教科書通りじゃない、現場で本当に役立つ知識を、コード例も交えながらぶっちゃけていくぜ。

—

`work_mem`って、一体何者なんだ？

ぶっちゃけ、`work_mem`は「クエリが内部で作業をするための一時的なメモリ空間」だと思ってもらっていい。具体的には、こんな場面で大活躍する。

ソート処理 (ORDER BY, DISTINCT, LIMITなど): 大量のデータを並べ替えたり、重複を取り除いたりする時に、この`work_mem`が使われる。
ハッシュ処理 (JOIN, GROUP BYなど): ハッシュテーブルを作って、高速なデータ検索や集計をする時も、`work_mem`が頼りになる。

PostgreSQLは、クエリが実行されるたびに、これらの作業のために`work_mem`で指定された量のメモリを確保しようとする。で、ここがポイントなんだけど、このメモリはクエリごとに割り当てられるんだ。つまり、同時にたくさんのクエリが大量のデータをソートしたりハッシュしたりすると、かなりのメモリを食う可能性があるってこと。

メモリが足りない！スピルアウトの恐怖

じゃあ、もし`work_mem`で指定されたメモリを使い切っちゃったらどうなるか？

ここで恐ろしい「スピルアウト (Spill out)」が発生する。これは、PostgreSQLがメモリ上で処理しきれなくなったデータを、一時ファイルに書き出すこと。

一時ファイルへの書き出しは、ディスクI/Oが発生するから、当然、処理速度は激しく低下する。数百ミリ秒で終わるはずのクエリが、数秒、場合によっては数十秒かかるなんてこともザラにある。普段は軽快に動いているアプリケーションが、急に重くなった！なんて時の原因の一つに、このスピルアウトが隠れていることが多いんだ。

`work_mem`の設定、どうすればいい？

「じゃあ、どれくらいに設定すればいいんだよ！」って声が聞こえてきそうだ。これは、一概に「この値！」とは言えないのが難しいところ。

1. デフォルト値と、その問題点

PostgreSQLのデフォルト設定では、`work_mem`は通常、非常に小さい値（例えば4MBとか）になっていることが多い。これは、デフォルトで大量のメモリを消費しないように、保守的な設定になっているからだ。

でも、このデフォルト値のままだと、ちょっと複雑なクエリや、大量のデータを扱うクエリでは、すぐにメモリ不足になってスピルアウトしやすくなる。特に、開発環境やテスト環境では、このデフォルト値のままパフォーマンスを測ると、実運用環境では全然ダメだった、なんてことが起こりうる。

2. 現場での考え方：バランスが大事！

`work_mem`を増やすと、ソートやハッシュ処理のパフォーマンスは向上する。でも、増やしすぎると、サーバー全体のメモリを圧迫して、他のプロセスに影響が出たり、OSのスワップが発生したりして、かえってパフォーマンスが悪化する可能性もある。

だから、基本的には以下のステップで考えるのが現実的だ。

現状把握: まず、よく実行されるクエリや、パフォーマンスのボトルネックになっているクエリを特定する。
モニタリング: `pg_stat_activity`や`pg_stat_statements`、あるいはPrometheus + Grafanaなどのモニタリングツールを使って、クエリごとの実行時間、一時ファイルの使用状況などをチェックする。
設定調整:
グローバル設定 (`postgresql.conf`): サーバー全体で`work_mem`のデフォルト値を少し上げる。ただし、サーバーの総メモリ量と、他のプロセスがどれくらいメモリを使うかを考慮して、慎重に。
セッション/トランザクション設定: 特定のクエリや、特定のアプリケーションから実行されるクエリのために、一時的に`work_mem`を増やす。これは、`SET work_mem = ‘…’`コマンドで実行できる。

3. 具体的な設定例

例えば、あるクエリでスピルアウトが発生していることが分かったとする。

現状の確認 (例):
`EXPLAIN (ANALYZE, BUFFERS) SELECT … ORDER BY …;` の結果で、`Sort Method: external merge Disk: 12345kB` のような表示が出ていたら、スピルアウトしている証拠だ。

一時的な設定変更:

— クエリ実行前に設定
SET work_mem = ’64MB’; — 例えば64MBに増やしてみる

— 問題のクエリを実行
SELECT …
FROM …
ORDER BY …;

— 設定を元に戻す (必要なら)
RESET work_mem;

このように、必要に応じてセッションごとに設定を変えられるのが、`work_mem`の面白いところであり、注意すべき点でもある。

4. どのくらいまで増やす？

これは本当にケースバイケースなんだけど、一般的には、サーバーの総メモリ量の1/4〜1/8程度を、`work_mem`の最大値として考えることが多い。ただし、これはあくまで目安。DBサーバーが専属で動いているか、他のアプリケーションも動いているか、同時接続数はどれくらいか、などによって大きく変わってくる。

大事なのは、感覚で「とりあえず大きくしておけ！」ではなく、計測して、少しずつ調整していくこと。

`work_mem`を賢く使うためのヒント

`EXPLAIN ANALYZE`は必須！

スピルアウトしているかどうか、どのクエリが原因かを知るには、`EXPLAIN (ANALYZE, BUFFERS)`が最強の武器になる。これを使わない手はない。

EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.customer_name,
o.order_date
FROM
customers c
JOIN
orders o ON c.customer_id = o.customer_id
WHERE
o.order_date >= ‘2023-01-01’
ORDER BY
o.order_date DESC;

この結果で `Sort Method: external merge Disk: …` と出ていたら、`work_mem`を増やすことを検討しよう。

インデックスの重要性再確認

`work_mem`はあくまで「ソートやハッシュ処理」のメモリ。そもそも、JOINやWHERE句でインデックスが適切に使われていれば、大量のデータをソートしたり、フルスキャンしたりする必要がなくなる。インデックスをしっかり貼ることが、`work_mem`に頼らずともパフォーマンスを改善する王道だ。

クエリのチューニング

複雑すぎるORDER BYやGROUP BYは、クエリの見直しで解決できることもある。アプリケーション側でデータを受け取ってからソートする、集計方法を変える、といったアプローチも検討しよう。

`log_temp_files`パラメータの活用

`postgresql.conf`で`log_temp_files`を設定しておくと、指定したサイズ以上のテンポラリファイルが作成されたときにログに記録される。

# postgresql.conf
log_temp_files = 1024 # 1MB以上のテンポラリファイル作成時にログ出力

これにより、どのクエリがどのくらいのサイズのテンポラリファイルを作成しているかを把握しやすくなる。

—

まとめ：`work_mem`は諸刃の剣

`work_mem`は、PostgreSQLのパフォーマンスを劇的に改善する可能性を秘めたパラメータだ。でも、使い方を間違えると、システム全体を不安定にすることもある。

今日の話で一番伝えたかったのは、

1. `work_mem`は、クエリごとの一時作業領域であり、不足するとディスクにスピルアウトしてパフォーマンスが激落ちする。
2. 設定値は、サーバーの総メモリ量、クエリの内容、同時実行数などを考慮して、計測・調整しながら決めるのが鉄則。
3. `EXPLAIN ANALYZE`で現状を把握し、インデックスやクエリの見直しも並行して行うことが重要。

ということ。

現場でPostgreSQLを触っている君たちなら、この「`work_mem`」というキーワードを聞いたときに、今日の話を思い出してくれると嬉しい。何か困ったことがあったら、またいつでも聞いてくれよな！