postgres-schema-design

PostgreSQL テーブル設計

基本ルール

• 参照テーブル（users、orders など）には PRIMARY KEY を定義してください。時系列・イベント・ログデータでは必ずしも必要ではありません。使用する場合は BIGINT GENERATED ALWAYS AS IDENTITY を優先し、グローバルな一意性や不透明性が必要な場合にのみ UUID を使用してください。
• まず 3NF まで正規化 してデータの冗長性や更新異常を排除してください。非正規化は、結合性能が問題であることが計測によって証明された、投資対効果の高い読み取りに 限って 行ってください。時期尚早な非正規化は保守の負担を生みます。
• 意味的に必須の箇所にはすべて 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 を挿入するとエラーになりますが、サイレントに切り捨てたり丸めたりする一部のデータベースとは異なります。
• シーケンスや ID には欠番が生じる（正常な動作であり、「修正」しないでください）。ロールバック、クラッシュ、同時トランザクションによって ID シーケンスに欠番（1, 2, 5, 6...）が生じます。これは想定された動作であり、ID を連続させようとしないでください。
• ヒープストレージ: デフォルトではクラスタ化された PK はありません（SQL Server や MySQL の InnoDB とは異なります）。CLUSTER は一度限りの再編成であり、その後の挿入では維持されません。明示的にクラスタ化しない限り、ディスク上の行の順序は挿入順になります。
• 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() を使用します。
• 真偽値: 三値が必要な場合を除き、NOT NULL 制約付きの BOOLEAN を使用します。
• 列挙型: 小規模で安定した値の集合（例: 米国の州、曜日）には 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 を使用します。ネットワーク演算子（<<、>>、&&）をサポートします。
• 幾何型: 2 次元の空間データには POINT、LINE、POLYGON、CIRCLE を使用します。インデックスには GiST を使用します。高度な空間機能には PostGIS の利用を検討してください。
• 全文検索: 全文検索ドキュメントには TSVECTOR、検索クエリには TSQUERY を使用します。tsvector には GIN でインデックスを作成します。言語は必ず指定してください: to_tsvector('english', col) および to_tsquery('english', 'query')。引数が 1 つのバージョンは決して使用しないでください。これはインデックス式とクエリの両方に適用されます。
• ドメイン型: 検証付きで再利用可能なカスタム型には、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, NULL) は 1 つだけ許可されます。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) = '[email protected]'。
• GIN: JSONB の包含・存在チェック、配列（@>、?）、全文検索（@@）に使用します。
• GiST: 範囲、ジオメトリ、排他制約に使用します。
• BRIN: 非常に大きく、自然に順序付けられたデータ（時系列データ）向けです。ストレージのオーバーヘッドが最小限です。ディスク上の行の順序がインデックス対象の列と相関している場合（挿入順や CLUSTER 実行後）に効果的です。

パーティショニング

• クエリが常にパーティションキー（多くの場合、時間や日付）でフィルタリングされる、非常に大きなテーブル（1 億行超）に使用します。
• 別の用途: データのメンテナンスタスクによって、定期的なデータの削除や一括置換が行われるテーブルに使用します。
• 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 を使用する—インデックスのメンテナンスを回避する HOT 更新のための領域を残します。
• インデックスが付与された列の更新を避ける—有用な HOT 更新を妨げます。
• 更新パターンでパーティショニングする—頻繁に更新される行を、安定したデータとは別のパーティションに分離します。

挿入が多いワークロード

• インデックスを最小限にする—クエリする対象のみを作成します。すべてのインデックスは挿入を遅くします。
• 単一行の挿入ではなく、COPY または複数行の INSERT を使用する。
• 再構築可能なステージングデータには UNLOGGED テーブル—書き込みがはるかに高速になります。
• 一括ロードでは インデックス作成を後回しにする—インデックスを削除し、データをロードし、インデックスを再作成します。
• 負荷を分散するために 時間またはハッシュでパーティショニングする。TimescaleDB は、挿入が多いデータのパーティショニングと圧縮を自動化します。
• グローバルな一意性の強制が重要な場合は、(timestamp, device_id) のような 自然なキーを主キーに使用する。多くの挿入が多いテーブルでは、主キーはまったく必要ありません。
• 代理キーが本当に必要な場合は、UUID よりも BIGINT GENERATED ALWAYS AS IDENTITY を推奨します。

Upsert に適した設計

• 競合ターゲットの列に UNIQUE インデックスが必要—ON CONFLICT (col1, col2) には完全に一致する一意インデックスが必要です（部分インデックスは機能しません）。
• EXCLUDED.column を使用して挿入されようとしている値を参照します。実際に変更された列のみを更新して、書き込みのオーバーヘッドを削減します。
• 実際の更新が不要な場合、DO UPDATE よりも DO NOTHING の方が高速です。

安全なスキーマの進化

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

生成列

• 計算され、インデックス可能なフィールドには ... 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 列とテキスト列の両方に対する GIN インデックス）。
• hstore: キーと値のペア。現在は主に JSONB に置き換えられていますが、単純な文字列のマッピングには便利です。
• timescaledb: 時系列データに不可欠で、自動パーティショニング、保持期間の管理、圧縮、継続的集計を提供します。セルフホスト可能で、Tiger Cloud でも利用できます。
• 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 の使用を検討してください:
  • 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')

例

Users

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

Orders

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