PostgreSQLのテーブルサイズの調べ方

こんにちは。最近、体型維持の目的で筋トレを始めた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テーブルです。

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;

TOASTテーブルのoid取得結果

②対象テーブル中の行の長さを取得する

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

TOASTテーブル（再掲）

おわりに

今回は業務でPostgreSQLのテーブルサイズを調査したことをきっかけに、どのような構成でTOASTが成り立っているのかを更に調べました。1行に大規模なデータが格納されている場合に、どのような仕様で実際どのような形で格納されているのかを確認することができました。

1つの物事に焦点を当て、深掘りする楽しさを経験することができました。

参考

www.bishounen.sakura.ne.jp

kaigai.hatenablog.com

detail.chiebukuro.yahoo.co.jp

---

• エンジニア中途採用サイト
  ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！
  ご興味ありましたら是非ご確認をお願いします。
  
  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