SQL面接：重複行を安全に削除し最新レコードを保持する方法

プロンプトと適用されるコンテキスト

PostgreSQLのcustomer_contactsテーブルには2億行のレコードが存在します。古いインポーターの再試行処理により、同一の(tenant_id, external_id)に対して複数の行が作成されてしまいました。updated_at基準で最新の行を残す必要があります。タイムスタンプが同値の場合は、idが最大の行を勝者とします。contact_events.contact_idの外部キーはいずれのコピーも参照している可能性があるため、参照先を付け替える前に不要行（敗者）を削除すると、失敗するかリレーションが失われます。

customer_contacts(
    id bigint primary key,
    tenant_id bigint not null,
    external_id text not null,
    updated_at timestamptz not null,
    payload jsonb not null
)

contact_events(
    id bigint primary key,
    contact_id bigint not null references customer_contacts(id),
    event_type text not null,
    created_at timestamptz not null
)

ビジネスキーごとに決定論的な正規のコンタクトを1件残し、すべてのイベントを保持し、破壊的な処理を有界なトランザクションで実行し、監査および再試行が可能で、最終的にデータベースレベルで一意性を保証するPostgreSQL修復処理を設計してください。読み取りは常に利用可能である必要があります。制御された最終的な書き込みの一時停止は許容されますが、その所要時間は想定ではなく実測する必要があります。

2億行という規模と最終的な書き込み停止は面接上の制約条件です。ウィンドウ関数は単なる選択メカニズムにすぎず、実際の回答では参照整合性、同時実行性、ロールバック、ストレージの健全性、将来の書き込みの保護も網羅する必要があるため、これは難度の高いデータエンジニアリングおよびSQLの問題です。現在の公開SQL面接教材でも、ROW_NUMBERを用いた重複排除と決定論的なタイブレークは明示的な面接パターンとして扱われ続けています。検証可能な企業帰属情報はないため、companyNameはnullです。

面接官が評価するポイント

第1の評価シグナルは、候補者がDELETEを書く前に「重複」と「最新」を定義しているかどうかです。ビジネスキーは(tenant_id, external_id)であり、idは物理的な行を識別します。updated_at DESC, id DESCによる全順序付けにより、タイムスタンプが同値であっても厳密に1行が勝者となります。RANKでは同値の行が複数残る可能性がありますが、ROW_NUMBERは厳密に1つの位置1を割り当てます。

第2のシグナルは、破壊的処理の境界管理です。本番環境向けの回答では、件数とサンプルのプレビュー、不変な敗者から勝者へのマップの固定、敗者行または復元可能なスナップショットの保存、およびプライマリキーによる削除を行います。各バッチ実行時に独立して順位を再計算すると、書き込みが継続している間に処理対象セットが変動し、実行結果の説明が困難になります。

第3のシグナルは、参照整合性とトランザクションの正確性です。親レコードが削除される前に、すべての子参照を敗者から勝者へ移動する必要があります。子テーブルの一意性制約によってその更新が衝突する場合があるため、インベントリと競合ポリシーなしに「すべての外部キーを更新する」だけでは不完全です。各バッチは冪等で、短時間で終了し、可観測であり、安全に停止できなければなりません。

最後のシグナルは、クリーンアップによって根本原因が解消されるかどうかです。今日の重複を削除するだけのクエリでは、明日のインポーターが再び重複を作成してしまいます。永続的な不変条件は、nullおよび正規化の明示的な規約を伴う一意性制約またはユニークインデックスに設定すべきです。候補者はまた、正確性の証拠とパフォーマンスの証拠を区別する必要があります。重複キーゼロおよび孤立参照ゼロはセマンティクスを証明し、EXPLAIN、ロック待ち、WAL生成率、レプリケーション遅延、デッドタプル、オートバキュームの挙動は許容可能な稼働レートを決定します。

回答前に確認すべき質問

• どのカラムがビジネスエンティティを定義しているか？ ここでは厳密なペア(tenant_id, external_id)です。大文字小文字の統一、空白のトリミング、Unicode正規化を行うと異なるキーが定義されるため、修復前に合意する必要があります。
• どの行が勝者となるか？ 最大のupdated_at、次いで最大のidです。2つの行が同値の場合、タイムスタンプ単体では全順序になりません。
• 敗者行に固有のデータが含まれる可能性があるか？ ペイロードをマージする必要がある場合、「最新を保持」では不十分です。本プロンプトでは最新のペイロードを正とし、確認用に敗者行をアーカイブします。
• どのテーブルがcustomer_contacts.idを参照しているか？ 宣言された外部キーとアプリケーションレベルの参照をインベントリ化します。contact_eventsが示されていますが、実際の運用ではカタログと所有権ドキュメントを検索する必要があります。
• 参照先の付け替えにより子レコードの重複が発生するか？ 子テーブルにUNIQUE(contact_id, event_type, created_at)がある場合、統合後に同等の2つのイベントが衝突する可能性があります。更新前にマージ、保持、拒否のいずれを行うかを決定してください。
• 修復中に書き込みを継続できるか？ 保護されたライターが継続している間も履歴バッチを実行できますが、最終的な重複スキャンと一意性の引き渡しには、データベースレベルで検証された書き込みガードまたは測定された書き込み停止が必要です。一部のライターが回避可能なアプリケーションの慣例は保証になりません。
• ロールバック要件は何か？ 検証とロールバックウィンドウが終了するまで、バックアップまたはアーカイブされた敗者行と元の子マッピングを保持します。敗者から勝者へのマップ単体では破棄されたペイロードを再構築できません。
• どの程度の負荷が許容されるか？ バッチサイズは定数ではなく制御パラメータです。小さく開始し、ロック待ち、書き込みp99、WAL、レプリケーション遅延、デッドタプル、オートバキュームの進行状況に応じてスロットリングします。
• nullはどのように動作すべきか？ ここでは両方のキー列が非nullです。nullが許可され衝突させる必要がある場合、PostgreSQLの一意性にはNULLS NOT DISTINCTが必要です。デフォルトの一意性セマンティクスでは複数のnullが許可されます。

30秒の回答フレームワーク

「まずビジネスルールを確定させます。重複は(tenant_id, external_id)を共有し、勝者は最大のupdated_at、次いで最大のidとします。順位付けの結果をプレビューし、敗者行をアーカイブし、各敗者からその勝者への不変なrun_idマップを1つ実体化します。短時間の冪等バッチですべての子参照を記録・付け替え、敗者を指す参照が残っていないことを検証した上で、プライマリキーで敗者を削除します。各フェーズ後に件数、ペイロードサンプル、重複キー、孤立参照を照合します。最後に、検証されたライターガードまたは測定された書き込み停止の下で、最終的な差分クリーンアップを実行し、ユニークインデックスを構築して、すべての書き込みが同一のキー規約を使用するようにします。本番メトリクスからスロットリングを行い、ロールバックウィンドウが期限切れになるまでアーカイブを保持します。」

このフレームワークは、選択、依存関係の順序、破壊的制御、収束、予防を明確にします。SQLはそれらの決定に従うものであり、決定に代わるものではありません。

ステップごとの詳細な回答

ステップ1：不変条件、所有権、実行境界の確立

データに触れる前に事後条件を記述します：

• 各(tenant_id, external_id)に対して厳密に1つのコンタクトが存在すること。
• そのIDは(updated_at, id)の順序付けにおいて最大であること。
• 既存のすべてのcontact_events行が引き続き存在し、その勝者を参照していること。
• 宣言された参照またはアプリケーションレベルの参照のいずれも、削除されたIDを指していないこと。
• 完了したバッチを再試行しても変更される行数がゼロであること。
• 新規の書き込みによって別のビジネスキー重複が作成されないこと。

run_id、担当者、ソーススナップショットまたはバックアップ、開始時刻、コードバージョン、バッチカーソル、ダッシュボード、一時停止しきい値、ロールバック期限を割り当てます。修復前の行数、個別ビジネスキー数、重複キー数、敗者数、イベント数を記録します。全体合計によってテナントレベルの損失が隠蔽されないよう、テナントごとの管理合計を保持します。

ステップ2：決定論的ランキングのプレビュー

読み取り専用クエリから開始し、件数とペイロードの差異を検査します：

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY tenant_id, external_id
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM customer_contacts
)
SELECT tenant_id, external_id, COUNT(*) AS loser_count
FROM ranked
WHERE rn > 1
GROUP BY tenant_id, external_id
ORDER BY loser_count DESC, tenant_id, external_id
LIMIT 100;

規約で勝者を1件に絞る必要があるため、ROW_NUMBERが適切です。RANKまたはDENSE_RANKでは、idが含まれていない限り同等の順序付け値に同じランクが割り当てられ、決定論的なタイブレーカーがなければ再実行時に異なる勝者が選択される可能性があります。DISTINCTは選択された出力行の完全一致のみを除外するため、ビジネスルールに基づいて優先される完全な行を保持することはできません。

ステップ3：不変な敗者から勝者へのマッピングの実体化

子レコードの更新と削除のために勝者を個別に再計算しないでください。合意された実行境界の下で決定を一度実体化します。スニペットでは、修復ランナーから提供されるバインドパラメータを示すために:nameを使用しています：

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        ROW_NUMBER() OVER w AS rn,
        FIRST_VALUE(id) OVER w AS winner_id
    FROM customer_contacts
    WINDOW w AS (
        PARTITION BY tenant_id, external_id
        ORDER BY updated_at DESC, id DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)
INSERT INTO contact_dedup_map (
    run_id, loser_id, winner_id, tenant_id, external_id
)
SELECT :run_id, id, winner_id, tenant_id, external_id
FROM ranked
WHERE rn > 1;

contact_dedup_mapにはPRIMARY KEY(run_id, loser_id)、敗者と勝者が異なることのチェック、および(run_id, winner_id)のインデックスを持たせる必要があります。同一のrun_idの下で完全な敗者コンタクト行をアーカイブするか、テスト済みのポイントインタイムリストアパスを保持します。すべてのマップ勝者が存在し、同一実行内で勝者が敗者としても表示されず、各敗者が1回だけマップされ、マップサイズが測定された敗者数と一致することを検証します。

2億行の場合、ランキング処理に大規模なスキャンとソートが必要になる可能性があります。本番環境相当のデータでEXPLAINを実行し、一時領域とワークロードへの影響を確認した上で、それに応じてスケジュールまたはスロットリングを設定してください。カバリングインデックスは測定された計画に役立つ場合がありますが、それ自体の構築にコストがかかるため、根拠なしに推奨すべきではありません。

ステップ4：親の削除前の参照先付け替え

まずすべての参照をインベントリ化します。contact_eventsについて、監査テーブルに元のマッピングを保持してから、有界なID範囲を更新します：

UPDATE contact_events AS e
SET contact_id = m.winner_id
FROM contact_dedup_map AS m
WHERE m.run_id = :run_id
  AND e.contact_id = m.loser_id
  AND e.id > :after_event_id
  AND e.id <= :batch_end_event_id;

再試行は冪等です。イベントが勝者を指すようになった後は、m.loser_idに一致しなくなります。有界なバッチごとにコミットし、コミット後にのみカーソルを永続化し、更新された行と参照監査行を照合します。子の一意性制約が衝突する場合は、事前に合意したマージまたは拒否ルールを適用します。制約を無効にして最終状態が有効になることを期待するような運用は避けてください。

親を削除する前に、すべての子テーブルに対してこのクエリがゼロを返す必要があります：

SELECT COUNT(*) AS remaining_loser_references
FROM contact_events AS e
JOIN contact_dedup_map AS m
  ON m.run_id = :run_id
 AND m.loser_id = e.contact_id;

ステップ5：短時間で再開可能なバッチでの敗者の削除

固定されたマップからIDのみを削除します：

WITH batch AS (
    SELECT loser_id
    FROM contact_dedup_map
    WHERE run_id = :run_id
      AND loser_id > :after_loser_id
    ORDER BY loser_id
    LIMIT 10000
)
DELETE FROM customer_contacts AS c
USING batch AS b
WHERE c.id = b.loser_id
RETURNING c.id, c.tenant_id, c.external_id;

10000は面接用の初期値であり、普遍的な最適値ではありません。測定されたロック時間、p99、WAL、レプリカ遅延、デッドタプル、オートバキュームからバッチサイズと遅延を調整します。RETURNINGの出力は削除の証拠となります。コミット後かつカーソル永続化前にプロセスがクラッシュした場合でも、バッチを再実行すると単に既に削除されたIDが検出されるだけであり、安全性は保たれます。

PostgreSQLでの大規模な削除は、デッドタプルとWALを生成します。通常のVACUUMを計画し、テーブルとインデックスの肥大化（bloat）を監視してください。テーブルを書き換えて強力なロックを取得するVACUUM FULLをデフォルトにしないでください。読み取りは引き続き利用可能ですが、リソースの飽和によりサービスのSLOに違反する可能性があります。

ステップ6：書き込みの収束と恒久的なガードレールの導入

一括修復だけでは、動き続けるターゲットを収束させることはできません。最終的な収束の前に、正規化された同一ビジネスキーに対する作成をシリアライズし、別のコピーを挿入する代わりに正規行を更新する単一の書き込み規約をデプロイします。すべてのAPI、インポーター、ジョブ、直接データベースライターがそれに従っていることを検証します。その証明が得られない場合は、最終的な差分クリーンアップとインデックス引き渡しの間、書き込みを停止します。

最後の重複スキャンがゼロを返した後、トランザクションブロック外でユニークインデックスを作成します：

CREATE UNIQUE INDEX CONCURRENTLY customer_contacts_business_key_uidx
ON customer_contacts (tenant_id, external_id);

CONCURRENTLYを使用すると、通常のインデックス構築ロックによって書き込みがブロックされるのを防ぐことができますが、処理量が増加し、トランザクションブロック内で実行できず、一意性違反によって失敗して無効なインデックスが残る可能性があります。有効性を検査し、データまたはライターの競合を診断し、ランブックに従って無効なインデックスを削除して再試行してください。IF NOT EXISTSが成功を証明すると決めつけないでください。有効になったら、スキーマガバナンスで制約セマンティクスが求められる場合、短時間の制御されたDDLステップで名前付きの一意性制約としてアタッチします。

すべての書き込みが停止され、実測された通常のインデックス構築が許容ウィンドウに収まる場合は、非concurrentなユニークインデックスの方がシンプルかつ高速です。concurrent形式は、検証された保護下の書き込みを継続する必要がある場合の適切なトレードオフです。SLOとリハーサルによってどちらを採用するかを選択します。

恒久的なライターは、データベースの一意性不変条件と明示的な競合ポリシーを使用する必要があります。クリーンアップ後に一意性違反をキャッチすることだけを唯一の冪等性設計とみなさないでください。同一キーで正規行を更新するのか、競合するペイロードを拒否するのか、レビューに回すのかを決定してください。

ステップ7：検証、監視、ロールバックウィンドウの終了

少なくとも以下のチェックを照合します：

• 修復後の総コンタクト数が、修復前のコンタクト数からアーカイブされた敗者数を引いた数と一致すること。
• GROUP BY tenant_id, external_id HAVING COUNT(*) > 1がゼロを返すこと。
• すべてのマップ勝者が存在し、すべての敗者が存在しないこと。
• 子イベント数が変化しておらず、残存する敗者参照がゼロで、孤立参照がゼロであること。
• サンプリングされた勝者が(updated_at DESC, id DESC)ルールおよびアーカイブされたペイロードと一致すること。
• 重複挿入が拒否されるか、宣言された更新ポリシーに従うこと。
• 完了したマッピング、付け替え、削除バッチを再実行しても変更行数がゼロであること。

バッチ処理速度、エラー、行ロック待ち、書き込みp50/p95/p99、WALバイト数、レプリケーション遅延、デッドタプル、オートバキュームの進行状況、マップ/アーカイブの増加、残存する敗者参照、残存する重複キー、ユニークインデックス構築の進行状況を監視します。テストされたロールバックウィンドウが終了するまで、アーカイブおよび参照監査データを保持します。その後初めて、一時的な書き込みガードと修復テーブルを保持ポリシーに従って削除します。

質の高い模範解答

「私は重複を同一の(tenant_id, external_id)を持つものと定義し、updated_at DESC, id DESCによって正規の行を選択します。ユニークIDによりタイブレークは決定論的になります。削除の前に、ベースライン件数の記録、ペイロード差異の検査、すべての参照のインベントリ化、復元可能なバックアップの取得を行い、ROW_NUMBERとFIRST_VALUEを使用して各敗者IDからその勝者への不変なrun_idマップを作成します。

敗者行と元の子マッピングをアーカイブします。次に、プライマリキーの短い範囲単位でcontact_eventsの付け替えを行います。各バッチはカーソルを進める前にコミットし、すでに移動されたイベントは敗者IDに一致しなくなるため再試行は冪等です。子テーブルの制約は有効なままとし、衝突が発生した場合は明示的なマージまたは拒否ルールに従います。すべての子テーブルで敗者参照がゼロと報告された後にのみ、固定マップのIDに基づいてコンタクトを削除し、有界なトランザクションとRETURNINGを証拠として使用します。

稼働レートは固定のバッチ数ではなく、ロック時間、リクエストp99、WAL、レプリケーション遅延、デッドタプル、オートバキュームに従って調整します。行数の保存、ビジネスキーごとの1行、敗者なし、孤立参照なし、イベント数不変、決定論的残存レコードのサンプリング、およびゼロ変更の再試行を検証します。

再発を防止するため、すべてのライターは同一の正規化ビジネスキーに収束する必要があります。検証されたライターガードまたは測定された停止の下で、最終的な差分クリーンアップを実行し、トランザクション外で(tenant_id, external_id)にconcurrentなユニークインデックスを構築します。失敗したconcurrent構築は無効なインデックスを残す可能性があるため、インデックスが有効であることを検証します。恒久的な書き込みパスは、宣言された規約に従って競合を更新、拒否、またはレビューします。ロールバック証拠と観察ウィンドウが完了するまでアーカイブを保持します。」

この回答は、不可逆な操作を明示的なゲートに依存させ、SQL、外部キー、ライターの挙動、およびデータベース制約を単一のビジネスキー規約の下で管理しています。

よくある間違い

• 重複を見つけた直後にDELETEを実行する → 参照、ペイロード、ロールバック証拠が失われる可能性があります → プレビュー、アーカイブ、マッピング、付け替え、検証を行ってから削除します。
• external_id単体でパーティションを区切る → 異なるテナントで同一の外部IDが統合されてしまいます → 完全なビジネスキー(tenant_id, external_id)を使用してください。
• updated_atのみで順序付けする → タイムスタンプが同値の場合、別の実行で異なる勝者が選択される可能性があります → 最終タイブレーカーとしてユニークなidを追加してください。
• RANKをrn > 1と併用する → 同値の最新行が両方ともランク1を受け取る可能性があります → 厳密に1つの残存レコードが必要な場合は、全順序を指定したROW_NUMBERを使用してください。
• 各バッチで勝者を再計算する → 同時変更によって対象がシフトし、監査性が失われます → バージョン管理された単一の敗者から勝者へのマップを固定してください。
• 子レコードの参照先を付け替える前に親を削除する → 外部キーによって削除が拒否されるか、カスケードによって有効な履歴が削除されます → まずすべての参照をインベントリ化して移行してください。
• 速度のために制約を無効化する → 修復処理によってサイレントな孤立レコードや子レコードの衝突が発生する可能性があります → 制約を有効に保ち、競合処理を定義してください。
• 10,000を適切なバッチサイズと決めつける → ハードウェアとワークロードによって安全なスループットが決まります → 小さく開始し、SLO、WAL、遅延、バキュームのメトリクスから適応させてください。
• クリーンアップ後に処理を終了する → 不具合のあるライターが再び重複を作成します → ライターを収束させ、PostgreSQLで一意性を強制してください。
• CREATE UNIQUE INDEX CONCURRENTLYが常に成功すると仮定する → 競合や重複によって無効なインデックスが残る可能性があります → 有効性を検査し、明示的な復旧手順に従ってください。
• 日常的なクリーンアップとしてVACUUM FULLを使用する → テーブルを書き換え、強力なロックを取得します → 通常のバキュームと肥大化を監視し、例外的なテーブル書き換えは別途スケジュールしてください。

フォローアップの質問と回答

フォローアップ1：なぜ1つのCTEで削除し、単一のトランザクションで完了させないのですか？

参照がなくライターが停止している小さな隔離されたテーブルであれば、CTEによるDELETEで十分な場合があります。2億行の場合、単一トランザクションではロックと古い行バージョンが保持され、大量のWALバーストが発生し、レプリカが遅延し、バキュームが複雑化し、全か無かのリカバリイベントが発生する可能性があります。固定マップと有界バッチにより、進行状況の把握、監査性、スロットリング、再開可能性が得られます。規模と依存関係の前提を証明した後にのみ、よりシンプルなクエリを使用してください。

フォローアップ2：2つの行が同じタイムスタンプを共有し、ペイロードが異なる場合はどうなりますか？

プロンプトのルールでは最大のidを選択するため、結果は決定論的ですが、決定論的であることはペイロードが意味的に正しいことを証明しません。修復前に競合するペイロードを測定し、リスクの高いフィールドをレビューまたはドメイン固有のマージルールにルーティングします。両方の行をアーカイブしてください。削除が開始された後にフィールドごとのマージをアドホックに考案しないでください。

フォローアップ3：external_idがnullになり得る場合はどうなりますか？

まず、nullが「不明で独立」を意味するのか、1つの重複値を意味するのかを定義します。PostgreSQLの一意性制約とインデックスはデフォルトでnullを個別のものとして扱うため、複数のnullキーが許可されます。nullを衝突させる必要がある場合はNULLS NOT DISTINCTを使用し、同一のセマンティクスで順位付けします。不明なコンタクトが独立している場合は、デフォルトの動作を維持するか、非nullのIDに対して部分ユニークインデックスルールを使用します。クリーンアップと制約は一致している必要があります。

フォローアップ4：書き込みの一時停止なしで修復を完全にオンラインで実行できますか？

最終スキャン前に、すべてのライターがビジネスキーに対してデータベースに裏付けられたシリアライゼーションまたは予約ルールを使用していることが証明されている場合にのみ可能です。そうであれば、履歴のクリーンアップを収束させ、concurrentなユニークインデックスを構築できます。レガシーインポーターや直接書き込みを行うライターがガードをバイパスすると、構築中に新たな重複が発生して失敗する可能性があります。シャドウテーブルと制御されたカットオーバーも選択肢の1つですが、二重書き込み、バックフィル、外部キー、ロールバックの複雑さが増すため、ダウンタイムSLOによって正当化される必要があります。

フォローアップ5：すべての外部キーと隠れた参照をどのように見つけますか？

参照先リレーションがcustomer_contactsである外部キーについてPostgreSQLのカタログを照会し、次にビュー、トリガー、CDCコンシューマー、検索インデックス、データエクスポート、アプリケーションスキーマを検査して保存されているコンタクトIDを探します。宣言された外部キーは強制可能な証拠を提供し、所有権ドキュメントとコード検索はアプリケーションレベルの参照をカバーします。削除ゲートには、検出されたすべてのコンシューマーとそれぞれの照合チェックが一覧表示されます。

フォローアップ6：イベントの付け替えが子テーブルの一意性制約に違反した場合はどうなりますか？

更新前に一時停止します。衝突は、親IDが収束した後に2つの子行が同一になることを意味します。それらが重複イベントなのか、新しいキーを必要とする個別の観測値なのか、データの競合なのかを定義します。衝突グループを実体化してアーカイブし、付け替え前に決定論的なマージまたは拒否ルールを適用します。子制約の削除はビジネスセマンティクスを変更してしまうため、修復計画としては不適切です。

フォローアップ7：長時間の実行中にテーブルが変更された後、選択された勝者が最新であったことをどのように証明しますか？

マップは、無制限の将来の書き込みに対してではなく、記録されたスナップショットまたは実行境界の下で勝者を証明します。以降の操作がマッピングされた正規行を更新するようにライターを保護し、ソースバージョンを記録し、一意性の引き渡し前に最終的な差分スキャンを実行します。ビジネスルールで以降の更新によるペイロードの置換が必要な場合は、勝者を更新し、別の物理コンタクトを作成しないでください。時間的な保証を監査レコードに明記してください。