Multi-Tenant setup with schemas

[Dave Smith]

I am currently using Bonecp with one connection pool per schema. Since I have lots of customers with few users (< 10) this is horribly inefficient. I am looking to switch to Hikari with just one connection pool and have the schema set when a connection is pulled out of the pool. Is this currently possible?

[Brett Wooldridge]

Well, HikariCP isn't going to do the work for you, but depending on the database, it is possible.  On some databases you can call Connection.setCatalog(), on others you can execute SQL like ("use database1"), and on still others (PostgreSQL) it is simply not possible.

But just so you know, it is not as inefficent as you might think.  We have one user that has literally several thousand pools in the same JVM.  Granted, you should probably set minimumIdle to 0 in this case, and a fairly aggressive idleTimeout.

-Brett

[Dave Smith]

We use Postgresql , I believe "set schema to x" will do the trick, but I will need to call it after the connection is pulled out of the poll and before it is used. is there a hook for it...

[Brett Wooldridge]

The existing hook (connectionCustomizer) is deprecated in the current release and has already been removed from the codebase.  To understand why, and available alternatives, please read this other post:

https://groups.google.com/d/msg/hikari-cp/9n-N5W0FIOI/2EHZefJw3jcJ

[Jens]

 and on still others (PostgreSQL) it is simply not possible.

Since Java 1.7 you can call connection.setSchema() and the standard postgres jdbc driver (at least a recent one) has implemented that method.

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Connection.java#L1273

But just so you know, it is not as inefficent as you might think.  We have one user that has literally several thousand pools in the same JVM.  Granted, you should probably set minimumIdle to 0 in this case, and a fairly aggressive idleTimeout.

Right, at work we also have one pool per customer with minimumIdle set to 0 and a reasonable idleTimeout. Works well. 

-- J.

[Dave Smith]

There are two points here..

1. Even if I proxy the connection I will still need to know when it is pulled from the pool so I can do setSchema on it , I do not want to do that before each prepareStatement , would isValid() be a good spot for it?

2. If you have multiple pools with an aggressive idleTimeout then the question becomes why bother pooling in the first place? What is the cost of doing a prepare/excuteStatement for the schema change vs opening a new connection? If you workload is users busy for a few minutes then idle for a few minutes it looks like we will be opening and closing connections at a great rate. Does anybody have any metrics on this?

[Brett Wooldridge]

You don't need to proxy the Connection.  Write a wrapper DataSource, implement getConnection() to delegate to HikariDataSource, and before returning the Connection execute connection.setSchema("xxx").

Executing setSchema() at getConnection()-time should be much faster than new connection setup.  In general it will be a big win, even with aggressive timeouts.  But it should be trivial to benchmark with something like ap-bench.

-Brett

[Dave Smith]

That will not work, since once you have the connection it will be in the pool, then you will keep handing it out.  I am assuming getConnection only is called if we need to add a connection to the pool?

[Brett Wooldridge]

Then flip it around, implement the wrapper DataSource to delegate to PostgreSQL JDBC, on getConnection() delegate to them, then call setSchema().  Configure your wrapper DataSource as the data source that HikariCP should call for connections.

-Brett

[Jens]

Then flip it around, implement the wrapper DataSource to delegate to PostgreSQL JDBC, on getConnection() delegate to them, then call setSchema().  Configure your wrapper DataSource as the data source that HikariCP should call for connections.

Hu that would be bad, wouldn't it? If you just have a single pool then the app gets connections for a random schema. You really want a wrapper that delegates to hikari and then on each getConnection() call adjust the schema as needed before handed out to the app.

Also while I don't have any metrics I would not set the idleTimeout too aggressive. Currently we have it set to 10 minutes. If our customers do constant work then the pool adjusts the amount of connections to what is needed to handle the work load. Only if the work load decreases for at least 10 minutes then some connections are killed via the idleTimeout.

-- J.

[Dave Smith]

Right , and I do not see that.

public final Connection getConnection(final long hardTimeout) throws SQLException Does final PoolBagEntry bagEntry = connectionBag.borrow(timeout, TimeUnit.MILLISECONDS);  and returns return ProxyFactory.getProxyConnection((HikariPool) this, bagEntry, leakTask.start(bagEntry)); Somewhere in between we need to do setSchema on the connection ...

[Brett Wooldridge]

Which was my original suggestion:

class WrapperDataSource implements DataSource {

   private HikariDataSource ds;

   ...

   public Connection getConnection() {

      Connection c = ds.getConnection();

      c.setSchema(whatever);

      return c;

   }

   ...

}

Setting your schema at connection checkout-time should not introduce much overhead, probably single digit milliseconds.

-Brett