Database Pooling when to use it and when not

Question

I've been researching the whole web about database pooling, but I still don't understand few things which I hope to find answer here from more experienced developers.

My understanding of database pooling is that when there are multiple clients connecting to the database, it makes sense to keep the connection in a "cache" and use that to connect to the database faster. What I fail to understand is how that would help if let's say I have a server that connects to the database and keeps the connection open. When a client requests data from an endpoint, the server will use the same connection to retrieve the data. How would pooling help in that case?

I'm sure I'm missing something in the understanding of pooling. It would make sense if multiple instances of the same database exist and therefore it's decided in the pool which database to connect to using the cached credentials. Is it what happens there?

Also could you give me a scenario where database pooling should be used and when not?

Thanks for clarifying any doubt of mine.

Answer 1

In .Net, connection pooling is handled automatically, not directly by your application.

All you need to do is open, use and close your connections normally and it will take care of the rest.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

If you're talking about a different platform, the mechanics are different, although the purpose is the same.

In all cases, it's time consuming to open and close connections to the DB server, so something between your application and the DB server (typically the database driver or some sort of middle-ware) will maintain a pool of open connections and create, kill and recycle them as necessary.

Pooling keeps the connections open and cuts down on the overhead of opening one for each request.

Also could you give me a scenario where database pooling should be used and when not?

Connection pooling useful in any application that uses the same database connection multiple times within the lifetime of the connection pool.

There would actually be a very slight decrease in performance if you had an application that used a single connection once, then didn't use it again until the connection pool had timed out and recycled. This is extremely uncommon in production applications.

What I fail to understand is how that would help if let's say I have a server that connects to the database and keeps the connection open.

If you have an application that opens a connection and leaves it open, then theoretically pooling would not help. Practically speaking, it's good to periodically kill and recreate various resources like connections, handles, sockets, etc. because software isn't perfect and some code contains resource leaks.

I'm just guessing, but suspect that you're concern is premature optimization. Unless you have done actual testing and determined that connection pooling is a problem, I wouldn't be too concerned with it. Connection pools are generally self-maintaining and almost always improve performance.

Answer 2

Connection pooling is handled differently in different application scenarios and platforms/languages.

The main consideration is that a database connection is a finite resource, and it costs time to establish it.

Connections are finite because most database servers impose a maximum limit on the number of concurrent connections (this may be part of your licensing terms). If your application uses more connections than the database allows, it may start rejecting connections (leading to error handling requirements in the client app), or making connections wait (leading to poor response times). By configuring a connection pool, the client application can manage these scenarios in a central place.

Secondly, managing connections is a bit of a pain - there are lots of different error handling scenarios, configuration settings etc.; it's a good idea to centralize this. You can, of course, do that without a connection pool.

Thirdly, connections are "expensive" resources - they take time to establish. Most web pages require several database queries; if each query spends just 1 tenth of a second creating a database connection, you're very quickly spending noticable time waiting for database connections. By using a connection pool, you avoid this overhead.