📚
RDBにおけるよくある履歴管理のパターンについてまとめてみた
そーだいさんの素晴らしい書籍「失敗から学ぶRDBの正しい歩き方」を読んでいたら
履歴管理について一回まとめたくなったのでまとめた話です。
失われた事実問題
RDBって時間軸と直交する設計が大事。
過去の事実を上書きすると、過去の事実が失われる。
はい⋯わかります⋯（反省中）これは、あるあるですね⋯
どんな問題が起きるか
-
税率マスタを更新しちゃったケース
- 消費税率を5%→8%→10%って変更したとき、過去の注文データの税率が全部10%になっちゃう
- 過去の請求書を再発行したら金額が変わっちゃうとか、やばい
-
商品マスタの商品名を変更しちゃったケース
- 商品コードはそのままで商品名だけ変えると、過去の注文履歴も新しい商品名になっちゃう
- 「当時何を注文したか」っていう事実が消える
-
社員マスタの部署を更新しちゃったケース
- 異動履歴が残らないから、過去のプロジェクトでどの部署が担当してたか分からなくなる
-
過去の集計値が出せないケース
- グループごとの売上を集計したい。でもこのグループは期の途中で再編成されている。
- 再編成された履歴が残っていないので、過去の値を集計することができない。
RDBの履歴管理パターンをまとめてみた
1. バージョン管理パターン
各バージョンを独立したレコードとして保存する方法です。
CREATE TABLE documents (
document_id INT,
version INT,
title VARCHAR(255),
content TEXT,
created_at TIMESTAMP,
PRIMARY KEY (document_id, version)
);
どんなときに使うか
- ドキュメント管理システムとか？
- 契約書のバージョン管理とか？
- 仕様書の改訂履歴とか？
2. イミュータブルデータモデル
一度書き込んだデータは変更しないで、変更は新しいレコードで表現するってやつです。
UPDATEを極力排除するのがポイント。
-- 純粋なイミュータブルデータモデル
CREATE TYPE operation_type AS ENUM ('CREATE', 'UPDATE', 'DELETE');
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_code VARCHAR(50),
product_name VARCHAR(255),
price NUMERIC(10, 2),
operation_type operation_type,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 最新データの取得
SELECT * FROM products
WHERE product_code = 'P001'
AND operation_type != 'DELETE'
ORDER BY created_at DESC
LIMIT 1;
注意点
- 最新データ取得のパフォーマンスが気になる場合は、テンポラルデータモデリングを検討したほうがいいかも
どんなときに使うか
- 監査ログが必要なシステム
- 金融取引システム？
- 医療記録システム？
3. テンポラルデータモデリング（有効期間管理）
各レコードに有効期間を持たせる方法です。時間軸を明示的に管理するのがポイント。
CREATE TABLE tax_rates (
tax_rate_id SERIAL PRIMARY KEY,
rate NUMERIC(5, 2),
start_date DATE NOT NULL,
end_date DATE,
CHECK (end_date IS NULL OR start_date < end_date)
);
-- データ例
INSERT INTO tax_rates (rate, start_date, end_date) VALUES
(5.00, '1997-04-01', '2014-03-31'),
(8.00, '2014-04-01', '2019-09-30'),
(10.00, '2019-10-01', NULL);
特定時点のデータ取得
-- 2018年の税率を取得
SELECT rate
FROM tax_rates
WHERE '2018-01-01' BETWEEN start_date AND COALESCE(end_date, CURRENT_DATE);
ユースケース
- 税率マスタ？
- 料金プラン変更履歴？
- 組織改編履歴？
- 商品価格の改定履歴？
4. 履歴テーブル分離パターン
現在のデータと履歴データを別テーブルで管理する。
-- 現在のデータ
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
updated_at TIMESTAMP
);
-- 履歴データ
CREATE TABLE employees_history (
history_id SERIAL PRIMARY KEY,
employee_id INT,
name VARCHAR(100),
department VARCHAR(100),
valid_from TIMESTAMP,
valid_to TIMESTAMP,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
⚠️ やっちゃだめなパターン：変更日のみを持つ履歴テーブル
履歴テーブルに変更日のみを持つ設計は、大量の履歴があるとかなり辛いです。
-- アンチパターン：変更日のみ
CREATE TABLE employees_history_bad (
history_id SERIAL PRIMARY KEY,
employee_id INT,
name VARCHAR(100),
department VARCHAR(100),
changed_at TIMESTAMP -- 変更日のみ！
);
-- 最新の履歴を取得するには...
SELECT *
FROM employees_history_bad
WHERE employee_id = 123
ORDER BY changed_at DESC
LIMIT 1; -- 全履歴をソートして最後の1件
何が問題か
-
1レコードあたりの履歴が数万件あると辛い
- ある社員の履歴が3万件あったら3万件をソートすることになる
- 複合インデックス
(employee_id, changed_at DESC)があれば最新取得は高速だけど
-
特定時点のデータ取得が困難
- 「2020年1月時点の部署」を取得するには、全履歴を読んで条件判定する必要がある
- 「いつからいつまで有効だったか」が表現できないのも辛い
こうしたほうがいいと思う：valid_fromとvalid_toを持つ
CREATE TABLE employees_history (
history_id SERIAL PRIMARY KEY,
employee_id INT,
name VARCHAR(100),
department VARCHAR(100),
valid_from TIMESTAMP NOT NULL,
valid_to TIMESTAMP,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
CREATE INDEX idx_employee_period ON employees_history(employee_id, valid_from, valid_to);
-- 最新の履歴を高速取得
SELECT *
FROM employees_history
WHERE employee_id = 123
AND valid_to IS NULL; -- インデックスで直接取得
-- 特定時点のデータも高速取得
SELECT *
FROM employees_history
WHERE employee_id = 123
AND '2020-01-01' BETWEEN valid_from AND COALESCE(valid_to, CURRENT_TIMESTAMP);
パフォーマンス比較
| 1社員あたりの履歴数 | 変更日のみ | valid_from + valid_to |
|---|---|---|
| 100件 | 100件をソート | 1件を直接取得 |
| 1万件 | 1万件をソート | 1件を直接取得 |
| 3万件 | 3万件をソート | 1件を直接取得 |
ユースケース
- 社員マスタの異動履歴？
- 顧客情報の変更履歴？
- 設定値の変更履歴？
5. トランザクションへの履歴埋め込み
トランザクションテーブルに、その時点のマスタ情報を埋め込む。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
product_name VARCHAR(255), -- マスタから複製
unit_price NUMERIC(10, 2), -- マスタから複製
tax_rate NUMERIC(5, 2), -- マスタから複製
quantity INT,
order_date TIMESTAMP
);
メリット
- 注文時点の情報が確実に保存される
- マスタ変更の影響を受けない
- 参照が高速（JOINが不要）
デメリット
- 第3正規形に違反
- データの冗長性が高い
ユースケース
- ECサイトの注文履歴？
- 請求書データ？
- 見積書データ？
実践的な設計例：商品マスタと注文
過去の事実を保持する設計
-- 商品マスタ（履歴管理）
CREATE TABLE products (
product_id INT,
start_date DATE NOT NULL,
end_date DATE,
product_name VARCHAR(255) NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL,
sort_order INT,
CHECK (end_date IS NULL OR start_date < end_date),
PRIMARY KEY (product_id, start_date)
);
-- 注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
order_date DATE,
quantity INT
);
-- 注文時点の商品情報を取得
SELECT
o.order_id,
o.product_id,
p.product_name,
p.unit_price,
o.quantity,
p.unit_price * o.quantity AS total_price
FROM orders o
LEFT JOIN products p ON (
o.product_id = p.product_id
AND o.order_date BETWEEN p.start_date AND COALESCE(p.end_date, CURRENT_DATE)
);
商品情報を更新する関数
CREATE OR REPLACE FUNCTION update_product(
p_product_id INT,
p_current_start_date DATE,
p_new_start_date DATE,
p_new_price NUMERIC(10, 2),
p_new_name VARCHAR(255)
)
RETURNS VOID AS $$
BEGIN
-- 現在のレコードの終了日を設定
UPDATE products
SET end_date = p_new_start_date - INTERVAL '1 day'
WHERE product_id = p_product_id
AND start_date = p_current_start_date;
-- 新しいレコードを追加
INSERT INTO products (product_id, start_date, end_date, product_name, unit_price)
VALUES (p_product_id, p_new_start_date, NULL, p_new_name, p_new_price);
END;
$$ LANGUAGE plpgsql;
履歴管理の注意点
パフォーマンスへの影響
- レコード数が増加しテーブルサイズが大きくなる
- 集計クエリが複雑になり遅くなる
- インデックス設計が重要
対策
- 適切なインデックスの設定（start_date, end_date）
- パーティショニングの活用
- マテリアライズドビューの利用
- 古い履歴のアーカイブ化
実装の複雑さ
- JOINの条件が複雑になる
- アプリケーションコードが煩雑になる
対策
- Viewで最新データを抽出
- ORMのスコープ機能を活用
- 共通処理の関数化
データ整合性の維持
- 期間の重複や隙間が発生しないようにする
- CHECK制約やトリガーで制御
RDB外での履歴管理の重要性
履歴管理の要件がない場合でも
RDB上で明示的な履歴管理の要件がない場合でも、何が起きたかの事実を消さないことは重要です。システム障害時の復旧、不正アクセスの調査、ビジネス分析など、後から履歴が必要になるケースは多々あります。
CDC（Change Data Capture）とデータレイク
RDBに履歴テーブルを持たない場合でも、CDC（Change Data Capture） を使って変更データをキャプチャし、データレイクやデータウェアハウスに保存する方法があります。
CDCの主な方式
- ログベース: トランザクションログから変更を検知
- タイムスタンプベース: updated_at列で変更を検知
-- タイムスタンプベースの例
SELECT * FROM products
WHERE updated_at > '2024-01-01 00:00:00' -- 前回取得時刻
ORDER BY updated_at;
CDCツールの例
- Debezium + Apache Kafka
- AWS DMS（Database Migration Service）
- Fivetran
データレイクへの差分保存
RDBの変更データを定期的にデータレイク（S3、Azure Data Lake、GCSなど）に保存することで：
- RDBのパフォーマンスに影響を与えない
- 低コストで大量の履歴を保存可能
- 後から分析や機械学習に活用できる
[RDB] → [CDC] → [データレイク]
↓
[分析・監査]
メリット
- RDBへの負荷軽減: 履歴テーブルを持たないため、本番DBのパフォーマンスが維持される
- 低コスト: データレイクのストレージコストはRDBより大幅に安い
- 柔軟な分析: 後から予期しない分析要件にも対応可能
- 監査証跡: 何が起きたかの事実が残る
＊ただし、CDCによる履歴管理は結果整合性となることが基本となるかと思うので要検討
まとめ
RDBで履歴管理をするときに考えたいこと：
-
ユースケースを明確にする
- 何のために履歴が必要か（監査、分析、復元）
- どれくらいの頻度で履歴を参照するか
-
パフォーマンスとのトレードオフを考える
- 全てのテーブルで履歴管理する必要はない
- 必要なテーブルに絞って実装するのが吉
-
時間軸を意識した設計をする
- 過去の事実を上書きしない
- 未来の変更を事前登録できる仕組みにする
過去の事実を失わないための履歴管理は、システムの信頼性と監査性を高める重要な要素。
Discussion