データモデル設計ガイド

主キー設計とクラスタリング — InnoDB と PostgreSQL の違い

InnoDB は PK がテーブル本体、PostgreSQL は heap + 独立 PK。違いを踏まえた PK 形状・列順・複合 PK の設計。

InnoDB の clustered index という特殊事情

MySQL (InnoDB) では 主キーそのものがテーブルの物理データ構造です。PK の B+Tree のリーフノードに行本体が格納される「clustered index」構造なので、行は PK 順にディスク上に並びます。

この設計の帰結は 2 つあります。

PK 順に INSERT するとディスク書き込みが append-only になり高速。逆に PK がランダムだと挿入位置が散らばり、ページ分割・I/O 増加・索引断片化が起きる
全セカンダリインデックスは「値 → PK」の対応表として作られる。つまり PK が 36 バイトの UUID なら、各セカンダリ索引のエントリにも 36 バイトの PK が含まれる

PostgreSQL は heap（無秩序な行の置き場）+ 独立した PK 用 B-Tree という構成なので、この特殊事情はありません。セカンダリインデックスのリーフには PK ではなく ctid（物理位置）が入ります。

挿入順と PK — なぜ UUID v4 が遅いのか

InnoDB での BIGINT AUTO_INCREMENT は、PK 順 = 時間順なので常にテーブル末尾に追加されます。最後のページだけが hot で、ディスク I/O は少数のページに集中し、キャッシュ効率も最高です。

これが UUID v4（完全ランダム）に変わると、挿入のたびに B+Tree の中央・先頭など全領域がランダムに hot 化します。ページ分割が頻発し、バッファプールから溢れたページを読み直す I/O が増えます。書き込みスループットが数分の 1 に落ちるのが典型です。

対策は 2 つ。

UUID v7（時刻順序性のある UUID）を採用。先頭 48 bit がミリ秒タイムスタンプなので、ほぼ append-only の挿入パターンになる
BIGINT AUTO_INCREMENT を主キーに据え、UUID は別列にする。URL 露出や外部連携では UUID を使い、内部参照は BIGINT で効率を確保

詳細な実測データと v1 / v4 / v7 の違いは主キーの選び方（UUID v4 / v7 / BIGINT）にまとめています。

複合 PK がセカンダリ index を膨らませる

InnoDB のセカンダリインデックスが「値 → PK」のペアを保持する性質から、PK が太いほど全セカンダリインデックスが太ります。複合 PK の安易な採用は、予想外のコストになりがちです。

例: (tenant_id BIGINT, id BIGINT) を複合 PK にすると、セカンダリインデックスの 1 エントリ当たり 16 バイト（+ 値）になります。id 単独 PK なら 8 バイト。インデックス本数 × 行数 × 8 バイトの差が積み上がります。

通常の業務テーブル: 単一代理キー（id 1 列）を PK に。tenant_id は普通の列 + UNIQUE / INDEX で対応
中間テーブル（N:M の交差表）: (a_id, b_id) の複合 PK は OK。ここでは PK がそのまま検索用インデックスとして機能するので、他に索引を足す必要が少ない
サブテーブル（親の ID + 連番）: 親テーブルの ID を先頭にした複合 PK にするかは、セカンダリ索引の本数と相談

PostgreSQL では heap + ctid なのでこの問題は発生しませんが、複合 PK のそもそもの可読性・扱いにくさは共通です。

sql

-- ❌ InnoDB で全セカンダリ索引が太る
CREATE TABLE orders (
  tenant_id BIGINT NOT NULL,
  id        BIGINT NOT NULL AUTO_INCREMENT,
  user_id   BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, id),
  INDEX idx_user (user_id)   -- ← 1 エントリに (user_id, tenant_id, id) が入る
);

-- ✅ 代理キー単独 PK + tenant_id は普通の列
CREATE TABLE orders (
  id        BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  user_id   BIGINT NOT NULL,
  INDEX idx_tenant_user (tenant_id, user_id)
);

PostgreSQL の場合 — heap + 独立 PK index

PostgreSQL ではテーブル本体は heap（無秩序な行配置）で、PK は普通の UNIQUE B-Tree として独立に存在します。InnoDB のような「PK 順に並ぶ」性質はなく、挿入は空きスペースのあるページに入ります。

このため、PostgreSQL では:

UUID v4 のペナルティは InnoDB より小さい（とはいえゼロではない。PK 索引の断片化は発生する）
複合 PK でもセカンダリ索引が膨らまない（セカンダリは ctid を指すだけ）
クラスタリングしたければ CLUSTER コマンドで物理並べ替えを実行（一度きり、新規行には適用されない）

ただし PostgreSQL でも、時系列列でよくアクセスする場合は BRIN インデックスや パーティショニングで物理順序を整えると効果があります（インデックス種別の選び方）。

設計時のチェックポイント

主キー設計で最初に確認する 5 項目。

InnoDB か PostgreSQL か — PK の重みが根本から違う
PK は時間順に増えるか — BIGINT AUTO_INCREMENT / UUID v7 など。ランダムだとフラグメント
PK の幅は 16 バイト以内か — InnoDB では全セカンダリ索引に影響
分散発行が要るか — 単一 DB なら BIGINT、マルチ DB / オフライン発行なら UUID v7
URL / 外部連携で露出するか — BIGINT は連番で件数が漏れる。露出する場合は UUID v7 を検討

判断材料が揃わないうちは、BIGINT AUTO_INCREMENT（or BIGSERIAL） をデフォルトにしておくのが最も安全です。迷ったらコレ。