はじめに
PostgreSQL の REINDEX は、インデックスを再作成し、古いファイルと置き換えるコマンドです。
インデックスの断片化や、PostgreSQL のマイナーバージョンアップ後に必要となるケースがあります。
REINDEX は排他ロックを取得するため、メンテナンス中の実施が望ましいです。
今回、pgbench を使ってオンライン中を再現し、REINDEX の影響を観測してみます。
実施手順
下記を 1セットとし、3回繰り返して TPS を測定します。
- pgbench初期化 (スケールファクタ=100)
- pgbench実行 (2クライアント、30秒)
- PostgreSQL の再起動
- pgbench初期化 (スケールファクタ=100)
- pgbench実行 (2クライアント、30秒)
- pgbench実行中に REINDEX (pgbench が使用する pgbench_accounts, pgbench_branches, pgbench_tellers のそれぞれに作成された 3インデックスが対象)
- PostgreSQL の再起動
環境は OCI上のコンピュート・インスタンスに PostgreSQL 17.6 をインストールしています。
1セット目
REINDEX と PostgreSQL の再起動は別ターミナルで実施しています。
結果だけ知りたい方は右の TOC から「測定結果」にジャンプしてください。
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4388200 of 10000000 tuples (43%) of pgbench_accounts done (elapsed 10.19 s, remaining 13.03 s
5845000 of 10000000 tuples (58%) of pgbench_accounts done (elapsed 15.20 s, remaining 10.81 s
7640000 of 10000000 tuples (76%) of pgbench_accounts done (elapsed 20.20 s, remaining 6.24 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 39.93 s, remaining 0.00
vacuuming...
creating primary keys...
done in 55.57 s (drop tables 0.09 s, create tables 0.01 s, client-side generate 45.48 s, vacuum 0.15 s, primary keys 9.85 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 38331
number of failed transactions: 0 (0.000%)
latency average = 1.567 ms
initial connection time = 4.610 ms
tps = 1276.122882 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.021 0 BEGIN;
0.125 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.098 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.069 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.074 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.049 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.128 0 END;
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4316200 of 10000000 tuples (43%) of pgbench_accounts done (elapsed 10.19 s, remaining 13.42 s
5905400 of 10000000 tuples (59%) of pgbench_accounts done (elapsed 16.09 s, remaining 11.16 s
7414900 of 10000000 tuples (74%) of pgbench_accounts done (elapsed 20.03 s, remaining 6.98 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 46.02 s, remaining 0.00
vacuuming...
creating primary keys...
done in 54.88 s (drop tables 0.09 s, create tables 0.01 s, client-side generate 46.17 s, vacuum 0.14 s, primary keys 8.47 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 26956
number of failed transactions: 0 (0.000%)
latency average = 2.226 ms
initial connection time = 4.322 ms
tps = 898.622746 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.015 0 BEGIN;
0.687 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.102 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.070 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.074 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.048 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.228 0 END;
2セット目
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4408900 of 10000000 tuples (44%) of pgbench_accounts done (elapsed 10.18 s, remaining 12.91 s
5833800 of 10000000 tuples (58%) of pgbench_accounts done (elapsed 15.69 s, remaining 11.20 s
7501100 of 10000000 tuples (75%) of pgbench_accounts done (elapsed 20.04 s, remaining 6.68 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 39.81 s, remaining 0.00
vacuuming...
creating primary keys...
done in 54.24 s (drop tables 0.09 s, create tables 0.00 s, client-side generate 41.42 s, vacuum 4.00 s, primary keys 8.72 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 38248
number of failed transactions: 0 (0.000%)
latency average = 1.569 ms
initial connection time = 4.672 ms
tps = 1275.041202 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.025 0 BEGIN;
0.126 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.094 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.070 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.074 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.049 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.128 0 END;
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4365300 of 10000000 tuples (43%) of pgbench_accounts done (elapsed 10.19 s, remaining 13.15 s
5942100 of 10000000 tuples (59%) of pgbench_accounts done (elapsed 15.50 s, remaining 10.58 s
7487800 of 10000000 tuples (74%) of pgbench_accounts done (elapsed 20.02 s, remaining 6.72 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 45.49 s, remaining 0.00
vacuuming...
creating primary keys...
done in 54.32 s (drop tables 0.09 s, create tables 0.00 s, client-side generate 45.64 s, vacuum 0.15 s, primary keys 8.44 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 29025
number of failed transactions: 0 (0.000%)
latency average = 2.067 ms
initial connection time = 4.715 ms
tps = 967.566407 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.022 0 BEGIN;
0.535 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.093 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.070 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.074 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.048 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.222 0 END;
3セット目
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4150200 of 10000000 tuples (41%) of pgbench_accounts done (elapsed 10.16 s, remaining 14.32 s
6165000 of 10000000 tuples (61%) of pgbench_accounts done (elapsed 15.09 s, remaining 9.39 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 42.92 s, remaining 0.00
vacuuming...
creating primary keys...
done in 54.14 s (drop tables 0.09 s, create tables 0.00 s, client-side generate 45.43 s, vacuum 0.15 s, primary keys 8.47 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 37932
number of failed transactions: 0 (0.000%)
latency average = 1.582 ms
initial connection time = 4.856 ms
tps = 1264.485985 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.015 0 BEGIN;
0.124 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.103 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.071 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.074 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.049 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.144 0 END;
[postgres@instance-20251105-1101 ~]$ pgbench -i -s 100 -q postgres
dropping old tables...
creating tables...
generating data (client-side)...
4306200 of 10000000 tuples (43%) of pgbench_accounts done (elapsed 10.00 s, remaining 13.22 s
5826900 of 10000000 tuples (58%) of pgbench_accounts done (elapsed 15.27 s, remaining 10.93 s
7464900 of 10000000 tuples (74%) of pgbench_accounts done (elapsed 20.09 s, remaining 6.82 s)
10000000 of 10000000 tuples (100%) of pgbench_accounts done (elapsed 42.90 s, remaining 0.00
vacuuming...
creating primary keys...
done in 55.00 s (drop tables 0.09 s, create tables 0.01 s, client-side generate 46.25 s, vacuum 0.16 s, primary keys 8.49 s).
[postgres@instance-20251105-1101 ~]$ pgbench -c 2 -T 30 -r postgres
pgbench (17.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 29045
number of failed transactions: 0 (0.000%)
latency average = 2.066 ms
initial connection time = 5.151 ms
tps = 968.184610 (without initial connection time) ★
statement latencies in milliseconds and failures:
0.001 0 \set aid random(1, 100000 * :scale)
0.000 0 \set bid random(1, 1 * :scale)
0.000 0 \set tid random(1, 10 * :scale)
0.000 0 \set delta random(-5000, 5000)
0.023 0 BEGIN;
0.573 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.094 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.071 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.084 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.049 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.170 0 END;
測定結果
測定した TPS をまとめると下記のようになりました。
| REINDEX なし | REINDEX あり | |
|---|---|---|
| 1セット目 | 1276.122882 | 898.622746 |
| 2セット目 | 1275.041202 | 967.566407 |
| 3セット目 | 1264.485985 | 968.184610 |
「REINDEX あり」の TPS が、「REINDEX なし」の TPS と比較して 20～30% ほど劣化していることがわかります。
今回は 1テーブルあたり 1インデックスでしたが、インデックス数が増えるほど影響は大きくなると考えられます。
REINDEX は、メンテナンス中に実施するか、負荷の低い時間帯に CONCURRENTLYオプションを付けて実施しましょう。
CONCURRENTLY
このオプションが使われると、PostgreSQLは、そのテーブルで同時実行される挿入、更新、削除を妨げるようなロックを取得せずにインデックスを再構築します。一方、標準のインデックス再構築は終了するまでテーブルの書き込みをロックします（読み込みはロックしません）。 このオプションを使用する場合に注意すべき点がいくつかあります—下記のインデックスを同時に再構築を参照してください。
一時テーブルに対してはREINDEXは常に同時再作成ではありません。他のセッションはアクセスできませんし、同時でないインデックス再作成の方がより安価だからです。