PostgreSQLのインデックスのメンテナンス方法についてまとめます。PostgreSQLはデータのINSERTとDELETEを繰り返すと、インデックスの性能が徐々に劣化します。もし、劣化したならば、インデックスを再作成したりクラスタ化する事で修復する事ができます。
- PostgreSQL インストール
- PostgreSQL データベースの作成
- PostgreSQL contribモジュールの使い方
- PostgreSQL パラメタの設定方法
- PostgreSQL ベンチマークツールの紹介
- PostgreSQL WAL(Write Ahead Log)の基本説明
- PostgreSQL バックアップとリストア
- PostgreSQL PITR(Point In Time Recovery)基本概念の説明
- PostgreSQL PITR(Point In Time Recovery)操作方法の説明
- PostgreSQL PITR(Point In Time Recovery)タイムライン操作
- PostgreSQL ストリーミングレプリケーションの最小構成
- PostgreSQL ストリーミングレプリケーションのパラメタ説明
- PostgreSQL ストリーミングレプリケーションの障害復旧
- PostgreSQL ストリーミングレプリケーションの多段構成
- PostgreSQL ストリーミングレプリケーションのDR向け設定
- PostgreSQL ストリーミングレプリケーションのコンフリクト
- PostgreSQL 自動バキューム(AUTO VACUUM)
- PostgreSQL HOT(Heap Only Tuple)
- PostgreSQL インデックスのメンテナンス (いまここ)
- PostgreSQL 統計情報の更新
- PostgreSQL 実行計画
- PostgreSQL スロークエリの調査方法
インデックスの性能劣化
PostgreSQLはインデックスの更新処理が発生すると、「肥大化」「断片化」「クラスタ性の欠落」が起きえます。
テストデータの準備
断片化の動作確認のため、staff_01とstaff_02の2つのテーブルを作成します。
createdb test psql test << EOF CREATE TABLE staff_01 ( number integer default 0, f_name varchar(30) default '', l_name varchar(30) default '' ); CREATE INDEX idx_number_01 ON staff_01 (number); INSERT INTO staff_01 (number) VALUES (generate_series(1,1000)); CREATE TABLE staff_02 ( number integer default 0, f_name varchar(30) default '', l_name varchar(30) default '' ); CREATE INDEX idx_number_02 ON staff_02 (number); INSERT INTO staff_02 (number) VALUES (generate_series(1,1000)); EOF
staff_02はDELETE, INSERTを繰り返し、インデックスの劣化が起きた状態にします。
psql test << EOF DELETE FROM staff_02 WHERE number % 2 = 0; INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); DELETE FROM staff_02 WHERE number % 2 = 0; INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); DELETE FROM staff_02 WHERE number % 2 = 0; INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); DELETE FROM staff_02 WHERE number % 2 = 0; INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); DELETE FROM staff_02 WHERE number % 2 = 0; INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); ANALYZE staff_02; EOF
肥大化の確認
INDEXへのDELETE, INSERTを繰り返すと、徐々にページ数が増加し、インデックスファイルが肥大化していきます。ページ数とタプル数は、pg_classテーブルから調べる事ができます。これは統計情報ですので、正確な値を把握したいならば、コマンド実行前にANALYZEしておくと良いでしょう。
staff_01とstaff_02を比較すると、DELETE, INSERTを繰り返す事によって、ページ数が5から11へ増えている事が分かります。
[postgres@centos10 ~]$ psql test << EOF
> SELECT relname, relpages, reltuples FROM pg_class WHERE relname LIKE 'idx_number%';
> EOF
relname | relpages | reltuples
---------------+----------+-----------
idx_number_01 | 5 | 1000
idx_number_02 | 11 | 1000
(2 rows)
断片化の確認
PostgreSQLのB-treeインデックスは、インデックスのあるページが一杯になると、左右２つのページに分割されます。この分割が繰り返される事によって断片化が発生します。
contribモジュールのpgstattupleに含まれるpgstatindex関数を使用する事で断片化の程度を確認する事ができます。leaf_fragmentationは断片化の程度を表しており、0は全く断片化していない状態を表します。
[postgres@centos10 ~]$ psql test << EOF
> \x
> CREATE EXTENSION pgstattuple ;
> SELECT * FROM pgstatindex('idx_number_01');
> SELECT * FROM pgstatindex('idx_number_02');
> EOF
Expanded display is on.
CREATE EXTENSION
-[ RECORD 1 ]------+------
version | 4
tree_level | 1
index_size | 40960
root_block_no | 3
internal_pages | 1
leaf_pages | 3
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 81.99
leaf_fragmentation | 0
-[ RECORD 1 ]------+------
version | 4
tree_level | 1
index_size | 90112
root_block_no | 3
internal_pages | 1
leaf_pages | 9
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 27.53
leaf_fragmentation | 33.33
クラスタ性の欠如
PostgreSQLは、テーブルデータの並び順をクラスタ性と呼びます。クラスタ性があれば、順次の読み込みが出来るためIOPSを少なくする事ができますが、並び順がランダムになればIOPSは増えて行きます。
クラスタ性は統計情報を示すpg_statのcorrelationから読み取る事ができます。1は昇順で-1は降順です。0に近づけば近づくほどランダムの状態である事を表します。correlationが1または-1が望ましい状態です。以下はstaff_02のクラスタ性が欠如している事が読み取れます。
[postgres@centos10 ~]$ psql test << EOF > SELECT tablename, attname, correlation FROM pg_stats WHERE tablename LIKE 'staff%' ; > EOF tablename | attname | correlation -----------+---------+------------- staff_02 | number | 0.5014985 staff_02 | f_name | 1 staff_02 | l_name | 1 staff_01 | number | 1 staff_01 | f_name | 1 staff_01 | l_name | 1 (6 rows)
インデックスメンテナンス
「肥大化」「断片化」「クラスタ性の欠如」の解消方法は複数通りあります。以下、解消方法について説明します。
インデックスの再作成
小規模の場合
インデックスを再作成する事で断片化を解消できます。
DROP INDEX idx_number ; CREATE INDEX idx_number ON staff (number);
REINDEXコマンドでも差し支えございません。
REINDEX INDEX idx_number ;
排他ロックの確認
DROP INDEX, CREATE INDEX, REINDEX INDEXは排他ロックを取得します。indexに伴う処理が一瞬で完了するような小規模データベースならば問題ないですが、indexの作成や削除に時間を要すような大きなデータベースでは排他ロックを考えなければなりません。
まずは排他ロックの動作確認のために、端末を2つ立ち上げます。
indexを削除します。
[postgres@centos10 ~]$ psql test -c "DROP INDEX idx_number ;"
staffテーブルにロックをかけるような処理を実施します。
[postgres@centos10 ~]$ psql test psql (13.1) Type "help" for help. test=# BEGIN; BEGIN test=*# INSERT INTO staff (number) VALUES (1001); INSERT 0 1 test=*#
別の端末を起動し、indexの作成を試みます。すると待ち状態になるのが分かります。
[postgres@centos10 ~]$ psql test -c "CREATE INDEX idx_number ON staff (number);"
1つ目の端末に戻り、ROLLBACKまたはCOMMITを行うと1つ目のCREATE INDEX処理が完遂します。
test=*# ROLLBACK; ROLLBACK test=#
排他ロックの回避
排他ロックを回避するには、CREATE INDEXとDROP INDEXにCONCURRENTLYオプションを付与します。CONCURRENTLYはロックを取得せずにindexの作成または削除を行うオプションです。
使用例は以下の通りです。常にindexがある状態にしたいならば、別のindexを作成した後に元のindexを削除する手法もあります。
CREATE INDEX CONCURRENTLY idx_number_new ON staff (number); DROP INDEX CONCURRENTLY idx_number ;
CLUSTERコマンド
CLUSTERコマンドを使用する事で、クラスタ性を保つ事ができます。CLUSTERコマンドはVACUUM FULL同様に排他ロックがかかる事に注意ください。
構文は以下の通りです。
CLUSTER <table_name> USING <index_name> ;
使用例は以下の通りです。
[postgres@centos10 ~]$ psql test << EOF > CLUSTER staff_02 USING idx_number_02 ; > EOF CLUSTER [postgres@centos10 ~]$
クラスタが解消された事を確認します。
[postgres@centos10 ~]$ psql test << EOF > ANALYZE staff_02 ; > SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'staff_02' ; > EOF ANALYZE tablename | attname | correlation -----------+---------+------------- staff_02 | number | 1 staff_02 | f_name | 1 staff_02 | l_name | 1 (3 rows)
なお、クラスタ性はCLUSTERコマンドが実行された時のみ維持され、INSERT, DELETEが繰り返されるとクラスタ性は徐々に欠如します。
以下はクラスタ性が再度失われてしまった事を確認する動作確認例です。
[postgres@centos10 ~]$ psql test << EOF > DELETE FROM staff_02 WHERE number % 2 = 0; > INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); > DELETE FROM staff_02 WHERE number % 2 = 0; > INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); > DELETE FROM staff_02 WHERE number % 2 = 0; > INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); > DELETE FROM staff_02 WHERE number % 2 = 0; > INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); > DELETE FROM staff_02 WHERE number % 2 = 0; > INSERT INTO staff_02 (number) VALUES (generate_series(2,1000,2)); > ANALYZE staff_02 ; > SELECT tablename, attname, correlation FROM pg_stats WHERE tablename = 'staff_02' ; > EOF DELETE 500 INSERT 0 500 DELETE 500 INSERT 0 500 DELETE 500 INSERT 0 500 DELETE 500 INSERT 0 500 DELETE 500 INSERT 0 500 ANALYZE tablename | attname | correlation -----------+---------+------------- staff_02 | number | 0.5014985 staff_02 | f_name | 1 staff_02 | l_name | 1 (3 rows)
VACUUME FULL
VACUUME FULLでもインデックスの「肥大化」「断片化」「クラスタ性の欠如」を解消する事ができます。VACUUME FULLは、排他ロックがかかる事に充分注意ください。
VACUUME FULL <テーブル名>
