PostgreSQLの隠れた「影の主役」：maintenance_work_mem を使いこなせ！
やあ。データベースの運用、お疲れ様。
最近、PostgreSQLのチューニングについて相談を受けることが増えたんだけど、みんな意外と「SQLの書き方」や「work_mem」には気を配るのに、`maintenance_work_mem` のことを忘れがちだ。
正直に言うよ。この設定値を放置していると、ある日突然、メンテナンス処理が驚くほど遅くなって、深夜の運用アラートに悩まされることになる。今回は、この「縁の下の力持ち」について、現場の知見を詰め込んで解説するよ。
—
maintenance_work_mem って結局何者？
簡単に言うと、PostgreSQLが「メンテナンス作業」をするときに使う専用の作業用メモリだ。
対象になるのは主に以下の操作だ。
- VACUUM / VACUUM ANALYZE（領域の掃除と統計情報の更新）
- CREATE INDEX（インデックス作成）
- ALTER TABLE ADD FOREIGN KEY（外部キー制約の追加）
- REINDEX（インデックスの再構築）
普段のSQLクエリが `work_mem` を使うのに対して、こっちは「データベースを健康に保つための重たい処理」で使われる。デフォルト値は 64MB なんて控えめな数字だけど、大規模なテーブルを運用しているなら、このサイズがボトルネックになるのは目に見えているよね。
—
なぜこの設定が重要なのか？
例えば、数百万件あるテーブルで `VACUUM` を走らせるとしよう。`maintenance_work_mem` が小さいと、PostgreSQLは死んだレコード（デッドタプル）のリストをメモリに収めきれず、何度もディスクに書き出しながら作業することになる。
結果はどうなるか？
- I/O負荷の急増: ディスクへの書き込みが増え、本来のクエリ処理を圧迫する。
- 処理時間の増大: 本来なら10分で終わるはずのメンテナンスが、1時間以上かかる。
特に `CREATE INDEX` を行う時、このメモリが小さいとインデックスの構築速度が劇的に落ちる。実務では、「インデックス作成中にサイトが重くなった」なんてトラブルの主犯格がこれだったりするんだ。
—
現場での設定目安：どう決める？
じゃあ「とりあえず最大値にすればいいのか？」というと、それは危険だ。このメモリはセッションごとに確保される。もし `maintenance_work_mem` を極端に大きくして、並列でメンテナンス処理が走ったら、サーバーのメモリが一気に枯渇して OOM Killer に叩かれる可能性がある。
僕が現場でよく使うガイドラインはこんな感じだ。
1. 物理メモリの 5% 〜 10% 程度を上限の目安にする。
2. サーバーが32GBメモリなら、1GB〜2GBくらいをベースに調整する。
3. ただし、`autovacuum` が並列で動くことを考慮して、`autovacuum_max_workers` とのバランスを見る。
もし、特定のテーブルでインデックスを張り直す時だけ速くしたいなら、そのセッション内だけで一時的に増やすのが一番賢いやり方だ。
— 一時的にこのセッションだけメモリを増やす
SET maintenance_work_mem = ‘2GB’;
— その後で重い処理を実行する
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
こうすれば、サーバー全体のメモリを圧迫せずに、必要な時だけブーストをかけられる。これができるのが、わかっているエンジニアのやり方さ。
—
注意点：インデックス作成時の「並列化」
最近のPostgreSQL（バージョン11以降）では、`CREATE INDEX` が並列で実行できるようになった。このとき、指定した `maintenance_work_mem` は「並列実行される各ワーカー」に割り当てられる。
例えば `maintenance_work_mem = 1GB` で `max_parallel_maintenance_workers = 4` に設定していると、理論上は最大 4GB 近くのメモリを消費する可能性がある。この計算だけは忘れないようにしてほしい。
—
最後に：まずは現状を知ろう
君の担当しているDBで、最近 `VACUUM` がやけに遅いと感じることはないか？
まずは `pg_stat_activity` を覗いて、メンテナンス処理がどんな状況か確認してみよう。
SELECT pid, query, state, backend_start
FROM pg_stat_activity
WHERE query LIKE ‘%VACUUM%’ OR query LIKE ‘%CREATE INDEX%’;
もし「もっと速くしたい」と思ったら、まずは `maintenance_work_mem` を今の2倍、3倍に増やしてみることから始めてみてくれ。劇的に世界が変わるはずだ。
データベースチューニングに魔法はない。あるのは「仕組みを理解して、地道に調整する」ことだけだ。困ったことがあったら、またいつでも聞いてくれよ。応援しているぞ。
コメント