履歴・状態・削除・多対多・非正規化——正規化の先にある「実務の判断」を身につける回である。
第4回・第5回で正規化と型・制約の基礎を身につけた。この回では、それだけでは決まらない実務上の判断を扱う。価格改定のような「時間で変わる値」をどう持つか、注文ステータスのような「決まった経路でしか変わらない値」をどう守るか、削除や非正規化をいつ選びいつ避けるか、という4つのテーマを、共通スキーマのproduct_pricesとordersを素材に具体的なSQLで詰める。
product_pricesにtstzrange + EXCLUDE USING gistで、有効期間が重複しない制約を実装できる第1回で構築したスキーマ（customers / categories / products / product_prices / orders / order_items / events）にデータが投入済みであることを前提とする。本回では有効期間の重複を防ぐためにEXCLUDE制約を使い、その内部でbtree_gist拡張が必要になる。あらかじめ有効化しておく。
psql -d postshop -c "CREATE EXTENSION IF NOT EXISTS btree_gist;"
PostgreSQL 16系を前提とする。
履歴・状態・削除の実装で使う道具の名前を先に揃えておく。 回をまたいで使う語は用語集にもまとめている。
| 用語 | 一行での説明 |
|---|---|
range型（tstzrange） | 「いつからいつまで」のような区間を1つの値として持つ型 |
排他制約（EXCLUDE） | 「この条件で衝突する行どうしは共存できない」をDBに強制させる制約 |
btree_gist | GiST索引の中で = のような通常の比較演算子も使えるようにする拡張 |
| トリガ | 行の挿入・更新・削除に反応して、自動的に指定した関数を実行させる仕組み |
| PL/pgSQL | PostgreSQLの手続き型言語。トリガ関数などの中身を書くのに使う |
| ビュー | 名前を付けて保存したSELECT文。テーブルと同じように参照できる |
| 部分インデックス | 条件に合う行だけを載せた索引。対象が偏っているとき小さく速い |
| 論理削除 | 行を物理的に消さず、削除済みを表す列を立てて見えなくする設計 |
| 非正規化 | 正規化で分離した値を、意図的に重複させて持たせること |
product_pricesは商品の価格改定履歴を保持するテーブルである。products.priceが「現在価格」の1点のスナップショットだとすれば、product_pricesは「いつからいつまでいくらだったか」を区間として記録する。履歴テーブルが必要になる場面は、価格改定に限らない。契約プランの変更、住所変更履歴、ステータス変更ログなど、「時間軸で値が変わり、過去の値も参照したい」ものすべてに共通する設計課題である。
共通スキーマのproduct_pricesはこの方式で定義されている。
CREATE TABLE product_prices (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_id bigint NOT NULL REFERENCES products(id),
price numeric(10,2) NOT NULL CHECK (price >= 0),
valid_from timestamptz NOT NULL,
valid_to timestamptz -- NULLは現在も有効
);
長所は列がシンプルなことである。ORMやフレームワークとの相性がよく、valid_from・valid_toという名前だけで意図が伝わる。短所は、「同じ商品について有効期間が重複しない」という制約を素のCHECK制約では表現できないことである。CHECK制約は挿入・更新されるその1行の値しか見えず、他の行のvalid_from・valid_toと比較できない。したがって、アプリのバグや同時実行の競合によって重複する有効期間のレコードが入り込んでも、DBはそれを自動的には拒否できない。
PostgreSQLのrange型は「区間」を1つの値として表現する型である。tstzrangeはtimestamptzの区間を表す組み込み型で、区間演算子（&&＝重なる、@>＝含む、など）がそのまま使える。
-- 発想を示すための独立設計。実データはproduct_pricesを使い続ける
CREATE TABLE product_price_periods (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
product_id bigint NOT NULL REFERENCES products(id),
price numeric(10,2) NOT NULL CHECK (price >= 0),
valid_period tstzrange NOT NULL
);
長所は、EXCLUDE USING gistと組み合わせることで「同じ商品の区間が重ならない」ことをDBレベルで保証できる点にある。短所は、range型に不慣れなメンバーには読みにくく、ORMのサポートが薄い場合があることである。
| 観点 | valid_from/valid_to | tstzrange |
|---|---|---|
| 重複防止をDBで保証 | 素のCHECKでは不可（トリガ等が別途必要） | EXCLUDE USING gistで可能 |
| 「ある時点の値」を引くクエリ | 2条件のANDで書く | @>演算子1つで書ける |
| 可読性・学習コスト | 低い（誰でも読める） | range型の知識が必要 |
| 有効な索引 | B-treeで部分的にカバー可能 | GiSTが範囲検索に強い |
実務では、既存のvalid_from/valid_to列を残しつつ、その2列から生成列でtstzrangeを導出し、重複防止の保証だけをtstzrangeに担わせるハイブリッドが有力な選択肢になる。既存のクエリを書き換えずに、DBレベルの保証を後から足せるからである。
GENERATED ALWAYS AS ... STOREDでvalid_from・valid_toからtstzrangeの生成列を作り、そこにEXCLUDE USING gistを張る。
ALTER TABLE product_prices
ADD COLUMN valid_period tstzrange
GENERATED ALWAYS AS (tstzrange(valid_from, valid_to, '[)')) STORED;
ALTER TABLE product_prices
ADD CONSTRAINT product_prices_no_overlap
EXCLUDE USING gist (product_id WITH =, valid_period WITH &&);
tstzrange(valid_from, valid_to, '[)')は「valid_from以上、valid_to未満」の半開区間を作る。valid_toがNULLなら上限は無限大（＝現在も有効）として扱われる。EXCLUDE USING gist (product_id WITH =, valid_period WITH &&)は「同じproduct_idかつ区間が重なる（&&）行の共存を許さない」という意味の排他制約である。product_id（bigint）の等価比較をGiSTインデックスの中で使うために、btree_gist拡張が必要になる（GiSTはもともと範囲・幾何型向けのインデックスで、素の等価比較演算子クラスを持たないため）。
重複するINSERTが実際に弾かれる様子を確認する。
-- 商品id=1の価格が2024-01-01から2024-07-01まで1000円だったとする
INSERT INTO product_prices (product_id, price, valid_from, valid_to)
VALUES (1, 1000, '2024-01-01', '2024-07-01');
-- 重なる期間で別の価格をINSERTしようとすると弾かれる
INSERT INTO product_prices (product_id, price, valid_from, valid_to)
VALUES (1, 1200, '2024-06-01', NULL);
ERROR: conflicting key value violates exclusion constraint "product_prices_no_overlap"
DETAIL: Key (product_id, valid_period)=(1, ["2024-06-01 00:00:00+00",)) conflicts with
existing key (product_id, valid_period)=(1, ["2024-01-01 00:00:00+00","2024-07-01 00:00:00+00")).
（タイムゾーン表記はセッションのtimezone設定に依存する。ここではUTCの例を示した。）
アプリのバグや同時実行の競合で価格改定が重複登録されても、DBが機械的に拒否する。この種の保証はアプリ層のバリデーションだけでは達成しづらい。バリデーションのSELECTとINSERTの間に別トランザクションが割り込むレース条件が起こり得るからである。
「ある時点tにおいて有効だった価格」を引く書き方は2通りある。
valid_from/valid_toの2条件で書く場合。
SELECT price
FROM product_prices
WHERE product_id = 1
AND valid_from <= '2024-06-15'::timestamptz
AND (valid_to IS NULL OR '2024-06-15'::timestamptz < valid_to);
tstzrange（生成列）の@>演算子で書く場合。
SELECT price
FROM product_prices
WHERE product_id = 1
AND valid_period @> '2024-06-15'::timestamptz;
price
--------
1000.00
(1 row)
@>は「範囲が値を含む」という演算子である。valid_from/valid_to方式の2条件と論理的に等価だが、読みやすさと、GiSTインデックスをそのまま使える点で有利になる。どちらを採用するかはチームの方針の問題であり、片方を選んだらクエリ全体で書き方を混在させないことが大切である。
orders.statusは次の6状態を取り、遷移できる経路は共通スキーマの定義どおり決まっている。
(start) --> pending --> paid --> shipped --> delivered [終端]
| |
| +--> refunded [終端]
+--> cancelled [終端]
同じことを遷移可否の表にすると次のようになる。
| 現在の状態 | 遷移先 | 可否 |
|---|---|---|
| pending | paid | ○ |
| pending | cancelled | ○ |
| pending | shipped | ×（pendingから直接shippedにはならない） |
| paid | shipped | ○ |
| paid | refunded | ○ |
| paid | pending | ×（後戻りしない） |
| shipped | delivered | ○ |
| shipped | cancelled | ×（発送後のキャンセルは別業務） |
| delivered | （どこへも） | ×（終端状態） |
| cancelled | （どこへも） | ×（終端状態） |
| refunded | （どこへも） | ×（終端状態） |
delivered・cancelled・refundedはそこから先に遷移しない終端状態である。
なぜCHECK制約だけでは守れないか。CHECK (status IN ('pending','paid',...))のように取りうる値の集合は制限できるが、「pendingからしかpaidになれない」という遷移の可否はUPDATE前後の値を比較しないと判定できない。CHECK制約は1行の値しか見ないため、これは表現の対象外になる。
これを守る方法は2つあり、実務では併用するのが基本になる。
1. UPDATE文のWHERE句に現在状態を条件として入れる
UPDATE orders SET status = 'shipped'
WHERE id = 12345 AND status = 'paid';
影響行数が0件なら、「その注文がそもそもpaidではなかった」か「他のトランザクションが先に状態を変えた」ことを意味する。PostgreSQLのUPDATEは対象行にロックをかけて処理するため、2つのトランザクションが同時に同じ注文をpaidから別の状態へ遷移させようとしても、先に確定した方だけが成功し、後発のUPDATEはコミット後にWHERE句を再評価して0件になる。アプリ側は影響行数を見て遷移の成否を判定する。
2. トリガで遷移表を強制する
トリガとは、指定したテーブルへの挿入・更新・削除に反応して、DBが自動的に指定の関数を実行する仕組みである。
更新前に割り込むBEFOREトリガでは、関数の中で更新前の行をOLD、更新後の行をNEWという名前で参照でき、
RAISE EXCEPTIONで処理を中止できる。CHECK制約と違ってOLDとNEWを突き合わせられるため、遷移の可否を判定できる。
CREATE OR REPLACE FUNCTION check_order_status_transition()
RETURNS trigger AS $$
BEGIN
IF OLD.status = NEW.status THEN
RETURN NEW; -- statusを変えない更新は許可
END IF;
IF (OLD.status, NEW.status) NOT IN (
('pending', 'paid'),
('pending', 'cancelled'),
('paid', 'shipped'),
('paid', 'refunded'),
('shipped', 'delivered')
) THEN
RAISE EXCEPTION 'invalid order status transition: % -> %', OLD.status, NEW.status;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_status_transition
BEFORE UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION check_order_status_transition();
不正な遷移を試みるとエラーになる。
UPDATE orders SET status = 'delivered' WHERE id = 12345; -- 現在pendingだったとする
ERROR: invalid order status transition: pending -> delivered
CONTEXT: PL/pgSQL function check_order_status_transition() line 14 at RAISE
トリガは、アプリのコードパスを経由しない直接のSQL実行（バッチ処理、他チームのスクリプト、手動修正など）からもDBを守る。一方でアプリ層のバリデーションは、ユーザーへのエラーメッセージや業務フロー（在庫確保のタイミングなど）を担う。トリガとアプリ層は代替ではなく併用が基本であり、トリガは「最後の砦」、アプリ層は「業務ロジックの本体」と位置づけるとよい。
論理削除は、行を物理的にDELETEせずdeleted_at timestamptz列（NULL＝生存、非NULL＝削除済み）を立てて「見えなくする」設計である。
ALTER TABLE customers ADD COLUMN deleted_at timestamptz;
利点は明確である。他テーブルからの外部キー参照が残っていても壊れない（削除した顧客のorders履歴は参照可能なまま）。誤削除からの復旧が容易。監査証跡が残る。
一方で「伝染コスト」がある。deleted_at列を持つテーブルを参照するすべてのSELECT・JOINにWHERE deleted_at IS NULLを書き忘れなく足す必要があり、1箇所でも忘れると、削除済みのはずの顧客が集計やUI一覧に出てくる。さらにUNIQUE制約は削除済み行も含めて評価されるため、email UNIQUEな顧客テーブルで論理削除した顧客と同じメールアドレスで再登録しようとすると、削除済みのはずの行と衝突してエラーになる。
緩和策は2つある。
部分インデックスで、生存行だけにUNIQUE制約を絞る。部分インデックスとは、WHEREで条件を付けて
「その条件に合う行だけ」を載せた索引である（索引としての性質は第7回で扱う）。
一意インデックスに条件を付ければ、一意性の検査対象も同じ範囲に絞れる。
ALTER TABLE customers DROP CONSTRAINT customers_email_key;
CREATE UNIQUE INDEX customers_email_active_key
ON customers (email) WHERE deleted_at IS NULL;
これで削除済み行のメールアドレスは再利用可能になる。
UPDATE customers SET deleted_at = now() WHERE id = 1;
INSERT INTO customers (email) VALUES ('a@example.com'); -- id=1と同じメールでも成功する
ビューで「生存行だけ」を既定にする。ビューとは、名前を付けて保存したSELECT文であり、
参照する側からはテーブルと同じように扱える（実データは持たず、参照のたびに元のSELECTが実行される）。
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;
アプリのクエリの起点をこのビューにしておけば、WHERE deleted_at IS NULLの書き忘れをビュー定義側に一本化できる。ただしJOINが絡む複雑なクエリではビューだけでは防ぎきれない場合があり、根本的には「本当に論理削除が必要か」を都度疑うべきである。他テーブルから参照されず、単に一覧から消したいだけなら、物理DELETEしてアーカイブテーブルに移す設計の方が伝染コストを避けられる。
order_itemsは「注文×商品」の多対多関係を表す中間テーブルであり、qtyやunit_priceという関係そのものに属する属性を持つ。
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id),
product_id bigint NOT NULL REFERENCES products(id),
qty int NOT NULL CHECK (qty > 0),
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id)
);
qtyやunit_priceは「注文」の属性でも「商品」の属性でもない。「この注文でこの商品を何個いくらで買ったか」という関係の属性であり、これを持たせられる場所は中間テーブルしかない。これが多対多を「実体化する（中間テーブルとして具体的な行にする）」理由である。
関係に属性がない単純な多対多、たとえば商品とタグの「タグ付け」であっても、products.tag_ids integer[]のような配列列で済ませるのはアンチパターンである。外部キー制約が効かずタグ削除時に整合性が崩れる、インデックスでの絞り込みが素直にできない、「このタグを持つ商品を全部」のような集合演算がSQLとして書きにくい、という欠点がある。多対多は関係に属性があってもなくても中間テーブルで実体化するのが基本線であり、配列列は検索に使わない軽い表示用途などに限定して使う。
非正規化とは、正規化で分離した値を意図的に重複させて持つことである。典型例は集計列のキャッシュで、たとえば「顧客ごとの累計購入額」を都度order_itemsからSUMで計算する代わりに、customers.total_spentのような列にキャッシュしておく設計を指す。
非正規化は無料ではない。元データ（order_items）が変わるたびにキャッシュ列を更新しないと値がずれる。更新方法は主に2つある。
| 方式 | 整合性 | コスト | 向く場面 |
|---|---|---|---|
| トリガで都度更新 | 高い（即時） | 書き込みのたびにトリガが走り、書き込み遅延・ロック競合が増える | 参照頻度が非常に高く、多少の書き込み遅延を許容できる |
| バッチで定期更新 | 低い（遅延あり） | 実装が単純。ただし更新間隔の分だけ値が古くなる | 「だいたい合っていればよい」ダッシュボード的な集計 |
判断基準は次の3点で考える。(1) 読み取り頻度が書き込み頻度に対して圧倒的に高いか。(2) 元テーブルの集計コストが実際にボトルネックになっているか（第8回でEXPLAINを使って確かめる）。(3) 多少のズレを業務上許容できるか。正規化のままJOINやSUMで十分な速度が出るなら非正規化は不要であり、非正規化は「測って初めて選ぶ」最適化である。
product_pricesにtstzrangeの生成列とEXCLUDE制約を追加し、次の2点を確認する。
valid_from/valid_toでINSERTするとエラーになること想定解答
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE product_prices
ADD COLUMN valid_period tstzrange
GENERATED ALWAYS AS (tstzrange(valid_from, valid_to, '[)')) STORED;
ALTER TABLE product_prices
ADD CONSTRAINT product_prices_no_overlap
EXCLUDE USING gist (product_id WITH =, valid_period WITH &&);
-- 1. 商品id=1の「現在も有効」な行(valid_to IS NULL)と重なる期間でINSERTしてみる
INSERT INTO product_prices (product_id, price, valid_from, valid_to)
SELECT product_id, 999, valid_from + interval '1 day', valid_to
FROM product_prices
WHERE product_id = 1 AND valid_to IS NULL;
-- => ERROR: conflicting key value violates exclusion constraint "product_prices_no_overlap"
-- 2. ある日時の価格
SELECT price
FROM product_prices
WHERE product_id = 1
AND valid_period @> '2024-06-15 00:00:00+09'::timestamptz;
price
--------
2480.00
(1 row)
EXCLUDE制約はproduct_id単位で区間の重なりを見るため、他の商品の期間とは無関係に判定される。@>演算子はGiSTインデックスがあればインデックススキャンで評価され、valid_from/valid_toの2条件と比べても実行計画上素直になりやすい（実行計画の読み方は第8回で扱う）。
状態遷移図（pending→paid→shipped→delivered、pending→cancelled、paid→refunded）に従い、ordersテーブルで不正な状態遷移をDB側で防ぐトリガを実装し、pendingからdeliveredへ直接遷移させようとするとエラーになることを確認する。
想定解答
本編のcheck_order_status_transition関数とtrg_order_status_transitionトリガをそのまま作成したうえで、次のように確認する。
-- テスト用にpending状態の注文を1件作る
INSERT INTO orders (customer_id, status, ordered_at)
VALUES (1, 'pending', now())
RETURNING id;
-- => idが返る。以下 :id をこの値に読み替える
-- 不正遷移を試す
UPDATE orders SET status = 'delivered' WHERE id = :id;
ERROR: invalid order status transition: pending -> delivered
-- 正しい経路なら通る
UPDATE orders SET status = 'paid' WHERE id = :id;
UPDATE orders SET status = 'shipped' WHERE id = :id;
SELECT id, status FROM orders WHERE id = :id;
id | status
----+---------
1 | shipped
(1 row)
トリガはOLD.statusとNEW.statusの組を許可リストと照合するだけなので、遷移経路の追加・変更は許可リストに1行足すだけで済み、業務ルールの変更に追従しやすい。
| 症状 | 原因 | 対処 |
|---|---|---|
全SELECTにWHERE deleted_at IS NULLを書き続けて疲弊する、または書き忘れて削除済みデータが出る | 論理削除の伝染コストを見積もらずに導入した | 生存行だけを返すビューを起点にする、または部分インデックスで代替する。他テーブルから参照されないなら物理削除+アーカイブを検討する |
| 「現在価格」と「価格履歴」の値がずれる | products.priceとproduct_pricesを別々に更新し、片方だけ更新し忘れる | 現在価格をproducts.priceに二重管理せず、product_pricesのvalid_to IS NULLの行から都度取得する（ビュー化してもよい）。二重管理するならトリガで同期する |
| 同じ商品の価格改定が重複してINSERTされてしまう | valid_from/valid_toだけではDBが重複を拒否できない | EXCLUDE USING gist制約を張る（本編・課題1参照） |
| CHECK制約でstatusの遷移を守ろうとして書けない、または想定通り機能しない | CHECK制約は1行しか見えず、OLD値と比較できない | トリガ、またはUPDATE文のWHERE句に現在状態を条件として入れる方針に切り替える |
集計列（total_spentなど）の値が実データとずれていく | 非正規化した集計列を更新するトリガ・バッチに漏れがある、または一部の更新経路（バッチ処理など）がトリガを経由していない | 更新経路を1つに絞る、またはトリガで全経路を確実に捕捉する。定期的に実データと突合するバッチ検証を入れる |
EXCLUDE USING gist制約を追加できる@>）で書けるorder_itemsの合計金額をorders.total_amountとしてキャッシュする設計を考え、AFTER INSERT/UPDATE/DELETEトリガで同期する実装を書く。トリガが走った場合と、走らない操作（TRUNCATE order_itemsなど）が起きた場合とで、どうずれるかも確認する。categoriesテーブルにdeleted_at列を追加し、部分インデックスとactive_categoriesビューを作成する。削除済みカテゴリと同名のカテゴリを再作成できることを確認する。本編で触れたEXCLUDE制約やUNIQUE制約の裏側は、B-treeやGiSTといったインデックス構造そのものに支えられている。第7回では、これらのインデックスがディスク上でどう格納され、なぜ範囲検索や重複防止に向いているのかを扱う。また本編で「実行計画は第8回で」と留保した@>演算子の評価方法は、第8回でEXPLAINとともに扱う。