【実務・中級編】 maintenance_work_mem – PostgreSQL

よう、元気か？

今回はPostgreSQLのメンテナンス作業を裏で支える、ちょっと地味だけど超重要な設定パラメータ、「`maintenance_work_mem`」について、がっつり掘り下げていくぞ。

ぶっちゃけ、普段の業務で「`work_mem`」は意識しても、「`maintenance_work_mem`」って何？って人もいるんじゃないか？でもな、こいつを疎かにすると、VACUUMがいつまで経っても終わらなかったり、インデックス作成がディスクI/Oの嵐になったりして、システム全体に迷惑をかけかねない。

現場のDBAとしては、ここ、マジで重要だからな。俺の経験も交えつつ、具体的な使い方やチューニングのコツを伝授するぜ。

—

maintenance_work_memって、結局何なの？

まず、「`maintenance_work_mem`」が何者なのか、その正体から見ていこう。

簡単に言うと、これはPostgreSQLがメンテナンス作業を行う際に一時的に使用できるメモリの上限を設定するパラメータだ。

「え、一時的なメモリなら`work_mem`があるじゃん？」と思ったお前、鋭いな。そこがポイントだ。

`work_mem`は「セッションごとの」ソートやハッシュテーブルなどの操作に使うメモリ上限。例えば、巨大なJOINやORDER BYを含むSELECT文を実行するときに使うメモリだな。

対して、`maintenance_work_mem`は、特定の種類のメンテナンス操作、つまりデータベース全体やテーブル構造に影響を与えるような作業で使うメモリなんだ。そして、これらの作業は、通常のユーザーセッションとは性質が異なるため、別のパラメータで管理されているってわけだ。

何のために使われるの？

具体的に`maintenance_work_mem`が使われる主要な操作はこれらだ。

VACUUM / VACUUM FULL: デッドタプル（削除された行や更新前の行）の回収処理
CREATE INDEX: 新しいインデックスを作成する際のデータソート
ALTER TABLE ADD COLUMN: デフォルト値を持つカラムを追加する際など、テーブル全体を書き換えるような操作
CLUSTER: テーブルの物理的な並び替え

これらの操作は、大量のデータを一時的に読み込み、ソートしたり、ハッシュテーブルを構築したりする必要がある。その際に、指定された`maintenance_work_mem`の範囲内でメモリを効率的に利用しようとするんだ。

—

設定値の考え方と注意点

じゃあ、この`maintenance_work_mem`、どれくらいに設定すればいいんだ？って話になるよな。

デフォルト値は？

デフォルトは64MBだ。正直なところ、多くの本番環境ではこの64MBじゃ全然足りないことが多い。特にデータ量が増えてくると、あっという間にボトルネックになりかねない。

どれくらいが適切なのか？

ここが一番悩むところだよな。一概に「これ！」って数値は言えないんだが、いくつか目安と考えるべきポイントがある。

1. サーバーの総メモリ量とのバランス:
`maintenance_work_mem`は、`shared_buffers`や`work_mem`（たくさんのセッションが同時に使う可能性がある）と違って、通常は一つのメンテナンスプロセスでしか使われない。だから、`shared_buffers`やOSキャッシュを圧迫しない範囲で、比較的大胆に設定できることが多い。
例えば、サーバーに64GBのメモリがあるなら、数百MB、場合によっては1GBや2GBを設定することも現実的だ。

2. 作業の種類とデータ量:

VACUUM: デッドタプルが多いテーブルや、テーブルサイズが大きい場合に効果が大きい。
CREATE INDEX: インデックスを作成するテーブルのサイズが大きければ大きいほど、インデックスキーのソートにメモリを必要とする。

3. 設定を高くしすぎた場合の弊害:
「じゃあ、とりあえずデカくしとけばいいんだろ？」と思うのはちょっと待て。

OOM (Out Of Memory): PostgreSQLプロセスが一時的に大量のメモリを確保しようとして、OSのメモリが枯渇し、プロセスが強制終了される可能性がある。これは最悪のシナリオだ。
スワップの発生: メモリが足りない場合、OSはディスクにスワップアウトする。こうなると、ディスクI/Oが激増し、パフォーマンスは著しく低下する。メモリでやろうとしたことが、結局ディスクI/O待ちになってしまうという本末転倒な事態だ。

4. 設定を低くしすぎた場合の弊害:

ディスクI/Oの激増: メモリが足りないと、ソートやハッシュ処理をディスク上の一時ファイルで行うことになる。これがパフォーマンス悪化の主な原因だ。VACUUMが何時間もかかったり、インデックス作成がいつまで経っても終わらない、なんて経験ないか？それ、`maintenance_work_mem`不足が原因かもしれないぞ。
メンテナンス時間の増加: 結果的に、メンテナンス作業全体にかかる時間が長くなる。

感覚としては、`shared_buffers`が総メモリの25%程度、`maintenance_work_mem`は総メモリの数%（例えば、1GB～4GBくらい）といったレンジで考えることが多いな。ただし、これはあくまで目安だ。

—

具体的な使用例とコード

じゃあ、実際にどう使うのか、コードを交えて見ていこう。

`maintenance_work_mem`は、`postgresql.conf`で設定してサーバー全体に適用することもできるし、特定のセッションやトランザクション、あるいは個別のコマンドの直前で一時的に変更することも可能だ。

一時的な設定変更

特定のメンテナンス作業のためにだけメモリを増やしたい場合は、`SET`コマンドが便利だ。

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

— 今回の作業のために一時的に2GBに設定
SET maintenance_work_mem = ‘2GB’;

— ここにメンテナンスコマンドを実行
— 例: VACUUM
VACUUM VERBOSE ANALYZE my_large_table;

— 例: CREATE INDEX
CREATE INDEX concurrently_idx ON my_large_table (column_to_index);

— 作業が終わったら、セッションを終了するか、元の値に戻す（自動で戻る）
— もし明示的に戻したいなら
— SET maintenance_work_mem = default;

`SET`コマンドで設定した値は、そのセッションが終了するか、明示的に変更しない限り有効だ。ただし、`postgresql.conf`で設定された値は、新しいセッションが開始されるたびに適用されるから覚えておけよ。

1. VACUUMでの活用

`VACUUM`は、デッドタプルの回収のためにテーブルをスキャンし、インデックスもスキャンする。特に`VACUUM FULL`や`VACUUM ANALYZE`では、統計情報の収集やテーブルの再構築に大量のメモリを使うことがある。

デッドタプルが多い巨大なテーブルに対して`VACUUM`を実行する場合、`maintenance_work_mem`を大きくすると、デッドタプルのリストをメモリ内で効率的に処理できるようになり、ディスクI/Oを減らせる。

— デッドタプルが多いと予想されるテーブルに対して
SET maintenance_work_mem = ‘1GB’; — 必要に応じて調整
VACUUM VERBOSE ANALYZE my_huge_log_table;

`VERBOSE`をつけると、どれくらいメモリが使われたか、どのフェーズに時間がかかったか、といった情報がログに出力されるので、チューニングの参考になるぞ。

2. CREATE INDEXでの活用

新しいインデックスを作成する際、PostgreSQLはインデックスキーとなるデータをソートする必要がある。テーブルが大きければ大きいほど、このソート処理に時間がかかり、メモリを消費する。

`maintenance_work_mem`を十分に設定しておけば、ディスクに一時ファイルを書き出すことなくメモリ上でソートを完結させることができ、劇的にインデックス作成時間を短縮できる可能性がある。

— 巨大なテーブルに新しいインデックスを作成する場合
SET maintenance_work_mem = ‘2GB’; — ここもテーブルサイズに応じて調整
CREATE INDEX concurrently_new_idx ON customer_orders (order_date DESC, customer_id);

ポイント: `CREATE INDEX CONCURRENTLY`を使う場合も、このパラメータは有効だ。オンラインでインデックスを作成できる便利な機能だが、その分、裏でソート処理は走っているからな。

3. ALTER TABLE … ADD COLUMNでの活用

`ALTER TABLE … ADD COLUMN`は、デフォルト値を持つカラムを追加する際、テーブル全体を書き換える場合がある。特に`NOT NULL`制約とデフォルト値を持つカラムを追加する場合、テーブルの全ての行を更新する必要があり、これもまた`maintenance_work_mem`の恩恵を受ける。

— 大規模なテーブルにデフォルト値付きのNOT NULLカラムを追加する場合
SET maintenance_work_mem = ‘1GB’;
ALTER TABLE products ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

—

現場でのチューニングのコツ

最後に、俺が実務で`maintenance_work_mem`をチューニングする際に気をつけていること、後輩にアドバイスする内容を共有するぜ。

1. いきなり大きくしない、少しずつ上げて効果を見る:
「よし、メモリ2GBに！」って一気に上げるのは危険だ。まずは、デフォルトの64MBから128MB、256MB、512MB、1GB、2GBというように、段階的に上げてみて、メンテナンス作業の完了時間やディスクI/Oの変化を観察するんだ。

2. 監視と効果測定は必須:

実行計画: `EXPLAIN (ANALYZE, BUFFERS)`を使って、ソートやハッシュがどこでメモリを使っているか、あるいは一時ファイルを生成しているかを確認する。
I/O監視: `iostat`や`sar`コマンド、クラウドであればベンダー提供のメトリクスなどで、ディスクI/Oの使用状況を監視する。`maintenance_work_mem`を増やした結果、一時ファイルへの書き込みが減り、I/Oが改善されたら成功だ。
`pg_stat_statements`: どのクエリがどれくらいメモリを使ったか、一時ファイルを使ったか、といった情報も参考になる。

3. 一時的な変更を積極的に活用する:
`postgresql.conf`でサーバー全体に設定するのではなく、特定の時間帯に実行するバッチ処理（例えば深夜のVACUUMやインデックス再構築）の直前に`SET maintenance_work_mem = ‘XGB’;`を実行し、その作業が終わったら元の値に戻すか、セッションを終了させるのが安全策だ。こうすれば、普段の運用には影響を与えずに済む。

4. 複数のメンテナンス作業が同時に走る可能性を考慮する:
通常、`maintenance_work_mem`は一つのプロセスでしか使われないと言ったが、例えば、`VACUUM`と`CREATE INDEX CONCURRENTLY`がたまたま同じタイミングで走ったりすると、それぞれのプロセスが`maintenance_work_mem`分のメモリを確保しようとする可能性がある。
とはいえ、このパラメータはあくまで上限であり、実際にその分すべてを常に使うわけではない。だが、念のため、大規模なメンテナンス作業は時間帯をずらすなど、スケジューリングで考慮しておくのが賢明だ。

5. テスト環境での検証は怠るな:
本番環境に適用する前に、必ずテスト環境で十分な負荷をかけて検証すること。本番と同等か、それに近いデータ量や構成の環境でテストするのが理想だ。

—

まとめ

`maintenance_work_mem`は、PostgreSQLのメンテナンス作業の効率を大きく左右する、縁の下の力持ちのようなパラメータだ。適切に設定することで、システムの安定性とパフォーマンスを向上させることができる。

何に使われるか: `VACUUM`, `CREATE INDEX`, `ALTER TABLE`など、データベース構造を変更するメンテナンス作業。
なぜ重要か: メモリを有効活用することで、ディスクI/Oを減らし、メンテナンス時間を短縮する。
設定のポイント: サーバーの総メモリ量や作業の種類に応じて、`postgresql.conf`または`SET`コマンドで適切に設定する。ただし、高くしすぎるとOOMやスワップのリスクもあるため、段階的に調整し、効果を測定することが重要。

PostgreSQLは奥が深い。一つ一つのパラメータが、データベースの挙動に大きく影響する。これからも一緒に、現場で使える実践的な知識を深めていこうぜ！

何か困ったことがあったら、いつでも気軽に相談してくれ。じゃあな！