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?
Thanks