WEBVTT 1 00:00:00.190 --> 00:00:01.860 Hi guys, and welcome back. 2 00:00:01.860 --> 00:00:06.070 In this video we're going to talk about connection pooling. 3 00:00:06.070 --> 00:00:08.820 Right now we have a problem with our app which is that we're 4 00:00:08.820 --> 00:00:13.190 constantly creating, using and then destroying connections 5 00:00:13.190 --> 00:00:15.840 in the create connections function which gets 6 00:00:15.840 --> 00:00:19.370 called in every method pretty much of our models. 7 00:00:19.370 --> 00:00:22.400 But creating connections is expensive and it's slow, 8 00:00:22.400 --> 00:00:26.320 so doing it over and over again is not ideal. 9 00:00:26.320 --> 00:00:28.480 Obviously in our application that is only running 10 00:00:28.480 --> 00:00:31.750 in our computer, It's not a big problem anyway, 11 00:00:31.750 --> 00:00:33.580 but if you have an application that's being used 12 00:00:33.580 --> 00:00:35.840 by a lot of people doing that over and over again 13 00:00:35.840 --> 00:00:38.020 all the time is a problem. 14 00:00:38.020 --> 00:00:40.930 So, instead we have connection pooling. 15 00:00:40.930 --> 00:00:43.130 We create a few connections at once, 16 00:00:43.130 --> 00:00:44.830 and we never destroy them. 17 00:00:44.830 --> 00:00:46.380 Whenever you need one, you grab it 18 00:00:46.380 --> 00:00:48.410 from the pool of connections, 19 00:00:48.410 --> 00:00:50.500 and when you're done you commit it and you put it back 20 00:00:50.500 --> 00:00:53.200 into the pool so that the next time you come back 21 00:00:53.200 --> 00:00:54.503 you can grab it again. 22 00:00:55.500 --> 00:00:59.440 That way, ideally your connection should always have a pool 23 00:00:59.440 --> 00:01:01.370 already created and available for you 24 00:01:01.370 --> 00:01:03.163 to grab whenever you need it. 25 00:01:04.270 --> 00:01:06.910 Connections in the connection pool are stored 26 00:01:06.910 --> 00:01:07.870 in our Python code. 27 00:01:07.870 --> 00:01:10.650 So, all the connection pools that we're gonna be doing, 28 00:01:10.650 --> 00:01:12.800 are Python connection pools. 29 00:01:12.800 --> 00:01:16.220 Essentially what we'll do is basically, make a list where 30 00:01:16.220 --> 00:01:20.270 we add and remove elements when they get put back or used. 31 00:01:20.270 --> 00:01:23.360 But psycopg2 has some connection pool classes 32 00:01:23.360 --> 00:01:25.890 that we can use as well, and we will be using them. 33 00:01:25.890 --> 00:01:28.660 A couple of questions that you may have is how many 34 00:01:28.660 --> 00:01:33.040 connections do you make in the pool when you start it off? 35 00:01:33.040 --> 00:01:35.180 Every connection consumes a bit of RAM 36 00:01:35.180 --> 00:01:36.700 in the database server. 37 00:01:36.700 --> 00:01:38.550 So, if you're running your database server locally, 38 00:01:38.550 --> 00:01:40.430 then that's your computer's RAM. 39 00:01:40.430 --> 00:01:42.590 If you're using a server on the internet, 40 00:01:42.590 --> 00:01:44.660 then that's that server's RAM. 41 00:01:44.660 --> 00:01:47.610 And sometimes due to that as well as other reasons 42 00:01:47.610 --> 00:01:49.920 there are hard limits on the number 43 00:01:49.920 --> 00:01:51.590 of connections you can have. 44 00:01:51.590 --> 00:01:55.280 It maybe 50, maybe a hundred, but there can be limits. 45 00:01:55.280 --> 00:01:59.190 So, you should aim for the lowest number of connections 46 00:01:59.190 --> 00:02:02.910 possible while still having enough for dealing with the 47 00:02:02.910 --> 00:02:05.350 number of users that will interact 48 00:02:05.350 --> 00:02:07.660 with your application at the same time. 49 00:02:07.660 --> 00:02:11.780 So, let's say that you've got 10 users simultaneously 50 00:02:11.780 --> 00:02:14.410 using your application, each one of them consuming 51 00:02:14.410 --> 00:02:16.540 one connection, usually you need to have 52 00:02:16.540 --> 00:02:19.320 probably 10 connections in your connection pool. 53 00:02:19.320 --> 00:02:22.630 Even if sometimes you get 12 users using your application 54 00:02:22.630 --> 00:02:25.090 at the same time, it's not a big deal. 55 00:02:25.090 --> 00:02:26.650 If you have 50 users, though, 56 00:02:26.650 --> 00:02:28.770 you may wanna create more connections. 57 00:02:28.770 --> 00:02:30.530 We'll talk about what happens when you run out 58 00:02:30.530 --> 00:02:31.930 of connections in a moment. 59 00:02:31.930 --> 00:02:33.300 But something else that can happen 60 00:02:33.300 --> 00:02:35.160 is you can run out of server RAM. 61 00:02:35.160 --> 00:02:36.550 If you have too many connections 62 00:02:36.550 --> 00:02:38.180 or you reach the connection limit, 63 00:02:38.180 --> 00:02:41.580 users are gonna be waiting for connections to free up. 64 00:02:41.580 --> 00:02:43.240 That can be a problem. 65 00:02:43.240 --> 00:02:46.440 So, if this happens because you've got too many users 66 00:02:46.440 --> 00:02:47.980 and you need too many connections to be open 67 00:02:47.980 --> 00:02:51.100 at the same time, consider splitting the database 68 00:02:51.100 --> 00:02:52.980 into multiple servers. 69 00:02:52.980 --> 00:02:55.070 in Postgres, we usually do that via sharding, 70 00:02:55.070 --> 00:02:56.830 which is not something we're covering in this course. 71 00:02:56.830 --> 00:03:00.100 It's more of a deployment and DevOps type thing, 72 00:03:00.100 --> 00:03:01.250 but it's something that can be done. 73 00:03:01.250 --> 00:03:04.490 You can run your same database in two servers 74 00:03:04.490 --> 00:03:08.030 so that you get basically double the number of connections. 75 00:03:08.030 --> 00:03:09.750 The other thing that can happen as something that we're more 76 00:03:09.750 --> 00:03:12.180 interested in is you can run out of connections 77 00:03:12.180 --> 00:03:13.890 in your connection pool. 78 00:03:13.890 --> 00:03:16.450 Usually the strategy is you start off your 79 00:03:16.450 --> 00:03:19.050 connection pool with as few connections as possible, 80 00:03:19.050 --> 00:03:22.070 and if you run out, you just create more, basically. 81 00:03:22.070 --> 00:03:24.620 It's a bit slow, but when you create a new one, 82 00:03:24.620 --> 00:03:26.140 you put it back in the pool when you're done, 83 00:03:26.140 --> 00:03:29.420 and essentially that increases the size of the pool. 84 00:03:29.420 --> 00:03:32.620 But many connections can be added over time. 85 00:03:32.620 --> 00:03:36.380 So, do be careful because you may run out of server RAM 86 00:03:36.380 --> 00:03:39.010 if you keep adding connections. 87 00:03:39.010 --> 00:03:42.390 It does mean that having a pool that is too small 88 00:03:42.390 --> 00:03:44.853 isn't the worst problem in the world. 89 00:03:45.790 --> 00:03:48.040 So, let's add this to our app. 90 00:03:48.040 --> 00:03:51.100 We can add connection pooling with this psycopg2 module. 91 00:03:51.100 --> 00:03:53.610 So, let's go ahead and do that in the next video. 92 00:03:53.610 --> 00:03:55.810 Thanks for watching, and I'll see you there.