PostgreSQLのインデックスメンテナンス：REINDEXとVACUUM FULLの使い分け

PostgreSQLデータベースで最適なパフォーマンスを維持するには、インデックスの効率性が鍵となります。更新や削除の頻度が高いと、時間の経過とともにインデックスが肥大化したり断片化したりする可能性があり、クエリの実行速度が遅くなったり、ストレージ消費量が増加したりします。この劣化は単なる些細な問題ではなく、アプリケーションの応答性やシステム全体の健全性に著しく影響を与える可能性があります。この問題に対処するために利用できるツール、特にREINDEXとVACUUM FULLを理解し、それらをいつ、どのように適用するかを知ることは、データベース管理者や開発者にとって不可欠です。この記事では、これら2つの強力なコマンドについて、その基礎となるメカニズム、ユースケース、およびPostgreSQLインデックスを最高の状態に保つための実践的な影響を掘り下げていきます。

基本の理解：ブロート（肥大化）、インデックス、MVCC

REINDEXとVACUUM FULLについて詳しく説明する前に、いくつかのコアコンセプトに簡単に触れましょう。

インデックスブロート（Index Bloat）: PostgreSQLでは、行が更新または削除されると、それらの行の古いバージョン（および対応するインデックスエントリ）はすぐに削除されません。代わりに、「デッドタプル（dead tuples）」としてマークされます。autovacuumが最終的にこれらをクリーンアップしますが、変更率が高いと、デッドタプルの蓄積が顕著になり、インデックスが必要以上に大きくなる原因となります。この不要なスペース消費と、より多くのデータページをスキャンする必要性が、「インデックスブロート」を構成します。ブロートは、ディスクからより多くのデータを読み取る必要があり、キャッシュの利用効率が低下するため、パフォーマンスに直接影響します。

PostgreSQLインデックス: インデックスは、データベース検索エンジンがデータ取得を高速化するために使用できる特別なルックアップテーブルです。本質的には、インデックス列の値のソートされたリストであり、テーブル内の実際の行へのポインタが含まれています。クエリがインデックス列を使用すると、PostgreSQLはテーブル全体をスキャンすることなく、関連する行をすばやく見つけることができます。

MVCC（Multi-Version Concurrency Control）: PostgreSQLは、テーブル全体をロックすることなく並行トランザクションを処理するためにMVCCを実装しています。行が更新されると、行の新しいバージョンが作成され、古いバージョンは更新前に開始されたトランザクションから引き続き参照可能になります。このメカニズムは、並行処理には優れていますが、デッドタプル、ひいてはブロートの根本原因となっています。

REINDEX：インデックスの段階的な再構築

REINDEXは、既存の1つ以上のインデックスを再構築するために使用されるコマンドです。REINDEXを実行すると、PostgreSQLは古いインデックスを削除（またはそのエントリを無効としてマーク）し、テーブルの現在のライブデータを使用して完全に新しいインデックスを再作成します。このプロセスは、古いインデックス構造に存在するブロートをすべて排除します。デッドタプルはすべて破棄され、新しいインデックスはアクティブなポインタのみで構築されます。

REINDEXの仕組み

概要レベルでは、REINDEXは以下を含みます：

1. テーブルのスキャン: すべてのライブ行を識別するためにテーブル全体を読み取ります。
2. 新しいインデックスの構築: ライブ行に基づいて、完全に新しいインデックス構造を構築します。
3. 古いインデックスの置き換え: 新しいインデックスが構築されると、それが古いインデックス atomically に置き換わります。

REINDEXの使用時期

REINDEXは、インデックスブロートに対処するための主要なツールです。以下のようなシナリオで、使用を検討してください：

• 大幅なインデックスブロート: pg_stats_reportsまたはpg_bloat_checkツールが、インデックスに高い割合のブロートを示している場合。
• パフォーマンス劣化: 特定のインデックスを利用するクエリが著しく遅くなっている場合、インデックスブロートが原因であると疑われる場合。
• 破損: まれに、インデックスが破損した場合、REINDEXはテーブルデータから再構築することにより、効果的に修復できます。
• インデックスパラメータの変更: 既存のインデックスに新しいストレージパラメータまたはfillfactor設定を適用する場合。

REINDEXの実践的な例

例を挙げて説明しましょう。productsテーブルとidx_product_nameインデックスがあるとします。

-- サンプルテーブルとインデックスの作成
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2)
);

CREATE INDEX idx_product_name ON products (name);

-- データの挿入
INSERT INTO products (name, price) SELECT 'Product ' || generate_series(1, 10000), random() * 100;

-- 多くの行を更新してブロートをシミュレート
UPDATE products SET name = 'Updated Product ' || id WHERE id > 5000;
DELETE FROM products WHERE id BETWEEN 100 AND 200;

-- ブロートの確認が可能（特定の拡張機能またはクエリが必要）
-- デモンストレーションのために、ブロートが発生したと仮定します。

-- 特定のインデックスを再インデックス化
REINDEX INDEX idx_product_name;

-- テーブル上のすべてのインデックスを再インデックス化
REINDEX TABLE products;

-- データベース上のすべてのインデックスを再インデックス化（非常にリソースを消費する可能性あり）
REINDEX DATABASE my_database;

REINDEXに関する重要な考慮事項：

• ロック: REINDEXは、インデックス（またはテーブル、またはデータベース）に対してACCESS EXCLUSIVEロックを取得する可能性があり、実行中に影響を受けるオブジェクトの読み書きを防ぎます。このダウンタイムは、高可用性システムでは許容できない場合があります。
• CONCURRENTLYオプション: テーブル上のインデックスの場合、PostgreSQLはREINDEX INDEX CONCURRENTLYとREINDEX TABLE CONCURRENTLYを提供します。このオプションにより、ACCESS EXCLUSIVEロックを取得せずに再インデックスを実行でき、本番環境に適しています。テーブルを2回スキャンし、より多くの時間と一時ディスクスペースを必要とします。

  REINDEX INDEX CONCURRENTLY idx_product_name;

  この並行して構築されたインデックスは、並行DML操作をブロックすることなく元のインデックスを置き換えます。CONCURRENTLYはREINDEX DATABASEまたはREINDEX SYSTEMでは利用できないことに注意してください。

VACUUM FULL：テーブルとインデックスのスペースの回収

VACUUM FULLは、VACUUMよりもはるかに攻撃的なコマンドです。通常のVACUUMはデッドタプルを再利用のためにマークするだけですが、VACUUM FULLはテーブル全体とその関連インデックスを、テーブルの完全に新しいバージョンを書き込むことによって最初から再構築します。これにより、テーブルデータ内およびインデックス内のデッドタプルスペースがすべて効果的に回収され、テーブルがディスク上で圧縮されます。

VACUUM FULLの仕組み

VACUUM FULLは以下のように動作します：

1. 新しいテーブルファイルの作成: ディスク上の新しい場所に、ライブ行のみを含むテーブルの新しいバージョンを書き込みます。
2. インデックスの再構築: このプロセスの一部として、すべての関連インデックスも新しいテーブル構造を指すように再構築されます。
3. 古いテーブルの置き換え: 新しいテーブルとそのインデックスが完了すると、古いテーブルとその関連インデックスファイルは削除されます。

VACUUM FULLの使用時期

VACUUM FULLは粗雑な手段であり、その深刻なロックの影響により、控えめに使用する必要があります。その主なユースケースは次のとおりです：

• 深刻なテーブルブロート: テーブル自体が、通常のVACUUM（autovacuumであっても）が効率的に回収できない大量のデッドスペースを蓄積した場合。これは、インデックスブロート自体というよりも、テーブル全体のストレージに関するものです。
• 特定のストレージシナリオ: ディスクスペースをすぐに回復する必要があり、長時間のダウンタイムが許容できると確信している、非常にまれなケース。

VACUUM FULLの実践的な例

productsテーブルの例を使用しましょう：

-- 大幅なテーブルとインデックスのブロートをシミュレート
-- （例：autovacuumが実行されずに、大量の行を削除する）
DELETE FROM products WHERE id > 7000;

-- VACUUM FULLを実行
VACUUM FULL products;

VACUUM FULLに関する重要な考慮事項：

• ロック: VACUUM FULLは、処理中のテーブルに対してACCESS EXCLUSIVEロックを取得します。これは、VACUUM FULLが完了するまで、そのテーブル（そして暗黙的にそのインデックス）に対する読み取りまたは書き込みが発生しないことを意味します。大きなテーブルの場合、このダウンタイムは数時間続く可能性があり、一般的に本番システムには適していません。
• ディスクスペース: 処理中のテーブルのサイズにほぼ等しい一時ディスクスペースが必要であり、テーブルの新しいコピーを作成します。
• インデックスへの影響: VACUUM FULLはテーブルの再構築の副作用としてインデックスブロートを排除しますが、その主な焦点はテーブルブロートにあります。インデックスブロートのみが懸念事項である場合、REINDEX（特にCONCURRENTLY）はほぼ常に推奨されるソリューションです。
• 代替手段: テーブルブロートについては、pg_repack（サードパーティユーティリティ）のようなツールを検討してください。これは、長期間ACCESS EXCLUSIVEロックを保持することなく、テーブルとインデックスをオンラインで再構築できます。

REINDEXとVACUUM FULLの使い分け

選択は、解決しようとしている特定の問題とダウンタイムに対する許容度にかかっています：

REINDEXを好む場合：

• 主にテーブル自体ではなく、インデックスにブロートが観測される場合。
• 破損したインデックスを修復したい場合。
• インデックスのストレージパラメータを変更したい場合。
• 最小限のダウンタイムが必要な場合（CONCURRENTLYを使用）。

VACUUM FULLを検討する場合（最大限の注意を払って）：

• テーブル自体が深刻にブロートしており、VACUUM（autovacuumであっても）が不十分な場合。
• 長時間のACCESS EXCLUSIVEロックが許容できるスケジュールされたメンテナンスウィンドウがある場合。
• テーブルの完全なダウンタイムを理解し、受け入れている場合。

ほとんどの現代的なPostgreSQLデプロイメントでは、REINDEX INDEX CONCURRENTLYがインデックスメンテナンスの標準的なソリューションです。テーブルブロートが繰り返される問題である場合は、VACUUM FULLに頼る前に、autovacuum設定の調整やpg_repackのような外部ツールの検討を推奨します。

結論

PostgreSQLのパフォーマンスを最適化するには、インデックスの健全性を維持することが最も重要です。REINDEXとVACUUM FULLの両方がブロートを排除し、スペースを回収できますが、その範囲、ロック動作、およびシステム可用性への影響は大きく異なります。REINDEXはインデックス専用に設計されており、そのCONCURRENTLYオプションはオンラインインデックス最適化の推奨方法となっています。一方、VACUUM FULLはテーブル全体に作用し、ダウンタイムの許容度が高いまれな状況でのみ使用する必要があります。適切なツールを適切に選択することで、データベースはサービスの中断を招くことなく、高速で効率的な状態を維持できます。