PostgreSQL 12がやってくる(7) - CREATE INDEX/REINDEX/CLUSTER/VACUUM FULL command progress monitor

はじめに

今回はPostgreSQL 12の監視に関連する新機能をちょっと試してみた。

時間のかかるメンテナンスコマンドの状況をみたい！

PostgreSQLのメンテナンス系のコマンドだけど、巨大なテーブルに対して実行すると時間が結構かかるものが多い。

これらの時間がかかるメンテナンス系のコマンドの進捗状況を監視する機構はPostgreSQL 9.6から入っていたが、これまではVACUUMのみに対応していた。PostgreSQl 12では更に４種類のコマンドに対応するようになった。

コマンド名	内容	対応バージョン
VACUUM	データベースの不要領域の回収とデータベースの解析（オプション）を行う	PostgreSQl 9.6
VACUUM FULL1	「完全な」バキュームを実行。容量を削減する場合に使う。	PostgreSQl 12
CLUSTER	インデックスに従ってテーブルをクラスタ化する	PostgreSQl 12
CREATE INDEX	新しいインデックスを定義する	PostgreSQL 12
REINDEX	インデックスを再構築する	PostgreSQL 12
ANALYZE	データベースに関する統計を収集する	未対応

(注：マニュアルはPostgreSQL 11日本語版をリンクしている)

先日もとある案件(PostgreSQL 10)で、数百GBのテーブルに対するCREATE INDEXを実行することがあって、「いつ終わるんだよー！」と思いながら作業していたんですが、この機能が入ることによって、途中経過が見られるようになり、多少は苛つきが収まるかもしれない・・・ということで試してみました。

基本的な仕組み

こういった実行に長い時間がかかるメンテナンス用のコマンドの進捗状況を参照するために、PostgreSQL 10から、進捗情報のビューが提供されるようになった。
VACUUM（通常のVACUUM用）のビューはPostgreSQL 10から提供されていたが、PostgreSQL 12ではこれに加えて、2つのビューが追加され3つの進捗情報参照用のビューが使えるようになった。

ビュー名	内容	サポートバージョン
pg_stat_progress_vacuum	通常のVACUUMコマンドの進捗状況を表示する	PostgreSQL 9.6
pg_stat_progress_cluster	CLUSTERコマンドおよびVACUUM FULLコマンドの進捗状況を報告する。 VACUUM FULLコマンドはpg_stat_progress_vacuumではなく、こっちに進捗が表示されるので要注意。	PostgreSQL 12
pg_stat_progress_create_index	CREATE INDEXコマンドおよびREINDEXコマンドの進捗状況を表示する。	PostgreSQL 12

これらのビューの内容を定期的にSELECTすることで進捗状況をアドホックに参照することができる。
また、こうしたビューの検索結果をINSERT文の結果として挿入することで、こうしたメンテナンスコマンドの進捗情報を蓄積することもできる。

pg_stat_progress_cluster

このビューには、CLUSTERコマンドとVACUUM FULLコマンドの進捗状況が格納される。PostgreSQL 9.xのどこかのバージョンで、CLUSTERコマンドとVACUUM FULLコマンドは「だいたい同じ」ような動作(テーブルを物理的に再生成する)になったので、CLSTERもVACUUM FULLもpg_stat_progress_clusterの報告内容は同じだろうと思ったら、実は微妙に違っていたりする。

列名	VACUUM FULL	CLUSTER
command	VACUUM FULL	CLUSTER
phase	"seq scanning heap"と表示される。 僅かなタイミングによっては他のステータスが表示されるのかもしれないが、だいたいはこれ。	"index scanning heap"と表示される。 僅かなタイミングによっては他のステータスが表示されるのかもしれないが、だいたいはこれ。
heap_tuples_scanned	スキャンしたタプル数。	同左
heap_tuples_written	書き込んだタプル数。 だいたいheap_tuples_scannedと同じ値になってる。	同左
heap_blks_total	最初から最後まで不変。	0のまま。変化なし。
heap_blks_scanned	0からheap_blks_totalまで増加	0のまま。変化なし。

CLUSTERは論理的なタプル数はカウントするが、VACUUM FULLとは違って、物理的なブロック数はカウントしない、ということなんだろうか。

あと、CLUSTER/VACUUM FULLに共通するが、進捗状況の報告はスキャンまでである。その後、テーブル再生成のための書き込み処理を行っているはずだが、その部分については、進捗ビューには報告されない(pg_stat_progress_cluster を検索しても行がでてこなくなる)。
大きいテーブルの再生成だと、このファイル書き込みの時間もそれなりにかかるんだけど、残念ながら今の仕組みだと、その部分の報告はされない。
なので、pg_stat_progress_cluster の検索結果が0件になっても、コマンド自体は終了していない、という状況が発生する。
ここが少々イケてない気がするが・・・。

pg_stat_progress_create_index

このビューはCREATE INDEX/REINDEXの進捗状況を表示しれくれるビュー。
一応、両方のコマンドを試してみましたが、だいたい同じような情報を表示してくれます。違いはcommand列くらい。

列名	CREATE INDEX	REINDEX
command	CREATE INDEX	REINDEX
phase	各インデックス毎に、以下のように遷移するようだ。 building index: scanning table building index: loading tuples in tree	同左
blocks_total	"scanning table" phaseの最初の頃にこの値がセットされるっぽい。 "loading tuples in tree" phaseになると0になる。	同左
blocks_done	"scanning table" phaseの進捗を示すのがこの値か。 blocks_totalの値になるまで増加していく。 "loading tuples in tree" phaseになると0になる。	同左
tuples_total	"loading tuples in tree" phaseに入るとこの値がセットされる。	同左
tuples_done	"loading tuples in tree" phaseの進捗を示すのがこの値。0からtuples_totalの数値まで増加していく。 この値がtuples_totalに到達すると、そのインデックスに関する処理はだいたい終わり。	同左

pg_stat_progress_create_index の内容を別テーブルに挿入して、そこから上記の表に関する列のみ抜き出すと、こんな感じになる。
（pciというのは、pg_stat_progress_create_indexの内容＋タイムスタンプを付与した情報をINSERTしていく自作のテーブル）

testdb=# SELECT command, phase, relid, blocks_total, blocks_done, tuples_total, tuples_done FROM pci;
   command    |                 phase                  | relid | blocks_total | blocks_done | tuples_total | tuples_done
--------------+----------------------------------------+-------+--------------+-------------+--------------+-------------
 REINDEX      | building index: scanning table         | 16442 |       163935 |       56837 |            0 |           0
 REINDEX      | building index: scanning table         | 16442 |       163935 |      163935 |            0 |           0
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     4334900
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     7440215
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     9282493
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |    10000000
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |    10000000

VACUUM FULLやCLUSTERと違って、pg_stat_progress_create_index ビューから検索結果が得られない(0件になる)状態から、CREATE INDEX/REINDEXコマンド自体が終了するまでの時間差はそれほどない気がする。

おわりに

PostgreSQLのメンテナンス系コマンドの進捗を確認するためのシステムビューの機能を軽く試してみました。
とにかく巨大なテーブルに対する、こうしたコマンドは時間がかかるので、「あとどのくらいで終わるんだろう」のヒントが得られるのは、運用を行うオペレータにとっては嬉しい機能かもしれません。

pg_stat_progress_create_index に関しては、パーティション固有の情報も出力するようですが、そこは今回は未検証です(単に、パーティション構成の検証環境を組むのが面倒だったので・・・すみません）。

1. 個人的には、もうSQLコマンドとしてもVACUUMとは別の項でいいんじゃないかという気もしている・・・（だいたいCLUSTERと同様のことをするので）。 ↩