PostgreSQL テーブルサイズ ディスク容量 見積もり方法
参考サイト
http://www2b.biglobe.ne.jp/~caco/webdb-pdfs/vol24_214-221.pdf
Version
PostgreSQL 8.0 を対象に説明する。
データベース
テーブルやインデックスの本体はデータベース配下の「base」というディレクトリに 格納されるのは常識ですね。 base以下の数字のディレクトリ1つ1つが、1つの「データベース」に対応していて、 どのデータベースがどのディレクトリに対応しているかはpg_databaseでわかりますね。 # SELECT oid, datname from pg_database ;
ヒープファイルとブロック
データベースに所属するテーブルは、テーブルスペースを指定しない限り、データベースと
同じテーブルスペースに作成される。
テーブルの実体は、「ブロック」という固定長（デフォルトでは8192バイト）の領域が
0個以上131072個以下集まったもの。
テーブルを作った直後は対応するファイル（「ヒープ（heap）ファイル」と呼びます）
の大きさは0です。
データを追加していくにつれてヒープファイルは大きくなります。ブロックが131072個、
すなわちヒープファイルのサイズが1Gバイトを超えると、新しいヒープファイルが作られます。
こうして、1つの論理的なテーブルは32Tバイト（=32×1024Gバイト）までの大きさを取ること
ができます。
1つの論理的なテーブル
｜
└ 複数のヒープファイル 32Tバイト（=32×1024Gバイト）
｜
└ 複数のブロック 0個以上131072以下（=1Gバイト）
ページとタプル
ブロックの中には「ページ」があります。PostgreSQL（postgres）の歴史上ある時点では
1ブロック中に複数のページを納める計画もあったようですが、現在では1ブロックの中に
入るページ数が1以外になることはないので、実際にはブロック＝ページになっています。
ただし、ブロックとページの使い分けが一応あり、ブロックは物理アクセスの単位であり、
ブロック内の情報の構造を問題にするときはページという言葉を使います。
ページの中には行が0個以上入ります。
ページの先頭には24バイトの管理領域があり、「ページヘッダ」と呼ばれています。
※ PostgreSQL7.4 では、20バイトです。
ページヘッダの中にはトランザクションログの管理データのほか、このページの中の
空き領域を管理する情報が入っています。
●ページの構造
・ページヘッダ(24バイト)
・アイテムポイントＡ ⇒ タプルＡ先頭/末尾 を記憶
・アイテムポイントＢ ⇒ タプルＢ先頭/末尾 を記録
↓
↓ ※アイテムポイントの成長する方向
フリースペース
↑
↑ ※タプルの成長する方向
・タプルＡ
・タプルＢ
・スペシャルスペース(通常0バイト)
●ページヘッダの構造(計算上では20バイトですが･････24バイト)
フィールド名 バイト長 用途
pd_lsn 8 LSN（Log Sequence Number）
pd_tli 4 タイムラインID
pd_lower 2 ページ内の空き領域の開始位置
pd_upper 2 ページ内の空き領域の終了位置
pd_special 2 ページ内のスペシャルスペースの終了位置
pd_pagesize_version 2 ページサイズとバージョンに関する情報
LSNは、PostgreSQLがトランザクションログを使ってデータベースをリカバリするときに使用します。
トランザクションログには「更新を行った」とか「新しいレコードを追加した」というような情報
が入っているので、これをデータベースに適用することによってデータをリカバリすることができ
ます。このときにLSNを調べることにより、誤って同じログを2回適用してしまうことを防ぐことが
できます。
「タイムライン（TimeLine）ID」もトランザクションログによるリカバリに関係しています。
PostgreSQLは、トラザンクションログを使ってリカバリをかける際に、直近ではなくて任意の時点
まで戻ることができ、このときにタイムラインIDが生成されます。タイムラインIDを使うことにより、
一旦リカバリをかけた後に再度別の時点を選んでリカバリすることができます。これは、リカバリを
かけて戻るべき時点がはっきりしておらず、何度もリカバリをかけてデータ内容を確認しなければな
らないときなどに有効です。
ページヘッダの後には「アイテムデータ」が続きます。
アイテムデータは可変長の領域で、1項目につき4バイトの大きさがあり、ページ内のタプルの位置を
管理しています。
タプルはページの後方から追加されていきます。タプルが1個追加されるたびにアイテムデータの1項目
がページヘッダの後ろに追加されます。
つまり、ページの中央の空き領域を、前からはアイテムデータ、後ろからはタプルが攻めていくような
イメージでページの空き領域が埋まっていくわけです。ページの最後には「スペシャルスペース」があ
る場合もありますが、通常のテーブルではこのサイズは0であり、使用されていません。
タプルの構造
タプルの先頭には管理領域である「タプルヘッダ」があります。
タプルヘッダは普通のPCでは27バイトの大きさです。
※ PostgreSQL7.4 では、23バイトです。
●タプルヘッダの構造
フィールド名 バイト長 用途
t_xmin 4 行を挿入したトランザクションID
t_cmin 4 行を挿入したコマンドID
t_xmax 4 行を削除したトランザクションID
t_cmax/t_xvac 4 行を削除したコマンドIDまたはVACUUMによって
移動された行のバージョン
t_ctid 6 この行あるいは新しい行のTID（タプルID）
t_natts 2 列の数
t_infomask 2 フラグビット
t_hoff 1 行データへのオフセット
タプルヘッダの後ろには「ヌルビットマップ」があり、NULL データを含むタプルにおいて、
ある列がNULLかどうかを記録します。
⇒ NULLを含まない行ではヌルビットマップは存在しません。
⇒ NULLを許す列が1つでも含まれる場合は、列の数分のビットを持つヌルビットマップが存在します。
そのサイズは、（列の数＋7）／8バイトになります。もしこのバイト数が4の倍数にならない場合は、
4の倍数に切り上げるためにパディングが行われます。
その後には4バイトのOID（オブジェクトID）があります。
「WITHOUT OID」を指定して作成されたテーブルでは、OIDは存在しません。
計算
テーブルサイズの計算の簡単な例
条件
●ブロックのサイズはデフォルトの8192バイト ●サンプルテーブル CREATE TABLE t1( i INTEGER NOT NULL, j INTEGER NOT NULL );
1. フリースペースの計算
ページの先頭には24バイトのページヘッダがあるので、 8192 － 24 = 8168バイト が使用可能である。
2. 格納タプル数の計算
タプルあたりの大きさ = タプルヘッダ（27バイト）
+ 1バイトのパディング
+ OID（4バイト）
+ i列の大きさ（4バイト）
+ j列の大きさ（4バイト）
※ ここで1 バイトのパディングが入っているのは、OID（OIDがない場合は列の先頭）が
4バイトバウンダリになることが要求されるからです。
このバウンダリ要求はアーキテクチャによって異なりますが、普通のPCでは4です。
以上から、1ページに格納できるタプル数は、
8168／（40＋4）＝185.63
を切り下げて185個になります。
※ 4バイトは、アイテムデータのバイト数。
3. 必要ページ数の計算
したがってt1に4096行のデータを挿入したとすると、 4096／185＝22.14 から、23ページ＝23ブロックのテーブル領域が必要であることがわかります。 これはディスク容量で言うと、23×8192＝188416バイト＝184Kバイトが必要であることを示します。
4. ディスク容量が既に決まっている場合
たとえば、1Mバイトのヒープファイルに何行格納できるか計算してみましょう。 まず1Mバイト＝1024×1024＝1048576バイトですから、ブロック数で言うと 1048576／8192＝128になります。 各ブロック＝ページには185行入りますから、トータルでは128×185＝23680行格納できることが わかりました。
データ型
●データ型の記憶容量
型 記憶容量（バイト）
SMALLINT（INT2） 2
INTEGER（INT4） 4
BIGINT（INT8） 8
SERIAL（SERIAL4） 4
BIGSERIAL（SERIAL8） 4
REAL 4
DOUBLE PRECISION 8
FLOAT 8
FLOAT（1-24） 4
FLOAT（25-53） 8
TIMESTAMP 8
TIMESTAMP WITHOUT TIME ZONE 8
TIMESTAMP WITH TIME ZONE 8
INTERVAL 12
DATE 4
TIME 8
TIMEP WITHOUT TIME ZONE 8
TIME WITH TIME ZONE 12
BOOLEAN 1
XID 4
OID 4
●可変長データ型の場合の計算方法
文字列型、NUMERIC、配列の場合は3つの理由に
より容量計算がかなり難しくなります．
① ユーザが入力するデータ長によって容量が変わる
② データによっては自動圧縮が行われる
③ 文字コードによっては、文字数とバイト数の関係が
文字種によって変わる
①③については平均データ長を決めて計算すれば対
応できますが、②はプログラムの挙動がわからないと
計算できません．
●TEXT 型の場合
まず、TEXT型について調べてみましょう．ご存じ
の方も多いと思いますが、TEXT型はPostgreSQL固
有のデータ型で、無限長（実際には圧縮後で1Gバイ
ト）までの文字列を格納できます．文字数の制限がな
いのでPostgreSQLのユーザには広く使われています．
TEXT型の構造は、文字列の長さを格納する4バイ
トのヘッダとその後に続く実際の文字データになって
います．したがって、圧縮を考えなければ「4＋文字
列バイト長」がTEXT型のバイト長です．
文字種とバイト長
ここで「漢字」と言っているのは、いわゆる全角文 字のことを意味します．ですから「あ」や「α」など もこの分類に入ります．使いたい文字がどの分類にな るかよくわからない場合は、octet_lengthを使ってバ イト長を確認できます（図4） ●文字種●文字コード●1文字あたりのバイト長 ASCII EUC-JP 1 1バイトカタカナEUC-JP 2 JIS X 0208漢字EUC-JP 2 JIS X 0212漢字EUC-JP 3 ASCII UTF-8 1 1バイトカタカナUTF-8 3 JIS X 0208漢字UTF-8 3 JIS X 0212漢字UTF-8 3
TOAST と圧縮を考慮した場合
このままでは8192バイトを超えるような大きなデ ータは格納できません．そこでPostgreSQL では、 TOASTテーブルという別のテーブルにはみ出た部分を 格納することによって大きなデータを格納できる
TOAST テーブルの構造
TOASTテーブルにはデータがBYTEA型に変換され
た後「チャンク（chunk）」と呼ばれる単位に分割さ
れて格納されます．1つのチャンクの大きさは最大で
も通常1994バイトを超えない大きさです．TOASTテ
ーブルは表5 のような構造を持っています．ここで
chunk_idは、ある行のある列データを識別するため
の固有のIDです．chunk_seqは複数のチャンクに分
割したデータに付けられた一連の番号です．
chunk_dataがチャンク本体です．TOASTテーブルに
はインデックスもあり、chunk_idとchunk_seqの複
合ユニークインデックスが作られます．
●列名 ●データ型
chunk_id OID
chunk_seq INTEGER
chunk_data BYTEA
● TOAST テーブルの中身を調べる
実際にデータを格納して確認してみましょう．文字
コードはEUC-JPを使用するものとします．格納する
テーブルはリスト2のものを使用します．
▼リスト2 サンプルテーブル（2）
CREATE TABLE t2(
t TEXT NOT NULL
);
test=# insert into tb_test values (1111,'4444');
INSERT 0 1
test=# select length(id), length(name) from tb_test ;
length | length
--------+--------
4 | 4
テーブルに可変長の項目が含まれていると自動的に
TOASTテーブルとインデックスが作成されます．TOAST
テーブルは「pg_toast_テーブルのrelfilenode注5」、イ
ンデックスは「pg_toast_テーブルのrelfilenode_index」
という命名規則があるので、以下に解説するようにし
て名前と大きさ（ブロック数）が検索できます．
検索前に一度VACUUMを実行して、ブロック数に
関する情報を更新しておくのがポイントです（図6）．
pg_toast_17288がTOASTテーブルで、pg_toast_17288
_indexがインデックスです．それぞれ、1ブロック、2
ブロックの大きさがあります．
注5）relfilenodeはテーブルに割り当てられた固有のIDで、システムカタログのpg_classで管理されます．
●toast
SELECT c2.relname, c2.relfilenode, c2.relpages
FROM pg_class c,pg_class c2
WHERE (c2.relname = ('pg_toast_' || c.relfilenode) OR
c2.relname = ('pg_toast_' || c.relfilenode || '_index')) AND
c.relname like '%t2%' ;
relname | relfilenode | relpages
----------------------+-------------+----------
pg_toast_16723_index | 16727 | 1
pg_toast_16723 | 16725 | 0
pg_toast_16718_index | 16722 | 1
pg_toast_16718 | 16720 | 0
※テーブルを作成して時点で作成される。
●table
select relname,relfilenode,relpages
from pg_class
where relname like '%t2%' ;
relname | relfilenode | relpages
---------+-------------+----------
t2_v2 | 16723 | 0
t2 | 16718 | 1
※作成例
ls -l /psgdata/base/16643/1672*
31 bsaa49033:/psgdata/base/16643 root# ls -l /psgdata/base/16643/1672*
-rw------- 1 psgadm dba 0 May 21 10:59 /psgdata/base/16643/16720
-rw------- 1 psgadm dba 8192 May 21 10:59 /psgdata/base/16643/16722
32 bsaa49033:/psgdata/base/16643 root# ls -l /psgdata/base/16643/1671*
-rw------- 1 psgadm dba 8192 May 21 11:21 /psgdata/base/16643/16718
33 bsaa49033:/psgdata/base/16643 root# ls -l /psgdata/base/16643/16723
-rw------- 1 psgadm dba 0 May 21 11:46 /psgdata/base/16643/16723
34 bsaa49033:/psgdata/base/16643 root# ls -l /psgdata/base/16643/16725
-rw------- 1 psgadm dba 0 May 21 11:46 /psgdata/base/16643/16725
35 bsaa49033:/psgdata/base/16643 root# ls -l /psgdata/base/16643/16727
-rw------- 1 psgadm dba 8192 May 21 11:46 /psgdata/base/16643/16727
それではいよいよTOASTテーブルの中身を調べて
みましょう．TOASTテーブルはpg_toastというTOAST
専用のスキーマに所属しており、またpg_toastはスキ
ーマサーチパスに含まれていないので、検索するとき
はスキーマ名をテーブル名の前に付けておきます注6
（図7）．一連のチャンクには17294というIDが付けら
れ、0から2までのシーケンス番号を持つ3つのチャン
クに分割されています．0と1のチャンクは最大長で
ある1994バイトで、残りのデータがチャンク2に格納
されています．ですから、TOASTデータの全長は5593
バイトということになります．
●TOAST テーブルの中身を検索
SELECT chunk_id, chunk_seq, length(chunk_data)
FROM pg_toast.pg_toast_16718;
chunk_id | chunk_seq | length
----------+-----------+--------
16728 | 0 | 1994
16728 | 1 | 1994
16728 | 2 | 1994
16728 | 3 | 1994
16728 | 4 | 1994
16728 | 5 | 385
(6 rows)
SELECT chunk_id, chunk_seq, length(chunk_data)
FROM pg_toast.pg_toast_16723;
test=# SELECT chunk_id, chunk_seq, length(chunk_data)
test-# FROM pg_toast.pg_toast_16723;
chunk_id | chunk_seq | length
----------+-----------+--------
16729 | 0 | 1994
16729 | 1 | 1994
16729 | 2 | 1994
16729 | 3 | 1994
16729 | 4 | 1994
16729 | 5 | 385
test=# select length(t) from t2_v2;
length
--------
328188
(1 row)
注6）chunk_dataの中身はBYTEAで、しかも圧縮がかかった後のバイナリ値を表現したものですから、
そのまま見ても「\346%\000\000\000\012\305\314\301...」のようになってしまうので、
ここでは長さだけを表示させています．⇒kinn確認済み
●ヒープファイル本体
ヒープファイル本体のほうにはどの程度の大
きさのデータが格納されているのでしょうか？ contrib
に登録されているpgstattupleを使えば確認できます．
$ cd postgresql-8.0.0beta4/contrib/pgstattuple
$ make
$ make install
$ psql -f pgstattuple.sql test
これでインストールは完了です．さっそくpgstattuple
を使ってヒープファイル本体のタプルの長さを調べて
みましょう（図8）．この中で、tuple_lenがテーブルの
中のタプルの長さの合計です．ここではタプルが1個だ
けなので、52というのがそのタプルの長さです．tuple_len
にはタプルヘッダも含まれており、その長さは、
タプルヘッダ（27バイト）
＋1バイトのパディング
＋OID（4バイト）
＝32
バイトです．
ですから、データ本体の大きさは52－
32＝20バイトということになります．20バイトの中
身は表6のようになっています．ご覧のように、すべ
て管理データであり、ユーザデータ本体はTOASTテ
ーブルに格納されていることがわかります．
ここまで調べて、ようやくTEXT型でTOASTと圧
縮がある場合のデータ容量を計算できるようになりま
した．
表6 ◎データ本体の中身
●フィールド名●データ長●意味
va_header 4 フラグ＋データ長※
va_rawsize 4 圧縮前のサイズ
va_extsize 4 TOASTに格納されているサイズ
va_valueid 4 chunk_id
va_toastrelid 4 TOASTテーブルのID
※ va_header の頭1 ビットがオンならばTOAST テーブルを使用しているこ
とを表し、次のビットがオンならば値が圧縮されていることを表します．
したがって、今回のテストデータでは両方のビットがオンになっているは
ずです．残りの30 ビットは全データ長です．30 ビットを使って表現でき
る最大の数字は1G バイトです．このため、PostgreSQL では、データの
最大長は1Gバイトに制限されています．
▼図8 ヒープファイル本体のタプルの長
test=# \x
Expanded display is on.
test=# select * from pgstattuple('t2');
-[ RECORD 1 ]------+------
table_len | 8192
tuple_count | 1
tuple_len | 52
tuple_percent | 0.63
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 8112
free_percent | 99.02
●データ容量の計算
圧縮がある場合は圧縮後の正確なデータ長を正確に
見積もるのが難しいのですが、ここではとりあえずテ
ストデータで圧縮率8％（TOASTデータ長（5593）／
元データ長（6022）＝0.92から計算）として、
リスト2の定義を持つテーブルに10Kバイトの
テキストを4096個格納した場合の容量見積もりを行っ
てみましょう．
!テーブル本体に格納されるタプル長は一律52 バイ
ト．したがって1 ページには8168／（52＋4）＝
145.85から145タプルが格納できる．4096タプル
ならば4096／145＝28.24だから、29ブロック必
要になる．
⇒4バイトは、注4）参照。
!10K バイトのデータを圧縮すると（10 × 1024 ×
0.92＝9420.8）から9421バイトになる．これはチ
ャンクに分割すると9421／1994＝4.72から5つの
チャンクになる．したがって4096個ならば4096×
5＝20480チャンクが必要で、1つのブロックには4
チャンク格納できるので、20480／4＝5120ブロ
ックが必要．
!以上から、テーブル本体とTOAST テーブルで合計
29＋5120＝5149ブロック必要．これは、5149×
8192／1024／1024＝40.22から約41Mバイトに
なる．
元データは4096×10×1024／1024／1024＝40M
バイトですから、ほぼ同じくらいのディスクスペース
が必要であることがわかります．
5593 ⇒ TOASTのlengthの合計
6022 ⇒ テーブルのlength
TOAST と圧縮がない場合
PostgreSQLでは、圧縮をしてデータを格納するか どうかはデータの内容によることになります．すなわ ち、同じ文字が連続しているような場合には圧縮効率 がよくなるので、比較的小さいデータでも圧縮が行わ れます．したがってあらかじめデータ圧縮が行われる かどうか判断するのは難しいのですが、ソースコード 中のコメントによれば、1Kバイト以下のデータの場 合、圧縮が行われる確率は低いとのことです注8． 注8）src/backend/utils/adt/pg_lzcompress.c TOASTも圧縮も行われない場合は、タプル長は、 52＋4＋データ長 となります．たとえば512 バイトの文字列データを 4096個格納すると、8168／（52＋4＋512）＝14.38 ですから、1ブロックには14タプル入ります．したが って、4096タプルでは4096／14＝292.57から293ブ ロック必要になり、これは約2.3Mバイトです．元デ ータは512×4096／1024／1024＝2Mバイトですか ら、PostgreSQLに格納する場合はちょっとだけデー タが余計に必要になっています．
NUMERIC 型の場合
NUMERIC型も可変長データ型ですが、データ本 体のバイト数の計算式は以下のようになります． 8＋精度（スケール）／4×2 ですからリスト3のようなテーブルにおいて、タプ ルの長さは、28（タプルヘッダ）＋4（OID）＋8＋ 128（スケール）／4×2＝104バイトとなります． ▼リスト3 サンプルテーブル（3） CREATE TABLE t4( n NUMERIC(128) NOT NULL );
配列の場合
PostgreSQLでは任意のデータ型注9を配列にするこ とができます．データ量としては、基本的に今まで説 明したデータ型を配列要素数格納したときのデータ量 と考えてよいのですが、配列としての管理領域が加算 されることだけが異なります． 管理領域は全部で16＋4×次元数＋4×次元数バイ トで、内容は表6になります． 例を示します． CREATE TABLE t5( i INTEGER[] ); INSERT INTO t5 VALUES(ARRAY[1,2,3,4,5,6,7,8,9,10]); この場合、1次元配列なので、タプルの長さは28 （タプルヘッダ）＋4（OID）＋24（配列管理領域）＋ 4（INTEGERのデータサイズ）×10＝96バイトにな ります． 以下のような2次元配列を登録した場合は、 INSERT INTO t5 VALUES(ARRAY[[1,2,3,4,5,6,7,8,9,10], [1,2,3,4,5,6,7,8,9,10]]); 28（タプルヘッダ）＋4（OID）＋32（配列管理領 域）＋4（INTEGERのデータサイズ）×20＝144バ イトになります． 表6 ◎管理領域の内容 ●フィールド名●バイト長●用途 size 4 この配列データのバイト長 ndim 4 次元数 flag 4 未使用 elemtype 4 データ型OID dim 4×次元数各次元のサイズ dim_lower 4×次元数次元の下限値
ご訪問頂き有難う御座います。
当サイトを効率良く使うためにまずは FrontPage を見て下さい。
検索方法、一覧表示などの各情報を纏めています。
当サイトの説明 → Frontpage