- データ復旧や規制遵守のために
archived_at 列ベースのソフトデリート がよく使われるが、時間が経つにつれて 複雑さと非効率 が増していく
- この方式は クエリ、インデックス、マイグレーション、復元ロジック を複雑にし、ほとんどのアーカイブデータは再び読まれないため データベースに不要な負荷 をもたらす
- 代替案として アプリケーションイベントベースのアーカイブ、トリガーベースのアーカイブ、WAL(Change Data Capture) ベースのアーカイブ が提示されている
- 各方式は 運用の複雑さ、インフラ要件、復元のしやすさ に違いがあり、特に WAL ベースは Kafka など外部システムとの統合 が必要になる
- 新しいプロジェクトであれば トリガーベースのアプローチ が シンプルさと保守性の面で最もバランスの取れた選択 である
ソフトデリートの問題点
- 一般的には
deleted ブール値や archived_at タイムスタンプ列を使ってデータを論理的に削除する
- 顧客が誤ってデータを削除したときに復旧できる
- 規制や監査の目的で保持が必要な場合もある
- しかし
archived_at 列は クエリ、運用、アプリケーションコード 全体に複雑さをもたらす
- ほとんどのアーカイブデータは再度読まれない
- API の動作問題や自動化ツール（Terraform など）によって 数百万件の不要な行 が蓄積する可能性がある
- アーカイブデータの整理作業が設定されていないと データベースのバックアップおよび復元時に性能低下 が発生する
- クエリやインデックスでアーカイブデータをフィルタリングする必要があり、データ漏えいのリスク が存在する
- マイグレーション時に古いデータの処理やデフォルト値の修正が難しい
- 復元ロジックが複雑になり、外部システム呼び出し が必要な場合はバグが発生しうる
- 結果として
archived_at 方式はシンプルに見えても 長期的には保守コストが高い
アプリケーションレベルのアーカイブ
- 削除時に イベントを発行 し、それを SQS に送って別のサービスが S3 にアーカイブする
- 利点
- 主要データベースとアプリケーションコードをシンプルにできる
- 外部リソースのクリーンアップを 非同期処理 にして性能と安定性を向上できる
- JSON 形式でシリアライズして アプリケーションに適した構造 で保存できる
- 欠点
- アプリケーションコードのバグによって アーカイブデータが失われる 可能性がある
- メッセージキューなど 運用インフラの複雑さが増す
- S3 のアーカイブデータには 検索および復元ツールが必要
トリガーベースのアーカイブ
- 削除前トリガーが行を 別の archive テーブル に JSON 形式でコピーする
- 例のテーブル:
archive(id, table_name, record_id, data, archived_at, caused_by_table, caused_by_id)
- 外部キー削除（cascade）時の 削除原因の追跡 のためにセッション変数（
archive.cause_table, archive.cause_id）を使う
- 利点
- ライブテーブルをクリーンに保てる ため、
archived_at 列が不要
- アーカイブテーブルの整理（
WHERE archived_at < NOW() - INTERVAL '90 days'）が簡単
- クエリとインデックスの効率を維持でき、マイグレーションも単純化される
- バックアップサイズを削減できる
- アーカイブテーブルは 別テーブルスペース や 時間パーティショニング で管理できる
WAL(Change Data Capture) ベースのアーカイブ
- PostgreSQL の WAL ログを読んで削除イベントを外部システムへストリーミング する
- 代表的なツール: Debezium（Kafka と連携）
- 経路の例:
PostgreSQL → Debezium → Kafka → Consumer → Archive Storage
- 軽量な代替案
- pgstream: WAL を webhook やメッセージキューへ直接送信
- wal2json: WAL を JSON として出力
- pg_recvlogical: PostgreSQL 組み込みの論理レプリケーションツール
- 運用の複雑さ
- Kafka ベースは 監視・障害対応・チューニング が必要
- コンシューマーが遅延すると WAL ファイルが蓄積 → ディスク容量不足のリスク がある
- PostgreSQL 13+ の
max_slot_wal_keep_size 設定で制限可能
- レプリケーションスロットの遅延監視とアラートが必須
- 利点
- アプリケーションコードを変更せずに すべての変更をキャプチャ可能
- 多様な宛先（S3、データウェアハウス、検索インデックス） へストリーミングできる
- 基本データベースに追加負荷がない
- 欠点
- 運用の複雑さとインフラコスト が高い
- コンシューマー遅延時に データ損失または再同期が必要 になる可能性がある
- スキーマ変更時に ソースとコンシューマー間の調整が必要
削除を処理しないレプリカというアイデア
- DELETE クエリを無視する PostgreSQL レプリカ を維持するアイデアが提示されている
- 削除されていないすべてのデータを蓄積して保持できる
- アーカイブデータを 直接クエリ可能
- 潜在的な問題
- 削除情報を区別できない可能性がある
- マイグレーション適用時に衝突のリスクがある
- ストレージ容量と運用コストが増加 する
結論
- 新しいプロジェクトでは トリガーベースのアーカイブ方式 が最も実用的な選択
- 設定が簡単で、ライブテーブルをクリーンに保てる
- 別個のインフラがなくてもアーカイブデータの参照と管理がしやすい
- 複雑なインフラがすでに存在する、または複数宛先へのストリーミングが必要な場合には WAL ベースのアプローチ が適している
4件のコメント
トリガーベースだとDBに負荷がかかると習った気がするけど……？ トリガーを勧めるのか
その程度のトリガーで発生する負荷が問題になるなら、トリガーがなくてもすでに問題だらけの状況です
結局のところ、規制はいつだってコストですね。まあ、どうせ最終的には消費者が負担する分ですが。
Hacker Newsの意見
私が働いている銀行ドメインでは、むしろ soft delete のほうが有利だと感じた
deleted_atカラムがあればクエリの記述が明確になり、分析用クエリや管理者ページでも同じデータセットを扱える 削除はほとんどの場合まれで、soft delete された行が性能問題を起こしたこともほぼなかった また関係がそのまま維持されるので、**復元（undo）も簡単だ 私はさらに進めて、行を完全に不変（immutable）**にし、更新時には新しい行を追加する方式を好む ログを残すなら、DB トリガーを使って INSERT/UPDATE/DELETE 時に複製テーブルへ記録を残すアプローチがよいと思うsoft delete の最大の落とし穴はクエリの複雑さだ 最初は
WHERE deleted_at IS NULLを追加すればよいと思うが、数か月たつとフィルタ漏れで幽霊データがレポートに現れる View で解決できるが、結局は並列アクセスパターンを維持する必要があり、削除済みデータを参照するときは抽象化を迂回しなければならない Event sourcing のほうがよりクリーンだが、運用負荷が大きいため、たいていはハイブリッドなアプローチが選ばれるsoft delete がDB の組み込み機能として提供されるとよいと思う テーブル単位で有効化し、削除処理の戦略を選べるなら理想的だ
私の経験では、トリガーベースのアプローチが最も安定していた アーカイブテーブルはappend-onlyで維持し、復元はアプリケーションレイヤーで処理すべきだ 更新は soft delete と見なし、トリガーが以前の状態をキャプチャするようにする トリガーは必ずBEFOREのタイミングで実行されるべきで、ロジックは単純でなければならない パーティションは月単位が一般的で、書き込み負荷が高いなら日単位に分けるのがよい
私は DB がstateful → statelessへ進化してほしいと思っている すべての変更を append-only イベントとして記録し、必要なデータはviewで表現する構造を好む DB が自動でmaterialized indexを管理してくれたら理想的だ 一部の最新 DB はこうした機能を提供しているが、まだOLTP 中心の発展が不足している
以前いた会社では、すべてのシステムに soft delete を適用していた 教授も「ビジネスの世界ではデータは決して削除されない」と言っていたのを覚えている
データベースは**事実（fact）**を保存する場所だ レコードの作成は新しい事実であり、削除もまた別の事実だ しかし行を物理的に消すと、事実が失われる ほとんどの場合、このような削除は望ましくない
Firezone では最初、soft delete を監査ログ用に使っていたが、マイグレーションの問題で断念した その代わり、Postgres CDC（Change Data Capture）を使って別の書き込み最適化テーブルへイベントを送る方式に切り替えた soft delete はユーザー復元機能には有用だが、監査やコンプライアンス用途には不適切だと思う
soft delete フィールドを持つテーブルの上にViewを作り、削除済み行を隠す方式はきれいだ こうすればアプリケーションは削除の有無を気にする必要がない
**スキーマドリフト（schema drift）**をどう処理するのか、という疑問がある 削除当時のスキーマでシリアライズされたデータを後で復元しようとすると、スキーマ変更が問題になる