Why is the connection pool for many open connections for a system less expensive than opening a new connection each time?

Question

Answer here

As a rule, opening a connection to a database is an expensive operation, so the union maintains active connections, so when a connection is later requested, one of the active ones is used in preference to open the other.

I understand the concept of Connection Pool in database management. This is the answer to the question " what is ~ ". All developer blog posts, answers, tutorials, DB databases always answer the " what is " question. Just like they constantly copy / paste text into each other. No one is trying to explain why this is so and how . The answer given above is an example for him.

I cannot understand why and how it is possible that keeping, say, 30 open connections in a pool is less expensive for the system than opening a new one when required.

Suppose I have a web server located in Australia. And the database at AWS located in the USA, somewhere in Oregon. Or in GB. Or anywhere, but very far from AUS. So they all say that keeping a pool of 20 -... open connections will be less expensive for memory and system performance than for opening a new connection every time in this case? How can it be? Why?

Answer 1

In your scenario, I think the biggest problem is network latency. You cannot expect communication between servers located on two different continents to be especially fast. Therefore, if you initiate a new connection every time you need it, you will experience this delay every time.

From here :

Connecting to a database server usually consists of several time steps. A physical channel must be established, such as a socket or named pipe, the initial handshake with the server must occur , the connection string information must be parsed, the connection must be authenticated by the server, the scan must be started to be included in the current transaction, etc.

In addition, if the connection between the web server and the database uses SSL / TLS, there should be a handshake that should be performed on each new connection before the actual connection (in addition to the usual handshake that occurs with regular connections). This handshake is expensive in terms of time.

From here :

Before the client and server begin to exchange application data via TLS, the encrypted tunnel must be negotiated: the client and server must agree on the version of the TLS protocol, select ciphersuite and, if necessary, check the certificates. Unfortunately, each of these steps requires a new batch transition between the client and server, which adds a delayed start for all TLS connections. (...) As you can see from the above exchange, new TLS connections require two callbacks for a “full handshake” - this is bad news

When you use the connection pool, this overhead is avoided by regularly sending something like a “ping” message to the SQL server from time to time to avoid a timeout connection due to inactivity. Of course, this may consume more memory on your server, but these days it is a much less expensive resource. Network delays are inevitable.