What really is connection pooling?

You should use connection pooling whenever the time to establish a connection is greater than zero (pretty much always) and when there is a sufficient average usage such that the connection is likely to be used again before it times out.

Advantages are it's much faster to open/close new connections as they're not really opened and closed, they're just checked out/in to a pool.

Disadvantage would be in some connection pools you'll get an error if all pooled connections are in use. This usually is a good thing as it indicates a problem with the calling code not closing connections, but if you legitimately need more connections than are in the pool and haven't configured it properly, you could get errors where you wouldn't otherwise.

And of course there will be other pros and cons depending on the specific environment you're working in and database.

The idea is that you do not open and close a single connection to your database, instead you create a "pool" of open connections and then reuse them. Once a single thread or procedure is done, it puts the connection back into the pool and, so that it is available to other threads. The idea behind it is that typically you don't have more than some 50 parallel connections and that opening a connection is time- and resource- consuming.

When should i consider using connection pooling?

• Always for production system.

What are the advantages and disadvantages of connection pooling?

Advantages:

• Performance. Use a fixed pool of connection and avoid the costly creation and release of connections.
• Shared infrastructure. If your database is shared between several apps, you don't want one app to exhaust all connections. Pooling help to limit the number of connection per app.
• Licensing. Depending on your database license, the number of concurrent client is limited. You can set a pool with the number of authorized connections. If no connection is available, client waits until one is available, or times out.
• Connectivity issue. The connection pool that is between the client and the database, can provide handy features such as "ping" test, connection retry, etc. transparently for the client. In worse case, there is a time-out.
• Monitoring. You can monitor the pool, see the number of active connections, etc.

Disadvantage:

• You need to set it up and configure it, which is really peanuts usually.