212

I just wanted to know the concept of database connection pooling and how it is achieved.

Wing
  • 8,438
  • 4
  • 37
  • 46
sagar27
  • 3,121
  • 3
  • 27
  • 37

5 Answers5

336

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)
              |    /------\     +---------+ (6) +-----------+
              |    | Pool | --> | Cleaner | ==> | RealClose |
              |    \------/     +---------+     +-----------+
          (4) |       ^
              |       |(5)
              |    +-------+
              #==> | Close |
                   +-------+

In it's simplest form, it's just an API call (1) to an Open API call which is similar to the "real" one, RealOpen. 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) and given to the client.

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 RealClose, rather it puts the connection back into the pool (5) for later use. At some point, connections in the pool may be actually closed (6). This could be done by a thread that continuously monitors the pool and calls RealClose if they are old enough or certain other conditions are met.

That's a pretty simplistic explanation. Real implementations may be arbitrarily more complex such as the previously mentioned:

  • handling connections to multiple servers and using multiple user accounts;
  • using arbitrary rules to decide when connections should be really shut down, things like its age, how many similar connections there are, and so on.

Database connection pooling is a specific case for a more general one, that of maintaining cached things where they may be expensive to start. As you see from a similar answer of mine, it can apply to thread pools as well (or pools of backup tapes, communication devices, laser cutters, or dozens of other different things).

In all cases, it's best if it's "expensive" to bring another resource online rather than use one that had previously been online.

That linked answer also shows one possible algorithm for deciding when to start up or (fully) shut down a resource.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 20
    Where do you have the nice little graph from? – Adrian Grigore Oct 28 '10 at 08:53
  • 40
    I made it from scratch (silly me). If you want to see some decent graphics, have a look at zengr's answer. – paxdiablo Oct 28 '10 at 08:55
  • 2
    If one is available, that's given to the client, otherwise a new one is created.Similarly, there's a close API call which doesn't actually call the real close-connection, rather it puts the connection into the pool for later use. As your mention this, I have little question. When 1000 client request connection and not closed. So 1000 connection is alive in pool.This will good for pool performance?? And let me know my understanding is little wrong?? – Ye Win Mar 26 '15 at 08:59
  • 1
    @YeWin, no that sounds good. Re your question about 1000 connections being left in the pool, that can happen but generally only if you end up at some point with 1000 concurrent active connections. Otherwise, there'd be re-use and it wouldn't reach 1000. In terms of that happening, see my penultimate paragraph, particularly the "may actually close old connections when the usage pattern quietens down" bit. – paxdiablo Mar 26 '15 at 09:08
  • 1
    Ohh!Thank you for your reply and I understand well what you really point out. So we assume 1000 concurrent active connections is rare case?? – Ye Win Mar 26 '15 at 09:25
  • 1
    good explanation! Btw, how long did it take to make the draw ? –  Apr 05 '16 at 08:21
  • 3
    @DiegoMariani, slower than if I'd done it by hand, faster than if I'd try to coerce MS Word into making it easier :-) – paxdiablo Apr 06 '16 at 01:48
  • What does the graph mean? It is obscure. Could you at least put numbers there to represent order of connection steps – Green May 20 '17 at 02:10
  • > _opening a database connection is an expensive operation..._ I don't understand it. Do you mean that keeping 10, 20, 30, you name it number of connections ___opened___ is less costly for memory and overall system performance than opening one connection when required? How it can be? 30 vs. 1 is less costly? – Green May 20 '17 at 02:19
  • @Green, the 'costly' comment has more to do with time, especially when the DB is remote. All that session establishment, authentication, authorisation, and so on, is not free. If that can be bypassed by using an existing connection, it'll save some time. In terms of understanding, please read paras 3 and 4, they should hopefully make the steps clear re what happens on open and close. – paxdiablo May 20 '17 at 04:12
  • @Green: However, I'll try to clarify it a little re one *possible* set of steps. – paxdiablo May 20 '17 at 04:34
  • This may be obvious to some but I was confused how the client could take a connection from the pool without having to go through authentication. It should be noted in this case that only connections with the same configuration can be pooled (i.e. connections can be separated into pools by connection string). – Matthew Thomas Dec 01 '21 at 04:43
  • @paxdiablo While reading answer just amazed by how well it is explained but when finished & see you written that answer then I don't wonder. – Abhishek Mane Dec 21 '22 at 10:27
159

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

alt text

Source

zengr
  • 38,346
  • 37
  • 130
  • 192
  • 61
    And apparently, _good_ images speak a few hundred pieces of ASCII art as well :-) – paxdiablo Oct 28 '10 at 08:51
  • @sagar please select the answer which you found to be most useful. You have no accept record. – zengr Nov 24 '10 at 07:01
  • 2
    I seen 4 connection in Pool.So connection number is restrict in this pool by pooling type??Or what will happen when connection is not free in Pool? Client need to wait connection free?? – Ye Win Mar 26 '15 at 08:34
  • @zengr. Please reply for Ye Win question. – deadend Feb 28 '17 at 05:35
  • 2
    @DEADEND It really depends on how the connection pool is implemented. Most of the pools create a new connection when connections reach the max capacity. This can keep on growing till the db reaches a threshold. In some case (like oracle jdbc) you can specify "initial size" and "max size" during pool construction itself. – zengr Feb 28 '17 at 18:07
  • 6
    Unfortunately, the image doesn't say the most important thing. That is: __Why keeping 10, 20, 30, ... number of connections opened is less costly for memory and overall system performance than opening one connection when required?__ How it can be? 30 vs. 1 is less costly? How? – Green May 20 '17 at 02:23
40

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.

vdegenne
  • 12,272
  • 14
  • 80
  • 106
  • 1
    Is there any limitation on the number of people in the swimming pool? Like the number of connections connected to the database? When is the time for database to start a new connection? – RobotCharlie Sep 23 '21 at 01:12
19

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.

Supriya
  • 1,940
  • 24
  • 23
Peter
  • 302
  • 3
  • 7
2

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

Sandhya Saini
  • 29
  • 1