PostgreSQL 18がやってくる(6) 仮想生成列

# はじめに

にゃーん。
GW期間中は仕事しなくてもいいので、こういう調べ物が捗りますね。:-)
今回は（個人的にPostgreSQL 18の目玉機能だと思っている）仮想生成列[^1](Virtual generated column)について書いてみます。

# 生成列と仮想生成列
実は「生成列」自体は、PostgreSQL 12からサポートされている。
この生成列は同テーブルの他の列の参照を含む式から計算された結果を、タプル内に格納するという仕組みになっている。
（この機能は以前、自分も調べたことがある（[PostgreSQL 12がやってくる！(1) - 生成列](https://qiita.com/nuko_yokohama/items/ca53b77b2a21079c01c8)参照）
PostgreSQL 18の仮想生成列は「仮想」という言葉がついているので、たぶん「生成列」のように実体がタプルに格納されてはいない、と予想しているのだが・・・。

# 概要
[Commitfest 2025-03](https://commitfest.postgresql.org/52/)を「virtual」かつCommitedでフィルタすると2つの項目が引っかかる

|タイトル|[Virtual generated columns](https://commitfest.postgresql.org/patch/4972/)|
|:--|:--|
|Topic|SQL Commands|
|Last modified|2025-02-07 12:38:01|
|Emails|[Virtual generated columns](https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org)|

|タイトル|[support virtual generated column not null constraint](https://commitfest.postgresql.org/patch/5578/)|
|:--|:--|
|Topic|Server Features|
|Created|2025-02-18 08:38:05|
|Last modified|2025-03-28 14:06:45|
|Emails|[support virtual generated column not null constraint](https://www.postgresql.org/message-id/flat/CACJufxHArQysbDkWFmvK+D1TPHQWWTxWN15cMuUaTYX3xhQXgg@mail.gmail.com)|

"support virtual generated column not null constraint"のMLを見ると、"Virtual generated columns"がコミットされたのを受けて追加で提案されたものっぽい。

以下、それぞれの項目について見てみる。

# Virtual generated columns
まず、仮想生成列自体について見てみる。

## SQL文の変更
``CREATE TABLE``文のcolumn_constraintを見ると、PostgreSQL 17では

> where column_constraint is:
（略）
  GENERATED ALWAYS AS ( generation_expr ) STORED |

となっているが、PostgreSQL 18では
> where column_constraint is:
（略）
  GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |

``STORED``に加えて``VIRTUAL``が追加されている。この``VIRTUAL``を指定したときに、仮想生成列となる。
（``ALTER TABLE``文も同様）

## 簡単な例
まずは、簡単な例で仮想生成列がどういう動きになるかを確かめてみる。
* ``foo``というテーブルを定義して、生成列（``gc_data``）と仮想生成列（``vgc_data``）を定義する。
  * ``gc_data``列には``data``列に100を加算した値を設定する。
  * ``vgc_data``列には``data``列に10000を加算した値を設定する。
* id列とdata列のみを指定して3行挿入する。
* 挿入後に全ての行/列を検索する。

```sql
$ psql testdb -a -f 1_simple.sql
DROP TABLE IF EXISTS foo;
DROP TABLE
CREATE TABLE foo (
  id int primary key,
  data int,
  gc_data int GENERATED ALWAYS AS
  (data + 100) STORED,  -- 生成列
  vgc_data int GENERATED ALWAYS AS
  (data + 10000) VIRTUAL -- 仮想生成列
);
CREATE TABLE
\d foo
                                 Table "public.foo"
  Column  |  Type   | Collation | Nullable |                 Default
----------+---------+-----------+----------+-----------------------------------------
 id       | integer |           | not null |
 data     | integer |           |          |
 gc_data  | integer |           |          | generated always as (data + 100) stored
 vgc_data | integer |           |          | generated always as (data + 10000)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

INSERT INTO foo (id, data) VALUES
  (1, 10), (2, 20), (3, 30)
;
INSERT 0 3
SELECT * FROM foo;
 id | data | gc_data | vgc_data
----+------+---------+----------
  1 |   10 |     110 |    10010
  2 |   20 |     120 |    10020
  3 |   30 |     130 |    10030
(3 rows)

```
確かに``CREATE TABLE``文で指定したとおりの式の値が、``gc_data``列および``vgc_data``列に格納されている。

## 仮想生成列の式の結果がオーバーフローしたらどうなる？
次に、可変長文字列（varchar(n)）を生成列/仮想生成列に指定した例を考えてみる。

```sql
$ psql testdb -a -f 2_varchar.sql
DROP TABLE IF EXISTS foo;
DROP TABLE
CREATE TABLE foo (
  id int primary key,
  data1 varchar(8),
  data2 varchar(8),
  gc_data varchar(12) GENERATED ALWAYS AS
  ('ドーモ、' || data1 || '＝サン') STORED,  -- 生成列
  vgc_data varchar(12) GENERATED ALWAYS AS
  ('ドーモ、' || data2 || '＝サン') VIRTUAL  -- 仮想生成列
);
CREATE TABLE
\d foo
                                                               Table "public.foo"
  Column  |         Type          | Collation | Nullable |                                       Default

----------+-----------------------+-----------+----------+-----------------------------------------------------------------------------------
---
 id       | integer               |           | not null |
 data1    | character varying(8)  |           |          |
 data2    | character varying(8)  |           |          |
 gc_data  | character varying(12) |           |          | generated always as (((('ドーモ、'::text || data1::text) || '＝サン'::text))) stor
ed
 vgc_data | character varying(12) |           |          | generated always as (((('ドーモ、'::text || data2::text) || '＝サン'::text)))
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

```
この例では、``gc_data``列は``data1``の前後、``vgc_data``列は``data2``列の前後の文字列を連結している。今回は、``data1``,``data2``列に6文字以上の値を指定することでそれぞれオーバーフローエラーになるのかを確認してみる。

さて、実験してみると実に面白い結果になる。
まず、``gc_data``も``vgc_data``もオーバーフローしないケースを見てみる。
```sql
testdb=# INSERT INTO foo (id, data1,data2) VALUES (1, 'ヌコ','ヌコ');
INSERT 0 1
testdb=# SELECT * FROM foo ;
 id | data1 | data2 |      gc_data       |      vgc_data
----+-------+-------+--------------------+--------------------
  1 | ヌコ  | ヌコ  | ドーモ、ヌコ＝サン | ドーモ、ヌコ＝サン
(1 row)
```

次に、``gc_data``列がオーバーフローするケースを見てみる。
```sql
testdb=# INSERT INTO foo (id, data1,data2) VALUES (2, 'ポストグレス','ヌコ');
ERROR:  value too long for type character varying(12)
```
これは予想どおり、格納時に``varchar(12)``のサイズをオーバーするのでエラーになる。

次に、``vgc_data``列がオーバーフローするケースを見てみる。
```sql
testdb=# INSERT INTO foo (id, data1,data2) VALUES (3, 'ヌコ', 'ポストグレス');
INSERT 0 1
```
なんと、しれっと格納できてしまった。
しかし、格納された列（``id = 3``）を検索しようとすると、そのときにオーバーフローエラーになってしまう！
```sql
testdb=# SELECT * FROM foo WHERE id = 3;
ERROR:  value too long for type character varying(12)
testdb=#
```
``vgc_data``列をSELECTリストに含めなければフツーに検索はできる。
```sql
testdb=# SELECT id, data1, data2, gc_data FROM foo WHERE id = 3;
 id | data1 |    data2     |      gc_data
----+-------+--------------+--------------------
  3 | ヌコ  | ポストグレス | ドーモ、ヌコ＝サン
(1 row)
```
うーん、こういう仕様なのか。
**なんか検索時にエラーになるデータが格納できてしまうのって、ちょっと危うい仕様な気がする・・・。**

## 生成列・仮想生成列に値を明示的にセットしてみる
今度は、生成列や仮想生成列に明示的に値を設定してみる。
最初の例の``foo`テーブルの定義を再掲する。

```sql
testdb=# \d foo
                                 Table "public.foo"
  Column  |  Type   | Collation | Nullable |                 Default
----------+---------+-----------+----------+-----------------------------------------
 id       | integer |           | not null |
 data     | integer |           |          |
 gc_data  | integer |           |          | generated always as (data + 100) stored
 vgc_data | integer |           |          | generated always as (data + 10000)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
```
この``foo``テーブルに対して、``gc_data``列、``vgc_data``列に対し、明示的に値を設定してINSERTしてみる。
```sql
testdb=# INSERT INTO foo (id, data) VALUES (1, 10);
INSERT 0 1
testdb=# INSERT INTO foo (id, data, gc_data) VALUES (2, 20, 120);
ERROR:  cannot insert a non-DEFAULT value into column "gc_data"
DETAIL:  Column "gc_data" is a generated column.
testdb=# INSERT INTO foo (id, data, vgc_data) VALUES (3, 30, 10030);
ERROR:  cannot insert a non-DEFAULT value into column "vgc_data"
DETAIL:  Column "vgc_data" is a generated column.
```
**想定どおり、生成列・仮想生成列に対して明示的に値を設定しようとするとエラーになる。**
なお、``id=1``の行の``gc_data``列や``vgc_data``列をUPDATE文で更新しようとしても同様のエラーになる。
```sql
testdb=# UPDATE foo SET gc_data = 0 WHERE id = 1;
ERROR:  column "gc_data" can only be updated to DEFAULT
DETAIL:  Column "gc_data" is a generated column.
testdb=# UPDATE foo SET vgc_data = 0 WHERE id = 1;
ERROR:  column "vgc_data" can only be updated to DEFAULT
DETAIL:  Column "vgc_data" is a generated column.
```

## 仮想生成列にインデックスは設定できるのか
生成列には実はインデックスも設定できる。（PostgreSQ 12調査のときに試した）
では、仮想生成列にはインデックスは設定できるのだろうか？

最初の例の``foo`テーブルの定義を再掲する。

```sql
testdb=# \d foo
                                 Table "public.foo"
  Column  |  Type   | Collation | Nullable |                 Default
----------+---------+-----------+----------+-----------------------------------------
 id       | integer |           | not null |
 data     | integer |           |          |
 gc_data  | integer |           |          | generated always as (data + 100) stored
 vgc_data | integer |           |          | generated always as (data + 10000)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
```
``vgc_data``にインデックスを設定してみる。

```sql
testdb=# CREATE INDEX vgc_data_idx ON foo USING btree (
data      gc_data   id        vgc_data
testdb=# CREATE INDEX vgc_data_idx ON foo USING btree (vgc_data);
ERROR:  indexes on virtual generated columns are not supported
testdb=#
```

``psql``のTAB補完で仮想生成列も候補には挙がってくるのだが、実際に設定しようとすると、**仮想生成列へのインデックスは未サポート、というエラーになる。**
``psql``のTAB補完の改善ポイント（列が生成仮想列だったら、インデックス列の候補に挙げない）なのかもしれないけど、今のところ実装されていないのは、いうほど簡単ではないからなんだろうかな。

## 生成列 vs 仮想生成列
PostgreSQL 18で生成列と仮想生成列の2つの生成列が使えるようになったわけだが、どう使い分けるのがいいのだろうか？

ということで、今回はテーブルのサイズ・テーブルへの格納性能・テーブルの検索性能の観点で調べてみた。
おそらく実装内容から想定すると、以下のような違いがあるはず。
* テーブルサイズは仮想生成列のほうが小さい（生成後の結果をタプルに持たないから）
* 挿入性能は仮想生成列のほうが小さい（生成のための演算および、生成値をタプルに持たない状態で挿入するから）
* 検索性能は生成列のほうが小さい（仮想生成列は検索時に生成列定義時の演算を行うため）


以下のような生成列を持つUNLOGGEDテーブル``foo``と仮想生成列を持つUNLOGGEDテーブル``bar``を用意する。
（UNLOGGEDテーブルにすることでWAL書き込み時間以外の挿入時間を確認する）

```sql
$ psql testdb -a -f 3_gc.sql -f 3_vgc.sql
DROP TABLE IF EXISTS foo;
DROP TABLE
CREATE UNLOGGED TABLE foo (
  id int primary key,
  data text,
  gc_data text GENERATED ALWAYS AS
  (repeat(data, 10)) STORED -- 生成列
);
CREATE TABLE
\d foo
                               Unlogged table "public.foo"
 Column  |  Type   | Collation | Nullable |                    Default
---------+---------+-----------+----------+-----------------------------------------------
 id      | integer |           | not null |
 data    | text    |           |          |
 gc_data | text    |           |          | generated always as (repeat(data, 10)) stored
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

DROP TABLE IF EXISTS bar;
DROP TABLE
CREATE UNLOGGED TABLE bar (
  id int primary key,
  data text,
  vgc_data text GENERATED ALWAYS AS
  (repeat(data, 10)) VIRTUAL -- 仮想生成列
);
CREATE TABLE
\d bar
                            Unlogged table "public.bar"
  Column  |  Type   | Collation | Nullable |                Default
----------+---------+-----------+----------+----------------------------------------
 id       | integer |           | not null |
 data     | text    |           |          |
 vgc_data | text    |           |          | generated always as (repeat(data, 10))
Indexes:
    "bar_pkey" PRIMARY KEY, btree (id)
```
``foo``テーブルと``bar``テーブルに``data``列に32文字のランダムな文字列（md5関数の結果）を設定した10万件のデータを挿入する。

挿入SQLのイメージ
```sql
INSERT INTO テーブル名 (id, data) VALUES (generate_series(1,100000), md5(clock_timestamp()::text));
```

挿入後にテーブルサイズ（``pg_relation_size('テーブル名')``を取得する。
そして、それぞれのテーブルに対して3回以下のイメージのSELECT文を実行してEXPLAIN ANALYZEのExecution Timeの平均で評価する。
```sql
EXPLAIN ANALYZE SELECT length(仮想列名) FROM テーブル名;
```

それぞれの測定結果を見てみる。
|方式|テーブルサイズ(MiB)|挿入時間(ms)|検索時間(ms)|
|:--|--:|--:|--:|
|生成列(STORED)| 39.1| 331.468| 181.418|
|仮想生成列(VIRTUAL)| 6.5| 246.206| 184.371|

ということで想定通りの結果が得られた。
（挿入時間差に対して検索時間の差があまりないのは、``repaet(列, 10)の演算×10万回``の操作の処理時間が数ミリ秒程度だったからなのだと思われる）

極端に重い演算の生成列定義をしなければ、仮想生成列のほうが有利なケースが多いということがかもしれない。

# support virtual generated column not null constraint
こちらは、生成列に対して``not null``制約を付与する、というものである。

例えば以下のような文字列を連結する仮想生成列を定義する。
```sql$ psql testdb -a -f 4_not_null.sql
DROP TABLE IF EXISTS foo;
DROP TABLE
CREATE TABLE foo (
  id int primary key,
  data varchar(8),
  vgc_data varchar(12) GENERATED ALWAYS AS
  ('ドーモ、' || data || '＝サン') VIRTUAL  -- 仮想生成列
);
CREATE TABLE
\d foo
                                                           Table "public.foo"
  Column  |         Type          | Collation | Nullable |                                   Default
----------+-----------------------+-----------+----------+------------------------------------------------------------------------------
 id       | integer               |           | not null |
 data     | character varying(8)  |           |          |
 vgc_data | character varying(12) |           |          | generated always as (((('ドーモ、'::text || data::text) || '＝サン'::text)))
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
```
この``foo``テーブルの``data``列にNULLを設定すると、仮想性整列``vgc_data``もNULLになる。
```sql
testdb=# INSERT INTO foo (id, data) VALUES (1,'ヌコ'),(2, NULL);
INSERT 0 2
testdb=# \pset null (null)
Null display is "(null)".
testdb=# SELECT * FROM foo;
 id |  data  |      vgc_data
----+--------+--------------------
  1 | ヌコ   | ドーモ、ヌコ＝サン
  2 | (null) | (null)
(2 rows)
```
PostgreSQL 18の生成列にはNOT NULL制約が設定可能である。

```sql
testdb=# ALTER TABLE foo ALTER vgc_data SET NOT NULL ;
ALTER TABLE
testdb=# \d foo
                                                           Table "public.foo"
  Column  |         Type          | Collation | Nullable |                                   Default
----------+-----------------------+-----------+----------+------------------------------------------------------------------------------
 id       | integer               |           | not null |
 data     | character varying(8)  |           |          |
 vgc_data | character varying(12) |           | not null | generated always as (((('ドーモ、'::text || data::text) || '＝サン'::text)))
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
```
この状態でINSERT文を実行する。
```sql
testdb=# INSERT INTO foo (id, data) VALUES (1,'ヌコ');
INSERT 0 1
testdb=# INSERT INTO foo (id, data) VALUES (2, NULL);
ERROR:  null value in column "vgc_data" of relation "foo" violates not-null constraint
DETAIL:  Failing row contains (2, null, virtual).
testdb=#
```
2回目のINSERTは``data``列にNULLを設定しようとする。
``data``列自体はNOT NULL制約はかけていないが、``data``列から演算された結果（NULL）は生成仮想列``vgc_data``のNOT NULL制約に引っかかるようになった。

# おわりに
PostgreSQL 18の新機能「仮想生成列」についてざっと調べてみました。
なかなか面白い機能なんだけど、実案件で使う機会あるかな・・・？

[^1]: 「仮想生成列」という訳語は自分が暫定的につけたものです。PostgreSQL 18リリース後に、JPUGの[文書・書籍関連分科会]が日本語文書を翻訳する際には、別の約語になっているかもしれません。