postgresql

PostgreSQL テーブル設計

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

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

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

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

手順

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

安全性

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

基本ルール

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

PostgreSQL の「落とし穴」

• 識別子: 引用符なし → 小文字化されます。引用符付き/大文字混在の名前は避けてください。規約: テーブル/カラム名には snake_case を使用してください。
• ユニーク + 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 を推奨します。正確な 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 でインデックスを作成します。スケジューリング、バージョニング、数値範囲に適しています。境界スキームを 1 つ選び、一貫して使用してください。デフォルトでは [)（包含／排他）を推奨します。
• ネットワーク型: 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 よりも推奨します。GIN でインデックスを作成します。オプションまたは半構造化された属性にのみ使用してください。コンテンツの元の順序を必ず保持する必要がある場合にのみ JSON を使用します。
• ベクトル型: 埋め込みのベクトル類似検索には pgvector による vector 型を使用します。

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

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

テーブルの種類

• 通常: デフォルト。完全に永続化され、ログに記録されます。
• 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 値はチェックを通過します（三値論理）。例: 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: 時系列に不可欠です。自動パーティショニング、保持、圧縮、継続的な集約を行います。
• 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"}'])
• @> の負荷が大きいワークロード: 包含専用のより小さく高速なインデックスには opclass 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')

例

ユーザー

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