Connection Pooling with PostgreSQL JDBC

Question

Recently I downloaded the JDBC driver for PostgreSQL from here. Since I'm using Java 1.7 JVM and it's written:

If you are using the 1.6 or 1.7 JVM, then you should use the JDBC4 version.

I download JDBC4. The problem is there exist no PoolingDataSource's in it. If you get JDBC3 you can use org.postgresql.jdbc3.Jdbc3PoolingDataSource or others as is seen here.

Is there any pooling DataSource in JDBC4 that I don't know about, or what should I use instead? The only thing I found in JDBC4 is PGPoolingDataSource but I'm not sure if I'm supposed to use this because based on their Java doc message:

Don't use this if your server/middleware vendor provides a connection pooling implementation which interfaces with the PostgreSQL ConnectionPoolDataSource implementation!

Answer 1

Use org.postgresql.ds.PGPoolingDataSource
Here is an example: http://jdbc.postgresql.org/documentation/head/ds-ds.html
I've checked this example using JDBC4 driver and it worked fine.

However in documentation from this link they discourage from using postgreSQL pooling data source because of it's limitations:

The pooling data-source implementation provided here is not the most feature-rich in the world. Among other things, connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connections requested for users other than the default configured user are not pooled. Its error handling sometimes cannot remove a broken connection from the pool. In general it is not recommended to use the PostgreSQL˘ provided connection pool. Check your application server or check out the excellent jakarta commons DBCP project.

They recommend to use DBCP connection pool: http://commons.apache.org/proper/commons-dbcp/ check it, it is much better - just download library files, place them in a classpatch and import to the project, documentation from the above link contains examples how to use it in code.

Most (all?) application servers implements their own connection pools, if you are using the application server, it's the best option.
For Example Tomcat 7 has it's own implementation of the connection pool, it's even better than DBCP, check documentation: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html