Connection Pooling with Appsheet?

[gerzy]

I've built an appsheet app against a postgresql cloud sql instance. When more than three or four users use the app simultaneously I run out of database connections. My instance has 100 max connections and when this happens, there's one or two active connection and over 90 idle connections. The database is not large yet (12k rows, 22 tables). The cloud sql instance has plenty of memory/cpu resources left.

Does anyone know if it's possible to configure a connection pooler with Appsheet/Postgresql?  Is there any documentation out there about best practices for configuring postgres with appsheet? I've read the main help page... no info there, and I didn't find any useful info in prior posts.

The only option I currently see is to shorten the idle connection timeout, but I'd rather make appsheet re-use an open connection. This app is supposed to scale to support 100's of users.

[WillowMobileSys]

Why do you think there is an issue reaching max connections?  Are you getting an error?  If so, can you show that error message?

Do you have any other processes that are connecting to your database?  If so, maybe one of them is opening connections without properly closing them.  You should probably check the database for connection activity to see if you can discern where those connections are coming from.

A quick search returned these two Postgresql queries.  You can probably combine them. 

SELECT * FROM pg_stat_activity WHERE datname = 'dbname';
SELECT datname,usename,application_name,client_addr FROM pg_stat_activity;

AppSheet might make a few connections in order to send/receive data.  I do not know their policy for keeping connections open or if they close and re-open as needed.  But at most there should only ever need to be a few connections from AppSheet servers at any given time.  Postgresql is a long supported database on AppSheet so I doubt it would have this kind of connection issue.  Of course, that doesn't mean a bug couldn't have creeped in recently.

If none of the above sheds any light on what the issue could be and you are sure that only AppSheet is connecting to your DB, then I would open a support ticket for help.

[gerzy]

The app will start failing during sync for some users with an error saying "Remaining connections are reserved..." when more than 2 or 3 users connect.

I can clearly see in the Cloud SQL dashboard that I've run out of connections at this point. Digging deeper with PGAdmin, I can see that all of the idle database connections are in the following wait state: "Client: ClientRead". 

By looking at the appsheet performance logs I can see that for a full sync appsheet is loading tables in parallel with up to 10 parallel connections per client. I think it is these parallel connections that appear to be staying open. 

There are no other apps on this database, and this is a dedicated server.

Without visibilty into server code I can only speculate as to what's happening though.

[WillowMobileSys]

Since it seems only AppSheet could be making these connections to your database, I would definitely open a support ticket with AppSheet.

By the way, to determine the number parallelism your app is actually being granted, open the Performance Analyzer and drill into one of the table reads - the log tells you exactly what's being utilized for each read.  See image

[SkrOYC]

You need to implement a connection pooling method by your own.
Another alternative could be to use something like Supabase, which you can learn about here:

[Updated] Supabase mets AppSheet - Google Cloud Community

[gerz]

I ended up using pgbouncer... helped out alot.

[SkrOYC]

---

@gerz wrote:

I ended up using pgbouncer... helped out alot.

---

That's exactly what Supabase uses, haven't had any issues since I switched to it. Hundreds of records get saved without problems