I have a retool database resource that is cached. Using “Debug Tools” I see first load not using cache, and I see subsequent loads using cache. So all is well in terms of enabling cache.
For this entire example, the query size is 188 bytes.
First, I’m seeing poor latency for the non-cached requests compared to directly running the query using a postgresql client.
Non-cached requests take between 320ms to 7+ seconds.
When running via postgresql query, it consistently takes 38ms - 42ms, excluding any transport to/from the database.
Questions for non-cached scenario:
Why is there such a delta between min and max latency? The latency distribution is all over the place with 2 - 4 seconds being a pretty common range.
Is it expected that a 42ms query execution (from pg client) would take 2 - 4 seconds (used common range) when retrieved from app resource query? I guess the general question is, what is the “ground floor” latency for a retool db query, or even a heuristic, that we can expect, no matter how gnarly the query itself is?
Second, I frequently see poor latency for the cached requests compared to the non-cache request latency.
Cached request: latency as low as 240ms, although not common.
Cached request: latency generally around 800ms - 1500ms. This is fairly common.
Cached request: latency as high as 4+ seconds. I see this about 10% of the time.
Questions for cached scenario:
Should the time of day matter? In morning (PT) I’ll see longer latencies for cached requests.
I would expect cached latencies to be under non-cached. There are many times it’s not. How can this be addressed?
The time you see for the PostgreSQL client is only the database execution time. Your Retool query will go through auth, request routing, serialization etc so it will always be higher.
But yes, 2-4 sec for this query isn’t right. You should try opening a support ticket or wait for retool team to respond here
Good questions, @lkiss! To @himanshu.sharma's point, it's not quite fair to compare the total query delta to the isolated DB execution time.
I can dig into your org's backend logs to look at some of the outliers you're describing, but Retool's overall architecture probably explains most of the behavior that you're describing. The root of it is understanding the path a request takes:
Client --> Retool Backend --> Retool DB Connector --> Resource
As a baseline, I typically tell folks that Retool will add a minimum latency of 500ms compared to a direct client-resource connection. It can be significantly higher depending on the physical location of the client and resource, though. Other factors that deterministically increase latency include overall payload size and data transformations.
The overall variance that you're describing is harder to conclusively root-cause, but can most likely be attributed to query congestion in either the Retool backend or specified resource. Traffic from all Cloud orgs flows through the same set of backend pods and connection pools sometimes become exhausted during peak use. The same is true of individual resources, albeit typically on a much smaller scale.
Last but not least, cached queries will almost always execute faster than their non-cached equivalents because they are directly handled by the Retool Backend without needing to involve the DB Connector. That said, cached query results are stored in Redis and do need to be retrieved. This is typically done very quickly but we sometimes run into something called a cache miss, which incurs a latency penalty. I'd be interested to look at your logs to see how often this is happening!
I hope that all makes sense, @lkiss! The bottom line is that running queries through Retool will always be longer than querying your resource directly and that there will always be some variation due to the multi-tenant nature of our Cloud. That said, there are specific failures - pool exhaustion and cache misses, most prominently - that may be the result of legitimate bugs.
I appreciate the explanation, and no follow-up questions per se. This is a “it is what it is” type of thing. I find it frustrating that a non-cached resource request can take 7+ seconds when the query itself is optimized and response payload is tiny, and when cached resource requests take longer than non-cached requests. In this specific scenario, I have turned to caching the data myself, which I don’t love but it works very fast since it’s client based and no server based.
Anyway, thanks for the follow-up here. Feel free to close out.
Yeah, the primary thing I wanted to communicate is that our architecture introduces a minimum level of latency and some variability is to be expected just based on traffic and shared infrastructure. The upper end of of what you're seeing is still concerning, though. I'll keep this open and do some additional digging!
I'll keep this open and do some additional digging!