VACUUMを使用したPostgreSQLのデータベース膨張の検出と解消

PostgreSQLのデータベース膨張は、古い行バージョンや過剰に大きなインデックスが、実際のデータが必要とする以上の領域を占有するときに発生します。バックアップの肥大化、スキャンの低速化、ストレージコストの増加、またはautovacuumが常に動作しているにもかかわらずテーブルのディスク上のサイズが縮小しないなどの現象に気づくかもしれません。

修正方法は、どのような領域を解放する必要があるかによって異なります。標準のVACUUMは、PostgreSQL内部でデッドタプルの領域を再利用可能にします。VACUUM FULL、REINDEX、pg_repackなどのツールは、オブジェクトを物理的に縮小できますが、それぞれ異なるロックや運用コストが伴います。

PostgreSQLのMVCCと膨張の理解

膨張に効果的に対処するには、まずその根本原因を理解する必要があります。PostgreSQLのMVCCアーキテクチャは、リーダーがライターをブロックせず、その逆もないようにします。行が更新されると、PostgreSQLは古い行を上書きせず、新しいバージョンを挿入し、古いバージョンをデッドとしてマークします。同様に、削除された行はデッドタプルを残します。

膨張は、これらのデッドタプルが、メンテナンスプロセス（Autovacuumまたは手動のVACUUM）がそれらをクリーンアップしたり領域を再利用したりするよりも速く蓄積されるときに発生します。

データベース膨張の影響

膨張は、いくつかの主要な領域でパフォーマンスに影響を与えます。

ディスク容量の増加: デッドタプルが物理領域を占有し、テーブルとインデックスが必要以上のストレージを消費する原因となります。
シーケンシャルスキャンの低速化: データベースエンジンはテーブルスキャン中にデッドタプルを読み飛ばす必要があり、I/O負荷が増加します。
非効率的なインデックス: 膨張したインデックスはより大きく、インデックス構造をトラバースするためにより多くのディスク読み取りが必要になります。
Autovacuumの無駄な努力: Autovacuumはテーブルのクリーンアップにより多くの労力と時間を費やす必要があり、他のテーブルの重要なメンテナンスが遅れる可能性があります。

データベース膨張の検出

検出はカタログ統計とオブジェクトサイズから始まります。単純なクエリはトリアージとして扱い、正確な膨張測定とは考えないでください。テーブルレイアウト、fillfactor、TOASTデータ、インデックスが実際の数値に影響を与えるためです。

1. `pg_stat_user_tables`を使用した膨張したテーブルの特定

pg_stat_user_tablesビューは、ユーザー定義テーブルの統計を提供します。テーブルに割り当てられた合計サイズとライブデータのサイズを比較することで、おおよその膨張を計算できます。

監視すべき主要な指標:

n_dead_tup: デッドタプルの数。
last_autovacuum, last_vacuum: 最後にメンテナンスが実行された日時。

単純なカウントは、どこでバキューム圧力が高まっているかを示すため有用です。n_live_tupと比較してn_dead_tupが多い場合は、テーブルをより詳細に調査する良い理由になります。

サンプルクエリ（バキューム候補の検索）:

このクエリは、デッドタプルが多いテーブルを強調表示し、最後にバキュームが実行された日時を示します。

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. 膨張したインデックスの評価

膨張はしばしばインデックスに大きな影響を与えます。まず、変更頻度の高いテーブルにある異常に大きなインデックスを探します。

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

サイズが大きいだけでは膨張を証明できませんが、pgstattuple拡張機能や監視プラットフォームの膨張クエリなどのより深いツールでチェックする価値のあるインデックスを示します。

膨張の管理: VACUUMの役割

VACUUMは、デッドタプルから領域を再利用し、可視性マップを更新するためのPostgreSQLの主要なツールです。

Autovacuum: 最初の防御線

デフォルトでは、PostgreSQLはautovacuumプロセスを自動的に実行します。Autovacuumは、しきい値に達すると標準のVACUUM（領域を内部的に再利用可能としてマークしますが、OSに解放はしません）を実行します。このしきい値は、autovacuum_vacuum_scale_factor（デフォルト0.2、つまりテーブルサイズの20%）とautovacuum_vacuum_threshold（デフォルト50タプル）によって定義されます。

設定のヒント: 変更頻度の高いテーブルでは、scale_factorを低く設定してメンテナンスをより早くトリガーし、大きな膨張の蓄積を防ぐことを検討してください。

-- 例: 重要なテーブル 'orders' に積極的なautovacuumパラメータを設定
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

標準VACUUM vs. VACUUM FULL

クリーンアップには主に2つのモードがあります。

標準 `VACUUM`

標準のVACUUMは、デッドタプルを既存の物理ファイル内で再利用可能としてマークします。ディスク上のテーブルファイルサイズは縮小しません。これはノンブロッキングであり、トラフィックの多いテーブルに対して安全です。

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- クリーンアップされたタプルに関する統計を表示

`VACUUM FULL`（領域再利用ツール）

VACUUM FULLは、テーブルファイル全体を書き換えて、デッドタプルを物理的に削除し、領域をオペレーティングシステムに戻します。

警告: VACUUM FULLは、その実行中、テーブルに対してACCESS EXCLUSIVEロックを必要とします。つまり、VACUUM FULLが完了するまで、そのテーブルに対するすべての読み取りおよび書き込み操作がブロックされます。このコマンドは、大規模で頻繁に使用されるテーブルには慎重に使用してください。

VACUUM FULL table_name;

ベストプラクティス: VACUUM FULLは、膨張が深刻でダウンタイムが許容できる場合、または計画されたメンテナンスウィンドウ中にのみ使用してください。

高度なアンチ膨張戦略

VACUUM FULLが妨害的すぎる場合、ダウンタイムを減らして領域を再利用する代替方法があります。

1. インデックスの再構築

インデックスの膨張が主な問題である場合、個々のインデックスを再構築できます。ビジーなシステムでは、操作の大部分で読み取りと書き込みを継続できるように、同時実行形式を優先してください。

REINDEX INDEX CONCURRENTLY index_name;

プレーンなREINDEX INDEX index_name;は高速ですが、より強力なロックを取得するため、メンテナンスウィンドウ中に使用してください。

2. オンラインテーブル書き換えのための`pg_repack`の使用

pg_repackユーティリティは、ダウンタイムを最小限に抑えてテーブルの膨張を解消するための推奨方法です。これは、古いテーブルと並行して新しいクリーンなテーブル構造とデータのコピーを作成し、変更を同期的に適用し、アトミックにテーブルを交換することで機能します。

pg_repackの仕組み:

元のテーブルをミラーリングする一時テーブル（_new）を作成します。
トリガーを使用して元のテーブルの変更を継続的に監視します。
最終的な同期コピーとスワップを実行します。

インストールと使用法（典型的なフロー）:

PostgreSQLのバージョンに合わせて拡張機能とCLIパッケージをインストールし、データベースで拡張機能を有効にしてから、シェルからpg_repackコマンドを実行します。

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

pg_repackに関する注意: VACUUM FULLと比較してロックを大幅に削減しますが、トリガーの作成とデータのコピーが必要であり、一時的に追加のI/Oとストレージを消費します。

まとめ

データベース膨張は、障害になる前に監視していれば管理可能です。調整されたautovacuumによる予防は、緊急の書き換えよりも優れています。膨張が発生した場合は、次の階層に従ってください。

監視: pg_stat_user_tablesで高いn_dead_tupカウントを定期的に確認します。
Autovacuumの調整: アクティブなテーブルでは、標準のVACUUMがより頻繁に実行されるようにスケールファクターを下げます。
修復: 膨張が軽微でテーブルアクティビティが低下した場合、標準のVACUUM table_nameで十分な場合があります。
積極的な修復（低ダウンタイム）: pg_repackを使用してテーブル構造をオンラインで書き換えます。
緊急修復（高ダウンタイム）: VACUUM FULLは、排他ロックを保持するため、ダウンタイムが許容される場合の最後の手段としてのみ使用します。

書き換えの前に、長時間実行トランザクションを確認し、操作に十分な空きディスク容量があることを確認してください。