Benifits of PGCat in Scenarios with Many DBs/users and Idle Connections

[Vad1mo]

Hi,
we have a bit of an edge case for PGCat (I think). In this circumstance, I am unsure if PGCat can provide its usual benefits.

The problem we are facing is that we often run out of connections to our DB because we have many tenants that are hogging mostly idle connections.

Our setup

• One DB per customer/tenant
• Each DB has a dedicated user
• Each application stack per user consists of 2-4 services/process
• Each process has a connection to the DB
• Each tenant needs 0–20 connections, depending on load
• The application/services, have the following configuration:

              maxIdleConns: "1"
              maxOpenConns: "10"
              connMaxIdleTime: "33s"
              connMaxLifetime: "5m"

The application stacks are using a pooler inside the application (pgxpool).

Usually, the DB is idling as most data is read only and coming from the cache. In some usage scenarios, where the application opens many connections, is during batch processing, where many parallel operations happen resulting in 2–10 active connections per process. In total, this peaks to ~20 active connections per application stack. This typically lasts for a few seconds to max 1–3 minutes. After the rush, the connMaxIdleTime kicks in and closes the connection.

The issue is: We have many idle connections that basically cause the system to run out of connections if 2-5 instances are processing data. If we could reduce the idle connections to 10% instead of 60% this would increase the efficiency of the DB.

Do you see any beneficial use of PGCat in such a scenario?

[drdrsh]

Postgres connection pooling does help reducing idle connections when used in transaction mode. This is true for Pgbouncer and PgCat. PgCat does not allow multiple users to share the same pool. So, if you have 10 users, you will have to create 10 pools with each pool having N connections but in that setup connections are pooled within the same tenant not across tenants. For the best utilization you will want to allow pooling across tenants, I think Pgbouncer supports this but I could be wrong.