TOAST

自己紹介 ● ぬこ＠横浜 (@nuko_yokohama) ● にゃーん ● 趣味でポスグレをやってる者だ ● パンよりご飯が好き。麺はもっと好き。

お題 ● TOAST is 何？ ● TOAST のしくみ ● ストレージ格納戦略 ● EXTENDED vs EXTERNAL ● TOAST_TUPLE_TARGET

TOAST is 何？

TOAST is 何？ ● The Over-sized Attribute Storage Technique ● 過大属性格納技法 ● 非常に長いデータを PostgreSQL の固定長 8Kb の ページに収めるための手法。 ● パンをスライスして以来最善のもの ● マジ透過的 英略語は COOL だし 日本語表記もカッコイイ。

TOAST is 何？ ● 普通に PostgreSQL を使っているぶんには意識しなくてヨシ！ なんだか知らんが 動いているからヨシ！

TOAST のしくみ

TOAST のしくみ ● 約 2Kb 以上の長さの列が TOAST の対象となる。 ● TOAST の対象となった列は、圧縮されたり、あるいは別の TOAST テーブルと呼ばれるテーブルに格納される。 ● TOAST テーブル内では、長大データは 2Kb のチャンク (Chunk) によって分割され格納される。 ● 通常のテーブルには TOAST テーブルへのポインタが格納される。

TOAST のしくみ ● テーブルがどの TOAST テーブルに対応しているのかは、シス テムカタログ pg_class の reltoastrelid を見ればわかる。 testdb=# SELECT relname, relfilenode, reltoastrelid FROM pg_class WHERE relname = 'test'; relname | relfilenode | reltoastrelid ---------+-------------+--------------- test | 132110 | 132113 (1 row) ● で、 relfilenode と reltoastrelid の値で示されたファイルの中を見てみると・・・

TOAST のしくみ ● relfilenode の hexdump $ hexdump -C 132117 00000000 00 00 00 00 10 71 96 1d 00 00 00 00 1c 00 d0 1f |.....q..........| 00000010 00 20 04 20 00 00 00 00 d0 9f 5c 00 00 00 00 00 |. . ......\.....| 00000020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00001fd0 24 04 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |$...............| 00001fe0 01 00 02 00 06 08 18 00 01 00 00 00 01 12 cd 09 |................| 00001ff0 00 00 c9 09 00 00 1b 04 02 00 18 04 02 00 00 00 |................| 00002000 ● reltoastrelid の hexdump $ hexdump -C 132120 00000000 00 00 00 00 80 70 96 1d 00 00 00 00 20 00 e8 15 |.....p...... ...| 00000010 00 20 04 20 00 00 00 00 10 98 e0 0f e8 95 42 04 |. . ..........B.| 00000020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 000015e0 00 00 00 00 00 00 00 00 24 04 00 00 00 00 00 00 |........$.......| 000015f0 01 00 00 00 00 00 00 00 02 00 03 00 02 08 18 00 |................| 00001600 1b 04 02 00 01 00 00 00 04 08 00 00 30 34 32 34 |............0424| 00001610 33 39 39 31 34 36 33 37 38 30 35 39 35 36 36 37 |3991463780595667| 00001620 39 33 32 33 38 37 32 39 36 33 36 37 30 34 32 35 |9323872963670425| ・・・

Storage 格納戦略

Storage 格納戦略 ● PostgreSQL では TOAST に関わる格納戦略を選択できる。 格納戦略名 略記 意味 PLAIN p 値は必ずそのまま格納される。固定長の型はだいたいこれ。 ダメならエラーでいいや。諦めのいい奴。 MAIN m まず、値を圧縮して格納しようと試みる。 例外的に圧縮しても格納できなければ TOAST テーブルに格納する。 EXTERNAL e 格納できなかったら TOAST テーブルに非圧縮で格納する。 EXTENDED x まず、値を圧縮して格納しようと試みる。圧縮が有効そうでなければ、 TOAST テーブルに格納する。

Storage 格納戦略 ● Storage 格納戦略は、データ型によってデフォルトの戦略が規程され ている。 – integer などの短い固定長データは plain – text などの可変長データは extended – 一部のデータ型が main （ numeric など） – デフォルトが external のデータ型はなさげ。 ● どの型がどの戦略を使うかは、 pg_type.typstorage で確認可能。 ● Storage 格納戦略は ALTER TABLE コマンドで列単位で変更可能。

Storage 格納戦略 ● ALTER TABLE コマンドで列の Storage 格納戦略を変更した場 合も、既に格納ずみのデータは変更されない。 – 過去にはとらわれない奴。 ● Storage 格納戦略が適用されるのは、設定後に新しく行が追加 されたとき。

Storage 格納戦略 (1) ● 検証用スクリプト CREATE TABLE test ( id int, data text ); ALTER TABLE test ALTER COLUMN data SET STORAGE plain; INSERT INTO test VALUES (1, '0001_' || repeat(generate_random_numeric_text(500), 5) ), (2, '0002_' || repeat(generate_random_numeric_text(2500), 1) ), (3, '0003_' || repeat(generate_random_numeric_text(500), 5) ), (4, '0004_' || repeat(generate_random_numeric_text(2500), 1) ), (5, '0005_' || repeat(generate_random_numeric_text(500), 5) ) ; SELECT relname, reltype, relfilenode, reltoastrelid FROM pg_class WHERE relname = 'test'; CHECKPOINT; XXXX に以下の値を設定。 plain, main, external, extended ● id が 1, 3, 5 のデータは圧縮効果があるデータ。 2, 4 は圧縮効果がないデータ。

Storage 格納戦略 (1) ● 圧縮効果がある 2500 バイトのデータ＆圧縮効果のない 2500 バイトのデータを 5 レコード格納 格納戦略名 主テーブル TOAST テーブル PLAIN 非圧縮データの状態で 2 ブロックに 格納。 格納はされない。 MAIN 非圧縮 / 圧縮混在で 1 ブロックに格 納する。 格納はされない。 EXTERNAL data は TOAST ポイントのみ。 1 ブ ロックに格納する。 非圧縮状態で 2 ブロックに格納する。 EXTENDED 圧縮または TOAST ポインタとして 1 ブロックに格納する。 非圧縮状態で 1 ブロックに格納する。

Storage 格納戦略 (2) ● 検証用スクリプト CREATE TABLE test ( id int, dummy char(7500), data text ); ALTER TABLE test ALTER COLUMN dummy SET STORAGE plain; ALTER TABLE test ALTER COLUMN data SET STORAGE XXXX; INSERT INTO test VALUES (1, '', repeat(generate_random_numeric_text(512), 2) ) ; INSERT INTO test VALUES (2, '', repeat(generate_random_numeric_text(1024), 1) ) ; SELECT relname, reltype, relfilenode, reltoastrelid FROM pg_class WHERE relname = 'test'; CHECKPOINT; XXXX に以下の値を設定。 plain, main, external, extended

Storage 格納戦略 (2) ● 圧縮しないと主テーブル用のページに格納できないが、 圧縮すれば主テーブル用のページ内に格納できるケース 格納戦略名 挙動 PLAIN エラーになる。 MAIN 値を圧縮して主テーブルに格納する。 格納できない場合、圧縮せずに TOAST テーブルに格納する。 EXTERNAL 値を圧縮せずに TOAST テーブルに格納する。 EXTENDED 値を圧縮して主テーブルに格納する。 格納できない場合、圧縮して TOAST テーブルに格納する。

EXTENDED vs EXTERNAL

EXTENDED vs EXTERNAL ● TEXT 型や bytea 型のような長大なデータが格納されるデータ型 の Storage 格納戦略としては EXTENDED がデフォルトの格納戦 略となる。 ● ALTER TABLE で格納戦略を EXTERNAL 等に変更することもでき る。 ● EXTENDED を EXTERNAL に変更して嬉しいケースはあるのか？

EXTENDED vs EXTERNAL ● 測定モデル（圧縮が効くケース） CREATE TABLE test ( id int, data1 text, data2 text, data3 text, data4 text ); ALTER TABLE test ALTER COLUMN data1 SET STORAGE extended; ALTER TABLE test ALTER COLUMN data2 SET STORAGE extended; ALTER TABLE test ALTER COLUMN data3 SET STORAGE extended; ALTER TABLE test ALTER COLUMN data4 SET STORAGE extended; \timing INSERT INTO test VALUES (generate_series(1, 2500), '0001_' || repeat(generate_random_numeric_text(500), 5), '0002_' || repeat(generate_random_numeric_text(500), 5), '0003_' || repeat(generate_random_numeric_text(500), 5), '0004_' || repeat(generate_random_numeric_text(500), 5) ); EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test; 実際には 5 回測定、 2 回目以降の 4 回分の平均を取得 任意の長さの数字文字列を 生成する自作 SQL 関数 plain/main/ extended/external を 設定して違いを見てみる。

EXTENDED vs EXTERNAL ● テーブルサイズ TOAST 含むサイズ。 主テーブルのみのサイズ

EXTENDED vs EXTERNAL ● 挿入時間

EXTENDED vs EXTERNAL ● 検索 (SeqScan actual time ）

EXTENDED vs EXTERNAL ● 雑なまとめ – EXTERNAL のほうが EXTENDED と比べると挿入時間 / 検索時間とも すこーしだけ速い。 – 代わりに EXTERNAL はサイズがめっさ大きくなる – 性能とサイズのトレードオフ ● その他気づいたこと。 – TOAST テーブルファイルの読み込みは共有バッファに乗らない？ (EXPLAIN ANALYZE の buffer hit にカウントされない）

TOAST_TUPLE_TARGET

TOAST_TUPLE_TARGET ● TOAST 処理を行うかどうかの閾値。だいたい 2K くらい。 ● この値は ./src/include/access/heaptoast.h で定義されている。 ● 単なる定数ではない。 ● この閾値を変更するためにはヘッダファイルの変更と再コンパイル が必要。（自分もやったことはない） – configure オプションでも変更はできない。

おわり なんだか知らんが 動いているからヨシ！