DELETE_FLAG を付ける前に確認したいこと。

DELETE_FLAG という思考停止フラグ

DELETE_FLAG という boolean の列が DB 設計でよく話題になります。
論理削除という言葉で上手に論理武装し、スキを見せるとすぐに入れたがる人がおり、
一方でそれにつよく反対する人もいます。

自分の経験としては、広義の論理削除はありえると思いますが、実現方法が DELETE_FLAG だとなった時、それはあまり考えてないでなんとなくパターンとして盛り込んでる場合が多いと感じます。

ただし、設計に唯一の答えは無いので、もしかしたらそれが妥当な設計である場合があるかもしれません。
今回は「DELETE フラグがなぜダメなのか？」などという話をするつもりも、アンチパターンだと断言するつもりもありません。

問題は、仕様をきちんと把握すると、「最適な設計は DELETE_FLAG ではない」という場合が有って、その場合は、その最適な設計を探すのが正しいだろうという話です。単純に考え至って無い可能性が隠れている。

今回は設計段階で DELETE_FLAG が出てきたとき、それが本当に妥当かどうが確認するために、自分がやっていたことを書いてみます。

まず理由を聞く

DELETE_FLAG がしれっと入ったテーブル設計を見たところから話は始まります。
まず意図を聞く。

Q 「この DELETE_FLAG 列はどういう目的で必要なのですか？」
A 「データ上は無い事にしたいけど、実際のデータは消したくないからです」(??)

だいたいこんな説明が返ってきます。
この突っ込みどころの多いパターンで返答が来た場合は、次の質問でさらに深く聞くと、そもそも設計として妥当じゃないことが分かったりします。

次に聞く事

だいたいこんな質問を 3 つほどします。

「その論理削除した行は、どういう時に参照しますか?」
「論理削除した行が復活する事がありますか?」
「論理削除した行は最終的にどうなりますか?」

まあ質問の仕方は何でも良いので、以下の観点で話を掘り起こして行く感じです。
だいたいが「本当は何がしたいのか」が見えて来て、それは大抵 DELETE_FLAG でやることじゃないと分かる場合が結構あると思います。

「その論理削除した行は、どういう時に参照しますか?」

「滅多に見る事は無い。何か重大なことが起こったときに、記録として提出したり、調査する可能性があるから消せない」

責務としてその記録を残しておく必要がある場面であり、例えば会社が何かやらかしたり、攻撃を受けたりしたときに、ほり起こして参照する可能性があるというパターン。

この場合は、実際に DELETE 文で消してしまう事を薦めます。

そして、その数年だか数ヶ月に一度あるかないかの事態が起こってしまった場合、バックアップからリストアすれば良いでしょう。
特に Oracle のような高機能な DB は Flash Recovery Table などで、テーブルを過去の状態にタイムスリップさせることもできるので、数分オーダーでできる場合もある。DB をまるごと戻すにしても、それくらいの事態が起こってるんだろうから、多少コストかけてもやればいい。

結果、削除 = DELETE 文という非常に奇麗な世界でアプリを設計でき、そのメリットの方がでかいと思います。

この場合、次の質問はこうなります。

「その記録としての保持期間はどのくらいですか？」
「復元にかかる時間はどのくらい許せますか？」

本来やるべきだった事は、 DB を戻すためにはログやバックアップが必要なので、そのバックアップがこの保持期間を満たすように世代管理です。
特に差分更新バックアップなどをしている場合は、リストア時間の調整が必要になる。
そもそも運用の人になんの相談も行ってないだろうし、まずは彼らを呼び出してミーティングの再設定ですね。

かなりの頻度で参照される場合は、質問は違えど概ね次と同じ対応になります。

「論理削除した行が復活する事がありますか？」

まさかと思うかもしれないが、「ある」と答えが返ることがあります。それは削除じゃなく、状態です。
データが状態を持つ事は多々あります。そして、この場合削除としているものは、例えば「available」が「unavailable」になったことを、
論理削除という操作で表している証拠でしょう。

ここで STATE 列を入れる前に、必ず聞くべきことがあります。

「他に状態はありませんか?」

大抵、色々仕様を見返すと「こういう状態もありますよね?」とこちらが気づくくらいには見落としがあって、
これは放っておいたら、状態が発見されるたびに FLAG 列が追加されていた可能性があります。

前述の「残したいが、結構な頻度で見る」というのも、そもそも削除じゃなく何かしらの「低頻度で参照する」という状態がある筈です。

そして、状態の表現に boolean は適しません、状態の追加に対して閉じてしまうからです。そもそも状態が二つしか無いデータの方が経験的に珍しいと思います。

本来やるべきだったことは、状態をくまなく探し、例えば ENUM として Number を割り当てるなどの設計です。
システム全体でその数値が何を意味するかを周知する必要もあるでしょう。
周知の代わりに、それぞれの状態に応じた VIEW を合わせて設計するというのも、非常に重要かつ有効な場合もあります。

ここで気になるのは、実は同じような ENUM が、すでにアプリケーションコードの方に定義されてないかです。
既存アプリがあるなら、その担当チャネルに IRC で呼びかけるか、リポジトリを clone して git grep してみましょう。
だいたいみんな同じ事を考えていて、擦り合わせないと後で後悔します。

復活する事が無いなら、それは次の質問に繋がります。

「論理削除した行は最終的にどうなりますか？」

「ずっと残すが、更新はされない。」

これは、前述の「データの状態」の一つとすることもできるが、更新しないが削除もしないし参照も少ないコールドデータとなったということが重要です。

コールドデータをアクティブなデータと同じテーブルに残すと、結構なサイズになってしまう場合があります。
大した量じゃなければ、状態を付加して放っておいてもいいのですが、それがアクティブデータの参照に影響するなら、対策が必要です。

聞くべきことはこのあたりかと。

「そのコールドデータはどのくらいのオーダーで増えますか?」
「コールドデータのみを、アーカイブすることは可能ですか?」
「物理で殴る予算がありますか?」

「いずれ消す」という答えが返った場合は、その保存期間を聞くのですが、結果対応は同じ感じになります。
コールドデータであることに変わりはないからです。

対応法を見て行きます。

テーブル移動/アーカイブ

素直に考えると、同じスキーマのアーカイブテーブルを作りそこに移すという手が考えられます。

移動をリアルタイムでやるなら DELETE_FLAG はいりません。
しかしこれは SQL ではできないので、プロシージャ等を別途書いて行う必要があります。

ただし、これは実は非常に難しいことが多いです。
追加で確認する必要があるのはこれです。

「外部キーでひもづくデータはどうしますか？」

データの移動はトランザクションの中で行えば、 DELETE + INSERT で安全に行えるでしょう。
つまり、 DELETE 自体はします。

しかし、これはマスタ系のテーブルだと上手く行きません。
例えば社員表から退職者を移すなどと言う場合、その退職者の ID は多くのテーブルから参照されており、
その参照をどうするかが最大の課題となります。

on delete cascade が特に負荷も大変な事になります。
on delete set null だと、参照が切れてしまい、他の多くのテーブルと参照が切れていいのか？という話になります。

RDB では、一つの列が複数の列(社員表のID と退職者表のID)を参照することができないので、特に参照関係が多い場合は難しいか、無理だったりします。

ただし、トランザクションやログ的な追記データならば、古い方だけ切り出すのが結構楽な場合が多く、要件によっては、テーブル以外の形に export して置いておくということもあり得ると思います。

物理で殴る

物理でなぐって解決できるなら、迷わず殴りましょう。

テーブルを移さないでよいように、そのテーブルのファイルが入ったインスタンスのみ物理的にスケールアップするとか、プロシージャが快適に動く程度のメモリを確保するなど、物理で解決できる問題も多くあります。

仮に、退職社員のデータを社員表から引きはがせなかった場合も、アクティブデータの参照に問題が出ないならそのままという選択肢も有ると思います。
いくらお金がかけられるかという話がでかいですね。

パーティションテーブル

また Oracle になってしまいますが、単一の表を任意の条件でデータファイル上別として扱うパーティションテーブルという機能があります。いわゆる水平分割です。
これを使うと、表を分けずとも単一のテーブルの実データを指定した条件で分けることが可能になります。

例えば、退職社員と現職社員を、パーティションテーブルで構成すれば、現職社員への操作負荷を減らしつつ一つのテーブルで扱うことができます。

この場合は、パーティションのキーとなる状態があればいいでしょう。

Oracle という時点で物理で殴るよりもお金がかかるかもしれませんが。

補足

Update の方が速い

JOIN が遅いから非正規化する、などと同じ考え方で、パフォーマンスを設計の理由にする場合があるかもしれません。
通常チューニングやスケールアップでは解決できる場合が多いですが、「どう考えてもそこがボトルネックで、どうチューニングしても解決しない」場合はいいのかもしれません。

問題は、本当にそんな場合があるのか、安易なフラグを付けることによって設計にしわ寄せがいったとしてもフラグを付けるだけの価値があるか、物理設計のツケを論理設計で払うのが本当に良いのか、つける前に検証する価値は有ると思います。推測しないで計測せよという話です。

データを「消す」ということ

そもそも、データは事実を現すので、物理的にせよ論理的にせよ、一度生まれたデータが消えるということは、無くはないが、実はそんなに多くはないと思っています。

マスタ系のデータであれば、見る必要がなくなるという状態の変化はありえますが、トランザクション系のデータから参照も多いため、完全に消えることは少ないです。
トランザクション系のデータであれば、そのまま蓄積され「データ」が「ログ」のような扱いに変わる事はあります。
そのログの保持期限がくれば消えるかもしれませんが、消えるといっても移されるだけかもしれません。

CRUD はデータのライフサイクルを表しますが、データが消えるということは結構レアな事だということをふまえると、論理であれ物理であれ、「削除」だと思っているその操作は、そのデータに対して行うべきこととして本当に正しいのか、ちゃんと考えた方が良いと思います。
そう考えてみると「消した事にする」という設計はどのみち半端な事が多いように思います。

DELETE_FLAG 自体の是非よりもその手前の話として、論理削除 == DELETE_FLAG というパターンの一つ覚えで設計する多くの人は、単純にちゃんと考えられてない場合が多いので、少し掘り下げて聞いてみると良いと思っています。