e-dash Tech BlogPublicationへの投稿
🐾

そのデータ設計、大丈夫？論理削除との付き合い方

kakenyan
に公開
SQL
Tech
Database
tech

はじめに

こちらはe-dash advent calendar 2025の11日目の記事です。

こんにちは。e-dashでバックエンドを開発している山口です。

本記事では、新規アプリケーションの開発を行っている際に直面した課題をきっかけに、改めて論理削除と物理削除について整理し、今後の設計に役立てることを目的としています。

同じようにアプリケーションの実装を行う方、特にDB設計に携わる可能性のある方に読んでいただけると嬉しいです。

論理削除と物理削除の定義

まず最初に、それぞれの定義と簡単にメリット・デメリットを確認します。

論理削除 (Soft Delete)

レコード自体は残したまま、「削除された」とマークして扱うこと。

  • 例：deleted_at カラムに日時を入れる、is_deleted フラグを true にする
  • アプリケーション側で「削除フラグの立ったデータを通常の一覧・検索から除外する」ことで、ユーザーからは削除されたように見せるが、履歴・復元・監査のためにデータ自体は保持する

メリット

  • フラグ等を戻すだけで、復元が容易に行える
  • 過去データの追跡が容易で、履歴や監査に強い

デメリット

  • クエリや実装ルールが複雑化しやすい
  • 削除済みデータが溜まり続けるため、ストレージ圧迫やパフォーマンス劣化の可能性がある

物理削除 (Hard Delete)

データベースやストレージからレコードそのものを完全に削除すること。

  • DELETE 文で行を消す、ファイルを消す など
  • バックアップなどの手段を除き、削除後は通常の手段では参照できない

メリット

  • 実装がシンプルである
  • ストレージを節約できる

デメリット

  • 実際にデータを削除するため、復元が難しい
  • 履歴の追跡が難しく、監査等に向かない

今回のアプリケーション開発において、私はそこまで深く考えずに「データの復元が容易そうだから・社内の別アプリケーションでも論理削除を採用していたから」という気持ちで論理削除を選択し、DB設計や実装を進めていました。この後の苦労も知らずに...。

論理削除のよくある課題

ユニークにしたい値の例として、ユーザーのメールアドレスを考えます。

CREATE TABLE users (
  mail_address      VARCHAR(255) PRIMARY KEY, 
  user_name         VARCHAR(100) NOT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at        DATETIME DEFAULT NULL -- 値が非nullの場合、論理削除済として扱う
);

論理削除されたレコードのIDが再利用できない

最もよくあるケースかと思います。例えば、ユーザーのメールアドレスを、主キーやユニーク制約として設計した場合に発生します。

一度解約したユーザーが再度同じメールアドレスで登録しようとすると、メールアドレスが同じ値の論理削除されたレコードが存在するため、そのままでは新規にレコードが登録できない、というものです。

下の例ではyamaguchi_1のユーザーは論理削除されているにも関わらず、同じメールアドレスのyamaguchi_2のレコードはINSERTに失敗します。論理削除といっても、テーブルにレコードは残っているため、同じメールアドレスのレコードは登録することができないのです。

-- mail_address がPKであるため、失敗する
INSERT INTO users (mail_address, user_name)
VALUES('yamaguchi@example.com', 'yamaguchi_2');
mail_address(PK) user_name (略) deleted_at
yamaguchi@example.com yamaguchi_1 ... 2025-12-10 09:00:00
yamaguchi@example.com yamaguchi_2 ... NULL

「じゃあ、メールアドレス単体で主キーにするのはやめて、deleted_atと合わせて複合ユニーク制約を作成すれば良いのでは？deleted_atには時刻が入るから、論理削除したものでユニーク違反になることはないだろう」と無知な私は考えました。すると次の問題にぶつかりました。

ユニーク制約におけるNULLの扱い

DBMSの種類によっては、UNIQUE INDEXではNULLの値を同じ値として区別できません。つまり「メールアドレスとdeleted_atで複合ユニーク制約を設定する」という状況では、以下の状態が許容されてしまいます。

mail_address(複合ユニーク) user_name (略) deleted_at(複合ユニーク)
yamaguchi@example.com yamaguchi_1 ... 2025-12-09 09:00:00
yamaguchi@example.com yamaguchi_2 ... NULL
yamaguchi@example.com yamaguchi_3 ... NULL

yamaguchi_1は論理削除済のため、同じメールアドレスを使用するyamaguchi_2が存在することは何も問題ありません。しかし、本来共存することを意図されていないyamaguchi_2とyamaguchi_3のレコードは共存できてしまうのです。
ここでmail_address単体をユニークにしたとしても、最初の課題が再発してしまうため、解決には繋がりません。

https://www.postgresql.org/docs/current/indexes-unique.html

ユニーク制約と論理削除を両立する方法

この「ユニーク制約をかけたい」「論理削除済ユーザーのメールアドレスを使いたい」という2つの目的を両立するには、下記のような方法があります。

  • 論理削除されていないレコードにのみ部分インデックスを作成し、そこにユニーク制約を適用する
  • 論理削除済時にNULLとなるカラムを用意し、対象のカラムと共に複合ユニーク制約を設定する
    • 例：IF(deleted_at IS NULL, 1, NULL)で値を計算される生成カラムを作成する
      • 論理削除済みのレコードに重複するメールアドレスが存在していても、UPDATEやINSERT時に無視される
      • 逆に論理削除されていないレコードに重複するメールアドレスでINSERTしようとした場合、(特定のメールアドレス, 1)が複合ユニーク制約に反して失敗する
      • こちらの記事が参考になります

DBMSによって実現方法が異なるので、その点には注意が必要です。

そもそも論理削除を用いる必要はある？

両立することが可能であると知った上で、私はこの疑問を抱かざるを得ませんでした。

外部キー制約における矛盾

対象のレコードが論理削除されていたとしても、外部キー制約のエラーは発生しません。つまりそれは、論理削除済のレコードを参照するレコードを新規に登録できてしまう、という事を意味します。そして新しく登録されたデータは論理削除されておらず、矛盾した状態が発生する可能性があります。

CREATE TABLE orders (
  order_id           BIGINT PRIMARY KEY,
  user_mail          VARCHAR(255) NOT NULL,
  order_total        INT NOT NULL,
  CONSTRAINT fk_orders_users
    FOREIGN KEY (user_mail)
    REFERENCES users(mail_address)
);
mail_address(PK) user_name (略) deleted_at
deleted-user@example.com deleted-user ... 2025-12-10 09:00:00

上記の削除済ユーザーdeleted-userに対し、

order_id(PK) user_mail(FK) (略) deleted_at
1000 deleted-user@example.com ... NULL

という注文レコードを登録したとしても、当然エラーにはなりません。削除されたユーザーに紐付く注文レコードが生まれてしまいました。さらには、このordersのレコードがある限り、usersテーブルのdeleted-userのレコードは参照されているため物理削除することができません。この状態が続けば、論理削除済のレコードを単純に物理削除する、ということも簡単ではなくなってしまうかもしれません。

アプリケーション側の実装により回避する手段はいくらでもあるかと思いますが、「それを考慮しながら実装しなければならない」「不整合なデータが生まれてしまった場合、より複雑な調査や対応が必要になる」といった懸念は拭えません。

ちなみに物理削除なら、以下のようにON DELETE CASCADEオプションを利用することで、外部キーとして参照しているテーブルのレコードまで一度に削除することができます。

-- 親テーブル: ユーザー
CREATE TABLE users (
  mail_address      VARCHAR(255) PRIMARY KEY, 
  user_name         VARCHAR(100) NOT NULL
);

-- 子テーブル: 注文
CREATE TABLE orders (
  order_id           BIGINT PRIMARY KEY,
  user_mail          VARCHAR(255) NOT NULL,
  order_total        INT NOT NULL,
  CONSTRAINT fk_orders_users
    FOREIGN KEY (user_mail)
    REFERENCES users(mail_address)
    ON DELETE CASCADE
);

-- このDELETE 1文だけで
-- users から対象ユーザーが削除され、orders からそのユーザーの行も自動削除される
DELETE FROM users
WHERE mail_address = 'deleted-user@example.com';

クエリの複雑化

論理削除を採用している全てのテーブルにおいて、検索時に以下が必要かどうかを考慮しなければなりません。

WHERE deleted_at IS NULL

もし別テーブルをJOINしてクエリを発行する場合は、その結合条件にも論理削除を考慮する必要があります。全てを無条件に結合してから、当該テーブルで論理削除済かどうかを考慮することも可能ですが、パフォーマンス面でマイナスに働く可能性もあります。

LEFT JOIN orders ON users.mail_address = orders.user_mail
                  AND orders.deleted_at IS NULL;

特に私のように後付けで設計変更を試みた場合は、全てのクエリにおいて漏れなく・正しく対応することは簡単ではありません。

これらのようなアプリケーション面での複雑さ以外にも、最初に挙げた「不要データの蓄積」などといったデメリットも当然存在します。

それでも論理削除を避けては通れない

じゃあ論理削除やめよう！となるかと言うと、そういう訳には行きません。全てのテーブルから論理削除フラグを取り除くことは容易ではありませんし、新規テーブルを設計して移行するとしても、そもそもそんな移行作業を必要とするほどのメリットがあるかと言われたらそうではないでしょう、と言うのが本音かと思います。

引き続き論理削除を利用していくにあたり、一例ではありますが下記のような事柄の考慮が必要になると考えています。

  • バックアップの作成
  • ログの保存方法や内容の検討
  • 不整合データのチェックの自動化
  • 定期的な不要レコードの物理削除
  • データの不整合が発生しないアプリケーションのロジック設計

つまるところ、最も大事なのは「最初のDB設計時にしっかりと検討を行い、状況に応じて論理削除と物理削除を使い分けること」であると思います。

ユニーク制約に対応させないまま論理削除を採用したテーブルで、挙動テストのために都度レコードの物理削除を行なったり、履歴等が不要な一時データのみを保持するテーブルではそもそも論理削除である必要がなかったりと、今回の開発中だけでもより楽をできていたな...と思う経験がありました。最も反省すべき点は初期段階で検討の浅かった自分、という結論になりました。設計、難しいですね。

終わりに

「データを完全に消しちゃうのは怖いから...」と安易に論理削除を採用すると、クエリやデータ整合性の複雑化・不要データの蓄積といった負債を招きかねません。メリットとデメリットの両方に目を向けて、アプリケーションの要件や規模によって適切に設計を行いましょう。

私が苦労したのは「論理削除を選択したこと」が理由ではなく、それを選択した上で適切な設計を行うだけの知識を、そのタイミングで持っていなかったことが理由なのです。今回の経験を糧に、次の機会ではより良いDB設計ができることを祈っています。

最後まで読んでいただき、ありがとうございました。皆様のDB設計にも幸多からん事を！

e-dash Tech Blog
e-dash Tech BlogPublication

Discussion