PostgreSQLのインデックス再編成

インデックスは肥大化しやすいので、再編成が必要になることが多いと思います。
実際にインデックスがどのように肥大化するか確認してみます。

まずはテスト用のテーブルを作成し、データ（500万件）を投入します。

drop table testa;
create table testa (
  id numeric,
  txt varchar(10)
);

insert into testa(id, txt) select i, '0123456789' from generate_series(1, 5000000) as i;
create unique index idx_testa01 on testa(id);

この時点でテーブルとインデックスのページ数は以下のようになりました。

select relname, relpages from pg_class where relname in ('testa', 'idx_testa01');
   relname   | relpages 
-------------+----------
 testa       |    31838
 idx_testa01 |    13713
(2 rows)

テーブルから400万件を削除し、Vacuumを実行します。
そうすると、テーブルの使用ページ数は31838から6360に減っています。
一方、インデックスのページ数は13713から変わりありません。

postgres=# delete from testa where id between 1 and 4000000;
DELETE 4000000
postgres=# vacuum analyze testa;
VACUUM

postgres=# select relname, relpages from pg_class where relname in ('testa', 'idx_testa01');
   relname   | relpages 
-------------+----------
 testa       |     6360
 idx_testa01 |    13713
(2 rows)

次は400万件を挿入してみます。
テーブルの使用ページ数は（当たり前ですが）6360から12730に増加。
インデックスの使用ページ数は13713から27421に増加。挿入した1億万件から4000万件は削除されているのに、再利用されずに増加し続けています。

postgres=# insert into testa(id, txt) select i, '0123456789' from generate_series(5000001, 10000000) as i;
INSERT 0 5000000
postgres=# delete from testa where id between 5000001 and 9000000;
DELETE 4000000
postgres=# vacuum analyze testa;
VACUUM

postgres=# select relname, relpages from pg_class where relname in ('testa', 'idx_testa01');
   relname   | relpages 
-------------+----------
 testa       |    12730
 idx_testa01 |    27421
(2 rows)

ただし、Vacuumにより解放されて再使用可（reusable）にはなっています。今回はインデックス（id）が単調増加のため再利用されていないためこのような現象が発生します。
ちなみにPostgreSQL12からはvacuumにindex_cleanupが追加されていて、インデックスの不要領域の回収をOFFにすることができるようになっています。OFFにした場合、以下のように明示的に改修する必要があります。

vacuum (index_cleanup true, analyze, verbose) testa;

reindexでのインデックス再編成

再使用可となっている領域を解放して容量を削減するためにはreindexでインデックスを再編成します。
reindexすると、以下のように使用ページ数が27421から5487ページまで減少しました。

postgres=# reindex index idx_testa01;
REINDEX
postgres=# select relname, relpages from pg_class where relname in ('testa', 'idx_testa01');
   relname   | relpages 
-------------+----------
 testa       |    12730
 idx_testa01 |     5487
(2 rows)

このように便利なreindexですが、実行時にロックがかかります。
インデックスのロックを確認すると、AccessExclusiveLock（排他ロック）がかかっていることが分かります。
AccessExclusiveLockなのでSELECTも実行できません。

postgres=# SELECT now() - s.query_start as duration, relation::regclass, l.*, s.query FROM pg_locks l join pg_stat_activity s on s.pid = l.pid where query like 'reindex index%' ;
    duration     |  relation   |   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |
 virtualtransaction |  pid  |        mode         | granted | fastpath |           query            
-----------------+-------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+
--------------------+-------+---------------------+---------+----------+----------------------------
 00:00:12.169258 |             | virtualxid    |          |          |      |       | 11/249     |               |         |       |          |
 11/249             | 12216 | ExclusiveLock       | t       | t        | reindex index idx_testa01;
 00:00:12.169258 |             | transactionid |          |          |      |       |            |        192994 |         |       |          |
 11/249             | 12216 | ExclusiveLock       | t       | f        | reindex index idx_testa01;
 00:00:12.169258 | idx_testa01 | relation      |    14185 |    78591 |      |       |            |               |         |       |          |
 11/249             | 12216 | AccessExclusiveLock | t       | f        | reindex index idx_testa01;
 00:00:12.169258 | testa       | relation      |    14185 |    78585 |      |       |            |               |         |       |          |
 11/249             | 12216 | ShareLock           | t       | f        | reindex index idx_testa01;
(4 rows)

reindexコマンドを確認すると以下のように記載されています。

REINDEXは、インデックスの中身を1から作り直すという点では、インデックスを削除してから再作成する処理と似ています。しかし、ロックに関しては異なります。 REINDEXはインデックスの元となるテーブルの書き込みをロックしますが、読み込みはロックしません。また、処理中のインデックスに対する排他ロックを取得するので、そのインデックスを使用する読み込みはブロックされます。

REINDEXはテーブルの書き込みと処理中のインデックスに対して排他ロックを取得します。
読み込みはロックしませんと書かれていますが、インデックスに対してのSELECT文は実行できません。

reindexとselectを以下のように同時に実行してみます。
すると、インデックス指定（id）で検索するSQLに11秒もかかってしまいました。
これはreindexにかかった11秒の間、SELECT文が待たされたということです。

postgres=# reindex index idx_testa01;
REINDEX

postgres=# \timing
Timing is on.
postgres=# select * from testa where id = 1000;
  id  |    txt     
------+------------
 1000 | 0123456789
(1 row)

Time: 11416.692 ms (00:11.417)

PostgreSQL12のreindexコマンドでのconcurrentlyオプション

PostgreSQL12ではreindexコマンドをオンラインで実行することができるconcurrentlyオプションに対応しました。
以下のように実行します。

reindex index concurrently idx_testa01;

対象テーブルに対する同時挿入、更新、削除を防止するようなロックを獲得せずにインデックスを作成します。

PostgreSQL11以下では、以下のようにcreate index文でconcurrentlyオプションを作成してから、インデックスを入れ替えることになります。もしくはpg_repack（後述）を使用します。

postgres=# create unique index concurrently idx_testa02 on testa(id);
CREATE INDEX

postgres=# select * from testa where id = 1000;
  id  |    txt     
------+------------
 1000 | 0123456789
(1 row)

concurrentlyオプションの欠点は、テーブルを2回スキャンする必要があるため、リソースの使用量が増える点です。
また、インデックス作成に失敗した場合に注意が必要です。
以下のようにわざとcreate indexの作成をキャンセルします。

postgres=# create unique index concurrently idx_testa02 on testa(id);
^CCancel request sent

ERROR:  canceling statement due to user request
Time: 7474.606 ms (00:07.475)

途中で中断した後に、再実行するとエラーになります。

postgres=# create unique index concurrently idx_testa02 on testa(id);
ERROR:  relation "idx_testa02" already exists

この状態で100万レコード挿入して、再度ページ数を見ると、インデックス自体は更新されていることが分かります。

postgres=# select relname, relpages from pg_class where relname = 'idx_testa02';
   relname   | relpages 
-------------+----------
 idx_testa02 |    19196
(1 row)

postgres=# insert into testa(id, txt) select i, '0123456789' from generate_series(2000001, 3000000) as i;
INSERT 0 1000000
postgres=# vacuum analyze testa;
VACUUM
postgres=# select relname, relpages from pg_class where relname = 'idx_testa02';
   relname   | relpages 
-------------+----------
 idx_testa02 |    24146
(1 row)

ただし、\dコマンドでみるとINVALIDとなっており、実行計画では使用されませんが、インデックスは更新されるという状態となっていることが分かります。

postgres=# \d testa
                      Table "public.testa"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | numeric               |           |          | 
 txt    | character varying(10) |           |          | 
Indexes:
    "idx_testa01" UNIQUE, btree (id)
    "idx_testa02" UNIQUE, btree (id) INVALID

インデックスの状態は以下のSQLでも確認できます。

postgres=# SELECT cl.relname, ind.indisvalid FROM pg_class cl, pg_index ind WHERE ind.indisvalid = false AND ind.indexrelid = cl.oid;
   relname   | indisvalid 
-------------+------------
 idx_testa02 | f
(1 row)

このようにconcurrentlyオプションを付けてインデックスの作成に失敗するとインデックスが無効になるため、すぐにインデックスを削除して再度作成する必要があります。

また、他のトランザクションが終了しないと、create indexコマンドは永久に待機します。
idle_in_transaction_session_timeoutパラメータというパラメータで制御できるという話が合ったので確認してみました。

postgres=# begin;
BEGIN
postgres=# insert into testa(id, txt) select i, '0123456789' from generate_series(3000001, 4000000) as i;
INSERT 0 1000000

postgres=# create unique index concurrently idx_testa02 on testa(id);

上のように実行するとcreate indexはいつまでも終わりません。end;でトランザクションを終了すると終了します。

postgres=# end;
postgres=# create unique index concurrently idx_testa02 on testa(id);
CREATE INDEX
Time: 235562.131 ms (03:55.562)

idle_in_transaction_session_timeoutパラメータを20秒に設定して試してみます。

postgres=# set idle_in_transaction_session_timeout=20000;
SET

これではうまくいかなかった。以下のサイトを見てlock_timeoutパラメータを設定すると、うまくキャンセルされました。

https://www.reddit.com/r/PostgreSQL/comments/crb2u0/experiencing_lock_timeouts_while_creating_index/

postgres=# set lock_timeout=20000;
SET
postgres=# begin;
postgres=# insert into testa(id, txt) select i, '0123456789' from generate_series(4000001, 4000002) as i;
INSERT 0 2
postgres=# create unique index concurrently idx_testa02 on testa(id);
ERROR:  canceling statement due to lock timeout
Time: 20002.362 ms (00:20.002)

実行中のロックの状況を確認すると以下のようになりました。
CREATE INDEX CONCURRENTLYでテーブルに対してShareUpdateExclusiveLock、インデックスに対してExclusiveLockのロックが取得されています。
（virtualxidはトランザクションの仮想IDらしいがよーわからん。）
ExclusiveLockはSELECTは可能、ShareUpdateExclusiveLockはSELECTに加えてUPDATE、DELETE、およびINSERTコマンドもOK。

postgres=# SELECT now() - s.query_start as duration, relation::regclass, l.*, s.query FROM pg_locks l join pg_stat_activity s on s.pid = l.pid where query like 'create unique index concurrently%' ;
    duration     |  relation   |  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |           mode           | granted | fastpath |              
             query                            
-----------------+-------------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+--------------------------+---------+----------+--------------
----------------------------------------------
 00:00:12.786517 | idx_testa02 | relation   |    14185 |    78614 |      |       |            |               |         |       |          | 5/144              | 4861 | RowExclusiveLock         | t       | t        | create unique
 index concurrently idx_testa02 on testa(id);
 00:00:12.786517 |             | virtualxid |          |          |      |       | 5/144      |               |         |       |          | 5/144              | 4861 | ExclusiveLock            | t       | t        | create unique
 index concurrently idx_testa02 on testa(id);
 00:00:12.786517 | testa       | relation   |    14185 |    78585 |      |       |            |               |         |       |          | 5/144              | 4861 | ShareUpdateExclusiveLock | t       | f        | create unique
 index concurrently idx_testa02 on testa(id);
(3 rows)

パーティションでのreindex

reindexはパーティションテーブルやパーティションインデックスのインデックス再作成はサポートされていません。個々のインデックスに対して実行します。

テストテーブルを作成して試してみます。

CREATE TABLE test_part (
    id         int not null,
    regdate    date not null
) PARTITION BY RANGE (regdate);

CREATE TABLE test_part_y2019m01 PARTITION OF test_part
    FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');

CREATE TABLE test_part_y2019m02 PARTITION OF test_part
    FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');

CREATE TABLE test_part_y2019m03 PARTITION OF test_part
    FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
    
CREATE INDEX idx_test_part01 ON test_part (regdate);

insert into test_part(id, regdate) select i, '2019-01-01 10:00:00'::timestamp from generate_series(1, 2000000) as i;
insert into test_part(id, regdate) select i, '2019-02-01 10:00:00'::timestamp from generate_series(2000001, 4000000) as i;
insert into test_part(id, regdate) select i, '2019-03-01 10:00:00'::timestamp from generate_series(4000001, 6000000) as i;

パーティションテーブルのインデックスに対してreindexを実行するとエラーになります。

postgres=# reindex index idx_test_part01;
ERROR:  REINDEX is not yet implemented for partitioned indexes

個々のパーティションのインデックスに対してreindexを実行します。

postgres=# \d test_part_y2019m01
         Table "public.test_part_y2019m01"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 id      | integer |           | not null | 
 regdate | date    |           | not null | 
Partition of: test_part FOR VALUES FROM ('2019-01-01') TO ('2019-02-01')
Indexes:
    "test_part_y2019m01_regdate_idx" btree (regdate)

postgres=# reindex index test_part_y2019m01_regdate_idx;
REINDEX

concurrentlyオプションをつけてパーティションテーブルに対してインデックス作成はエラーになります。

postgres=# create index concurrently idx_test_part02 on test_part(regdate);
ERROR:  cannot create index on partitioned table "test_part" concurrently

下のようにconcurrentlyでなければパーティションテーブルに対してインデックスを作成できる。

postgres=# create index idx_test_part02 on test_part(regdate);
CREATE INDEX

concurrentlyではパーティションはNGなので子テーブルに対してインデックスをリビルドする。

drop index idx_test_part01;
drop index idx_test_part02;

しかし、パーティションテーブルへの書き込みをロックしている時間を短くするために、各パーティション上のインデックスを個別に同時作成してから最後にパーティションインデックスを非同時的に作成することはできます。
この場合、パーティションインデックスの作成はメタデータのみの操作になります。

create index concurrently idx_test_part02_y2019m01 on test_part_y2019m01(regdate);
create index concurrently idx_test_part02_y2019m02 on test_part_y2019m02(regdate);
create index concurrently idx_test_part02_y2019m03 on test_part_y2019m03(regdate);

create index idx_test_part02 on test_part(regdate);
postgres=# create index concurrently idx_test_part02_y2019m01 on test_part_y2019m01(regdate);
CREATE INDEX
Time: 1676.153 ms (00:01.676)
postgres=# create index concurrently idx_test_part02_y2019m02 on test_part_y2019m02(regdate);
CREATE INDEX
Time: 2333.291 ms (00:02.333)
postgres=# create index concurrently idx_test_part02_y2019m03 on test_part_y2019m03(regdate);
CREATE INDEX
Time: 2745.683 ms (00:02.746)
postgres=# 
postgres=# create index idx_test_part02 on test_part(regdate);
CREATE INDEX
Time: 34.474 ms
postgres=# 
postgres=# \d+ test_part
                           Partitioned table "public.test_part"
 Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
---------+---------+-----------+----------+---------+---------+--------------+-------------
 id      | integer |           | not null |         | plain   |              | 
 regdate | date    |           | not null |         | plain   |              | 
Partition key: RANGE (regdate)
Indexes:
    "idx_test_part02" btree (regdate)
Partitions: test_part_y2019m01 FOR VALUES FROM ('2019-01-01') TO ('2019-02-01'),
            test_part_y2019m02 FOR VALUES FROM ('2019-02-01') TO ('2019-03-01'),
            test_part_y2019m03 FOR VALUES FROM ('2019-03-01') TO ('2019-04-01')

パーティションを確認すると以下のようになっています。

postgres=# \d+ test_part_y2019m01
                             Table "public.test_part_y2019m01"
 Column  |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
---------+---------+-----------+----------+---------+---------+--------------+-------------
 id      | integer |           | not null |         | plain   |              | 
 regdate | date    |           | not null |         | plain   |              | 
Partition of: test_part FOR VALUES FROM ('2019-01-01') TO ('2019-02-01')
Partition constraint: ((regdate IS NOT NULL) AND (regdate >= '2019-01-01'::date) AND (regdate < '2019-02-01'::date))
Indexes:
    "idx_test_part02_y2019m01" btree (regdate)
Access method: heap

drop indexのconcurrentlyオプション

create indexと同様にdrop indexにもconcurrentlyオプションがあります。

concurrentlyオプションは以下のような説明がされています。
drop indexは排他ロックがかかりますが、concurrentlyオプションをつければ、競合するトランザクションが完了するまで待ってからdrop indexが実行されます。

https://www.postgresql.jp/document/11/html/sql-dropindex.html

インデックスのテーブルに対して同時に実行される選択、挿入、更新、削除をロックすることなくインデックスを削除します。通常のDROP INDEXではテーブルに対する排他ロックを獲得し、インデックスの削除が完了するまで他のアクセスをブロックします。このオプションを使うと、競合するトランザクションが完了するまでコマンドは待たされます。
このオプションを使用する時に注意すべき、複数の警告があります。指定できるインデックス名は１つだけであり、また、CASCADEオプションはサポートされません。（したがってUNIQUEまたはPRIMARY KEY制約をサポートするインデックスをこの方法で削除することはできません。）また、通常のDROP INDEXはトランザクションブロックの中で行うことができますが、DROP INDEX CONCURRENTLYはできません。

pg_repackについて

reindexは制約があったり、手順が煩雑になることがあるので、pg_repackを利用することが多いかと思います。

インデックスのみ再編成する場合、pg_repackは以下のように動作すると書かれていました。
（https://reorg.github.io/pg_repack/jp/）

元のインデックス定義に添って、新しいインデックスをconcurrentlyオプションを利用して作成します。
システムカタログを更新し、元のインデックスと新しいインデックスを入れ替えます。
元のインデックスを削除します。

また、reindexでは主キーに対して直接再編成はできませんが、pg_repackは以下のように直接実行することができます。

$ pg_repack -d postgres -i idx_testa01
INFO: repacking index "public.idx_testa01"


drop table testa_pk;
create table testa_pk (
  id numeric,
  txt varchar(10),
  primary key(id)
);

$ pg_repack -d postgres -i testa_pk_pkey

パーティションに対しては以下のようにエラーになります。

$ pg_repack -d postgres -i idx_test_part02
INFO: repacking index "public.idx_test_part02"
WARNING: Error creating index "public"."index_79186": ERROR:  cannot create index on partitioned table "test_part" concurrently
WARNING: Skipping index swapping for "public.test_part", since no new indexes built
INFO: Skipping drop of index_79186
WARNING: repack failed for "idx_test_part02"

pg_repackでも内部的にはcreate index concurrentlyが実行されている？
concurrentlyはパーティションに対応していないのでパーティションテーブルに対してはpg_repackは利用できないということだろうか。

パーティションのインデックスを再編成するには、主キーか一意キーが必要です。

$ pg_repack -d postgres -I test_part
WARNING: relation "public.test_part_y2019m01" must have a primary key or not-null unique keys
WARNING: relation "public.test_part_y2019m02" must have a primary key or not-null unique keys
WARNING: relation "public.test_part_y2019m03" must have a primary key or not-null unique keys

create unique index idx_test_part03 on test_part(id, regdate);

# pg_repack -d postgres -I test_part
INFO: repacking table "public.test_part_y2019m01"
INFO: repacking table "public.test_part_y2019m02"
INFO: repacking table "public.test_part_y2019m03"

その他

インデックス作成はmaintenance_work_memの設定に影響を受けます。設定値を大きくするとインデックス作成にかかる時間が短縮できることがあります。（少なくともデフォルト値になっていれば大きくすれば早くなります。）
alter tableでparallel_workersの値を設定することで並列度を設定できます。

まとめ

インデックスは肥大化するので、reindexで再編成する必要がある。
reindexはロックがかかるので注意。PostgreSQL12ならconcurrentlyオプションでオンライン実行できる。
PostgreSQLはロック対策でpg_repackを用いてインデックスを再編成する。