Rewrote the hot-path feed query, twice

Situation

At Goodpods, a podcast app, the feed endpoint powers the For You home screen, so it is the hottest and most latency-sensitive path in the API. The interest-based part of the feed was one large multi-CTE Postgres query: a row_number() window partitioned per podcast, a keyword-weight subquery to score posts against the user’s interests, and NOT EXISTS subqueries to exclude bookmarks and already-served items. As the platform grew, that query became far too slow, sitting at 1900ms p50.

Task

Earlier monitoring work I had done ranked endpoints by how much misery they caused users, and the feed came out on top. Nobody assigned it to me; I took on the rewrite because the numbers said it was the biggest problem we had.

Action

I rewrote the feed query twice as traffic grew, first in July 2024 and again in May 2025. In 2024 I trimmed the feed payload, included episode data directly in stories, changed the algorithm to prioritise recently published episodes, and shipped a much faster v2 latest-episodes endpoint. In 2025 I went after the interest-based query itself. The key decision was to stop computing an exact windowed ranking on every request. Instead, the new version pulls the matching podcast IDs once, takes a random sample capped at twice the page limit, then does one indexed join ordered by publish date. That trades exact ranking for cheap sampling, which changes what users see. I judged it acceptable because the feed already filters to the most recently published episodes, so a random sample of matching shows still surfaces fresh, relevant content, and the old algorithm was simply too slow for a growing platform. The rewrite removed 141 lines from feed.py and added about 66.

Result

Feed p50 latency went from 1900ms to 110ms. The core endpoint stayed fast as the platform kept scaling, and the simpler query left far less code to maintain. The lesson I took from doing it twice: on a hot path, a good-enough answer computed cheaply beats a perfect answer you cannot afford.