A query written to handle "a host's uncrawled pages" ran fine against every host in the corpus except one. That one host had six times as many uncrawled pages as the next largest, and a query shaped around the assumption of an ordinary-sized host turned a routine operation into a hundred-second stall.
What the query was supposed to do
Part of the crawl pipeline periodically needs to pick the single best next page to crawl for each host in a batch — the highest-priority uncrawled URL, by a specific ranking. The natural way to write this against a set of hosts at once is a single query that ranks every uncrovered URL within each host, using a windowing function to number them by priority within their own host group, then keeps only the top-ranked row per group.
Where the assumption broke
That approach's cost scales with how many candidate rows exist per host, because ranking a group means the database has to sort the whole group before it can say which row comes first. For a typical host with a few hundred or few thousand uncrawled pages, that sort is cheap enough not to matter. One specific host in the corpus — a very large blogging platform — had 5.2 million uncrawled, non-boilerplate URLs sitting in its own backlog, roughly six times the size of the next-largest host in the entire corpus. Any batch that happened to include this one host meant fully sorting a 5.2-million-row group just to identify a single winning row.
What that actually cost in practice
A batch containing this host took over a hundred seconds to complete — the dominant contributor, at the time, to the database server's overall load climbing to a level well above the fleet's own configured safety threshold for an extended period. One host, out of many thousands the pipeline processes routinely, was single-handedly capable of degrading throughput for the whole system whenever it happened to come up in a batch.
The fix: ask the index for one row directly, instead of sorting everything
An existing index already supported looking up a single host's highest-priority uncrawled URL directly, without needing to rank anything. The fix replaced the whole-partition ranking approach with a targeted per-host lookup: for each host in the batch, ask the index directly for its top candidate, ordered the same way, limited to one row. Where the ranking approach has to materialize and sort an entire group before it can discard everything but the winner, a direct top-one lookup against a well-ordered index can stop as soon as it finds the first matching row — regardless of how many total rows exist behind it.
The actual before-and-after
Measured directly against the outlier host alone: the original ranking approach ran for over two minutes before being cancelled outright. The direct per-host lookup against the same data completed in 2.3 milliseconds — a scan that finds its answer near the start of an ordered index and stops, never touching the other 5.2 million rows behind it at all.
Why "works for every host we've tested" isn't the same as "works"
A query correct against every host in a representative sample can still carry an assumption that only breaks against a case the sample never included — not because the sample was chosen carelessly, but because the corpus itself contains one genuinely unusual outlier, six times larger than anything else, that a typical spot-check wouldn't necessarily surface. Fixing it required accepting that "ranking within a group" and "finding the single best row in a group" are different operations with very different costs at scale, even though they produce identical results for every ordinary-sized group — the distinction only matters once a group exists that's large enough to make sorting the whole thing actually expensive, and there's no way to know how large that has to be without a real outlier showing up first.
Leave a comment
Comments are reviewed before they appear.