The LIMIT that hid part of the map
A short note on a row cap in Flatlas that returned the right number of rows and the wrong set of blocks.
When you panned the map, the API fetched recent resale transactions
for every HDB block inside the viewport. The query found the blocks
with a spatial index, then used a LATERAL join to pull
each block's newest sales, up to 100 per block. A per-block limit was
deliberate: one global LIMIT lets busy blocks crowd out
quiet ones.
On top of that sat a LIMIT 5000 backstop, so a
zoomed-out view couldn't send a multi-megabyte response. It was
ordered by sale month, newest first.
The backstop reintroduced the problem the per-block limit was meant to prevent. Ordered by month alone, the newest 5,000 rows all came from blocks with recent sales. A block whose latest sale was a few years old had every row below the cutoff, so it vanished from the map without an error. In one dense viewport of 371 blocks, the response covered only 262.
ORDER BY sale_month DESC LIMIT 5000 block A 2026-09 2026-08 2026-08 … ┐ block B 2026-09 2026-07 … ├─ newest 5,000 rows block C 2026-08 2026-06 … ┘ ─────────────────────────────── cutoff ─────── block D 2022-03 2021-11 … ✗ every row cut block E 2021-08 … ✗ every row cut
Inside the LATERAL, each block's rows get a rank with row_number(), newest first. The backstop then orders by
that rank before the date:
ORDER BY rn, sale_month DESC LIMIT 5000 rn = 1 newest row of every block ← all kept first rn = 2 second-newest of every block … rn = k cutoff lands here: trims old rows evenly
Every block's newest sale is now in the first rows of the result, so a block can only disappear once a single viewport holds more than 5,000 blocks. The cap still applies, but it now trims each block's oldest rows instead of removing whole blocks. It was faster too: an 8,213-block view went from 1,270 ms to 518 ms.
A LIMIT raises two questions: how many rows, and which
rows. The second question decides whether results are correct, and
a cap ordered only by date is a filter on age. Now, whenever a query
returns groups, I check what a cap removes from each group, and I
test against the densest view instead of a typical one. The bug
never threw an error. It only showed up when I counted blocks.
A postscript: a week later the frontend stopped calling the viewport endpoint altogether, and I removed it. It had become the API's slowest query, and nothing used it. Deleting a query is the surest way to make it fast.