‫Connection cooling is a pattern of creating a pool of available connection, usually DCP, and allow
‫multiple clients to share this pool of connections.
‫This pattern is usually very useful when connection, establishment and connection Thiering down connection.
‫Security is particularly expensive, which is the case with databases.
‫Right, right, guys.
‫And that's also useful when the server has very limited number of database connections.
‫Right.
‫And you have a lot of clients at what you do.
‫Right.
‫In this video, we're going to learn how first how to do this classically by basically creating a rest
‫API.
‫When you make a request, you establish a connection at the back, end a database connection and then
‫make a query and then close it.
‫That's like the old really bad way of doing things right.
‫But it's a stateless nevertheless.
‫Right.
‫So another way we're going to do it is like we're going to spin up a pool of database connections.
‫We to just call the pool dot query, which will pick up a random connection, OK, from this pool and
‫then execute our query.
‫So that is we're going to and then finally we're going to perform some performance numbers between the
‫statelets approach, which is like every request opens and closes the database connection and the backend
‫and the pool connection point course.
‫All right.
‫So I have locally here a Postgres database and I have PJI admin and I have a database here with a database
‫called Hosain DB and I have a table called Employees and those employees.
‫And here's the old approach of doing things, guys.
‫So I have here and angrist endpoint I have written and this hits the back end, which we're going to
‫show the code in.
‫And what does does is every time I hit enter, it makes a get request to this all, which in turn establishes
‫a database connection with the Bosco's database, execute the query, select staff from employees,
‫return the rows and then closes that Abess connection.
‫Every single request does that.
‫So and that's what we get back.
‫Beautiful rest of JSON with all that fancy Dandi stuff.
‫Right.
‫So what are we going to do here?
‫Is it also tells you, like the elapsed, how long it took and the method that I just added some method,
‫more metadata.
‫So it's going to show you that how we used to do things.
‫All right.
‫So this is the back end here.
‫All right.
‫And I'm using Express, obviously, in the PPG library.
‫So what we do is I get a client and when someone is excused, all we just stab Snapp's the time before
‫the execution, open the connection to the database just for by their library.
‫So that's the database was in the username, password, password, all that stuff and then connect that
‫establishes the TCP connection.
‫So there's like a three way handshake and all that jazz going on.
‫The protocol handshake that's expensive, that's very expensive.
‫But we're doing it every gets request.
‫We're doing that.
‫And then we establish a query, we make a query through the database.
‫That's a war square.
‫Never, never, never, never, never, never, ever, ever do this.
‫And production select star is bad enough and inbounded queries even worse.
‫Always have you.
‫You'd have to do a proper paging with these kind of thing.
‫You get the results printed in a nice table like that.
‫Close the connection.
‫Also expensive, just like releasing the file, the and all that stuff and then get a new time because
‫we've just done that last time and then return the rows and just tell me how long it took.
‫And that's a very simple stuff.
‫So how can I rewrite this using the pulling up to go ahead and create a brand new file called Pool?
‫OK, so I created this file folder, Jazz, and here's why we are just going to copy the whole chord.
‫Same thing, because it is very minor.
‫The changes are here and here's what we're to do.
‫And instead of creating a client, we're going to create the pool.
‫We're going to get the pool class.
‫And instead of doing creating a pool, a client object, we're going to create a pool object.
‫And obviously, this is bad to have it in the every request.
‫We want to execute it once when we start our server.
‫All right.
‫So we're going to replace the the client with the pool.
‫And here's the thing.
‫It's a pool, right?
‫So how many connections do you want?
‫There is the first parameter, which is called Max.
‫All right.
‫And the max parameter is how many connections do you want by default?
‫The default, I believe, is then.
‫Right.
‫But you can specify any number based on, like, how healthy you want your Postgres database to be.
‫Right.
‫So you specify a number and there's going to manage those connections.
‫So that's the maximum number of connections that will allow you to create to create.
‫You don't you don't control that anymore as a user.
‫You just ask the pool, hey, execute this query for me.
‫It will pick one of those twenty connections.
‫Right.
‫And execute a query if there are no.
‫Connections left, all of them are being used or busy, right?
‫You're going to have to wait as a client and there is a time out for that.
‫And that time out is called let's add both properties and talk about them.
‫So that's the connection time outs, right after which the default is zero.
‫So how long I should wait for a pool to give me a connection?
‫If all of them are busy, zero means wait forever.
‫You can you can decide to shorten this time.
‫Right.
‫And the final one is the ideal time.
‫I believe the default is ten second.
‫What ideal time out means essentially is after establishing that connection, if those connections are
‫not used, when do you want me to get rid of it?
‫Because it's a memory, right?
‫It's it's a stake in memory or using these file descriptors and undertaking memory.
‫So when do you want me to destroy it?
‫This much time after it will destroy zero means it will never get destroyed.
‫Yeah.
‫So now we have this connection.
‫Let's go back to our method.
‫We don't longer need a client to connect anymore, but you can ask the pool to give you a dedicated
‫client and then you release it, right?
‫That's especially it's good for using the transactions.
‫We are executing almost like a stately squares here.
‫So I don't really care which one do you want.
‫So I'm going to choose the pool dot query and query.
‫You ask the pool to execute this query and the pool will take care of which connection it will use to
‫execute that query.
‫Get back the results.
‫We really don't need to end the client because there's nothing here.
‫I know what we want to do here as insurers give the time and then get back.
‫There is the method.
‫Is not all this the pool method?
‫And that's it.
‫Let's test it out, guys.
‫So I'm going to change the configuration to the pool dot jass.
‫Let's go ahead and run.
‫So what are we going to do?
‫You're not going to see any of your friend guys, but let's take a look.
‫If I do no longer all here, right.
‫You didn't see any difference.
‫It gives you the same results, obviously.
‫But now this is using polling.
‫So it's actually faster because those pools are already available for you and it will going to pick
‫that.
‫And then there is no overhead of establishing and closing the connection every time.
‫So that's always a better approach to do that.
‫And that and the jazz is single instances as a single threaded application.
‫It will it's a non blocking as well.
‫So it will, despite having single thread, will manage all these connections for you without actually
‫blocking this thing, which is pretty cool stuff.
‫So what can we do to prove that this is actually good?
‫Right.
‫So here is the thing.
‫I'm going to go ahead and actually a and all the jazz file with both methods.
‫Right.
‫So what I did is like I did a slash old method which executed our old pattern and give us the time and
‫the average as well.
‫How long it took after, I don't know, the total number of executions.
‫Right.
‫I'm going to sum all their attempts.
‫And that time it took and then take the average of the time and then we'll get a report of the average
‫over time and then got to do the same thing with the pool.
‫Right.
‫So and then on the client, we're going to execute like 100 queries right on the pool and 100 records
‫and then see the average between the two.
‫How about that?
‫Does that sound good, guys?
‫The code will be available for you guys in the description below.
‫So you don't have to, like, pull the video and do all that stuff.
‫So all the jazz is has now.
‫It's a very interesting two and points magpul and point to use the pool approach, slash all approach
‫to use the old approach, which is always a stateless closing, opening connections.
‫Every time I go ahead and do that now we're doing the old let's go ahead and test it.
‫If I do this old get the results, if I do a will, I get the result felt I don't see any difference.
‫But if I go to a developer tools and here's what I'm going to do, then do a loop, let's say a thousand
‫against the old thousands.
‫All that much but sure.
‫Execute.
‫And you can see that it is actually -- and you can see the average of the old is around 40 milliseconds,
‫right?
‫And we are getting a lot of requests almost done, I think, done all the thousand queries has been
‫executed successfully.
‫Right.
‫So we have.
‫Created and destroyed thousands TCBY connection in the back end.
‫Not surprisingly, fast, right, especially fought for our local database connection.
‫Right.
‫If it's a remote is going to you're going to you're going to be even slower than that.
‫So let's go ahead and change my approach to use pool.
‫Hit the pool, right, and that wall does does like do a fish and then just expect Jason and then just
‫and let's do how those see how pool is doing.
‫Oh, that is way better.
‫Forty milliseconds versus, you know, it's going down nice.
‫So I can see you guys, it's almost like 50 percent faster.
‫And I guarantee you if you move to a remote that it's like in the cloud is going to be even more than
‫that.
‫Right.
‫And the reason is like the more connections you have, it's even getting better and better.
‫So it's like almost like a resource issue.
‫Right.
‫That's a stateless query that we have explained.
‫Right.
‫So we just query.
‫But sometimes you want to execute a series of queries in an acid manner and then atomic manner.
‫And to do that, you can ask the pool to give you a client and you can lock that client for you and
‫then execute multiple queries.
‫Right.
‫And gets you on the next one.
‫You guys stay awesome.