Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0

You can choose between 3 different strategies that will impact connection polling. In any case you have to provide an implementation of MultiTenantConnectionProvider. The strategy you choose will of course impact your implementation.

General remark about MultiTenantConnectionProvider.getAnyConnection()

getAnyConnection() is required by hibernate to collect metadata and setup the SessionFactory. Usually in a multi-tenant architecture you have a special/master database (or schema) not used by any tenant. It's a kind of template database (or schema). It's ok if this method returns a connection to this database (or schema).

Strategy 1 : each tenant have it's own database. (and so it's own connection pool)

In this case, each tenant have it's own connection pool managed by C3PO and you can provide an implementation of MultiTenantConnectionProvider based on AbstractMultiTenantConnectionProvider

Every tenant have it's own C3P0ConnectionProvider, so all you have to do in selectConnectionProvider(tenantIdentifier) is to return the correct one. You can keep a Map to cache them and you can lazy-initialize a C3POConnectionProvider with something like :

private ConnectionProvider lazyInit(String tenantIdentifier){    C3P0ConnectionProvider connectionProvider = new C3P0ConnectionProvider();    connectionProvider.configure(getC3POProperties(tenantIdentifier));    return connectionProvider;}private Map getC3POProperties(String tenantIdentifier){    // here you have to get the default hibernate and c3po config properties     // from a file or from Spring application context (there are good chances    // that those default  properties point to the special/master database)     // and alter them so that the datasource point to the tenant database    // i.e. : change the property hibernate.connection.url     // (and any other tenant specific property in your architecture like :    //     hibernate.connection.username=tenantIdentifier    //     hibernate.connection.password=...    //     ...) }

Strategy 2 : each tenant have it's own schema and it's own connection pool in a single database

This case is very similar to the first strategy regarding ConnectionProvider implementation since you can also use AbstractMultiTenantConnectionProvider as base class to implement your MultiTenantConnectionProvider

The implementation is very similar to the suggested implementation for Strategy 1 except that you must alter the schema instead of the database in the c3po configuration

Strategy 3 : each tenant have it's own schema in a single database but use a shared connection pool

This case is slightly different since every tenant will use the same connection provider (and so the connection pool will be shared). In the case : the connection provider must set the schema to use prior to any usage of the connection. i.e. You must implement MultiTenantConnectionProvider.getConnection(String tenantIdentifier) (i.e. the default implementation provided by AbstractMultiTenantConnectionProvider won't work).

With postgresql you can do it with :

 SET search_path to <schema_name_for_tenant>;

or using the alias

 SET schema <schema_name_for_tenant>;

So here is what your getConnection(tenant_identifier); will look like:

@Overridepublic Connection getConnection(String tenantIdentifier) throws SQLException {    final Connection connection = getAnyConnection();    try {        connection.createStatement().execute( "SET search_path TO " + tenanantIdentifier );    }    catch ( SQLException e ) {        throw new HibernateException(                "Could not alter JDBC connection to specified schema [" +                        tenantIdentifier + "]",                e        );    }    return connection;}

Useful reference is here (official doc)

Other useful link C3POConnectionProvider.java

You can combine strategy 1 and strategy 2 in your implementation. You just need a way to find the correct connection properties/connection url for the current tenant.

EDIT

I think that the choice between strategy 2 or 3 depends on the traffic and the number of tenants on your app. With separate connection pools : the amount of connections available for one tenant will be much lower and so: if for some legitime reason one tenant need suddenly many connections the performance seen by this particular tenant will drastically decrease (while the other tenant won't be impacted).

On the other hand, with strategy 3, if for some legitime reason one tenant need suddenly many connections: the performance seen by every tenant will decrease.

In general , I think that strategy 2 is more flexible and safe : every tenant cannot consume more than a given amount of connection (and this amount can be configured per tenant if you need it)

IMHO, the connection pool management will be default handled by the Sql Server itself, however some programming languages like C# do offer some ways to control the pools. Refer here

The choice of (1) schema or (2) separate database for a tenant depends upon the volume of the data that you can anticipate for the tenant. However, the following consideration can be worth looking into

1. create a shared schema model for the trial customers and the lowvolume customers, this can be identified by the number of thefeatures that you provide to a tenant during the process ofonboarding a customer

2. when you create or onboard a enterprise level customer that mayhave a large transactional data, it is ideal to go for a separatedatabase.

3. The schema model may have a different implementation for SQL Serverand a different one for the MySQL Server, which you should consider.

4. also when choosing for the option, do consider the fact that a customer [tenant] may be willing to scale out after a considerable amount of time and system usage. If there is no appropriate scale out option supported in your app, you will have to be bothered.

Share your comments on the above points, to take this discussion further