The downside of a map of pools is that it is not as dynamic as a design that just "prefers" affinity with an arbitrary key. The map of pools works well when you are connection rich and tenant poor, however when you have a scenario where you may be facing 2x tenants to db connections with sparse usage across tenants in bursts, you end up having to create a lot of new connections. The per user datasource would accomplish that wouldn't it, of course it requires more sql user creation, configuration and whatnot to go along with that.
Also this would require knowing all of the configuration at startup time, new tenants should be able to be added without restart and just dynamically route properly and minimize the USE statements, what I am seeking is a much more dynamic solution, something where connections are routed (with a USE statement) only when necessary, and selection from the pool would just favor the ones with affinity. I know it sounds simple and of course the devil is in the details, but I have yet to be able to explain why that would not work. thanks! -jesse On Wed, Nov 8, 2023 at 11:36 PM Romain Manni-Bucau <rmannibu...@gmail.com> wrote: > Hi jessie, > > Think it can makes sense but not sure it fits in commons-dbcp "core" since > the best is generally to not merge the connection in the same pool but use > N pools and enable the database to inject a custom "router"/selector > (that's what spring or tomee did for ex [1]/[2]). > The rational to do it like that is to leverage autosizing and > optimizations. > Indeed you can add a map on top of the pool in the pool but I'm not sure > the benefit so it should mainly be importing the facade and router api and > facade existing pools (which must all keep a distinct configuration, > customer1=size=200, customer2=size=10 for ex, this is where keeping N > pooled datasources is way easier than merging it in a single instance -> > getCurrentDataSource() ;)). > > Side note: if used with JPA it must NOT be used with JPA cache nor used for > transversal things like app-pool+customer-pool in the same transaction AND > it will make XA usage delegated to the underlying datasource if needed and > not to the pool directly. > > So my 2cts would be to create a new package (dynamic or whatever name you > like) and import the facade and create the selecting api and be it, will > also fit when the database is distinct, schema or just the sizing is > different per customer to avoid side effects of one customer on another one > on reactive apps. > > Hope it makes sense. > > [1] > > https://github.com/apache/tomee/blob/bc8f54e3f5b1674b3f6ced4bd564a1c48c32c5bc/container/openejb-core/src/main/java/org/apache/openejb/resource/jdbc/RoutedDataSource.java > [2] > > https://github.com/spring-projects/spring-framework/blob/main/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/lookup/AbstractRoutingDataSource.java > > Best, > Romain Manni-Bucau > @rmannibucau <https://twitter.com/rmannibucau> | Blog > <https://rmannibucau.metawerx.net/> | Old Blog > <http://rmannibucau.wordpress.com> | Github < > https://github.com/rmannibucau> | > LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book > < > https://www.packtpub.com/application-development/java-ee-8-high-performance > > > > > Le jeu. 9 nov. 2023 à 00:39, Gary Gregory <garydgreg...@gmail.com> a > écrit : > > > Hi Jesse, > > > > Please take a look at Commons DBCP and how your proposal would fit in. > > > > Gary > > > > On Wed, Nov 8, 2023, 4:49 PM Jesse Harris <jesse.har...@veeva.com > .invalid> > > wrote: > > > > > Would there be any interest in contributing an implementation of a > multi > > > tenant connection pool implementation? > > > > > > One implementation of a multi-tenant architecture is to have a schema > per > > > tenant, the downside is that you need to set the default database on > each > > > connection pulled from the pool, this creates a lot of overhead. > Ideally > > a > > > pool with "affinity" based on an arbitrary key, pull a connection > already > > > prepared for the given schema, if there is not one, steal one from > > another > > > schema and reset the default schema in that case. > > > > > > The problem with the per-user pool is that it requires a per schema > user, > > > also when stealing connections you have to recreate the socket when the > > > user changes. > > > > > > thanks in advance > > > -jesse > > > > > >
