postgresql

PostgreSQL のテーブル設計

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

• PostgreSQL のスキーマを設計するとき
• データ型と制約を選択するとき
• インデックス、パーティション、または RLS ポリシーを計画するとき
• テーブルのスケーラビリティと保守性をレビューするとき

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

• PostgreSQL 以外のデータベースを対象とするとき
• スキーマを変更せず、クエリのチューニングのみが必要なとき
• データベースに依存しないモデリングガイドが必要なとき

手順

1. エンティティ、アクセスパターン、規模の目標（行数、QPS、保持期間）を把握します。
2. 不変条件を確実に適用できるデータ型と制約を選びます。
3. 実際のクエリパスに合わせてインデックスを追加し、EXPLAIN で検証します。
4. 規模やアクセス制御の要件に応じて、パーティショニングまたは RLS を計画します。
5. マイグレーションの影響を確認し、安全に変更を適用します。

安全性

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

基本ルール

• 参照用テーブル（users、orders など）には PRIMARY KEY を定義します。時系列データ、イベントデータ、ログデータでは必ずしも必要ありません。使用する場合は、BIGINT GENERATED ALWAYS AS IDENTITY を推奨します。グローバルな一意性や不透明性が必要な場合にのみ UUID を使用してください。
• まず 正規化（第3正規形まで） して、データの冗長性と更新時の異常をなくします。非正規化は、結合パフォーマンスに実際の問題があると確認された場合に限り、測定済みで高い ROI が見込める読み取り処理のために行います。早まった非正規化は保守の負担を生みます。
• 意味上必要な箇所にはすべて NOT NULL を追加し、一般的な値には DEFAULT を使用します。
• 実際にクエリするアクセスパスにインデックスを作成します。PK／一意制約（自動）、FK 列（手動！）、頻繁に使うフィルター／ソート条件、結合キーが対象です。
• イベント時刻には TIMESTAMPTZ、金額には NUMERIC、文字列には TEXT、整数値には BIGINT、浮動小数点値には DOUBLE PRECISION（正確な10進演算には NUMERIC）を推奨します。

PostgreSQL の「落とし穴」

• 識別子：引用符で囲まれていない識別子は小文字に変換されます。引用符付きの名前や大文字・小文字が混在する名前は避けてください。慣例として、テーブル名と列名には snake_case を使用します。
• 一意制約と NULL：UNIQUE では複数の NULL が許可されます。NULL を1つだけに制限するには、UNIQUE NULLS NOT DISTINCT (...)（PG15 以降）を使用します。
• FK インデックス：PostgreSQL は参照先の FK 列にインデックスを自動作成しません。結合や親テーブルの更新／削除に対するインデックスの必要性を、書き込みコストと照らし合わせて検討してください。
• 暗黙の変換は行われない：長さや精度のオーバーフローはエラーになる場合があります。一方で、数値のスケール変換では丸めが行われることがあり、明示的な文字型キャストでは切り詰められることがあります。例：NUMERIC(2,0) に 999 を挿入するとエラーになり、暗黙的に切り詰めたり丸めたりする一部のデータベースとは異なります。
• シーケンス／identity には欠番が生じます（正常な動作です。「修正」しないでください）。ロールバック、クラッシュ、同時実行トランザクションにより、ID シーケンスには欠番（1、2、5、6...）が生じます。これは想定された動作です。ID を連番にしようとしないでください。
• ヒープストレージ：デフォルトでは、PK に基づくクラスタ化は行われません（SQL Server／MySQL InnoDB とは異なります）。CLUSTER は一度限りの再編成であり、その後の挿入では維持されません。ヒープの物理的な順序は保証されず、更新やメンテナンスによって変わります。クエリの順序には ORDER BY が必要です。
• 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 を使用します。TOAST の判断は、個々の値に対する一律の 2KB しきい値ではなく、タプル全体のサイズ、圧縮可能性、ストレージ設定に依存します。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 を使用します。ネットワーク演算子（<<、>>、&&）をサポートします。
• 幾何型：2D 空間データには 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（タイムゾーンなし）は使用しないでください。代わりに timestamptz を使用してください。
• char(n) または varchar(n) は使用しないでください。代わりに text を使用してください。
• money 型は使用しないでください。代わりに numeric を使用してください。
• timetz 型は使用しないでください。代わりに timestamptz を使用してください。
• timestamptz(0) やその他の精度指定は使用しないでください。代わりに timestamptz を使用してください。
• serial 型は使用しないでください。代わりに generated always as identity を使用してください。

テーブルの種類

• Regular：デフォルトです。完全な耐久性があり、ログに記録されます。
• TEMPORARY：通常はセッションの範囲内で、セッション終了時に削除されます。ON COMMIT によってライフサイクルを変更でき、データは WAL に記録されません。一時的な作業には高速です。
• 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())。current_user_id() 関数はアプリケーション固有のプレースホルダーであり、組み込み関数ではありません。信頼できる ID 関数を定義し、非オーナーのロールでポリシーをテストするとともに、オーナーおよび BYPASSRLS によるバイパスと FORCE ROW LEVEL SECURITY を考慮してください。

制約

• 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 値はチェックを通過します（三値論理）。例：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) のインデックスが使用されますが、b だけを対象とする述語では、スキャンまたはバージョンに依存するスキップスキャンが使用される場合があります。実際の実行計画を確認してください）。選択性が最も高い列、または最も頻繁にフィルターに使われる列を先頭に配置してください。
• カバリング：CREATE INDEX ON tbl (id) INCLUDE (name, email)。ヒープへのアクセスを回避したインデックスオンリースキャン用に、キー以外の列を含めます。ただし、MVCC の可視性が許す場合に限ります。
• 部分インデックス：頻繁に使われるサブセット向けです（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。プランナーは、クエリの述語がインデックスの述語を含意すると証明する必要があります。汎用のパラメーター化プランやコスト判断によって、インデックスが使用されない場合があります。
• 式インデックス：計算された検索キー向けです（CREATE INDEX ON tbl (LOWER(email))）。WHERE 句の式は完全に一致する必要があります：WHERE LOWER(email) = '[email protected]'。
• GIN：JSONB の包含・存在判定、配列（@>、?）、全文検索（@@）向けです。
• GiST：範囲、ジオメトリ、排他制約向けです。
• BRIN：自然な順序を持つ非常に大きなデータ（時系列）向けです。ストレージのオーバーヘッドは最小限です。ディスク上の行順序がインデックス対象の列と相関している場合（挿入順、または CLUSTER の実行後）に効果的です。

パーティショニング

• パーティションキー（多くの場合、時刻や日付）で一貫して絞り込むクエリが実行される、非常に大きなテーブル（>100M rows）に使用します。
• 別の用途として、データの削除や一括置換などのデータ保守作業を定期的に行うテーブルにも使用します。
• 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制約を使用する別の仕組みです。
• 宣言的パーティショニングまたはハイパーテーブルを優先してください。テーブル継承は使用しないでください。
• 制限事項：グローバルなUNIQUE制約は設定できないため、PK／UNIQUEにはパーティションキーを含めてください。最新のPostgreSQLはパーティションテーブル上の外部キーをサポートしています。整合性を場当たり的なトリガーで代替せず、対象のバージョンと制約ルールを確認してください。

特別な考慮事項

更新頻度の高いテーブル

• 頻繁に更新される列と低頻度の列を分離する—頻繁に更新される列を別のテーブルに置き、肥大化を最小限に抑えます。
• HOT更新用の空き領域を確保し、インデックスの保守を回避するために、fillfactor=90を使用します。
• インデックス対象の列の更新は避けます—有益なHOT更新ができなくなるためです。
• 更新パターンに基づいてパーティション分割します—頻繁に更新される行を、安定したデータとは別のパーティションに分けます。

挿入頻度の高いワークロード

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

Upsertに適した設計

• 競合対象の列にUNIQUEインデックスが必要です—ON CONFLICT (col1, col2)には、完全に一致する一意インデックスが必要です（競合対象に互換性のあるWHERE述語が含まれている場合、部分一意インデックスを推論できます）。
• 挿入される値を参照するには**EXCLUDED.columnを使用します**。書き込みのオーバーヘッドを減らすため、実際に変更された列だけを更新してください。
• 実際に更新する必要がない場合は、DO NOTHINGのほうがDO UPDATEより高速です。

安全なスキーマの変更

• トランザクションDDL：ほとんどのDDL操作はトランザクション内で実行でき、ロールバックも可能です。安全にテストするには、BEGIN; ALTER TABLE...; ROLLBACK;を使用します。
• インデックスの並行作成：CREATE INDEX CONCURRENTLYは書き込みをブロックしませんが、トランザクション内では実行できません。
• 揮発性のデフォルト値はテーブルの書き換えを引き起こします：揮発性のデフォルト値（例：gen_random_uuid()。now()はSTABLEであり、VOLATILEではありません）を指定して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);
  • トレードオフ：キーの存在確認（?、?|、?&）クエリのサポートは失われます。サポートされる JSONPath 演算子 @? および @@ は利用できますが、キーの存在確認演算子は利用できません。
• 特定のスカラー値に対する等価比較／範囲検索には、値を抽出して 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);

制限事項

設計上のデフォルトはトレードオフであり、型を一律に禁止するものではありません。意図的な壁時計時刻の表現にはタイムゾーンなしの timestamp を使用でき、varchar で長さを制限できます。インデックス、パーティショニング、拡張機能の選択では、固定の行数しきい値や特定ベンダーの拡張機能の必須使用を前提とせず、ワークロードとバージョンを検証する必要があります。pgAudit のログは設定に依存し、すべてのアクティビティが記録される保証はありません。メールアドレスの正規表現例は意図的に不完全です。メールが配信可能であることの証明には使用しないでください。ロック時間の予算とバックアップを考慮して移行のリハーサルを行ってください。ロールバックですべての外部への影響が取り消されるわけではありません。