Postgres JDBC DataSource instance

Question

I am trying to use Postgresql with Spring.

The way it was written was to initiate DataSource instance for each query/specific set of queries that was about to be executed.

So, the question is, is it a good idea to keep only one instance for the entire lifetime of the app or do as is now?

Will this lead to any memory leak if many instances are used here?

• Basil Bourque: Do not conflate DataSource with Connection. An instance of the first provides instances of the second. Really a misnomer, 'DataSource' should have been named 'ConnectionSource'.

Answer 1

Instantiating a new DataSource for every request is a bad idea because of the overhead of establishing the connection to the database server. There are two design patterns that you can choose from:

1. A DataSource is instantiated for every session or instance of your application. The advantage is that you can tailor the instance with all the details being used in the original application. The downside is that you may need many sessions - eating up resources both on the server and the client - that may be idle most of the time in between requests - but still using up those resources.
2. Use a JNDI (Tutorial) connection pool. Connections to the database are shared between sessions. This is generally much more efficient because connections stay open and will be used more intensively. On the down side, every connection has all connection details set - including user name and password - and this might not work very well with your database design.

To clarify:

• If every user of your application has their own role in the database they should set their own role name and password when opening the connection: connection pooling is not possible and you should create a DataSource for every session.
• If the users of your application use a single role in the database - say "webuser" - then you can use connection pooling with the details of that role. This would also work if your application has a few such roles: "webuser", "webmanager", "webadmin". You then have to create a DataSource for each of those roles in the JNDI store.

• Basil Bourque: Good Answer, but to be clear, a DataSource stored in a JNDI compliant server is not necessarily backed by a connection pool.