よーし、来たね！今日はPostgreSQLのインデックス保守について、みっちり実践的な話をしよう。
「インデックスって、一度作ったら終わりじゃないんですか？」
たまにそう聞かれることがあるんだけど、残念ながらそれは甘い考え方だよ。インデックスも、実は生き物みたいなもので、使っていくうちにだんだんパフォーマンスが落ちてきたり、肥大化したりするんだ。放っておくと、せっかくインデックスを作ったのに、かえってデータベースの足かせになっちゃうこともあるんだよ。
今日は、そんなインデックスの「健康診断」と「メンテナンス」について、REINDEXコマンドの活用から、オンラインで安全に再構築する方法まで、現場で本当に役立つ知識を伝授するよ。さあ、一緒にインデックスの深淵を覗きに行こうか！
—
1. REINDEXコマンドでインデックスを生まれ変わらせる
インデックスの調子が悪くなったら、まず考えるのが`REINDEX`コマンドだね。これは、既存のインデックスを最初から作り直す、いわば「リフレッシュ」操作なんだ。
なぜREINDEXが必要になるのか？
PostgreSQLのインデックス、特にB-treeインデックスは、データが更新（UPDATE）されたり、削除（DELETE）されたりすると、その変更履歴が内部に残ることがある。完全に削除されずに「不要になったページ」や「空き領域」が増えていくんだ。これが続くと、インデックスの物理的なサイズが大きくなりすぎたり、インデックス内のデータが飛び飛びに配置されたりして、効率が悪くなる。これが「断片化」や「肥大化」だね。
REINDEXは、これらの不要な部分を整理し、インデックスを最適な状態に再構築してくれるんだ。具体的には：
- 肥大化の解消: 無駄なディスクスペースを解放して、インデックスサイズを小さくする。
- 断片化の解消: 物理的に連続した領域にデータを再配置し、I/O性能を向上させる。
- 統計情報の更新: インデックスの統計情報を最新の状態にし、オプティマイザがより適切な実行計画を選択できるようにする（これはVACUUM ANALYZEでも行われるけど、REINDEXも再構築時に統計情報を使う）。
REINDEXの基本的な使い方
REINDEXには、特定のインデックスだけを再構築するモードと、テーブルに紐づく全てのインデックスを再構築するモードがあるよ。
— 特定のインデックスを再構築する場合
REINDEX INDEX my_schema.my_index_name;
— あるテーブルに属する全てのインデックスを再構築する場合
REINDEX TABLE my_schema.my_table_name;
— データベース内の全てのインデックスを再構築する場合 (これは慎重に！)
REINDEX DATABASE my_database_name;
— システムカタログを含む全てのインデックスを再構築する場合 (ほとんど使うことはないけど)
REINDEX SYSTEM my_database_name;
実務では、特定のインデックスか、せいぜい特定のテーブルのインデックスを再構築することがほとんどだね。データベース全体やシステムカタログの再構築は、相当な理由がない限り手を出さない方がいい。
REINDEXの注意点：排他ロック
この`REINDEX`コマンド、実は強力な排他ロック（`ACCESS EXCLUSIVE`ロック）を取得するんだ。これはどういうことかというと、コマンドが実行されている間、対象のテーブルに対して読み込みも書き込みも一切できなくなるということ。つまり、アプリケーションが停止してしまうんだ。
これは本番環境では致命的だよね。だから、安易に実行できるコマンドではない。じゃあどうすればいいのか？それが次の「CONCURRENTLY」オプションの話につながるんだけど、その前に、そもそもインデックスが本当に悪い状態なのかどうかを確認する方法を知っておこう。
—
2. インデックスの断片化をどうやって確認する？
インデックスの再構築を検討する前に、本当にそれが「断片化している」のか、「肥大化している」のかを確認することが重要だ。闇雲にREINDEXしても、効果がないどころか、無駄なダウンタイムを引き起こすだけだからね。
PostgreSQLには、インデックスの状態を把握するための便利なビューや関数がいくつか用意されているよ。
活用するビューと関数
- `pg_stat_user_indexes`: ユーザーが作成したインデックスに関する統計情報（スキャン回数、読み込みタプル数など）。
- `pg_class`: テーブルやインデックスなどのリレーションのメタ情報（ページ数、タプル数など）。
- `pg_relation_size()`: リレーションのディスク上のサイズ。
- `pg_size_pretty()`: バイト数を人間が読みやすい形式に変換してくれる便利関数。
具体的な確認クエリ例
1. インデックスのサイズとスキャン状況を確認する
まずは、どのインデックスがよく使われていて、どのくらいのサイズになっているのかをざっと見てみよう。
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan, — インデックススキャン数
idx_tup_read, — インデックススキャンで読み取られたタプル数
idx_tup_fetch — インデックスでフェッチされたテーブルタプル数
FROM
pg_stat_user_indexes
ORDER BY
idx_scan DESC;
このクエリで、インデックスの使われ方やサイズを把握できる。`idx_scan`が多いのに`idx_tup_fetch`が少ない場合、インデックスは使われているけど、結果的にテーブルへのアクセスが多く発生している、というような傾向が見えてくることもあるよ。
2. インデックスのページ数とタプル数の比率で肥大化を推測する
インデックスの断片化や肥大化を直接的に示す完璧なメトリクスはPostgreSQLにないんだけど、`pg_class`から得られる「ページ数（`relpages`）」と「タプル数（`reltuples`）」の比率から、ある程度推測することができるんだ。
1つのインデックスページ（通常8KB）に、どれくらいのインデックスタプルが格納されているか、という視点だね。効率的なインデックスなら、1ページあたりのタプル数が多いはず。
SELECT
n.nspname AS schema_name,
c.relname AS index_name,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
c.reltuples AS live_tuples, — インデックスに含まれる実タプル数
c.relpages AS total_pages, — インデックスが使用しているページ数
CASE
WHEN c.reltuples > 0 THEN (c.relpages 8192.0 / c.reltuples)
ELSE 0
END AS bytes_per_tuple — 1タプルあたりの平均バイト数（目安）
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
c.relkind = ‘i’ — インデックスのみを対象
AND n.nspname NOT IN (‘pg_catalog’, ‘information_schema’) — システムカタログを除外
ORDER BY
bytes_per_tuple DESC NULLS LAST;
この`bytes_per_tuple`という指標が高いインデックスは、1タプルあたりのディスクスペース消費量が多い、つまり肥大化している可能性がある、と考えられる。これはあくまで目安だけど、私の経験上、かなり有効な指標だよ。
どれくらいの値で再構築を検討する？
これはデータ型やインデックスの種類にもよるから一概には言えないんだけど、一般的な数値型や短い文字列のインデックスで、`bytes_per_tuple`が数十〜数百バイトを超えてくるようだと、一度REINDEXを検討する価値があるかもしれない。
最終的には、再構築前後のクエリ性能を比較したり、`EXPLAIN ANALYZE`の結果を見て、本当に効果があったかどうかを確認するのが一番確実だよ。
—
3. CONCURRENTLYオプションを用いたオンライン再構築
さて、インデックスの肥大化が確認できて、REINDEXが必要だと判断したとしよう。でも、さっき言ったように、通常の`REINDEX`は排他ロックを取ってしまうから、本番環境ではなかなか実行できない。そこで登場するのが、`CONCURRENTLY`オプションだ。
CONCURRENTLYの偉大な力
`CONCURRENTLY`オプションを使うと、対象のテーブルに対して最小限のロックしかかけずにインデックスを再構築できるんだ。つまり、インデックスを再構築している間も、アプリケーションはテーブルに対して読み書きを続けることができる。これは本番運用ではめちゃくちゃ助かる機能だよね！
`CONCURRENTLY`は、`CREATE INDEX`と`REINDEX INDEX`、そしてPostgreSQL 12からは`REINDEX TABLE`でも使えるようになった。
— 新しいインデックスを並行して作成する場合
CREATE INDEX CONCURRENTLY idx_my_table_col1 ON my_schema.my_table_name (col1);
— 既存のインデックスを並行して再構築する場合 (PostgreSQL 12以降)
REINDEX INDEX CONCURRENTLY my_schema.my_index_name;
— テーブルに属する全てのインデックスを並行して再構築する場合 (PostgreSQL 12以降)
REINDEX TABLE CONCURRENTLY my_schema.my_table_name;
CONCURRENTLYが「オンライン」でできる仕組み
「どうやってロックなしで再構築してるの？」と疑問に思うよね。CONCURRENTLYは、こんな賢い手順を踏むんだ。
1. 新しいインデックスの作成: まず、既存のインデックスとは別に、全く新しいインデックスをバックグラウンドで作成する。このとき、テーブルの最初のスキャンが行われる。
2. 変更履歴の追跡: 新しいインデックスを作成している間に、対象テーブルに加えられた変更（INSERT, UPDATE, DELETE）をログに記録しておく。
3. 変更の適用: 新しいインデックスがほぼ完成したら、記録しておいた変更履歴を新しいインデックスに適用する。このとき、テーブルの2回目のスキャンが行われる。
4. アトミックな置き換え: 最終的に、既存のインデックスと新しいインデックスをアトミック（不可分）に置き換える。この瞬間だけ、ごく短い期間（マイクロ秒単位）の`SHARE UPDATE EXCLUSIVE`ロックがかかるんだけど、これはテーブルへの読み書きをブロックしない程度の弱いロックなんだ。
5. 古いインデックスの削除: 役目を終えた古いインデックスは自動的に削除される。
この一連のプロセスで、テーブルへの`ACCESS EXCLUSIVE`ロックが回避され、アプリケーションのダウンタイムが発生しない、というわけだ。素晴らしい技術だよね！
CONCURRENTLYのメリット・デメリット
メリット
- ダウンタイムなし: アプリケーションへの影響を最小限に抑えつつ、インデックスを再構築できる。
- 高可用性: 24時間365日稼働が求められるシステムで、安心してインデックスメンテナンスができる。
デメリット・注意点
1. 実行時間が長い: テーブルを2回スキャンする必要があるため、通常のREINDEXよりも実行に時間がかかる。大きなテーブルだと数時間かかることもある。
2. 一時的なディスク使用量の増加: 古いインデックスと新しいインデックスが一時的に両方存在するため、ディスク使用量が約2倍になる。十分な空き容量があるか確認しておこう。
3. 失敗時の挙動: 何らかの理由で`REINDEX CONCURRENTLY`が失敗すると、中途半端な状態のインデックスが「`INVALID`」状態でデータベースに残ってしまうことがある。これは手動で削除する必要があるんだ。
— INVALIDなインデックスが残っていないか確認
SELECT
c.relname, i.indisvalid, i.indisready, i.indisprimary
FROM
pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE
c.relname = ‘idx_my_table_col1’; — 失敗したインデックス名
— INVALIDなインデックスの削除
DROP INDEX my_schema.idx_my_table_col1; — CONCURRENTLYなしでDROPできる
この`INVALID`なインデックスは、使われないまま残ってしまうと、データベースのカタログを汚したり、将来的に同じ名前のインデックスを作ろうとしたときにエラーになったりする原因になるから、見つけたらすぐに削除しようね。
4. ロックの競合: `CONCURRENTLY`でも、ごく稀に他のトランザクションとのロック競合が発生することがある。特に、テーブルに対するDDL操作（ALTER TABLEなど）が同時に実行されると、どちらかがブロックされる可能性がある。運用時は、DBへの負荷が低い時間帯を選ぶなど、計画的に実行するのがおすすめだよ。
—
まとめ：インデックス保守は「継続的な健康診断」
どうだったかな？インデックスの保守は、データベースのパフォーマンスを維持する上で非常に重要な作業なんだ。インデックスは「作って終わり」じゃなくて、「作ってからも育てていく」ものだと考えてほしい。
今日話したことをまとめると：
- REINDEXはインデックスをリフレッシュする強力なコマンドだけど、排他ロックに注意が必要。
- `pg_stat_user_indexes`や`pg_class`を使って、インデックスの肥大化や断片化の兆候を定期的にチェックしよう。`bytes_per_tuple`は良い目安になるよ。
- `CONCURRENTLY`オプションを使えば、オンラインで安全にインデックスの再構築ができる。ただし、実行時間の長さ、一時的なディスク使用量の増加、失敗時の対処法はしっかり覚えておこう。
データベースの健全性を保つためには、定期的なインデックスの「健康診断」と、必要に応じた「メンテナンス」が不可欠だ。自分のシステムの特性や負荷状況に合わせて、最適なインデックス保守のサイクルを見つけてみてほしい。
もし、実際に作業してみて「あれ、これってどういうことだろう？」とか「この結果の見方、ちょっと自信ないな」って思ったら、いつでも相談してくれ。一緒に解決策を考えよう！
コメント