Connection Pooling in PostgresSQL with NodeJS (Performance Numbers)

connection pooling is a pattern of creating a pool of available connection usually TCP and allow multiple clients to share this pool of connections the spanner is usually very useful when connection establishment and connection tearing down connection security it's 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 so what do you do right in this video we're gonna learn how first how to do this classically by basically creating a REST API that when you make a get request you establish a connection in the backend a database connection and then make a query and then close it it's like the old really bad way of doing things right but it's a stage listener but of the rest right so another way we're gonna do it it's like we're gonna spin up a pool of database connections and use a stateless query pool and see like gonna we're gonna just call the pool dot query which will pick up a random connection okay from this pool and then execute our query so that is we're gonna and then final we're gonna perform some performance numbers between the stateless approach which is like every request opens and closes the database connection at the back in and the pool connection point requests so stay tuned if you're interested in that if you're new here welcome my name is russain in this channel we discuss all sorts of software engineering by example so if we're gonna become a better software engineer consider subscribe and have that be like and so you get notified every time I upload and do video what I say let's just jump into this video all right guys so I have locally here a post Chris database and I have PG admin and I used docker to do all that stuff if you're interested to know how to do that I'm gonna reference the video that we have done in this channel I'm not gonna go through that and I have a post cos database here with the database called Jose in DB and I have a table called employees and those that employees and here's the old approach of doing things guys okay so I have here an and rest endpoint that I've written and this hits the back end which we're gonna show the code in and what does does is every time I hit enter it makes a it requests to their /all which in turn establishes a database connection with the PostGIS database execute the query select star from employees return the rose and then closes the database connection every single request does that okay so and that's what we get back beautiful rest of jason with old ass jana fancy dandy stuff right so what we gonna do here is it also tells you like that it lasts how long it took and the method that's why i just added some more metadata yet so it's gonna code and show you that how we used to do things and we have built a lot of code using that approach right alright guys so this is the back end here alright and i'm using express obviously in the PG library which we have discussed in this videos before i'm gonna reference the video guys if you're interested want to know like how do we do that from scratch right because I'm not gonna dive details into this I'm just gonna go through it real quick right so you can just check out that video that we did the introduction to all squares and nodejs so what we do is I get a client and when someone with excuse slash oh we just stabbed snaps the time before the execution open the connection to the database just provide the library so that's the database saying username password forward all that stuff and then connect that's establishes the TCP connection so there's like three way handshake and all that jazz going on the protocol handshake that's expensive that's very expensive right but we're doing it every get request we're doing that and then we extol establish a query we make a query to the database that's a worse query ever never never never never never ever ever do this in production select star is bad enough and unbounded query is even worse right I always have you you'd have to do proper paging with these kind of things you get back the results printed in a nice table like that close the connection also expensive right just like releasing the father's disc of toner and all that stuff and then get that new time because we're just done get that last time and then return the rose and just tell me how long it took right and that's it very simple stuff so how can i rewrite this using the pulling up Roger go ahead and create a brand new file called pool yeah okay so I created this full pulled ojs and here's what we're gonna do we're just gonna copy the whole code same thing because it's very minor the changes really here and here's what we're gonna do and instead of creating a client we're gonna create the pool we're gonna get the pool class and instead of doing creating a pool client object we're gonna 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 alright so we're gonna replace the client with the pool and here's the thing it's a pool right so how many connections do you want there's 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 10 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 it's gonna manage those napkin xions so that's the maximum number of connection it will allow you to create to create you don't you don't control that anymore as a user you just ask the pool execute this query for me it will pick one of those 20 connections write an executed query if there are no connections left all of them are being used or busy right you're gonna have to wait as a client right and there is a timeout for that okay and that timeout is called let's add both both properties let's talk about them so there's the connection timeouts 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 and shorten this time right and the final one is the ideal time I believe this the default is 10 second what idle timeout means essentially is after establishing the connection if those connection are not used when do you want me to get rid of it because it's a memory right so it's staking memory to you these file descriptors and in staking memory so when do you want me to destroy it this much time the after watch it will destroy zero means it will never get destroyed yeah so now we have the disconnection let's go back to our method we don't no longer need a client dot connect anymore what you can't ask the pool to give you and dedicate to the client and then you release it right that's especially it's good for using it transactions we are executing almost like a status squares here so I don't really care which one do you want so I'm gonna choose the pool dot query and query you asked the pool to query 2 at 662 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 is ensure I give the time and then get back there's and the method is not all this the pool method and that's it let's test it out guys so I'm gonna change the configuration to the pool dot yes let's go ahead and run so what are we gonna do not gonna see any dear friend guys but let's take a look so if I do now slash all here right you didn't see any difference it gives you the same results obviously but no this is using a pooling so it's actually faster because those pools are already available for you and it will gonna 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 ok and then the node.js is single senses as a single threaded application it will meant it's a non-blocking as well so it will despite having single right it will manage all these connection 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's the thing I'm gonna go ahead on actually creating an older J's file with both methods right so what I did is like I did a slash old method which executes our old pattern and give us the time and the average as well how long it took after I don't the total number of executions right I'm gonna sum all the attempts and that time it took and then take the average of the time and then we'll get a report the average over the time and then I'm gonna do the same thing with the pool right so and then on the client we're gonna execute like a hundred queries right on the pool and 100 records on the old and 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 pause the video and do all that so so all the J's is a has now it's a very interesting two endpoints slash pooling point to use the pool approach slash old approach to use the old approach which is always a stateless closing opening connections every time so let's go ahead and do that now we're doing the old let's go ahead and test it if I do slash old give the results fight or slash pool I get the result stell I don't see any reference but if I go to developer tools and here's what I'm gonna do then do a loop let's say thousand against the old thousands all bad much but sure execute and you can see that it is actually querying and you can see the average of the old is around forty milliseconds right and we got getting a lot of requests almost done I think done all the thousand queries has been executed successfully right so we have just created and destroyed thousand TCP connection in the backend right that's surprisingly fast right especially for for a local database connection right if it's a remote who's gonna you're gonna you're gonna be even slower than that so let's go ahead and change my approach to use pool hit the pool right and that will do is like do a fetch and then just expect adjacent and then just do a fish request we talked about fetch API manner of friends that if you are interested and let's do how they'll see how pool is doing oh that is way better forty milliseconds versus nineteen oh it's going down nice so I can see you guys it's almost like fifty percent faster and I guarantee you if you move to a remote database like in the cloud is gonna be even more than that right and the reason is like the more connections you have it's even getting better and better right so it's like almost like a resource issue right so that's licensure that's our video guys hope you enjoyed it and leave your leave your questions below if you if you have any questions that's the 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 in an 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 leave a comment below if you are interested to see that I'm gonna reference the code below it and so the DA and then OJ's doc I'll be enjoy this video give it a like share with your friends and and see you on the next one you guys stay awesome

Channel: Hussein Nasser

Views: 69,718

Keywords: software engineering, postgressql connection pooling, connection pooling javascript, connection pooling postgres, javascript connection pooling postgres, connection pool, connection poolg, pg pool, connection pooling

Id: GTeCtIoV2Tw

Length: 12min 1sec (721 seconds)

Published: Fri Nov 01 2019