こんにちは。最近、体型維持の目的で筋トレを始めたbadaikiです。
先日、業務でPostgreSQLのテーブルサイズを調査することがあり、PostgreSQLの仕様の理解が不足していると実感しました。今回はそのことについて備忘録的に書いていこうと思います。
はじめに
冒頭にも記載しましたが、業務でPostgreSQLのテーブルサイズを調査する機会がありました。テーブルサイズを調査する上でPostgreSQLの仕様について理解したことや、テーブルサイズの調べ方をまとめていきます。
実は過去に資格受験でこの辺りを学習していたのですが、すっかり内容を忘れておりました。資格学習での知識って実用可能なレベルで理解するのは難しいですね...
PostgreSQLのデータサイズの持ち方
概要
PostgreSQLは固定長のページサイズ(通常8kB)を使用し、複数ページにまたがる行(tuple)を許しません。それによって大規模なフィールド値を直接格納することができません。そこでフィールド値を圧縮したり、複数の物理的な行に分割するTOASTと呼ばれる技法を用いたりして大規模なフィールド値を格納しています。
※なお、TOASTは可変長(varlena)表現を持つデータ型のみサポートしています。
TOASTテーブル
ページサイズを超過し物理的に分割された行はTOASTテーブルに格納されます。TOASTテーブルは通常のテーブルを定義するとそのテーブル専用のTOASTテーブルが作成されます。TOASTテーブル名はpg_toast_{対象テーブルのoid}
になります。
TOASTテーブルの構成は以下のようになっています。
名前 | 型 | 説明 |
---|---|---|
chunk_id | oid | 特定のTOAST化された値を識別するOID |
chunk_seq | integer | 値の塊に対する連番 |
chunk_data | bytea | 塊の実際のデータ |
そしてchunk_data
の値がTOAST_TUPLE_TARGET
バイト(通常1994Byte)より小さくなるかそれ以上の縮小ができなくなるまで、フィールド値の圧縮や行外への移動を繰り返します。
下のレコードは約150kBのフィールド値をもつテーブルのTOASTテーブルです。
実際に取得してみる
システムカタログ
テーブルサイズを取得するために登場するシステムカタログを紹介します。その中でも登場するカラムのみ抜粋して説明していきます。
- pg_class
このカタログは、テーブルとその他に列を持つもの、あるいはテーブルに似た全てのものを列にしています。その中にはインデックス(pg_indexも参照)、シーケンス、ビュー、マテリアライズドビュー、複合型およびTOASTテーブルが含まれます。
名前 | 型 | 説明 |
---|---|---|
oid | oid | 行識別子(隠し属性です。明示的に選択しなければなりません) |
relname | name | テーブル、インデックス、ビューなどの名前 |
relnamespace | oid | このリレーションを持つ名前空間のOID |
reltoastrelid | oid | このテーブルに関連しているTOASTテーブルのOID。 何もない場合はゼロです。 TOASTテーブルは"行に収まらない"大きい属性を副テーブルに格納します。 |
relkind | char | rは通常のテーブル、iはインデックス、Sはシーケンス、vはビュー、mはマテリアライズドビュー、cは複合型、tはTOASTテーブル、fは外部テーブルを表します。 |
- pg_namespace
pg_namespaceカタログは名前空間を保存します。名前空間はSQLスキーマの裏にある構造です。それぞれの名前空間は、リレーション、型などの集合を、名前が競合することなく、個別に持ちます。
これはpg_class.relnamespace
から参照されます。
名前 | 型 | 説明 |
---|---|---|
oid | oid | 行識別子(隠し属性です。明示的に選択しなければなりません) |
nspname | name | 名前空間の名前 |
取得手順
①対象テーブル情報を取得する
SELECT pc.oid, relname, reltoastrelid FROM pg_class pc INNER JOIN pg_namespace pn ON relnamespace = pn.oid WHERE nspname IN ('public', 'pg_catalog') AND relkind IN ('r', 'S', 'i') AND relname = '{対象テーブル名}' ORDER BY relname;
WHERE句の条件にTOAST要素を追加するとTOASTテーブルも取得できます。
SELECT pc.oid, relname, reltoastrelid FROM pg_class pc INNER JOIN pg_namespace pn ON relnamespace = pn.oid WHERE nspname IN ('public', 'pg_catalog', 'pg_toast') AND relkind IN ('r', 'S', 'i', 't') AND relname = 'pg_toast_16404' ORDER BY relname;
②対象テーブル中の行の長さを取得する
SELECT tuple_len FROM pgstattuple(16408);
pgstattuple()
関数を呼び出すにはモジュールを取り込む必要があります。
取り込み方法は↓のサイトに記載されています。
https://www.postgresql.jp/document/9.4/html/contrib.html
pgstattuple()
関数の引数はテーブル名でも可能です。
select tuple_len from pgstattuple('big_tuple');
select tuple_len from pgstattuple('pg_toast.pg_toast_16404');
TOASTテーブルはpg_toast
というTOAST専用のスキーマに所属しており、またpg_toastはスキーマサーチパスに含まれていないので、検索するときはスキーマ名をテーブル名の前に付けておきます。
③(参考)TOASTテーブルの取得
SELECT chunk_id, chunk_seq, chunk_data, OCTET_LENGTH(chunk_data) FROM pg_toast.pg_toast_16404
おわりに
今回は業務でPostgreSQLのテーブルサイズを調査したことをきっかけに、どのような構成でTOASTが成り立っているのかを更に調べました。1行に大規模なデータが格納されている場合に、どのような仕様で実際どのような形で格納されているのかを確認することができました。
1つの物事に焦点を当て、深掘りする楽しさを経験することができました。
参考
エンジニア中途採用サイト
ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております!
ご興味ありましたら是非ご確認をお願いします。
https://career-recruit.rakus.co.jp/career_engineer/カジュアル面談お申込みフォーム
どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
以下フォームよりお申込みください。
rakus.hubspotpagebuilder.comラクスDevelopers登録フォーム
https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/イベント情報
会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください!
◆TECH PLAY
techplay.jp
◆connpass
rakus.connpass.com