1. はじめに
にゃーん。
今日は、先週、バージョン1.1がリリース（リリースニュース）されたpg_ivmについて、実際に動作させて改善点を検証しました。
2. What's pg_ivm?
pg_ivmはPostgreSQLでIncremental View Maintenanceを実現する拡張機能です。
Incremental View Maintenanceとは、再計算を行わず、増分変更のみを計算してビューに適用することで、実体化したビューを最新の状態にする方法です。
この増分変更の計算と適用の方式に関する考え方は、論文などでも紹介されています（PostgreSQL Wiki - Incremental View Maintenance等参照）が、自分は猫なのでそういう難しいことはわかりません。本記事ではあくまでも使い方について書いてみます。
詳細は前回書いた記事（pg_ivm(IMMV)を使ってみた(1)）を参照していただければと。
3. pg_ivm 1.1の改善点
pg_ivm 1.1のリリースノートに記載されている改善点は以下のとおり。
- PostgreSQL 13のサポート
- 集約関数のサポート
- ベーステーブルに対するTRUNCATEのサポート
- refresh_immv()関数の追加
- create_immv()への入力が不正な場合のエラーコードを修正
以下、それぞれの改善点について見ていきます。
3.1 PostgreSQL 13のサポート
前回調査時は、PostgreSQL 14上での動作を確認しましたが、pg_ivm 1.1ではPostgreSQL 13にも対応したとのこと。
手元の環境（PostgreSQL 12, 13, 14, 15 beta1）でpg_ivmがビルドできるかを一応確認してみました。
| バージョン | ビルド可否 | 備考 |
|---|---|---|
| PostgreSQL 12 | ✗ |
QueryCompletion型がないINT4OIDがないIndexStmtのメンバoldCreateSubid,opclassoptsが存在しない |
| PostgreSQL 13 | ○ | |
| PostgreSQL 14 | ○ | |
| PostgreSQL 15 beta1 | ✗ |
make_new_heap()への引数が少ない |
今のところ、PostgreSQL 13, 14のみ利用可能なようです。
pg_ivm 1.0のときには、PostgreSQL 15beta1でもビルドはできていたような気がしたけど・・・。もうすぐPostgreSQL 15 beta2 がリリースされるらしいので、beta2がリリースされたら、beta2でビルド可能かも確認しておかねば。
なお、以降の検証は全て、PostgreSQL 14上で実施しています。
3.2 集約関数のサポート
pg_ivm 1.1での改善項目の目玉その1。
pg_ivm 1.1では、組み込み集約関数のうち、count, sum, avgの3関数に対応しているとのこと。
ということで、以下の検証をしてみました。
検証概要
- 各ビューの集約演算元のテーブル(base)を定義して、10万件のランダムなデータを挿入する。
- baseに対してCOUNT(*), SUM(), AVG()の集約を行うクエリをもとに3種類のビューを作成する。
- VIEW(agg_v)
- MATERIALIZED VIEW(agg_mv)
- INCREMENTAL MAINTENANCE MATERIALIZED VIEW(agg_immv)
- 作成直後に3種類のビューを検索する。
- その状態で、更に10万件のデータを挿入する。
- agg_mvをリフレッシュする。
- この状態で3種類のビューを検索する。
検証用のテーブル
以下のベーステーブルを定義する。
このベーステーブルはPKとint型、double precision型、numeric型を持つ。
CREATE TABLE ivm.base
(id int primary key, i_data int, d_data double precision, n_data numeric);
データ挿入(1)
baseテーブルに10万件のデータを挿入する。i_data, d_data, n_dateにはrandom() * 100000 の数値を格納する。
INSERT INTO ivm.base VALUES
(generate_series(1, 100000), (random() * 10000)::int, random(), random());
各ビューの作成
3種類のビューを作成する。それぞれのビューの生成元となるSELECT文は以下とする。
SELECT
COUNT(*),
SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
FROM ivm.base
実際のビュー定義は以下のようになる。
CREATE VIEW ivm.agg_v AS
SELECT
COUNT(*),
SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
FROM ivm.base
;
CREATE MATERIALIZED VIEW ivm.agg_mv AS
SELECT
COUNT(*),
SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
FROM ivm.base
;
SELECT create_immv('ivm.agg_immv', $$
SELECT
COUNT(*),
SUM(i_data) AS i_sum, SUM(d_data) AS d_sum, SUM(n_data) n_sum,
AVG(i_data) AS i_avg, AVG(d_data) AS d_avg, AVG(n_data) n_avg
FROM ivm.base
$$
);
検索(1)
各ビューに対して検索を行う。（1行が長いので拡張表示形式で出力する）1
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_v;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834
Time: 22.003 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_mv;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834
Time: 0.310 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_immv;
-[ RECORD 1 ]---------------------
count | 100000
i_sum | 499208554
d_sum | 49947.221876297386
n_sum | 50082.73372440000083375849
i_avg | 4992.0855400000000000
d_avg | 0.4994722187629739
n_avg | 0.50082733724400000834
Time: 0.212 ms
int型やnumeric型に対するSUM, AVGはどのビューでも同じ値になる。
double precision型に対するSUM, AVGは各ビューでビミョーに違う値になってしまうが、これは浮動小数点型だから仕方ないのかも。
データ挿入(2)
この状態で、さらに10万件のデータをbaseテーブルに挿入する。
また、REFRESH MATERIALIZED VIEWも実施する。
INSERT INTO ivm.base VALUES
(generate_series(100001, 200000), (random() * 10000)::int, random(), random());
INSERT 0 100000
Time: 354.707 ms
REFRESH MATERIALIZED VIEW ivm.agg_mv;
REFRESH MATERIALIZED VIEW
Time: 32.198 ms
検索(2)
再度データ挿入した後で再検索する。
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_v;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.67078115631
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.5003783539057816
n_avg | 0.50058409007212873207
Time: 25.712 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_mv;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.6707811564
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.500378353905782
n_avg | 0.50058409007212873207
Time: 0.374 ms
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_immv;
-[ RECORD 1 ]----------------------
count | 200000
i_sum | 999078870
d_sum | 100075.6707811559
n_sum | 100116.81801442574641475671
i_avg | 4995.3943500000000000
d_avg | 0.5003783539057796
n_avg | 0.50058409007212873207
Time: 0.137 ms
1回目の検索と同様、int型やnumeric型に対するSUM, AVGはどのビューでも同じ値になり、double precision型に対するSUM, AVGは各ビューでビミョーに違う値になってしまう。これは想定どおり。
ということで、Incremental View Maintenanceによる、集約関数(COUNT, SUM, AVG)が対応していることを確認できました。
あとは、MAX, MIN関数がサポートされればなあ・・・。これがサポートされると、IMMVを使うことで、PostgreSQLでも巨大なテーブルのMAX, MINが素早く求められるようになるのだけど。
3.3 ベーステーブルに対するTRUNCATEのサポート
pg_ivm 1.1の改善項目の目玉その2。
pg_ivm 1.0ではベーステーブルに対してTRUNCATEしても、IMMVは追随してくれなかったのですが、pg_ivm 1.1ではベーステーブルに対するTRUNCATEの結果にIMMVが追随するようになりました。
検証
ベーステーブル(base)に200万件入った状態で、各ビューの結果を表示します。
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_v;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.49547540041
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770021
n_avg | 0.49930606097435924830
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_mv;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.4954754003
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770015
n_avg | 0.49930606097435924830
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_immv;
-[ RECORD 1 ]---------------------
count | 200000
i_sum | 1001123321
d_sum | 100082.49547540018
n_sum | 99861.21219487184966014496
i_avg | 5005.6166050000000000
d_avg | 0.5004124773770009
n_avg | 0.49930606097435924830
この状態で、ベーステーブル(base)をTRUNCATEします。
またマテリアライズド・ビューはリフレッシュしておきます。
TRUNCATE ivm.base;
TRUNCATE TABLE
REFRESH MATERIALIZED VIEW ivm.agg_mv;
REFRESH MATERIALIZED VIEW
TRUNCATEした後は、ビュー、マテリアライズド・ビュー、IMMVともにCOUNTが0件、SUM, AVGの結果はNULLになりました。めでたしめでたし。
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_v;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_mv;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |
SELECT count, i_sum, d_sum, n_sum, i_avg, d_avg, n_avg FROM ivm.agg_immv;
-[ RECORD 1 ]
count | 0
i_sum |
d_sum |
n_sum |
i_avg |
d_avg |
n_avg |
3.4 refresh_immv()関数の追加
pg_ivm拡張を登録後に、\dfコマンドでimmvがつく関数を探してみます。
postgres@ivm# \df pg_catalog.*immv;
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------+------------------+---------------------+------
pg_catalog | create_immv | bigint | text, text | func
pg_catalog | refresh_immv | bigint | text, boolean | func
(2 rows)
たしかにrefresh_immvという関数が追加されているようです。
refresh_immv関数は2つの引数を持っています。
最初の引数はリフレッシュしたいIMMV名（create_immv実行時に指定したもの）を指定します。
2つ目の引数は
WITH [NO] DATA オプションに対応する boolean パラメータ
というもの。true, falseを指定すると以下のような挙動になります。
| 引数の値 | 挙動 |
|---|---|
| true | create_immv()で指定したクエリを再実行して、クエリの実行結果をIMMVに設定する。 同時にベーステーブルにトリガを再設定する。 後述のfalseを指定してrefresh_immv()を実行したあとで、再びIMMVを有効にするときに使う。 |
| false | 指定したIMMVの結果は空になる。 またベーステーブルに設定されたトリガは削除される。 IMMV自体は削除されていないので、このパラメータをtrueにすることで、再びクエリの結果を反映することができる。 一時的にIMMVを無効化する、ということかな。 |
検証概要
- ベーステーブルを定義して10万件データを挿入する。
- IMMVを作成する。
- 作成直後にトリガ定義一覧を確認する。
- IMMVの内容を検索する。
- refresh_immv()をfalseモードで実行する。
- 作成直後にトリガ定義一覧を確認する。
- IMMVの内容を検索する。
- refresh_immv()をtrueモードで実行する。
- 作成直後にトリガ定義一覧を確認する。
- IMMVの内容を検索する。
ベーステーブルの作成とデータ挿入
以下のようなベーステーブルを定義する。
CREATE TABLE ivm.base (id int, i_data int, n_data numeric);
ベーステーブルにテキトーに10万件を挿入する。
INSERT INTO ivm.base VALUES
(generate_series(1, 100000), random() * 10000, random());
IMMVの作成と確認
IMMVを作成する。
SELECT create_immv('ivm.agg_immv', $$
SELECT
id, i_data, n_data
FROM ivm.base
WHERE i_data <= 1
$$
);
作成直後にトリガの状態を確認する。
SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
tgrelid | relname | tgname
---------+----------+-----------------------------------
18616 | agg_immv | IVM_prevent_immv_change_18629
18616 | agg_immv | IVM_prevent_immv_change_18630
18616 | agg_immv | IVM_prevent_immv_change_18631
18616 | agg_immv | IVM_prevent_immv_change_18632
18611 | base | IVM_trigger_del_after_18626
18611 | base | IVM_trigger_del_before_18622
18611 | base | IVM_trigger_ins_after_18625
18611 | base | IVM_trigger_ins_before_18621
18611 | base | IVM_trigger_truncate_after_18628
18611 | base | IVM_trigger_truncate_before_18624
18611 | base | IVM_trigger_upd_after_18627
18611 | base | IVM_trigger_upd_before_18623
(12 rows)
ベーステーブルと作成されたIMMVに対するトリガの一覧が表示される。
この状態でIMMVを検索する。
SELECT * FROM ivm.agg_immv;
id | i_data | n_data
-------+--------+-------------------
333 | 1 | 0.46216434577229
770 | 1 | 0.643584418527755
1380 | 1 | 0.160069154000713
（略）
93344 | 0 | 0.890123925243319
99346 | 1 | 0.211752012419627
(15 rows)
refresh_immv()をfalseで実行
作成したIMMVに対して、refresh_immvを第2引数falseで実行する。
SELECT refresh_immv('ivm.agg_immv', false);
refresh_immv
--------------
0
(1 row)
この状態で、トリガの状態を確認する。
SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
tgrelid | relname | tgname
---------+----------+-------------------------------
18616 | agg_immv | IVM_prevent_immv_change_18629
18616 | agg_immv | IVM_prevent_immv_change_18630
18616 | agg_immv | IVM_prevent_immv_change_18631
18616 | agg_immv | IVM_prevent_immv_change_18632
(4 rows)
ベーステーブルに対するトリガ定義が削除されていることがわかる。
この状態でもIMMVへの検索自体はできる。
SELECT * FROM ivm.agg_immv;
id | i_data | n_data
----+--------+--------
(0 rows)
検索はできるが、空の結果が返却されるだけになる。
refresh_immv()をtrueで実行
この状態でrefresh_immvを第2引数trueで実行する。
SELECT refresh_immv('ivm.agg_immv', true);
refresh_immv
--------------
15
(1 row)
refresh_immvの実行後に、再びトリガ状態を確認する。
SELECT t.tgrelid, c.relname, t.tgname
FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid)
ORDER BY t.tgname;
tgrelid | relname | tgname
---------+----------+-----------------------------------
18616 | agg_immv | IVM_prevent_immv_change_18629
18616 | agg_immv | IVM_prevent_immv_change_18630
18616 | agg_immv | IVM_prevent_immv_change_18631
18616 | agg_immv | IVM_prevent_immv_change_18632
18611 | base | IVM_trigger_del_after_18648
18611 | base | IVM_trigger_del_before_18644
18611 | base | IVM_trigger_ins_after_18647
18611 | base | IVM_trigger_ins_before_18643
18611 | base | IVM_trigger_truncate_after_18650
18611 | base | IVM_trigger_truncate_before_18646
18611 | base | IVM_trigger_upd_after_18649
18611 | base | IVM_trigger_upd_before_18645
(12 rows)
ベーステーブルに対するトリガ設定が復活している。
この状態でIMMVを検索すると
SELECT * FROM ivm.agg_immv;
id | i_data | n_data
-------+--------+-------------------
333 | 1 | 0.46216434577229
770 | 1 | 0.643584418527755
1380 | 1 | 0.160069154000713
（略）
93344 | 0 | 0.890123925243319
99346 | 1 | 0.211752012419627
(15 rows)
元の結果に戻る。めでたしめでたし。
3.5 create_immv()への入力が不正な場合のエラーコードを修正
（この調査は省略）
5. おわりに
pg_ivm 1.1は前回調査したときの不満点の多くが改善されたバージョンになっていることが分かりました。MAX/MINや、現状未サポートのSELECT機能にも早く対応してもらえると嬉しいですね。
あとは、AWS RDS Aurora PostgreSQL互換、Google AlloyDB、Azure PostgreSQL Flexible Server等のパブリッククラウドでこの拡張がサポートされたり、contribモジュールとしてPostgreSQL本体のコードにマージされたりすると、より気軽に使えるようになるかなあ。
-
SELECT * FROM ...で検索すると、IMMVの場合、管理用のカラムも表示されてしまうので、明示的にカラム名をSELECT句に指定した。 ↩