A migration meant to clean up duplicate rows took six real, distinct failures to get right. None of the six were the same mistake repeated — each one revealed a different way "deduplicate this data" is harder than it sounds once the data is large and the duplicates aren't isolated from each other.
What the migration had to do
A collation change (documented separately) had let a unique constraint silently stop enforcing uniqueness, minting duplicate rows for any hostname affected by the mismatch. Fixing it meant finding every duplicate, picking one survivor per group, and repointing every other table that referenced the doomed rows — links, statistics, crawl-queue entries — onto the survivor before deleting the duplicates themselves. Simple in concept. The corpus behind it was not small: hundreds of millions of link rows, tens of millions of URLs, real foreign-key relationships tying everything together.
Failure one: duplicates colliding with each other, not just with the survivor
The first version of the logic checked each doomed row's outgoing and incoming links against the one row being kept — a straightforward pairwise comparison. It missed an entire category: two different doomed rows, once both repointed onto the same survivor, could end up holding links that now collided with *each other*, never having conflicted with anything before the repoint began. A unique-constraint violation on rows that had nothing to do with the original survivor at all. The fix required an extra deduplication pass across the doomed set itself, before ever touching the survivor.
Failure two: the same shape of bug, one level up
Once that was fixed, a second unique constraint hit the identical problem from a different angle: the logic for finding candidate URLs to repoint only checked against the one already-established canonical host, missing the case where two entirely different non-canonical hosts in the same duplicate cluster each held a URL that would collide with each other, but only after both got mapped onto the shared canonical host. Fixing this one properly meant a real redesign, not another patch: gather every URL across the whole duplicate cluster at once, pick exactly one survivor per group using a consistent rule, then build a complete mapping from everyone else onto that survivor — replacing a pairwise check with a check that actually accounts for the whole cluster at once.
Failure three: a fifteen-hour stall from a setting left on too long
Fixing the original collation bug required disabling certain query-planning strategies for one specific comparison, where the old collation's index couldn't be trusted. That setting stayed active for the rest of the migration, not just the one query that needed it — forcing full sequential scans of a table with hundreds of millions of rows on every subsequent join, even though those later joins used plain integer columns with no collation sensitivity at all. The fix wasn't a new insight about the data; it was noticing the setting had never been turned back off.
Failure four: a planner guess that was a thousand times wrong
Even after re-enabling normal query planning, one join against a freshly created temporary table still chose a full scan over an index — the planner's row-count estimate for that brand-new table was off by roughly a thousand times, even after explicitly refreshing statistics on it. Freshly created tables don't always carry statistics good enough for the planner to trust automatically. The fix scoped a targeted planner override tightly around just the specific joins affected, confirmed by checking the actual query plan switched to an indexed lookup instead of a full scan.
Failure five: cleaning up after failure
Every failed attempt left behind partially built index fragments — real, on-disk artifacts from indexes that never finished validating. Left in place, they interfered with validating the next attempt's own indexes, meaning each retry needed its own cleanup step first, each one run as its own standalone operation since dropping an invalid index this way can't happen inside a transaction alongside anything else.
What finally ran clean
The sixth attempt completed in about three minutes. Every earlier attempt after the correctness fixes were in place had still been slow or wrong for a performance reason layered on top of the correctness ones — getting the logic right and getting the query fast turned out to be two separate problems, solved in sequence rather than at the same time.
Why six is the honest number, not five or seven
Nothing about this migration was rushed or careless. Each failure surfaced a real property of the data — duplicates that formed clusters rather than isolated pairs, a debugging setting with a narrower intended scope than its actual lifetime, a planner making a reasonable-looking guess that happened to be wrong by three orders of magnitude — that wasn't visible until the previous fix exposed it. A migration this size, against data this interconnected, finding its real edge cases one at a time rather than all at once isn't a sign of a sloppy process. It's what actually happens when a fix gets tested against the real data instead of a simplified mental model of it.