【テクニカル・上級編】インデックスの保守 – PostgreSQL

熟練のPostgreSQL使いの皆さん、日々の運用でインデックスの健全性を意識していますか？データベースのパフォーマンスチューニングにおいて、インデックスはまさにアプリケーションの生命線とも言える存在です。しかし、一度作成すれば終わり、というものではありません。データが更新され続ける限り、インデックスもまた時間とともに「疲弊」していくものです。

今回は、PostgreSQLのインデックス保守における奥義、特に`REINDEX`コマンドの活用と、ダウンタイムを最小限に抑えるオンライン再構築の戦略について、内部アーキテクチャの視点も交えながら深く掘り下げていきましょう。

インデックスの断片化、その実態

インデックスの保守を語る上で避けて通れないのが「断片化」の問題です。これは、インデックスのページ内にデッドタプル（更新や削除によって論理的に無効になったエントリ）が蓄積されたり、頻繁なページ分割（SPLIT）によって物理的な連続性が失われたりすることで発生します。

なぜ断片化は起こるのか？

PostgreSQLのMVCC（Multi-Version Concurrency Control）モデルと`VACUUM`の挙動が、インデックスの断片化と密接に関わっています。

1. デッドインデックスエントリの蓄積:

テーブルのタプルが更新されると、新しいタプルが作成され、古いタプルはデッドマークされます。これに伴い、インデックスにも新しいエントリが追加され、古いエントリは論理的に無効となります。
`VACUUM`はデッドタプルを物理的に削除しますが、インデックス内のデッドエントリは必ずしもすぐに再利用されません。特にB-treeインデックスでは、ページ内のスペースが再利用されることはありますが、デッドエントリがページを占有し続けることで、インデックスの「密度」が低下します。
`HOT (Heap-Only Tuples)`更新が可能な場合、テーブルのヒープ内だけで更新が完結し、インデックスの更新は発生しません。これはインデックスの肥大化を抑制する上で非常に有効ですが、すべての更新がHOT更新になるわけではありません。

2. ページ分割 (SPLIT):

インデックスのページが満杯になり、新しいエントリを挿入するスペースがない場合、PostgreSQLはそのページを2つに分割します。このプロセス自体は正常な動作ですが、頻繁に発生するとインデックスの物理的な連続性が失われ、ディスク上のブロックが散らばって配置されることになります。
結果として、インデックススキャン時にディスクI/Oが増加し、OSやデータベースのキャッシュ効率が低下する可能性があります。

断片化がパフォーマンスに与える影響

断片化が進んだインデックスは、主に以下の点でパフォーマンスを悪化させます。

I/Oの増加: インデックススキャンが必要なデータを読み込むために、より多くのディスクブロックにアクセスする必要が生じます。これは特に、インデックス全体をスキャンするようなクエリ（例: 範囲スキャン）で顕著です。
キャッシュ効率の低下: 物理的に分散したインデックスブロックは、データベースバッファやOSのページキャッシュに効率的に収まらず、キャッシュヒット率の低下を招きます。
メモリ使用量の増加: 同じ情報量を保持するために、より多くのメモリを必要とします。

断片化の確認方法

PostgreSQLはインデックスの内部構造を直接可視化する機能は提供していませんが、システムカタログや統計情報ビューからその兆候を掴むことはできます。

`pg_class`と`pg_relation_size()`:

インデックスの物理サイズ（`pg_relation_size(index_oid)`）と、インデックスがカバーしているテーブルのタプル数（`pg_class.reltuples`）を比較することで、インデックスの肥大化を推測できます。

SELECT
c.relname AS index_name,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
c.reltuples AS indexed_tuples,
(pg_relation_size(c.oid) / c.reltuples) AS bytes_per_tuple — 肥大化の目安
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname = ‘public’ — スキーマ名に合わせて変更
AND c.relkind = ‘i’ — インデックスのみ
ORDER BY
bytes_per_tuple DESC;

`bytes_per_tuple`が非常に大きい場合、インデックスが肥大化している可能性があります。ただし、インデックスの列数やデータ型にも依存するため、あくまで目安です。

`pg_stat_user_indexes`:

このビューは、インデックスのスキャン回数や読み取られたタプル数、フェッチされたタプル数などの統計を提供します。インデックスがほとんど利用されていないのにサイズが大きい場合、そのインデックス自体が不要であるか、肥大化している可能性があります。

これらの情報から、怪しいインデックスを見つけ出し、再構築の要否を検討するのが賢明です。

REINDEXコマンドの活用

インデックスの断片化を解消し、その物理的な構造を最適化するための最も直接的な手段が`REINDEX`コマンドです。

REINDEXの基本的な使い方

`REINDEX`コマンドは、対象に応じていくつかのバリエーションがあります。

`REINDEX INDEX index_name;`: 特定のインデックスを再構築します。
`REINDEX TABLE table_name;`: 特定のテーブルに関連するすべてのインデックスを再構築します。
`REINDEX DATABASE database_name;`: データベース内のすべてのユーザーインデックスを再構築します。
`REINDEX SYSTEM database_name;`: データベース内のすべてのシステムカタログインデックスを再構築します。これは非常に強力であり、慎重に使うべきです。

REINDEXの内部動作とロック

`REINDEX`が内部でどのように動くかを知ることは、その影響を理解する上で不可欠です。

1. 新しいインデックスの構築: `REINDEX`は、対象となるインデックスと同じ定義で、新しいインデックスをゼロから構築します。この際、既存のテーブルデータをスキャンし、新しいB-tree構造を作成します。
2. 古いインデックスの削除: 新しいインデックスが完全に構築されると、古いインデックスは削除されます。
3. ロックの適用: このプロセス全体、特に新しいインデックスの構築中は、対象のテーブルに対して`ACCESS EXCLUSIVE`ロックが適用されます。

この`ACCESS EXCLUSIVE`ロックが肝です。このロックが取得されている間、対象テーブルへの読み書きを含むあらゆる操作がブロックされます。 つまり、`REINDEX`を実行する間は、そのテーブルに対するアプリケーションからのアクセスが完全に停止するのです。

大規模なテーブルに対する`REINDEX`は、数分、場合によっては数時間かかることがあります。これは本番環境においては許容できないダウンタイムとなり得ます。だからこそ、`REINDEX`の利用には細心の注意と計画が必要です。メンテナンスウィンドウが確保できる場合や、アプリケーションのアクセスが少ない時間帯に限定して実行するのが基本戦略となります。

オンライン再構築の切り札：CONCURRENTLYオプション

「ダウンタイムは許容できないが、インデックスは再構築したい」。そんなジレンマに直面したとき、PostgreSQLの`CONCURRENTLY`オプションが救世主となります。

`REINDEX CONCURRENTLY`は存在しない？

PostgreSQLには残念ながら、直接的な`REINDEX CONCURRENTLY`というコマンドは存在しません。（PostgreSQL 12までは存在していましたが、現在は非推奨となり、実質的には`CREATE INDEX CONCURRENTLY`と`DROP INDEX`の組み合わせが推奨されています。）

しかし、`CREATE INDEX CONCURRENTLY`を巧妙に利用することで、既存のインデックスをオンラインで再構築するのと同等の効果を得ることができます。

その手順は以下のようになります。

1. 既存のインデックスと同じ定義で、別の名前で新しいインデックスを`CREATE INDEX CONCURRENTLY`を使って作成します。
2. 新しいインデックスが完成し次第、元のインデックスを`DROP INDEX`します。
3. （オプション）アプリケーションが元のインデックス名を参照している場合、新しいインデックスを元の名前に`ALTER INDEX … RENAME TO …`で変更します。

`CREATE INDEX CONCURRENTLY`の内部動作

`CREATE INDEX CONCURRENTLY`は、その「並行性」を実現するために、通常の`CREATE INDEX`よりも複雑なステップを踏みます。

1. 初期スキャンとインデックス構築:

まず、対象テーブルに対し`SHARE UPDATE EXCLUSIVE`ロックを取得します。これは、テーブルの構造変更（`ALTER TABLE`など）を禁止しますが、読み書き操作は許可します。
テーブルを一度スキャンし、既存のデータを基に新しいインデックスを構築します。

2. 二度目のスキャンと更新:

インデックス構築中にコミットされたテーブルへの変更を反映させるため、再度テーブルをスキャンします。この間も`SHARE UPDATE EXCLUSIVE`ロックは保持されます。
このフェーズで、インデックス構築開始後に発生した新しいタプルや更新されたタプルに対応するインデックスエントリを追加します。

3. インデックスの有効化:

全ての変更が反映されると、新しいインデックスは有効な状態となり、クエリプランナーによって利用可能になります。
この一連の操作は、テーブルに対する書き込みトランザクションをブロックすることなく進行します（ただし、インデックス作成中のテーブルに対する書き込みトランザクションは、インデックス作成が完了するまでコミットが待たされる可能性がありますが、これは通常非常に短い時間です）。

`CONCURRENTLY`オプションのメリットとデメリット

メリット

ダウンタイムなし: 対象テーブルへの読み書きをブロックすることなくインデックスを再構築できます。これは本番環境において最大のメリットです。
オンライン操作: サービスを停止することなく、インデックスの健全性を回復させることができます。

デメリットと注意点

実行時間の増加: 通常の`CREATE INDEX`がテーブルを一度スキャンするのに対し、`CONCURRENTLY`オプションは二度スキャンするため、実行時間が長くなります。
一時的なディスク使用量の増加: 古いインデックスと新しいインデックスが同時に存在するため、一時的にディスク使用量が約2倍になります。十分な空き容量を確認しておく必要があります。
失敗時の挙動: `CREATE INDEX CONCURRENTLY`が何らかの理由で失敗した場合、中途半端な`INVALID`状態のインデックスが残ることがあります。これは手動で`DROP INDEX`する必要があります。
ロックの性質: `SHARE UPDATE EXCLUSIVE`ロックは、テーブル構造の変更（ALTER TABLE）をブロックします。もしインデックス作成中にALTER TABLEを実行する予定があるなら注意が必要です。

インデックス保守のポリシーとプラクティス

インデックスの保守は、闇雲に実施するものではありません。システムの特性と監視結果に基づいた、戦略的なアプローチが求められます。

「本当に必要か？」の問いかけ

インデックスの断片化や肥大化は、必ずしもパフォーマンス問題に直結するわけではありません。むしろ、再構築による負荷の方が大きい場合もあります。重要なのは、実際にパフォーマンスが悪化しているのか、あるいはその兆候があるのかを見極めることです。

`pg_stat_user_indexes`の`idx_scan`や`idx_tup_read`、`idx_tup_fetch`などの統計情報に加え、システムのI/Oメトリクス（ディスク読み込み量、IOPSなど）を監視し、相関関係を分析することが重要です。
`EXPLAIN ANALYZE`を用いて、特定のクエリが遅くなっている原因がインデックススキャンにあるのかを特定します。

自動VACUUMとの連携

`autovacuum`は、デッドタプルの回収を自動的に行うことで、インデックスのデッドエントリがページ内で再利用される機会を増やします。`autovacuum`の設定（特に`autovacuum_vacuum_scale_factor`や`autovacuum_vacuum_threshold`）が適切であるかを確認し、必要に応じて調整することで、インデックスの断片化の進行をある程度抑制できます。`fillfactor`の設定も、インデックスページの空き容量を調整し、ページ分割を遅らせるのに役立ちます。

継続的な監視と計画

インデックスの状態は、継続的に監視すべきメトリクスの一つです。前述の`pg_class`や`pg_stat_user_indexes`の情報を定期的に収集し、トレンドを分析することで、問題の兆候を早期に捉えることができます。

インデックスのサイズが急激に増加していないか？
インデックススキャンにおけるI/Oが増加していないか？

これらの兆候が見られた場合、計画的なインデックス再構築を検討します。特に大規模なテーブルのインデックス再構築は、必ずテスト環境で事前に実行時間を計測し、本番環境への影響を評価した上で実行計画を立てましょう。

まとめ

PostgreSQLのインデックス保守は、単なるコマンド実行ではありません。それは、データベースの内部構造への深い理解と、システム全体のパフォーマンスに対する洞察を要求される、まさに「芸術」とも言える領域です。

`REINDEX`は強力なツールですが、そのロックの特性を理解せずに使うと、予期せぬダウンタイムを引き起こす可能性があります。一方で、`CREATE INDEX CONCURRENTLY`を巧妙に活用することで、サービスを停止することなくインデックスの健全性を回復させる道も開かれています。

闇雲な再構築ではなく、監視に基づいて「本当に必要か？」を問いかけ、適切なタイミングで、最も影響の少ない方法を選択する。これこそが、熟練のDBAに求められるインデックス保守の真髄ではないでしょうか。

常にデータと対話し、PostgreSQLの内部を深く見つめることで、皆さんのシステムはさらなる高みへと到達するでしょう。