Apr 2026 · Profound
Three slow endpoints and a query that scanned every tenant
Sentry said 1.8s, 5.3s and 6.5s at p99. The worst one was also a tenancy bug.
Production Sentry data had three route procedures sitting at p99 latencies of 1,833ms, 5,305ms and 6,546ms. I took the investigation and the fixes.
What each one turned out to be
- route.create at 6,546ms — block ID validation was running a correlated subquery per block. Rewritten as a single = ANY() set operation.
- route.checkPath at 5,305ms — a missing composite index on (website_id, path). Adding it fixed the endpoint with no application change at all, which is the most satisfying kind of fix and the least interesting to write about.
- route.getByPath at 1,833ms — the route pattern matcher was regex-scanning every tenant's routes to find one website's match. Filtering by tenant and marking the function STABLE fixed the latency. It was also, separately, an isolation problem: a function that reads across tenants to answer a single-tenant question is one bug away from returning the wrong one.
The risky part
The rewrites dropped the previous function overloads, so every caller in the monorepo had to move in lockstep. That went out as one reviewed PR with the breaking change spelled out in the description, rather than a series of small ones that would each have left the tree half-migrated.
What I got wrong
I recorded the baselines carefully and never recorded the after. The fixes are correct and the reasoning is documented, but I cannot tell you what the p99 became, which means I can't honestly claim an improvement figure. Measuring afterwards costs about ten minutes. I do it now.