Hop ing you can help. I've set up a postgres db with AWS and linked it to my retool account. When I load a query, the run time is super slow. However when I run exactly the same query using pgAdmin4, the runtime is quick.
For example
Query loaded in retool app using AWS resource connection: 1.873s
Same query loaded using pgAdmin4 = 142 ms
I've created a new (empty) app and run the query from there so I know it's not anything to do with the app. I read somewhere the retool's processing adds c. 150ms to a query so would expect it to be around 300 ms / 0.3 secs.
I assume it's something to do with the connection. Any ideas please on how to improve?
I see you are having some issues with latency. It would helpful for us to have a bit more context on this issue and to also double check a few key things.
Can you ensure that both retool and pgAdmin4 are accessing the PostgreSQL instance in the same AWS region. Latency can increase if the tools are connecting from different regions, affecting query performance.
Check if there are any network-related issues such as firewall rules or routing configurations that could impact the connection between retool and AWS PostgreSQL.
Once you can confirm these, can you possibly write out your query so that we can also check to see if it is written in the most optimal way? These are just a few things to help us get started!
For various reasons (not related to any Retool issues), we’re migrating from RetoolDB to Xata.io—which runs on AWS Aurora—and swapping out the resource on every query from RetoolDB to Xata (and making zero additional changes).
The additional latency from using Xata as the resource instead of RetoolDB for the same queries is typically 800-1000ms. Even for simple queries on small tables like:
SELECT id
FROM users
WHERE userId = 123
As @TimC mentioned, this latency doesn’t exist when using pgadmin or the sort (DataGrip in my case). Also doesn’t happen when calling the Xata db from Postman or a test server I spun up in a separate region (to test that as the issue).
Both RetoolDB and Xata resources are set up exactly the same way (though idk if there are internal differences).
——
I did notice that firing the same query (with different inputs) rapidly in succession brings down the latency, but it alternates consistently between 360ms (matching RetoolDB time) and 860ms. If waiting an additional few seconds and firing the same query again, it’s back to 1860ms.
So it feels like a Retool cold start issue on the postgres resource? I don’t know how the connection is maintained on the Retool side, but again this is not an issue in other tools or from test servers that mimic the setup.
Is the Xata.io server hosted close to our cloud server?
It’s possible having to travel further or Xata.io is just less performant. You can hover over the query runtime for a more detailed breakdown on what’s taking the extra time.
You mentioned, there’s no latency when testing in PgAdmin or Postman. Because you're connecting locally to the db already for pgadmin, then making a query with an existing open connection or making a connection from your local PC from postman the runtimes will be faster. Whereas if you're on cloud, the query has to go from Retool cloud servers -> your xata.io server. We have to open the connection from scratch then make the query which takes a little longer.We usually pool connections for DB resources, so if you make one request, then make another query right after, you should be able to see the difference between a “cold start” (opening a new connection) and reusing an existing one.
N.B runtime when making a manual run in a WF will probably be quicker than making a manual query in an app.
