Kazuhoさんの論理削除はなぜ「筋が悪い」かを読んで。

UPDATEが発生しないテーブルならば、削除フラグを使った実装手法でも現在の状態と更新ログを別々に表現でき、結果として効率と過去の情報を参照できるメリットを簡潔に両立できるのではないか、という話。

大前提として全く同意なのだけども、今あるテーブルにdeleted_atを足すだけで、過去のレコードを復旧可能なようにしたい＞＜ みたいに思っちゃった僕のような人間が実際に取るべき実装手法は何か、あるいは、それを想定して今やっておくべきテーブル設計はどういうものか！？というのが最後の疑問。

まずUPDATEがなければ、immutableなマスタ、更新ログ、「現時点のビュー」の３テーブルは、例えば次のようになる（PostgreSQLの場合）：

-- immutableなマスタ。create table records (  id serial primary key,  another_col uuid not null);-- 更新ログ。deleted_atカラムがある。create table record_delete_events (  record_id int primary key references (records.id),  deleted_at timestamp with time zone not null default now());create index delete_events_deleted_at on record_delete_events (deleted_at);-- 現時点のビュー。マスタから更新ログに含まれるidを取り除いたVIEW。create view live_records asselect * from recordswhere id not exists (select * from record_delete_events where record_id = id);-- 削除する操作。更新ログにINSERTINSERT INTO record_delete_events (record_id) values (1);

削除フラグを使った実装手法でも、まったく同じスキーマを定義できる：

-- immutableなマスタ。deleted_atカラムは無視する。create table records (  id serial primary key,  another_col uuid not null,  deleted_at timestamp with time zone defualt null);create index records_deleted_at on records (deleted_at) where deleted_at is not null;-- 更新ログ。マスタからdeleted_atがセットされたレコードを抽出したVIEW。create view record_delete_events asselect id as record_id, deleted_at from records where deleted_at is not null;-- 現時点のビュー。マスタからdeleted_atがセットされていないレコードを抽出したVIEW。create view live_records asselect id, another_col from records where records where deleted_at is null;-- 削除する操作。UPDATEでdeleted_atをセットUPDATE records SET deleted_at = now() WHERE id=1;

上記のクエリは、live_recordsからprimary key以外の条件でSELECTする際に、部分インデックスrecords_deleted_atが必要になるので最適化が効きにくくなる弊害が残るが、これは次の実装手法で解決できるはず：

-- 現時点のビュー。新しいレコードはここに入れる。create table live_records (  id serial primary key,  another_col uuid not null);-- 更新ログ。削除したレコードはこっちに移す。create table deleted_records (  record_id id primary key,  another_col uuid not null,  deleted_at timestamp with time zone not null default now());create index records_deleted_at on deleted_records (deleted_at);create view record_delete_events asselect record_id, deleted_at from deleted_records;-- immutableなマスタ。liveとdeletedをUNION ALLしたビュー。create view records asselect id, another_col from live_recordsunion allselect record_id as id, another_col from deleted_records;-- 削除する操作：WITH deleted (  DELETE FROM live_records  WHERE id=1  RETURNING *)INSERT INTO deleted_records (record_id, another_col)SELECT id, another_col

ただし、PostgreSQLでは、UPDATEの負荷とDELETE+INSERTの負荷があまり変わらないと仮定する。削除する操作が複雑になっているが、これはFUNCTIONを作っておくことで回避できる：

CREATE FUNCTION delete_record (delete_record_id int not null) as $$WITH deleted (  DELETE FROM live_records  WHERE id=delete_record_id  RETURNING *)INSERT INTO deleted_records (record_id, another_col)SELECT id, another_col$$ language sql;select delete_record(1);

VIEWではなくINHERITでも実装できるはず。おそらくこうなる：

-- immutableなマスタ。親テーブルからのSELECTは、子テーブルのレコードを含むcreate table records (  id serial primary key,  another_col uuid not null);-- 現時点のビュー。子テーブル。create table live_records ()inherits (records);-- 更新ログ。子テーブル。create table deleted_records (  deleted_at timestamp with time zone not null default now())inherits (records);create view record_delete_events asselect id as record_id, deleted_at from deleted_records;

ここで疑問は、

• 効率的にSELECTや更新ができるスキーマを作ろうとすると、VIEWやFUNCTIONなど、側に実装するコードが増えてくる。それらのコードは、上記のようにDB側に実装しても良い（するべき）だろうか？それともアプリケーションに実装するべきだろうか？
• WebサービスとかTreasure Dataのようなサービス事業者だと、テーブルを最初に設計するときにあまり時間をかけられないので、後から見直して最適化していくことが多い。テーブルのスキーマは停止時間なしで変更する手法をいくつか思いつくが（PostgreSQLなら）、上記のレコードを削除する操作などはアプリケーションの変更を伴うので難しい（アプリケーションとDBのスキーマをアトミックに変更できない）。
  • レコードの削除や追加は、どのようなSQLの構文で表現しておくと良いだろうか？全部FUNCTIONにしておくのは煩雑である*1
  • とりあえず論理削除でもいいから、後で何とかしようと思ったときに、後から変更しやすいスキーマ設計（または後から変更できなくて困るタイプのスキーマ設計）は、どんなスキーマだろうか？