How to decide the best database connection pooling library

What is the flow of  database connections in application?

•  Application asks the data source for a database connection.
•  Datasource uses the database driver to open a database connection.
•  Connection is created and TCP socket is opened.
•  Application performs the database operation.
• Connection is closed and the socket is closed.

What are the expensive things we have to consider?

This involves opening and closing connections which are expensive operations. This is an extra strain on the database management system to open / close connections.

So the best option is to reuse the database connections. This can reduce OS I/O overhead of opening/closing TCP connections.

Why do we need pooling mechanism?

Whenever a connection is requested, the data source will use the available connection pool to acquire a new connection. 

The pool will only create new connections when there are no connections available and a pool is not at maximum size.

 When the close() method is called on the connection, it’s not closed but returned to the connection pool. 

 So reducing the overhead of creating/opening the new connections and making use of existing connections, connection pools are much faster.

Criteria to select a connection pool library:

• Reliability
• Performance
• Features
• It’s important to look at the connection strategies:
• Do they reset connection properties when they return to the pool? E.g. auto-commit, isolation level
• Do they support statement caching?
• Ease of use

Available Connection Pool Libraries 

• Apache Commons DBCP2: DBCP is out of date and not production grade. 
• C3P0:
• Tomcat JDBC:
• HikariCP:

HikariCP:    

• Very light library, around 130 kb.
• Tests connections at getConnection()
• Tracks and closes abandoned connections.
• Clears connections before returning the connection to the client.
• Resets auto-commit, transaction isolation, read-only status
• The library has 58 active contributors and has a very detailed configuration page with performance benchmarks.
• The library has 33 issues listed on GitHub
• Licensed under Apache 2.0

Winner:

Tomcat JDBC and HikariCP seem to be reliable and faster than other two connection pools. Both the communities are very active. Their codebase is smaller than other two. 

HikariCP being too young. Young though it may be it has had several billion transactions run through it. As with anything, I would suggest you try it in a pre-production environment before deployment

Availability with Spring-boot

Spring Boot will look for HikariCP on the classpath and use it by default when present

If HikariCP is not found on the classpath, then Spring Boot will pick up the Tomcat JDBC Connection Pool, if it's available

If neither of these options is available, Spring Boot will choose Apache Commons DBCP2, if that is available

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<dependency>

<groupId>org.apache.tomcat</groupId>

<artifactId>tomcat-jdbc</artifactId>

<scope>compile</scope>

<optional>true</optional>

</dependency>

<dependency>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

<scope>compile</scope>

<optional>true</optional>

</dependency>