Billboards near a coffee shop: making proximity search fast in ClickHouse

🎟️ Please RSVP here to join-in for this conversation in-person.

Abstract
We build planning software for out-of-home advertising: billboards, and the places people want to be near. Almost everything comes back to one query, show me the billboards near our coffee shops, and it sounds simpler than it is. There are about three million billboards on one side, twenty million places on the other, and “near” is not one number, it changes with every search. Big on both sides, with a radius that keeps moving, it is the kind of query an analytics engine is worst at, and every easy way to speed it up fell apart.
This is the story of fixing that one query. We tried the obvious things first, and each one hit a wall. What actually worked was not a faster query, it was a different question: we changed what we were asking, and ClickHouse happened to have the one thing that made the new question cheap. I will walk through what we tried, why it broke, the idea that fixed it, and the two lessons I would take to any hard join.

Takeaways

• Know when, and how much, to precompute. The obvious precompute can cost more than it saves.
• Change the question, not the query. Often the fix is not a faster query but a different one.

Who is this for
Data and platform engineers who serve queries at scale, anyone doing geospatial or proximity search, and anyone who likes a good battle story. A little SQL helps.

About the Speaker
Amaan is an engineer with four years of experience. He started a fullstack engineer, then moved into large-scale data, building and running data workflows on Spark, Airflow and ClickHouse, in Scala and Python. He now works on data infrastructure for an ad-tech analytics platform.