共通列の設計
いつ入った行なのか分からない
運用が始まると、必ず聞かれる質問があります。
この行はいつ入ったのか。最後に更新されたのはいつか。
障害調査でも、不正の調査でも、集計の食い違いの調査でも、最初に見るのが時刻です。ところが設計時に列を用意していないと、答えようがありません。あとから列を足しても、過去の行の値は永久に分かりません。
そこで、全テーブルに標準装備する列を決めます。これを監査列と呼びます。
標準装備する2列
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()created_at は行が生まれた時点、updated_at は最後に書き換えられた時点です。どちらも NOT NULL にできます。行を作る時点で必ず決まるからです。
既定値を書いておく理由
DEFAULT now() を書いておくと、INSERT のときに値を渡さなくても現在時刻が入ります。
これには2つの意味があります。1つは書く側が楽になること。もう1つが重要で、入れ忘れが起きなくなることです。アプリごとに時刻をセットする実装だと、新しく増えた書き込み経路が入れ忘れて NULL になります。既定値は、どの経路から書かれても効きます。
さらに now() はデータベースの時計を使うので、書き込むサーバーの時計がずれていても値がずれません。複数台のアプリサーバーから書き込む構成では、これが効きます。
updated_at は自動では動かない
注意が要るのは updated_at です。DEFAULT は INSERT のときにしか効かないので、UPDATE では自動で更新されません。
更新の方法は次の2つです。
| 方法 | 特徴 |
|---|---|
UPDATE 文に毎回 updated_at = now() を書く | 単純。ただし書き忘れる |
| トリガーで自動更新する | 書き忘れが起きない。仕掛けが1つ増える |
入口が複数あるならトリガー、アプリ1つに閉じているなら UPDATE 文で書く、という判断になります。理由は外部キーのときと同じで、入口の数が判断を決めます。
消した記録も残したいとき
もう1つ、deleted_at timestamptz を足して、行を物理削除せずに時刻を入れる設計があります。論理削除と呼びます。復元できる利点がある一方、すべての SELECT に WHERE deleted_at IS NULL が必要になり、書き忘れると消したはずのデータが表に出ます。全テーブルに機械的に付けるものではなく、消し戻しが業務上あり得る表だけに絞ります。
手を動かす
4つのテーブル全部に監査列を足し、既定値まで含めて確かめます。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id),
ordered_on date NOT NULL
);
期待される出力
| table_name | audit_columns |
|---|---|
| customers | 2 |
| orders | 2 |
| products | 2 |
| stores | 2 |