MariaDBによる時制データベース入門(前置き編)
時制データベースとは
時制データベース(Temporal database) とは時制、時間をデータと共に保存し、過去、現在、未来におけるデータを参照できるようにしたデータベースモデル。
有効時間(Valid time) 、 トランザクション時間(Transaction time) 、 決定時間(Decistion time) などを保存し、これらを組み合わせた、 uni-temporal , bi-temporal , tri-temporal と呼ばれるデータモデルがある。
有効時間は、アプリケーション内で論理的に有効とする期間。
トランザクション時間は、データベース上に記録され、物理的に有効とする期間。
決定時間は、トランザクションがコミットされ、データが確定した時間。
uni-temporal は、有効時間またはトランザクション時間のいずれか1つを保持する。
bi-temporal は、有効時間とトランザクション時間の2つを保持する。
tri-temporal は、有効時間とトランザクション時間、決定時間の3つを保持する。
参考:
- wikipedia Temporal_database https://en.wikipedia.org/wiki/Temporal_database
つまり何ができるのか
データを更新、削除することなく、永続的に挿入し続けて履歴を残しつつ、最新データを真とするデータ構造の設計を求められることがあります。
例えば、金融や物販の購入履歴や取引履歴、医療の治療歴や電子カルテなど一度挿入したデータの改変が許されず、当時の履歴をいつでも見ることが可能とする要件が求められた場合です。
時制データベースはこれらのデータ構造を表現する上で有利になります。
シンプルかつ乱暴に言うと、あるテーブルの、あらゆる過去時点のスナップショットを、自在に見ることができる機能 を簡易に使うことができます。
従来の RDB ではこれらを実現するには相性が悪く、テクニカルなデータ構造と実装が求められました。
例えば、履歴テーブルと最新データを表すテーブルを分けたり、シーケンシャル ID を発行し一意なオブジェクトを定義し、履歴テーブルと最新データを1つのテーブルに同居させるモデルなどが考えられます。前者はアプリケーションからデータの更新作業をされたら、現在の最新データテーブルにある情報を履歴テーブルにコピーし、最新データを削除後に最新データを挿入するような実装が必要となります。後者も SQL クエリで容易に検索するには Window 関数などを駆使することが求められるかもしれません。
また、その際には関連するマスタにも配慮する必要があります。
例えば、ある商品の名称が商品マスタにあり、購入履歴を持つ場合、商品マスタは更新され続けても当時の商品名称で表示する必要があれば、商品マスタも履歴化するか、正規化を崩して購入履歴に名称も持たせるかなどです。
こうして従来の RDB の機能でやり切ろうとした場合、システム自体が比較的小規模だったり、適用する範囲が限定的であれば問題になることは少ないかもしれないですが、システム規模が大きくなったり、適用範囲が広がってくると煩雑で苦しくなっていきます。
それらの履歴、世代管理的なところをデータベースシステム自体の機能として実装させ、データベース利用者からのデータ利用を簡便に行えるようにする仕組みが時制データベースモデルであり、標準 SQL 規格においても SQL:2011 として主要な新機能として時制データベースの改善として、application time period tables や system-versioned tables が、有効時間やトランザクション時間を活用できるように採用されています。
参考:
- wikipedia SQL:2011 https://en.wikipedia.org/wiki/SQL:2011
RDB上での履歴を持ったデータ構造について先人たちの検討や最適解
こちらはご参考まで。 Tempral databse を使わない場合の先人達の知恵です。
- 変更履歴を持つテーブルの設計 https://qiita.com/ak-ymst/items/2e8e92f212c807bb09a1
- データベース設計 〜 マスタデータを含めて、全ての履歴を残したいという要望 https://entree.hatenadiary.org/entry/20140607/1402127909
- 履歴を持ったテーブルの設計 https://qiita.com/takanemu/items/d9c52811d70d37b232a9
- 履歴テーブルについて https://user-first.ikyu.co.jp/entry/history-table
- DB データの履歴管理設計 https://knooto.info/software-design-data-revisions/
- Wikipedia が記事の履歴をどのように DB に格納してるか調べてみた http://tech.nitoyon.com/ja/blog/2010/09/24/wikipedia-db-table/
bi-temporal モデルについてもう少し詳しく
有効時間 は、アプリケーション内で論理的に有効とする期間で、開始終了の2つの日時を持ちます。
トランザクション時間 は、データベース上に記録され、物理的に有効とする期間で、開始終了の2つの日時を持ちます。
有効時間のuni-temporal モデル
ある商品マスタがあって、有効時間のみ1軸の uni-temporal としてテーブルを作成した場合の例は以下になります。
データ操作1
現在日時: 2022-05-01 00:00:00 時点
商品1を挿入した
| シーケンスID | 商品ID | 商品名 | 有効開始日時 | 有効終了日時 |
|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00 | 9999-12-31 23:59:59 |
データ操作2
現在日時: 2022-05-10 00:00:00 時点
商品1を三日前(2022-05-07)から商品2だったと遡及して変えた
| シーケンスID | 商品ID | 商品名 | 有効開始日時 | 有効終了日時 |
|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00 | 2022-05-07 00:00:00 |
| 1 | 1 | 商品2 | 2022-05-07 00:00:00 | 9999-12-31 23:59:59 |
以上のような遍歴のデータがあり、
現在日時: 2022-05-02 00:00:00 ときに 商品ID:1 を検索したならば、結果は、
商品名: 商品ID:1, 商品1, 有効開始日時: 2022-05-01 00:00:00, 有効終了日時: 2022-05-07 00:00:00
となっていたはずです。
現在日時: 2022-05-08 00:00:00 ときに 商品ID:1 を検索したならば、結果は、
商品名: 商品ID:1, 商品1, 有効開始日時: 2022-05-01 00:00:00, 有効終了日時: 2022-05-07 00:00:00
となっていたばずです。
現在日時: 2022-05-11 00:00:00 ときに 商品ID:1 を検索したならば、結果は、
商品名: 商品ID:1, 商品2, 有効開始日時: 2022-05-07 00:00:00, 有効終了日時: 9999-12-31 23:59:59
となっていたはずです。
つまり、データベース上では最終的に、
2022-05-01 00:00:00 以降から 2022-05-07 00:00:00 未満 までは商品1
2022-05-07 00:00:00 以降から 9999-12-31 23:59:59 未満 までは商品2
となっているように見えますが、
三日遡及して更新したデータのため、現実世界では、
2022-05-01 00:00:00 以降から 2022-05-10 00:00:00 未満 までは商品1
2022-05-10 00:00:00 以降から 9999-12-31 23:59:59 未満 までは商品2
として検索されていたことになり、乖離が生まれます。
そして、この三日間の乖離は更新以降に再現させることはできません。
有効時間のuni-temporal モデルでは遡及したことによるデータの乖離を表現することができません。
トランザクション時間の uni-temporal モデル
トランザクション時間は、RDB利用者から直接変更することはできません。適宜、データベースシステム側で自動的に打刻されます。
先ほどと同じく商品マスタがあって、トランザクション時間のみ1軸の uni-temporal としてテーブルを作成した場合の例は以下になります。
※トランザクション時間はトランザクション時間開始終了日時とするとbegin～commitのようなトランザクション処理の開始終了日時と紛らわしいので row_start, row_end とします。
データ操作1
現在日時: 2022-05-01 00:00:00 時点
商品1を挿入した
| シーケンスID | 商品ID | 商品名 | row_start | row_end |
|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
データ操作2
現在日時: 2022-05-10 00:00:00 時点
商品1が三日前(2022-05-07)から商品2だったので変更した
| シーケンスID | 商品ID | 商品名 | row_start | row_end |
|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00.000000 | 2022-05-10 00:00:00.000000 |
| 2 | 1 | 商品2 | 2022-05-10 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
以上のような遍歴のデータになりますが、トランザクション時間の uni-temporal モデルはアプリケーション上の有効期間という概念がないので、過去に遡及したり、未来に予定したデータを作ることができません。
トランザクション時間を使用すると、検索する際にその日時時点だったらとする基準日時を付与することが出来るようになります。
基準日時を付与せずに検索すると、最新レコードのみが表示され、履歴レコードはユーザから隠ぺいされます。
基準日時を付与することで過去に遡って検索することが出来るようになります。
リアル世界の現在日時が 2022-05-10 00:00:00 だとしても、基準日時を 2022-05-09 00:00:00 としたならば、商品1を検索することができます。
トランザクション時間はユーザからの変更を禁じているため、履歴としての整合性は保たれます。
有効時間とトランザクション時間両方の bi-temporal モデル
有効時間とトランザクション時間両方の bi-temporal モデルは、上述の uni-temporal の欠点を補うことができます。
同じく商品マスタがあって、bi-temporal としてテーブルを作成した場合の例は以下になります。
データ操作1
現在日時: 2022-05-01 00:00:00 時点
商品1を挿入した
| シーケンスID | 商品ID | 商品名 | 有効開始日時 | 有効終了日時 | row_start | row_end |
|---|---|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00 | 9999-12-31 23:59:59 | 2022-05-01 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
データ操作2
現在日時: 2022-05-10 00:00:00 時点
商品1を三日前(2022-05-07)から商品2だったと遡及して変えた
| シーケンスID | 商品ID | 商品名 | 有効開始日時 | 有効終了日時 | row_start | row_end |
|---|---|---|---|---|---|---|
| 1 | 1 | 商品1 | 2022-05-01 00:00:00 | 9999-12-31 23:59:59 | 2022-05-01 00:00:00.000000 | 2022-05-10 00:00:00.000000 |
| 2 | 1 | 商品1 | 2022-05-01 00:00:00 | 2022-05-07 00:00:00 | 2022-05-10 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
| 3 | 1 | 商品2 | 2022-05-07 00:00:00 | 9999-12-31 23:59:59 | 2022-05-10 00:00:00.000000 | 2038-01-19 03:14:07.999999 |
以上のような遍歴のデータになります。
システム時間に適用される基準日時を指定しなければ、有効時間のuni-temporal モデルと同じですが、
基準日時を指定すると、有効時間のuni-temporal モデルでは再現させられなかった乖離しているデータが再現できるようになります。
つまり、たとえば、基準日時を 2022-05-09 00:00:00 としたならば、アプリケーション上の有効期間の2022-05-01 00:00:00 から 9999-12-31 23:59:59 の間、商品1が検索されます。
基準日時を 2022-05-10 00:00:00 以降のどこかとしたならば、
2022-05-01 00:00:00 以降から 2022-05-07 00:00:00 未満 までは商品1
2022-05-07 00:00:00 以降から 9999-12-31 23:59:59 未満 までは商品2
となります。
時制データベースの実装状況
詳しくは先ほど参考に上げた wikipedia にあるので参照して頂きたいのですが、この記事の初稿執筆時現在で一部を掻い摘んで述べると、
- MariadDB
- Oraclue
- DB2
はすでに SQL:2011 相当の bi-temporal 機能を実装済みの様です。
PostgeSQL は、次のブログ記事によると PostgeSQL 15 で採用が検討されているようです。
https://blog.dbi-services.com/temporal-tables-for-postgresql-15/
このブログ記事でリンクされている PostgeSQL のコミッタ達のMLや、コミットステータスを見ると Temporal database 提案パッチの取り込み検討や議論が止まっているようにも見えます。
この記事の初稿執筆時現在では、アプリケーションに時制データベースを検討するなら、多くのプロジェクトにとってオープンソースである MariaDB が最も低い初期コストで採用しやすいと思われます。
MariaDB の Temporal table 実装
MariaDB では version 10.3.4 から、 SQL:2011 標準の "System-Versioned Tables" が実装されました。
これによって、有効時間かトランザクション時間のどちらかの uni-temporal モデルか、有効時間とトランザクション時間両方の bi-temporal モデルを選択できるようになりました。
実践編で MariaDB を実際に動かしながら詳細を見ていきます。
あとがき
MariaDB の Temporal table という機能はライバルの MySQL や PostgreSQL に対してすごいアドバンテージだと思うのですが、今のところ、日本語で書かれた記事は数えるほどしかみあたらず。もっと推し機能として世に出て欲しいです。がんばって記事書く。