Why are database connection pools better than a single connection?

Question

I'm currently working on writing a multithreaded application that will need to access a database in order to serve requests. I see many people saying that using a pool of many persistent database connections is the way to go for this type of application, but I'm trying to wrap my head around why exactly this is the case.

Keep in mind that I'm designing this application in Erlang, so I'll be using threads/processes/workers a lot.

So let's compare two situations:

1. You have a single thread that owns a single database connection. All your client-handling-threads talk to this thread in order to make database queries.

2. You have a pool of threads, each with their own database connection. When a client-handling-thread wants to access the database, it gets one of these threads from the pool, and uses that to query the DB.

In the first case, I see many people saying that it is bad because having one thread handling all database related queries will in turn cause a bottleneck. But my confusion is the following: Wouldn't the bottleneck in that single thread actually be the database itself? If all that the thread is doing is querying the database through its connection handle, isn't waiting for the DB to respond to requests the main source of latency? How will throwing more connections threads at this problem solve it?

Answer 1

The database probably has well-developed multithreading abilities. Using a connection pool allows:

1. Make use of the DB's multithreading / load-balancing ability
2. Avoid the overhead of setting up and tearing down connections over and over

When the database is serving multiple connections, it can make its own decisions on how to prioritize requests. Imagine this scenario:

1. User A requests a set of records from Table A with 100,000 rows
2. User B requests a set of records from Table B with 50 rows
3. User C updates Table A

If multiple connections are used, the DB can take advantage of the fact that (1) and (2) can occur concurrently, and User B gets his 50 records without having to wait for User A to get all 100,000 of his. Only User C has to wait for User A to finish.

Also, setting up and tearing down TCP connections is a relatively expensive task. Using a pool allows one user to release the resource without tearing down the TCP connection, so the next user doesn't have to wait for a new connection. Your single-threaded approach wouldn't benefit from this aspect of connection-pooling, though.