1
00:00:00,060 --> 00:00:05,130
‫Connection cooling is a pattern of creating a pool of available connection, usually DCP, and allow

2
00:00:05,130 --> 00:00:07,680
‫multiple clients to share this pool of connections.

3
00:00:07,830 --> 00:00:14,910
‫This pattern is usually very useful when connection, establishment and connection Thiering down connection.

4
00:00:14,940 --> 00:00:18,540
‫Security is particularly expensive, which is the case with databases.

5
00:00:18,540 --> 00:00:19,310
‫Right, right, guys.

6
00:00:19,650 --> 00:00:24,690
‫And that's also useful when the server has very limited number of database connections.

7
00:00:24,730 --> 00:00:25,160
‫Right.

8
00:00:25,320 --> 00:00:28,260
‫And you have a lot of clients at what you do.

9
00:00:28,260 --> 00:00:28,620
‫Right.

10
00:00:28,920 --> 00:00:35,280
‫In this video, we're going to learn how first how to do this classically by basically creating a rest

11
00:00:35,280 --> 00:00:35,970
‫API.

12
00:00:36,180 --> 00:00:41,430
‫When you make a request, you establish a connection at the back, end a database connection and then

13
00:00:41,550 --> 00:00:42,970
‫make a query and then close it.

14
00:00:42,990 --> 00:00:46,850
‫That's like the old really bad way of doing things right.

15
00:00:47,190 --> 00:00:48,630
‫But it's a stateless nevertheless.

16
00:00:48,630 --> 00:00:48,850
‫Right.

17
00:00:48,870 --> 00:00:53,730
‫So another way we're going to do it is like we're going to spin up a pool of database connections.

18
00:00:53,730 --> 00:01:00,900
‫We to just call the pool dot query, which will pick up a random connection, OK, from this pool and

19
00:01:00,900 --> 00:01:02,090
‫then execute our query.

20
00:01:02,100 --> 00:01:07,890
‫So that is we're going to and then finally we're going to perform some performance numbers between the

21
00:01:08,550 --> 00:01:13,890
‫statelets approach, which is like every request opens and closes the database connection and the backend

22
00:01:14,040 --> 00:01:16,190
‫and the pool connection point course.

23
00:01:16,290 --> 00:01:16,560
‫All right.

24
00:01:16,590 --> 00:01:24,570
‫So I have locally here a Postgres database and I have PJI admin and I have a database here with a database

25
00:01:24,570 --> 00:01:28,620
‫called Hosain DB and I have a table called Employees and those employees.

26
00:01:28,890 --> 00:01:33,540
‫And here's the old approach of doing things, guys.

27
00:01:33,840 --> 00:01:40,020
‫So I have here and angrist endpoint I have written and this hits the back end, which we're going to

28
00:01:40,020 --> 00:01:40,950
‫show the code in.

29
00:01:40,950 --> 00:01:50,100
‫And what does does is every time I hit enter, it makes a get request to this all, which in turn establishes

30
00:01:50,100 --> 00:01:54,900
‫a database connection with the Bosco's database, execute the query, select staff from employees,

31
00:01:55,170 --> 00:01:58,410
‫return the rows and then closes that Abess connection.

32
00:01:58,410 --> 00:02:00,290
‫Every single request does that.

33
00:02:01,020 --> 00:02:02,360
‫So and that's what we get back.

34
00:02:02,370 --> 00:02:06,780
‫Beautiful rest of JSON with all that fancy Dandi stuff.

35
00:02:06,780 --> 00:02:07,050
‫Right.

36
00:02:07,320 --> 00:02:09,870
‫So what are we going to do here?

37
00:02:09,880 --> 00:02:15,850
‫Is it also tells you, like the elapsed, how long it took and the method that I just added some method,

38
00:02:15,900 --> 00:02:16,620
‫more metadata.

39
00:02:16,890 --> 00:02:20,120
‫So it's going to show you that how we used to do things.

40
00:02:20,130 --> 00:02:20,370
‫All right.

41
00:02:20,490 --> 00:02:21,990
‫So this is the back end here.

42
00:02:22,110 --> 00:02:22,520
‫All right.

43
00:02:22,650 --> 00:02:26,290
‫And I'm using Express, obviously, in the PPG library.

44
00:02:26,310 --> 00:02:34,320
‫So what we do is I get a client and when someone is excused, all we just stab Snapp's the time before

45
00:02:34,320 --> 00:02:38,880
‫the execution, open the connection to the database just for by their library.

46
00:02:38,910 --> 00:02:45,180
‫So that's the database was in the username, password, password, all that stuff and then connect that

47
00:02:45,180 --> 00:02:46,770
‫establishes the TCP connection.

48
00:02:46,780 --> 00:02:50,130
‫So there's like a three way handshake and all that jazz going on.

49
00:02:50,130 --> 00:02:53,460
‫The protocol handshake that's expensive, that's very expensive.

50
00:02:54,090 --> 00:02:56,700
‫But we're doing it every gets request.

51
00:02:56,730 --> 00:02:57,510
‫We're doing that.

52
00:02:58,020 --> 00:03:02,580
‫And then we establish a query, we make a query through the database.

53
00:03:02,580 --> 00:03:03,540
‫That's a war square.

54
00:03:03,540 --> 00:03:07,380
‫Never, never, never, never, never, never, ever, ever do this.

55
00:03:07,850 --> 00:03:13,230
‫And production select star is bad enough and inbounded queries even worse.

56
00:03:13,920 --> 00:03:15,780
‫Always have you.

57
00:03:15,810 --> 00:03:19,110
‫You'd have to do a proper paging with these kind of thing.

58
00:03:19,950 --> 00:03:22,770
‫You get the results printed in a nice table like that.

59
00:03:23,490 --> 00:03:24,350
‫Close the connection.

60
00:03:24,360 --> 00:03:32,100
‫Also expensive, just like releasing the file, the and all that stuff and then get a new time because

61
00:03:32,100 --> 00:03:36,640
‫we've just done that last time and then return the rows and just tell me how long it took.

62
00:03:37,650 --> 00:03:39,230
‫And that's a very simple stuff.

63
00:03:39,480 --> 00:03:45,720
‫So how can I rewrite this using the pulling up to go ahead and create a brand new file called Pool?

64
00:03:46,050 --> 00:03:51,360
‫OK, so I created this file folder, Jazz, and here's why we are just going to copy the whole chord.

65
00:03:51,600 --> 00:03:53,970
‫Same thing, because it is very minor.

66
00:03:53,970 --> 00:03:56,640
‫The changes are here and here's what we're to do.

67
00:03:56,850 --> 00:04:00,300
‫And instead of creating a client, we're going to create the pool.

68
00:04:00,300 --> 00:04:02,040
‫We're going to get the pool class.

69
00:04:02,340 --> 00:04:09,060
‫And instead of doing creating a pool, a client object, we're going to create a pool object.

70
00:04:09,390 --> 00:04:13,350
‫And obviously, this is bad to have it in the every request.

71
00:04:13,350 --> 00:04:16,490
‫We want to execute it once when we start our server.

72
00:04:16,710 --> 00:04:16,880
‫All right.

73
00:04:16,920 --> 00:04:19,890
‫So we're going to replace the the client with the pool.

74
00:04:20,010 --> 00:04:20,940
‫And here's the thing.

75
00:04:20,940 --> 00:04:21,930
‫It's a pool, right?

76
00:04:21,940 --> 00:04:24,600
‫So how many connections do you want?

77
00:04:24,750 --> 00:04:27,060
‫There is the first parameter, which is called Max.

78
00:04:27,060 --> 00:04:27,410
‫All right.

79
00:04:27,690 --> 00:04:31,320
‫And the max parameter is how many connections do you want by default?

80
00:04:31,620 --> 00:04:33,280
‫The default, I believe, is then.

81
00:04:33,450 --> 00:04:33,640
‫Right.

82
00:04:33,720 --> 00:04:38,730
‫But you can specify any number based on, like, how healthy you want your Postgres database to be.

83
00:04:39,150 --> 00:04:39,410
‫Right.

84
00:04:39,420 --> 00:04:43,140
‫So you specify a number and there's going to manage those connections.

85
00:04:43,140 --> 00:04:47,520
‫So that's the maximum number of connections that will allow you to create to create.

86
00:04:47,790 --> 00:04:50,850
‫You don't you don't control that anymore as a user.

87
00:04:50,970 --> 00:04:54,360
‫You just ask the pool, hey, execute this query for me.

88
00:04:54,450 --> 00:04:56,610
‫It will pick one of those twenty connections.

89
00:04:56,790 --> 00:04:57,180
‫Right.

90
00:04:57,180 --> 00:04:59,760
‫And execute a query if there are no.

91
00:04:59,850 --> 00:05:04,170
‫Connections left, all of them are being used or busy, right?

92
00:05:04,190 --> 00:05:08,120
‫You're going to have to wait as a client and there is a time out for that.

93
00:05:08,360 --> 00:05:14,000
‫And that time out is called let's add both properties and talk about them.

94
00:05:14,240 --> 00:05:17,970
‫So that's the connection time outs, right after which the default is zero.

95
00:05:18,140 --> 00:05:21,980
‫So how long I should wait for a pool to give me a connection?

96
00:05:21,980 --> 00:05:25,760
‫If all of them are busy, zero means wait forever.

97
00:05:26,360 --> 00:05:29,530
‫You can you can decide to shorten this time.

98
00:05:29,560 --> 00:05:29,840
‫Right.

99
00:05:30,440 --> 00:05:32,870
‫And the final one is the ideal time.

100
00:05:32,870 --> 00:05:35,460
‫I believe the default is ten second.

101
00:05:35,510 --> 00:05:42,290
‫What ideal time out means essentially is after establishing that connection, if those connections are

102
00:05:42,290 --> 00:05:44,990
‫not used, when do you want me to get rid of it?

103
00:05:44,990 --> 00:05:46,600
‫Because it's a memory, right?

104
00:05:46,680 --> 00:05:51,610
‫It's it's a stake in memory or using these file descriptors and undertaking memory.

105
00:05:51,620 --> 00:05:53,660
‫So when do you want me to destroy it?

106
00:05:53,990 --> 00:05:58,520
‫This much time after it will destroy zero means it will never get destroyed.

107
00:05:58,820 --> 00:05:59,080
‫Yeah.

108
00:05:59,330 --> 00:06:01,020
‫So now we have this connection.

109
00:06:01,040 --> 00:06:02,240
‫Let's go back to our method.

110
00:06:03,130 --> 00:06:10,810
‫We don't longer need a client to connect anymore, but you can ask the pool to give you a dedicated

111
00:06:10,810 --> 00:06:12,590
‫client and then you release it, right?

112
00:06:12,610 --> 00:06:15,100
‫That's especially it's good for using the transactions.

113
00:06:15,370 --> 00:06:19,120
‫We are executing almost like a stately squares here.

114
00:06:19,120 --> 00:06:21,340
‫So I don't really care which one do you want.

115
00:06:21,340 --> 00:06:25,630
‫So I'm going to choose the pool dot query and query.

116
00:06:26,380 --> 00:06:32,860
‫You ask the pool to execute this query and the pool will take care of which connection it will use to

117
00:06:32,860 --> 00:06:33,700
‫execute that query.

118
00:06:33,850 --> 00:06:34,750
‫Get back the results.

119
00:06:35,080 --> 00:06:38,200
‫We really don't need to end the client because there's nothing here.

120
00:06:38,470 --> 00:06:42,040
‫I know what we want to do here as insurers give the time and then get back.

121
00:06:42,040 --> 00:06:42,880
‫There is the method.

122
00:06:42,880 --> 00:06:44,470
‫Is not all this the pool method?

123
00:06:44,830 --> 00:06:45,370
‫And that's it.

124
00:06:46,270 --> 00:06:48,010
‫Let's test it out, guys.

125
00:06:48,220 --> 00:06:52,900
‫So I'm going to change the configuration to the pool dot jass.

126
00:06:54,260 --> 00:06:55,350
‫Let's go ahead and run.

127
00:06:55,370 --> 00:06:56,540
‫So what are we going to do?

128
00:06:56,570 --> 00:06:59,150
‫You're not going to see any of your friend guys, but let's take a look.

129
00:06:59,240 --> 00:07:02,230
‫If I do no longer all here, right.

130
00:07:02,600 --> 00:07:03,710
‫You didn't see any difference.

131
00:07:03,740 --> 00:07:05,270
‫It gives you the same results, obviously.

132
00:07:05,270 --> 00:07:06,920
‫But now this is using polling.

133
00:07:06,920 --> 00:07:12,890
‫So it's actually faster because those pools are already available for you and it will going to pick

134
00:07:12,890 --> 00:07:13,250
‫that.

135
00:07:13,250 --> 00:07:17,660
‫And then there is no overhead of establishing and closing the connection every time.

136
00:07:17,670 --> 00:07:20,060
‫So that's always a better approach to do that.

137
00:07:20,540 --> 00:07:25,400
‫And that and the jazz is single instances as a single threaded application.

138
00:07:25,730 --> 00:07:27,860
‫It will it's a non blocking as well.

139
00:07:27,860 --> 00:07:33,740
‫So it will, despite having single thread, will manage all these connections for you without actually

140
00:07:33,740 --> 00:07:36,500
‫blocking this thing, which is pretty cool stuff.

141
00:07:37,190 --> 00:07:42,440
‫So what can we do to prove that this is actually good?

142
00:07:42,440 --> 00:07:42,790
‫Right.

143
00:07:43,010 --> 00:07:44,330
‫So here is the thing.

144
00:07:44,360 --> 00:07:51,830
‫I'm going to go ahead and actually a and all the jazz file with both methods.

145
00:07:51,830 --> 00:07:52,160
‫Right.

146
00:07:52,190 --> 00:07:59,330
‫So what I did is like I did a slash old method which executed our old pattern and give us the time and

147
00:07:59,330 --> 00:08:00,290
‫the average as well.

148
00:08:00,290 --> 00:08:04,400
‫How long it took after, I don't know, the total number of executions.

149
00:08:04,400 --> 00:08:04,620
‫Right.

150
00:08:04,670 --> 00:08:07,190
‫I'm going to sum all their attempts.

151
00:08:07,190 --> 00:08:11,570
‫And that time it took and then take the average of the time and then we'll get a report of the average

152
00:08:11,570 --> 00:08:14,230
‫over time and then got to do the same thing with the pool.

153
00:08:14,260 --> 00:08:14,460
‫Right.

154
00:08:14,730 --> 00:08:20,810
‫So and then on the client, we're going to execute like 100 queries right on the pool and 100 records

155
00:08:20,810 --> 00:08:22,590
‫and then see the average between the two.

156
00:08:22,820 --> 00:08:23,460
‫How about that?

157
00:08:23,480 --> 00:08:24,580
‫Does that sound good, guys?

158
00:08:24,920 --> 00:08:27,950
‫The code will be available for you guys in the description below.

159
00:08:27,950 --> 00:08:30,500
‫So you don't have to, like, pull the video and do all that stuff.

160
00:08:30,860 --> 00:08:34,010
‫So all the jazz is has now.

161
00:08:34,400 --> 00:08:41,600
‫It's a very interesting two and points magpul and point to use the pool approach, slash all approach

162
00:08:41,600 --> 00:08:46,490
‫to use the old approach, which is always a stateless closing, opening connections.

163
00:08:46,490 --> 00:08:53,060
‫Every time I go ahead and do that now we're doing the old let's go ahead and test it.

164
00:08:53,480 --> 00:08:59,420
‫If I do this old get the results, if I do a will, I get the result felt I don't see any difference.

165
00:08:59,420 --> 00:09:05,810
‫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

166
00:09:06,470 --> 00:09:09,090
‫against the old thousands.

167
00:09:09,110 --> 00:09:10,400
‫All that much but sure.

168
00:09:10,760 --> 00:09:11,420
‫Execute.

169
00:09:12,680 --> 00:09:19,520
‫And you can see that it is actually -- and you can see the average of the old is around 40 milliseconds,

170
00:09:19,520 --> 00:09:19,810
‫right?

171
00:09:20,960 --> 00:09:26,900
‫And we are getting a lot of requests almost done, I think, done all the thousand queries has been

172
00:09:26,900 --> 00:09:28,170
‫executed successfully.

173
00:09:28,460 --> 00:09:28,780
‫Right.

174
00:09:28,970 --> 00:09:29,690
‫So we have.

175
00:09:31,310 --> 00:09:35,060
‫Created and destroyed thousands TCBY connection in the back end.

176
00:09:35,600 --> 00:09:40,230
‫Not surprisingly, fast, right, especially fought for our local database connection.

177
00:09:40,230 --> 00:09:40,480
‫Right.

178
00:09:40,510 --> 00:09:44,210
‫If it's a remote is going to you're going to you're going to be even slower than that.

179
00:09:44,510 --> 00:09:49,010
‫So let's go ahead and change my approach to use pool.

180
00:09:49,960 --> 00:09:56,430
‫Hit the pool, right, and that wall does does like do a fish and then just expect Jason and then just

181
00:09:56,440 --> 00:10:01,060
‫and let's do how those see how pool is doing.

182
00:10:01,480 --> 00:10:03,650
‫Oh, that is way better.

183
00:10:03,940 --> 00:10:08,380
‫Forty milliseconds versus, you know, it's going down nice.

184
00:10:09,000 --> 00:10:12,100
‫So I can see you guys, it's almost like 50 percent faster.

185
00:10:12,110 --> 00:10:17,560
‫And I guarantee you if you move to a remote that it's like in the cloud is going to be even more than

186
00:10:17,560 --> 00:10:17,920
‫that.

187
00:10:17,920 --> 00:10:18,220
‫Right.

188
00:10:18,460 --> 00:10:22,560
‫And the reason is like the more connections you have, it's even getting better and better.

189
00:10:23,080 --> 00:10:24,970
‫So it's like almost like a resource issue.

190
00:10:24,970 --> 00:10:25,270
‫Right.

191
00:10:25,600 --> 00:10:29,110
‫That's a stateless query that we have explained.

192
00:10:29,110 --> 00:10:29,280
‫Right.

193
00:10:29,290 --> 00:10:30,880
‫So we just query.

194
00:10:30,880 --> 00:10:38,350
‫But sometimes you want to execute a series of queries in an acid manner and then atomic manner.

195
00:10:38,470 --> 00:10:45,460
‫And to do that, you can ask the pool to give you a client and you can lock that client for you and

196
00:10:45,460 --> 00:10:47,020
‫then execute multiple queries.

197
00:10:47,020 --> 00:10:47,220
‫Right.

198
00:10:47,500 --> 00:10:48,580
‫And gets you on the next one.

199
00:10:48,700 --> 00:10:50,100
‫You guys stay awesome.