What is database pooling?

Question

Database connection pooling is a method used to keep database connections open so they can be reused by others.

Typically, opening a database connection is an expensive operation, especially if the database is remote. You have to open up network sessions, authenticate, have authorisation checked, and so on. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used in preference to having to create another one.

Refer to the following diagram for the next few paragraphs:

  +---------+
  |         |
  | Clients |
+---------+ |
|         |-+  (1)   +------+   (3)    +----------+
| Clients | ===#===> | Open | =======> | RealOpen |
|         |    |     +------+          +----------+
+---------+    |         ^
               |         | (2)
               |     /------\
               |     | Pool |
               |     \------/
           (4) |         ^
               |         | (5)
               |     +-------+   (6)   +-----------+
               #===> | Close | ======> | RealClose |
                     +-------+         +-----------+

In it's simplest form, it's just a similar API call (1) to an open-connection API call which is similar to the "real" one. This first checks the pool for a suitable connection (2) and, if one is available, that's given to the client. Otherwise a new one is created (3).

A "suitable connection" is just one that already has access to the database using the correct information (such as database instance, credentials, and possibly other things).

Similarly, there's a close API call (4) which doesn't actually call the real close-connection, rather it puts the connection into the pool (5) for later use. At some point, connections in the pool may be actually closed (6).

That's a pretty simplistic explanation. Real implementations may be able to handle connections to multiple servers and multiple user accounts, they may pre-allocate some baseline of connections so some are ready immediately, and they may actually close old connections when the usage pattern quietens down.

Images speak a thousand words (paxdiablo gave an awesome description):

Source

As the name suggests. If a few people wants to swim, they can swim in the same swimming-pool, does it really make sense to construct a new swimming-pool each time someone adds in ? Time and cost is a priority.

Database connection pooling is simply caching connections to databases so that they can be reused next time to reduce the cost of establishing a new connection each time we want to connect to a database.

Connection Pooling concept not only in Java but across many programming languages. Creating a new connection object is costly so a fixed number of connections are made and maintained in lifecycle creating a virtual pool Java Just ( http://javajust.com/javaques.html ) see question 14 on this page