PostgreSQLにおけるVACUUMコマンドの使用方法

PostgreSQLデータベースのテーブルで行が更新または削除されるたびに、デッドローが残されます。VACUUMは、スペースを再利用できるようにそれらを削除します。テーブルが空にされないと、肥大化し、ディスク領域を浪費し、シーケンシャルテーブルスキャンと、より少ない程度ですが、インデックススキャンの速度を低下させます。

VACUUMコマンドは、不要になった行バージョン（タプルとも呼ばれる）だけを削除します。削除されたトランザクションのトランザクションID（xmaxシステム列に格納）が、PostgreSQLデータベース（または共有テーブルのクラスタ全体）でまだ有効な最も古いトランザクション（xmin bound）よりも古い場合、このタプルはもはや必要ありません。

PostgreSQLクラスタにおけるxmin boundは、以下の3つのケースに注意することで抑制することができます。

1. 長時間稼働しているトランザクションを探す

長時間稼働しているトランザクションを探し、pg_terminate_backend()関数を使用して、VACUUMコマンドをブロックしているデータベースセッションを終了させることができます。

2. レプリケーションスロットの検索

レプリケーションスロットは、PostgreSQLサーバが待機サーバでまだ必要とする情報を破棄しないようにするためのデータ構造です。レプリケーションが遅れたり、待機サーバが停止した場合、レプリケーションスロットはVACUUMコマンドによる古い行の削除を防止します。

レプリケーションスロットは、マスターサーバーがすべてのスレーブサーバーに受信されるまでWALブロックを削除しないことを保証する自動化された方法を提供します。また、マスターサーバーは、スレーブサーバーが切断された場合でも、リカバリーの競合を引き起こす可能性のある行を削除しません。

レプリケーションスロットは、既知の必要数のみのWALブロックを保持し、必要数を超えないようにします。

この問題は、レプリケーションスロットを使用することで、スレーブサーバーが接続されていない期間は保護されないため、回避することができます。

pg_drop_replication_slot()関数を使用して、不要なレプリケーションスロットを廃棄することができます。

これは、物理レプリケーションにおいて、hot_standby_feedbackパラメータがonに設定されている場合にのみ発生する現象です。論理レプリケーションであれば、同様の危険がありますが、システムディレクトリのみが影響を受けます。

3. 準備のできたトランザクションの検索

2相コミットプロトコルは、原子確認応答プロトコルである。これは、分散されたアトミックトランザクションに関わる全てのプロセスを調整し、そのトランザクションをコミットするか終了（ロールバック）するかを決定する分散アルゴリズムである。

2相コミット処理では、分散型トランザクションはまずPREPARE TRANSACTIONを使用して、2相コミットのために現在のトランザクションを準備します。何らかの理由でPREPARE TRANSACTIONコマンドが失敗した場合、ROLLBACKとなり、現在のトランザクションはキャンセルされます。

次に、COMMIT PREPAREDを使用して、2相コミットのために事前に準備されたトランザクションをコミットします。

トランザクションは一度準備されると、コミットされるか中止されるまで「さまよえる」状態を保ちます。通常、トランザクションは長い間準備状態に留まることはありませんが、時々エラーが発生するため、管理者が手動でトランザクションを削除する必要があります。

また、ROLLBACK PREPAREDを使用して、2相コミットのために事前に準備されたトランザクションをキャンセルすることができます。

追記：postgresqlのバキューム操作について

PostgreSQLのデータベース管理では、定期的なバキュームが重要な作業となります。

バキュームの効果

1.1 更新/削除された行によって占有されているディスクスペースを解放、再利用する。

1.2 POSTGRESQLクエリプランで使用する統計情報を更新する

1.3 トランザクションIDのリセットにより、非常に古いデータが失われることを防止する。

第一の理由は、PostgreSQLのデータの挿入、更新、削除操作は、実際にはデータベースの容量に入れられないからです。定期的に領域を解放しないと、データが多すぎるためにクエリの速度が劇的に低下します。

2つ目の理由は、PostgreSQLが問い合わせ処理を行う際に、問い合わせ速度を上げるために統計情報を元に実行計画を決定していることです。もしそれが時間通りに更新されないと、クエリが期待通りに動作しない可能性があります。

3つ目の理由は、PostgreSQLではトランザクションごとにトランザクションIDが生成されますが、この数値には上限があることです。トランザクションIDが最大値に達すると、再び最小値からループを開始します。つまり、前のデータの解放が間に合わないと、トランザクションIDの消失により、古いデータが失われてしまうのです。

Postgresqlの新しいバージョンでは自動バキュームがありますが、大量のデータIOがある場合、自動実行が遅くなる場合があり、手動実行や独自のスクリプトでデータベースをクリーンアップする必要があります。

1. vacuumdb は、SQL コマンド VACUUM のラッパーです。

つまり、vacuumdbとvacuumの両方を使用してデータベースをクリーンアップすることは、同じように機能します。

2. vacuumdbのいくつかの重要なパラメータ

は、vacuumdb --helpで問い合わせることができます。

-a/--all 全てのデータベースをバキュームする

-d dbname データベースのみ バキューム dbname

-f/--full は完全バキュームを実行します。

-t table データテーブルのみバキューム

3. postgresのユーザーに切り替わる

vacuumdb -d yourdbname -f -z -v でデータベースをクリーンアップします。

またはcontrtab 15 1 * * * postgres vacuumdb -d mydb -f -z -v >> /tmp/vacuumdb.log に追加してください。

毎日1:15に清掃を開始します。

4. 私のXIDが臨界値に近いかどうかを調べるコマンドです。

select age(datfrozenxid) from pg_database;

または

select max(age(datfrozenxid)) from pg_database;

5. しかし、我々は実際に真空にするためにどの大きなテーブルグループを懸念している

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_ size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

このコマンドは、1G以上のテーブルで、上位20位までのテーブルを、XIDの古い順に調べるクエリである。

以下はその一例です。

relname | xid_age | table_size
------------------------+-----------+------------
postgres_log | 199785216 | 12 GB
statements | 4551790 | 1271 MB
normal_statement_times | 31 | 12 GB

その後、各テーブルに対して個別にバキュームを実行することができます。

vacuumdb --analyze --verbose --table 'postgres_log' mydb

上記は私の個人的な経験ですが、参考にしていただき、BinaryDevelopをもっと応援していただければと思います。もし間違いや不十分な考察があれば、お気軽にアドバイスしてください。