Query timeout not respected + queries slower than usual

Hello Retool team,

Since today, it seems like Retool is no longer respecting the timeout I've set on my queries. Also, for some reason the time to transfer data randomly became very long, it was running much faster than this yesterday. This specific query only returned 5 rows too.

Hi @Nancy_Ts thanks for reaching out about this! I am looking into it internally. What type of query is this? (Rest API, Postgres, etc)

Hello Tess! This is a PostgreSQL query.

Thanks, @Nancy_Ts! Is there any chance I can log in to your org to test this out? If you set the timeout to something very small, like 10ms, does it ever throw a timeout error?

Hi @Nancy_Ts keep me posted about your queries! I haven't been able to reproduce this behavior internally, so hopefully we can take a closer look at your account :crossed_fingers:

Hello Tess,

I am not sure how to consistently reproduce this for you to check. Sometimes the queries run fast as normal and the data transfer is completed right away.

Hmm are there any patterns you've noticed? Such as a lot of end users accessing the app when it happens or roughly the same time every day

Actually yes, I do notice an increase in query timeouts when more than 1 user is using the app. That shouldn't be "a lot" of users though right?

Maybe it's better if you login and try after all. Should I message you the details?

Hi @Nancy_Ts as long as I get the OK from you, I can step in :slightly_smiling_face: I'll take a look

Thanks! I messaged you the deets

Hi @Nancy_Ts!

Apologies for the delay here!

I discussed your feedback with the engineers that own the Postgres integration, and it doesn't sound like this aligns with any recent changes. We haven't had a trend of similar reports of this issue, which makes me think it's not a regression within the Retool product :thinking:. In regards to your original post, they did highlight that the query timeout setting on the query doesn't apply to the entire query time, but rather, only the actual time of the query. It does not include the time to acquire the connection which can take significant time.

I stepped into your app a couple of times, per your instructions, but I haven't been able to reproduce this issue on my side :thinking: The queries are running relatively quickly & I don't see connection errors. It sounds like this is somewhat inconsistent behavior, which could be why it's difficult for me to repro.

I also discussed this with some teammates internally, and based on the behavior described here, the fact that it works sometimes, & the errors that you flagged in your direct message, it could be related to connection pool issues. We'd recommend looking into the connection pool settings on Postgres (outside of Retool). Is this something you've already looked into? For some additional context, Retool will create new connections for each instance of an app that is open, so if you the app open many times (or if many users have the same app opened), then this scenario would lead to many connections being opened. All these users will be connected to your database using the same DB user that is set up in your Retool account. There is a related discussion happening here. I found a stack overflow article that might be helpful as well.

Lastly, I don't think this fully encompasses the issue as you are seeing, but I still wanted to share for anyone else that comes across this post: one thing to be mindful of is where your data is hosted, as described in this post, the location can have a decent impact on query performance. Transfer data is dependent on the resource (size of data) and location (bandwidth, latency).

I'll keep looking into this a bit more, but I hope this helps! :crossed_fingers:

Hi @Nancy_Ts! Just checking in to share some additional ideas from our team:

  1. Try re-creating the resource in Retool. Sometimes creating a new resources can help to resolve unexpected connection issues
  2. Can you cross check connectivity with other tools besides Retool that use this resource?

Hello Tess, thanks for checking into it and for the reply, I will look into that. Does Retool have a limit on how many connections can be open or is it only dependent on our database?

Have you had any issues with the app recently?

On Cloud we support 10 connections in a pool for a given resource (per resource ID, so that goes across apps)