1 00:00:00,323 --> 00:00:04,373 Michael: Hello and welcome to Postgres Event, a weekly show about all things PostgreSQL. 2 00:00:04,733 --> 00:00:09,053 I'm Michael, founder of PG Mustard, and this is my co-host Cola founder of Postgres ai. 3 00:00:09,503 --> 00:00:10,823 Hey, Nicola, what are we talking about today? 4 00:00:11,963 --> 00:00:12,684 Nikolay: Hi, Michael. 5 00:00:12,798 --> 00:00:15,374 I call this topic no backend, but. 6 00:00:16,429 --> 00:00:17,329 how can we call el? 7 00:00:17,449 --> 00:00:26,316 Like it's, it's about the idea of stop writing application code in Java, Python, ruby, php, doesn't matter. 8 00:00:26,766 --> 00:00:35,611 And using only front end code in your web or mobile app and only database site code, and that's it. 9 00:00:35,611 --> 00:00:36,421 Nothing in between. 10 00:00:37,062 --> 00:00:40,662 Michael: Yeah, so we had a nice, this is another listener suggestion. 11 00:00:40,662 --> 00:00:45,765 So we had a nice question that was all about good practices to build APIs directly in the database. 12 00:00:45,915 --> 00:00:47,985 And they specifically mentioned Postgres. 13 00:00:47,989 --> 00:00:48,762 Understandably, 14 00:00:49,737 --> 00:00:50,172 Nikolay: Right, right. 15 00:00:50,397 --> 00:00:52,077 So I, I describe this topic. 16 00:00:52,163 --> 00:00:55,143 First of all, I want to apologize that I'm interrupting you constantly. 17 00:00:55,723 --> 00:00:59,273 I, I, I will, I cannot promise, I I will stop doing it. 18 00:00:59,303 --> 00:01:02,312 But we had a comment in YouTube and I agree. 19 00:01:02,312 --> 00:01:03,692 I do it quite often. 20 00:01:03,692 --> 00:01:04,802 I, I know this problem. 21 00:01:05,222 --> 00:01:07,982 Some people cannot work with me because of it actually. 22 00:01:08,792 --> 00:01:08,882 So, 23 00:01:08,897 --> 00:01:10,727 Michael: well, I don't mind, so please don't worry 24 00:01:11,072 --> 00:01:12,512 Nikolay: But I will try to improve. 25 00:01:13,052 --> 00:01:13,592 I will try it. 26 00:01:13,952 --> 00:01:24,542 So I describe this topic as we 15 or like 20 years ago, it was normal to say that code on clients should be very lightweight. 27 00:01:24,572 --> 00:01:26,322 It should be some smallish tm. 28 00:01:27,002 --> 00:01:27,602 and so on. 29 00:01:27,733 --> 00:01:35,687 In 2004 or so, plus minus couple of years things changed with web 2.0 when Gmail, Google Maps and other web 30 00:01:35,687 --> 00:01:39,482 applications started to write much more code on client side. 31 00:01:40,377 --> 00:01:40,647 Right. 32 00:01:41,067 --> 00:01:43,666 And then iPhone and Android edit even more. 33 00:01:43,666 --> 00:01:51,908 So I call it we live in the era of very thick clients because a lot of code in business logic is coded on the 34 00:01:51,913 --> 00:01:55,478 client side in, in running, in browser or in model app. 35 00:01:56,258 --> 00:02:00,248 And this already ate some, it, it, it not ate. 36 00:02:00,248 --> 00:02:02,898 Like it took some work from backend developers. 37 00:02:03,383 --> 00:02:04,523 and changed everything. 38 00:02:05,123 --> 00:02:06,925 But then the idea why we. 39 00:02:07,020 --> 00:02:12,180 Still have backend code if also SQL is quite powerful today, right? 40 00:02:12,400 --> 00:02:18,840 We have, we have, we can do a lot, we can extract only the data we need from tables. 41 00:02:18,845 --> 00:02:27,712 We have, we can work with very efficiently and actually we, we can have again, store procedures or functions with lot of. 42 00:02:28,177 --> 00:02:28,747 On database. 43 00:02:28,747 --> 00:02:32,973 So database can be kind of limited application server itself. 44 00:02:33,783 --> 00:02:37,053 And also additional thing is JS O appeared right? 45 00:02:38,133 --> 00:02:42,260 JS o is a common format of communication for everyone. 46 00:02:42,440 --> 00:02:46,070 This gives us idea, why not eliminate. 47 00:02:46,785 --> 00:02:49,785 The middle of our three tier architecture. 48 00:02:49,785 --> 00:02:52,095 So keeping only client and database. 49 00:02:52,538 --> 00:02:58,808 some, maybe some additional middleware, but very lightweight and not needing constant modification in between. 50 00:02:59,738 --> 00:03:00,068 Right? 51 00:03:00,098 --> 00:03:09,296 So, so returning to request of our, our listeners, it was like how to write rest API on top of database easily, or it can be graph q. 52 00:03:09,296 --> 00:03:10,004 Server. 53 00:03:10,304 --> 00:03:17,460 So we want something that will support development of our applications, client code, and, and it can be just, it can 54 00:03:17,460 --> 00:03:21,849 be swift mobile app, written swift or something else, right? 55 00:03:21,849 --> 00:03:24,159 It's quite natural to think in this direction. 56 00:03:24,949 --> 00:03:28,226 Michael: Yeah, I think from a simplicity point of view, it makes a lot of sense. 57 00:03:28,376 --> 00:03:32,816 And also that a common one I see is a speed of development perspective. 58 00:03:32,846 --> 00:03:40,036 If I, if we can write the, the client and have the database and not have to write something else, we can 59 00:03:40,036 --> 00:03:42,646 get to a version one of our application quite a lot. 60 00:03:43,726 --> 00:03:49,625 Nikolay: Also think about data flow here, for example, we have relational database of course relational. 61 00:03:49,625 --> 00:03:50,135 What else? 62 00:03:50,405 --> 00:03:53,128 Even if it's not relational data can be. 63 00:03:53,583 --> 00:03:55,743 Stored in JS O and relation to database. 64 00:03:55,743 --> 00:04:00,693 I mean, we, we have very good strong js o support in Postgres, for example. 65 00:04:00,693 --> 00:04:07,876 We, we store many parts of our data as Js O, and then data flow, if it's some. 66 00:04:08,671 --> 00:04:11,385 Python or it's some Java application. 67 00:04:11,625 --> 00:04:17,355 We translate this data originally in JS O to some object format, right? 68 00:04:17,445 --> 00:04:20,775 Some, some objects or arrays or some structures, data structures. 69 00:04:21,105 --> 00:04:27,625 And then we translate them and give, give to our client called again in Js O. 70 00:04:28,200 --> 00:04:29,520 It feels very strange. 71 00:04:29,530 --> 00:04:32,470 this like jumping back and forth. 72 00:04:32,740 --> 00:04:36,250 It's called actual data marshing when we translate too much. 73 00:04:36,580 --> 00:04:40,660 So we could take this on and give Jason maybe transforming it somehow. 74 00:04:40,660 --> 00:04:44,454 Why we, why we should, we deal with all those objects and errors. 75 00:04:44,674 --> 00:04:52,445 And People like myself, invested a lot of time into writing, a lot of cold, following some object oriented design planters. 76 00:04:52,445 --> 00:05:00,132 They now regret it because it's good, like It's beautiful in terms of some concept, but practical wise it's not that good. 77 00:05:01,062 --> 00:05:11,025 And working with Dron and things like React, which define how to work with both structure and what to do with this structure. 78 00:05:11,055 --> 00:05:12,105 In, in same way. 79 00:05:12,105 --> 00:05:17,907 It's, it's this, this, these things changed the world already and the idea of having very light. 80 00:05:18,777 --> 00:05:25,617 Middleware and allow us to focus only on client code, only on database code. 81 00:05:25,617 --> 00:05:27,297 It makes sense, in my opinion. 82 00:05:28,077 --> 00:05:32,547 That's why, yeah, that's why we have Postgres and others. 83 00:05:33,612 --> 00:05:33,912 Michael: Yeah. 84 00:05:34,212 --> 00:05:36,342 Feels like a good time to dive into progress. 85 00:05:36,347 --> 00:05:41,172 Do you wanna give us a little bit of a, a top level on what, what exactly it is and why it's useful? 86 00:05:41,757 --> 00:05:42,477 Nikolay: Yeah, sure. 87 00:05:42,477 --> 00:05:42,657 Yeah. 88 00:05:42,657 --> 00:05:46,107 First time I, I saw it maybe like eight years ago. 89 00:05:46,112 --> 00:05:46,767 Quite long. 90 00:05:47,802 --> 00:05:55,747 and I implemented I, I used it in several projects including a case when successfully a small team of like four Ruby developers who are 91 00:05:56,887 --> 00:06:02,451 substituted by like 10 hours of my work per week with same result. 92 00:06:02,451 --> 00:06:07,122 So Postgre is an application written in has. 93 00:06:07,937 --> 00:06:10,507 which sits next to your Postgres database. 94 00:06:10,507 --> 00:06:16,617 It can sit on other hosts and, and of course, like It's stateless, so you can scale it and you can provision more. 95 00:06:16,617 --> 00:06:18,666 Compute nodes it's like proxy, right? 96 00:06:18,906 --> 00:06:23,436 But it allows you to define a structure in database. 97 00:06:23,441 --> 00:06:32,676 So of special format that will define automatically what rest API your Client code will be, be able to use. 98 00:06:32,826 --> 00:06:39,583 So for example, if you have a table, you can quickly define a view in, in special sche up, for example, view one, it's our like 99 00:06:39,883 --> 00:06:50,043 API version one, you define an updateable view and automatically a client code sees endpoint and can read insert, select from it. 100 00:06:50,163 --> 00:06:59,631 And HTP methods like get put post or automatically translate a patch are automatically translated to four dml statements. 101 00:06:59,691 --> 00:07:00,951 Select, update and search. 102 00:07:00,981 --> 00:07:01,281 Delete. 103 00:07:01,791 --> 00:07:02,751 We have so. 104 00:07:03,691 --> 00:07:09,196 There is a lot of logic for automation, like play with headers, modification, and so on and so on. 105 00:07:09,376 --> 00:07:14,536 It, it's already very advanced software actively developed for many years. 106 00:07:15,106 --> 00:07:18,406 Version 10, major version 10 is current. 107 00:07:18,676 --> 00:07:25,945 So I, couple of last years I not, I'm not following very closely it's development, but it's, it's. 108 00:07:27,250 --> 00:07:27,530 Constantly. 109 00:07:27,970 --> 00:07:31,076 And this is the project behind for example, super base. 110 00:07:31,226 --> 00:07:35,516 Super base is based on Postgres plus Postgres plus several more components. 111 00:07:36,236 --> 00:07:41,306 And so this, I, I love this idea, and actually I've already explained why, right? 112 00:07:41,306 --> 00:07:45,206 So you can focus, you can say we have Postgres. 113 00:07:45,746 --> 00:07:52,616 And for example, we have one POG guy, or half of it, like, like maybe 20% of a postgre guy per week. 114 00:07:52,625 --> 00:07:55,785 We just need to define our tables and these views and so on. 115 00:07:56,085 --> 00:07:56,565 And that's it. 116 00:07:56,565 --> 00:08:04,359 We whole team can focus on product development, on client code and go with startup activities, not spending 117 00:08:04,359 --> 00:08:07,844 power on API development in the old fashioned way. 118 00:08:09,441 --> 00:08:11,721 Michael: Yeah, and it's, and it's free to use as well, right? 119 00:08:11,721 --> 00:08:18,953 So even if you're not using something like super base, it, it's when I checked it out, it's just plain MIT license, which is awesome. 120 00:08:18,985 --> 00:08:22,439 So anybody can very quickly with a Postgres db. 121 00:08:22,506 --> 00:08:29,121 As a, as a backend, fire this up and have a fully working rest API pretty much immediately, right? 122 00:08:29,571 --> 00:08:30,291 Nikolay: right, right. 123 00:08:30,681 --> 00:08:31,821 It's also very performant. 124 00:08:32,340 --> 00:08:37,782 I remember when I first looked at the collect some things like for example, you know, no offset. 125 00:08:38,757 --> 00:08:40,257 Offset is very terrible thing. 126 00:08:40,257 --> 00:08:42,897 We know we, most of us know it, but very quick. 127 00:08:42,934 --> 00:08:45,650 Reminder using offset with like, limit. 128 00:08:45,650 --> 00:08:53,700 Offset is usually a bad idea because if you check the plan, execution plan, you will see that all previous C were fetched and discarded. 129 00:08:53,970 --> 00:08:57,059 So instead of it, you, you should use like How was called 130 00:08:57,384 --> 00:08:57,784 Michael: Ation. 131 00:08:58,559 --> 00:08:59,169 Nikolay: Keyset? 132 00:08:59,174 --> 00:09:00,359 Imagination, I think. 133 00:09:00,389 --> 00:09:01,049 Yeah, yeah, yeah. 134 00:09:01,679 --> 00:09:03,989 And by the way, I've learned from PostGrest a lot. 135 00:09:04,139 --> 00:09:07,289 For example, this term I learned, I learned dealing with Postgres. 136 00:09:07,529 --> 00:09:10,139 I also learned about ski. 137 00:09:10,379 --> 00:09:14,609 I mentioned many times ski tool Alternative to Flyway or, liquid base. 138 00:09:14,759 --> 00:09:20,312 So the tool to control schema changes and to have schema version control. 139 00:09:20,738 --> 00:09:30,205 I also learned from there just reading documentation and chatting with people in Postgre community, which is also not, not huge, but very active and good. 140 00:09:30,805 --> 00:09:33,806 So You can have very performant api. 141 00:09:33,811 --> 00:09:37,312 So it's written, high scale, very polished. 142 00:09:37,312 --> 00:09:40,972 When, ah, when I joined, they had this offset. 143 00:09:41,294 --> 00:09:49,444 and I don't remember details, but I, I contributed a little bit to improve how ation is working and to get rid of suboptimal 144 00:09:49,444 --> 00:09:52,204 queries when you have like billions softwares, for example. 145 00:09:52,384 --> 00:09:56,923 So right now Postgres has better proven on large databases under heavy load. 146 00:09:56,923 --> 00:10:00,253 You can use it however, there are of course cons. 147 00:10:00,403 --> 00:10:02,803 We, we haven't touched them in this approach. 148 00:10:02,803 --> 00:10:03,463 We have cons. 149 00:10:03,468 --> 00:10:07,513 Let's, let's first think if we mentioned all pros, right? 150 00:10:08,008 --> 00:10:11,068 Michael: I think another big pro that people bring up is security. 151 00:10:11,098 --> 00:10:16,078 So you can define things in the DA at the database level and it will inherit those. 152 00:10:16,258 --> 00:10:23,567 So it's quite nice to have that in one place and to be able to trust the data or to be able to trust Postgres in this specific example to handle that. 153 00:10:23,597 --> 00:10:25,727 So both at the schema level, so. 154 00:10:26,327 --> 00:10:36,305 Which objects are exposed to it, to the Postgres user, and I guess at the, the role level role level security policies which is something I'd never 155 00:10:36,305 --> 00:10:40,218 seen used much until I looked into how how super base were doing things. 156 00:10:41,208 --> 00:10:42,108 Nikolay: Right, right. 157 00:10:42,508 --> 00:10:48,966 So this approach is an alternative to manual implementation of arrest api. 158 00:10:49,896 --> 00:10:59,438 I think many, maybe most dev backend developers did it at some point and had feeling that there is some repetitive action, like 159 00:10:59,648 --> 00:11:04,088 some copy pasting involved and so on, and it's performant and secure. 160 00:11:04,118 --> 00:11:06,128 And also it's an alternate maybe. 161 00:11:06,828 --> 00:11:13,426 The, maybe it's also makes sense to think about it as an alternative to, or om right. 162 00:11:13,426 --> 00:11:16,906 Because it's also, it's also mapping, but there is no objects here. 163 00:11:16,911 --> 00:11:25,576 We mapping usually either relational data to Jason or Jason in tables to Jason or a mix of them to Jason. 164 00:11:25,936 --> 00:11:34,806 And Jason of course is Also working with Postgre, I learned about, how about Js o manipulation and postgre how, like a lot, a lot. 165 00:11:34,811 --> 00:11:41,941 Like I, I, I was constantly sitting in the js o part of Postgres documentation because I needed. 166 00:11:42,818 --> 00:11:52,203 And one, it requires some effort to, to master based on skills for pogs because there are a lot of operators and functions. 167 00:11:52,593 --> 00:11:53,862 But once you achieve it you. 168 00:11:54,597 --> 00:11:59,257 Like it actually because it's very powerful and a lot of freedom. 169 00:11:59,677 --> 00:12:05,285 You can combine relation that and JS o and give it to your client code so easily. 170 00:12:05,522 --> 00:12:09,717 like we already discussed this store procedures and database site. 171 00:12:10,899 --> 00:12:19,893 and many people, uh, and we also mentioned that sometimes you, a few lines in SQL can replace hundred of lines in Python, Java, right? 172 00:12:20,223 --> 00:12:23,324 It can happen easily when you talk about managing data. 173 00:12:23,336 --> 00:12:25,092 So we have it here as well, right? 174 00:12:25,097 --> 00:12:26,872 In this is our approach. 175 00:12:26,877 --> 00:12:30,462 We write a database site code, and. 176 00:12:31,422 --> 00:12:34,752 Produce JS O and we can consume json easily. 177 00:12:34,820 --> 00:12:37,770 And, that's why it gives speed of development actually, right? 178 00:12:37,775 --> 00:12:41,058 Because it's so you, Get rid of all like fat. 179 00:12:41,058 --> 00:12:44,598 I would say in quoting, you don't need to think about this. 180 00:12:44,648 --> 00:12:46,040 object design patterns. 181 00:12:46,040 --> 00:12:54,400 You just take raw data, do what you need on database side, and, and give js o very, very efficiently in, in, in develop. 182 00:12:56,020 --> 00:13:02,260 Michael: Yeah, not, I guess it's not directly related to this subject, but while we're talking about them, I think I'll, I'll include links 183 00:13:02,265 --> 00:13:06,490 to a lot of the JS O functions as a good list in the Postgres docs. 184 00:13:06,707 --> 00:13:11,057 it's fun to read, even if you're not planning to use it anytime soon, just to see what is possible. 185 00:13:11,357 --> 00:13:20,478 And the other one was, You mentioned the no offset, and I think Marcus Wynand deserves a shout out for all of the work he's doing publicizing this movement. 186 00:13:20,708 --> 00:13:24,568 But he also think gives out stickers and he, he's got a great page on his modern. 187 00:13:24,628 --> 00:13:24,868 Yeah. 188 00:13:24,868 --> 00:13:25,138 Great. 189 00:13:25,249 --> 00:13:28,519 He's got a great page on his modern SQR website that I'll link to as well. 190 00:13:28,579 --> 00:13:32,539 Nikolay: Yeah, modern scale and use the index loop.com as well. 191 00:13:32,599 --> 00:13:33,229 Like I, I 192 00:13:33,239 --> 00:13:33,779 Michael: Very good one. 193 00:13:34,009 --> 00:13:40,007 Nikolay: like, brilliant websites for, for those who are interested in relational performance optimization. 194 00:13:40,025 --> 00:13:44,264 Also Few more words about efficiency of development. 195 00:13:44,264 --> 00:13:55,355 If you think about all rest APIs, most of them are repeating some logic and it's natural to use some declarative form to declare new endpoints and so on. 196 00:13:55,355 --> 00:13:55,625 Right? 197 00:13:55,895 --> 00:14:03,215 So SQL is declarative language and post rest can be considered as a declar of way to build rest api, right? 198 00:14:04,185 --> 00:14:04,675 Michael: Yeah. 199 00:14:05,345 --> 00:14:06,425 Nikolay: That's why it's so good. 200 00:14:07,370 --> 00:14:15,713 Js O declarative way and a lot of things are automated that most of projects need enough pro maybe switch to cons. 201 00:14:16,430 --> 00:14:16,580 What 202 00:14:16,585 --> 00:14:17,000 do you think? 203 00:14:17,828 --> 00:14:18,428 Michael: Yeah, for sure. 204 00:14:18,428 --> 00:14:24,824 And I, I guess is it worth saying that a lot of these same benefits could be said of similar projects? 205 00:14:24,824 --> 00:14:32,480 Like in the GraphQL space we have things like post graph Azure, if it feels for me, I mean, I might be wrong, but logically it's very similar. 206 00:14:33,149 --> 00:14:33,479 Nikolay: Right. 207 00:14:33,636 --> 00:14:34,026 right. 208 00:14:34,116 --> 00:14:41,719 So both open source projects you mentioned, And companies, we mentioned super base and you mentioned ura. 209 00:14:42,139 --> 00:14:45,058 The difference is Postgre is like rest api. 210 00:14:45,358 --> 00:14:47,068 There are several attempts to build. 211 00:14:47,488 --> 00:14:56,168 I like, it's not my thing GraphQL, but I, I know a lot of front end developers have the idea of GraphQL especially those who work with React. 212 00:14:56,168 --> 00:15:00,587 And so nice idea, but it's just, I, I like experience in, in this area. 213 00:15:01,151 --> 00:15:06,762 , there were attempts to build ability to create GraphQL APIs on top of sre. 214 00:15:07,082 --> 00:15:10,542 But also there is, has now somehow it's also Haskill. 215 00:15:10,569 --> 00:15:11,559 They use has scale as well. 216 00:15:12,024 --> 00:15:12,864 Water incidents. 217 00:15:12,864 --> 00:15:13,164 Right. 218 00:15:13,184 --> 00:15:14,664 And also very successful. 219 00:15:15,204 --> 00:15:24,204 So I, I like to see these successful projects, but it's interesting that I, I, I'm not an expert in, in history, but I know that there 220 00:15:24,209 --> 00:15:27,979 are a couple of projects pars.com and one more, I forgot the name. 221 00:15:27,979 --> 00:15:34,847 One was acquired, was acquired by Facebook, another was acquired by PayPal, and they it was more than 10 years. 222 00:15:35,507 --> 00:15:36,977 Or roughly 10 years ago. 223 00:15:37,487 --> 00:15:44,215 And they, they, ideas of those companies which were both acquired and then shut, shut down somehow. 224 00:15:44,515 --> 00:15:52,765 The idea was to provide service and cloud to mobile application developers to avoid the need to, to fill 225 00:15:52,825 --> 00:15:55,555 the need, to fulfill the need in backend development. 226 00:15:55,855 --> 00:16:06,926 So these class of projects were, was called, or services was called M B a SBA BUS, MD Bus, mobile database as a service, right? 227 00:16:06,926 --> 00:16:13,785 So not database as a service like rds, but mobile database as a service, meaning that database for mobile app developers. 228 00:16:13,935 --> 00:16:14,085 I. 229 00:16:15,045 --> 00:16:15,315 Right. 230 00:16:15,315 --> 00:16:23,275 So you have not only database, but also some kind of application server equipped with database and somehow they are close. 231 00:16:23,275 --> 00:16:25,045 I don't know why, interestingly, right, right. 232 00:16:25,045 --> 00:16:35,515 So like this, it looks like super base and has, are a new wave of, of this idea to simplify backend development 233 00:16:35,515 --> 00:16:38,425 for those who want to focus only on front end develop. 234 00:16:39,262 --> 00:16:39,472 Right. 235 00:16:39,472 --> 00:16:43,129 And they are much more successful and and a lot of users and we see it. 236 00:16:44,374 --> 00:16:44,734 Michael: Yeah. 237 00:16:45,034 --> 00:16:48,514 And the other big successful one in that space feels like fire base. 238 00:16:48,514 --> 00:16:52,825 I know it's Closed source, but it, it feels worth mentioning that that has been a success. 239 00:16:52,825 --> 00:16:55,825 So it seems likely that these, these other ones could be too. 240 00:16:56,490 --> 00:16:57,050 Nikolay: Right? 241 00:16:57,220 --> 00:17:02,643 So fire brass is no, no sequel, like key value or Document Store 242 00:17:02,808 --> 00:17:03,558 Michael: Is, yeah. 243 00:17:04,323 --> 00:17:05,133 Nikolay: on Google Cloud. 244 00:17:05,913 --> 00:17:09,715 And it's very successful because for example, imagine you're an app. 245 00:17:10,255 --> 00:17:12,085 An app, mobile app development. 246 00:17:12,085 --> 00:17:18,415 You want database, but also if something changes on database, you want your application to receive the change somehow. 247 00:17:18,415 --> 00:17:24,715 Like automatically probably using, like, I would implement it using like web sockets for example, right? 248 00:17:24,715 --> 00:17:30,497 So I, I pushed the change to my application and this is what Firebase offer. 249 00:17:30,992 --> 00:17:33,619 So it's fully managed database with this. 250 00:17:34,378 --> 00:17:37,837 Synchronization capability, like push the change to to client side code. 251 00:17:38,287 --> 00:17:44,910 And it's, it's, I think it's still successful in super bases positioned as open source alternative to fire best. 252 00:17:44,943 --> 00:17:47,889 It's based on post past guest plus additional competence. 253 00:17:48,001 --> 00:17:54,451 by the way, I'm not sure how it's implemented in detail, but I guess it's based on maybe on logical decoding and Web sos. 254 00:17:54,492 --> 00:17:58,995 Maybe one day we should invite them to, to describe it in detail. 255 00:17:59,476 --> 00:17:59,716 Michael: Yep. 256 00:18:00,046 --> 00:18:00,286 Nikolay: Right. 257 00:18:00,586 --> 00:18:06,016 But it everything, like, everything is super successful, especially if you're a small team of some startup and you need to quickly 258 00:18:06,016 --> 00:18:10,351 change your, your interfaces and check various ideas very fast. 259 00:18:10,681 --> 00:18:16,351 And you don't want to keep whole team of rub Java developers constantly. 260 00:18:17,521 --> 00:18:17,671 Yeah. 261 00:18:18,091 --> 00:18:18,841 But let's, 262 00:18:18,976 --> 00:18:19,156 Michael: good. 263 00:18:19,156 --> 00:18:19,636 How about, 264 00:18:19,906 --> 00:18:20,176 yep. 265 00:18:20,371 --> 00:18:20,581 Nikolay: yeah. 266 00:18:20,851 --> 00:18:22,141 So, sorry I interrupted you. 267 00:18:22,141 --> 00:18:22,471 Sorry. 268 00:18:22,726 --> 00:18:27,136 Michael: No, that's my um, how about some of the downsides, I guess is what you're gonna go 269 00:18:27,466 --> 00:18:27,856 next? 270 00:18:27,856 --> 00:18:28,186 Is that what 271 00:18:28,236 --> 00:18:28,636 you're thinking? 272 00:18:29,101 --> 00:18:29,731 Nikolay: exactly. 273 00:18:29,761 --> 00:18:33,781 So if, if you're a small team, it's good, but, but what if you're a bigger team? 274 00:18:33,811 --> 00:18:40,573 I observe cases when even in bigger companies things like or Supervisor ura also work well. 275 00:18:41,055 --> 00:18:49,649 I remember some talk about I don't remember the company exactly, but it was a big company and they decided to stop developing their own APIs. 276 00:18:50,399 --> 00:18:54,509 It was REST API originally, and a lot of development in house, and they switched. 277 00:18:54,869 --> 00:18:55,979 It was uh, a. 278 00:18:56,087 --> 00:19:04,547 One step switch to GraphQL and ura and they liked it so much so they, they talked about it with and how to shift it in a big company. 279 00:19:04,547 --> 00:19:08,867 So it definitely makes sense for big companies also to consider this approach. 280 00:19:08,897 --> 00:19:09,407 Definitely. 281 00:19:09,437 --> 00:19:11,687 But what are downsides, first of all? 282 00:19:12,247 --> 00:19:18,519 Like maybe not the main, definitely not the main downside is that these somehow these middle where uh, 283 00:19:19,149 --> 00:19:22,489 CS are written in HU skill if you want to adjust it. 284 00:19:23,299 --> 00:19:25,579 Well, HUS skill is interesting language, I would say. 285 00:19:25,879 --> 00:19:27,409 I tried to contribute there. 286 00:19:27,409 --> 00:19:35,479 It will, I maybe something I, some things I did, but it's definitely you need to some mind shift to, to work with high skill. 287 00:19:35,809 --> 00:19:40,069 And I have huge respect to people who, who like it and continue. 288 00:19:41,369 --> 00:19:41,849 So this is 289 00:19:41,849 --> 00:19:42,329 probably some, 290 00:19:42,499 --> 00:19:42,659 Michael: a, 291 00:19:43,349 --> 00:19:43,499 Nikolay: yeah, 292 00:19:44,339 --> 00:19:46,829 Michael: all I was gonna say is a functional programming language. 293 00:19:46,829 --> 00:19:51,393 And for full disclosure, PG mustard is written enclosure, so I can't 294 00:19:51,393 --> 00:19:51,959 Nikolay: disclosure. 295 00:19:51,959 --> 00:19:52,799 Written closure. 296 00:19:52,799 --> 00:19:53,609 Right. 297 00:19:54,089 --> 00:19:54,809 , full disclosure. 298 00:19:54,809 --> 00:19:55,709 Written closure. 299 00:19:55,714 --> 00:19:58,739 Closure is also, it's like also functional language. 300 00:19:58,739 --> 00:19:59,429 It's, it's good. 301 00:19:59,669 --> 00:20:10,105 Like these languages are great, but their adoption is very, It's, they are not super popular compared to, I dunno, like goal language for example, or Python. 302 00:20:10,172 --> 00:20:21,255 And another problem like possible downside is that following this route, you will route, you will have a tendency to use more database site code. 303 00:20:22,035 --> 00:20:24,649 and if communication with external. 304 00:20:24,679 --> 00:20:25,459 It's this problem. 305 00:20:25,459 --> 00:20:29,629 We, we discussed this problem at in our episode about store procedures. 306 00:20:30,115 --> 00:20:41,147 if you need to communicate with external world call some rest a external api, for example, or to download some binary, for example, a picture and save it. 307 00:20:41,987 --> 00:20:45,734 It's not good to consume CPU cycles On your 308 00:20:46,639 --> 00:20:47,744 Michael: On your primary database? 309 00:20:47,869 --> 00:20:49,069 Nikolay: especially on primary. 310 00:20:49,519 --> 00:20:50,029 Exactly. 311 00:20:50,029 --> 00:20:59,493 If it's a function, which rights to databases can be only primary, so you consume cpu, which like very limited 312 00:20:59,493 --> 00:21:02,703 resource, the most limited resource in the Nepo cluster. 313 00:21:02,783 --> 00:21:14,933 This can be dangerous, and I remember one day I used even pssh, which I wouldn't recommend for normal use at all to save a picture from Postgre. 314 00:21:14,935 --> 00:21:19,935 I had the call, like if they called as I remember, maybe still like rpc. 315 00:21:19,940 --> 00:21:23,255 So you have post slash rpc slash your function name. 316 00:21:23,675 --> 00:21:25,295 And this is a function in Postgres. 317 00:21:25,295 --> 00:21:31,281 And it was SSH function, which is like store procedure, store function using batch. 318 00:21:31,971 --> 00:21:43,284 And it, it then I had some cool calls there and stored the, sold my primary PO's primary node downloaded image and then called 319 00:21:43,284 --> 00:21:47,296 some additional small microservice to, it can be s3, for example. 320 00:21:47,296 --> 00:21:49,306 You can now save it on three. 321 00:21:49,606 --> 00:21:50,866 It's the approach. 322 00:21:50,866 --> 00:21:55,351 You never should you never would like to follow unless it's some very. 323 00:21:56,456 --> 00:21:58,106 Quick and dirty prototype of something. 324 00:21:58,736 --> 00:22:01,946 It's not on normal service because why? 325 00:22:01,946 --> 00:22:05,606 Because CPU on the primary is very limited and you shouldn't do it at all. 326 00:22:06,656 --> 00:22:08,756 Michael: and if you're, if you're a new listener, welcome. 327 00:22:08,761 --> 00:22:12,416 But also we have a previous episode all about this on stop procedures. 328 00:22:13,047 --> 00:22:14,007 Nikolay: Right, right. 329 00:22:14,307 --> 00:22:17,967 So this is second cons, second item in our cons list. 330 00:22:17,997 --> 00:22:18,327 What? 331 00:22:18,387 --> 00:22:19,047 What else? 332 00:22:20,011 --> 00:22:20,781 Michael: Good question. 333 00:22:20,971 --> 00:22:23,978 I, well, I've got some cons from developers actually on Reddit. 334 00:22:24,038 --> 00:22:25,958 I think the first one they said. 335 00:22:26,270 --> 00:22:31,578 When this, in one of the places where this was shared when I think probably when it first came out, but I didn't check. 336 00:22:31,645 --> 00:22:33,285 The top comment on that is, thanks. 337 00:22:33,290 --> 00:22:34,305 I'm unemployed now. 338 00:22:34,635 --> 00:22:39,810 So that's, maybe that's a con losing developer jobs, could that be considered a downside? 339 00:22:40,200 --> 00:22:41,220 Nikolay: Well, yes. 340 00:22:41,280 --> 00:22:45,198 Maybe, maybe, But also to balance it. 341 00:22:45,198 --> 00:22:50,178 I, I saw not once I saw people who say it's not right to, to use SQL for it. 342 00:22:50,598 --> 00:22:52,098 SQL is okay. 343 00:22:52,098 --> 00:22:58,248 SQL is okay language, but I would love rather to use my Goal or Python or Java for that. 344 00:22:58,308 --> 00:23:00,918 Michael: Oh yeah, by the way, I was definitely joking. 345 00:23:01,038 --> 00:23:06,138 I think there's plenty of developer jobs to go around and definitely still more demand than supply. 346 00:23:06,436 --> 00:23:08,896 Nikolay: you're touching very sensitive topic. 347 00:23:08,974 --> 00:23:17,452 So yeah, but, so maybe we could say like, A new item on the Cons list is a lack of libraries probably, right? 348 00:23:17,482 --> 00:23:26,992 Because if you use Python or even go, which is newer language, you have so many libraries already written, so you have 349 00:23:27,052 --> 00:23:31,942 so huge power to extend your API with additional things. 350 00:23:32,542 --> 00:23:40,852 For example, if you want machine learning and you, you use Jungle and you decided to to, this is, by the way, when I. 351 00:23:40,913 --> 00:23:42,434 Ruby developers in that project. 352 00:23:42,494 --> 00:23:48,974 Then later, I already, it was fine, but they, they pivoted this company and they then hired Python developers. 353 00:23:49,274 --> 00:23:53,486 One of them is my, one of my best friends in university. 354 00:23:53,846 --> 00:23:56,606 He, he told me We are not going to use Postgres anymore. 355 00:23:56,654 --> 00:24:01,634 We know John, we are going to use a lot of machine learning stuff, so we are going to. 356 00:24:01,634 --> 00:24:03,791 Switch from your stuff to John. 357 00:24:03,791 --> 00:24:06,821 Go API development and we will be using a lot of machine learning. 358 00:24:07,121 --> 00:24:10,581 Of course, I could say there is machine learning in pogs. 359 00:24:10,586 --> 00:24:13,721 There is a Med leap of this pogo, something new, right? 360 00:24:14,111 --> 00:24:15,401 So it's possible. 361 00:24:15,401 --> 00:24:16,541 And there is P Python. 362 00:24:16,546 --> 00:24:18,941 You can use a lot of Python code inside pogs. 363 00:24:19,511 --> 00:24:23,831 But I felt there, I mean, I, I not fail, I lost, I lost that battle. 364 00:24:24,167 --> 00:24:24,497 Why? 365 00:24:25,319 --> 00:24:28,229 I'm not sure by the way why, like they just said, this is what we use. 366 00:24:28,469 --> 00:24:30,079 This is easier for us, more convenient. 367 00:24:30,922 --> 00:24:37,322 Michael: I think generally people do have a bit more familiarity with their backend services that they've got used to than they do with Postgres. 368 00:24:37,322 --> 00:24:42,872 So one of the, the downsides is that we are pushing a bit more responsibility and work into the database. 369 00:24:42,872 --> 00:24:51,032 So like, for example, around those security things, I wonder if that's part of it, familiarity with how to do that and how to debug things if things go. 370 00:24:51,172 --> 00:24:51,952 Nikolay: Oh, debugger. 371 00:24:51,982 --> 00:24:52,912 Debugger is a good thing. 372 00:24:52,942 --> 00:24:54,352 Yes, IDE and so on. 373 00:24:54,622 --> 00:25:00,332 So the same, same issues as for store procedures when we cover them in that episode, actually. 374 00:25:00,337 --> 00:25:00,532 Yeah. 375 00:25:00,592 --> 00:25:01,162 Right, right. 376 00:25:01,702 --> 00:25:09,850 But if it's quite simple api, for example, if you have a table, you define a view, as I said, if you just need simple inserts, select, 377 00:25:09,850 --> 00:25:19,856 and so on, and some kind of logic related to which columns should be available for users, everything can be done at the view level. 378 00:25:19,856 --> 00:25:25,127 You don't need the to write a function and use p gsk at all for this, right? 379 00:25:25,487 --> 00:25:32,062 So in this case, Probably like, it's good, it's enough and, and it's faster in terms of development. 380 00:25:32,062 --> 00:25:35,354 And actually people should know SQL at in general. 381 00:25:35,876 --> 00:25:40,254 but if you need some advanced logic, maybe you'll have issues. 382 00:25:40,554 --> 00:25:41,934 Again, it's possible, but. 383 00:25:42,549 --> 00:25:42,759 Yeah. 384 00:25:43,569 --> 00:25:44,439 So it depends. 385 00:25:44,499 --> 00:25:47,985 And the, constant pros are not very strict to me. 386 00:25:47,985 --> 00:25:54,175 Like, for example, if I take some project, I would, am I, going to use Postgre one more time? 387 00:25:54,175 --> 00:25:59,044 Or, I will say, okay, a team of Python developers, you, you do it. 388 00:25:59,814 --> 00:26:03,394 So I have personal preference to use Postgres, of course. 389 00:26:03,544 --> 00:26:07,174 And at Post, we use it for our platform as well, by the way. 390 00:26:07,761 --> 00:26:08,211 Michael: Oh, cool. 391 00:26:08,271 --> 00:26:09,741 I didn't realize that makes sense. 392 00:26:10,547 --> 00:26:16,826 Nikolay: So if, if you, if we extend something, it's it's, this is written in React and post, that's it. 393 00:26:17,765 --> 00:26:23,195 Michael: Are there any, this might be a stupid question, but are there any downsides or is, is it, is it easy enough to 394 00:26:23,195 --> 00:26:28,025 do complex qu, like maybe a quite a complex analytical query? 395 00:26:28,115 --> 00:26:29,315 Is that easy enough to do? 396 00:26:30,305 --> 00:26:31,795 Nikolay: Well, any query can be there. 397 00:26:32,265 --> 00:26:32,685 Wow. 398 00:26:32,705 --> 00:26:35,677 So it can be a view, it can be a store procedure like 399 00:26:35,700 --> 00:26:38,330 Michael: But it has to be on the Postgres side rather than 400 00:26:38,730 --> 00:26:39,330 Nikolay: right? 401 00:26:39,390 --> 00:26:39,720 But 402 00:26:40,160 --> 00:26:40,650 Michael: Okay. 403 00:26:40,770 --> 00:26:43,830 Nikolay: it should be always on database side queries should be there. 404 00:26:44,490 --> 00:26:49,509 It, it's not good to load them like our developers do and do everything in memory. 405 00:26:50,259 --> 00:26:51,999 So I don't like this approach. 406 00:26:52,269 --> 00:26:52,689 It should be in 407 00:26:52,734 --> 00:26:53,544 Michael: Makes sense. 408 00:26:53,979 --> 00:26:54,159 Nikolay: right? 409 00:26:54,482 --> 00:26:56,465 Oh, by the way, I think one more. 410 00:26:57,149 --> 00:27:02,309 it won't be very like strictly defined, but one more item to the cons list. 411 00:27:02,519 --> 00:27:09,031 It's more like about progress, not about progressed lack of tools for a synchronous nature of work with data. 412 00:27:09,421 --> 00:27:12,451 We discussed it also several times, so for example, you want something. 413 00:27:13,036 --> 00:27:16,006 But you don't want to do this heavy work right now. 414 00:27:16,006 --> 00:27:18,946 You want to schedule it and then return to processing. 415 00:27:19,366 --> 00:27:28,365 And this encourages you to implement some, some kind of queue inside database or to use external queueing mechanism. 416 00:27:28,845 --> 00:27:34,565 And there are ways to do it, but it it, like from a SQL context that's slightly. 417 00:27:35,260 --> 00:27:41,740 Difficult, especially because unfortunately, the world of developers lost very good, in my opinion. 418 00:27:41,740 --> 00:27:46,330 Tool called PG Q from Skype developed many, many years ago. 419 00:27:46,600 --> 00:27:52,240 Unfortunately, humanity lost this quite good dinosaur I would say. 420 00:27:53,005 --> 00:27:54,974 I liked it because of Its performance. 421 00:27:54,974 --> 00:27:56,385 And it was easy to work with. 422 00:27:56,445 --> 00:28:02,355 It was good, it had good performance, some kind of maintenance needed, but much less than for Kafka, for example. 423 00:28:03,075 --> 00:28:07,357 And since then we observe a lot of attempts to reinvent that wheel. 424 00:28:08,112 --> 00:28:13,541 and most of them are failing to do it in, in a way that I would be able to use them in large project. 425 00:28:13,751 --> 00:28:19,061 So most of attempt to implement Q in database cannot scale well, unfortunately. 426 00:28:20,021 --> 00:28:22,861 But this is maybe another another topic to discuss. 427 00:28:22,921 --> 00:28:25,801 So, and if you use postgre, you build api. 428 00:28:26,446 --> 00:28:33,316 , for example, to save pictures on some cold, I would think about how to have a cue mechanism close to it. 429 00:28:33,586 --> 00:28:35,206 What would be your cue mechanism? 430 00:28:35,566 --> 00:28:42,223 A synchronous processing in, in this case, you, in this case you'll be fine because for most languages, of course, 431 00:28:42,223 --> 00:28:45,583 we have libraries to work with, like , anything like that. 432 00:28:46,103 --> 00:28:47,723 Michael: Yeah, good point. 433 00:28:48,433 --> 00:28:49,233 Wonderful. 434 00:28:50,228 --> 00:28:51,548 Well, I think that's probably a wrap then. 435 00:28:51,728 --> 00:28:52,988 Thank you so much, Nicola. 436 00:28:52,988 --> 00:28:55,388 Thanks everyone for listening and see you next week. 437 00:28:55,538 --> 00:28:57,218 Nikolay: Yeah, I hope it was helpful. 438 00:28:57,248 --> 00:29:02,258 Don't forget to share links and provide new ideas to discuss. 439 00:29:02,528 --> 00:29:03,308 Thank you so much. 440 00:29:03,458 --> 00:29:04,188 Till next time, bye bye.