インデックスをメンテナンスする〜PostgreSQLのきほん〜

はじめに

この記事では

なぜインデックスをメンテナンスしなければならないのか？？
インデックスのメンテナンスでは何に注意してどう対策するか？？

についてまとめました。

PostgreSQLを本番運用する際には役に立つ内容かと思います。

なぜインデックスのメンテナンスが必要なのか？？

インデックスファイルの肥大化とその対策

PostgreSQLは追記型アーキテクチャであるため、

インデックス情報が更新されると

更新前を不要領域として残したまま更新の情報を新規登録します。

そのためこれを放置すると、

インデックスファイルが肥大化して目的のデータへアクセスするまでに

無駄なディスクI/Oが発生します。

不要領域に無駄にアクセスする例

そのためバキュームを行うことで、

定期的に不要領域を再利用可能な状態にしてやる必要があります。

バキュームについてはこちらの記事の

自動バキュームランチャと自動バキュームワーカを併せてご覧ください。

2023年12月3日内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜

PostgreSQLのデフォルト設定では自動バキュームがonになっているため、

特に意識しなくても不要領域はメンテナンスされ

インデックスファイルの肥大化への対策を行う必要はありません。

肥大化の傾向を取得するクエリ

SELECT relname, relpages, reltuples FROM pg_class WHERE relname = `インデックス名`

インデックスファイル断片化とその対策

インデックスファイルはページ単位で区切られ、

ツリー構造になってポインタでルートから辿って

最終的にテーブルファイルの該当レコードにアクセスできます。

インデックスファイルの実体イメージ

テーブルの更新に伴いインデックスは断片化していきます。

例えば、

上の図のページ3のリーフ部のデータ量が増加して一杯になると、

SPLITが発生して分割し、左右に50%ずつのデータが入ります。

これが断片化です。

ページが分割されるイメージ

インデックスの大部分のページで断片化が進むと、

狭い範囲のデータが複数ページに点在することになるので、

キャッシュヒット率が悪化し性能に悪影響を及ぼします。

ちなみにツリー構造の一番右端のページでSPLITが発生すると、

左のページにできるだけデータを詰めてSPLITするため、

昇順にデータが追加されるだけのインデックスであれば断片化の影響を避けられます。

断片化の傾向を取得するクエリ

SELECT * FROM pgstatindex(`インデックス名`)

leaf_fragmentationを確認

サーバに負荷がかかるので調査するタイミングには要注意！！

対策としては、インデックスを再構築します。

インデックスを再構築するクエリ

-- 対象インデックスのみ再構築
REINDEX (VERBOSE) INDEX インデックス名

-- 対象テーブル上の全てのインデックスを再構築
REINDEX (VERBOSE) TABLE テーブル名

-- ロックせずにインデックスを再構築
REINDEX TABLE CONCURRENTLY テーブル名

REINDEX時は対象テーブルがロックされるので更新できなくなることに注意。

CONCURRENTLYオプションをつければロックを取得しないため、

実行中も対象テーブルの更新は可能となります。

クラスタ性の欠落とその対策

クラスタ性とはテーブルデータの物理的な配置順序のことで、

インデックスの並び順とテーブルの並び順が近いと効率的に検索できます。

運用している間にテーブルデータの物理的な配置順序が

インデックスの並び順と乖離した状態になることをクラスタ性の欠落と呼び、

インデックスキャンをしても最終的にテーブルデータを取得する際、

複数のページへのアクセスが必要になりI/Oの発行回数が増加して性能に影響が出ます。

クラスタ性の欠落イメージ

上の図のインデックスページ1で該当データをインデックススキャンした後、

テーブルも同じようにページ1内で収まっていれば

テーブルのページ1を参照するだけで終わりなのですが、

複数ページに散らばっているためその分参照しなければならず

性能が悪くなります。

クラスタ性を確認するクエリ

SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = `テーブル名`

correlationが1に近ければ綺麗に並んでいる

対策としては、

CLUSTERコマンドでクラスタ性を復活させます。

クラスタ性を復活させる

--クラスタ性を復活させる
CLUSTER テーブル名 USING インデックス名

-- CLUSTER実行時の基準となるインデックスを確認する
SELECT relname FROM pg_class
WHERE oid = (SELECT indexrelid from pg_class c, pg_index i 
                        WHERE c.oid = i.indrelid AND i.indisclustered = 't'
                        AND c.relname = 'テーブル名')

-- CLUSTER実行時の基準にインデックスを登録する
ALTER TABLE テーブル名 CLUSTER ON インデックス名

-- CLUSTER実行時の基準からインデックスを削除する
ALTER TABLE テーブル名 SET WITHOUT CLUSTER

PostgreSQLではクラスタ性を持つインデックスの定義はできないため、

CLUSTERコマンドは一時的な効果しかありません。

CLUSTER実行時はREINDEXも実行されているため、

CLUSTERコマンドのみで断片化とクラスタ性の改善は同時に行えます。

ただし、

CLUSTERとVACUUM FULLはほぼ同じロジックを利用するため

これらのことには注意が必要です。