We're having some serious performance degradation in our Retool project stemming from Postgres queries taking a long time to execute (Retool db). We made a few attempts at reducing and spacing out the queries (limiting them to specific actions, etc.) without real success.
I just became aware of the breakdown of the query metrics via hover/tooltip, and I now see that with all of these Postgres queries, the latency is in the 'transfer data' process.
The rest of the query metrics are all quite good nearly all the time. But for some reason, there's an issue with 'transfer data' which is taking up 90%+ plus of the query's 'total time in most cases. I've attached a screengrab of the performance metrics. When running the queries individually using 'Run' they tend to complete in ~600ms.
Is there some type of configuration issue on the backend? Other explanation why this is happening and how to avoid it? I thought that our load is relatively light at this point, so a little concerning to see this. The query latency is making page rendering and user experience unpleasant - it is often much worse than depicted in the screengrab 5-10 seconds.
I see that you were able to work with our internal team in more detail. It sounds like they mentioned refactoring the app a bit to help with overall performance.
I am experiencing the same issue with my queries which is impacting my user experience and sometimes leading retool mobile to crash. What was the solution or "refactoring" meant here?
@Hussein_Ahmed
I'd first mention, we didn't run into the similar issues with Mobile, although our query setup for Mobile and application/usage, in general, was quite different.
Tbh, this was and continues to be a major problem.
At least for the issues we were facing, database queries running in parallel (or in sequence) was a problem. So we tried to minimize that. That's not always possible, without making concessions.
This doesn't really count as a solution, but after 10s of hours of trying to fight it I gave in: more performant hardware signifigantly helped the issue(s). And in a non-linear way. I'm throwing numbers around, but maybe something like: 50% more CPU resulted in 300% improved load speed).
Thanks Diskin! its honestly becoming a go or no go with Retool. I have users where the mobile app is crashing on them, in the morning eastern time, the app works fine, at night, loading queries takes quite sometime and can lead to crashes. This weird behavior appears even when I am in the app editor, where it crashes on me. Will work fine in the morning though! Not sure what is going on, but def embarrassing in front of my users.
Hi @Hussein_Ahmed,
Are you able to reproduce the crashing of the Mobile App? It sounds like this is tied to a specific action, rather then general performance issues. If you can't, look back on cases where there were reports of the app crashing (or ask you users to report whenever they have the issue) and the Audit Log could possibly indicate what that problematic action may be. (There may be a more streamlined way to send notification when the app crashes, if anyone else wants to weigh in on that).
A few other points:
-The point you mentioned about differing behavior based on time of day: if you're using Retool Cloud, that may be a result of which gateway you (or the users) are hitting (which may just correlate with time of day). While I have seen some performance variability over different parts of day, I haven't noticed any clear pattern with that. What I have clearly seen though, is that for a team member that we have that is based on a different continent, certain actions appear to be much slower.
-If you are talking about the App Editor for Retool web (not-mobile), the App Editor is always considerably less performant, so don't be surprised by that. App Editor for mobile, I can't comment on - we haven't had these types of performance issues with Mobile.
