Limiting connection pool size when using multiple DbContexts in EF Core-CodePudding

I'm using EF Core with .NET 6 and I noticed some behaviour around connection pooling with multiple DbContexts that I don't fully understand. I have one Postgres database with multiple schemas in it. I have two DbContext classes, both access this one database but with different search paths set, so they work on different schemas. One of the two sets the search path dynamically in the OnConfiguring method. Both have a MaxPoolSize of 20 defined in OnConfiguring by adding this to the connection string.

A simplified version of the OnConfiguring method of both contexts looks like this:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      var builder = new NpgsqlConnectionStringBuilder
      {
        Host = host,
        Database = "mydb",
        Username = user,
        Password = password,
        MaxPoolSize = 20,
      };

      builder.SearchPath = "public";

      optionsBuilder
          .UseNpgsql(builder.ConnectionString);
    }

The main difference between both is that one of them sets the SearchPath dynamically, the other one has a static search path.

What I observed now was that the number of connections to Postgres went higher than 20 and lead to refused connections. So the connection pool does not seem to be shared across the two DbContexts. I tried to find some information about how the pool should work in this situation, but the only thing I could find is that the pool is supposed to work at a lower level than the context, which does not explain the behaviour I saw.

So how does the connection pool work exactly, and do I have one or more than one pool in my case? Is there a pool per DbContext or not? And does changing the SearchPath component of the query string affect this in any way?

Ideally I'd like to have a global connection pool for my app, but it seems like this is not how EF Core behaves in this case. So I'd like to first understand the behaviour here and how it is supposed to work, and then figure out a way to either get a global pool or if that isn't possible to understand the limitations so that I can reliably know the maximum number of DB connections my app will actually open. I don't want to blindly increase the max connections on the DB here.

CodePudding user response：

ADO.NET (framework under EF) does not "inspect" specific parameters in connection string, it simply creates connection pool for each unique connection string

So there is no "pool per DbContext", if contexts share connection strings. If you want common connection pool, just make sure all context use the same connection string. Using search_path with connection pooling is always tricky, you are safer fully qualifying objects in queries.