PostgreSQLのテーブル肥大化を解消する：VACUUMとANALYZEの実践ガイド

Table of Contents

午前2時のディスク容量緊急事態

最近、すべてのDBAが恐れるPagerDutyのアラートに遭遇しました。本番データベースのディスク使用率が95%に達したのです。計算が合いませんでした。新しいデータセットをインポートしたわけでもなく、主要なテーブルの行数も横ばいでした。それなのに、500GBのEBSボリュームの空き容量が1時間ごとに数ギガバイトずつ減っていたのです。

原因は「テーブル肥大化（Table Bloat）」でした。私はこれまでMySQLやMongoDBを管理してきましたが、PostgreSQLは多版型同時実行制御（MVCC）アーキテクチャを採用しているため、データの更新処理が異なります。この設計はパフォーマンスには優れていますが、領域を占有し続け、なかなか消えてくれない「デッドタプル（不要なタプル）」という特有の課題を生み出します。

MVCCが実際に肥大化を引き起こす仕組み

容量の増加を抑えるには、PostgreSQLが書き込みをどのように処理するかを理解する必要があります。ほとんどのデータベースはデータをその場で上書きしますが、PostgreSQLは違います。重いロックを回避し、複数のユーザーが同時にデータにアクセスできるように、トランザクション管理の一環として行の「バージョン」を作成します。

UPDATEを実行すると、PostgreSQLは元の行をディスクに残したまま「期限切れ（expired）」としてマークします（これがデッドタプルです）。そして、その行の完全に新しいバージョンを挿入します。DELETEも同様に動作し、即座にデータを消去するのではなく、将来のトランザクションからは見えないようにフラグを立てるだけです。

これらのデッドタプルは幽霊のような存在です。VACUUMと呼ばれるプロセスがそれらをクリアするまで、ディスク上に残り続けます。書き込み量が多い場合（例えば秒間5,000回の更新など）、VACUUMの設定が保守的すぎると、これらの幽霊が蓄積し、実際のデータよりも多くのスペースを消費するようになります。

ボトルネックの特定

闇雲に設定を変更し始めてはいけません。まずは、適切なデータベース監視を通じて、どのテーブルのクリーンアップが追いついていないかを確認する必要があります。PostgreSQLは、これをpg_stat_user_tablesビューで追跡しています。

私はクラスター内で最も問題のあるテーブルを見つけるために、以下のクエリを使用しています。

SELECT 
    relname AS table_name, 
    n_live_tup AS live_rows, 
    n_dead_tup AS dead_rows, 
    last_vacuum, 
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

もしdead_rows（不要な行）がlive_rows（有効な行）の20%を超えているテーブルがあれば、Autovacuumが追いついていません。last_autovacuumが空だったり、数日前の日付だったりする場合は、設定に問題があります。

ツールボックス：VACUUM、VACUUM FULL、そしてANALYZE

これらを管理するための主なツールは3つありますが、ピーク時に選択を誤るとアプリケーションがオフラインになるリスクがあります。

1. 標準のVACUUM

これは日常的に使用するツールです。デッドタプルを見つけ、その領域を*新しい*PostgreSQLデータが利用できるようにマークします。OSに領域を返却するわけではありません。読み取りや書き込みをブロックしないため、アプリケーションの稼働中に実行できます。

VACUUM (VERBOSE, ANALYZE) orders;

2. VACUUM FULL

これは「最終手段」です。テーブル全体をディスク上の新しいファイルに書き換え、肥大化部分を完全に排除します。EBSボリューム上のデータベースサイズを物理的に縮小する唯一の方法です。しかし、これにはACCESS EXCLUSIVEロックが必要です。完了するまで、アプリケーションはそのテーブルに対して読み書きができなくなります。100GBのテーブルの場合、1時間のダウンタイムが発生する可能性があります。

3. ANALYZE

VACUUMが領域を管理するのに対し、ANALYZEはクエリプランナが使用する統計情報を更新します。統計情報が古いと、PostgreSQLは高速なインデックスを無視して、低速なシーケンシャルスキャンを実行してしまう可能性があります。大量のデータをロードした後は、必ずANALYZEを実行してください。

高トラフィック向けのAutovacuumチューニング

PostgreSQLのデフォルト設定は、現代のハードウェアを用いた負荷テストの結果に比べると、消極的すぎることが多いです。例えば、デフォルトのautovacuum_vacuum_scale_factorは0.2です。これは、1億行あるテーブルにおいて、デッドタプルが2,000万行に達するまでクリーンアップが実行されないことを意味します。本番環境としては、あまりにも肥大化しすぎです。

postgresql.confを、より積極的な以下の値に更新してください。

# テーブルの5%が変更されたときにvacuumを実行
autovacuum_vacuum_scale_factor = 0.05

# テーブルの2%が変更されたときにanalyzeを実行
autovacuum_analyze_scale_factor = 0.02

# アクティブなテーブルが多い場合はワーカー数を増やす
autovacuum_max_workers = 5

# ワーカーがより速く動作できるように「コスト制限（予算）」を増やす
autovacuum_vacuum_cost_limit = 1000

sessionsテーブルのように更新が非常に激しい特定のテーブルに対しては、データベース全体に影響を与えることなく、さらに個別の制限を設定できます。

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 500
);

「ゾンビトランザクション」の罠

Autovacuumが実行されているのに、領域が回収されないことがあります。これは通常、長時間実行されているトランザクションが原因です。PostgreSQLは、ある行が削除される*前*に開始されたアクティブなトランザクションがある場合、そのデッドタプルを削除できません。PythonスクリプトでのBEGINの消し忘れや、終了しないBIレポート1つで、数百万行のクリーンアップがブロックされる可能性があります。

これらのブロック要因を以下のクエリで特定します。

SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' 
AND (now() - xact_start) > interval '10 minutes';

クエリが数時間実行されている場合は、強制終了（kill）を検討してください。トランザクションが終了すれば、Autovacuumはようやくその役割を果たすことができます。

本番環境のための最終チェックリスト

データベースの健康維持は一度限りの修正ではなく、習慣です。PostgreSQLインスタンスを軽量かつ高速に保つために、以下のルールを守りましょう。

n_dead_tupを監視し、テーブルサイズの15%を超えたらアラートを出すように設定する。
データ量が増えるにつれてscale_factorを下げる。1GBの20%なら問題ありませんが、1TBの20%は惨事です。
計画メンテナンスの時間を確保できる場合を除き、VACUUM FULLは避ける。
30分以上開いたままのアイドル状態のトランザクションを強制終了する。

MVCCを理解することは、ディスク容量を節約するだけでなく、クエリを高速化し、アプリケーションの信頼性を高めることにつながります。