はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 12日目の記事です。
今回はALTER TABLEコマンドのちょっとした変更について書いてみます。
概要
| 項目 | 内容 |
|---|---|
| タイトル | ALTER TABLE ... SET STORAGE default |
| Topic | Server Features |
| ステータス | commited |
| Last Modified | 2022-11-10 |
| 概要 | ALTER TABLEのSET STORAGEオプションに関する変更 |
変更内容
発端となったメール([PATCH] ALTER TABLE ... SET STORAGE default)に書かれていたのは、
- ALTER TABLEに
SET COMPRESSION defaultがある。 - でも、
SET STORAGE defaultがないのは変では？ - 一度設定したストレージ種別を簡単に元に戻せないのではないか。
という内容のようです。
SET STORAGE設定
PostgreSQLはページ(通常8192byte)という固定長の領域を使ってテーブルやインデックスの値を管理しています。
では、8192byteを超えるような長大な可変長データはどうやって管理しているのでしょうか？
PostgreSQLではTOASTと呼ばれる方法を使って長大なデータ（だいたい2Kbyteを超えるもの）を管理しています。TOASTは別領域（TOASTテーブル）に追い出して分割格納したり、対象の領域を圧縮したりします。
TOASTには4つの格納戦略があり、それを利用者が列単位で指定することができます。
| 戦略名 | 内容 |
|---|---|
| PLAIN | TOASTテーブルへの追い出しや圧縮を行わない。 短い固定長データ型(integerとか)はこの戦略になる。 |
| EXTENDED | TOASTテーブルへの追い出しと圧縮を許可する。 組み込みの可変長データ(textとか)はこの戦略になる。 |
| EXTERNAL | TOASTテーブルへの追い出しを行う。データ量は大きくなるが、少しだけ取り出しが早い。 この戦略がデフォルトで設定されている組み込みデータ型はたぶんないはず。 |
| MAIN | 圧縮は許容するが、極力TOASTテーブルへの追い出しは行わない。短めの可変長データ(numericとか)はこの戦略になる。 |
格納戦略は、psqlの\d+ <テーブル名>などのメタコマンドで確認することができます。
以下は、PostgreSQL 15での表示例です。
Storage列に格納戦略が表示されています。
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | extended | | |
Access method: heap
=#
格納戦略はデータ型によりデフォルトの戦略が設定されていますが、ALTER TABLE ... SET STORAGEコマンドにより変更することができます。
あえて格納戦略の変更が必要なケースはほとんどない、と自分は思っていますが・・・。
以下は、ALTER TABLEコマンドで、text型のtdata列の格納戦略をexternalに変更する例です。
=# ALTER TABLE test ALTER COLUMN tdata SET STORAGE EXTERNAL;
ALTER TABLE
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | external | | |
Access method: heap
=#
PostgreSQL 15まで
PostgreSQL 15までは、格納戦略を変更したあとデータ型デフォルトの格納戦略に戻すためには、明示的にその格納戦略を指定する必要がありました。
=# ALTER TABLE test ALTER COLUMN tdata SET STORAGE EXTENDED;
ALTER TABLE
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | extended | | |
Access method: heap
=#
PostgreSQL 16から
PostgreSQL 16からは、格納戦略を変更したあとデータ型デフォルトの格納戦略に戻すために、SET STORAGE defaultという指定が可能になりました。
ALTER TABLEでtext型のtdataの格納戦略をexternalに変更しておきます。
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | extended | | |
Access method: heap
=# ALTER TABLE test ALTER COLUMN tdata SET STORAGE EXTERNAL;
ALTER TABLE
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | external | | |
Access method: heap
=#
この状態からtdataの格納戦略をデフォルト(extended)に戻すために、ALTER TABLE ... SET STORAGE defaultを実行します。
=# ALTER TABLE test ALTER COLUMN tdata SET STORAGE default ;
ALTER TABLE
=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
n_data | numeric | | | | main | | |
f_data | double precision | | | | plain | | |
tdata | text | | | | extended | | |
Access method: heap
=#
tdataの格納戦略がextendedに変更されたことが確認できました。
余談
このSET STORAGE defaultという指定方法ですが、MLでの議論ではRESET STORAGEとかのほうが良いのでは？という話も出ていたようですが、既にSET COMPRESSION compression_methodのcompression_methodにdefaultを使っているから、そっちにならおう、という経緯があったみたいです（自分がMLの内容を読み違えてなければ・・・）。
おわりに
今回はALTER TABLEで指定する格納戦略指定に関するちょっとした改善について書きました。
格納戦略については、以前自分も実験はしてPostgreSQLアンカンファレンスで発表したのですが（TOAST）、こうかはばつぐんだ！という用途はまだ良くわかっていないです・・・。