Postgresql and .Net - Connection Pooling with Multiple Endpoints

Question

Basic Intro:

Connection string: Host=IP;Port=somePort;Username=someUser;Password=somePass;Database=someDb;Maximum Pool Size=100

My web application has several dozen endpoints available via WS and HTTP. Every one of these endpoints opens a new NPGSQL connection (all using the same connection string as above), processes data, then closes via the using statement.

Issue: When the application restarts for an update, there is typically 2-3,000 users all reconnecting. This typically leads to errors regarding the connection pool being full and new connections being rejected due to too many clients already. However, once it can finally come online it typically only uses between 5-10 connections at any given time.

Question: Is the logic below the proper way to use connection pooling? With every endpoint creating a new NPGSQL connection using the same connection string specifying a connection pool of 100?

It seems that the connection pool often shoots right up to 100, but ~80/100 of those connections are shown as idle in a DB viewer with new connection requests being denied due to pool overflow.

Better option? I could also try and force a more "graceful" startup by slowly allowing new users to re-connect, but I'm not sure if the logic for creating a new connection with every endpoint is correct.

// DB Connection String - Used for all NPGSQL connections
const string connectionStr "Host=IP;Port=somePort;Username=someUser;Password=somePass;Database=someDb;Maximum Pool Size=100";

// Endpoint 1 available via Websocket
public async Task someRequest(someClass someArg)
{
    /* Create a new SQL connection for this user's request using the same global connections string */
    using var conn = new NpgsqlConnection(connectionStr);
    conn.Open();

    /* Call functions and pass this SQL connection for any queries to process this user request */
    somefunction(conn, someArg);
    anotherFunction(conn, someArg);

    /* Request processing is done */
    /* conn is closed automatically by the "using" statement above */
}

// Endpoint 2 available via Websocket
public async Task someOtherRequest(someClass someArg)
{
    /* Create a new SQL connection for this user's request using the same global connections string */
    using var conn = new NpgsqlConnection(connectionStr);
    conn.Open();

    /* Call functions and pass this SQL connection for any queries to process this user request */
    somefunction(conn, someArg);
    anotherFunction(conn, someArg);

    /* Request processing is done */
    /* conn is closed automatically by the "using" statement above */
}

// endpoint3();
// endpoint4();
// endpoint5();
// endpoint6();
// etc.

EDIT: I've made the change suggested, by closing connections and sending them back to the pool during processing. However, the issue still persists on startup.

1. Application startup - 100 connections claimed for pooling. Almost all of them are idle. Application receives connection pool exhaustion errors, little to no transactions are even processed.

2. Transactions suddenly start churning, not sure why? Is this after some sort of timeout perhaps? I know there was some sort of 300 second timeout default in documentation somewhere... this might match up here.

3. Transactions lock up again, pool exhaustion errors resume.

4. Transactions spike and resume, user requests start coming through again.

5. Application levels out as normal.

EDIT 2: This startup issue seems to consistently be taking 5 minutes from startup to clear a deadlock of idle transactions and start running all of the queries.

I know 5 minutes is the default value for idle_in_transaction_session_timeout. However, I tried running SET SESSION idle_in_transaction_session_timeout = '30s'; and 10s during the startup this time and it didn't seem to impact it at all.

I'm not sure why those 100 pooled connections would be stuck in idle like that on startup, taking 5 minutes to clear and allow queries to run if that's the case...

Answer 1

A connection is released to the pool once you close it in your code. From what you wrote, you are keeping it open for the entire time of a request, so basically 1 user = 1 connection and pooling is just used as a waiting room (timeout setting, 15 seconds by default). Open/Close the connection each time you need to access the DB, so the connection is returned to the pool and can be used by another user when time is spent in .net code.

Example, in pseudo code:

Enter function  
 Do some computations in .net, like input validation

  Open connection (grab it from the pool)
    Fetch info#1  
  Close connection (return it to the pool)

 Do some computations in .net, like ordering the result, computing an age from a date etc 
  
  Open connection (grab it from the pool)
    Fetch info #2   
  Close connection (return it to the pool)

 Do some computations in .net

Return

Answer 2

I had forgotten to update this post with some of the latest information. There was a few other internal optimizations I had made in the code.

One of the major ones, was simply changing conn.Open(); to await conn.OpenAsync(); and conn.Close(); to conn.CloseAsync();.

Everything else I had was properly async, but there was still IO blocking for all of the new connections in NPGSQL, causing worse performance with large bursts.

A very obvious change, but I didn't even think to look for an async method for the connections opening and closing.