LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@nuko_yokohama(nuko yokohama)

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

Last updated at Posted at 2025-04-29

はじめに

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

生成列と仮想生成列

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

概要

Commitfest 2025-03を「virtual」かつCommitedでフィルタすると2つの項目が引っかかる

タイトル Virtual generated columns
Topic SQL Commands
Last modified 2025-02-07 12:38:01
Emails Virtual generated columns
タイトル support virtual generated column not null constraint
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

"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行挿入する。
  • 挿入後に全ての行/列を検索する。
$ 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)）を生成列/仮想生成列に指定した例を考えてみる。

$ 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もオーバーフローしないケースを見てみる。

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列がオーバーフローするケースを見てみる。

testdb=# INSERT INTO foo (id, data1,data2) VALUES (2, 'ポストグレス','ヌコ');
ERROR:  value too long for type character varying(12)

これは予想どおり、格納時にvarchar(12)のサイズをオーバーするのでエラーになる。

次に、vgc_data列がオーバーフローするケースを見てみる。

testdb=# INSERT INTO foo (id, data1,data2) VALUES (3, 'ヌコ', 'ポストグレス');
INSERT 0 1

なんと、しれっと格納できてしまった。
しかし、格納された列（id = 3）を検索しようとすると、そのときにオーバーフローエラーになってしまう！

testdb=# SELECT * FROM foo WHERE id = 3;
ERROR:  value too long for type character varying(12)
testdb=#

vgc_data列をSELECTリストに含めなければフツーに検索はできる。

testdb=# SELECT id, data1, data2, gc_data FROM foo WHERE id = 3;
 id | data1 |    data2     |      gc_data
----+-------+--------------+--------------------
  3 | ヌコ  | ポストグレス | ドーモ、ヌコ＝サン
(1 row)

うーん、こういう仕様なのか。
なんか検索時にエラーになるデータが格納できてしまうのって、ちょっと危うい仕様な気がする・・・。

生成列・仮想生成列に値を明示的にセットしてみる

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

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してみる。

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文で更新しようとしても同様のエラーになる。

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`テーブルの定義を再掲する。

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にインデックスを設定してみる。

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書き込み時間以外の挿入時間を確認する）

$ 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のイメージ

INSERT INTO テーブル名 (id, data) VALUES (generate_series(1,100000), md5(clock_timestamp()::text));

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

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になる。

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制約が設定可能である。

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文を実行する。

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の[文書・書籍関連分科会]が日本語文書を翻訳する際には、別の約語になっているかもしれません。 ↩

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?