[Bug]: Postgres Idle Connections

[sneha122]

Is there an existing issue for this?

• I have searched the existing issues

Description

When a cloud user executes PostgreSQL queries at page start, it creates new sessions. However, these sessions don't close after the page is closed or are no longer needed. This causes the sessions to accumulate and quickly hit the 100-session limit they have set in their database

Steps To Reproduce

https://theappsmith.slack.com/archives/C0341RERY4R/p1703228974015659

Public Sample App

No response

Environment

Production

Issue video log

No response

Version

Cloud/ Self-Hosted - 1.8.4

[sneha122]

Checked out this user issue with respect to postgres idle connections, there are a couple of things unknown right now, because of which I was not able to reproduce the issue, we have asked for this information from the user, the conversation is going on here. We have asked user for details like:

1. Where they have hosted their postgres DB. This information is crucial because we know from past experience that when we create a postgres DB on supabase, it creates issues with connection pool and causes idle connections to be created. If we get to know where user has hosted their DB it would help us reproduce the issue quickly
2. Whether user has created one datasource or multiple datasources against the same DB. This can be useful in understanding why multiple connections are being created

Apart from this, I checked out the connection pool codebase and also the logs when multiple connections are created, here are a couple of observations:

1. When we execute a query, we check if the datasource context already exists or not, if it does we use the same to execute the query and hence no new connection is created. In case the context does not exist, we create a new connection and add that connection in the pool.

2. We also check if the connection in the pool is stale, we destroy that connection, but the way in which we determine if connection is stale or not is by checking the timestamp of when the datasource object was updated and when the datasource context was created. In our situation when we execute select queries, datasource object is never updated, hence the context will never become stale and will remain in the pool 3. I also checked the cloud logs when the active connections to database keep increasing, the entry point in our code base for creating new connection for postgres is as shown below. When searched in logs for "Connecting to Postgres db", I could not find any results, which either means that these active connections are not happening from our code base or there is another entry point for creation of these connections.

[sneha122]

Moving this issue back to sprint backlog, we can further investigate this issue once we have required information from user.

[rhuanbarreto]

I'm also having this issue.

[habermst]

Same issue. Using am Postgres installed on a azure vm. Asked on Discord. Here is the screenshot:

Some more context:

Row Count shows the idle connections (this was taken after about 1.5 - 2 hrs). Only one user (me) actively working on an app & testing. SET application_name = 'Appsmith JDC Driver' has 47 idle connections. The other one with 36 connections was executed when a modal was opened for a spefic id. So I would expect it to reuse the connection. Apart from the param, it was the exact same statement.

[rhuanbarreto]

Best solution today is to use pgbouncer to try to minimize. But appsmith itself could also improve this on their side.