Cloud SQL for PostgreSQL - VACUUM について深く掘り下げるためのよくある質問と回答
Google Cloud Japan Team
※この投稿は米国時間 2023 年 7 月 7 日に、Google Cloud blog に投稿されたものの抄訳です。
PostgreSQL は高度なオープンソースのデータベースです。料金、パフォーマンス、ベンダー ロックインの回避などのさまざまな理由で、ワークロードを他のエンジンから Cloud SQL for PostgreSQL に移行する企業が増えています。PostgreSQL の特長は、マルチバージョン同時実行制御（MVCC）の実装と、VACUUM と呼ばれるガベージ コレクタです。VACUUM は広範に及ぶトピックですが、このブログ投稿では理解しやすい Q&A 形式で VACUUM の仕組みについて説明します。
Q. 対象読者は誰ですか。
A. このブログ投稿は、PostgreSQL の MVCC と VACUUM の仕組みについて関心を持つデータベース管理者と開発者の皆様を対象としています。
Q. MVCC とは何ですか。また、なぜ VACUUM が必要なのですか。
A. MVCC により、複数のトランザクションが互いに干渉することなく、データベースへの読み取りと書き込みを同時に実行できるようになります。MVCC の最も重要なポイントは、「読み取り側が書き込み側を絶対にブロックしない」と同時に「書き込み側が読み取り側を絶対にブロックしない」点です。PostgreSQL では、新しいバージョンの行を作成することで MVCC を実現しています。更新トランザクションが commit されると、それ以降に開始される新しいトランザクションはすべて新しい行を参照するようになりますが、既存のトランザクションは古い行を参照し続けます。PostgreSQL は、すべての行バージョンをテーブルデータ構造で保持します。つまり、UPDATE クエリを実行すると既存の行バージョンはそのまま保持され（すなわち「デッドタプル」）、更新されたデータで新しいバージョンが作成されます。同様に、行に DELETE クエリを実行するとデッドタプルとしてマークされますが、物理的に削除されるわけではありません。PostgreSQL は、2 つの列、xmin と xmax を使用してこれらのバージョン管理されたすべての行とトランザクションに関する可視性を追跡し、その情報を各行に格納します。
xmin - タプルを挿入するトランザクション ID を定義します。
xmax - タプルを削除するトランザクション ID を定義します。
多数の更新と削除を行う場合、より多くのデッドタプルが生じる可能性があり、ストレージ使用量が増大するだけでなく、クエリのパフォーマンスにも影響が及びます。テーブルを VACUUM 処理することで、これらのデッドタプルが占めていた容量を回収できます。また、トランザクション ID を凍結して、トランザクションのラップアラウンドを回避することもできます。VACUUM オペレーションは、自動バキューム ランチャー プロセスによってバックグラウンドで実行されますが、VACUUM コマンドを使用して手動で実行することもできます。
Q. トランザクション ラップアラウンドとは何ですか。
A. xmin 列と xmax 列は 4 バイトの固定サイズであり、最大で 2^32 - 1、つまり約 40 億のトランザクション ID を格納できます。ビジー状態のデータベースで処理するトランザクションがこれより多くなった場合、トランザクション ID をリサイクルする必要があります。PostgreSQL の自動バキュームは、トランザクション ID の経過時間（age）がしきい値の上限（autovacuum_freeze_max_age フラグで制御）を超えているテーブルがないか常にチェックし、該当するテーブルに対して凍結オペレーションを実行します。緊急時には、手動で凍結ジョブを実行することもできます。
Q. 可視性マップとは何ですか。
A. ヒープ リレーションごとに、最後の VACUUM の実行後に変更されたページを追跡する可視性マップがあります。可視性マップは、ヒープページあたり 2 つのビットを格納します。1 つ目のビットが設定されている場合、このページのタプルがすべてのセッションから可視であることを示します。これは、このページにはバキューム処理が必要なタプルがないことを意味します。この情報をインデックスのみのスキャンで使用すると、インデックス タプルのみを使用するクエリに対応でき、ヒープテーブルのタプルにアクセスして可視性をチェックする必要がなくなります。2 つ目のビットが設定されている場合、このページのすべてのタプルが凍結されていることを意味します。つまり、ラップアラウンド防止用 VACUUM の場合でもページに再アクセスする必要がありません。可視性マップを使うと、テーブルの自動バキューム / バキューム プロセスを高速化することができます。
Q. VACUUM プロセスは常にデッドタプルをクリーンアップ、またはトランザクションを凍結しますか。
A. VACUUM プロセスのブロッカーが存在する場合、必ずしもすべてのデッドタプルをクリーンアップ、またはトランザクションを凍結できません。たとえば、書き込み側のインスタンスまたはレプリカ、放棄されたレプリケーション スロット、あるいは孤立した準備済みトランザクションで長時間実行されているトランザクションがブロッカーになる可能性があります。ブロッカーを特定して削除するには、Cloud SQL のドキュメントをご覧ください。
Q. VACUUM が実行されていない、または停止されている場合、データベースにどのような影響がありますか。
A. ビジー状態のデータベースで VACUUM が実行されていない場合、データベースのワークロードと DML オペレーションに応じてデッドタプルが蓄積し続けるため、クエリのパフォーマンスに悪影響が及びます。トランザクション ID のリサイクルを行わない場合、最終的にはデータベースでラップアラウンドの問題が発生し、データベース停止を引き起こします。Cloud SQL には、デッド / ライブタプルおよびトランザクション ID の使用状況をモニタリングする指標が用意されています。
Q. 通常の VACUUM ではなく VACUUM FULL を使用すべきなのはどのような場合ですか。
A. 通常の VACUUM（「FULL」が付かないもの）は容量を回収し、再利用できる状態にするだけです。この形式のコマンドは、排他ロックがかからないため、テーブルの通常の読み取りおよび書き込みと並列で実行できます。しかし、余った容量はオペレーティング システムに返されず（ほとんどの場合）、同じテーブル内での再利用が可能になります。VACUUM FULL は、容量を余らせずにテーブルのコンテンツ全体を新しいディスク ファイルに再書き込みするため、未使用の容量をオペレーティング システムに戻すことができます。この形式の方が処理に時間がかかり、処理中は各テーブルに ACCESS EXCLUSIVE ロックが必要で、処理が完了するまで使用できないようにテーブルがブロックされます。この場合、テーブルが停止しているとみなす場合があります。VACUUM FULL は、特定のテーブルが使用されていない行でいっぱいになり、再びそれほど大きくならないと想定される場合に有用です。
テーブルの最後の空のページを切り捨てる場合、通常の VACUUM にも ACCESS EXCLUSIVE ロックをかけることができますが、通常は短期間です。
Q. ロックせずに VACUUM FULL を利用できますか。
A. Cloud SQL が提供している pg_repack 拡張機能を使用すると、テーブルとインデックスから肥大化した部分を削除できます。CLUSTER や VACUUM FULL とは異なりオンラインで動作し、処理中に短時間、テーブルに排他ロックをかけます。これは、長時間のダウンタイムを許容できないビジネスの本番環境のデータベースに有用です。A. PostgreSQL 12 では index_cleanup の延期機能が導入されました。これによってインデックスのクリーンアップを延期できるため、速度が大幅に向上します。これは、すぐにもラップアラウンドの問題が発生しそうな場合や、すでに停止状態にある場合など、緊急事態において便利です。
PostgreSQL 14 は、フェイルセーフの VACUUM がトリガーされた場合にインデックスのバキューム処理をスキップします。これを制御するのは vacuum_failsafe_age フラグです。
Q. VACUUM を高速化するための並列オプションはありますか。
A. PostgreSQL の全バージョンで 2 つのテーブルのバキューム処理を並列して実行できます。PostgreSQL 13 では、1 つのテーブルのバキューム処理での並列実行に対応しました。一点注意していただきたいのが、ヒープスキャンは引き続き 1 つのプロセスで処理されるという点です。1 つのインデックスのクリーンアップに対応する並列ワーカーは最大で 1 つで、VACUUM ワーカー プロセスの最大数は、max_worker_processes、min_parallel_index_scan_size、max_parallel_maintenance_workers フラグによって決まります。これは、複数のインデックスがある大きなテーブルでのバキュームの実行時間を短縮する際に有用です。
Q. VACUUM オペレーションを高速化するにはどうすればよいですか。
A. VACUUM の実行時間を短縮することを目的とした、データベースのワークロードに応じて調整可能な自動バキューム / バキュームのフラグがいくつかあります。
autovacuum_vacuum_cost_limit
コストがこの値まで累積すると、自動バキューム プロセスがスリープ状態になります。autovacuum_vacuum_cost_delay
指定された合計時間（ミリ秒単位）がこの制限を超えると、自動バキューム プロセスがスリープ状態になります。低い値を指定すると VACUUM が高速になりますが、使用する CPU と I/O は増えます。autovacuum_work_mem
自動バキューム ワーカー プロセスそれぞれで使用できる最大メモリ量を指定します。高い値を設定すると、VACUUM の実行時間が短くなります。現時点では、1 つの VACUUM プロセスが使用できるメモリは 1 GB までです。
VACUUM オペレーションを高速化するその他の手法として、並列ワーカーを使用して VACUUM を実行する手法や、前述した方法でインデックスのクリーンアップ フェーズを延期する手法などがあります。
Q. VACUUM のその他の調整方法はありますか。
A. VACUUM オペレーションの調整に使用できるフラグが他にもいくつかあります。
autovacuum_max_workers
任意のタイミングで実行できる自動バキューム プロセス（自動バキューム ランチャーを除く）の最大数。autovacuum_naptime
特定のデータベースで自動バキュームが実行されてから次に実行されるまでの最小遅延を指定します。autovacuum_freeze_max_age
ラップアラウンド防止用 VACUUM の前にテーブルの pg_class.relfrozenxid フィールドが取得できる（トランザクションの）最大経過時間を指定します。高すぎる値に設定すると、ほとんどの場合は VACUUM を終了できず、ラップアラウンドの問題が発生します。autovacuum_freeze_min_age
VACUUM が行のバージョンを凍結するかどうかを決定するために使用する必要がある（トランザクションの）カットオフ経過時間を指定します。デフォルトは 5,000 万件のトランザクションです。INSERT のみのテーブルの場合、これらのタプルはその存続期間において更新 / 削除されることがないため、これを 0 に設定できます。autovacuum_vacuum_thresholdおよびautovacuum_vacuum_scale_factor
これらのフラグは、自動バキュームがトリガーされる場合の動作を制御します。テーブルレベルで構成可能で、大きなテーブルの場合はより積極的な VACUUM を構成できます。
statement_timeout を設定して SQL ステートメントの最長期間に制限をかける手法や、idle_in_transaction_session_timeout を設定してトランザクションがアイドル状態の接続の最長期間に制限をかける手法もおすすめです。一部のフラグはテーブルレベルで設定できるため、テーブルの使用状況に応じて VACUUM を制御できます。Q. TOAST テーブルが関連付けられている場合、VACUUM はどのように動作しますか。
A. UPDATE オペレーション中、変更のないフィールドの値は通常そのまま保持されます。このため、行外の値を持つ行で UPDATE を実行しても、行外の値に変更がなければ TOAST に関連するコストは発生しません。
たとえば、2 つの列「ID INT」と「NAME TEXT[]」を含むテーブルがあるとします。この NAME 値は TOAST テーブルの行外に格納されます。ここで ID 列を更新すると、メインのテーブルのみでデッドタプルが生成され、TOAST テーブルには変更はありません。
TOAST を適用可能なデータ型はそれぞれ、そのデータ型の列向けのデフォルトの戦略を指定しますが、特定のテーブル列の戦略は ALTER TABLE ... SET STORAGE で変更できます。また別の設定である TOAST_TUPLE_TARGET は、ALTER TABLE ... SET (toast_tuple_target = N) を使用してテーブルごとに調整可能で、TOAST 管理コードがトリガーされるタイミングを制御します。
VACUUM オペレーションは、メインのテーブルと TOAST テーブルで並列実行できます。この点は、ラップアラウンドの状況が発生した緊急事態において停止から短時間で復帰する場合に役立ちます。
Q. VACUUM は、書き込み側のインスタンスで実行されているクエリをブロック / キャンセルしますか。
A. 通常の自動 VACUUM のロックタイプは SHARE UPDATE EXCLUSIVE MODE ですが、同時実行されている別のクエリがロックを必要としていることを認識した場合、ジョブがキャンセルされます。空のデータページを切り捨てるために ACCESS EXCLUSIVE ロックを使用する場合、競合するクエリを考慮してこの切り捨てオペレーションをスキップします。ただし、ラップアラウンド防止用バキュームは競合するクエリをブロックするため、DDL クエリを実行する前に lock_timeout を設定することをおすすめします。
Q. VACUUM は、Cloud SQL のレプリカノードで実行されているクエリをブロック / キャンセルしますか。
A. VACUUM の実行中に同じテーブルにアクセスするレプリカノードで SELECT クエリが実行されると、VACUUM はそのレプリカクエリで必要とされていたデッドタプルをクリーンアップする可能性があります（整合性のあるスナップショットのため）。この場合、レプリカクエリがキャンセルされます。クエリがキャンセルされる問題を回避するには、ホット スタンバイから書き込み側のインスタンスにフィードバックを送信する hot_standby_feedback フラグをレプリカに設定します。このフラグを設定した後も、状況によってはレプリカクエリがキャンセルされることがあります。たとえば、手動の DDL オペレーションでテーブルに排他ロックがかかっている場合や、空のデータページをリリースする VACUUM ジョブがある場合などです。
max_standby_streaming_delay フラグを使用できます。これは、競合がある場合にレプリカへの Write Ahead Log（WAL）の適用を遅延させるフラグですが、もちろんレプリケーションの遅延が発生します。クエリがキャンセルされる問題が発生する場合は、通常の VACUUM の切り捨て動作を無効にできます。pg_stat_database_conflicts のビューにはデータベースあたり 1 つの行が含まれ、スタンバイ サーバーでの復旧との競合が原因で発生しているクエリのキャンセルに関するデータベース全体の統計情報を示します。
Q. INSERT のみのテーブルに VACUUM は必要ですか。
A. はい。INSERT のみのテーブルにバキュームが必要になる理由がいくつかあります。
タプルを凍結してトランザクションのラップアラウンドの問題を回避するため。
可視性マップを更新するため。インデックスのみのスキャンでは可視性マップをチェックしてヒープデータのページがすべて可視であるかどうかを確認する必要があるので、更新によりインデックスのみのスキャンが関係するクエリのリグレッションを防止できます。
PostgreSQL 13 で autovacuum_vacuum_insert_threshold と autovacuum_vacuum_insert_scale_factor という 2 つのフラグが導入されました。これらを必要に応じて設定することで、挿入のみのテーブルの自動バキュームを早い段階でトリガーできます。
Q. VACUUM が予期せず再起動した場合や停止した場合、どうなりますか。
A. PostgreSQL は可視性マップを介してデッドタプルや凍結されていないタプルを含むページを追跡し、テーブル全体のスキャンを回避します。VACUUM の実行中、この可視性マップが頻繁に更新されるため、VACUUM オペレーションが予期せず再起動した場合もこれらのページを再処理する必要はありません。
ラップアラウンド防止用バキュームで relfrozenxid を進める（テーブルの経過時間を短くする）場合は、VACUUM がシングルパスで可視性マップのすべてのページをスキャンする必要があります。
Q. HOT 機能とは何ですか。また、どのような役割を果たしますか。
A. UPDATE クエリによって新しいタプルのバージョンが作成され、新しい ctid（タプルの物理的な場所）が取得されるため、UPDATE クエリが個々のインデックスもすべて更新しなければならないように思えます。しかし、PostgreSQL には「HOT」（Heap Only Tuple）と呼ばれる最適化機能があり、これによって（インデックス ページへの書き込みによる）書き込みの増大を回避し、UPDATE クエリのレイテンシを改善できます。HOT 機能を使用すると、更新された行の古いバージョンを通常のオペレーション（SELECT を含む）中に完全に削除できるようになり、定期的なバキューム オペレーションが不要になります。HOT を適用できるのは、新しいタプルが同じデータページ内に収まっており、インデックス列で更新が行われていない場合のみです。
Q. テーブルを設計するときに VACUUM に関して注意すべき点は何ですか。
A. テーブルで多数の UPDATE オペレーションを実行することが想定される場合、以下のように対応するとよいでしょう。
新しいタプルのバージョンを新しいページに移動するのではなく、同じデータページに収まるように、テーブルの FILLFACTOR 値を小さくする。FILLFACTOR のデフォルト値は 100 ですが、ユースケースに応じて 90～95 に減らすことができます。
HOT 機能を利用できるよう、可能な場合は頻繁に更新される列のインデックス化を回避する。
データサイズが大きい場合はパーティション化されたテーブルを使用する。VACUUM を 1 つの大きなテーブルで実行するよりも、管理しやすいサイズの個別のパーティションで管理、実行する方が効率的です。
更新する列によっては、正規化されたテーブル設計の方が効率的です。以下で、ソーシャル メディアのアプリのテーブル（ユーザーのフォロワーを格納するもの）を設計する簡単な例をご紹介しましょう。
配列を使用してフォロワーの ID すべてを格納する。userid int, follower_id int[]
この設計では、ユーザー ID に対して新しいフォロワーの追加または削除を行ったときに配列全体を更新する必要があり、ユーザーのフォロワー数によっては VACUUM オペレーションに多くのチャーンが追加されることがあります。
正規化されたテーブル設計を使用する。userid int, follower_id int
正規化された設計では、フォロワーを追加または削除するために、該当するテーブルで特定の行を追加 / 削除するだけで済みます。
この推奨事項は単純に VACUUM の効率を考慮したものですが、比較検討すべき要因は他にもある可能性があります（クエリのパフォーマンス、ストレージなど）。
Q. VACUUM の進捗状況とクリーン / 凍結対象のページの数を追跡するにはどうすればよいですか。
A. VACUUM の進捗状況をチェックするには、カタログビュー pg_stat_progress_vacuum を使用します。
このブログ投稿で言及したいくつかのフラグはインスタンス レベルでは使用できない場合がありますが、データベースまたはユーザーレベルで修正できます。
まとめ
データベースのパフォーマンスの維持、ディスク容量の効率的な管理、適切なデータベースのメンテナンスの実施、ラップアラウンドやパフォーマンスに関する問題のトラブルシューティングを実現するには、PostgreSQL の VACUUM に関する理解を深めることが重要です。このブログ投稿では、デッドタプルが占めるディスク容量を回収してフラグメンテーションを回避するうえで VACUUM が果たす役割と、オペレーティング システムに容量をリリースする方法について説明しました。また、バキューム処理の並列実行を活用する方法と、バキュームのパフォーマンスに影響を与える可能性がある重要なフラグについても説明しました。
- データベース エンジニア Virender Singla
