Posted inデータベース

SQLのタイムトラベル：MariaDBとPostgreSQLでテンポラルテーブルを活用する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

午前2時の悪夢：データはどこへ消えた？

午前2時14分。ナイトスタンドに置かれたスマートフォンが、本番環境の監視システムからのアラートで激しく振動しています。新人開発者がproductsテーブルに対してUPDATEを実行しましたが、WHERE句を忘れてしまったのです。一瞬にして、カタログ内の全商品の価格が0.00ドルになりました。大規模な季節セールがちょうど4時間後に始まるというのに。

500GBのデータベースをフルリストアするには少なくとも3時間はかかりますし、ポイントインタイムリカバリ（PITR）は、サイドインスタンスの用意やデータ移行を伴う運用上の悩みの種です。私が必要としていたのは、5分前の状態のデータを、そのまま本番環境のシェルで確認する方法でした。ここで、テンポラルテーブル（システムバージョンテーブルとも呼ばれます）が文字通り命の恩人となります。

私はこれまでMySQLからMongoDBまであらゆるものを使ってきましたが、MariaDBとPostgreSQLはSQL:2011テンポラル標準を扱うための最も洗練された方法を提供しています。手動の「audit_logs」や壊れやすいトリガーのことは忘れましょう。これらのエンジンは、すべての行の履歴を自動的に追跡し、データのための組み込みタイムマシンを提供してくれます。

ロジック：なぜバックアップだけでは不十分なのか

標準的なデータベースは刹那的です。それらは「今」の状態しか気にしません。行を更新すると、以前の値は上書きされ、失われます。テンポラルテーブルは、すべてのレコードに対して「有効期間」を追跡することで、この状況を一変させます。すべての変更にタイムスタンプが付与され、履歴内の任意の時点のデータベースに対してクエリを実行できるようになります。

なぜこれが標準的な監査ログよりも優れているのでしょうか？ 3つの実用的な理由は以下の通りです：

  • 即座のフォレンジック監査： テキストログを掘り返すことなく、3週間前に誰が顧客の配送先住所を変更したかを正確に特定できます。
  • ダウンタイムゼロの復旧： サブクエリに以前の状態を直接プルすることで、壊滅的な更新を「元に戻す」ことができます。
  • 正確なトレンド分析： 複雑なデータウェアハウスを構築することなく、前四半期の在庫レベルの変動を比較できます。

はじめに：セットアップとインストール

実装方法はエンジンの選択によって異なります。MariaDBはネイティブな「設定して忘れる（set and forget）」アプローチを提供しています。PostgreSQLはもう少し手動の設定が必要ですが、より高い柔軟性があります。

MariaDB：ネイティブのシステムバージョン管理

MariaDBはバージョン10.3.4から、コアエンジンにシステムバージョン管理を組み込みました。インストールするプラグインも、管理すべき追加の拡張機能もありません。現在のバージョンを使用しているなら、必要なツールはすでに揃っています。

PostgreSQL：柔軟なアプローチ

PostgreSQLには、テンポラルテーブルのための単一の「オン」スイッチはありません。AWS RDSやGoogle Cloud SQLなどのマネージドサービスを利用しているチームの多くは、履歴スキーマとトリガーを組み合わせて使用しています。これにより、セットアップのポータビリティが保たれ、特定のC言語拡張機能への依存を避けることができます。

ローカル環境で作業しており、専用の拡張機能が必要な場合は、サーバー開発パッケージをインストールできます：

# Debian/Ubuntuユーザーの場合
sudo apt-get install postgresql-server-dev-all
# ソースから拡張機能をビルドするか、パッケージマネージャー経由でインストールする

設定：タイムトラベルの有効化

MariaDBの設定

MariaDBでは、これは非常に簡単です。テーブル定義に WITH SYSTEM VERSIONING を追加するだけです。以下は、本番環境向けのproductsテーブルの例です：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2)
) WITH SYSTEM VERSIONING;

すでに稼働中のテーブルに追加する必要がありますか？ その場合はalterコマンドを実行するだけです：

ALTER TABLE products ADD SYSTEM VERSIONING;

MariaDBの非表示列である row_start と row_end が、舞台裏ですべての行のライフサイクルを管理するようになります。これらは SELECT * の結果を散らかすことはありませんが、常にそこに存在しています。

PostgreSQLの設定

Postgresでは、プライマリテーブルをミラーリングするセカンダリの履歴テーブルを作成します。このアプローチは、「ホット」なテーブルを小さく保ち、古いデータを別のストレージ領域に移動できるため、パフォーマンスに優れています。以下は、標準的なトリガーベースのパターンです：

-- 現在のデータ用のメインテーブル
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary INT,
    valid_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP WITH TIME ZONE DEFAULT '9999-12-31'
);

-- 履歴テーブル
CREATE TABLE employees_history (LIKE employees);

-- 古い行をアーカイブするロジック
CREATE OR REPLACE FUNCTION versioning_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        NEW.valid_from = CURRENT_TIMESTAMP;
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_versioning
BEFORE UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION versioning_trigger();

過去を照会する

ここからが投資の回収です。時間をさかのぼって、あの午前2時の危機をどのように解決するか見てみましょう。

MariaDBの場合

MariaDBは非常に読みやすい構文を使用します。もし開発者が午前2時10分にデータベースを壊したなら、午前2時5分時点の価格を一行で確認できます：

SELECT * FROM products 
FOR SYSTEM_TIME AS OF '2023-10-27 02:05:00';

特定の商品（商品ID 42など）のすべての価格変更を確認し、エラーをチェックするには：

SELECT name, price, row_start, row_end 
FROM products FOR SYSTEM_TIME ALL 
WHERE id = 42 
ORDER BY row_start ASC;

PostgreSQLの場合

手動設定の場合、UNION を使用して現在と履歴の両方のテーブルを照会します。少し冗長ですが、非常に明示的です：

-- 従業員101の昨日の正午時点の給与は？
SELECT * FROM employees_history 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to
UNION ALL
SELECT * FROM employees 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to;

メンテナンス：肥大化の管理

テンポラルテーブルは急速に肥大化します。UPDATE が実行されるたびに、履歴に新しい行が作成されるからです。更新頻度の高いテーブルがある場合、ストレージはあっという間に枯渇します。かつて、バージョン管理を有効にしたログテーブルが、わずか7日間で1TBのNVMeストレージを使い果たしたのを見たことがあります。

MariaDBでは、古いデータを簡単に削除（プルーニング）できます：

-- 30日より古い履歴を削除
DELETE FROM products FOR SYSTEM_TIME BEFORE (NOW() - INTERVAL 30 DAY);

PostgreSQLの場合は、pg_cron ジョブを設定して、_history テーブルから行を移動または削除します。これにより、プライマリデータベースを軽量に保ち、クエリの高速性を維持できます。

結論

テンポラルテーブルは単なるニッチなSQL機能ではありません。それはあなたの保険です。シンプルさと標準準拠の点ではMariaDBが勝者です。PostgreSQLはカスタマイズ性に優れており、履歴を安価なボリュームや別のテーブルスペースに保存できます。次にスクリプトが暴走しても、パニックにならないでください。ただ過去を照会して、また眠りにつけばいいのです。

Share:
Tags: