1 00:00:00,049 --> 00:00:03,780 Michael: Hello and welcome to Postgres fm, a weekly show about all things PostgreSQL. 2 00:00:04,070 --> 00:00:08,630 I am Michael, founder of pgMustard, and this is my co-host Nikolay, founder of Postgres ai. 3 00:00:09,079 --> 00:00:10,430 Hey, Nicolay, what are we going to be talking about? 4 00:00:11,344 --> 00:00:12,004 Nikolay: Hi, Michael. 5 00:00:12,004 --> 00:00:15,424 Storage, procedures, functions, triggers. 6 00:00:15,774 --> 00:00:16,505 What else? 7 00:00:17,236 --> 00:00:17,776 Michael: Yeah. 8 00:00:17,806 --> 00:00:24,106 So this is the, the age of debate of service side, Well, Postgres side logic install procedures on front. 9 00:00:24,356 --> 00:00:28,010 Nikolay: database side we can, we can mention other databases as well. 10 00:00:28,416 --> 00:00:37,566 this is very old topic I remember in 2002 I was working on with Oracle and SQL Server and we had a lot of logic inside. 11 00:00:37,822 --> 00:00:39,872 P pl, SQL or TS SQL code. 12 00:00:40,192 --> 00:00:45,715 Michael: Yeah, I, that even might be a great place to start with this topic because I think with my experience with those 13 00:00:45,765 --> 00:00:54,713 platforms as well was people used stored procedures a lot more in SQL Server and Oracle than I've seen them doing in Postgres. 14 00:00:54,732 --> 00:01:00,972 My experience is varied, and I think maybe that was larger companies and my experience more recently has been with smaller 15 00:01:00,972 --> 00:01:06,642 companies, so maybe that's a difference, but I definitely saw it a lot more often in the SQL seven, Oracle Worlds. 16 00:01:06,958 --> 00:01:07,618 Nikolay: Right, right. 17 00:01:07,888 --> 00:01:18,251 So this is very old debatable topic similar to oms and there are many people on both sides big fans of story procedures 18 00:01:18,251 --> 00:01:22,391 and this, like love toto procedures can, can be very acute. 19 00:01:22,684 --> 00:01:25,309 , like, wow, it's so, convenient and so on. 20 00:01:25,369 --> 00:01:30,484 But at the same time, much more people say they have problems. 21 00:01:30,734 --> 00:01:37,614 When we say, when I say store procedures, of course in postal terminology, I, I involve functions as well because procedure, 22 00:01:37,854 --> 00:01:42,814 procedure work appeared only a couple of years ago when it was added. 23 00:01:42,944 --> 00:01:44,948 And in post 12, I think I 24 00:01:44,983 --> 00:01:45,923 Michael: I read 11. 25 00:01:46,103 --> 00:01:46,403 Yeah. 26 00:01:46,793 --> 00:01:47,513 Nikolay: maybe 11. 27 00:01:47,513 --> 00:01:53,212 I remember my Microsoft SQL server guys laughed at me saying, Oh, only now 28 00:01:54,232 --> 00:01:56,032 Finally you have transaction control. 29 00:01:56,037 --> 00:01:56,542 Okay? 30 00:01:56,632 --> 00:02:04,009 It was like it was friendly laughing, fortunately, but in commercial relational databases it was considered 31 00:02:04,014 --> 00:02:06,248 normal to write a lot of code on server side. 32 00:02:06,248 --> 00:02:15,119 And, if you take any bank software, you'll find a lot of such code, but maybe banks are not a good example because we know, like, 33 00:02:15,119 --> 00:02:23,162 especially in the US, they are so outdated and Actually, I observe them as a user, And they have issues with software, definitely. 34 00:02:23,552 --> 00:02:28,672 And I considered usually, like, in Russia, banking is very, well, it's, very good applications and so on. 35 00:02:28,942 --> 00:02:35,122 But since I'm on the opposite side of earth, I see, I saw recently, I am already not using them, but, 36 00:02:35,138 --> 00:02:38,588 last several years, I saw their downsides as well. 37 00:02:38,588 --> 00:02:40,665 They still have nightly update. 38 00:02:40,982 --> 00:02:42,782 You know, like this approach is still there. 39 00:02:43,181 --> 00:02:51,401 I mean, I mean regular approach when you have a lot of server site code, but it's quite heavy, so it needs to be 40 00:02:51,521 --> 00:02:55,841 executed only once per day, so you have some window and so on. 41 00:02:55,961 --> 00:03:02,588 It's like, you try to log in at your daytime in the us but you cannot because you, you need to wait a couple of hours. 42 00:03:02,588 --> 00:03:04,028 You know, it's because it's not there. 43 00:03:04,478 --> 00:03:06,888 So it, it's a sign that software is not modern. 44 00:03:06,918 --> 00:03:09,048 Modern software doesn't, should not do this. 45 00:03:09,053 --> 00:03:10,878 It should be always available. 46 00:03:11,418 --> 00:03:18,114 And uh, of course if you have a lot of code on server side, be question when it will be a. 47 00:03:19,060 --> 00:03:19,300 . All right. 48 00:03:19,360 --> 00:03:28,555 This is one of the aspects, but before we discuss some details, again, I think a much more people prefer to avoid storage procedures. 49 00:03:28,783 --> 00:03:30,433 I just observe it much more. 50 00:03:30,433 --> 00:03:34,236 Like I have experience of discussions like, We have today. 51 00:03:34,866 --> 00:03:41,199 And usually a reaction from audience is like, a few people really support, but a lot of people discuss 52 00:03:41,629 --> 00:03:43,629 downsides and saying like, We, we should try to avoid it. 53 00:03:44,319 --> 00:03:46,657 And uh, that's why I, say I'm not normal. 54 00:03:46,657 --> 00:03:49,718 I'm, I'm a big fan of store procedures, but I know their downsides. 55 00:03:50,393 --> 00:03:51,773 Functions, procedures, triggers. 56 00:03:52,013 --> 00:03:54,503 I know that there are downsides, but I'm huge fan. 57 00:03:54,503 --> 00:03:58,175 I, I can explain why, but I consider myself as not normal. 58 00:03:58,175 --> 00:04:00,125 Normal is to try to avoid them. 59 00:04:00,545 --> 00:04:02,815 This is our normal what's your observ? 60 00:04:03,400 --> 00:04:12,842 Michael: yeah, I'm, I'm similar but with a slight caveat that I don't use them myself, and we don't use them in a very small database application. 61 00:04:12,874 --> 00:04:15,184 I would say that it would be really interesting to hear. 62 00:04:16,151 --> 00:04:17,561 you would advise others to do. 63 00:04:17,561 --> 00:04:26,554 So whilst you're a fan yourself, if a team came to you and they have a team of maybe 20 rails developers, and there's a couple of them that 64 00:04:26,554 --> 00:04:33,991 have got some Postgres expertise but they maybe they don't have a huge application, but it's, it's meaningful maybe millions of transactions. 65 00:04:34,021 --> 00:04:37,091 I so it's, it's meaningful, but not GitLab scale, say. 66 00:04:37,991 --> 00:04:39,521 What would your advice be to them? 67 00:04:39,521 --> 00:04:42,266 Would like, I would be surprised if you would advise 68 00:04:42,316 --> 00:04:44,006 Nikolay: by the way, it almost doesn't use it. 69 00:04:44,396 --> 00:04:48,524 Michael: Well, yeah, I'm probably skipping ahead mentioning them, so that's good. 70 00:04:48,974 --> 00:04:52,844 But what would your recommendation be to others versus like what your opinion is for yourself? 71 00:04:53,469 --> 00:04:58,092 Nikolay: Well, my recommendation is like I have very interesting experience. 72 00:04:58,122 --> 00:05:06,552 I wrote a lot of code in LPs, in some code or in pe, Python, some code in other languages in sometimes a little bit c, 73 00:05:06,972 --> 00:05:10,002 but first of all, we should say POS is very extendable. 74 00:05:10,677 --> 00:05:14,157 This is one of the key places where you can extend progress. 75 00:05:14,187 --> 00:05:19,338 You can write a lot of code, and for example, you can define some new aggregate function. 76 00:05:19,338 --> 00:05:27,840 You just need to write some function with, according to some rules, and then say, Okay, my operator, my, my aggregate function will be supported by this. 77 00:05:27,840 --> 00:05:28,620 And so you. 78 00:05:29,490 --> 00:05:32,730 Select your aggregate function, then group by and so on. 79 00:05:32,730 --> 00:05:34,470 This is very, very good thing. 80 00:05:34,680 --> 00:05:38,340 You can, you can define your operator support by some function. 81 00:05:38,340 --> 00:05:41,946 It's also like, there is good, good term server side pogs. 82 00:05:42,086 --> 00:05:47,570 Code coding or programming, server site programming in terms of positive site programming, right? 83 00:05:47,870 --> 00:05:52,440 It's not only procedures, functions, also triggers, also operators. 84 00:05:52,440 --> 00:05:58,010 You also can extend, define your index type data type. 85 00:05:58,250 --> 00:05:59,330 Many, many, many things. 86 00:05:59,671 --> 00:06:11,968 But first thing when we start, very carefully think about opportunity to use this approach is our work with data, how strict we should be in terms of data. 87 00:06:11,968 --> 00:06:13,660 How we want clean data. 88 00:06:13,930 --> 00:06:17,267 It should be, how this word, I always forget this word. 89 00:06:17,310 --> 00:06:17,980 Correct. 90 00:06:18,008 --> 00:06:18,693 Relationships. 91 00:06:19,359 --> 00:06:20,619 com data is, 92 00:06:20,619 --> 00:06:21,819 Michael: Oh, consistent. 93 00:06:21,969 --> 00:06:22,899 Nikolay: con consistent. 94 00:06:22,899 --> 00:06:23,409 Exactly. 95 00:06:23,589 --> 00:06:24,939 So data should be consistent. 96 00:06:25,006 --> 00:06:28,006 It should, We should not have dirty data in most cases. 97 00:06:28,006 --> 00:06:30,076 Sometimes it's okay, but in many cases, yes. 98 00:06:30,081 --> 00:06:32,354 Like for example, some user data and so on. 99 00:06:32,804 --> 00:06:35,381 And in this case we define some structure. 100 00:06:35,411 --> 00:06:37,362 We define some constraints. 101 00:06:37,705 --> 00:06:41,708 And there is no other way to follow constraint. 102 00:06:41,708 --> 00:06:50,253 In a hundred percent of cases except having them inside database because If you implement your constraint logic inside your application code, 103 00:06:50,553 --> 00:06:58,203 there's no guarantee that tomorrow you will write, you decide to write, or your management decide to write another application or to work directly. 104 00:06:58,203 --> 00:07:02,913 I mean, user working directly with data and breaking your constraints because nobody checked that. 105 00:07:03,153 --> 00:07:06,273 So the only way to have good constraints is to have triggers. 106 00:07:06,363 --> 00:07:09,281 Well, something we can define using schema. 107 00:07:09,340 --> 00:07:12,577 Data type or length of text field or something. 108 00:07:12,877 --> 00:07:16,717 This is, or then you can define check, check constraints. 109 00:07:16,717 --> 00:07:19,747 But some complex logic can be defined on the using triggers. 110 00:07:19,747 --> 00:07:22,267 And this is already server site programming, right? 111 00:07:22,597 --> 00:07:23,527 Post site programming. 112 00:07:23,707 --> 00:07:28,490 And it's inevitable if you want a hundred percent guarantee like warranty. 113 00:07:28,518 --> 00:07:34,391 You need someone can guarantee that data is clean, it should be done on pogs, in, on database side, 114 00:07:34,901 --> 00:07:35,411 Michael: Okay. 115 00:07:35,471 --> 00:07:36,431 Yeah, I like that. 116 00:07:36,431 --> 00:07:40,361 And I've already realized that I think my opinion is a bit of both. 117 00:07:40,451 --> 00:07:43,950 Like it's I don't see myself as one way or the other. 118 00:07:44,110 --> 00:07:45,960 I'm a big fan of database triggers as well. 119 00:07:46,458 --> 00:07:53,447 But most of the teams that I see and work with would be slowed down by, you know development speed wise. 120 00:07:53,447 --> 00:07:56,693 Not, I'm not talking about database performance, but speed of development wise. 121 00:07:56,768 --> 00:07:57,218 Nikolay: Good. 122 00:07:57,638 --> 00:07:58,808 Different aspect. 123 00:07:58,846 --> 00:08:06,181 When I discovered Postgres, Which, which is now very popular, thanks to Super base, for example. 124 00:08:06,186 --> 00:08:06,451 Right. 125 00:08:07,051 --> 00:08:07,801 Definitely. 126 00:08:08,311 --> 00:08:11,761 I mean, maybe some people don't realize that they use it, but they use it. 127 00:08:11,941 --> 00:08:13,531 Definitely much more users. 128 00:08:13,561 --> 00:08:19,520 But at that time, when I worked with it quite long ago, already, maybe like five, six years ago, I, in a 129 00:08:19,520 --> 00:08:21,830 couple of projects, I replaced the whole team myself. 130 00:08:22,044 --> 00:08:26,694 Like Ruby developers, three of developers was writing some code for some startup. 131 00:08:26,752 --> 00:08:28,316 Management was not satisfied. 132 00:08:28,496 --> 00:08:33,378 I said, We don't need this a lot of ruby code to support some i p development. 133 00:08:33,378 --> 00:08:34,968 We just defined some views. 134 00:08:34,990 --> 00:08:36,387 In pogs and that's it. 135 00:08:36,687 --> 00:08:44,595 But some code was immediately implemented in, in a form of functions using pl ps sometimes regular sequel because 136 00:08:44,595 --> 00:08:47,085 you can write function using regular sequel people. 137 00:08:47,595 --> 00:08:53,939 People underestimate this, but this is convenient sometimes and views and some logic and everything like, and I 138 00:08:53,939 --> 00:08:57,629 felt like I'm spending like 10 x less time than they. 139 00:08:58,538 --> 00:08:59,858 it, it's not a joke. 140 00:08:59,858 --> 00:09:00,638 It's really a soul. 141 00:09:00,638 --> 00:09:01,508 Like, it's so easy. 142 00:09:02,018 --> 00:09:03,218 You just install, post dressed. 143 00:09:03,358 --> 00:09:07,719 You, you need to know how to cook it in terms of authentication, some, some things. 144 00:09:08,019 --> 00:09:12,764 But once you understood it, it's, it's like with any framework, you need to learn it, of course, right? 145 00:09:13,154 --> 00:09:15,644 But once you understood it, everything is inside pore. 146 00:09:15,914 --> 00:09:19,991 You define functions, you have API endpoints, and you need some advanced logic. 147 00:09:19,991 --> 00:09:22,234 You write PPG scale function, That's it. 148 00:09:23,074 --> 00:09:27,484 Michael: So do you, do you think you were as productive as then because of the technology or 149 00:09:27,705 --> 00:09:28,035 Nikolay: Yes. 150 00:09:28,035 --> 00:09:30,675 Technology, technology and the server side programming. 151 00:09:30,855 --> 00:09:38,056 25% of me of my time replaced small team of free engineers of Ruby developers replaced fully. 152 00:09:38,176 --> 00:09:40,556 And I had tests, I had pipelines, everything like. 153 00:09:41,611 --> 00:09:48,949 But unfortunately then actually my friends came and they are Python developers and they decided to replace it. 154 00:09:49,489 --> 00:09:52,579 I, I like, we had discussions like, Well, it's good. 155 00:09:52,639 --> 00:09:54,889 They said, Oh, okay, but we don't know Pale Petski. 156 00:09:54,894 --> 00:09:57,049 We need to learn, but we've Python, we will create. 157 00:09:57,229 --> 00:10:02,929 So they replaced it and it was, in terms of time that it was, it's less, less productive. 158 00:10:03,139 --> 00:10:08,089 I mean, again, stay back, but they, they, they bring brought much more on. 159 00:10:08,944 --> 00:10:10,544 Like machine learning and so on. 160 00:10:10,894 --> 00:10:11,644 Michael: do you mean? 161 00:10:11,674 --> 00:10:14,884 They replaced the se, the Postgres side. 162 00:10:15,004 --> 00:10:15,334 Oh, 163 00:10:15,574 --> 00:10:18,214 Nikolay: with Django, this is is interesting project. 164 00:10:18,214 --> 00:10:22,474 It's, they started for Ruby on Rails when I, sometime a couple of years. 165 00:10:22,563 --> 00:10:25,713 I supported them with PostgREST and some coding. 166 00:10:25,713 --> 00:10:34,292 Then they had a lot of needs in terms of Python and they decided to continue write writing Python in, writing api using Jungle and, and, and there. 167 00:10:34,682 --> 00:10:35,132 So, 168 00:10:35,177 --> 00:10:38,100 Michael: thought you might, I thought you might have meant pl python. 169 00:10:38,147 --> 00:10:38,567 But no, 170 00:10:38,852 --> 00:10:40,472 Nikolay: we discussed this opportunity. 171 00:10:40,472 --> 00:10:40,862 Exactly. 172 00:10:40,862 --> 00:10:42,598 They could do that, but I don't know. 173 00:10:42,598 --> 00:10:47,966 Like this is like decision, like what you learn, what you know and so on. 174 00:10:48,266 --> 00:10:54,321 Of course if you need to scale this is a little bit more difficult to scale because LP scale you need to learn. 175 00:10:54,321 --> 00:10:57,921 It's like other language, modify other language, very old and outdated. 176 00:10:58,461 --> 00:11:07,153 It has some Loops and eve and some not super convenient approach to work with exception sellers location. 177 00:11:07,158 --> 00:11:08,023 But it's possible. 178 00:11:08,023 --> 00:11:10,276 Like, it feels like Pascal a little bit, right? 179 00:11:10,750 --> 00:11:14,058 but main benefit is that you're sitting there, you don't have round trip. 180 00:11:14,733 --> 00:11:15,003 Michael: Yep. 181 00:11:15,138 --> 00:11:19,920 Nikolay: you can be very efficient, but you need to understand how data flow. 182 00:11:20,039 --> 00:11:27,242 With database is organized and you need to understand how to troubleshoot slow plans, how to use explain command. 183 00:11:27,422 --> 00:11:28,562 How to optimize queries. 184 00:11:28,712 --> 00:11:29,462 You need to understand that. 185 00:11:29,612 --> 00:11:35,222 And you also need good environment where you can try many things and feel it. 186 00:11:35,316 --> 00:11:37,792 I, I mean, you need to like production, like the environment. 187 00:11:37,792 --> 00:11:39,172 I, I had it already that. 188 00:11:39,427 --> 00:11:39,847 Lab. 189 00:11:39,847 --> 00:11:44,924 So like, no, at that time didn't have, But now I have it, and it's, it's easy, right? 190 00:11:44,924 --> 00:11:53,217 Like you have same size development environment, so you can quickly see performance of what you wrote, of your trigger, of your function. 191 00:11:53,517 --> 00:11:54,131 And it's good. 192 00:11:54,647 --> 00:11:56,387 Michael: This is another, so this is great. 193 00:11:56,387 --> 00:12:01,817 We, we've covered, you've, you've touched on performance there and I think that's, if, if you look at the normal pros and cons 194 00:12:01,817 --> 00:12:08,687 lists, that's one that people that are on the side of stored procedures, they often bring up as if you are trying to get comp. 195 00:12:08,747 --> 00:12:14,509 You know, if you're trying to avoid those round trip times, if you're trying to do some logic on your data that's in the database, doing it 196 00:12:14,939 --> 00:12:18,133 database side saves you a lot of effort or can save a lot of effort there. 197 00:12:18,133 --> 00:12:22,812 So that's normally cited, That's probably one of the most compelling reasons for doing it. 198 00:12:22,883 --> 00:12:33,191 Nikolay: Well, I would put on first place this logic that only database can guarantee that your guard code checking consistency, 199 00:12:33,401 --> 00:12:36,731 data, clearness and so on, it won't be bypassed by anyone. 200 00:12:36,761 --> 00:12:38,111 This is, for me, it's number one. 201 00:12:39,341 --> 00:12:39,941 Obvious. 202 00:12:39,971 --> 00:12:50,505 Well, you can, if you can put some proxy and guarantee that all people and applications will go through it and you code some logic there, it's okay. 203 00:12:50,505 --> 00:12:51,679 But usually it's not. 204 00:12:51,684 --> 00:12:53,629 So, So this is number one for me. 205 00:12:53,659 --> 00:12:54,469 Second, yes. 206 00:12:54,546 --> 00:12:56,406 Performance and round trip time. 207 00:12:56,406 --> 00:13:02,143 We, we all know this extreme example when some Ruby or Python developer pulls whole data. 208 00:13:02,863 --> 00:13:05,143 Application side and then processes it, right? 209 00:13:05,803 --> 00:13:09,266 Maybe in loop updating one by another. 210 00:13:09,596 --> 00:13:11,921 This pull it and then analyze it. 211 00:13:11,921 --> 00:13:13,421 This is very bad approach. 212 00:13:13,510 --> 00:13:24,162 Database can do it much more efficiently because it has indexes and you could all, Another example, our R code, which has some r, 213 00:13:24,162 --> 00:13:28,737 has some Analog for group buy for joints and so on in memory only. 214 00:13:29,037 --> 00:13:32,609 So they, the aggregation grouping joining. 215 00:13:32,849 --> 00:13:34,769 So they pull whole data in memory. 216 00:13:35,056 --> 00:13:40,642 At some point, this will be like memory will be saturated, but it's not efficient because they don't have indexes. 217 00:13:40,762 --> 00:13:44,062 POGS has already built, right, and algorithms and so on. 218 00:13:45,012 --> 00:13:47,892 Michael: databases are really, well Postgres in particular, it has a really good query planner. 219 00:13:47,892 --> 00:13:52,032 It has, you know, it's really designed to do that on large amounts of data. 220 00:13:52,058 --> 00:13:57,895 And doing it without having to send it backwards and forwards is always good, even if it wasn't strictly better at those things. 221 00:13:57,925 --> 00:14:01,488 Doing it without having to send that data across saves so much time. 222 00:14:01,488 --> 00:14:02,028 Normally 223 00:14:02,205 --> 00:14:05,565 Nikolay: You still can have a problem if you, for example, use some CORs or approach. 224 00:14:05,565 --> 00:14:12,327 Like if you, instead of updating all roles, you're using one statement, you might like, okay, I will write P code. 225 00:14:12,332 --> 00:14:16,142 I will do some for loop, and then in loop I will update each row. 226 00:14:16,322 --> 00:14:17,912 It also will be less efficient. 227 00:14:17,917 --> 00:14:23,222 So you can, you can do mistakes using this approach as well, but at least you won't have network. 228 00:14:23,822 --> 00:14:24,122 Right. 229 00:14:24,958 --> 00:14:31,391 I wanted to emphasize it's not only about round trip type, but also about capabilities you have if you pull 230 00:14:31,391 --> 00:14:35,201 data to memory, you can do anything with it, right? 231 00:14:35,206 --> 00:14:38,801 But you cannot uses, which left on, on server site, you don't have them. 232 00:14:39,565 --> 00:14:45,715 Michael: so we've covered like a couple of the, the things people often cite as really good reasons for put pushing more 233 00:14:45,720 --> 00:14:52,331 and more, if not all of the logic that side The downsides I of, or the, cy criticisms I often here are less good develop. 234 00:14:52,361 --> 00:14:52,781 Oh, go on. 235 00:14:52,841 --> 00:14:54,371 Nikolay: let's mention one, one more. 236 00:14:54,431 --> 00:15:00,348 SQL in general is very, very it's very, powerful for working with large volumes of data. 237 00:15:00,588 --> 00:15:02,028 So many things developed. 238 00:15:02,059 --> 00:15:07,369 you can write a few lines instead of hundreds of lines and using regular language. 239 00:15:07,819 --> 00:15:11,777 And of course, you want to ensure that it'll be efficiently executed. 240 00:15:11,837 --> 00:15:15,107 That's why you need like, some understanding, experience and good playground. 241 00:15:15,527 --> 00:15:18,011 But in general The code is brief, right? 242 00:15:18,092 --> 00:15:23,275 and in PE P Gs QL function inside it, you can use regular SQL combined with these pelp. 243 00:15:23,665 --> 00:15:27,848 Like, they are working together without any need. 244 00:15:28,688 --> 00:15:32,648 Unless you use dynamic sql, you can avoid using some. 245 00:15:33,203 --> 00:15:36,143 Like, like execute or query quotes and so on. 246 00:15:36,143 --> 00:15:37,103 You don't need them at all. 247 00:15:37,133 --> 00:15:46,504 So like they are integrated already and by the way, there's HDB with their hql project, which try to reinvent sql. 248 00:15:46,774 --> 00:15:56,364 It's another topic, but also maybe in future more convenient work will be done in using regular languages for JavaScript, for example. 249 00:15:56,733 --> 00:15:59,583 so I want just to say that quote much less. 250 00:16:00,333 --> 00:16:02,763 So usually somehow much less code. 251 00:16:03,123 --> 00:16:04,593 You don't need to write loops. 252 00:16:04,593 --> 00:16:07,855 And so like open database connection, close database connection. 253 00:16:07,855 --> 00:16:08,525 No, no, no. 254 00:16:08,525 --> 00:16:09,645 Everything is automatic. 255 00:16:10,855 --> 00:16:11,185 Michael: So yeah. 256 00:16:11,185 --> 00:16:13,405 Should we cover some of the downsides? 257 00:16:13,895 --> 00:16:15,025 Nikolay: A lot of downsides. 258 00:16:15,030 --> 00:16:19,735 As I've said, I consider myself as not normal because normally people said we won't go this path. 259 00:16:19,765 --> 00:16:28,574 First of all, they mention, usually they mention that it's not convenient cause there is no good developer tooling, IgE or 260 00:16:28,574 --> 00:16:32,094 debugger we usually mentioned there, there is debugger actually. 261 00:16:32,909 --> 00:16:34,049 Michael: But people aren't familiar. 262 00:16:34,049 --> 00:16:38,915 People are familiar with their existing, you know with backend framework, debugging tools. 263 00:16:38,915 --> 00:16:42,995 So it's more familiarity and expertise, I think, when it comes to using those 264 00:16:43,385 --> 00:16:43,775 Nikolay: right. 265 00:16:43,805 --> 00:16:45,885 I personally, I live in tmux and. 266 00:16:46,835 --> 00:16:48,125 with, I'm not normal already. 267 00:16:48,125 --> 00:16:48,515 Right. 268 00:16:49,085 --> 00:16:51,598 And I, I prefer avoiding graphical ui. 269 00:16:52,228 --> 00:16:58,723 I use it sometimes, but sometimes, But I pref, I, I like when my work is on some remote remote machine. 270 00:16:58,723 --> 00:17:02,113 So I, I'm not dependent on my laptop or anything. 271 00:17:02,113 --> 00:17:07,298 I can continue working in Tmox from anywhere and also working in pairs that have needed and so on. 272 00:17:08,078 --> 00:17:11,111 But if we talk about idea Integr. 273 00:17:11,509 --> 00:17:17,556 . Actually, maybe I'm, I don't know, maybe there are some good examples for pl PPG scale, which I just don't know. 274 00:17:17,571 --> 00:17:19,844 But we switch to PL p scale discussion only. 275 00:17:19,849 --> 00:17:26,954 But again, you can write using sql, using pl, python, pl, other, other languages Postgres has. 276 00:17:27,464 --> 00:17:30,176 But question is how you like debugger. 277 00:17:30,206 --> 00:17:30,566 Debugger? 278 00:17:30,596 --> 00:17:30,866 Okay. 279 00:17:31,226 --> 00:17:33,913 In my personal opinion you should have a lot of code. 280 00:17:33,957 --> 00:17:40,076 Complex stored in multiple files to have an need in having debugger, there is debugger. 281 00:17:40,196 --> 00:17:41,276 For page p, just go. 282 00:17:41,276 --> 00:17:42,386 I, I never use it. 283 00:17:42,391 --> 00:17:46,586 I don't need it usually function, even if it's hundred lines of code. 284 00:17:47,066 --> 00:17:51,476 Print lining is fine for me in my also raise, debug raise bag is good. 285 00:17:51,921 --> 00:17:52,791 Michael: What about test? 286 00:17:52,791 --> 00:17:53,811 You mentioned testing. 287 00:17:53,811 --> 00:17:57,291 You mentioned when you had that project you were doing, you had tests for it. 288 00:17:57,686 --> 00:17:57,986 Nikolay: lab. 289 00:17:58,091 --> 00:18:02,202 Michael: What did you, what do you use for testing the procedures, making sure they 290 00:18:02,292 --> 00:18:06,522 Nikolay: Well, first of all, you, you should have data, enough data to test properly. 291 00:18:06,527 --> 00:18:10,902 So my approach is to have production size database, same number of roles or, or close to it. 292 00:18:11,472 --> 00:18:15,848 So you just spin off your think loan using database lab and do anything there. 293 00:18:16,445 --> 00:18:18,415 Michael: Do you use like pgTAP or anything? 294 00:18:18,420 --> 00:18:18,625 Like 295 00:18:18,835 --> 00:18:23,364 Nikolay: Oh, yeah, I usually I prefer using Sqitch instead of flyway liquibase or so. 296 00:18:23,668 --> 00:18:26,798 P tap and ski sheets from David Wheeler, if I'm not mistaken. 297 00:18:26,798 --> 00:18:28,088 Same, same author, right? 298 00:18:28,088 --> 00:18:31,688 So Ski has verify, it, has deploy, revert and verify. 299 00:18:31,688 --> 00:18:34,658 You need to define three functions for each database migration you have. 300 00:18:35,108 --> 00:18:39,883 By the way, this is good point, how you manage your function definitions. 301 00:18:40,115 --> 00:18:41,015 Michael: Source control, right. 302 00:18:41,208 --> 00:18:41,598 Nikolay: yes. 303 00:18:41,598 --> 00:18:42,528 Version control. 304 00:18:42,708 --> 00:18:44,028 Let's discuss it in, in a bit. 305 00:18:44,418 --> 00:18:48,162 So I usually try to, or have some logic in verify. 306 00:18:48,696 --> 00:18:50,616 PG scale has a short function. 307 00:18:50,806 --> 00:18:51,676 It's very convenient. 308 00:18:51,676 --> 00:18:57,101 So you can write another, and you, you can also have anonymous do block in plp. 309 00:18:57,401 --> 00:18:59,891 It's also in PLPs scale, so we do. 310 00:19:00,088 --> 00:19:09,577 And then some string, Usually people write dollar, dollar and then some pill logic there and there you can have a sort, so you, you write anonymous, 311 00:19:09,637 --> 00:19:14,287 do blog inside, verify functional for Sqitch just to test something. 312 00:19:14,287 --> 00:19:17,557 For example, you have a function which did something. 313 00:19:17,587 --> 00:19:18,787 Okay, you executed it. 314 00:19:18,787 --> 00:19:27,817 Then you ensure that the result is as and if you don't have a sort, for example, if in SQL context, you need to verify it, okay? 315 00:19:27,817 --> 00:19:36,865 You can use divide by zero or you can write he helper function, which will raise exception warning, notice or debug anything you want. 316 00:19:36,895 --> 00:19:41,014 So you can have heer functions and call them using regular sequel. 317 00:19:41,674 --> 00:19:43,281 And you you can have some case. 318 00:19:44,226 --> 00:19:52,163 If something like you check something using regular SQL and write your helper function to report some error, for example, And 319 00:19:52,163 --> 00:19:56,267 it can be soft, like warning or hard, like hard stop, anything. 320 00:19:56,377 --> 00:19:59,506 So it's possible you can have tests, definitely. 321 00:20:00,030 --> 00:20:03,640 But the problem is that debugger exists, I don't use it. 322 00:20:03,640 --> 00:20:05,172 I, just don't feel it's needed. 323 00:20:05,428 --> 00:20:06,028 Never. 324 00:20:06,243 --> 00:20:07,918 I use, usually I use a lot of. 325 00:20:08,635 --> 00:20:11,395 And I sort of just like, you know, this is not possible. 326 00:20:11,395 --> 00:20:12,475 Let's have a shot here. 327 00:20:12,715 --> 00:20:17,041 And I use usually a lot of raise commands on various levels. 328 00:20:17,371 --> 00:20:21,209 And you can, if you check my code, you can find raised bug in many places. 329 00:20:21,452 --> 00:20:27,847 So anytime when you debugging something, you can change client mean log, log mean, client. 330 00:20:28,282 --> 00:20:30,682 Level, I, I always forgot you. 331 00:20:30,682 --> 00:20:32,487 You can, you can change in two places. 332 00:20:32,817 --> 00:20:40,397 Log level in client connection and in log so you can have messages printed to your output or in logs. 333 00:20:40,441 --> 00:20:47,660 It's quite convenient and if, if you have raised dac, it won't be present anywhere by default, unless you say, Okay, I'm in the bugging mode. 334 00:20:47,660 --> 00:20:48,380 I change my. 335 00:20:49,460 --> 00:20:51,950 In either or both places. 336 00:20:52,280 --> 00:20:53,210 There are two places, right? 337 00:20:53,630 --> 00:20:56,120 So, and you have those debugging messages immediately. 338 00:20:56,360 --> 00:20:57,050 Quite convenient. 339 00:20:57,800 --> 00:20:58,280 Michael: Nice. 340 00:20:58,400 --> 00:21:06,698 But then on the flip side, I think people that are using Rails or Janga or something, they have migrations built in already 341 00:21:06,818 --> 00:21:13,088 and they don't have to worry about a set, separate tooling for Version control, source control, that kind of thing. 342 00:21:13,358 --> 00:21:13,628 Nikolay: Right. 343 00:21:13,628 --> 00:21:17,060 But they also have uh, first of all, they can have functions. 344 00:21:17,070 --> 00:21:17,880 They can have triggers. 345 00:21:17,880 --> 00:21:18,840 They do it, they do it. 346 00:21:18,870 --> 00:21:23,016 And they also have quite advanced testing capabilities. 347 00:21:23,406 --> 00:21:28,376 So I think it's, everything is doable if you want to move some logic to database. 348 00:21:28,376 --> 00:21:29,096 So it's doable. 349 00:21:29,706 --> 00:21:31,536 Michael: And this is, I think this is where I've landed. 350 00:21:31,536 --> 00:21:33,516 I think it's, you can have a bit of both. 351 00:21:33,516 --> 00:21:37,170 You don't, it doesn't have to be, you know, a Holy War one or the other. 352 00:21:37,282 --> 00:21:38,626 I definitely see that more now. 353 00:21:38,677 --> 00:21:39,667 Nikolay: yeah, You can have a mix. 354 00:21:40,474 --> 00:21:47,414 but usually people, you have some discussions like should we have business logic inside database or an implication? 355 00:21:47,631 --> 00:21:50,481 this is question we get like a black, black and white question. 356 00:21:50,511 --> 00:21:50,721 Right? 357 00:21:50,721 --> 00:21:52,941 So you need to choose one of one side 358 00:21:53,526 --> 00:21:54,136 Michael: Do you? 359 00:21:54,141 --> 00:21:54,396 Yeah. 360 00:21:54,606 --> 00:22:00,791 Did you see there was a post, I looked it up and it was back in 2015 by Derek Silvers around his approach. 361 00:22:00,791 --> 00:22:06,998 He's a, he works by himself, he's got his own database, and he, it's, it's a personal project, but he also sells his books. 362 00:22:06,998 --> 00:22:10,088 He's very popular author and he runs everything him. 363 00:22:10,823 --> 00:22:16,733 He, he wrote a post very, very strongly in favor of putting everything database aside. 364 00:22:17,213 --> 00:22:23,339 And he's, moved his application, he's migrated between different application frameworks and I think actually that's a, that's a final point that 365 00:22:23,339 --> 00:22:27,269 often gets brought up in the argument, even if it's not super relevant. 366 00:22:27,449 --> 00:22:33,119 Well, I dunno, I'd be interested in your opinion on how relevant it is, but ease of migration of application and 367 00:22:33,119 --> 00:22:36,359 ease of migration of database often come up as arguments. 368 00:22:36,944 --> 00:22:45,734 People seem to argue against putting logic in the database because they, they seem to think a migration of the database is more likely than a 369 00:22:45,739 --> 00:22:49,574 migration of the application, but I don't know if that's necessarily true. 370 00:22:49,994 --> 00:22:51,974 Nikolay: a few, a few things here to discuss. 371 00:22:52,016 --> 00:22:58,315 First if you, first you discover these capabilities, Postgres has, you are very, like, happy. 372 00:22:58,765 --> 00:23:05,085 But then for example, already, you have built quite complex sophisticated structure of views. 373 00:23:05,385 --> 00:23:07,815 For example, like here, hierarchy of views, right? 374 00:23:08,235 --> 00:23:09,465 And then you need to change some. 375 00:23:10,523 --> 00:23:11,723 you need to redefine them. 376 00:23:12,083 --> 00:23:16,433 It's good that they, they can be, a definition can be done and inside one transaction. 377 00:23:16,433 --> 00:23:20,513 So like small interruption, like a fraction of second, and that's it. 378 00:23:20,753 --> 00:23:24,049 Everything is transactional good, but it's not convenient at all. 379 00:23:24,049 --> 00:23:25,519 So even if you. 380 00:23:25,867 --> 00:23:32,407 for example, you have select star from some table, some view for, for example, for a postgre and you need to redefine it. 381 00:23:32,437 --> 00:23:39,445 A new column created you, you really need to recreate on each so underlying table change, you need to recreate view. 382 00:23:39,925 --> 00:23:42,219 It's like there is some maintenance cost here. 383 00:23:42,280 --> 00:23:45,595 In code, it could be different and it could survive it. 384 00:23:45,745 --> 00:23:47,875 Like you don't need to change codes. 385 00:23:47,935 --> 00:23:53,335 Well, if you edit some column, maybe you need to change code, but you can write it in a way that maybe you will. 386 00:23:53,526 --> 00:23:54,666 Not always need them. 387 00:23:54,996 --> 00:24:01,236 And also, so, so Iraq of use can be tricky and a little bit painful in, in, in maintenance. 388 00:24:01,446 --> 00:24:02,196 This is first thing. 389 00:24:02,196 --> 00:24:03,576 Second thing is more important. 390 00:24:04,056 --> 00:24:09,835 Where do you store your functions and how do you approach versioning? 391 00:24:10,195 --> 00:24:13,515 For example, sometimes we need two versions, both of. 392 00:24:14,065 --> 00:24:17,755 It can, it can be possible if you have a lot of application notes. 393 00:24:17,785 --> 00:24:19,615 Some of them already upgraded, some not. 394 00:24:19,825 --> 00:24:24,190 So we need to have we need to allow them working with both versions. 395 00:24:24,190 --> 00:24:24,770 All on you. 396 00:24:24,805 --> 00:24:26,515 People do this, some people do this. 397 00:24:26,545 --> 00:24:27,745 They just use ches. 398 00:24:28,059 --> 00:24:28,359 And they 399 00:24:28,359 --> 00:24:31,100 have bunch of soft ches right. 400 00:24:31,105 --> 00:24:34,028 And you, you can clean up old versions later. 401 00:24:34,394 --> 00:24:35,525 It's quite convenient. 402 00:24:35,544 --> 00:24:41,823 But sometimes people say like, Oracle also has packages, POS doesn't have packages. 403 00:24:41,883 --> 00:24:45,783 It's like, I already forgot everything about Oracle, so I cannot comment on this. 404 00:24:46,173 --> 00:24:48,303 But it feels like we are missing something. 405 00:24:48,303 --> 00:24:49,743 Definitely not my topic though. 406 00:24:49,750 --> 00:24:54,843 But I would, I wanted to say, Think about one function and we change it. 407 00:24:54,885 --> 00:25:02,240 Uh, There is create replace, but it's limited because if you, if signature of function changed, for example, new parameter, period. 408 00:25:02,960 --> 00:25:09,291 Also it can be overloading involved because you can have same function name, but different functions, Multiple 409 00:25:09,296 --> 00:25:11,431 ones with different number of parameters, right? 410 00:25:11,971 --> 00:25:17,129 But you cannot create or replace functionally, for example some parameter change to its type. 411 00:25:17,129 --> 00:25:21,370 You need to drop it and, and define ones again, and also dependencies. 412 00:25:21,370 --> 00:25:22,180 It's like quite. 413 00:25:22,730 --> 00:25:30,200 But my big question here, the biggest question here is that consider, okay, signature hasn't changed, but body of function changed. 414 00:25:30,590 --> 00:25:33,110 How do you approach versioning here. 415 00:25:33,286 --> 00:25:38,926 Question is if you have old function version, new function version, where do you store versions? 416 00:25:39,202 --> 00:25:40,912 Post this is not good voice. 417 00:25:40,942 --> 00:25:41,152 Okay. 418 00:25:41,152 --> 00:25:45,836 If you have uh, if, if you use name spaces, a very strict approach, right? 419 00:25:45,841 --> 00:25:45,999 You. 420 00:25:46,022 --> 00:25:48,507 Use like version one, version two, and, and so on. 421 00:25:48,507 --> 00:25:54,117 And so, but inside each version you also might have multiple sub versions like you, you developing, right? 422 00:25:54,267 --> 00:26:03,752 And you want to fix, like you, you want normal version control With branching uh, meets like probably you put whole function budget to get right. 423 00:26:04,393 --> 00:26:05,443 Michael: So, yeah. 424 00:26:05,533 --> 00:26:06,844 What, How else would you do it? 425 00:26:06,861 --> 00:26:07,431 Nikolay: I do it. 426 00:26:08,376 --> 00:26:12,366 And it's good that I, if I change one line, I see D with only one line changed. 427 00:26:12,546 --> 00:26:14,526 But what about our skew? 428 00:26:14,676 --> 00:26:21,216 Liquid based flyaway or rub, active record migrations, database migrations, or jungle migrations and so on. 429 00:26:22,086 --> 00:26:23,136 They're not friendly with it. 430 00:26:23,526 --> 00:26:30,047 If you, if I include that function like, like, okay, I, I keep each function in, in separate file, for example. 431 00:26:30,047 --> 00:26:30,557 Good idea. 432 00:26:30,557 --> 00:26:36,726 Because we can have normal GI flow and like pull request, merge request, approval process and so on review. 433 00:26:37,326 --> 00:26:43,636 But if we just included for example, if we have migrations, we include it into each migration. 434 00:26:43,936 --> 00:26:46,246 And imagine if we want to replace several. 435 00:26:47,476 --> 00:26:49,576 Which version of function will, will be taken. 436 00:26:49,576 --> 00:26:52,066 It'll the latest and it's not right. 437 00:26:52,066 --> 00:26:58,006 We need to include like, not just include, we need to include in a correlated way. 438 00:26:59,176 --> 00:26:59,476 Michael: Yeah. 439 00:26:59,536 --> 00:27:06,136 You know, I used to work like, I feel like we should probably do a whole topic on database version control, but you know, I used to work on this. 440 00:27:06,406 --> 00:27:12,736 Nikolay: I have, I have something to say about it, but may I have, I have a talk next week and for you about database branching. 441 00:27:12,826 --> 00:27:13,996 It's exactly about this. 442 00:27:14,986 --> 00:27:15,196 Michael: Yeah. 443 00:27:15,196 --> 00:27:16,786 Let's, let's definitely discuss that. 444 00:27:16,786 --> 00:27:20,746 But in, if you do things application side, this is, this is easier. 445 00:27:20,926 --> 00:27:22,516 It's, this is all handled already. 446 00:27:22,794 --> 00:27:29,009 Nikolay: Yeah, you don't have this problem normally, so, but with database, you need to do something about it. 447 00:27:29,059 --> 00:27:33,242 For example, in many cases I see people abandon the idea to have this very. 448 00:27:33,812 --> 00:27:40,302 Big chain sequence of database migrations and ability to replay them because you cannot include files, right? 449 00:27:40,362 --> 00:27:42,792 If you include files, you need to include all version of files. 450 00:27:42,792 --> 00:27:48,816 So I, I had some code like related conclusion for rails maybe, or for Sketch, I don't remember. 451 00:27:49,146 --> 00:27:51,666 But it's like you need to develop additionally. 452 00:27:51,921 --> 00:27:54,921 . So there is a gap here, and this is not convenient. 453 00:27:55,221 --> 00:27:57,591 This doesn't kill, well, not convenient. 454 00:27:57,591 --> 00:28:01,426 So people, if you, if you work in teams you have issues with that. 455 00:28:01,846 --> 00:28:08,163 But finally like we, we let to finalize with cons of pog site programming. 456 00:28:08,673 --> 00:28:16,236 The biggest negative side is that at some point doesn't scale because this code, if you so imagine you. 457 00:28:17,031 --> 00:28:23,391 Clusters and each cluster ha has a primary note with read, write, read, write capabilities. 458 00:28:23,451 --> 00:28:24,561 Other nodes are read only. 459 00:28:25,311 --> 00:28:29,451 Many of your functions will be read, write, so you need to write something. 460 00:28:30,201 --> 00:28:34,521 And a number of course of on the primary node is very limited. 461 00:28:34,701 --> 00:28:36,690 It's the most most to say. 462 00:28:37,587 --> 00:28:37,917 Yeah. 463 00:28:37,917 --> 00:28:38,097 Yeah. 464 00:28:38,097 --> 00:28:39,447 So, so it's most, 465 00:28:39,507 --> 00:28:40,617 Michael: It's like a but neck, right? 466 00:28:40,724 --> 00:28:40,964 Nikolay: Right. 467 00:28:40,964 --> 00:28:43,147 It's, it's very important resource. 468 00:28:43,207 --> 00:28:46,507 You, you have very limited resource only, like, for example, okay. 469 00:28:46,507 --> 00:28:47,857 96 course, but that's it. 470 00:28:48,307 --> 00:28:56,042 And to scale you need either to split your database in parts or to apply some charting or just to scale vertically better, cpu. 471 00:28:56,080 --> 00:28:57,460 with more course but. 472 00:28:57,491 --> 00:28:58,276 People. 473 00:28:58,282 --> 00:29:04,102 if they don't realize it in the very beginning, if putting a lot of logic inside database leads to some situation on 474 00:29:04,102 --> 00:29:06,982 CPU or on disc, this calls a very, very important resource. 475 00:29:06,982 --> 00:29:12,112 Here we have only one limit for disc io on primary note. 476 00:29:12,442 --> 00:29:14,822 We cannot do rights on standby notes, right? 477 00:29:15,277 --> 00:29:22,687 We need to do rights anyway, even if we have logical application code, but CPU spent for some calculations and so on. 478 00:29:23,047 --> 00:29:28,207 And especially not only to for calculations, but especially for communication with external world. 479 00:29:28,957 --> 00:29:35,231 The moment when you see that you need to analyze some external webpage from some crowling scraper or, or something. 480 00:29:35,261 --> 00:29:40,660 Or you even need to download some binary like file, for example image. 481 00:29:40,948 --> 00:29:43,738 And you, you are doing logic inside database. 482 00:29:43,868 --> 00:29:45,898 this is the, like many bells. 483 00:29:45,898 --> 00:29:47,548 Should, should, should uh, 484 00:29:47,548 --> 00:29:48,079 Michael: Go off. 485 00:29:48,109 --> 00:29:49,159 Alarms should ring. 486 00:29:49,159 --> 00:29:49,429 Yeah. 487 00:29:49,609 --> 00:29:49,999 Nikolay: Yeah. 488 00:29:49,999 --> 00:29:50,032 Yeah. 489 00:29:50,096 --> 00:29:50,576 It should drink. 490 00:29:50,666 --> 00:29:51,176 Yes. 491 00:29:51,236 --> 00:29:54,338 Michael: yeah, It's a great point about scaling up versus scaling out. 492 00:29:54,343 --> 00:29:57,428 It's easier to horizontally scale that on the application side. 493 00:29:57,768 --> 00:30:01,758 while we're on the last topics, I wondered if you wanted to bring up SubT transaction, like the idea 494 00:30:01,833 --> 00:30:02,343 Nikolay: No, no, no, no. 495 00:30:02,343 --> 00:30:07,979 Let, let finish this, let finish, let me finish because I was in this, like, I, I placed as I said, teams of 496 00:30:08,369 --> 00:30:11,489 whole, whole small teams of Ruby developers, for example. 497 00:30:11,879 --> 00:30:13,645 And I did it twice at least. 498 00:30:13,650 --> 00:30:17,124 I used the this approach in many places of my own projects. 499 00:30:17,124 --> 00:30:19,235 We've past grasp several projects. 500 00:30:19,805 --> 00:30:20,435 I loved it. 501 00:30:20,435 --> 00:30:23,425 I like advertised it also a lot. 502 00:30:24,113 --> 00:30:33,066 the moment I started to write some cool logic in PL python or also I discovered pls H from Peter 503 00:30:33,066 --> 00:30:33,306 Israel. 504 00:30:33,996 --> 00:30:34,146 It 505 00:30:34,146 --> 00:30:35,676 should be a very experimental thing. 506 00:30:35,736 --> 00:30:36,966 Don't do it on production. 507 00:30:37,236 --> 00:30:39,430 So you can write shell code, bash code. 508 00:30:39,650 --> 00:30:39,730 Actually. 509 00:30:40,595 --> 00:30:41,555 And have it in pos. 510 00:30:42,785 --> 00:30:51,790 So I, I did, I had some logic with COOL as well and bar had said Povi to just to download and put some images somewhere. 511 00:30:52,390 --> 00:30:55,450 I also had some parcels of external webpages. 512 00:30:55,870 --> 00:30:58,920 You know, the problem of I don't transaction, right. 513 00:30:59,110 --> 00:31:05,371 When an application started to work with database open transaction, then go, went to work with some external api. 514 00:31:05,770 --> 00:31:12,640 and for database it means some delay and we sit in transaction, not not doing anything inside database if 515 00:31:12,640 --> 00:31:15,810 you start working for, with external things from pogs. 516 00:31:16,125 --> 00:31:18,110 this comes to you very quickly. 517 00:31:18,410 --> 00:31:21,070 For example, I, I also had uh, work with Stripe. 518 00:31:21,130 --> 00:31:21,910 Quite convenient. 519 00:31:22,060 --> 00:31:28,000 Charge somebody right inside . Stripe has very great, like one of the best APIs in documentation. 520 00:31:28,360 --> 00:31:36,252 I also sent some Ps to Slack when, for example, user registrars immediately sends p to Slack using trigger synchronously. 521 00:31:36,562 --> 00:31:37,552 So it's not good. 522 00:31:37,732 --> 00:31:42,283 It's not good because you, quickly understand that your CPU on your primary. 523 00:31:42,836 --> 00:31:47,312 Does a lot of things it shouldn't do and I see as I mentioned, super base, right? 524 00:31:47,603 --> 00:31:51,053 I mentioned super base and I see they have already some science. 525 00:31:51,053 --> 00:31:52,314 They go this path. 526 00:31:52,704 --> 00:31:54,024 they are growing like rocket. 527 00:31:54,234 --> 00:32:00,946 A lot of users, I consider them as the best team who can productize pogs and POGS extensions. 528 00:32:01,666 --> 00:32:06,016 , but I see PL s sql, http, for example, extension, they 529 00:32:06,376 --> 00:32:06,586 Michael: Yeah. 530 00:32:06,586 --> 00:32:08,716 They've enable, they, they let you enable it right? 531 00:32:08,866 --> 00:32:09,216 in, 532 00:32:09,346 --> 00:32:09,676 Nikolay: Right? 533 00:32:09,736 --> 00:32:09,976 Right. 534 00:32:09,981 --> 00:32:15,610 So some signs that this like wool is, this is there for some products. 535 00:32:16,135 --> 00:32:18,325 Don't do this inside primary. 536 00:32:18,325 --> 00:32:22,255 If you want growth, do it only as experimental, as prototyping. 537 00:32:22,255 --> 00:32:23,095 As like, 538 00:32:23,365 --> 00:32:25,165 quick, quick quick prototyping. 539 00:32:25,165 --> 00:32:25,645 That's it. 540 00:32:25,965 --> 00:32:26,655 Don't do this. 541 00:32:26,709 --> 00:32:28,982 I did it, I understood it, I did it. 542 00:32:29,012 --> 00:32:31,682 But just exactly for the speed of development, it's great. 543 00:32:31,982 --> 00:32:35,496 Super fast, but also secret management. 544 00:32:35,496 --> 00:32:38,106 There you need, like, for example, if you need to work with. 545 00:32:38,626 --> 00:32:43,333 where do you keep your secrets as uh, do you see variables? 546 00:32:43,423 --> 00:32:46,093 I mean, post variables probably not safe, right? 547 00:32:46,813 --> 00:32:50,023 Anybody can select current setting or show. 548 00:32:50,113 --> 00:32:50,593 That's it. 549 00:32:51,013 --> 00:32:51,403 No, no. 550 00:32:51,403 --> 00:32:51,853 Not good. 551 00:32:51,853 --> 00:32:52,183 Also. 552 00:32:52,458 --> 00:32:57,407 I, I remember I kept secrets in a table and with permissions, nobody can. 553 00:32:58,007 --> 00:32:58,247 Yeah, 554 00:32:58,577 --> 00:32:59,957 there is some, some approach. 555 00:33:00,707 --> 00:33:02,447 Michael: even super base are big fans. 556 00:33:02,477 --> 00:33:07,039 They, big fans of row level security as well as obviously permission based. 557 00:33:07,109 --> 00:33:07,469 Nikolay: Right. 558 00:33:07,769 --> 00:33:09,269 Again, I'm, I'm not normal. 559 00:33:09,269 --> 00:33:14,525 I'm big fan of this approach, but downsides are obvious and everyone should understood them before going this path. 560 00:33:14,888 --> 00:33:16,988 Michael: Exactly, and it it age odd. 561 00:33:17,058 --> 00:33:18,108 Answer it depends, 562 00:33:18,108 --> 00:33:18,378 right? 563 00:33:18,498 --> 00:33:18,748 Yeah. 564 00:33:18,757 --> 00:33:21,337 So probably should have got that in up front. 565 00:33:21,404 --> 00:33:26,596 Last couple of things I wanted to mention that I forgot to, while you were talking about them on the query plan front. 566 00:33:27,016 --> 00:33:29,086 It can be really helpful this uh, using. 567 00:33:29,902 --> 00:33:36,958 Functions, nested functions uh, can be really important to enable, well, if you're using it or to explain as a log 568 00:33:36,958 --> 00:33:43,580 nested statements parameter, and putting that to true can be really helpful for debugging or for performance debugging. 569 00:33:43,584 --> 00:33:47,387 And then on the SQL server side, The tools are a bit further ahead on this. 570 00:33:47,387 --> 00:33:55,660 So there was a, a project called SQL Server Data Tools and Redgate, the company I worked at for many years had lots of tools around letting version 571 00:33:55,660 --> 00:34:04,510 control and test database side, which I don't, I haven't seen equivalence of in terms of the whole, whole framework or the, the tooling on the Postgres. 572 00:34:04,695 --> 00:34:06,675 Nikolay: The biggest concern for, for their tools? 573 00:34:06,675 --> 00:34:07,845 I think Red GI has it. 574 00:34:07,875 --> 00:34:10,065 I mean, I mean, regular version control tools. 575 00:34:10,065 --> 00:34:11,385 They all rely on gi. 576 00:34:11,855 --> 00:34:14,445 GI is not friendly with big data volumes at all. 577 00:34:14,445 --> 00:34:16,515 They just say, don't do this. 578 00:34:16,515 --> 00:34:20,103 And so we, we only talk about schema and DML comments. 579 00:34:20,103 --> 00:34:20,403 That's it. 580 00:34:20,433 --> 00:34:23,917 We don't, we cannot have data version, but we need also. 581 00:34:24,817 --> 00:34:32,557 Michael: Well, so they do have some, So both of those two do have some when data is schema, so like look up tables and things. 582 00:34:32,557 --> 00:34:34,477 You can version control that. 583 00:34:34,477 --> 00:34:38,737 But yeah, of, of course, in terms of C I C D or testing performance. 584 00:34:38,737 --> 00:34:39,037 You do? 585 00:34:39,067 --> 00:34:39,307 Yeah. 586 00:34:39,337 --> 00:34:40,237 If that, if that's what you're 587 00:34:40,342 --> 00:34:43,192 Nikolay: Or regular testing where you, you need data for testing. 588 00:34:43,552 --> 00:34:48,382 If you have all like few rows in the table, you cannot test properly. 589 00:34:48,442 --> 00:34:50,842 Search doesn't work, that doesn't return anything and so on. 590 00:34:51,472 --> 00:34:52,642 So it's also a problem. 591 00:34:52,752 --> 00:34:53,242 Yeah. 592 00:34:53,707 --> 00:34:55,837 But it's different problem, different topic at all, like 593 00:34:56,682 --> 00:34:57,532 Michael: definitely. 594 00:34:57,532 --> 00:34:58,452 We should cover that one. 595 00:34:58,537 --> 00:34:59,197 Nikolay: Sure, sure. 596 00:34:59,457 --> 00:35:04,693 Okay, so we, we roughly, we discussed big three pros and big three cons roughly. 597 00:35:04,783 --> 00:35:05,053 Right? 598 00:35:05,053 --> 00:35:07,463 So I still big fan of this approach. 599 00:35:07,463 --> 00:35:11,363 I know majority people are, are not trying to avoid it, but. 600 00:35:11,723 --> 00:35:13,313 Still like nothing changed. 601 00:35:13,313 --> 00:35:14,813 The pros are still here. 602 00:35:15,072 --> 00:35:16,302 Michael: And you can mix and match. 603 00:35:16,442 --> 00:35:26,376 Nikolay: and interesting to observe further how usage of pog side programming will change with projects like super base because 604 00:35:26,381 --> 00:35:29,436 users already use views and some logic and so 605 00:35:30,418 --> 00:35:31,888 Michael: And triggers are really common. 606 00:35:32,008 --> 00:35:33,508 A lot of use cases for triggers. 607 00:35:33,853 --> 00:35:35,293 Nikolay: And URA as well. 608 00:35:35,519 --> 00:35:35,849 Michael: Yeah. 609 00:35:36,421 --> 00:35:37,351 Well, thank you everybody. 610 00:35:37,412 --> 00:35:38,642 Let us know what you think. 611 00:35:38,642 --> 00:35:40,698 Send us your requests as usual. 612 00:35:40,724 --> 00:35:41,624 We really appreciate it. 613 00:35:42,119 --> 00:35:42,329 Nikolay: Yeah. 614 00:35:42,329 --> 00:35:43,199 Thank you everyone. 615 00:35:43,409 --> 00:35:44,998 And share, Share links. 616 00:35:45,139 --> 00:35:45,659 Thank you. 617 00:35:45,659 --> 00:35:45,939 Bye. 618 00:35:46,056 --> 00:35:46,549 Michael: Take care.