design-postgres-tables

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 を使用します。uuidv7()（PG18 以降を使用する場合に推奨）または gen_random_uuid()（古い PG バージョンを使用する場合）で生成します。
• 整数：ストレージ容量が重要でない限り BIGINT を優先します。範囲が小さい場合は INTEGER を使用し、制約がある場合を除き SMALLINT は避けます。
• 浮動小数点数：ストレージ容量が重要でない限り、REAL より DOUBLE PRECISION を優先します。正確な小数演算には 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 を使用します。ネットワーク演算子（<<、>>、&&）をサポートします。
• 幾何型：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 よりも JSONB を推奨します。GIN でインデックスを作成します。オプションの／半構造化された属性にのみ使用します。内容の元の順序を必ず保持しなければならない場合に限り、JSON を使用します。
• ベクトル型：埋め込みのベクトル類似度検索には、pgvector の vector 型を使用します。

次のデータ型は使用しないでください

• timestamp（タイムゾーンなし）は使用しないでください。代わりにtimestampzを使用してください。
• char(n)またはvarchar(n)は使用しないでください。代わりにtextを使用してください。
• money型は使用しないでください。代わりにnumericを使用してください。
• timetz型は使用しないでください。代わりにtimestampzを使用してください。
• timestampz(0)やその他の精度指定は使用しないでください。代わりにtimestampzを使用してください。
• serial型は使用しないでください。代わりにgenerated always as identityを使用してください。
• POINT、LINE、POLYGON、CIRCLEの組み込み型は使用しないでください。代わりにpostgis拡張機能のgeometryを使用してください。

テーブルの種類

• 通常：デフォルトです。完全に永続化され、ログに記録されます。
• 一時：セッション内でのみ有効で、自動的に削除され、ログには記録されません。一時的な作業に適しており、高速です。
• 非ログ：永続化されますが、クラッシュ時の安全性はありません。書き込みが高速で、キャッシュやステージングに適しています。

行レベルセキュリティ

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)では、priceが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：時系列データに不可欠です。パーティショニングの自動化、保持、圧縮、継続的な集計を提供します。
• 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 を使うより、B-tree を使用する WHERE price BETWEEN 100 AND 500 のようなクエリを推奨します。
• 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);