At the end of part one I promised route search: "how do I get from A to B?", with up to two changes and a short walk between stops where that beats waiting. It already existed on the server, "written as, you guessed it, another recursive query", and the app didn't call it yet.

This autumn the app started calling it. As of today, route search no longer runs in SQL. This is the story of the six weeks in between, and it's less about buses than about what happens when a query that works on a laptop meets a database that runs somewhere else.

Walking is a bus line too

The network from part one is stops and edges: "the A goes from C/ Mayor to Petanca in 1 minute 40". To plan a trip you also need to walk: from where you are to a stop, from one stop to another to change lines, and from the last stop to where you're going.

The first and the last start or end somewhere that isn't a stop, so they come from a walking router. The middle one is a trick. A small SQL script looks at every pair of stops less than 500 metres apart that doesn't already have an edge, and adds one on a line called walk():

SELECT a, b, 'walk()' AS line,
       PRINTF("%g", ROUND(distance * 3600 / 3)) || 's' AS duration -- 3 km/h walking speed with "obstacles" (e.g. traffic lights)
FROM ( … the distance between two stops, as great-circle maths in SQL … )

Three kilometres an hour sounds slow until you remember traffic lights, roundabouts and the August sun. After that, a walk is just another edge with a duration, and the router doesn't need to know the difference. There are more walking edges in the table than bus edges: about six hundred against four hundred, as of September.

The query

Route search is a cousin of the arrival-time query from part one, with ambitions. It starts at one stop, follows edges and remembers where it has been. It is trimmed a little here, but this is the version that was running until today:

WITH RECURSIVE dfs(station_id, path, path_lines, current_line, depth, line_changed, stations_on_current_line) AS (
  SELECT e.`from`, CAST(e.`from` AS TEXT), CAST(e.`line` AS TEXT), e.`line`, 0, 0, 1
  FROM route_edges e WHERE e.`from` = {:from}
  UNION ALL
  SELECT e.`to`,
         dfs.path || CASE WHEN e.`line` != dfs.current_line THEN ';switch()' ELSE '' END || ';' || e.`to`,
         …,
         e.`line`, dfs.depth + 1,
         CASE WHEN e.`line` = dfs.current_line THEN dfs.line_changed ELSE dfs.line_changed + 1 END,
         CASE WHEN e.`line` = dfs.current_line THEN dfs.stations_on_current_line + 1 ELSE 1 END
  FROM dfs JOIN route_edges e ON e.`from` = dfs.station_id
  WHERE dfs.depth < {:depth}
    AND instr(dfs.path, e.`to`) = 0
    AND (e.`line` = dfs.current_line
         OR (dfs.line_changed < 2 AND dfs.stations_on_current_line >= 2))
)
SELECT station_id, path_lines, depth, path FROM dfs
WHERE station_id = {:to} AND stations_on_current_line >= 2
ORDER BY depth, <number of lines> LIMIT {:limit};

Every row is a path in progress: the stops so far as a string, the lines used, how many times it changed. The rules are all in the WHERE:

  • Never visit a stop twice. instr(path, stop) = 0 is a substring check on the path. It's crude: once a path has passed panorama-2, it counts panorama as visited too. It mostly works.
  • At most two changes.
  • Ride at least two stops before changing. Without that rule the search happily hops on a bus for one stop to change at the next.

The router doesn't know which stop you'll walk to, either. It takes the three nearest stops to where you are and the three nearest to where you're going: nine pairs. For each pair it asks for paths up to 5 edges deep. If that finds fewer than four routes, it tries 10, then 15, all the way up to 50.

The {:from} placeholders are PocketBase's syntax, and so is {{route_edges}}, which I've written as plain route_edges above. On our server PocketBase fills them in itself. In the Worker, a few lines of code do the same, so our query files run in both places. I was quite proud of that. Route search itself, as it happens, only ever ran in the Worker.

It worked. Then we pointed the app at it

The query first appeared in October 2024, as a file I ran by hand, and got an endpoint in November, in a commit called "navigation alpha". The app never called it.

In August our Flutter developer turned it into a proper API method, one that starts from two points on the map instead of two stop ids. In October the app's navigation screen, built over the summer on mock data, was wired up to it, and the development builds started asking real questions, from real places, to real destinations. The problems started the same week:

  • Late October. The first optimisation: a path has to ride at least two stops before it may change. It's a sensible rule for people, and it cuts the search down.
  • Late November. The method got a route on the production API, and a day later D1 was running out of memory. Each search sent all nine stop pairs at once, and the fix was to send them one after another, with a comment that says what we thought was going on: "search paths SEQUENTIALLY to avoid D1 out-of-memory errors from too many concurrent recursive CTE queries."
  • December 1. A theory: the recursion itself was eating the memory, going deep down one branch after another. So a breadth-first version of the query was written, under a comment that reads "BFS approach using iterative queries instead of recursive CTE. This avoids the memory explosion of deep recursion." The same commit widened the search to the four nearest stops at each end, sixteen pairs, and capped the depth at 30. None of it changed anything we could see.

D1 is SQLite on Cloudflare's side, and it has limits the laptop doesn't: a query gets 30 seconds, and far less memory than a laptop, though Cloudflare doesn't say exactly how much. Our query was fine on a small network and a short trip. Across town, ten edges deep, it wasn't.

As of today: the graph lives in the Worker

So today we stopped asking SQL.

The stops and edges now go into a snapshot, a protobuf file in R2. The first time a Worker needs it, it loads that file into graphology, builds a plain map of which stops lead where, and keeps it in memory for the rest of its life. The search is a loop with a stack: take a path, extend it by every allowed edge, push those. The rules are the same three as in the SQL. D1 is still there for the cheap questions, like "which stops are near this point" and "when is the next bus", which it answers in milliseconds.

The new performance logs say a search now takes 0.00 ms. I'm choosing to believe them.

The lesson I'm taking away: SQL was the right tool for "add up a slice of a vector" and the wrong one for "list every way to get from here to there". A thousand edges is a tiny table, and a query that fits on one screen is a tiny query. The number of paths is neither.

What's next

  • A nightly job to rebuild the snapshot from the database, so nobody has to remember to.
  • Buses in a real routing engine. Walking directions already come from Valhalla, a routing engine we host ourselves. It can do public transport too, if you feed it a GTFS file: the standard timetable format, which, as far as we know, nobody publishes for Torrevieja.
From September 2026. This post is dated to the day route search left D1, and written as it looked then. With some hindsight, and finally some measurements:
  • We measured it, nine months late. We rebuilt the September 2025 network from git and replayed the search on a local D1, which reports the same rows-read counter D1 bills on. One query from one stop, five edges deep, read a median of about 5,800 rows. Ten edges deep, about 556,000, and 51.8 million from the town centre. One user search read a median of about 2.2 million rows and an average of about 4.1 million, and that's a lower bound. The free plan's 5 million rows a day covers one or two searches. On the paid plan, a thousand searches a day would have cost about $99 a month on top of the $5. As far as we can tell nobody ever paid it: no store release shipped the search before it moved. The last three days, with sixteen stop pairs instead of nine, made each search roughly 1.8 times as expensive.
  • It was never a depth-first search. A recursive CTE in SQLite keeps its pending rows in a queue unless you give the recursive part an ORDER BY, so ours was breadth-first all along. That's almost certainly the out-of-memory: breadth-first holds the whole frontier at once, and ten edges from the bus station that's about 650 MB, several times the 128 MB a Cloudflare isolate gets, the nearest thing to a limit Cloudflare documents. And the December 1 rewrite never ran. It edited a rendered copy of the query that we kept for debugging, while the Worker loads the template. The in-memory search that replaced them both is, fittingly, the first real depth-first search in this post.
  • Walking did it. Every walking edge is on the same "line", walk(), so walking from stop to stop to stop never counted as a change, and the two-stops rule meant a transfer on foot had to take at least two walking hops. Inside the mesh of walking edges the search could wander freely. Taking the mesh out shrinks a ten-deep query by a median factor of 220. The table itself was never the problem: SQLite builds a temporary index, so each query reads it in full only twice. In August 2026 walking became a transfer, like a change of bus.
  • The logs lied. The next day the in-memory search got stuck, so it got a three-second timeout. That timeout could never fire. Workers freeze the clock while code runs without doing any I/O, as a defence against timing attacks, so Date.now() and performance.now() return the same value however long the loop spins. Every "0.00 ms" in our logs was that frozen clock. In August 2026 one request burned 32.5 seconds of CPU before it died. It now stops after a fixed number of steps, shared across the whole request.
  • The nightly job arrived two days later and wrote navigation-graph1.pb. The Worker reads navigation-graph.pb. So the nightly rebuild didn't reach production for eight months, until August 2026.
  • Buses did move to Valhalla, with a GTFS feed we generate ourselves, in August 2026. That's part five of this series.
  • And the vector lookup that part one said "any database answers in no time"? It turned out to be about 80% of everything D1 read. The table it filters had no index at all, so every poll from the app scanned it whole. Late at night it could even say there were no more buses: it picked the sixteen nearest departures before splitting them into past and future, and at half past eleven, on a line with a bus every ten minutes, all sixteen were in the past. Since the end of September 2026, each line's board is a small precomputed file in Cloudflare KV, rebuilt whenever the timetable changes, and a request reads about two rows.
  • And then the next one. With the boards gone, Cloudflare's list of top queries had a new leader: station search, at about four million rows a day. It ran a view that joins every stop, line and edge and groups them, rebuilt whole on every keystroke. Second, at 1.25 million, was the freshness check the board fix had just added to every board request. In October the visible network became one more file in KV, and search, the nearest stop and the map now read it in plain JavaScript. The old SQL stays in the tests, as the answer the new code has to match row for row.
  • And a dashboard to keep an eye on it. Our Grafana now reads the numbers behind Cloudflare's D1 page live from its analytics API: rows read per query, per hour, and per execution. That last one doesn't move with traffic, only when a query gets better or worse. The board also explained why the nearest-stop query had barely shown up in the rankings. Its coordinates were pasted straight into the SQL text, by the same template trick I was so proud of above, so every position counted as a query of its own. And its first week already shows the board fix: