PostgreSQL Table Design

このスキルを使用する場合

• PostgreSQL のスキーマを設計する
• データ型と制約を選択する
• インデックス、パーティション、RLS ポリシーを計画する
• テーブルをスケーラビリティとメンテナンス性について確認する

このスキルを使用しない場合

• PostgreSQL 以外のデータベースをターゲットにしている
• スキーマ変更なしでクエリチューニングのみが必要
• DB に依存しないモデリングガイドが必要

手順

1. エンティティ、アクセスパターン、スケール目標（行数、QPS、保持期間）をキャプチャする。
2. 不変条件を強制するデータ型と制約を選択する。
3. 実際のクエリパスに対するインデックスを追加し、EXPLAIN で検証する。
4. スケールやアクセス制御に必要な場合、パーティショニングまたは RLS を計画する。
5. マイグレーションの影響を確認し、変更を安全に適用する。

セキュリティ

• バックアップとロールバック計画なしに本番環境で破壊的な DDL を避ける。
• スキーマ変更を適用する前に、マイグレーションとステージング検証を使用する。

コアルール

• 参照テーブル（users、orders など）に PRIMARY KEY を定義する。時系列/イベント/ログデータの場合は常に必要ではない。使用する場合は、BIGINT GENERATED ALWAYS AS IDENTITY を優先する。グローバルな一意性/非透過性が必要な場合のみ UUID を使用する。
• まずは正規化（3NF まで） してデータ冗長性と更新異常を排除する。結合パフォーマンスが実証的に問題であることが測定された、高い ROI の読み取りに対してのみ 非正規化 する。早期の非正規化はメンテナンス負担を生み出す。
• NOT NULL をセマンティクスに基づいて必要な場所に追加する。一般的な値に対して DEFAULT を使用する。
• 実際にクエリを実行するアクセスパスに対するインデックスを作成する：PK/UNIQUE（自動）、FK 列（手動！）、頻繁なフィルタ/ソート、結合キー。
• TIMESTAMPTZ をイベント時刻に、NUMERIC を金銭に、TEXT を文字列に、BIGINT を整数値に、DOUBLE PRECISION をフロート値に優先する（または正確な 10 進演算には NUMERIC を使用する）。

PostgreSQL の「落とし穴」

• 識別子：引用符なし → 小文字に変換される。引用符付き/混合ケース名を避ける。規約：テーブル/列名に snake_case を使用する。
• UNIQUE + NULL：UNIQUE は複数の NULL を許可する。UNIQUE (...) NULLS NOT DISTINCT（PG15+）を使用して 1 つの NULL に制限する。
• FK インデックス：PostgreSQL は FK 列に自動的にインデックスを付けない。手動で追加する。
• サイレント強制なし：長さ/精度オーバーフローはエラーになる（切り詰めない）。例：999 を NUMERIC(2,0) に挿入するとエラーで失敗し、一部のデータベースのようにサイレント切り詰めまたは丸めはされない。
• シーケンス/ID はギャップがある（正常；「修正」しない）。ロールバック、クラッシュ、同時実行トランザクションは ID シーケンスにギャップを作成する（1、2、5、6...）。これは予期された動作である—ID を連続させようとしない。
• ヒープストレージ：デフォルトではクラスタ化 PK なし（SQL Server/MySQL InnoDB とは異なる）。CLUSTER は 1 回限りの再構成で、その後の挿入では保持されない。ディスク上の行順は明示的にクラスタ化されない限り、挿入順である。
• MVCC：更新/削除は削除済みタプルを残す。Vacuum はそれらを処理する—ホットワイド行チャーンを避けるように設計する。

データ型

• ID：BIGINT GENERATED ALWAYS AS IDENTITY が優先（GENERATED BY DEFAULT も可）。マージ/フェデレーション/分散システムで使用、または不透過 ID に対して UUID を使用。PG18+ を使用している場合は uuidv7()（優先）で生成、または古い PG バージョンを使用している場合は gen_random_uuid()。
• 整数：ストレージスペースが重要でない限り BIGINT を優先。より小さい範囲には INTEGER。SMALLINT は制約がない限り避ける。
• フロート：ストレージスペースが重要でない限り、REAL より DOUBLE PRECISION を優先。正確な 10 進演算には NUMERIC を使用。
• 文字列：TEXT を優先。長さ制限が必要な場合、VARCHAR(n) の代わりに CHECK (LENGTH(col) <= n) を使用。CHAR(n) を避ける。バイナリデータに BYTEA を使用。大きな文字列/バイナリ（> 2KB デフォルト閾値）は自動的に圧縮で TOAST に保存。TOAST ストレージ：PLAIN（TOAST なし）、EXTENDED（圧縮+行外）、EXTERNAL（行外、圧縮なし）、MAIN（圧縮、可能な限り行内に保持）。デフォルト EXTENDED は通常最適。ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy と ALTER TABLE tbl SET (toast_tuple_target = 4096) で閾値を制御。大文字小文字が不区別：ロケール/アクセント処理には決定論的でない照合順序を使用。純粋な ASCII には LOWER(col) の式インデックス（列が大文字小文字が不区別の PK/FK/UNIQUE を必要としない限り優先）または CITEXT を使用。
• 金銭：NUMERIC(p,s)（フロートは絶対禁止）。
• 時刻：タイムスタンプに TIMESTAMPTZ。日付のみに DATE。期間に INTERVAL。TIMESTAMP（タイムゾーンなし）を避ける。トランザクション開始時刻に now() を使用、現在の壁時刻に clock_timestamp() を使用。
• ブール値：3 状態値が必要でない限り、BOOLEAN に NOT NULL 制約を使用。
• Enum：小さく安定したセット（例：米国の州、曜日）に対して CREATE TYPE ... AS ENUM を使用。ビジネスロジック駆動および進化する値（例：注文ステータス）→ TEXT（またはINT）+ CHECK または参照テーブルを使用。
• 配列：要素をクエリする順序付きリストに TEXT[]、INTEGER[] など。GIN で包含（@>、<@）および重複（&&）クエリのインデックス。アクセス：arr[1]（1 インデックス）、arr[1:3]（スライス）。タグ、カテゴリに適切。関係には結合テーブルを使用して避ける。リテラル構文：'{val1,val2}' または ARRAY[val1,val2]。
• 範囲型：区間に daterange、numrange、tstzrange。重複（&&）、包含（@>）、演算子をサポート。GiST でインデックス。スケジューリング、バージョン管理、数値範囲に適切。境界スキームを選択し、一貫して使用。デフォルトでは [)（包含/排他）を優先。
• ネットワーク型：IP アドレスに INET、ネットワーク範囲に CIDR、MAC アドレスに MACADDR。ネットワーク演算子（<<、>>、&&）をサポート。
• 幾何型：2D 空間データに POINT、LINE、POLYGON、CIRCLE。GiST でインデックス。高度な空間機能には PostGIS を検討。
• テキスト検索：フルテキスト検索ドキュメントに TSVECTOR、検索クエリに TSQUERY。tsvector を GIN でインデックス。常に言語を指定：to_tsvector('english', col) と to_tsquery('english', 'query')。単一引数バージョンを使用しない。これはインデックス式とクエリの両方に適用。
• ドメイン型：CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') を検証で再利用可能なカスタム型。テーブル全体で制約を強制。
• 複合型：CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) を列内の構造化データ。(col).field 構文でアクセス。
• JSONB：JSON より優先。GIN でインデックス。オプション/半構造化属性のみ使用。コンテンツの元の順序が保持される必要がある場合のみ JSON を使用。
• ベクトル型：pgvector の vector 型は埋め込みのベクトル類似度検索。

以下のデータ型を使用しないこと

• timestamp（タイムゾーンなし）を使用しない；代わりに timestamptz を使用。
• char(n) または varchar(n) を使用しない；代わりに text を使用。
• money 型を使用しない；代わりに numeric を使用。
• timetz 型を使用しない；代わりに timestamptz を使用。
• timestamptz(0) またはその他の精度仕様を使用しない；代わりに timestamptz を使用。
• serial 型を使用しない；代わりに generated always as identity を使用。

テーブル型

• Regular：デフォルト。完全に耐久性があり、ログ記録。
• TEMPORARY：セッションスコープ、自動削除、ログなし。スクラッチ作業用に高速。
• UNLOGGED：永続的だが、クラッシュセーフでない。より速い書き込み。キャッシュ/ステージングに適切。

行レベルセキュリティ

ALTER TABLE tbl ENABLE ROW LEVEL SECURITY で有効化。ポリシーを作成：CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())。行レベルでの組み込みユーザーベースアクセス制御。

制約

• PK：暗黙的な UNIQUE + NOT NULL。B-tree インデックスを作成。
• FK：ON DELETE/UPDATE アクション（CASCADE、RESTRICT、SET NULL、SET DEFAULT）を指定。参照列の明示的なインデックスを追加—結合を高速化し、親削除/更新でロック問題を防止。循環 FK 依存関係に DEFERRABLE INITIALLY DEFERRED を使用（トランザクション終了時にチェック）。
• UNIQUE：B-tree インデックスを作成。NULLS NOT DISTINCT（PG15+）がない限り複数の NULL を許可。標準動作：(1, NULL) と (1, NULL) は許可。NULLS NOT DISTINCT で：1 つの (1, NULL) のみ許可。重複 NULL が必要でない限り NULLS NOT DISTINCT を優先。
• CHECK：行ローカル制約。NULL 値はチェックをパス（3 値論理）。例：CHECK (price > 0) は 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) - テーブルを訪問せずにインデックスのみスキャンのための非キー列を含める。
• 部分：ホットサブセット（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。status = 'active' の任意クエリはこのインデックスを使用できます。
• 式：計算された検索キー（CREATE INDEX ON tbl (LOWER(email))）。式は WHERE 句で正確に一致する必要がある：WHERE LOWER(email) = 'user@example.com'。
• GIN：JSONB 包含/存在、配列（@>、?）、フルテキスト検索（@@）
• GiST：範囲、幾何、除外制約
• BRIN：非常に大きく、自然にソートされたデータ（時系列）—最小限のストレージ オーバーヘッド。行順がディスク上のインデックス列と相関するとき有効（挿入順序または CLUSTER 後）。

パーティショニング

• 非常に大きなテーブル（> 100M 行）でクエリがパーティションキー（多くの場合時刻/日付）で一貫してフィルタする場合に使用。
• 代替使用：データ定期的に削除または一括置換されるデータメンテナンスタスクがテーブルを指示する場合に使用。
• RANGE：時系列の一般的（PARTITION BY RANGE (created_at)）。パーティション作成：CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')。TimescaleDB は時間ベースまたは ID ベースのパーティショニング、保持ポリシー、圧縮を自動化。
• LIST：離散値（PARTITION BY LIST (region)）。例：FOR VALUES IN ('us-east', 'us-west')。
• HASH：自然キーがない場合、均等分布（PARTITION BY HASH (user_id)）。修飾数で N パーティション作成。
• 制約除外：クエリプランナがプルーニングするために CHECK 制約がパーティションに必要。宣言型パーティショニング（PG10+）で自動作成。
• 宣言型パーティショニングまたはハイパーテーブルを優先。テーブル継承を使用しない。
• 制限：グローバル UNIQUE 制約なし—パーティションキーを PK/UNIQUE に含める。パーティション化テーブルからの FK はサポートされない。トリガー使用。

特別な考慮事項

更新が多いテーブル

• ホット/コールド列を分離—頻繁に更新される列を別のテーブルに配置してブロートを最小化。
• fillfactor=90 を使用 —ホット更新のためにスペースを残し、インデックス保守を避ける。
• インデックス付き列の更新を避ける—有益なホット更新を防止。
• 更新パターンでパーティション—安定したデータから異なるパーティションで頻繁に更新される行を分離。

挿入が多いワークロード

• インデックスを最小化—クエリするもののみ作成。各インデックスは挿入を遅くする。
• 単一行挿入の代わりに COPY または複数行 INSERT を使用。
• 再構築可能なステージングデータに対して UNLOGGED テーブル—はるかに速い書き込み。
• 一括ロードのためにインデックス作成を遅延 —> インデックス削除、データ読み込み、インデックス再作成。
• 時間/ハッシュでパーティション —ロード分散。TimescaleDB はインデックス集約的なデータのパーティショニングと圧縮を自動化。
• グローバル一意性を強制することが重要な場合、（タイムスタンプ、device_id）などの自然キーをプライマリキーに対して使用。多くの挿入集約的なテーブルはプライマリキーを必要としない。
• サロゲートキーが必要な場合、UUID より BIGINT GENERATED ALWAYS AS IDENTITY を優先。

Upsert フレンドリー設計

• 競合ターゲット列の UNIQUE インデックスが必要 —ON CONFLICT (col1, col2) は正確に一致する UNIQUE インデックスが必要（部分インデックスは機能しない）。
• EXCLUDED.column を使用 —挿入される値をリファレンス。実際に変更された列のみ更新して書き込み オーバーヘッドを削減。
• DO NOTHING が DO UPDATE より速い —実際の更新が必要ない場合。

安全なスキーマ進化

• トランザクション DDL：ほとんどの DDL 操作はトランザクション内で実行でき、ロールバック可能—BEGIN; ALTER TABLE...; ROLLBACK; で安全なテスト。
• 同時実行インデックス作成：CREATE INDEX CONCURRENTLY は書き込みをブロックしないが、トランザクションで実行できない。
• 揮発性デフォルトがテーブル全体を再書き込みする：揮発性デフォルト（例：now()、gen_random_uuid()）を持つ NOT NULL 列を追加するとテーブル全体が再書き込みされる。非揮発性デフォルトは高速。
• 列の前に制約を削除：ALTER TABLE DROP CONSTRAINT その後 DROP COLUMN で依存関係の問題を避ける。
• 関数シグネチャの変更：異なる引数で CREATE OR REPLACE はオーバーロードを作成、置換ではない。オーバーロードが不要な場合は古いバージョンを削除。

生成列

• ... GENERATED ALWAYS AS (<expr>) STORED 計算可能、インデックス可能フィールド。PG18+ では VIRTUAL 列を追加（読み込み時に計算、保存されない）。

拡張機能

• pgcrypto：パスワードハッシュに crypt()。
• uuid-ossp：代替 UUID 関数。新しいプロジェクトには pgcrypto を優先。
• pg_trgm：% 演算子、similarity() 関数でファジー テキスト検索。LIKE '%pattern%' 高速化に GIN でインデックス。
• citext：大文字小文字が不区別のテキスト型。LOWER(col) の式インデックスを優先（大文字小文字が不区別の制約が必要でない限り）。
• btree_gin/btree_gist：混合型インデックスを有効（例：JSONB と TEXT 列の両方の GIN インデックス）。
• hstore：キー値ペア。主に JSONB に置き換えられたが、単純な文字列マッピングに有用。
• timescaledb：時系列に不可欠—自動パーティショニング、保持、圧縮、連続集計。
• postgis：基本幾何型を超えた包括的な地理空間サポート—ロケーションベースのアプリケーションに不可欠。
• pgvector：埋め込みのベクトル類似度検索。
• pgaudit：すべてのデータベースアクティビティの監査ログ。

JSONB ガイダンス

• GIN インデックスで JSONB を優先。
• デフォルト：CREATE INDEX ON tbl USING GIN (jsonb_col); → 加速：
  • 包含 jsonb_col @> '{"k":"v"}'
  • キー存在 jsonb_col ? 'k'、任意/すべてのキー ?\|、?&
  • パス包含 ネストドキュメント上
  • 論理和 jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
• 重い @> ワークロード：jsonb_path_ops opclass でより小さい/高速包含のみインデックスを検討：
  • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
  • トレードオフ：キー存在（?、?|、?&）クエリのサポートを失う—包含（@>）のみをサポート。
• 特定のスカラーフィールドの等価/範囲：B-tree で抽出とインデックス（生成列または式）：
  • ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
  • CREATE INDEX ON tbl (price);
  • WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 インデックスなしではなく、WHERE price BETWEEN 100 AND 500（B-tree を使用）のようなクエリを優先。
• JSONB の内部配列：包含（例：タグ）に GIN + @> を使用。包含のみを実行する場合、jsonb_path_ops を検討。
• コア関係をテーブルに保持。オプション/可変属性に JSONB を使用。
• 許可される JSONB 値を列に制限する制約を使用、例：config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

例

ユーザー

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

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);