The right way to DI NpgsqlConnection postgreSQL with multiple connectionString in ASP.NET Core 3.1

Question

I'm struggling to register DI NpgsqlConnection() with multiple connection strings in ASP.NET Core 3.1, Dapper v2.0.78 & Postgres v11.

I will provide the current state & fix the code below:

Current State

The idea comes from here

Step 1. Startup.cs --> ConfigureServices()

services.AddTransient<IDbConnectionFactory, DapperDbConnectionFactory>(sp =>
{
  var connectionDict = new Dictionary<DatabaseConnectionName, string>
  {
     { DatabaseConnectionName.Cnn1, "Connectionstring 1"},
     { DatabaseConnectionName.Cnn2, "Connectionstring 2"}
  };
  return new DapperDbConnectionFactory(connectionDict);
});

Step 2. DapperDbConnectionFactory looks like this:

public class DapperDbConnectionFactory : IDbConnectionFactory
{
    private readonly IDictionary<DatabaseConnectionName, string> _connectionDict;

    public DapperDbConnectionFactory(IDictionary<DatabaseConnectionName, string> connectionDict)
    {
        _connectionDict = connectionDict;
    }

    public IDbConnection CreateDbConnection(DatabaseConnectionName connectionName)
    {
        string connectionString = null;

        if (_connectionDict.TryGetValue(connectionName, out connectionString))
        {
            return new NpgsqlConnection(connectionString); // <--- I think the issue comes from here
        }

        throw new ArgumentNullException();
    }
}

Step 3: the way I use this code:

public ConstructorMedthod(IDbConnectionFactory _connFactory)
{
   _conn = _connFactory.CreateDbConnection(DatabaseConnectionName.Cnn1);
}
public async Task<QueryResult<IBaseReportModel>> Handle(...)
{
 ...
   var query = await _conn.QueryMultipleAsync("Query content here"); // <--- I think the issue comes from here
 ...
}

I did think that the way to use is incorrect, need to wrap the call _conn.QueryMultipleAsync in using statement at least to make sure the connection is closed & disposed then return the connection pool. Because DI container just manages the life cycle of DapperDbConnectionFactory only instead of IDbConnection

As a result, sometimes I got this error:

Npgsql.NpgsqlException (0x80004005): The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)

I have to restart the app then it turns to normal. So I'm not sure whether the issue is max connection pool or Timeout due to network. It should be able to check the number of connections in the pool compared to the max connection pool at that time. I'm assuming that.

Fixing the code

I have 2 ideas:

1. Wrap the call _conn.QueryMultipleAsync in using statement. But this way I have to change too much code everywhere calling _conn.

2. Change the way to DI (The idea comes from here). More details are below.

Step 1. Startup.cs --> ConfigureServices()

services.AddTransient<ServiceResolver>(serviceProvider => cnn_Name =>
{
    switch (cnn_Name)
    {
        case "Cnn1":
            return new NpgsqlConnection("Connectionstring 1");
        case "Cnn2":
            return new NpgsqlConnection("Connectionstring 2");
        default:
            throw new KeyNotFoundException();
    }
});

public delegate IDbConnection ServiceResolver(string connectionstring);

Step 2: the way I use it:

private readonly IDbConnection _conn;

public ConstructorMedthod(ServiceResolver serviceAccessor)
{
   _conn = serviceAccessor(DbConnectionKey.Cnn1);
}

public async Task<QueryResult<IBaseReportModel>> Handle(...)
{
 ...
   var query = await _conn.QueryMultipleAsync("Query content here"); 
   // Now I suppose the _conn will be closed & disposed by DI container.
 ...
}

Questions

1. What is the right way to register DI NpgsqlConnection of postgreSQL with multiple connection strings in ASP.NET Core 3.1?

2. How can I verify that connection string is returned the connection pool, something like being Query on PostgreSQL like this and this one ?

SELECT * FROM pg_stat_activity;

3. How to increase the max connection pool to greater than 100. Whether it's the best practice? I found this post said that

Just increasing max_connections is bad idea. You need to increase shared_buffers and kernel.shmmax as well.

But actually, I'm using RDS PostgreSQL of AWS. So I'm thinking config appsettings like this.

UserID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;Pooling=true;Minimum Pool Size=0;Maximum Pool Size=200;

Furthermore, As this post said that

Npgsql connection pooling is implemented inside your application process - it has nothing to do with PostgreSQL, which is completely unaware of it.

So I'm very confusing the max connection pool in between the PostgreSQL (Question #2) with Appsettings (Question #3)

Pensile

Answer 1

Unfortunately, The author of the current state solution said that

I am not sure if I am using the best practices correctly or not, but I am doing it this way, in order to handle multiple connection strings.

After reading the comment very in detail, I recognized that You must use using statement like this.

public async Task<QueryResult<IBaseReportModel>> Handle(...)
{
 ...
     using(var conn = _connFactory.CreateDbConnection(DatabaseConnectionName.Cnn1))
     {
       var response = await conn.QueryMultipleAsync("Query content here");
     }
 ...
}

Otherwise, you will get this error

Npgsql.NpgsqlException (0x80004005): The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)

or

"53300: remaining connection slots are reserved for non-replication superuser connections"

The highlighted comment is below. Hopefully, It would help you all to avoid hidden mistakes.

Updated - 2022-05-13

From @Shay Rojansky's answer, we already have clarified the confusion - Question #3 between Appsetings -> Maximum Pool Size=200; and PostgreSQL --> SHOW max_connections;

Pensile