243 round trips to find a city
The whole geocoder runs in your browser. You type an address, you get a rooftop coordinate, and no server ever sees your query — the gazetteer it resolves against is a SQLite database sitting on a CDN, and the page reads it with HTTP range requests, a few kilobytes at a time. It's a lovely trick. We were proud of it. Then we counted the requests it took to find a single city, and the number was 243.
So the questions for the day: why does looking up one name cost 243 round trips? What goes wrong when you search a database you can only read a slice at a time? And how do you get a global gazetteer — every country, region, county, and city we resolve against — down to about a dozen reads without putting a server back in the loop?
Reading a database through a straw
Here's the setup, because the constraint is the whole story. The admin database is 2.6 GB. Nobody is downloading 2.6 GB to type "Chicago" into a search box. What makes this work at all is sql.js-httpvfs: a SQLite build whose file-reading layer, instead of reading from disk, issues HTTP Range requests against the file on the CDN. SQLite asks for "bytes 4,210,688 through 4,276,224," the VFS turns that into a range GET, the CDN hands back that one 64 KB chunk, and the query engine carries on as if the file were local. You're reading a multi-gigabyte database through a straw, and most of the time you never touch 99.99% of it.
For a B-tree lookup on a primary key, this is close to magic. The engine walks root → interior → leaf, and each hop is one range request. Three or four reads and you're holding your row. The straw is wide enough.
Why search scatters
The trouble started because we weren't doing a primary-key lookup. We were doing a name search, and to make name search fast inside SQLite you reach for FTS5 — full-text search, an inverted index. For each token it stores a posting list: every row that contains it. Search becomes "go read the posting list for this token," which is exactly the kind of thing a real database does well when the disk is right there.
The disk is not right there. It's at the end of a straw. And an FTS posting list for a common name is not one tidy run of bytes — it's a chain of pages scattered through the file, because the index grew as the data loaded and the postings landed wherever there was room. Reading one posting list means following that chain, and every hop in the chain is its own range request. A common place name dragged in a long chain. Then the candidate rows themselves lived somewhere else again. The reads piled up: 243 of them, about 16.5 MB, to answer one query.
The maddening part came when we tried to tune it. The VFS lets you widen the straw — ask for bigger chunks per request, fetch 64 KB or 256 KB at a swing. We widened it, expecting fewer round trips. We got more bytes and barely fewer trips, because the postings were scattered, so a bigger chunk mostly bought us neighbours we didn't want. We'd been getting away with it on a "slim" database — a stripped 53 MB build with three countries in it — and the slim build was cheap for the dullest possible reason: it was small, so even a scattered search couldn't scatter very far. That's not an optimization. That's a database too little to misbehave.
Stop searching. Start addressing.
We talked it over with DeepSeek, and the reframe that came back was the one worth keeping: a database you read through a range request isn't a database you should search. It's a database you should address. Don't make the browser hunt for the answer across an index — precompute the answer and put it where one short read can reach it.
Concretely: a single table, one row per name a place can be found by. Each row carries everything the resolver needs already on it — the canonical name, the centroid, the bounding box, the country, and the population rank baked in as a sortable column — so once you've found the row, you're done, with no second trip to go fetch the details. The table is WITHOUT ROWID, which in SQLite means the rows are the B-tree, sorted by the key, and the key is the normalized name. We load it pre-sorted and VACUUM it so a name and its neighbours sit on adjacent pages. Now a lookup is the thing the straw was always good at: walk the tree to the name, read the leaf, and the matches for "springfield" are sitting there in a row, ranked, ready.
One discipline holds the whole thing up: the key has to be normalized the same way when we build the table and when we query it, or the walk lands one page off and finds nothing. So both sides call the same function — normalizeLocalityForKey — by construction, not by convention. It folds case, strips diacritics so Saint-Étienne keys as saint-etienne, and leaves scripts that aren't Latin alone so 北京 stays 北京. Build-side and query-side can't drift, because there's only one of it.
The cost we almost shipped
Now the catch. The candidate table carries US postcodes and not yet the rest of the world's, the same coverage the old slim build had. So when we first wired it up and typed a Berlin address — 10115 — the demo dropped a pin in Manhattan. 10115 is a real Berlin postcode and a real New York ZIP, the table only knew the New York one, and the cascade had reached for the postcode first and taken the only match it found.
The fix wasn't more postcodes. It was order. Resolve the locality first — "Berlin," which ranks to Germany by population — then let that resolved place gate the postcode lookup to its own country. No German 10115 in the table? Fine: fall through to Berlin's own centroid, which is exactly where the address wanted to be. An ambiguous postcode can't drag a German address across the Atlantic anymore, because it never gets to vote before the city does.
What it bought
A name now resolves in about 12 range requests and under a megabyte, down from 243 and 16.5 MB. The whole table is 490 MB with global coverage — every place we know, not three countries — in a single file the browser reads a dozen pages of. US locality resolution holds at 96.8%; the European cities we'd just added through Overture come back at parity. And the slim build — the per-model-version, three-country database we'd been maintaining — is gone, along with the chore of deciding which countries were worth shipping.
The lesson sticks because the constraint was never going away. When the only way to touch your data is one short read at a time, searching is the expensive verb. So we stopped searching. We built the answer ahead of time and gave it an address, and now the browser just goes and reads it.