ハクソク

世界を動かす技術を、日本語で。

ソフトデリートの課題

125日前原文(atlas9.dev)

概要

  • ソフトデリート 設計の複雑さと落とし穴についての考察
  • archived_atカラム 方式の問題点と運用上のリスク
  • アーカイブ方法の代替案 （アプリイベント、トリガー、WALベースCDCなど）の紹介
  • 各方式のトレードオフ と運用負担についての比較
  • 新規プロジェクトへの推奨策 としてトリガー方式を提案

ソフトデリート設計の課題

  • 多くのソフトウェアプロジェクトで ソフトデリート （deletedフラグやarchived_atカラム）を実装
  • 顧客が誤ってデータを削除しても 復元が容易 になり、カスタマーサポートの負担軽減
  • 監査やコンプライアンス 目的でアーカイブデータの保持が必要な場合も多い
  • archived_atカラム方式は クエリや運用、アプリケーションコード に複雑さが波及
  • 実際の復元は稀で 大半のアーカイブデータは読まれない ため、テーブルに大量の「死んだデータ」が蓄積
    • アクセスパターンによっては データ量が膨大 になりうる
  • APIやTerraformとの連携不備で 不要なレコード再作成が繰り返され、死んだ行が増大
  • ストレージコストは安価 だが、アーカイブデータの扱いを放置しやすい
  • バックアップ復元時 に死んだデータが障害となり、復元や再構築に時間がかかるリスク

archived_atカラム方式の運用上の問題

  • クエリや運用作業の複雑化
    • 常にアーカイブ済みデータを除外する必要
    • インデックス設計の工夫が必要
    • 手動クエリや分析時のクエリが冗長化
    • アーカイブデータの誤混入リスク
  • 関連テーブルやマッピングテーブル が絡むと複雑さが増大
  • マイグレーション時 に古いアーカイブデータへの対応が難しい
  • 復元処理 がAPIの通常作成処理と乖離しやすく、不具合の温床
    • 標準API経由での復元に統一することで運用の簡素化が可能

代替案1：アプリケーションレベルのアーカイブ

  • レコード削除時に アプリケーション層でイベント発行 （例：SQSへ送信、S3へアーカイブ）
  • 主DBとアプリケーションコードが単純化
  • 非同期処理で外部リソースのクリーンアップも容易
  • JSON形式でデータを柔軟に保存可能
  • トレードオフ：
    • アプリケーションコードのバグ でアーカイブ漏れが発生しやすい
    • インフラ構成が複雑化 （複数サービス・メッセージキューが必要）
    • S3上のアーカイブ検索が困難 でカスタマーサポート用の追加ツールが必要

代替案2：トリガーによるアーカイブ

  • 削除前にトリガーでアーカイブテーブルへコピー
  • アーカイブテーブルは 汎用JSONカラム で設計可能
  • サンプルテーブル・トリガー関数例
    • archiveテーブル：id, table_name, record_id, data, archived_at, caused_by_table, caused_by_id
    • トリガーで 削除元テーブル名やID、削除理由を記録
  • 外部キーのカスケード削除対応
    • セッション変数で 親子関係の削除理由 を伝播
  • トレードオフ：
    • 削除時のパフォーマンス低下 （トリガーの分だけオーバーヘッド）
    • アーカイブテーブルの肥大化
    • しかし、 本番テーブルはシンプルなまま運用可能
    • アーカイブの削除やパーティション分割も容易
    • クエリやインデックスの効率維持
    • マイグレーションやバックアップも容易

代替案3：WALベースのChange Data Capture（CDC）

  • PostgreSQLの WAL（Write-Ahead Log） を利用して変更履歴を外部システムへ転送
  • 代表的なツール： Debezium （Kafka連携）、pgstream、wal2json、pg_recvlogical
  • DELETEイベントのみ抽出し、外部ストレージへ保存
  • トレードオフ：
    • 運用負担が大きい （KafkaやDebeziumの監視・保守が必要）
    • WAL蓄積によるディスク圧迫リスク
      • max_slot_wal_keep_sizeで制御可能
      • レプリケーションスロットの監視・アラートが必須
    • スキーマ変更時の調整が難しい
  • メリット：
    • アプリケーションやDBスキーマを変更せずに運用可能
    • 複数の出力先（S3, Elasticsearch等）に同時転送が可能
    • 本番DBのクエリ負荷が増えない

代替案4：削除を適用しないレプリカ

  • 削除クエリを適用しないPostgreSQLレプリカ のアイデア
  • 過去データのクエリが容易
  • 削除情報の追跡・分離やスキーマ移行時の整合性に課題
  • 運用コストやストレージコスト も無視できない

結論と推奨策

  • 新規プロジェクトで ソフトデリート が必要なら、 トリガー方式 が最適解
    • 本番テーブルのクリーンさ
    • 運用・クエリ・マイグレーションの容易さ
    • 追加インフラ不要
  • より複雑な要件や既存のKafka基盤がある場合は WALベースCDC も検討
  • ご意見・ご質問はatlas9@eabuc.comまで

Hackerたちの意見

どこでもソフトデリートが導入されてる会社で働いたことがあるけど、関係ない内部システムでもそうだったな…文化的なものかもしれない！大学の教授に拡張プロジェクトでソフトデリートを実装してないって怒られたのを今でも覚えてる。彼の言葉では、「ビジネスの世界ではデータは絶対に削除されない！！」ってさ。

└

でも教授は修正については何も言わなかったの？

└

でも…本当にそうなんだ。データを完全に削除するのは、将来の分析を台無しにする簡単な方法だよ。ストレージは安いし、データは絶対に削除しない方がいい。

僕が働いてる業界（銀行）から来てる意見かもしれないけど、逆の考え方を持ってるよ。ソフトデリートの利点はね：* スキーマから明らか：deleted_atカラムがあれば、テーブルを正しくクエリできる（行がDELETEされてないと思ったり、別のテーブルを探す必要がない）。* 一つのやり方：分析クエリや管理ページは同じデータセットを見れるから、歴史的データのために別々の処理をする必要がない。* DELETEは多くのユースケースではあまり発生しないだろうし。* ソフトデリートされた行がパフォーマンスに大きな影響を与えるとは思わない。直感的に言っても、クエリはO log(N)であるべきだからね。* 元に戻すのがすごく簡単で、すべての関係がそのまま残るから、データが他の場所に移動しているわけじゃない（実際には、こういう元に戻す必要はあまり感じてないけど）。ほとんどの場合、さらに進んで行を完全に不変にして、新しい行を使って更新を処理するのが好きだ。これで歴史的データを参照するのがすごく楽になる。もしこの記事で説明されているログアプローチをやっていたら、INSERT/UPDATE/DELETEされた行のコピーを別のテーブルに保持するデータベーストリガーを使うかな。そうすれば、すべて同じデータベースに残るから、クエリも簡単だし、他の場所に複製するのも楽だ。

└

DELETEは多くのユースケースではあまり発生しないだろう その前提を考えると、他のポイントも納得できるよ。50%-70%がソフトデリートされているテーブルを見たことがあるけど、それはパフォーマンスに明らかに影響してた。 > 元に戻すのがすごく簡単 それは元に戻すことが実際に起こるかどうか、削除と復元の行為が監査記録を必要とするかどうかによるね。要するに、ソフトデリートがうまく機能するケースもあれば、そうでないケースもある。導入する前に分析が必要だね。

└

これを試す機会はなかったけど、ポストグレスでソフトデリートされたアイテムを別のドライブに保存するためにテーブルパーティションを使うことを探求したいと思ってた。できるはずだし、パフォーマンス向上にもつながるかもしれない。そうすれば、削除されたアイテムがパフォーマンスにあまり影響を与えることはないだろう。

└

不変のDBセマンティクスを実装してるなら、Datomicや他の代替案を考えてみるのもいいかも。そうすれば、すべてに対してそれが無料で得られるし、さらに素晴らしい機能としてタイムトラベルもついてくる。これで、DBの完全で一貫した状態をいつでも見ることができるんだ！

└

キャリアの全てを通じてデータベースに関わってきたけど、トリガーが大嫌いなんだ。問題は、誰もトリガーをきれいに保つ権限を持っていないこと。結局、トリガーはあらゆる種類の遅いコードのゴミ捨て場になっちゃう。だから、データベースをFirebaseみたいに扱うのはやめて、レコードやフィールドを適当に扱うのはやめてほしい。データベースはビジネスプロセスのストレージとして扱うべきなんだ。ビジネスプロセスはすべてのリクエストの保持を要求するから、レコードをソフトデリートするリクエストも保持する必要があるし、レコードを復元するリクエストも必要だよ。データベースにゴミが多すぎるなら、このレコードはこの日までにアーカイブされるっていうフィールドを作る必要がある。その日になったら、そのレコードを別のテーブルやファイルに移動させて、管理者だけがアクセスできるようにするんだ。もちろん、そのアーカイブの記録も残しておく必要があるよ。リクエストログにゴミが多すぎる？それなら、そのためのアーカイブプロセスも作らなきゃね。これらの原則は新しいものじゃないよ。アメリカの「一般に認められた記録保持の原則」に沿ったものだし、他の国にも似たような基準があるんだ。

└

DELETEは多くのユースケースではあまり多くないと思うけど、ユーザーにもう必要ないものを削除してもらうのが一つの問題だと思う。

└

銀行におけるソフトデリートは、監査可能性というもっと大きな問題へのバンドエイドに過ぎない。ソフトデリートで元の記録を保持できるけど、修正をしないと監査可能性は失われる。正しい方法は、イベントソーシングを使うことだよ。変更はすべてイベントとして記録されるべきで、削除もその一部（イベントとオブジェクトの両方）としてね。パフォーマンスの観点からはさらに問題があるけど、同期やスナップショットはそのためにあるんだ。あるいは、メインテーブルを別のイベントテーブルでバックアップして、定期的に「再構築」を行う方法もあるよ。

└

前の職場（保険会社）のコアシステムは、君が最後に説明した解決策に沿って動いていたよ。各テーブルは、あるオブジェクトに関する時点情報の追加専用ログなんだ。だから、現在の状態は最も高いタイムスタンプの行にあり、過去の状態は適切なフィルターで観察できる。これは本当に強力なアプローチだね。

データベースは事実を保存するもの。レコードを作成すること＝新しい事実。「レコードを削除する」こと＝新しい事実。でも、テーブルから行を消すこと＝消えた事実。それはほとんどの場合、良くないよ。稀にレコードの量が技術的なハードルになることもあるけど、その場合は事実を別のデータベースに移動すればいい。大量の事実を消したいと思ったことは、ほとんどゼロだね。

└

データベースが不変でない限り、レコードが変更されるたびに「消えた事実」が生じるよ。データを削除する正当な理由はいくつもあるから、データを永遠に保持する決定は軽々しく考えちゃいけない。

└

データを漏れたり環境災害を引き起こさないように維持管理コストがかかる潜在的な有害資産として考え始めると、大量の事実を処分したくなる可能性が高くなるよね。

└

同意だね。実際、データストアには主に2つの操作が必要だと思う：取得と挿入。これを実際に機能させるには、データの異なるフェーズごとに異なるタイプのデータストアが必要になるだろうね。残念ながら、データライフサイクルをしっかり理解している人は少ないよ。

Firezoneでは、監査/コンプライアンスログに役立つかもと思ってソフトデリートから始めたけど、すぐにこの記事で説明されている問題にぶつかった。私たちにとっての本当の問題はマイグレーションで、削除されたデータの構造をライブデータと一緒に維持するのは意味がなかったし、不変の監査トレイルの意味を損なってしまった。Postgresを使ってCDCに切り替えたけど、これは別の（複製されていない）書き込み最適化テーブルに出力される。複製接続は、各INSERT/UPDATE/DELETEの監査コンテキストを提供するために「subject」変数を維持している。今のところ、CDCはこの方法で非常にうまく機能している（Elixir / Postgrex）。ソフトデリートにはこの世界での役割があると思うけど、ユーザー向けの「削除を復元」機能くらいかな。コンプライアンスや監査トレイルには向いてないと思う。

└

シンプルなプロジェクトでは、データベースがAPIを通じてしか変更されないから、APIを監査するだけで済むよ。それに、各DB変更を追跡するよりも、表示も保存も簡単だからね。

アーカイブや履歴テーブルがあるのが好きだな。データベースに保存する際にジョブキューでも似たようなことをすることが多い。そうすれば、保留中のテーブルは小さく保てて、削除されたレコードをスキップするためにフルスキャンを避けられるから…それとは別に、イベント駆動のデータベースのために考えている別のアイデアは、sqliteのようなデータベースを使って、イベントやそのデータベースに関連する作業の後に全体をコピー/消去することだ。例えば、投票署名の検証/証拠の情報…それをオンラインやアクティブに持っている意味はあまりないし、他の投票イニシアティブと混ざっている必要もない。アプリに必要に応じてスキーマが変わることもあるし。ただそのファイルをコピーすれば、アーカイブができる。ファイルを圧縮して、必要があればハードアーカイブしてバックアップもできる。

もしかしたら夢を見てるかもしれないけど、ソフトデリートが何らかの組み込みのデータベース機能になってほしいな。テーブルでそれを有効にして、処理方法のいくつかの組み込み戦略を選べるといいよね。ソフトデリートは結構一般的な要望だから、優れたCSやデータベースの専門家たちに、何かアウトオブボックスな機能を開発してもらう価値があると思う。

└

多くのデータウェアハウジングのパラダイム（例：Iceberg、Delta Lake、BigQuery）は、組み込みの「タイムトラベル」を提供していて、時にはスケジュールされたテーブルバックアップと組み合わされているよ。ただ、ソフトデリートを希望するチームの多くは、プラットフォームネイティブの実装を使う代わりに、カスタムアプローチ（通常は普通のSCD）を必要とする他の要件も持っているんだ。

トリガーベースのアプローチが、私の経験上、唯一本当に機能する方法だと思う。アーカイブテーブルは、データに合った形でパーティション分けすればOK。管理するためのルールもいくつかあるよ：パーティションテーブルは追加専用にしないとね。あたりまえだけど、削除からの復旧はアプリケーション層でやる必要がある。アーカイブは歴史的記録としての役割で、運用データストアじゃないからね。それに、何かを復元する必要がある時には、世界が変わっているかもしれない。アプリケーションがそのデータを復元することがまだ意味があるかどうかを確認できる。もし更新を扱う必要があるなら、ソーステーブルでソフトデリートとして扱うべきだよ。トリガーは、古い状態（更新前）をキャッチして、その後は通常通り続ける。アプリケーションは、タイムスタンプでアーカイブレコードを並べることでタイムラインを再構築できる。言うまでもなく、トリガーは操作の前に発火するようにしないとね。消える前の行の状態をキャッチしたいから。トリガーロジックはシンプルに保って、複雑にするとトラフィックが多い時に痛い目に遭うよ。パーティション戦略については、月ごとのパーティションがほとんどのユースケースでうまくいくと思う。ボリュームが少ないなら年ごと、書き込みが多いなら日ごとがいいね。重要なのは、一般的なクエリ（通常は「エンティティXの履歴を見せて」や「日付YとZの間で何が変わった？」）がパーティションの境界に合うようにすることだよ。

eコマースで私たちが戦っている隠れたコストは、DBのストレージやパフォーマンスだけじゃなくて、検索インデックスの汚染なんだ。「在庫あり」を複雑な状態マシン（在庫あり、バックオーダー、廃止されたが表示中、ソフトデリート）として扱っている。これを直接Postgresのクエリに「WHERE deleted_at IS NULL」としてマッピングしようとすると、CRUDには機能するけど、発見には大きな摩擦を生む。厳密なCQRS/デカップリングがこれをスケールさせる唯一の方法だとわかったよ。運用DBには監査や整合性のためにソフトデリートを保持させるけど、検索インデックスは現在購入可能なものだけのクリーンで一時的なプロジェクションであるべきなんだ。検索エンジン内でソフトデリートをクエリ時にフィルタリングしようとするのは、レイテンシスパイクのレシピだよ。

クエリの複雑さがチームを驚かせる部分なんだ。「どこにでもWHERE deleted_at IS NULLを追加すればいい」と自分に言い聞かせるけど、6ヶ月後には誰かがレポートにゴーストデータが表示されている理由をデバッグしている。ビューは助けになるけど、並行してアクセスパターンを維持しないといけない。そして、実際に削除されたレコードをクエリする必要がある瞬間（監査、サポートチケット、元に戻す）には、自分の抽象化をバイパスすることになる。イベントソーシングはこれをもっとクリーンに解決するけど、運用オーバーヘッドは現実的だよ。私が見たほとんどのチームは、コアエンティティがイベントソーシングされていて、他のすべてはソフトデリートで指を交差させているハイブリッドになってしまう。

└

君の言いたいことがよくわからないな。CREATE VIEWは確かに役立つし、必要な時にはまさにそれだけで済むことも多いよね。でも、もし複数のアクセスパターンがあって、たまに「削除されたレコードを実際にクエリしなきゃいけない」ことがあるなら、誰かがそのアクセスパターンの不変条件を維持しなきゃいけないんだ。これは難しいことじゃないよ。問題の核心は、ソフトウェアエンジニアがスキーマや基本的なSQLを扱えない一方で、アナリストやBIの人たちはSQLにある程度詳しいけど、データベースの内部動作についてはほとんど理解していないってこと。しかも、90年代からの癖を持ってるしね。まさに「釜の中の鍋」って感じだよ。ソフトデリートのことは一旦忘れよう。もう一つ、私の経験から言うと、SWEとBIの人たちが両方とも見失う超基本的な例を挙げるね：タイプ2のスローチェンジングディメンション。これはソフトデリートとも深く関係してるし、アクセスパターンに関してはもっと一般的なんだ。例えば、情報を失わずにデータ更新をサポートしたいとするよね。保持ポリシーで指定しない限り、ユーザープロファイルの編集を追跡したいとしたら、どうする？Stackoverflowとかを見てみると、世界中のスキーマに対してもっとも悪影響を与えたアイデア、「監査テーブル」に出くわすだろう。だから、変更が必要な時に正規化されたデータ構造に安価なINSERTを行う代わりに、今度は高コストなUPDATEや追加のINSERTを行うことになる。なんでかって？DISTINCT ONや複合主キーが魔法のようなもので、ORMには忌避されるからだよ。BI側がもっと良いことをしてると思ったら、それは間違いだよ！彼らにとっても、DISTINCT ONはしばしば謎なんだから。一瞬のうちに、またサブクエリの地獄に戻っちゃうんだ。データベースは美しいものだよ。もっと尊重されるべきなのに、残念だね。

$dayjobのテーブルには、（開始、終了）と（発生、期限）フィールドの両方があるよ。「ある日付にXが真だった」ということだけど、「Z日付の時点で、私たちは…と思っていた」ということもできる。しかも、削除フラグが立っていなくても、ほとんどのデータが現在未使用のままだったりする。例えば、チケットシステムに入ると、ずっと前に閉じた古いリクエストもまだ見えるんだ。