In our database we need to create and use schemas dynamically. For this we would like to use a dynamic DbContext, which accepts a schema name at construction and uses it for all queries within this context.
Our first idea is to specify the schema in the search path connection string parameter leading to thousands of different connection strings. My question is whether this a intended use case for the connection pooling strategy in npgsql. I read that a separate connection pool is managed for every connection string which would result in thousands of connection pools having one or two connections each, which seems wasteful. But on the other hand there are common use cases in which every user gets his own DB Role. Since the Role is also specified in the connection string, this scenario also has to deal with a growing amount of connection strings.
We have a backup strategy in which the schema is included in the model of the DbContext. In this case we would have to manage thousands of different models leading to a overhead in creating them or increased RAM usage for caching them but every context would use the same connection string.
Read through this recent issue which deals with exactly this among other things.
tl;dr yes, any difference in the connection string results in a different connection pool, which can have significant impact on performance as connections are bound to a specific tenant and can't be shared - in the general case it's highly recommended to have just one pool.
One way around this (as in the issue) is to manually set your search_path each time you switch to a new tenant.