postgresql-table-design

PostgreSQL テーブル設計

使用すべき場面

• 新しい PostgreSQL スキーマを設計するとき、またはリリース前にレビューするとき。
• PostgreSQL に特化して、カラム型、キー、制約、インデックスを選定するとき。
• 大きなテーブルをパーティション分割すべきか、どのように行うか、あるいは半構造化データをどのように保存するかを決めるとき。
• 稼働中のデータベースでダウンタイムなしにスキーマ変更を計画するとき。

PostgreSQL スキーマのルールと判断ポイント。データ型の全カタログ、ワークロードパターン（更新重視、挿入重視、upsert、スキーマ進化）、拡張機能、JSONB インデックス、実例付きの DDL サンプルは references/details.md にあります。以下のセクションでそこを参照している箇所があれば開いてください。

基本ルール

• 参照テーブル（users、orders など）には PRIMARY KEY を定義します。時系列/イベント/ログデータには必ずしも必要ありません。使用する場合は BIGINT GENERATED ALWAYS AS IDENTITY を優先し、グローバルな一意性や不透明性が必要な場合にのみ UUID を使用します。
• データの冗長性と更新時の異常を排除するために まず正規化（3NF まで） します。非正規化は、結合性能が問題となることが実測で確認された、高 ROI の読み取りに対してのみ行います。
• 意味的に必要なすべての箇所に NOT NULL を追加し、よく使う値には DEFAULT を使用します。
• 実際にクエリするアクセスパスにインデックスを作成します: PK/unique（自動）、FK カラム（手動！）、頻繁なフィルタ/ソート、結合キー。
• イベント時刻には TIMESTAMPTZ、金額には NUMERIC、文字列には TEXT、整数には BIGINT、浮動小数点数には DOUBLE PRECISION（正確な小数演算には NUMERIC）を優先します。

PostgreSQL の注意点

• 識別子: 引用符なし → 小文字化されます。引用符付き/大文字混在の名前は避け、snake_case を使用します。
• Unique と NULL: UNIQUE は複数の NULL を許可します。NULL を 1 つに制限するには UNIQUE NULLS NOT DISTINCT (...)（PG15+）を使用します。
• FK インデックス: PostgreSQL は FK カラムを自動的にインデックスしません。手動で追加してください。
• 暗黙の変換はなし: 長さ/精度のオーバーフローはエラーになります（切り捨てなし）。NUMERIC(2,0) に 999 を挿入すると失敗します。これは暗黙に切り捨てや丸めを行うデータベースとは異なります。
• シーケンス/identity にはギャップができます（正常であり、「修正」しないでください）。ロールバック、クラッシュ、同時トランザクションによってギャップが生じます（1, 2, 5, 6...）。
• ヒープストレージ: デフォルトではクラスタ化された PK はありません。CLUSTER は一度きりの再編成であり、その後の挿入では維持されません。
• MVCC: 更新/削除はデッドタプルを残し、vacuum がそれらを処理します。ホットなワイド行のチャーンを避けるように設計してください。

データ型

• ID: BIGINT GENERATED ALWAYS AS IDENTITY。分散 ID や不透明な ID には UUID を uuidv7()（PG18+）または gen_random_uuid() で生成します。
• 数値: ストレージが重要でない限り BIGINT。REAL より DOUBLE PRECISION。金額や正確な小数には NUMERIC(p,s)。
• 文字列: TEXT。制限が必要な場合は CHECK (LENGTH(col) <= n) を併用します。バイナリには BYTEA。大文字小文字を区別しない検索: LOWER(col) の式インデックス、または制約を大文字小文字非依存にする必要がある場合は CITEXT。
• 時刻: TIMESTAMPTZ、DATE、INTERVAL。now() はトランザクション開始時刻、clock_timestamp() は実時刻です。
• ブール値: 三状態が必要でない限り BOOLEAN NOT NULL。
• Enum: 小規模で安定した集合にのみ CREATE TYPE ... AS ENUM を使用します。変化するビジネス値には TEXT + CHECK または参照テーブルを使用します。
• JSONB: 任意/半構造化属性にのみ使用し、GIN でインデックスします。
• 配列、範囲、ネットワーク、幾何、全文検索、ドメイン、複合、ベクター型、および TOAST ストレージと照合順序の制御については、references/details.md を参照してください。

避けるべき型

避ける	代わりに使う
timestamp（タイムゾーンなし）	timestamptz
char(n)、varchar(n)	text（必要に応じて長さの CHECK を併用）
money	numeric
timetz	timestamptz
timestamptz(0) または任意の精度	timestamptz
serial	generated always as identity

制約

• PK: 暗黙的に UNIQUE + NOT NULL となり、B-tree インデックスを作成します。
• FK: ON DELETE/UPDATE (CASCADE、RESTRICT、SET NULL、SET DEFAULT) を指定します。参照する列にインデックスを付けます。コミット時にチェックされる循環依存には DEFERRABLE INITIALLY DEFERRED を使用します。
• UNIQUE: B-tree インデックスを作成します。NULLS NOT DISTINCT (PG15+) でない限り、複数の NULL を許可します。重複した NULL を意図しない限り、NULLS NOT DISTINCT を推奨します。
• CHECK: 行ローカルです。NULL は通過します (三値論理)。NOT NULL と組み合わせます: price NUMERIC NOT NULL CHECK (price > 0)。
• EXCLUDE: 演算子を用いて重複を防ぎます。たとえば EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) は二重予約を防ぎます。GiST 対応の型が必要です。

インデックス作成

• B-tree: 等価比較・範囲検索 (=, <, >, BETWEEN, ORDER BY) のデフォルトです。
• 複合: 最左プレフィックスの規則 (WHERE a = ? AND b > ? は (a,b) を使用し、WHERE b = ? は使用しません)。最も選択性の高い列を先頭にします。
• カバリング: インデックスオンリースキャンには CREATE INDEX ON tbl (id) INCLUDE (name, email) を使用します。
• 部分: ホットなサブセットには CREATE INDEX ON tbl (user_id) WHERE status = 'active' を使用します。
• 式: CREATE INDEX ON tbl (LOWER(email))。クエリは同じ式を使用する必要があります。
• GIN: JSONB の包含・存在チェック、配列、全文検索。GiST: 範囲、ジオメトリ、排他制約。
• BRIN: 大規模で自然に順序付けられたデータ (時系列) を最小のストレージコストで扱います。ディスク上の順序がインデックス対象の列と相関している場合に効果的です。

パーティショニング

• クエリが一貫してパーティションキーでフィルタリングする大規模テーブル (1 億行超)、またはメンテナンス (プルーニング、一括置換) がキーに沿って行われる場合に使用します。
• 時系列には RANGE (PARTITION BY RANGE (created_at)。TimescaleDB は保持ポリシーと圧縮でこれを自動化します)、離散値には LIST、自然なキーがなく均等に分散させたい場合には HASH を使用します。
• 制約による除外: プランナは CHECK 制約を通じてパーティションをプルーニングします。宣言的パーティショニング (PG10+) はこれらを自動的に作成します。
• 宣言的パーティショニングまたはハイパーテーブルを推奨します。テーブル継承は使用しないでください。
• 制限事項: グローバルな UNIQUE 制約はありません。PK/UNIQUE にパーティションキーを含めてください。パーティションテーブルからの FK には PG11+、パーティションテーブルを参照する FK には PG12+ が必要です。古いバージョンではトリガーを使用してください。

例

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);

-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);

さらに詳しく

references/details.md には、このファイルが名前だけ挙げている内容が収められています:

• 完全なデータ型カタログ: TOAST ストレージ、照合順序、配列、範囲、ネットワーク、幾何、テキスト検索、ドメイン、複合、ベクトル。
• テーブル型 (TEMPORARY、UNLOGGED) と行レベルセキュリティ。
• 制約とインデックスに関する補足、および RANGE、LIST、HASH のパーティショニング DDL。
• ワークロードパターン: 更新重視、挿入重視、upsert 設計、安全なスキーマ進化。
• 生成列と拡張機能 (pg_trgm、citext、timescaledb、postgis、pgvector など)。
• jsonb_path_ops や抽出した B-tree 列を含む JSONB インデックス戦略。