pg_ivm(IMMV)を使ってみた(2) 1.1での改善点など

# 1. はじめに
にゃーん。
今日は、先週、バージョン1.1がリリース（[リリースニュース](https://www.postgresql.org/about/news/pg_ivm-11-released-2478/)）された[pg_ivm](https://github.com/sraoss/pg_ivm)について、実際に動作させて改善点を検証しました。

----
# 2. What's pg_ivm?
pg_ivmはPostgreSQLでIncremental View Maintenanceを実現する拡張機能です。
Incremental View Maintenanceとは、再計算を行わず、増分変更のみを計算してビューに適用することで、実体化したビューを最新の状態にする方法です。

この増分変更の計算と適用の方式に関する考え方は、論文などでも紹介されています（[PostgreSQL Wiki - Incremental View Maintenance](https://wiki.postgresql.org/wiki/Incremental_View_Maintenance)等参照）が、自分は猫なのでそういう難しいことはわかりません。本記事ではあくまでも使い方について書いてみます。

詳細は前回書いた記事（[pg_ivm(IMMV)を使ってみた(1)](https://qiita.com/nuko_yokohama/items/0ae59f5d797bc79dcc06)）を参照していただければと。

----
# 3. pg_ivm 1.1の改善点
[pg_ivm 1.1のリリースノート](https://github.com/sraoss/pg_ivm/releases/tag/v1.1)に記載されている改善点は以下のとおり。

* PostgreSQL 13のサポート
* 集約関数のサポート
* ベーステーブルに対するTRUNCATEのサポート
* refresh_immv()関数の追加
* create_immv()への入力が不正な場合のエラーコードを修正

以下、それぞれの改善点について見ていきます。

## 3.1 PostgreSQL 13のサポート
[前回調査](https://qiita.com/nuko_yokohama/items/0ae59f5d797bc79dcc06)時は、PostgreSQL 14上での動作を確認しましたが、pg_ivm 1.1ではPostgreSQL 13にも対応したとのこと。

手元の環境（PostgreSQL 12, 13, 14, 15 beta1）でpg_ivmがビルドできるかを一応確認してみました。

|バージョン|ビルド可否|備考|
|:--|:-:|:--|
|PostgreSQL 12|✗|`QueryCompletion`型がない<br>`INT4OID`がない<br>`IndexStmt`のメンバ`oldCreateSubid`,<br<`oldFirstRelfilenodeSubid`,<br>`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型を持つ。

```sql
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` の数値を格納する。

```sql
INSERT INTO ivm.base VALUES
  (generate_series(1, 100000), (random() * 10000)::int, random(), random());
```

### 各ビューの作成
3種類のビューを作成する。それぞれのビューの生成元となるSELECT文は以下とする。

```sql
  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
```
実際のビュー定義は以下のようになる。

```sql
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]

```sql
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も実施する。

```sql
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)
再度データ挿入した後で再検索する。

```sql
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万件入った状態で、各ビューの結果を表示します。
```sql
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します。
またマテリアライズド・ビューはリフレッシュしておきます。

```sql
TRUNCATE ivm.base;
TRUNCATE TABLE
REFRESH MATERIALIZED VIEW ivm.agg_mv;
REFRESH MATERIALIZED VIEW
```

TRUNCATEした後は、ビュー、マテリアライズド・ビュー、IMMVともにCOUNTが0件、SUM, AVGの結果はNULLになりました。めでたしめでたし。

```sql
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がつく関数を探してみます。

```sql
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に設定する。<br>同時にベーステーブルにトリガを再設定する。<br>後述のfalseを指定してrefresh_immv()を実行したあとで、再びIMMVを有効にするときに使う。|
|false|指定したIMMVの結果は空になる。<br>またベーステーブルに設定されたトリガは削除される。<br>IMMV自体は削除されていないので、このパラメータをtrueにすることで、再びクエリの結果を反映することができる。<br>一時的にIMMVを無効化する、ということかな。|

### 検証概要
* ベーステーブルを定義して10万件データを挿入する。
* IMMVを作成する。
* 作成直後にトリガ定義一覧を確認する。
* IMMVの内容を検索する。
* refresh_immv()をfalseモードで実行する。
* 作成直後にトリガ定義一覧を確認する。
* IMMVの内容を検索する。
* refresh_immv()をtrueモードで実行する。
* 作成直後にトリガ定義一覧を確認する。
* IMMVの内容を検索する。

### ベーステーブルの作成とデータ挿入
以下のようなベーステーブルを定義する。
```sql
CREATE TABLE ivm.base (id int, i_data int, n_data numeric);
```
ベーステーブルにテキトーに10万件を挿入する。
```sql
INSERT INTO ivm.base VALUES
  (generate_series(1, 100000), random() * 10000, random());
```
### IMMVの作成と確認
IMMVを作成する。
```sql
SELECT create_immv('ivm.agg_immv', $$
  SELECT
    id, i_data, n_data
  FROM ivm.base
  WHERE i_data <= 1
$$
);
```
作成直後にトリガの状態を確認する。
```sql
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を検索する。
```sql
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で実行する。
```sql
SELECT refresh_immv('ivm.agg_immv', false);
 refresh_immv
--------------
            0
(1 row)
```
この状態で、トリガの状態を確認する。
```sql
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への検索自体はできる。

```sql
SELECT * FROM ivm.agg_immv;
 id | i_data | n_data
----+--------+--------
(0 rows)
```
検索はできるが、空の結果が返却されるだけになる。

### refresh_immv()をtrueで実行
この状態でrefresh_immvを第2引数trueで実行する。
```sql
SELECT refresh_immv('ivm.agg_immv', true);
 refresh_immv
--------------
           15
(1 row)
```
refresh_immvの実行後に、再びトリガ状態を確認する。
```sql
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を検索すると
```sql
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は[前回調査](https://qiita.com/nuko_yokohama/items/0ae59f5d797bc79dcc06)したときの不満点の多くが改善されたバージョンになっていることが分かりました。MAX/MINや、現状未サポートのSELECT機能にも早く対応してもらえると嬉しいですね。

あとは、AWS RDS Aurora PostgreSQL互換、Google AlloyDB、Azure PostgreSQL Flexible Server等のパブリッククラウドでこの拡張がサポートされたり、contribモジュールとしてPostgreSQL本体のコードにマージされたりすると、より気軽に使えるようになるかなあ。

[^1]: ``SELECT * FROM ...``で検索すると、IMMVの場合、管理用のカラムも表示されてしまうので、明示的にカラム名をSELECT句に指定した。