題幹與適用情境
PostgreSQL 的 customercontacts 資料表有 2 億列。舊匯入程式的重試邏輯替同一個 (tenantid, externalid) 建立了多筆紀錄。依 updatedat 判斷，最新紀錄應保留；時間相同時，由 id 最大的紀錄勝出。contactevents.contactid 外鍵可能指向任一副本，如果在遷移參照前就刪除淘汰列，操作會因外鍵失敗或遺失原有關聯。
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
)請設計一套 PostgreSQL 修復方案：每個業務鍵只保留一筆確定的規範紀錄，所有事件都能保留，破壞性操作採用有界交易，可稽核、可重試，最後由資料庫強制保證唯一性。修復期間讀取流量必須可用；允許在最後收斂時受控暫停寫入，但暫停時間必須經過測量，不能憑空假設。
2 億列與最後寫入暫停是本題設定。這是一道高難度的資料工程與 SQL 題，因為視窗函式只負責選出保留列；完整回答還要處理參照完整性、並行、回復、儲存健康與後續寫入。目前公開 SQL 面試資料仍將 ROW_NUMBER 去重與確定性平手規則列為明確題型。題目沒有可驗證的公司歸屬，因此 companyName 為 null。
面試官考察點
第一項是候選人能否在寫 DELETE 前定義「重複」與「最新」。業務鍵是 (tenantid, externalid)，id 識別的是實體資料列。完整順序 updatedat DESC, id DESC 即使遇到相同時間，也只會產生一個勝出者。RANK 可能保留多筆平手資料；ROWNUMBER 會把唯一一列編號為 1。
第二項是能否控制破壞性邊界。正式環境等級的回答會先預覽數量與樣本，固化一份不可變的「淘汰列到保留列」對應，保留淘汰列或可還原快照，再依主鍵刪除。如果寫入仍持續，卻在每個批次中個別重算排名，目標集合可能改變，整個修復也很難說明。
第三項是參照與交易正確性。父紀錄刪除前，所有子表參照都必須從淘汰列遷移到保留列。子表本身的唯一規則可能讓遷移發生衝突，因此只說「更新所有外鍵」並不完整，還需要參照清單與衝突策略。每個批次都應具備冪等性、時間短、可觀測，並能安全停止。
最後看清理是否解決根因。只刪除今天的重複列，明天舊匯入程式仍可再次製造問題。持久不變量應落在唯一約束或唯一索引，並明確定義空值與正規化口徑。候選人也應區分正確性證據與效能證據：重複業務鍵為零、孤兒參照為零可證明語意；EXPLAIN、鎖定等待、WAL 速率、複寫延遲、死亡 tuple 與 autovacuum 狀態則決定執行速率是否可接受。
回答前需要釐清的問題
- 哪些欄位定義同一個業務實體？ 本題使用精確的
(tenantid, externalid)。大小寫折疊、去除空白或 Unicode 正規化會形成另一套業務鍵，必須在修復前確認。 - 哪一列勝出？
updated_at最大，其次id最大。兩筆資料時間相同時，單獨使用時間戳記不是完整順序。 - 淘汰列是否可能包含獨有資料？ 如果 payload 需要合併，「保留最新」就不夠。本題把最新 payload 視為權威，同時封存淘汰列供複核。
- 哪些資料表參照
customercontacts.id？ 要檢查已宣告的外鍵與應用層參照。題目展示了contactevents，實際修復還要搜尋系統目錄與維護責任文件。 - 遷移參照會不會造成子表重複？ 如果子表有
UNIQUE(contactid, eventtype, created_at)，收斂後兩筆等價事件可能衝突。更新前先決定合併、保留或拒絕策略。 - 修復期間是否允許寫入？ 受保護的寫入可與歷史批次同時執行，但最後的重複掃描與唯一性切換必須依賴經過驗證的資料庫層寫入保護，或經測量的寫入暫停。部分寫入方可繞過的應用約定不構成保證。
- 回復要求是什麼？ 在驗證與回復窗口結束前，保留備份或完整淘汰列封存，以及子表原始參照對應。只有淘汰列到保留列的對應，無法還原已丟棄的 payload。
- 允許多少執行負載？ 批次大小是控制量，不是固定答案。先從小批次開始，再依鎖定等待、寫入 p99、WAL、複寫延遲、死亡 tuple 與 autovacuum 進度節流。
- 空值如何處理？ 本題兩個業務鍵欄位都不允許空值。如果允許空值且空值也應視為重複，PostgreSQL 唯一性需要
NULLS NOT DISTINCT；預設唯一語意允許多個空值。
30 秒回答框架
「我會先固定業務規則：(tenantid, externalid) 相同就是重複，依 updatedat 遞減、id 遞減選出勝出列。先預覽排名結果，封存淘汰列，並依一個 runid 固化每個淘汰 ID 到勝出 ID 的不可變對應。接著以短交易冪等記錄並遷移所有子表參照，確認沒有參照仍指向淘汰列後，才依主鍵分批刪除。每個階段都核對數量、payload 樣本、重複業務鍵與孤兒參照。最後在已驗證的寫入保護或測量過的寫入暫停下完成最終增量清理並建立唯一索引，讓所有寫入方遵守同一業務鍵規則。我會依正式環境指標動態節流，並保留封存直到回復窗口結束。」
這套架構涵蓋選列、相依順序、破壞性控制、收斂與預防。SQL 是用來落實這些決策，無法取代它們。
分步深入解答
第一步：先確定不變量、負責人與執行邊界
修改資料前先寫出修復後的條件：
- 每個
(tenantid, externalid)只存在一筆 contact； - 依
(updated_at, id)排序，它的 ID 最大； - 修復前的每筆
contact_events紀錄仍存在，並指向勝出列； - 沒有已宣告或應用層參照指向已刪除 ID；
- 已完成批次再次執行時修改零列；
- 新寫入無法再次建立重複業務鍵。
為這次執行分配 run_id、負責人、來源快照或備份、開始時間、程式碼版本、批次游標、監控儀表板、暫停門檻與回復截止時間。記錄修復前總列數、不同業務鍵數、重複鍵數、淘汰列數與事件數，並依 tenant 保留控制總數，避免全域總數掩蓋單一租戶的資料遺失。
第二步：先預覽確定性排名
先執行唯讀查詢，並檢查數量與 payload 差異：
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;本題要求只保留一個勝出者，因此適合 ROWNUMBER。如果未加入 id 形成確定性的平手規則，重複執行可能選出不同勝出者；而 RANK 或 DENSERANK 在排序值相同時可能讓多列得到相同名次。DISTINCT 只能移除查詢結果中完全相同的選定欄位，無法依業務偏好保留一整筆規範紀錄。
第三步：固化不可變的淘汰列到保留列對應
不要替子表遷移與父表刪除分別重算勝出列。在已確認的執行邊界內只做一次決策並固化。以下程式碼中的 :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;contactdedupmap 應有 PRIMARY KEY(runid, loserid)、淘汰列與勝出列不可相同的檢查，以及 (runid, winnerid) 索引。使用同一 run_id 封存完整淘汰列，或保留經過還原演練的時間點還原路徑。檢查每個勝出列都存在，同一次執行中的勝出列不會同時成為淘汰列，每個淘汰列只對應一次，對應筆數與測得的淘汰列數一致。
對 2 億列執行排名可能需要大規模掃描與排序。先在接近正式環境的資料上執行 EXPLAIN，確認暫存空間與工作負載影響，再安排執行與節流。覆蓋索引也許能改善經過測量的計畫，但索引本身同樣昂貴，不能在沒有證據時直接指定。
第四步：刪除父紀錄前先遷移所有參照
先盤點全部參照。對 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;第五步：以短交易、可續跑的方式刪除淘汰列
只刪除固化對應中的 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、從庫延遲、死亡 tuple 與 autovacuum 調整批次大小和間隔。RETURNING 結果可作為刪除證據。如果程序在交易提交後、游標儲存前當機，再執行同一批只會遇到已刪除的 ID，仍然安全。
PostgreSQL 大量刪除會產生死亡 tuple 與 WAL。應安排一般 VACUUM 並監控資料表和索引膨脹；不能預設使用 VACUUM FULL，它會重寫資料表並持有強鎖。讀取請求雖維持可用，資源飽和仍可能違反服務 SLO。
第六步：讓寫入收斂，並建立持久防線
歷史修復無法自動收斂持續移動的目標。在最終收斂前，部署統一寫入契約：同一個正規化業務鍵的建立必須序列化，並更新規範紀錄，不能繼續插入副本。確認每個 API、匯入程式、工作與直接連線資料庫的寫入方都遵守。無法證明時，就在最終增量清理與索引切換期間暫停寫入。
最後一次重複掃描回傳零後，在交易區塊外建立唯一索引：
CREATE UNIQUE INDEX CONCURRENTLY customer_contacts_business_key_uidx
ON customer_contacts (tenant_id, external_id);CONCURRENTLY 可避免一般建立索引的鎖阻擋寫入，但工作量較大，不能放在交易區塊內，也可能因唯一性衝突失敗並留下無效索引。應檢查有效性，定位資料或寫入競爭，依執行手冊刪除無效索引後重試；不能把 IF NOT EXISTS 當成成功證明。索引有效後，如果 schema 治理需要約束語意，可在受控的短 DDL 步驟中把它掛接為具名唯一約束。
如果所有寫入都已暫停，而且測量證明一般建立方式可在允許窗口內完成，非並行唯一索引更簡單也更快。只有經過驗證的受保護寫入仍需繼續時，並行形式才是合適取捨；最終由 SLO 與演練結果決定。
最終寫入流程要依賴資料庫唯一性，並明確定義衝突策略。清理後只捕捉唯一衝突不等於完整的冪等設計；要決定同鍵寫入是更新規範列、拒絕衝突 payload，還是進入人工複核。
第七步：驗證、觀察並關閉回復窗口
至少完成以下對帳：
- 修復後 contact 總數等於修復前總數減去已封存淘汰列數；
GROUP BY tenantid, externalid HAVING COUNT(*) > 1回傳零；- 每個對應中的勝出列存在，每個淘汰列都已消失；
- 子表事件總數不變，剩餘淘汰列參照為零，孤兒參照為零；
- 抽樣勝出列符合
(updated_at DESC, id DESC)規則，並能與封存 payload 對照； - 重複插入會被拒絕，或依宣告的更新策略處理；
- 已完成的對應、遷移與刪除批次重跑時修改零列。
監控批次速率、錯誤、資料列鎖定等待、寫入 p50/p95/p99、WAL 位元組、複寫延遲、死亡 tuple、autovacuum 進度、對應與封存成長、剩餘淘汰列參照、剩餘重複業務鍵，以及唯一索引建立進度。在經過測試的回復窗口內保留封存與參照稽核資料，之後再依保留政策移除暫時寫入保護與修復表。
高品質示範回答
「我會把重複定義為 (tenantid, externalid) 相同，並依 updatedat DESC, id DESC 選規範列；唯一 ID 讓相同時間也有確定結果。刪除前先記錄基準數量、檢查 payload 差異、盤點所有參照、取得可還原備份，再用 ROWNUMBER 與 FIRSTVALUE 為一次 runid 建立每個淘汰 ID 到勝出 ID 的不可變對應。
我會封存淘汰列與子表原始參照，再依主鍵短區間遷移 contact_events。每個批次提交成功後才推進游標；已遷移的事件不再符合淘汰 ID，因此重試具備冪等性。子表約束始終啟用，發生衝突時執行明確的合併或拒絕規則。只有所有子表都回報淘汰列參照為零，才依固化對應中的 ID 分批刪除 contact，並把 RETURNING 當成操作證據。
執行速率依鎖定時間、請求 p99、WAL、複寫延遲、死亡 tuple 與 autovacuum 調整，不能直接套用固定批次。我會驗證列數守恆、每個業務鍵僅一列、淘汰列與孤兒參照為零、事件數不變、抽樣勝出規則正確，以及重試零變更。
為防止復發，所有寫入方必須收斂到同一個正規化業務鍵。在已驗證的寫入保護或測量過的暫停下，我會完成最終增量清理，並在交易外替 (tenantid, externalid) 建立並行唯一索引。並行建立失敗可能留下無效索引，因此必須檢查有效性。永久寫入流程再依既定規則更新、拒絕或複核衝突。封存會保留到回復證據與觀察窗口都完成。」
這段回答讓每個不可逆操作都依賴明確閘門，並把 SQL、外鍵、寫入行為與資料庫約束統一在同一業務鍵契約下。
常見錯誤
- 發現重複後立刻執行
DELETE→ 參照、payload 與回復證據可能遺失 → 先預覽、封存、建立對應、遷移、驗證，再刪除。 - 只依
externalid分區 → 不同租戶的相同外部 ID 會被合併 → 使用完整業務鍵(tenantid, external_id)。 - 只依
updated_at排序 → 相同時間可能在另一次執行中選出不同勝出列 → 把唯一id當成最後平手規則。 - 使用
RANK再刪除rn > 1→ 平手最新的多列都可能得到 1 → 需要恰好一個保留列時，使用完整順序的ROW_NUMBER。 - 每個批次都重算勝出列 → 並行變化會移動目標並破壞稽核性 → 固化一份有版本的淘汰列到勝出列對應。
- 遷移子表前刪除父紀錄 → 外鍵會拒絕刪除，或串聯刪除有效歷史 → 先盤點並遷移全部參照。
- 為了速度關閉約束 → 修復可能靜默產生孤兒或子表衝突 → 維持約束啟用並定義衝突處理。
- 把 10000 當成正確批次 → 硬體與負載決定安全吞吐量 → 從小批次開始，依 SLO、WAL、延遲與 vacuum 指標調整。
- 清理後就結束 → 錯誤寫入方會再次製造重複 → 收斂寫入方，並由 PostgreSQL 強制唯一性。
- 假設
CREATE UNIQUE INDEX CONCURRENTLY一定成功 → 資料競爭或重複列可能留下無效索引 → 檢查有效性並執行明確的回復流程。 - 把
VACUUM FULL當成例行收尾 → 它會重寫並強鎖資料表 → 監控一般 vacuum 與膨脹，特殊重寫另行排程。
追問及應對
追問一：為什麼不使用一個 CTE，在單一交易中一次刪完？
對於沒有參照、已暫停寫入的小型資料表，一個 CTE DELETE 可能足夠。面對 2 億列，單一交易可能長期保留鎖與舊版本，產生大量 WAL 突發，拖慢從庫，增加 vacuum 難度，並形成一次全有或全無的回復事件。固化對應加有界批次可提供進度、稽核、節流與續跑能力。只有證明規模與相依假設成立後，才選擇更簡單的一次性查詢。
追問二：兩列時間相同但 payload 不同怎麼辦？
題目規則選擇 id 最大者，因此結果確定；但確定性不代表 payload 在業務上正確。修復前先統計衝突 payload，把高風險欄位交給人工複核或領域合併規則，並封存兩列。不能在刪除已開始後臨時創造欄位層級的合併邏輯。
追問三：如果 external_id 可以是空值呢？
先確認空值代表「未知且彼此獨立」，還是一個應去重的值。PostgreSQL 唯一約束與索引預設把空值視為不同，因此允許多個空鍵。如果空值也應衝突，使用 NULLS NOT DISTINCT，排名也必須採用相同口徑；如果未知 contact 應獨立存在，就保留預設語意，或只替非空 ID 建立部分唯一規則。清理與約束必須一致。
追問四：能否完全線上修復，不暫停寫入？
可以，但前提是所有寫入方都已證明使用資料庫支援的業務鍵序列化或占位規則，之後才能進行最終掃描。歷史清理可逐步收斂，再並行建立唯一索引。如果舊匯入程式或直接寫入可繞過保護，建立期間仍可能出現新重複並導致失敗。影子表加受控切換也是一種方案，但會增加雙寫、回填、外鍵與回復複雜度，只有停機 SLO 值得時才採用。
追問五：如何找出所有外鍵與隱藏參照？
查詢 PostgreSQL 系統目錄，找出所有參照 customer_contacts 的外鍵；再檢查 view、trigger、CDC 消費者、搜尋索引、資料匯出與應用 schema 中儲存的 contact ID。已宣告外鍵提供可強制的證據，維護責任文件與程式碼搜尋則涵蓋應用層參照。刪除閘門要列出每個發現的消費方及其對帳檢查。
追問六：遷移事件時違反子表唯一約束怎麼辦？
先暫停更新。衝突表示兩筆子紀錄在父 ID 收斂後變得相等。要確認它們是重複事件、需要新鍵的獨立觀測，還是資料衝突。先固化衝突群組並封存，再依確定的合併或拒絕規則處理，然後遷移參照。移除子表約束會改變業務語意，不是修復方案。
追問七：長時間修復期間資料表又發生變化，如何證明勝出列仍是最新的？
對應只能證明它在記錄的快照或執行邊界下勝出，無法涵蓋無限期的未來寫入。應保護寫入方，讓後續操作更新對應中的規範列，記錄來源版本，並在唯一性切換前執行最終增量掃描。如果業務規則要求後來的更新取代 payload，就更新勝出列，不能再建立另一筆實體 contact。稽核紀錄要明確寫出這項時間保證。