필사 모드: データモデリング完全ガイド: 論理モデルからPostgreSQL物理モデルまで

• はじめに
• 1. 論理モデルを物理モデルに移すとき決まるもの
• 2. キー設計 — 何で行を識別するか
• 3. 型の選択が生む違い
• 4. 制約は文書ではなくコードだ
• 5. jsonbの境界線
• 6. 時間を表現する三つの方式
• 7. 正規化を崩す瞬間とその代償
• 8. 物理配置 — 列の並び順とTOAST
• クイズ: 理解度を確認しましょう
• おわりに
• 参考資料
• 関連記事

はじめに

データモデリングを扱う記事はたいてい正規化から始まり正規化で終わります。このブログのデータベース基礎完全ガイドもその系譜にあり、それ自体として有用です。

この記事は正規化が終わった後から始まります。エンティティと関係を全部描いたからといってスキーマが決まるわけではないからです。識別子を bigint にするか uuid にするか、金額を numeric にするか、時刻を timestamptz にするか timestamp にするか、どのルールをデータベース制約に移しどのルールをアプリケーションに残すか、jsonbはどこまで許すか。これらの決定は論理モデルより長く残り、変更がより困難です。列の型変更はたいていテーブル全体の再作成ですから。

基準エンジンは PostgreSQL 18 であり、型の格納サイズと挙動はすべてPostgreSQL 18のドキュメントで確認しました。他のエンジンでは同じ判断が異なる結論になりえます。

1. 論理モデルを物理モデルに移すとき決まるもの

論理モデルは「何があってどうつながるか」を語ります。物理モデルはそこに四つを加えます。

型 — 各属性がどのデータ型で格納されるか。格納サイズ、演算の正確性、比較規則がここで決まります。

制約 — どのルールをデータベースが強制するか。強制しないと決めたルールはいつか壊れたデータとして現れます。

アクセス経路 — どんなインデックスがあるか。これはクエリパターンから逆算され、スキーマ設計と一緒に決まるべきものです。

変更可能性 — 後で変えられるか。この観点がもっとも頻繁に抜けます。

最後の項目から押さえます。PostgreSQLで列の型変更は、ドキュメントの表現で「通常テーブルとインデックス全体を再作成」し、その間 ACCESS EXCLUSIVE ロックを保持します。例外は USING 句が内容を変えず、既存の型が新しい型に二進互換であるか制約のないドメインである場合だけです。

つまり型の選択は事実上戻しにくい決定です。 一方インデックスはいつでも追加も削除もでき、制約も NOT VALID で段階的に追加できます。この非対称が設計の優先順位を決めてくれます。型に時間を使い、インデックスは後でデータを見て決めてください。

2. キー設計 — 何で行を識別するか

最初の分岐は自然キーと代理キーです。

自然キーはドメインにすでに存在する識別子を使います。法人番号、ISBN、メールアドレスといったものです。結合時に追加照会が不要という長所がありますが、三つの問題を連れてきます。値が変わりうること(人はメールアドレスを変えます)、長さが長いとすべての参照テーブルのインデックスが大きくなること、そしてドメイン規則が変わるとスキーマが崩れることです。

代理キーは意味のない識別子を別途置きます。ほとんどの実務スキーマがこちらを選びます。それでも自然キーがあるなら一意制約で必ず表現してください。 代理キーを使うことが自然キーの一意性を放棄するという意味ではありません。

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 代理キーを使っても自然キーの一意性は制約として残す
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

GENERATED ALWAYS AS IDENTITY はSQL標準の構文で serial より推奨されます。serial は内部的にシーケンスを作って既定値をかけるマクロに近く、所有関係が紛らわしくなりがちです。

二つ目の分岐は整数かUUIDかです。

bigint は8バイトで順次に増加するのでB-treeインデックスでの挿入局所性が良好です。短所は値を推測できること(URLに露出してはいけません)と、分散環境で全域的な一意性を保証しにくいことです。

uuid は16バイトで全域的に一意です。短所はランダムなUUIDの挿入局所性が悪いことです。新しい値がインデックス全体に散らばるのでインデックスページが分割され続け、キャッシュ適中率が下がります。

PostgreSQL 18にはこの問題を狙った関数があります。ドキュメントは uuidv7() について「バージョン7(時間順)のUUIDを生成する。タイムスタンプはミリ秒精度のUNIXタイムスタンプとミリ秒未満のタイムスタンプ、そしてランダム値で計算される」と説明します。つまり UUIDの全域的な一意性と整数の挿入局所性を同時に得られます。

-- PostgreSQL 18: 時間順序を含むUUID
CREATE TABLE events (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id  bigint NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

同じドキュメントのページに gen_random_uuid() と uuidv4() もあります。uuidv7() の利用可否は使用中のバージョンのドキュメントで確認してください。 旧バージョンでは拡張やアプリケーション側での生成が必要です。

3. 型の選択が生む違い

文字列。 PostgreSQLドキュメントのTipは明確です。「この三つの型のあいだに性能差はない。空白で埋められる型を使うときの格納領域の増加と、長さ制約のある列に格納するときに長さを検査するわずかなCPUサイクルを除けばそうである。他のデータベースシステムでは character(n) が性能上の利点を持つ場合があるが、PostgreSQLにはそのような利点はない。実際 character(n) は追加の格納コストのため三つのうちたいていもっとも遅い。ほとんどの状況では text か character varying を使うべきである。」

つまり PostgreSQLで varchar(n) の n は性能ではなく制約です。 長さ制限がドメイン規則なら付け、ただの習慣なら付けないでください。後で伸ばすのは再作成なしにできますが、縮めるのは再作成です。ドキュメントによれば n は10,485,760を超えられず、格納可能な最長の文字列は約1GBです。

格納オーバーヘッドもドキュメントにあります。「短い文字列(126バイトまで)の格納要件は1バイトに実際の文字列を足したもので、character の場合は空白埋めを含みます。より長い文字列は1バイトの代わりに4バイトのオーバーヘッドを持ちます。」

数値。 ドキュメントの勧告は断固としています。numeric は「非常に多くの桁数の数値を格納でき」「金額や正確性が要求される数量の格納に特に推奨」されます。そして浮動小数点については「正確な格納と計算が必要なら(金額など)代わりに numeric 型を使え」と明示します。

同時に代償も書かれています。「numeric の値に対する計算は、整数型や次節で説明する浮動小数点型に比べて非常に遅い。」したがって金額と数量には numeric、統計・科学計算には浮動小数点が基準です。浮動小数点についてのドキュメントの警告二行も覚えておいてください。「不正確とは、ある値が内部形式に正確に変換されず近似値として格納されるという意味である」であり「二つの浮動小数点値を同等比較することが常に期待どおりに動作するとは限らない。」

格納サイズは smallint 2バイト、integer 4バイト、bigint 8バイト、real 4バイト、double precision 8バイトです。

時刻。 これがもっとも頻繁に間違える項目です。ドキュメントによれば timestamp with time zone は内部的にUTCで格納され、入力文字列にタイムゾーンが明示されていればそのオフセットでUTCに変換し、なければ TimeZone パラメータが指すタイムゾーンと仮定して変換します。そして「元々明示された、あるいは仮定されたタイムゾーンは保持されません。」 出力するときは常にUTCから現在の TimeZone に変換して地域時刻として見せます。

両方の型とも格納サイズは8バイトです。timestamptz のほうが大きいわけではありません。 したがって「容量を節約するために timestamp を使う」という根拠は成立しません。

基本規則はこうです。ある瞬間を指す値には timestamptz を使ってください。 注文時刻、ログ時刻、生成時刻がここに属します。timestamp without time zone はタイムゾーンと無関係な壁時計の値(例えば「毎日午前9時に通知」)にだけ使います。ちなみにドキュメントは「SQL標準は単に timestamp と書けば timestamp without time zone と同等であることを要求し、PostgreSQLはその挙動に従う」としているので、何も考えずに timestamp と書くとタイムゾーンなしの型になります。

4. 制約は文書ではなくコードだ

「この値は常に0より大きくなければならない」というルールをアプリケーションだけに置くと、三つの経路で壊れます。バッチスクリプト、運用中の手動SQL、そして新しく追加された別のサービスです。

データベース制約はこの三つの経路をすべて塞ぎます。入れられる制約を整理するとこうなります。

CREATE TABLE order_items (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku         text    NOT NULL,
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  status      text    NOT NULL
              CHECK (status IN ('PENDING', 'SHIPPED', 'CANCELLED')),
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (order_id, sku)
);

いくつかの実務指針です。

外部キーの子側にはインデックスを作ってください。 親行を削除したりキーを更新したりするとき子テーブルを検査する必要がありますが、インデックスがなければ毎回全体スキャンが発生します。PostgreSQLはこのインデックスを自動的に作ってくれません。

列挙型は CHECK と enum 型のどちらかから選びます。 CHECK は値の追加が制約の入れ替えで済んで単純であり、enum 型は型システムに組み込まれますが値の削除が面倒です。値が頻繁に変わるなら参照テーブルと外部キーがもっとも柔軟です。

運用テーブルに制約を追加するときは二段階に分けてください。 ドキュメントが明示するとおり NOT VALID を使えば ADD CONSTRAINT がテーブルをスキャンせず即座にコミットされ、その後の VALIDATE CONSTRAINT は SHARE UPDATE EXCLUSIVE ロックしか取らないので同時更新を妨げません。

ALTER TABLE order_items
  ADD CONSTRAINT chk_qty_positive CHECK (quantity > 0) NOT VALID;
ALTER TABLE order_items VALIDATE CONSTRAINT chk_qty_positive;

5. jsonbの境界線

jsonb は強力で、だから濫用されます。境界線を決める基準を三つ提案します。

基準1 — このフィールドで照会したり整列したりするか。 そうならば列に出してください。jsonbの中の値にもインデックスは張れますが、型検査がなく統計推定が不正確なので実行計画が悪くなりがちです。

基準2 — このフィールドに制約が必要か。 NOT NULL、外部キー、一意制約が必要なら列でなければなりません。jsonbの中にはこうした制約を張れません。

基準3 — スキーマが本当に予測不可能か。 「あとでフィールドが追加されるかもしれないから」は根拠になりません。列の追加は非揮発性の既定値であれば再作成なしに即座に終わるからです。

jsonbが正当な場合は明確です。外部システムが送った元のペイロードを監査目的で保管するとき、ユーザーが定義する任意の属性を入れるとき、スキーマがテナントごとに異なる設定値を入れるときです。

CREATE TABLE webhook_events (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- 照会・結合・制約に使う値は列に昇格させる
  provider     text        NOT NULL,
  event_type   text        NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  -- 残りの元データはそのまま保存
  payload      jsonb       NOT NULL
);

-- 特定のキーで頻繁に照会するなら式インデックスを作る
CREATE INDEX idx_webhook_payload_orderid
  ON webhook_events ((payload ->> 'order_id'));

-- 包含関係の検索が必要ならGIN
CREATE INDEX idx_webhook_payload_gin ON webhook_events USING gin (payload);

GINインデックスは一行が複数の値を持つ資料に最適化された転置インデックスです。jsonbの包含演算子の検索に有用ですが、インデックスが大きく更新コストが高い点を勘案してください。

6. 時間を表現する三つの方式

時間に関する要件は三つに分かれ、それぞれ異なるモデルが適します。

第一に、監査ログ(何がいつあったか)。 別途の履歴テーブルに変更事象を追加するだけにします。更新も削除もしません。

CREATE TABLE order_status_history (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint      NOT NULL REFERENCES orders (id),
  old_status text,
  new_status text        NOT NULL,
  changed_by bigint,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_osh_order_time ON order_status_history (order_id, changed_at DESC);

第二に、有効期間(いつからいつまでこの値が有効だったか)。 価格履歴や契約期間がここに属します。範囲型と排他制約を併用すると期間が重ならないという規則をデータベースが強制します。

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE product_prices (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint  NOT NULL REFERENCES products (id),
  price      numeric(12, 2) NOT NULL CHECK (price >= 0),
  valid      tstzrange NOT NULL,
  -- 同じ商品の有効期間は重なれない
  EXCLUDE USING gist (product_id WITH =, valid WITH &&)
);

排他制約はGiSTインデックスで実装され、整数列を同等比較に一緒に使うには btree_gist 拡張が必要です。この規則をアプリケーションで検査すると同時リクエスト二つが両方通過する穴ができますが、制約はデータベースが原子的に保証します。

第三に、論理削除(消したように見えるが残っている行)。 もっとも一般的でもっとも問題の多いパターンです。すべての照会に WHERE deleted_at IS NULL を付ける必要があり、一つでも漏らせば消したデータが見えます。そして一意制約が壊れます。削除された行のメールアドレスが依然として場所を占めるからです。

部分インデックスが二つ目の問題の正解です。

-- 生きている行どうしでのみ一意
CREATE UNIQUE INDEX uq_users_email_alive
  ON users (lower(email))
  WHERE deleted_at IS NULL;

一つ目の問題はビューや行レベルセキュリティで扱えます。ただし根本的には論理削除が本当に必要か問い直すほうが得策です。 監査が目的なら履歴テーブルが、復旧が目的ならバックアップと時点復旧がより適した道具です。

7. 正規化を崩す瞬間とその代償

正規化は既定値であって宗教ではありません。崩すべきときがあり、そのとき何を代償に払うのかを知ったうえで崩すべきです。

非正規化が正当化される典型的な場合は集計値の物理化です。投稿のコメント数を毎回数える代わりに列に維持するといったものです。代償は明確です。二か所の値がずれうるし、それを防ぐ責任がアプリケーションに移ります。

-- 非正規化すると決めたなら更新経路を一つに固定する
CREATE OR REPLACE FUNCTION bump_comment_count() RETURNS trigger AS $fn$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$fn$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION bump_comment_count();

警告: CREATE TRIGGER は対象テーブルに SHARE ROW EXCLUSIVE ロックを取ります。このロックは ROW EXCLUSIVE(つまりINSERT、UPDATE、DELETE、MERGE)と衝突するので、トリガーを作っている間そのテーブルの書き込みが塞がれます。 読み取りは通ります。トラフィックの少ない時間帯に SET LOCAL lock_timeout をかけて実行してください。

トリガーで維持する方式にはもう一つ代償があります。人気の投稿にコメントが集まると同じ posts の行を全員が更新しようとしてロック競合が生じます。代替は増分を別テーブルに追加するだけにして定期的に合算するか、そもそも正確なリアルタイムの値が必要か問い直すことです。ほとんどのカウンタは5秒遅れても何も起こりません。

非正規化を決めるとき残すべきものは三つです。なぜ崩したのか、整合性を何が保証するのか、そしてずれたときに正す再計算クエリです。三つ目をあらかじめ書いておかないと、事故対応の最中に急ごしらえすることになります。

8. 物理配置 — 列の並び順とTOAST

最後に目に見えにくい層です。

列の並び順とアラインメントのパディング。 PostgreSQLは各列を型のアラインメント要件に合わせて配置するので、順序によって行のサイズにパディングが入り込みます。サイズの大きい固定幅の型から小さい型の順に並べると無駄が減ります。数億行のテーブルではこの差がギガバイト単位になります。ただし可読性を大きく損ねてまで最適化する価値はたいていありません。非常に大きいテーブルでのみ検討してください。

TOAST。 大きな値は行にそのまま入らず別の格納領域に出て圧縮されたり分割されたりします。この挙動の利点は、大きな text や jsonb の列があってもその列を読まないクエリはコストをほとんど払わないことです。逆に SELECT * で習慣的に全部読むとその利点が消えます。幅の広いテーブルでは必要な列だけを列挙することが実質的な性能差を生みます。

パーティションキーはスキーマの一部です。 5節と6節で見た一意制約、排他制約、部分インデックスはすべてパーティションキーと相互作用します。パーティションされたテーブルで一意制約を作るには、ドキュメントの規定どおり制約の列がパーティションキーのすべての列を含まなければなりません。パーティショニングを後で導入する可能性があるならキー設計の段階であらかじめ検討してください。 後で発見するとスキーマを作り直すことになります。

スキーマそのものも設計対象です。 マルチテナントでテナントごとにスキーマを置く方式は分離が良く見えますが、テナントが数千あればカタログが大きくなり、マイグレーションが数千回繰り返され、コネクションプールの search_path 切り替えの問題まで生じます。ほとんどの場合 tenant_id 列と行レベルセキュリティのほうがよく拡張します。

クイズ: 理解度を確認しましょう

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE product_prices
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (product_id WITH =, valid WITH &&);

UPDATE posts p
   SET comment_count = c.cnt
  FROM (SELECT post_id, count(*) AS cnt FROM comments GROUP BY post_id) c
 WHERE p.id = c.post_id AND p.comment_count IS DISTINCT FROM c.cnt;

おわりに

データモデリングでもっとも高くつく失敗は、誤った正規化のレベルではありません。戻しにくい決定を根拠なく下すことです。型変更はテーブルの再作成であり、キー設計の変更はすべての参照テーブルに波及し、パーティションキーは一意制約の形まで変えます。

そこで順序をこう組むことを勧めます。まず型とキーを決定しその根拠を残します。次にドメイン規則のうちデータベースが強制できるものを全部制約に移します。インデックスは実際のクエリパターンが見えたときに追加します。非正規化は測定されたボトルネックがあるときだけ行い、行うときは再計算クエリを一緒にコミットします。

スキーマとクエリを直接実験してみるにはPostgresプレイグラウンドとSQLプレイグラウンドを、設計したスキーマにテストデータを詰めるにはモックデータ生成器を活用してください。

参考資料

• PostgreSQL 18, Character Types: https://www.postgresql.org/docs/18/datatype-character.html (2026-08-15 確認)
• PostgreSQL 18, Numeric Types: https://www.postgresql.org/docs/18/datatype-numeric.html (2026-08-15 確認)
• PostgreSQL 18, Date/Time Types: https://www.postgresql.org/docs/18/datatype-datetime.html (2026-08-15 確認)
• PostgreSQL 18, UUID Functions: https://www.postgresql.org/docs/18/functions-uuid.html (2026-08-15 確認)
• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (2026-08-15 確認)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (2026-08-15 確認)
• PostgreSQL 18, Table Partitioning: https://www.postgresql.org/docs/18/ddl-partitioning.html (2026-08-15 確認)
• PostgreSQL 18, Index Types: https://www.postgresql.org/docs/18/indexes-types.html (2026-08-15 確認)

関連記事

• 前の記事: 大容量データ処理完全ガイド — COPYとチャンクバッチ
• 次の記事: DB性能チューニング完全ガイド — 測定する順序
• データベース基礎完全ガイド — 正規化と基本概念
• PostgreSQLインデックス完全ガイド — スキーマに付けるインデックス設計
• Postgresプレイグラウンド — スキーマと制約の実験
• SQLプレイグラウンド — クエリ構文の実験
• モックデータ生成器 — 設計したスキーマを埋める