PostgreSQLのVACUUMとインデックス肥大化：検知、軽減、そして自動チューニング

あなたはアプリケーションのパフォーマンスを監視しています。かつてはキビキビと動作していたコアAPIエンドポイントが、今では頻繁にレイテンシSLOを超過しています。その背後にあるクエリは、usersやtransactionsテーブルを叩く単純なものに見えます。EXPLAIN ANALYZEは妥当なプランを示しているにもかかわらず、I/O時間は容赦なく増加し続けています。クラウドプロバイダーのディスク使用量チャートは、実際のデータ投入レートをはるかに上回るペースで、着実に直線的な成長を示しています。あなたはおそらく、PostgreSQLの最も厄介で誤解されがちなパフォーマンスの敵、すなわちテーブルとインデックスの肥大化（bloat）の犠牲者です。

これはバグではありません。PostgreSQLの洗練された多版型同時実行制御（MVCC）アーキテクチャがもたらす、根本的な結果なのです。MVCCは読み取りと書き込みをブロックしない素晴らしい仕組みですが、その過程で「不要タプル（dead tuples）」の痕跡を残します。これらが管理されずに放置されると、あなたのスリムなデータベースは、鈍重でストレージを食い尽くすモノリスへと変貌してしまいます。このガイドでは、タプルの可視性という原子レベルの話から、検知、軽減、そして自動修復のための本番環境グレードの戦略まで、この問題を技術的な観点から深く掘り下げて解説します。

肥大化の創世記：MVCCと不要タプル

肥大化を打ち負かすには、まずその起源を理解しなければなりません。PostgreSQLは、行を直接書き換える（in-place update）ことを決して行わないことで同時実行性を実現しています。UPDATEを実行すると、Postgresは実質的にDELETEとINSERTを実行します。行の古いバージョン（Postgresの用語で「タプル」）は即座に消去されるのではなく、「不要」とマークされ、新しいバージョンが挿入されます。DELETEは単にタプルを不要とマークするだけです。

各タプルには、その可視性を管理する2つの隠しシステムカラムがあります：xminとxmaxです。

• xmin: このタプルを生成したトランザクションID。
• xmax: このタプルを削除したトランザクションID。初期値は0。

トランザクションT1がある行を更新すると、古いタプルのxmaxをT1に設定し、xminがT1に設定された新しいタプルを挿入します。T1がコミットされる前に開始された他のトランザクションは、T1がまだデータベース状態の「スナップショット」に含まれていないため、古いタプルを見ることができます。これがPostgresが読み取りロックを回避する方法です。

「不要タプル」とは、そのxmaxが、現在実行中のすべてのトランザクションから可視なトランザクションIDに設定されている行バージョンのことです。これは、もはやこのバージョンを参照する必要があるアクティブなトランザクションが存在しないことを意味し、領域回収の対象となります。

そのプロセスは次のようになります：

この「不要」な領域は、オペレーティングシステムには返されません。テーブルの「ヒープ」ファイル内に留まり、空のポケットを作り出します。これが肥大化です。不要タプルが十分に蓄積されると、テーブルとそのインデックスは、格納している生存データよりも物理的にディスク上で大きくなります。これは以下の事態を引き起こします：

1. I/Oの増加： クエリが必要な生存データを見つけるために、より多くの8KBページをディスクから読み取る必要があります。
2. キャッシュ効率の低下： 肥大化したテーブルは共有バッファ（Postgresのメモリキャッシュ）を不釣り合いに消費し、有用なデータを追い出してしまいます。
3. スキャンの低速化： シーケンシャルスキャンとインデックススキャンの両方が、多くがゴミで満たされたページをより多く走査しなければならなくなるため、遅くなります。インデックスの肥大化は特に悪質で、インデックスを持つこと自体の目的を無効にしてしまう可能性があります。

VACUUMが防ぐ最終的な障害モードは、**トランザクションID周回問題（Transaction ID Wraparound）**です。トランザクションIDは32ビット整数です。約20億回の書き込みトランザクションの後、IDは周回します。古い不要タプルが「凍結（freeze）」（将来のすべてのトランザクションから可視であるとマーク）されない場合、データ破損を防ぐためにデータベースはシャットダウンしなければなりません。定期的なバキュームはパフォーマンスのためだけでなく、必須の運用要件なのです。

肥大化の検知と測定

測定できなければ、修正することはできません。汎用的な監視ツールでは肥大化を特定できません。Postgresの内部統計情報にクエリを投げる必要があります。以下のクエリは、様々なコミュニティソースから採用されたもので、pgstattupleのような拡張機能を必要とせずに、信頼性の高い推定値を提供します。これは、統計的に推定された生存タプルのサイズと、テーブルの実際のディスク上のサイズを比較することで機能します。

定番の肥大化検知クエリ

このクエリは、スーパーユーザーまたはテーブル統計を調査するのに十分な権限を持つロールによって実行されるように設計されています。

-- Detects table and index bloat.
-- Works on PostgreSQL 9.5+
-- Note: The accuracy of this query depends on the freshness of table statistics.
-- Run ANALYZE on your tables before trusting this output for the first time.

WITH constants AS (
  SELECT
    current_setting('block_size')::numeric AS bs,
    23 AS hdr,
    4 AS ma
),
tables AS (
  SELECT
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    hdr,
    ma,
    bs,
    tbl.reltuples,
    tbl.relpages,
    (
      (tbl.reltuples * (
        -- Average row width, accounting for overhead
        AVG(stat.stanullfrac) * (
          -- Average width of nullable columns
          SELECT SUM(stynsp) FROM pg_type typ JOIN pg_attribute attr ON typ.oid = attr.atttypid WHERE attr.attrelid = tbl.oid AND NOT attr.attnotnull AND attr.attnum > 0
        ) + (1 - AVG(stat.stanullfrac)) * (
          -- Average width of non-nullable columns
          SELECT SUM(stynsp) FROM pg_type typ JOIN pg_attribute attr ON typ.oid = attr.atttypid WHERE attr.attrelid = tbl.oid AND attr.attnotnull AND attr.attnum > 0
        )
      ) + ma - (
        -- Null bitmap overhead
        CASE WHEN tbl.reltuples > 0 THEN ((COUNT(DISTINCT stat.staattnum) + 7) / 8) ELSE 0 END
      ))
      -- Add tuple header overhead
      + hdr
    ) AS avg_row_width
  FROM
    pg_class tbl
  JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
  JOIN pg_attribute attr ON attr.attrelid = tbl.oid
  JOIN pg_statistic stat ON stat.starelid = tbl.oid AND stat.staattnum = attr.attnum
  WHERE
    tbl.relkind = 'r' -- only tables
    AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
    AND tbl.reltuples > 0
  GROUP BY
    ns.nspname, tbl.relname, hdr, ma, bs, tbl.reltuples, tbl.relpages
),
table_bloat AS (
  SELECT
    schema_name,
    table_name,
    reltuples AS live_tuples,
    relpages AS page_count,
    bs,
    -- Estimated pages needed for live data
    CEIL((reltuples * avg_row_width) / (bs - hdr)) AS estimated_pages,
    relpages AS actual_pages,
    -- Actual size
    pg_size_pretty(relpages * bs) AS actual_size
  FROM
    tables
),
table_bloat_summary AS (
  SELECT
    schema_name,
    table_name,
    live_tuples,
    -- Bloat in pages and bytes
    (actual_pages - estimated_pages) AS bloat_pages,
    pg_size_pretty((actual_pages - estimated_pages) * bs) AS bloat_size,
    -- Bloat percentage
    ROUND(100 * (actual_pages - estimated_pages) / actual_pages, 2) AS bloat_percentage
  FROM
    table_bloat
  WHERE
    -- Filter for significant bloat
    (actual_pages - estimated_pages) > 0
),
index_bloat AS (
  SELECT
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    idx.relname AS index_name,
    idx.reltuples,
    idx.relpages,
    -- Estimated index size
    ROUND((idx.reltuples * (
      -- Average index entry size
      SELECT
        -- Index header + item identifier + data
        16 + AVG(pg_column_size(indexdatacolumns))
      FROM
        pg_index i
      JOIN pg_stats s ON s.tablename = tbl.relname
      AND s.attname = ANY(string_to_array(i.indkey::text, ' ')::text[])
      WHERE i.indexrelid = idx.oid
    )) / current_setting('block_size')::numeric, 1) AS estimated_pages,
    idx.relpages AS actual_pages,
    pg_size_pretty(idx.relpages * current_setting('block_size')::numeric) AS actual_size
  FROM
    pg_class idx
  JOIN pg_index i ON i.indexrelid = idx.oid
  JOIN pg_class tbl ON tbl.oid = i.indrelid
  JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
  WHERE
    idx.relkind = 'i' -- only indexes
    AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
    AND idx.reltuples > 0
),
index_bloat_summary AS (
  SELECT
    schema_name,
    table_name,
    index_name,
    reltuples,
    (actual_pages - estimated_pages) AS bloat_pages,
    pg_size_pretty((actual_pages - estimated_pages) * current_setting('block_size')::numeric) AS bloat_size,
    ROUND(100 * (actual_pages - estimated_pages) / actual_pages, 2) AS bloat_percentage
  FROM
    index_bloat
  WHERE
    (actual_pages - estimated_pages) > 10 -- Filter for significant bloat
)
-- Combine and present the results
SELECT
  'table' AS object_type,
  schema_name,
  table_name AS object_name,
  bloat_percentage,
  bloat_size
FROM
  table_bloat_summary
WHERE bloat_percentage > 20.0
UNION ALL
SELECT
  'index' AS object_type,
  schema_name,
  index_name AS object_name,
  bloat_percentage,
  bloat_size
FROM
  index_bloat_summary
WHERE bloat_percentage > 30.0
ORDER BY
  bloat_size DESC;

出力の解釈： このクエリを実行すると、最も肥大化したテーブルとインデックスのリストが得られます。

経験則として：

• テーブルの肥大化 > 20%: 調査する価値あり。
• インデックスの肥大化 > 30-40%: 多くの場合、重大なパフォーマンスのボトルネックであり、修復の最優先候補。

最初の防衛線：自動バキュームのチューニング

autovacuumはPostgreSQLに組み込まれた清掃サービスです。これは、INSERT、UPDATE、DELETEが多数発生したテーブルに対して、定期的にVACUUMおよびANALYZE操作を実行するデーモンプロセスです。

しかし、デフォルトの自動バキューム設定は悪名高いほど保守的です。これらは低スペックのハードウェアでのリソース消費を最小限に抑えるように設計されており、中〜高程度の書き込み負荷には全く不十分です。デフォルト設定に依存することは、本番環境のパフォーマンス低下へのよくある道筋です。

なぜデフォルトでは失敗するのか

自動バキュームは、不要タプルの数が以下の式で計算されるしきい値を超えたときに、テーブル上でVACUUMをトリガーすることを決定します： vacuum_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_tuples

デフォルト値は以下の通りです：

• autovacuum_vacuum_threshold = 50
• autovacuum_vacuum_scale_factor = 0.20 (つまり、テーブルの20%)

1億行を持つevent_logsテーブルを考えてみましょう。しきい値は50 + 0.20 * 100,000,000 = 20,000,050となります。自動バキュームは、クリーンアップを検討する前に2000万もの不要タプルが蓄積するのを待ってしまいます。その時点では、テーブルはすでに深刻に肥大化しています。このスケールファクターが、巨大なテーブルにおける主要な元凶です。

本番環境グレードの自動バキュームチューニング

解決策は、グローバルなデフォルト設定から脱却し、書き込みの多いテーブルに対して、テーブルごとに積極的な設定を行うことです。

戦略

1. autovacuum_vacuum_scale_factorを劇的に下げる。
2. 妥当で静的なautovacuum_vacuum_thresholdを設定し、テーブルが小さくても頻繁にバキュームされるようにする。
3. オプションとして、バキュームワーカー自体のコスト制限を調整して、より積極的に動作させる。

書き込みが多いテーブルのためのレシピ

event_logsテーブルをチューニングしてみましょう。

-- Tune a specific high-write table.
-- A scale factor of 0.02 means vacuum triggers after 2% of the table has changed.
-- A threshold of 1000 ensures it runs even if the table is temporarily small.
ALTER TABLE public.event_logs SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 500
);

-- For extremely high-throughput tables, you might also make the vacuum worker
-- faster by reducing its sleep time between operations.
-- This table will now be vacuumed more often and more quickly.
ALTER TABLE public.event_logs SET (
  autovacuum_vacuum_cost_delay = '10ms' -- Default is 20ms on some versions
);

これらのストレージパラメータをテーブルに直接設定することで、そのリレーションに対してのみグローバルなpostgresql.confの設定を上書きします。これが正しく、的を絞ったアプローチです。グローバル設定を過度に積極的にすると、小さくて静的なテーブルに不要なI/O負荷をかける可能性があるため、それは避けたいところです。

書き込みの多いテーブルを見つけるには、pg_stat_user_tablesにクエリを投げます：

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM
  pg_stat_user_tables
ORDER BY
  n_tup_upd + n_tup_del DESC
LIMIT 20;

これにより、どのテーブルが最も更新頻度が高く、したがってカスタム自動バキュームチューニングの最有力候補であるかがわかります。

重火器：pg_repackによるゼロダウンタイムでの再構築

自動バキュームをチューニングしたものの、テーブルがすでに65%の肥大化で12GBも太ってしまっている場合はどうすればよいでしょうか？自動バキュームの標準的なVACUUMプロセスは、ディスク上のファイルを縮小したり、OSに領域を返却したりはしません。ファイル内の領域を将来のINSERTのために空きとしてマークするだけです。真に領域を回収し、ファイルを縮小するには、テーブルを書き直す必要があります。

ネイティブのVACUUM FULLコマンドはこれを実行しますが、操作の全期間にわたってテーブルにACCESS EXCLUSIVEロックを取得し、すべての読み取りと書き込みをブロックします。大規模で重要な本番テーブルにとって、これはアプリケーションを数分から数時間オフラインにすることに等しく、現実的な選択肢ではありません。

そこで登場するのがpg_repackです。これは、オンラインでのテーブルとインデックスの再構築を行うための、ゴールドスタンダードなコミュニティ拡張機能です。

pg_repackの仕組み

pg_repackは、最小限のロックでテーブルとそのインデックスを再構築し、真のゼロダウンタイムでの肥大化解消を可能にします。

1. ログテーブルとトリガー： ログテーブルを作成し、対象テーブルにトリガーをインストールして、プロセス中に発生するすべてのINSERT、UPDATE、DELETEをキャプチャします。
2. 新しいテーブルの作成： 元のテーブルと同じ構造を持つ、完全にパックされた新しいテーブルを作成します。
3. データコピー： 肥大化した元のテーブルから新しいテーブルへ、すべての行を効率的にコピーします。
4. インデックスの再構築： 新しいテーブル上に、新しくコンパクトなインデックスを構築します。
5. 差分の適用： ログテーブルにキャプチャされたすべての変更を新しいテーブルに適用し、最新の状態に追いつかせます。
6. スワップ： ここが魔法のような部分です。単一のトランザクション内で、短いACCESS EXCLUSIVEロックを保持しながら、以下の操作を行います：
   • 元のテーブルのインデックスと制約を削除する。
   • テーブル名を変更する（新しいテーブルが古いテーブルの名前になり、古いテーブルは一時的な削除候補となる）。
   • 新しく名前が変更された本番テーブルに制約を再作成する。
7. クリーンアップ： 古い、肥大化したテーブルファイルを削除します。

重い処理（ステップ3-5）はオンラインで行われます。排他ロック（ステップ6）は、テラバイト級のテーブルであっても通常はミリ秒から数秒しか続かないため、本番環境での使用にも安全です。

pg_repackの使用方法

まず、データベースサーバー上とデータベース内に拡張機能をインストールする必要があります。

-- In psql, connected to your target database
CREATE EXTENSION pg_repack;

その後、データベースサーバー上、またはネットワークアクセスとpg_repackバイナリを持つ任意のマシンからターミナルで実行します：

# Example: Repack the 'event_logs' table in the 'mydb' database
# -k, --no-order: Skips clustering by primary key. Faster, but you lose physical ordering.
# -j 4: Use 4 parallel jobs for index creation (if supported).
# --wait-timeout=60: Wait up to 60 seconds to acquire the final lock. Abort if a long-running query blocks it.

pg_repack -d mydb -h myhost.db.example.com -U db_admin_user -k -j 4 --wait-timeout=60 --table public.event_logs

前提条件：

• テーブルとそのインデックスの2つ目のコピーを一時的に保持するのに十分な空きディスク容量が必要です。
• テーブルにはPRIMARY KEYまたはNOT NULLカラムに対するUNIQUEインデックスがなければなりません。
• スキーマに対するCREATE権限が必要です。

可視性マップの役割

パズルの最後のピースは、可視性マップ（Visibility Map, VM）です。これは、テーブルのヒープファイル内のどのページが「すべて可視（all-visible）」なタプルのみを含んでいるかを追跡する、コンパクトなビットマップデータ構造です。「すべて可視」とは、現在および将来のすべてのトランザクションから可視であることを意味します。

VACUUMが実行されると、VMが更新されます。これは、特定の種類のクエリ、すなわち**インデックスオンリースキャン（index-only scans）**に対して絶大なパフォーマンス上の影響を与えます。

インデックスに完全に含まれるカラムをクエリする場合（例：SELECT user_id FROM event_logs WHERE processed_at > NOW() - '1 day'）、そしてVMが、インデックスが指し示すページ上のすべてのタプルがすべて可視であることを確認できた場合、Postgresはインデックスのみを読み取ることでクエリに答えることができます。メインテーブルのヒープにアクセスする必要がありません。これは非常に高速です。

バキュームの頻度が低いためにVMが古くなっていると、Postgresはタプルの可視性について確信が持てません。その結果、xmin/xmaxの値を確認するために、すべての行に対してインデックススキャンに続くヒープフェッチを実行せざるを得なくなります。これはインデックスオンリースキャンの利点を完全に無効にし、不可解なクエリの遅さの一般的な原因となります。

積極的な自動バキュームチューニングは、単に領域を回収するためだけではありません。クエリプランナの最も強力な最適化を可能にするために、可視性マップを最新の状態に保つためでもあるのです。

インタラクティブFAQ

知識を確認する（インタラクティブクイズ）

結論：肥大化のないデータベースのための行動計画

データベースの肥大化は、「もし」の問題ではなく、「いつ」の問題です。動的な書き込み負荷を持つあらゆるシステムにとって、受動的なアプローチは最終的なパフォーマンスの低下を保証します。プロアクティブで多層的な戦略が、長期的な健全性には不可欠です。

あなたの行動計画：

1. 監視： ここで提供した肥大化検知クエリを使用してください。週次で実行するようにスケジュールし、その結果を監視システムに送り、肥大化のしきい値（例：25%）を超えたテーブルについてアラートを出すようにします。
2. プロアクティブなチューニング： pg_stat_user_tablesを使用して、書き込みの多いトップ10〜20のテーブルを特定します。すぐに、テーブルごとに積極的な自動バキューム設定を適用してください。肥大化が問題になるまで待ってはいけません。
3. 安全な修復： すでに著しく肥大化しているテーブルを見つけたら、トラフィックの少ない時間帯にpg_repack操作をスケジュールします。ダウンタイムは最小限ですが、それでも主要な操作は注意深く実行するのがベストプラクティスです。
4. 啓蒙： チームが長時間実行トランザクションの影響を理解するように徹底してください。PgBouncerのidle_in_transaction_session_timeoutのような接続プーラーの設定を実装し、アイドル状態でありながらまだオープンなトランザクションを自動的に終了させます。

この検知、予防、そして修復のサイクルをマスターすることで、あなたは受動的な火消し役から戦略的なデータベースエンジニアへと移行し、PostgreSQLインフラストラクチャが大規模になっても高速で、効率的で、信頼性を保てるように保証するのです。

さらに読む

• PostgreSQL Documentation: The Autovacuum Daemon
• PostgreSQL Documentation: VACUUM
• The pg_repack Official Documentation
• The Visible Internals of PostgreSQL: A Deep Dive into MVCC (架空の関連記事)