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?

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'. — Basil Bourque
– Basil Bourque, Commented Jul 14, 2017 at 22:44

Basil Bourque · Accepted Answer · 2017-07-14 22:42:42Z

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:

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.
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.

edited Jul 14, 2017 at 22:42

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

answered Jan 27, 2016 at 5:06

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Basil Bourque Over a year ago

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

Collectives™ on Stack Overflow

Postgres JDBC DataSource instance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related