論理フラグを使用してソフトデリートパターンを実装する際、なぜこのフラグを複合インデックスに追加すると、部分インデックス戦略と比較してアクティブレコードクエリのパフォーマンスが劣化することがあるのか、また、クエリプランナーの選択性推定はこれらのアプローチの間でどのように異なるのか？

質問への回答

質問の背景

ソフトデリートパターンは、監査トレイルやデータ復旧のためのハードデリートの代替として登場しました。初期の実装では、単純なブールフラグやタイムスタンプ列を使用しましたが、開発者はこれらのフラグを標準のBツリーインデックスに組み込む際にパフォーマンスの低下に直面しました。この問題は、2000年代半ばにPostgreSQLの部分インデックスやSQL Serverのフィルターインデックスが広く採用されるようになってから顕著になり、アクティブなレコードのみをインデックスすることが可能になりました。選択性推定—クエリプランナーが条件に一致する行の割合を予測する方法—を理解することは、完全な複合インデックスと部分インデックス戦略を比較する際に重要となりました。

問題

ソフトデリートフラグ（例：is_deleted）が複合インデックス(is_deleted, user_id, created_at)に追加されると、データベースオプティマイザーはWHERE is_deleted = falseでフィルタリングするクエリの行選択性を誤計算する可能性があります。90%の行がアクティブである場合、オプティマイザーはインデックススキャンの代わりにシーケンシャルスキャンを選択するか、あるいは分布が偏っている場合には不適切にインデックスを優先するかもしれません。部分インデックス（WHERE is_deleted = false）はアクティブな行のみを保存し、高い選択性を保証しますが、標準の複合インデックスはすべての行を保存するため、インデックスの膨張を引き起こし、統計がソフトデリートの分布を正確に反映しない場合にあいまいな基数推定が発生します。

解決策

ソフトデリートされた行をまったく除外する部分インデックス（PostgreSQL）やフィルターインデックス（SQL Server）を実装し、必要に応じて削除データ用の別のインデックスを追加します。MySQLや部分インデックスがないデータベースでは、アクティブなデータセットが小さい場合にのみ、ソフトデリートフラグを先頭列として複合インデックスを使用します。それ以外の場合は、削除ステータスでテーブルをパーティション分割します。バルク削除の後にテーブル統計を明示的に分析し、古くなったヒストグラムを防ぎます。アクティブレコードをクエリする際には、オプティマイザーがインデックスの適用性を認識できるように、部分インデックス定義の正確な述語（WHERE is_deleted = false）を使用します。

コード例

-- PostgreSQL：アクティブなレコードのみの部分インデックス
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server：フィルターインデックスの同等物
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- 部分インデックスを利用するクエリ
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

実生活の状況

10百万件のユーザー記録を管理するSaaSプラットフォームは、作成日時でアクティブユーザーをフィルタリングする際に管理ダッシュボードで深刻な遅延に直面しました。最初はPostgreSQLで複合インデックス(is_deleted, created_at)を使用し、WHERE is_deleted = false ORDER BY created_atクエリを加速すると想定していました。しかし、データセットが80%のソフトデリートされた歴史的アカウントに達すると、クエリの処理に8-12秒かかり、プランナーが膨張したインデックススキャンのコストを過小評価していることが分かりました。

解決策A：複合インデックスを維持し、ヒントを使用してインデックスの使用を強制する。 このアプローチでは、SET enable_seqscan = offやクエリプランヒントを使用してインデックスを利用することを強制しました。一時的に特定のクエリを改善しましたが、メンテナンスの負担が増し、データ分布が変化すると他のアクセスパターンに最適でないプランを強いることが多くありました。この解決策は、以前の過剰な複合インデックスによるストレージとVACUUMオーバーヘッドの増加を引き起こす根本的なインデックス膨張に対応していませんでした。

解決策B：アクティブと削除されたレコードのために別々の部分インデックスを作成する。 CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = falseを実装することで、インデックスサイズを80%削減し、プランナーが200万件のアクティブ行と800万件の削除行を正確に推定できるようになりました。クエリの処理時間は40msに短縮されましたが、is_deleted = false述語が明示的であり、関数にラップされることがないように、すべてのアプリケーションクエリをリファクタリングする必要がありました。

チームは解決策Bを選択しました。それは、クエリヒントのメンテナンスなしで持続可能なパフォーマンスを提供したからです。その結果、クエリの遅延が95%減少し、以前の過剰な複合インデックスによって引き起こされた定期的なVACUUM膨張の問題が解消されました。監視により、ダッシュボードの主要使用ケースに対して一貫して1秒未満の応答時間が確認されました。

候補者が見落とすことが多い点

NULL値を使用したソフトデリートタイムスタンプ列（アクティブにはNULL、削除にはタイムスタンプ）を使用した場合、部分インデックスの使用がブールフラグアプローチとどのように異なるか？

NULL可能なdeleted_atタイムスタンプを使用する場合、部分インデックス（WHERE deleted_at IS NULL）はPostgreSQLでのNULLインデックス可用性の扱いに課題が生じます。= falseが明示的でサージャブルであるブールフラグとは異なり、IS NULL条件はプランナーがインデックスの適用性を認識することを要求し、パラメータ化されたステートメントを使用するクエリでプランナーがパラメータがNULLであることを証明できない場合は失敗することがあります。さらに、deleted_at = CURRENT_TIMESTAMPを設定する更新は、アクティブレコードの部分インデックスでのインデックスの膨張を引き起こすため、行がそこから削除されることになりますが、ブールフラグの更新はビットを反転させるだけでフル複合インデックスの中に留まります。このNULL可能なアプローチでは、削除ステータスの変更が頻繁であるため、より頻繁なANALYZE呼び出しとインデックス充填係数の慎重な考慮が必要です。

カバリングインデックスにソフトデリート列を含めることで、削除頻度が低くても書き込みが期待したほど遅くなる可能性は何ですか？

カバリングインデックス（PostgreSQL 11+やSQL ServerでのINCLUDE句を使用して）でis_deletedを追加し、テーブルルックアップを避けると、実際には書き込みパフォーマンスが低下します。なぜなら、各ソフトデリート操作（UPDATE）は複数のインデックス構造を変更しなければならないからです。ユーザーがソフトデリートされると、データベースはアクティブな部分インデックスで古いインデックスエントリーを死とマークし、削除されたレコードインデックスに新しいエントリーを挿入し、カバリングインデックスのヒープポインタを更新する必要があります。候補者は、部分インデックスがこの変動を隔離することを見落としがちです。アクティブまたは削除された状態-specificの部分インデックスのみが変更されるのに対し、メインテーブルのカバリングインデックスはソフトデリート状態に関係なく、プライマリインデックス構造の更新が必要になるため、トランザクションスループットに影響を与える書き込み増幅が発生します。

クエリオプティマイザーがソフトデリートデータの部分インデックスを無視するのはいつですか？明示的に削除されたレコードをフィルタリングするクエリがあっても？

部分インデックスが監査クエリのためにWHERE is_deleted = trueとして定義されている場合でも、アプリケーションがアクティブおよび削除されたクエリの両方のためにパラメータ$1を使用する準備されたステートメントを使用すると、PostgreSQLは特定のtrueケースのために部分インデックスを認識しない一般的なプランをキャッシュする可能性があります。これは、準備されたステートメントがパラメータ値がバインドされる前にプランを生成し、オプティマイザーが$1 = trueが常にインデックス述語に一致することを証明できないために発生します。候補者は、動的SQLまたは再コンパイルヒント（SQL ServerでのOPTION (RECOMPILE)、PostgreSQLでのリテラル値での実行）を使用する必要があることを見落としがちで、これによりプランナーが具体的な値を見て、部分インデックス述語に一致させることができます。