How Removing One Query Cut a Page's CPU by 23%

The repo overview page is one of GitHub’s most visited pages. When I pulled up its flamegraph, one bar stood out immediately: a search query consuming 198ms on logged-in page loads.

We first tried to replace the query with a faster one. That turned out to be the wrong approach. The eventual fix was to stop running it.

Starting with the flamegraph

This investigation started during a group profiling session. We opened a production flamegraph for the repository overview page and found a 198ms bar on the critical path.

The method checked whether a repository had any published packages. It ran on every logged-in page load and queried a search service to answer a yes-or-no question.

Looking at the call

Zooming in showed the same search query running twice inside the method, once near the start and once near the end.

More importantly, the main page did not need the answer. The packages sidebar was already loaded by a deferred request, and its endpoint already handled repositories with no packages. The upfront check was left over from an older version of the page.

The first attempt (and why it failed)

Our first attempt replaced the search query with an EXISTS query against the packages table.

We used a Scientist experiment to compare the two implementations. The database query was 91% faster, but at 1% of production traffic its mismatch rate settled at 3-4%.

The mismatches all had the same shape: the database query returned false while the search service returned true. The query checked registry_packages directly, which only covered Maven packages. The search index covered npm, Docker, RubyGems, and the other package types too.

On a page serving tens of millions of logged-in requests each day, 4% was nowhere near safe enough. We reverted the experiment.

The real fix

At that point a colleague asked why we were replacing the query at all.

We could always render the deferred fragment and let its endpoint handle the empty state. Repositories with no packages would briefly show a loading skeleton before the empty state appeared. That was the only visible change.

The change added 12 lines and deleted 65.

In simplified terms, the change looked like this:

# Before: check upfront, conditionally render
if might_have_packages?        # <- 198ms search query
  render_deferred :packages
end

# After: always render the deferred fragment
render_deferred :packages      # the endpoint handles the empty state

The results

The improvement was limited to logged-in traffic, which confirmed that the search query only ran for authenticated users. Anonymous traffic stayed flat.

p50 latency for logged-in users: dropped from about 671ms to 516ms, a 23% improvement and roughly 155ms saved per request.

p99 latency: improved by 5.4%, saving roughly 73ms per request.

Call volume to the search service dropped by a third.

Why the flamegraph mattered

We would not have guessed that a packages sidebar check was the most expensive part of the page. It was not visibly broken, and by then the code no longer matched the way the page was rendered.

git blame confirmed that the check was added when the packages feature launched and the sidebar was still rendered inline. The page later moved to deferred loading, but the old guard remained on the main request path.

The failed experiment was still useful. It proved that a superficially equivalent database query was not equivalent in production, and it pushed us to question whether the work belonged there at all.

I later presented this work at Brighton Ruby. Watch the talk on YouTube.