テーブル設計

論理データモデルから、実際のテーブル設計を行います。論理データモデルはE-R図で作成しましたが、そのE-R図を物理設計に落とし込むこともあります。今回はテーブル設計に限定します。

論理データモデルでは日本語で設計しましたが、物理設計では半角英数字を使用します。また、意味のある単語を使用し、複数の単語がある場合はアンダースコア（_）でつなぐスネークケースを使うのが一般的です。例えば、「quiz_type」などがその例です。今回はテーブル設計に限定します。

カラム名もテーブル名と同様に、意味が伝わるような名前にすることが望ましいです。必ずそうしなければならないわけではありませんが、一般的にはそのように命名されています。

物理設計では、実際に実装可能な内容にするため、データベースで使用できるデータ型、一意制約、NULL制約、インデックスなどを定義します。また、レコードの更新が頻繁に行われるかどうかも考慮する必要があります。

表領域

作成したテーブルは1つの表領域に格納され、その表領域には複数のページが含まれます。
各ページには複数のレコードが保存されますが、1つのレコードが複数のページにまたがることはありません。

1ページ（ブロック）に格納できるデータ量には限りがあり、データを更新（UPDATE）する際に空き領域がない場合は、新しいページが作成され、そこにレコードが書き込まれます。このとき、ポインタで新しいページが参照されます。

レコードを追加する際には、全てのページを埋めずに空き領域を残すことができます。
これをFILLFACTOR（フィルファクター）と呼びます。PostgreSQLのテーブルのデフォルトのFILLFACTORは100%で、インデックスは90%です。

空き領域を多めに確保することで、更新時に同じページ内でデータを処理できるため、性能が向上します。
インデックスで指定した項目が順序通りに同じページに格納されており、連続してレコードを読み取れる状態を「クラスタ率が高い」といいます。
逆に、インデックスで指定した項目が順序通りに格納されておらず、参照する際に多くのページを参照しなければならない状態を「クラスタ率が低い」といいます。
空き領域を増やすとデータサイズが大きくなりますので、更新頻度に応じて適切な設定を行うことが重要です。

ドメイン

ドメインとは、テーブルの列（カラム）に格納できるデータの範囲や型を定義する集合のことを指します。
具体的には、ある列が持つべき値の許容される範囲や制約を決めるものです。ドメインはデータの一貫性と整合性を保つために重要な役割を果たします。

PostgreSQLでは、独自のドメインを作成することができます。
独自ドメインを使用すると、共通の制約を1箇所にまとめることができるため、同じCHECK制約が複数の場所で必要な場合に、独自ドメインを作成するのは有効です。

テーブル設計

論理データモデルから実際のテーブルを設計していきます。
論理データモデルはE-R図で作成しましたが、そのE-R図を物理設計しなおすケースもありますが、今回はテーブルのみとします。

論理データモデルでは、日本語で設計していきましたが物理設計では半角英数字にします。

また、意味のある単語にして複数の単語がある場合は、アンダーバーでつなげるスネークケースを利用するのが一般です。

例として、「quiz_type」などと記載します。
カラム名もテーブル名と同じようにして、その単語からイメージが分かるようにすることが好ましいです。

そうしなくてはいけないわけではありませんが、一般的にそのように記載されています。

物理設計では、実装できる内容にしていきますので、そのデータベースで使用できる型や一意制約・NULL制約・Indexなど記載します。

また、レコードの更新が頻繁に行われるかも検討する必要があります。
1つのページ(ブロック)に入れれるデータには限りがあり、データの更新(UPDATE)でも空きがない場合には新しいページが生成されそちらにデータが書き込まれてポインターで参照するようになります。

そのため、レコードを追加したときに1つのページを全て使用しないでその割合のみを使用するのがFILLFACTER(空き領域)です。テーブルはデフォルトが100でインデックスはデフォルトが90です。

空き領域を多く確保すれば、更新時にそのページ内に収めることができますので性能が向上します。

しかし、その分の空き領域は確保されてしまいますのでデータ量が増加します。

更新の頻繁と合わせて確認することが大切です。

物理設計のサンプルはこちらです。

データ容量の計算

物理データベース設計の基になるものとしてディスク容量です。
そのため、何年間でどのテーブルに何レコードはいり、1レコードのデータ容量はどれぐらいなのか計算をする必要があります。
また、データ容量としては、データの量だけでなく、DBMSが出力するログやインデックスなどの容量も確認しなくてはいけません。
特にログは設定によって変わりますが、アクセス数が増加すればかなりディスク容量を圧迫する可能性があります。
そのため、logrotateの設定を行い、ログローテーションが必要になります。
インデックスとは、大量のデータがある場合に辞書などの索引と同じようにキーを付与して検索の高速化を図れますが、そのキーを付与した分データ容量が増加します。(レコード数に応じて増加)
データはファイルに記載されていき、ブロック毎でそのブロックに何行入るかで計算できます。

PostgreSQLのデータ型には数値データ型、通貨型、文字型、バイナリ列データ型、日付/時刻データ型、論理値データ型、 列挙型、幾何データ型、ネットワークアドレス型、ビット列データ型、テキスト検索に関する型、UUID型、XML型、JSONデータ型、 配列、複合型、範囲型、オブジェクト識別子データ型、疑似データ型があります。
全てのデータ型は説明できないため当方で主要だと思うものを次に記載します。

【数値データ型】
型名 格納サイズ 説明
smallint 2バイト 狭範囲の整数
integer 4バイト 典型的に使用する整数
decimal 可変長 ユーザ指定精度、正確
bigint 8バイト 広範囲整数
numeric 4バイト+可変長(TOAST圧縮有) ユーザ指定精度、正確
real 4バイト 可変精度、不正確
double precision 8バイト 可変精度、不正確
smallserial 2バイト 狭範囲自動整数
serial 4バイト 自動増分整数
bigserial 8バイト 広範囲自動増分整数

【通貨型】
型名 格納サイズ 説明
money 8バイト 貨幣金額

【バイナリ列データ型】
型名 格納サイズ 説明
bytea 1または4バイトと実際のバイナリ列の長さ 可変長のバイナリ列

【文字型】
型名 格納サイズ 説明
varying(n)4バイト+上限付き可変長
character4バイト+上限付き可変長
varchar(n) 4バイト+上限付き可変長(TOAST圧縮有)
character(n)4バイト+空白で埋められた固定長
char(n) 空白で埋められた固定長(TOAST圧縮有)
text 4バイト+制限なし可変長(TOAST圧縮有)

【日付/時刻データ型】
型名 格納サイズ 説明 最遠の過去 最遠の未来 精度
timestamp 8バイト 日付と時刻両方、時間帯付き
date 4バイト 日付（時刻なし）
time 8バイト 時刻（日付なし）
interval 12バイト 時間間隔
PostgreSQLのデータ容量を見積もるにはテーブル・インデックス・WAL・アーカイブログ・ログファイルの用量を見積もる必要があります。
次のクエリで用量を確認することができます。

postgres=# select pg_column_size(any);
→特定の値を格納するのに使用されるバイト数
postgres=# select pg_tablespace_size(oid or name);
→指定されたテーブルスペースで使用されたディスク領域
postgres=# select pg_database_size(oid or name);
→指定されたデータベースで使用されたディスク領域
postgres=# select pg_relation_size(oid or text);
→指定されたテーブルまたはインデックスで使用されたディスク領域
postgres=# select pg_total_relation_size(oid or text)
→指定されたテーブルで使用されるディスク領域(インデックスとtoast含む)
postgres=# select pg_size_pretty(bigint)
→容量単位の形式に変換したバイト数表示

WAL領域を見積もるには次のように計算で見積もれます

1つのWALセグメントのサイズは16MB
16MB * (postgres.confのcheckpoint_segments * 3 + 1)
※大量の更新などが発生すると上記で計算した用量を超える場合があります。

Postgres.confでwal_levelでarchiveでアーカイブログを有効にしている場合、容量を見積もりる必要があります。
しかし、見積もるのは難しいので、実際に更新トランザクション・ベースバックアップを行い、容量を確認する必要があります。
実際の用量は、ベースバックアップの頻度や、WALの用量によりことなります。
1ファイルは16MBで、削除する頻度にも依存します。

Linuxのdf/duコマンドを利用すれば、HDD容量やデータベースクラスタ・ログの用量が確認できます。
ユーザが作成したデータベースはデータベースクラスタ内のbase配下のディレクトリに管理されます。
OIDと呼ばれる一意のIDがディレクトリ名になります。
1テーブル・1インデックスにつき1つのファイルが割りあたえられます。
filenodeと呼ばれる一意のIDがファイル名となります。
ファイルのサイズが1GBを超えた場合、次のファイルは「.1」次のファイルは「.2」‥のようにファイル名に追記されてファイルが分割されます。
更新が多いなどの場合は、テーブル作成時ににFILLFACTOR(10から100までの割合)を設定して空き領域を確保する必要があります。

テーブル容量見積もりで記述しますが、ブロックやページにはヘッダがありますので、詳細を次に記載します。

[ページヘッダ]
フィールド 型 バイト長 説明
pd_lsn PageXLogRecPtr 8 バイト いわゆるLSN(Log Sequence Number)を記録するフィールド。
このページに対する更新内容を XLogInsert(rmid, info, rdata) を使い WAL レコードとして書き出した後に、PageSetLSN(page, lsn) を呼び出して記録する。
このフィールドはチェックポインティング処理で参照される。
pd_checksum uint16 2 バイト このページのチェックサムを記録する。
pd_flags uint16 2 バイト フラグビット。
pd_lower LocationIndex 2 バイト 空き領域の始まりに対するオフセット。
pd_upper LocationIndex 2 バイト 空き領域の終わりに対するオフセット。
pd_special LocationIndex 2 バイト 特別な空間の始まりに対するオフセット。
pd_pagesize_version uint16 2 バイト ページサイズおよびレイアウトのバージョン番号の情報。
pd_prune_xid TransactionId 4 バイト ページ上でもっとも古い切り詰められていないXMAX。存在しなければゼロ。
このフィールドは VACUUM に寄らないページ単位の削除タプルの回収のために heap_page_prune() を呼び出すタイミングを測るために記録される。
pd_prune_xid は、ページ内のタプルを DELETE した場合、ページ内に存在するタプルを UPDATE した場合、ページ内に INSERT したもののアボートされたタプルを発見した場合などに、その操作のトランザクション ID をPageSetPrunable(page, xid) で使って書き込まれる。
※ただし PageSetPrunable(page, xid) は引数として xid がすでに記録されていた pd_prune_xid よりも古い場合に上書きする。
ページ単位の削除タプルの回収はヒープスキャン(heap_getnext())などで、heap_page_prune_opt() が呼ばれると、pd_prune_xid が有効値に設定されており、かつページ内の空き領域が fill factor 指定よりも少なくなっている場合に heap_page_prune() が呼ばれて開始される。 回収後は pd_prune_xid は 0 に戻される。

[タプルヘッダ]
t_xmin TransactionId 4バイト 挿入XIDスタンプ
t_cmin CommandId 4バイト 挿入CIDスタンプ
t_xmax TransactionId 4バイト 削除XIDスタンプ
t_cmax CommandId 4バイト 削除CIDスタンプ（t_xvacと共有）
t_xvac TransactionId 4バイト 行バージョンを移すVACUUM操作用XID
t_ctid ItemPointerData 6バイト この行または最新バージョンの行の現在のTID
t_natts int16 2バイト 属性の数
t_infomask uint16 2バイト 様々なフラグビット
t_hoff uint8 1バイト ユーザデータに対するオフセット

[テーブル]
ファイルのサイズが1GB
ページ単位は8192byte
メンテナンス処理で可視状態管理のVisibilityMap「0000_VM」ファイルが追加
メンテナンス処理で空き領域管理のFreeSpaceMap「0000_fsm」ファイルが追加

[インデックス]
ファイルのサイズが1GB
ページ単位は8192byte
先頭の1ページはメターページ固定
基本はB-Tree構造

[テーブル空間]
create tablespace文でbase以外のディレクトリにデータを保存
定義したテーブル空間にはOIDが割り当てられて、シンボリックリンクとして配置

[TOAST]
大きなデータ（通常は2kb以上）を通常のテーブルのブロックではなく、専用の外部テーブルに格納する方式を取ります。

データ保存イメージ 行サイズは、Integerは4バイト、Timestampは8バイト、Varchar(n)は4バイト+データ長などで足し算をしていくことで計算できます。
インデックスもテーブルに近い計算が行えますが、ブロックヘッダの他に特別領域が必要(16バイト)+ヘッダ(24バイト)が必要で、1ノードには管理ヘッダ12バイト(B-tree)も必要です。
30%程度の空き領域が発生します。
インデックスイメージ 問題ヘッダーのテーブルで容量を計算していきます。

[条件]

  1. ・問題番号:int(Index)
  2. ・問題区分:int
  3. ・問題表示分類:int
  4. ・見積行数：10000
  5. ・テーブル空き領域(FILLFACTER)：100
  6. ・インデックス空き領域(FILLFACTER)：70

    ※全列NULLなし、テーブルにOIDは含まない


[1行の計算]
28(行ヘッダ)+ 4(問題番号) + 4(問題区分) + 4(問題表示分類) = 40
[ページサイズ(1ブロック)の行数]
(8192(ページサイズ) – 24(ページヘッダ)) * (100(空き領域))/100) / 40 = 204行(端数は切下)
[必要ページ数(ブロック)]
10000(見積行数)/ 204(行数) = 50(端数は切上)
[データ容量]
50(必要ページ数) × 8192(ページサイズ) / 1024(KB計算)= 400KB

テーブルのイメージは以下です。
テーブルイメージ

[インデックス 1ノードの計算]
12(管理ヘッダ) + 4(問題番号) = 16バイト
[インデックス1ページ(ブロック)のノード数]
(8192(ページサイズ) – 40(ページヘッダ)) / 16 = 509ノード
509 × (70(空き領域))/100) = 357ノード(端数は切上)
[インデックス必要ブロック数]
10000(見積行数)/ 357 = 29ブロック
[インデックス容量]
29ブロック × 8192(ページサイズ) / 1024(KB計算)= 232KB

問題ヘッダーのテーブル 容量:632KB = 400KB(データ容量) + 232KB(インデックス容量)
632KBのデータ容量が発生すると見積もることができます。

← 正規化
物理設計 →