Oracle Shared Connection Pools

[Richard Bair]

Hi,

I'm working on a multi-tenant SaaS application and wanted to make use of HikariCP for my connection pools. I have an Oracle database. Each tenant that I onboard into this SaaS app will have its own PDB (or schema, but we're leaning toward PDB) in the Oracle DB. On the front-end our REST API servers are simply stateless Jetty servers. For this to work, I need to be able to have a connection pool from which I can grab a connection for any tenant, and have it connect to the appropriate PDB for that tenant, but reuse the same underlying connection with the Oracle DB. Otherwise I'll have too many connections on the database, or have to setup and tear down connections constantly.

Oracle has this document on shared connection pools: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjucp/shared-pool-for-multitenant-data-sources.html#GUID-7C397AA8-9C78-4DB0-AAF4-15BBF9AFFB85

With that background, I'm probably going to ask a couple dumb questions, so I apologize in advance. I either haven't managed to craft the right google search or what I'm looking for isn't supported :-).

1. Does HikariCP have the underlying concepts that supported sharing the same underlying physical connection to an Oracle database across multiple "logical" connections in the connection pool?

2. Does it make any sense to layer HikariCP over Oracle's UCP, such that the UCP is used as a "dumb" layer and all the "smart" connection pooling (scaling it out, scaling it back in, etc) happens at the HikariCP level?

Thanks!
Richard

[Brett Wooldridge]

In this case, you're going to be better off just going with UCP instead of HikariCP.  HikariCP does not have any concept of a "shared" pool of Connections that point at different schemas.  This requires special service-aware APIs (see here) that do not make sense in a general purpose pooling solution like HikariCP.

Naive solutions that we have seen some pools take, such as setting the 'schema' on Connection borrow, completely defeat pooling on some databases.  For example, if you call Connection.setSchema(xxx) on the MySQL driver, it simply dumps the existing connection and re-establishes a new one under the covers, which is fine in the base case but in the context of pooling completely defeats it.

The good news is, UCP doesn't completely suck.

-Brett