Ashraf Ansary

The thing the number was trying to tell me

The API sits on top of two Postgres databases with PostGIS. One holds the places people have contributed and enriched; it's small. The other is a reference layer imported from Overture Maps: 72.8 million rows. The map's "search this area" feature draws a bounding box from the current view and asks that big table what falls inside it.

My first instinct was the boring one: 72 million rows is a lot of rows, maybe this is just slow, maybe I should shrink the default search area or throw a loading spinner at it. I'm a little embarrassed to admit I spent a few minutes on that path before stopping to actually think about the number.

Because the number doesn't make sense for "too much data." A two-kilometer box in a city has, what, a few hundred places in it? If the query were doing work proportional to the answer, it would come back in milliseconds. The only way a tiny box takes a hundred seconds is if the query is doing work proportional to the whole table — reading all 72.8 million rows every time, regardless of how small the question is.

Latency that doesn't shrink when you shrink the input isn't "slow." It's a sequential scan wearing a trench coat.

Confirming it without guessing

The nice thing about having three search endpoints is that I had a control group. Radius search — "everything within 500 meters of this point" — was fine, a couple of seconds cold. Text search was fine. Only the viewport box was dying. Same table, same region, same database. So whatever was wrong lived in the query, not the data.

So I put the two queries next to each other and read them like a diff. Radius used a geography distance function. Viewport did this:

WHERE geometry::geometry && ST_MakeEnvelope(:sw_lng, :sw_lat, :ne_lng, :ne_lat, 4326)

And there it was, hiding in plain sight: geometry::geometry.

The column is typed geography(POINT, 4326) — PostGIS's spheroidal type, the one that knows the Earth is round. It has a GiST index built for exactly this kind of spatial lookup. But geometry and geography are different types in PostGIS, with different operators and, crucially, different index support. By casting the column to geometry and using the planar && overlap operator, the query was asking Postgres to filter on an expression the geography index knows nothing about. There's no index on geometry::geometry. So the planner shrugged and scanned everything. All of it. On every pan.

The part that still makes me laugh: that exact cast is correct a few lines up, in the SELECT.

SELECT ST_Y(geometry::geometry) AS latitude,
       ST_X(geometry::geometry) AS longitude

ST_X and ST_Y only work on geometry, so you have to cast there — and it costs nothing, because it only runs on the handful of rows you've already found. The same three characters that are free in the projection are catastrophic in the filter. I'd bet money it got pasted from one into the other by someone (me, plausibly) who saw it working and didn't think twice.

The fix is the diagnosis, restated

Once you can name the problem, the fix writes itself. Don't drag the column out of the type its index lives in — bring the envelope into that type instead:

WHERE ST_Intersects(geometry, ST_MakeEnvelope(:sw_lng, :sw_lat, :ne_lng, :ne_lat, 4326)::geography)

Now both sides are geography, ST_Intersects is happy to lean on the geography GiST index, and the question gets answered by touching only the rows that could possibly matter.

I didn't want to trust a hunch on a fix, so I ran EXPLAIN ANALYZE on both forms against the real 72.8-million-row table. The new query came back as an Index Scan using idx_overture_geometry in 162 milliseconds. The old query I had to abort — I'd put a 20-second statement timeout on it just so my own test would terminate, and it blew straight through that into the sequential scan it had always been. The timeout was the proof.

While I was in there, I found the same mistake in the small database's viewport query — location::geometry && …, identical shape. Harmless today, because that table has a few rows. A trap set for whoever grows it. Fixed both, shipped them together, attached the EXPLAIN output to the change so the next person doesn't have to take my word for it.

What changed, and what stuck

End to end, viewport search went from over a hundred seconds to about 0.28 seconds — and a deliberately large box, sixteen kilometers across returning two hundred results, lands in 0.36. The map became the thing I'd been trying to build the whole time: drag it and the pins just keep up. The best part was deleting code — all those workarounds I'd been about to add, the forced minimum zoom, the artificially tiny default area, none of them were ever the answer. "The area is too big" was never true. The query was reading the whole planet to show you one neighborhood.

A type cast on an indexed column in a WHERE clause silently turns the index off. Nothing errors, the results are still correct, the tests still pass — it just gets quietly, exponentially worse as the table grows, which makes it invisible in development and brutal in production. If you must cast, cast the literal to match the column, never the column to match the literal.

And let the size of a number tell you what kind of problem you have before you start optimizing. A hundred seconds for a tiny query isn't a tuning problem, it's a "you're doing unbounded work" problem, and those have completely different fixes. I almost shrank the search area. The real bug was three characters away the whole time.

Traverra's schema doesn't just store coordinates. Every spot tracks revision history, trust levels, moderation flags, hours, photos — the machinery that makes community data trustworthy. I ran the numbers on loading all 73 million places with that full schema: 300–500 GB in Postgres, north of $100 a month on a managed database. For a project with exactly zero users. I looked at cheaper self-hosted options, but that meant owning backups, security, and PostGIS configuration — all time I'd rather spend building the product. I was stuck: full coverage was too expensive, partial coverage made the product useless, and self-hosting traded a money problem for a time problem.

The fix was embarrassingly simple once I saw it: stop treating all 73 million places the same. I split the data into two layers in the same database. A lightweight, read-only reference table holds the raw Overture Maps dataset with a spatial index — global coverage in about 15–25 GB, because it stores little more than names, categories, and coordinates. A separate community table uses the full schema and starts completely empty. A spot only gets written there when a user actually interacts with it — edits, flags, verifies, adds hours or photos. The API checks the community table first and falls back to the reference layer when nothing's there.

There's a quiet bonus in this design beyond the bill. Because every spot in the community table got there through a real interaction, the expensive layer fills up weighted toward the places people actually care about — I never had to decide which cities or regions to import upfront, because usage decides. And since each record tracks where its data came from, a spot's fields can graduate from "imported baseline" to "community-verified" as people confirm them.

The storage footprint dropped by more than 90%, the bill came down with it, and the architecture hasn't needed rethinking since.

First step: reproduce it. I recreated the customer's setup locally with their programs and generated timing data to confirm the problem was real, not environmental. Then I wanted a baseline, so I built the most trivial control service I could — one that just adds two numbers — and timed it. A fraction of a millisecond. That one experiment ruled out the entire platform as the cause: whatever was wrong was specific to this service and its data.

Next I captured the HTTP traffic with Wireshark to see what was actually crossing the wire, and there it was: the request body had an array initialized with 100 null elements, and by the response it had ballooned to 1,000. All that memory allocated, serialized, and shipped to say exactly nothing — that stood out immediately.

It also pointed at a suspect. COBOL doesn't have dynamic arrays the way modern languages do — its tables are fixed-size, with a maximum declared at compile time. All those nulls made me suspect the translation layer was allocating and serializing the full maximum table size and padding the unused slots, instead of sizing to the actual data.

The runtime engine belonged to another team, so I partnered with them to trace the request through their layer — and the hypothesis held. The table allocation was being calculated from the declared maximum, and we changed it to size from the number of elements actually populated. Latency dropped from 60 seconds back into the sub-millisecond range, the wasted memory disappeared, and the fix shipped to every customer on the product.

Two things I'd do differently. I'd loop in the runtime team earlier — I spent a fair amount of time investigating solo, and since the bug lived in their layer, their context would have gotten us to root cause faster. And because nothing about the bug was specific to this customer's data, I'd add a regression test that checks serialized payload size against the actual element count, so this whole class of over-allocation bug gets caught before it ever reaches a customer.

The idea was simple: one place to save the spots you've been, organize them into collections, add friends, and share trips with them. No more digging through group chats. We went serverless on AWS — DynamoDB for storage, Cognito for auth, the whole stack deployed automatically.

I ended up owning most of the backend plumbing, and the part I'm proudest of isn't any single feature — it's everything around the code. I wrote unit tests with Jest against mocked DynamoDB calls. I wired up git hooks so the tests had to pass before a commit or push would go through. Then I built a full CI pipeline in Azure Pipelines that spun up a VM, ran the suite, and published coverage reports. None of this was required — we were building the app for fun — but I wanted to know what a real development workflow felt like, end to end.

That project taught me more about backend architecture, CI/CD, and working on a team than anything I did in school. It's also the reason I'm a software engineer at all — I came out of grad school with an electrical engineering degree, and building this app was what convinced me that shipping software was the thing I actually wanted to do. Turns out building something just because you and your friends want it to exist is the most fun way to learn.

My first attempt — a basic two-tap LMS filter — flat-out didn't work. The weights oscillated and diverged instead of settling, and the error signal never died down. I spent a while tuning parameters, convinced I'd made a mistake somewhere, before accepting that the filter simply didn't have enough degrees of freedom to model the interference.

Bumping it up to eight taps helped a lot: the weights converged around 300 iterations and the error dropped to zero. But the real breakthrough was switching to NLMS, which normalizes each weight update by the energy of the input signal. With just two taps — the same two taps that had failed before — NLMS drove the error to near zero. And when I pushed the sampling rate higher, LMS fell apart all over again, weights oscillating with no sign of convergence, while NLMS stayed rock steady.

The lesson stuck with me: throwing more complexity at a problem — more taps, more iterations — doesn't always fix it. A fundamentally better algorithm can do more with less. That pattern shows up constantly in software too. I've caught myself brute-forcing web app problems with more code and more state when the real fix was rethinking the approach. Messy API responses, race conditions, state that won't settle — none of them look like radio signals, but the job is the same: pulling something clean out of something noisy.

Read the full paper (PDF) →