**ソフトデリートの課題** - **データ整合性リスク** - 非アクティブなレコードがテーブルに残ることで、重複データや誤ったビジネスロジックにつながる可能性があります。 - 関連テーブルがソフト削除行を考慮していない場合、参照整合性制約が破綻する恐れがあります。 - **パフォーマンス低下** - クエリは「`WHERE is_deleted = false`」で削除済み行を除外しなければならず、オーバーヘッドが増大します。 - インデックスが肥大化し、メンテナンス（例：PostgreSQL の VACUUM）がよりコスト高になります。 - **ストレージ膨張** - ソフト削除は行全体を保持するため、ハードデリートやアーカイブで解放できる領域が確保されません。 - 長時間実行中のトランザクションがこれらの行にロックを保持すると、同時実行性が低下します。 - **複雑なビジネスロジック** - 関数やストアドプロシージャは削除ステータスを追加で確認する必要があります。 - 監査システムは「本当に削除されたか」「論理的に除外されただけか」を区別しなければなりません。 - **データプライバシー遵守** - GDPR や CCPA といった法規制では個人情報の完全消去が求められるため、ソフト削除のみでは要件を満たせない場合があります。 - **復旧とリストアの課題** - ソフト削除されたレコードを復元する際、ビジネスルールが変更されているとエラーが発生しやすくなります。 - バルクでの未削除操作は古いデータや不整合な情報を再導入するリスクがあります。 - **テスト・QA の負荷** - テストスイートはアクティブ状態と非アクティブ状態の両方を網羅しなければならず、複雑さが増します。 - 単体テストでソフトデリート挙動をモックすることは容易ではありません。 --- **結論** ソフトデリートは監査性やロールバックの利便性を提供しますが、維持管理・パフォーマンス・コンプライアンスに関して大きな課題をもたらすため、慎重に運用する必要があります。

ソフトデリート設計レビュー

ソフトデリート（論理削除）フラグ（booleanまたは

archived_at

タイムスタンプ）は、
誤って削除されたレコードを復旧できると同時にコンプライアンス／監査要件にも応えるため、
ソフトウェアプロジェクトでよく使われます。
しかしながら、以下のような大きな複雑さが生じます。

• デッドデータ：ほぼすべてのアーカイブ行は参照されず、テーブルを肥大化します。
• クエリオーバーヘッド：各クエリで削除済みレコードを除外しなければならず、インデックスが増大して無駄な行も含まれます。
• 運用負担：ライブデータとアーカイブデータが同じテーブルに共存すると、マイグレーション・バックアップ・リストアが難しくなります。
• 信頼性リスク：古いアーカイブを削除し忘れると、データベースは大きく遅くなります。

以下では、ライブテーブルをクリーンに保ちつつ、レコードのアーカイブ・復旧を簡単に行える代替策を紹介します。主にPostgreSQL向けですが、多くのアイディアは他のRDBMSにも応用可能です。

---

1. アプリケーションレベルでのアーカイブ

• パターン：レコード削除時にドメインイベント（例：SQS）を発行し、
  バックグラウンドワーカーがそのイベントを受け取ってJSONスナップショットをオブジェクトストレージ（S3、GCSなど）へ保存します。

長所	短所
プライマリデータベースとアプリコードをシンプルに保てる。	バグが増える可能性がある；失われたアーカイブは手動でクリーンアップする必要がある。
削除処理が外部リソースも非同期にクリアできる。	複数サービスとメッセージキューを要する。
JSONで保存されるため、検査や再生成が容易。	アーカイブデータのクエリは難しく、追加ツールが必要になる。

---

2. トリガー駆動型アーカイブ

削除された行をJSONブロブとして格納する専用「archive」テーブルを作成します。

CREATE TABLE archive (
    id              UUID PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    data            JSONB NOT NULL,
    archived_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    caused_by_table TEXT,
    caused_by_id    TEXT
);

CREATE INDEX idx_archive_tbl_rec ON archive(table_name, record_id);
CREATE INDEX idx_archive_ts   ON archive(archived_at);

トリガー関数は以下のようになります。

CREATE OR REPLACE FUNCTION archive_on_delete()
RETURNS TRIGGER AS $$
DECLARE
    cause_table TEXT;
    cause_id    TEXT;
BEGIN
    -- カスケード削除のコンテキストを検知
    cause_table := current_setting('archive.cause_table', true);
    cause_id    := current_setting('archive.cause_id', true);

    IF cause_table IS NULL THEN
        PERFORM set_config('archive.cause_table', TG_TABLE_NAME, true);
        PERFORM set_config('archive.cause_id', OLD.id::TEXT, true);
        cause_table := TG_TABLE_NAME;
        cause_id    := OLD.id::TEXT;
    END IF;

    INSERT INTO archive (id, table_name, record_id, data,
                         caused_by_table, caused_by_id)
    VALUES (
        gen_random_uuid(),
        TG_TABLE_NAME,
        OLD.id::TEXT,
        to_jsonb(OLD),
        cause_table,
        cause_id
    );
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

トリガーを付与します。

CREATE TRIGGER archive_users
    BEFORE DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION archive_on_delete();

CREATE TRIGGER archive_documents
    BEFORE DELETE ON documents
    FOR EACH ROW EXECUTE FUNCTION archive_on_delete();

利点

• ライブテーブルは

  archived_at

  列や不要行を持たない。
• アーカイブテーブルはパーティション分割・別テーブルスペースへ移動可能。
• シンプルなクリーンアップ：

  DELETE FROM archive WHERE archived_at < NOW() - INTERVAL '90 days';

• 通常のクエリでアーカイブデータを除外する必要がない。

トレードオフ

• 削除時にトリガー実行分だけオーバーヘッドが増える。
• アーカイブテーブル自体もストレージを消費するが、削除済み行のみ保存される点はメリット。

---

3. WALベースのCDC（Change Data Capture）

PostgreSQL の論理リプリケーションスロットと CDC ツール（Debezium, pgstream, wal2json 等）を使い、DELETE イベントを Kafka → S3 / Elasticsearch / DB など外部ストアへストリームします。

長所

• アプリやトリガーの変更不要。
• 同時に複数の宛先へ送信可能。

短所

• Kafka クラスター、CDC ワーカー、監視など運用オーバーヘッドが大きい。
• コンシューマが遅延すると WAL が蓄積し、

  max_wal_size

  ・

  max_slot_wal_keep_size

  の調整が必要。
• スキーマ変更は送信側と受信側の両方で調整が求められる。

---

4. “Delete‑Ignore” レプリカ（実験的）

論理レプリカを作成し、DELETE コマンドを無視させることで、挿入・更新されたすべての行を保持します。必要に応じてこのレプリカ上で

archived_at

列へ変換してもよい。

潜在的課題

• スキーママイグレーションが失敗する可能性。
• レプリカ維持のため追加ストレージ・運用コストが発生。

---

推奨策

新規プロジェクトでソフトデリートを採用する場合、トリガー駆動型アーカイブ が最もシンプルかつ侵入性が低い選択肢です。

1. ライブテーブルはスリムに保ち（

   archived_at

   列不要）。
2. 削除を専用 JSON テーブルへアーカイブ。
3. 定期的に古いアーカイブを時間ベースで削除。
4. 復旧は通常 API で JSON スナップショットを再挿入するだけ。

この手法は保守性・パフォーマンス・復元性のバランスが良く、追加サービスや複雑なインフラを必要としません。Kafka を既に利用している場合や外部へ変更ストリームを送る必要があるなら CDC も検討すべきですが、それ以外はトリガーで十分です。