RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

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

こんにちは。最近、体型維持の目的で筋トレを始めたbadaikiです。

先日、業務でPostgreSQLのテーブルサイズを調査することがあり、PostgreSQLの仕様の理解が不足していると実感しました。今回はそのことについて備忘録的に書いていこうと思います。

はじめに

冒頭にも記載しましたが、業務でPostgreSQLのテーブルサイズを調査する機会がありました。テーブルサイズを調査する上でPostgreSQLの仕様について理解したことや、テーブルサイズの調べ方をまとめていきます。

実は過去に資格受験でこの辺りを学習していたのですが、すっかり内容を忘れておりました。資格学習での知識って実用可能なレベルで理解するのは難しいですね...

PostgreSQLのデータサイズの持ち方

概要

PostgreSQLは固定長のページサイズ(通常8kB)を使用し、複数ページにまたがる行(tuple)を許しません。それによって大規模なフィールド値を直接格納することができません。そこでフィールド値を圧縮したり、複数の物理的な行に分割するTOASTと呼ばれる技法を用いたりして大規模なフィールド値を格納しています。

※なお、TOASTは可変長(varlena)表現を持つデータ型のみサポートしています。

TOASTテーブル

ページサイズを超過し物理的に分割された行はTOASTテーブルに格納されます。TOASTテーブルは通常のテーブルを定義するとそのテーブル専用のTOASTテーブルが作成されます。TOASTテーブル名はpg_toast_{対象テーブルのoid}になります。

f:id:badaiki:20190916024311p:plain:w600
テーブルリスト

TOASTテーブルの構成は以下のようになっています。

名前 説明
chunk_id oid 特定のTOAST化された値を識別するOID
chunk_seq integer 値の塊に対する連番
chunk_data bytea 塊の実際のデータ

そしてchunk_dataの値がTOAST_TUPLE_TARGETバイト(通常1994Byte)より小さくなるかそれ以上の縮小ができなくなるまで、フィールド値の圧縮や行外への移動を繰り返します。

下のレコードは約150kBのフィールド値をもつテーブルのTOASTテーブルです。

f:id:badaiki:20190916045334p:plain:w600
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;

f:id:badaiki:20190916052130p:plain:w350
対象テーブル情報取得結果
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;

f:id:badaiki:20190916060324p:plain:w400
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

f:id:badaiki:20190916045334p:plain:w600
TOASTテーブル(再掲)

おわりに

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

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

参考

www.bishounen.sakura.ne.jp

kaigai.hatenablog.com

detail.chiebukuro.yahoo.co.jp

Copyright © RAKUS Co., Ltd. All rights reserved.