Should I have a single database connection or one connection per task?

Question

I have a Java server and PostgreSQL database.

There is a background process that queries (inserts some rows) the database 2..3 times per second. And there is a servlet that queries the database once per request (also inserts a row).

I am wondering should I have separate Connection instances for them or share a single Connection instance between them? Also does this even matter? Or is PostgreSQL JDBC driver internally just sending all requests to a unified pool anyway?

One more thing should I make a new Connection instance for every servlet request thread? Or share a Connection instance for every servlet thread and keep it open the entire up time?

By separate I mean every threads create their own Connection instances like this:

Connection connection = DriverManager.getConnection(url, user, pw);

Use a [connection pool](http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc) and don't get caught up with Connection management. Solved problems are the best problems :). — ProgrammerDan, Sep 28 '15 at 15:25
Building on what @ProgrammerDan said, a lot of JavaEE servers have connection pooling built-in... you just have to do a JNDI lookup to get a connection. — Powerlord, Sep 28 '15 at 15:32

score 4 · Answer 1 · answered Sep 28 '15 at 16:06

If you use a single connection and share it, only one thread at a time can use it and the others will block, which will severely limit how much your application can get done. Using a connection pool means that the threads can have their own database connections and can make concurrent calls to the database server.

See the postgres documentation, "Chapter 10. Using the Driver in a Multithreaded or a Servlet Environment":

A problem with many JDBC drivers is that only one thread can use a Connection at any one time --- otherwise a thread could send a query while another one is receiving results, and this could cause severe confusion.

The PostgreSQL™ JDBC driver is thread safe. Consequently, if your application uses multiple threads then you do not have to worry about complex algorithms to ensure that only one thread uses the database at a time.

If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full). If it is a fast-path call (e.g., reading a block from a large object) then it consists of sending and retrieving the respective data.

This is fine for applications and applets but can cause a performance problem with servlets. If you have several threads performing queries then each but one will pause. To solve this, you are advised to create a pool of connections. When ever a thread needs to use the database, it asks a manager class for a Connection object. The manager hands a free connection to the thread and marks it as busy. If a free connection is not available, it opens one. Once the thread has finished using the connection, it returns it to the manager which can then either close it or add it to the pool. The manager would also check that the connection is still alive and remove it from the pool if it is dead. The down side of a connection pool is that it increases the load on the server because a new session is created for each Connection object. It is up to you and your applications' requirements.

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

As per my understanding,You should defer this task to the container to manage connection pooling for you.

As you're using Servlets,which will be running in a Servlet container, and all major Servlet containers that I'm aware of provide connection pool management.

Should I have a single database connection or one connection per task?

2 Answers2

See Also