1 00:00:00,060 --> 00:00:02,280 Michael: Hello, hello and welcome to Postgres.FM, weekly show 2 00:00:02,280 --> 00:00:03,340 about all things PostgreSQL. 3 00:00:03,520 --> 00:00:04,900 I am Michael, founder of pgMustard. 4 00:00:04,960 --> 00:00:07,700 I'm joined as usual by Nikolay, founder of Postgres.AI. 5 00:00:07,700 --> 00:00:08,400 Hey Nikolay. 6 00:00:08,640 --> 00:00:09,360 Nikolay: Hey Michael. 7 00:00:09,360 --> 00:00:10,220 How are you? 8 00:00:10,380 --> 00:00:11,460 Michael: Nope, not falling for that. 9 00:00:11,460 --> 00:00:16,240 And we are joined by a wonderful guest, Lukas Eder, founder of 10 00:00:16,240 --> 00:00:20,820 Data Geekery and the creator of jOOQ, a very popular framework. 11 00:00:21,100 --> 00:00:22,860 Thank you so much for joining us, Lukas. 12 00:00:23,400 --> 00:00:24,840 Lukas: Thank you for having me, Michael. 13 00:00:25,680 --> 00:00:26,780 Michael: It's our pleasure. 14 00:00:27,260 --> 00:00:31,080 So, how do you describe jOOQ and what's it helping folks with? 15 00:00:31,160 --> 00:00:33,640 Lukas: So, jOOQ is an internal domain-specific language written 16 00:00:33,640 --> 00:00:37,280 in Java where it models the SQL language directly as a Java API. 17 00:00:37,900 --> 00:00:41,940 This helps Java developers to write SQL queries more efficiently 18 00:00:42,180 --> 00:00:44,940 and more correctly within their Java applications. 19 00:00:45,060 --> 00:00:48,860 There's a code generator that generates the entire schema as 20 00:00:48,860 --> 00:00:51,740 Java objects and you can interact with those objects and create 21 00:00:51,740 --> 00:00:52,800 type safe queries. 22 00:00:53,220 --> 00:00:55,240 That's the base of it. 23 00:00:55,520 --> 00:00:56,520 Michael: Yeah, it's super cool. 24 00:00:56,520 --> 00:01:00,040 But I'm super impressed with how much you focus on developer 25 00:01:00,060 --> 00:01:01,160 experience in general. 26 00:01:01,380 --> 00:01:04,220 I hear quite a few people say they really love jOOQ and they 27 00:01:04,220 --> 00:01:07,280 give about 15 different examples of things they love about it. 28 00:01:07,280 --> 00:01:10,460 But what are the main things you think that people particularly 29 00:01:10,520 --> 00:01:11,320 like about it? 30 00:01:11,320 --> 00:01:14,160 Lukas: Well, the first thing is, and I've studied the market 31 00:01:14,160 --> 00:01:17,900 extensively before making jOOQ and the first thing is it really 32 00:01:17,900 --> 00:01:22,900 looks as much as SQL as it's possible to do when you model an 33 00:01:22,900 --> 00:01:24,900 internal DSL API in Java. 34 00:01:25,080 --> 00:01:26,620 So, of course, there's some limitations. 35 00:01:27,040 --> 00:01:30,020 But when you have in mind there's a group by clause, you just 36 00:01:30,020 --> 00:01:32,960 start typing group by where you expect it, and then it just starts 37 00:01:32,960 --> 00:01:33,460 compiling. 38 00:01:33,520 --> 00:01:34,660 So there's no surprises. 39 00:01:35,060 --> 00:01:37,860 There's almost no surprises in terms of SQL syntax. 40 00:01:38,140 --> 00:01:41,180 So if you know SQL, then you immediately know the jOOQ API. 41 00:01:41,600 --> 00:01:43,300 You don't really have to learn it. 42 00:01:43,460 --> 00:01:47,220 I guess that's 1 of the most popular things, But other than that, 43 00:01:47,220 --> 00:01:50,820 it's also very nicely integrated into the Java language or Kotlin 44 00:01:50,820 --> 00:01:52,580 and Scala, that works as well. 45 00:01:52,900 --> 00:01:59,740 And if you've ever coded PL SQL in Oracle or PL/pgSQL in Postgres, 46 00:02:00,240 --> 00:02:03,160 You kind of like the way how the SQL language is embedded in 47 00:02:03,160 --> 00:02:04,280 the procedural language. 48 00:02:04,280 --> 00:02:08,940 So if you loop, if you iterate over a result set in those procedural 49 00:02:09,000 --> 00:02:13,160 languages, you have the same kind of type safety and you have 50 00:02:13,500 --> 00:02:16,480 the possibility, for instance, to insert bind variables into 51 00:02:16,480 --> 00:02:19,120 your statements at the right spot, and you don't have to worry 52 00:02:19,120 --> 00:02:22,240 about the infrastructure and the logistics of connecting to the 53 00:02:22,240 --> 00:02:24,100 database, and it just feels embedded. 54 00:02:24,440 --> 00:02:27,900 And I think that's what a lot of people really want to do, even 55 00:02:27,900 --> 00:02:31,280 more than having the type safety, is this feeling of embeddedness 56 00:02:31,560 --> 00:02:32,940 into the target language. 57 00:02:32,980 --> 00:02:36,880 And when you map your result sets to Java objects, that also 58 00:02:36,880 --> 00:02:38,000 feels very native. 59 00:02:38,000 --> 00:02:41,620 So it feels like the database is part of the Java of the JVM. 60 00:02:42,720 --> 00:02:43,080 Michael: Yeah. 61 00:02:43,080 --> 00:02:46,360 Even to the point of getting like auto-complete suggestions in 62 00:02:46,360 --> 00:02:49,520 IDEs, but like that is super nice. 63 00:02:50,180 --> 00:02:50,680 Lukas: Yeah. 64 00:02:50,740 --> 00:02:52,200 There are little details like this. 65 00:02:52,200 --> 00:02:55,640 So when you say autocomplete, there's also possibility to comment 66 00:02:55,640 --> 00:02:58,900 on your database tables and comments and that translates to Java 67 00:02:58,900 --> 00:02:59,400 doc. 68 00:02:59,440 --> 00:03:02,620 So whatever you comment directly in the database, it's translated 69 00:03:02,640 --> 00:03:06,500 to documentation inside of your job program you hardly ever have 70 00:03:06,500 --> 00:03:08,860 to go to the database to see what's there so. 71 00:03:10,680 --> 00:03:11,180 Michael: Nice. 72 00:03:11,480 --> 00:03:11,980 Lukas: Yeah. 73 00:03:12,560 --> 00:03:15,620 Michael: In terms of the history how like I did look it up in 74 00:03:15,620 --> 00:03:18,160 or at least the GitHub history How long have you been working 75 00:03:18,160 --> 00:03:19,780 on it and how does that feel? 76 00:03:20,580 --> 00:03:23,360 Lukas: I think the first public version started in 2009. 77 00:03:24,060 --> 00:03:28,580 There were prototypes before that in 2008 I think but 2009 was 78 00:03:28,580 --> 00:03:31,840 the first public version, not on GitHub then, it wasn't first 79 00:03:31,840 --> 00:03:32,340 port. 80 00:03:32,360 --> 00:03:34,900 I don't think GitHub was very popular at the time. 81 00:03:35,380 --> 00:03:36,240 And then Subversion. 82 00:03:37,360 --> 00:03:39,020 That's how old it is already. 83 00:03:39,780 --> 00:03:43,940 I had the idea already back during my university studies at EPFL 84 00:03:43,940 --> 00:03:44,600 in Lausanne. 85 00:03:45,180 --> 00:03:49,580 There was a professor who said you could model any kind of programming 86 00:03:49,640 --> 00:03:51,560 language as an internal DSL. 87 00:03:51,680 --> 00:03:53,660 I'm not sure if you use that term yet. 88 00:03:53,920 --> 00:03:56,180 I think DSL was more coined by... 89 00:03:57,180 --> 00:04:00,660 More recently, but he said you could model any language in terms 90 00:04:00,660 --> 00:04:04,340 of an API if you just have Java interfaces that return interfaces 91 00:04:04,500 --> 00:04:05,660 that are return interfaces. 92 00:04:05,820 --> 00:04:09,380 And this kind of idea stuck with me for all this time, but I 93 00:04:09,380 --> 00:04:11,080 didn't have a use case back then. 94 00:04:11,460 --> 00:04:16,320 And when I started working in the industry, in 2006 was my first 95 00:04:16,320 --> 00:04:17,180 Java job. 96 00:04:17,220 --> 00:04:20,440 I did some PHP work before, but Java started in 2006. 97 00:04:21,340 --> 00:04:25,080 I've seen that all the companies that I've applied to, and even 98 00:04:25,080 --> 00:04:29,700 the 1 that I worked at, they implemented dynamic SQL libraries 99 00:04:30,400 --> 00:04:32,900 because this was, this was something that everyone needs, right? 100 00:04:32,900 --> 00:04:34,980 So everyone has dynamic SQL queries. 101 00:04:35,020 --> 00:04:39,140 When you have a UI with various input boxes and they're all optional, 102 00:04:39,140 --> 00:04:41,980 so you have to compose your SQL queries in a dynamic way. 103 00:04:42,100 --> 00:04:44,860 And everyone wants to avoid string concatenation because it's 104 00:04:44,860 --> 00:04:46,220 not safe and it's boring. 105 00:04:46,560 --> 00:04:50,440 So they did that for both, they had one for SQL and they had one 106 00:04:50,440 --> 00:04:53,640 for Hibernate query language, which is essentially the same thing, 107 00:04:53,640 --> 00:04:56,100 just more limited, but at the same concept. 108 00:04:56,740 --> 00:05:00,840 And no one, no one actually thought about making this a true DSL. 109 00:05:00,860 --> 00:05:03,600 It's well, it was always just a query builder with some weird 110 00:05:03,600 --> 00:05:04,100 API. 111 00:05:04,120 --> 00:05:09,280 So you had Java style methods where you add clauses to the query 112 00:05:09,280 --> 00:05:09,740 object. 113 00:05:09,740 --> 00:05:12,480 And it didn't feel like SQL. 114 00:05:12,500 --> 00:05:16,180 It felt like a tool to solve exactly this dynamic SQL problem, 115 00:05:16,440 --> 00:05:19,940 but it didn't feel like you didn't enjoy using this thing. 116 00:05:19,940 --> 00:05:23,600 And I think JPA still has this thing with their criteria API, 117 00:05:23,600 --> 00:05:26,980 which is still very useful if you want to do dynamic JPQL. 118 00:05:27,700 --> 00:05:31,220 But I've never heard anyone enjoy using that API because it's 119 00:05:31,720 --> 00:05:33,740 just, you have to learn it. 120 00:05:33,740 --> 00:05:37,940 So you have to learn one more thing and it only serves this one purpose 121 00:05:37,960 --> 00:05:39,960 or at least that's how, what it feels like. 122 00:05:39,960 --> 00:05:42,460 It serves this purpose of dynamic JPQL. 123 00:05:43,780 --> 00:05:46,400 And that's when I started prototyping it. 124 00:05:46,600 --> 00:05:50,460 And in the very beginning, I had to first implement the infrastructure 125 00:05:50,580 --> 00:05:56,320 wasn't very diesel style API either so I created the query builder 126 00:05:56,320 --> 00:06:00,260 that could do some select from where very simple conditions But 127 00:06:00,260 --> 00:06:04,180 then I started to really implement this idea because I remembered, 128 00:06:04,180 --> 00:06:07,540 okay, this professor, he had this idea and I'm going to try this. 129 00:06:07,540 --> 00:06:08,600 Is it really possible? 130 00:06:09,520 --> 00:06:10,920 And it turns out it was. 131 00:06:10,920 --> 00:06:14,700 And it's really crazy how many SQL statements today we have in 132 00:06:14,700 --> 00:06:19,640 Juke that are all using this kind of approach where you just 133 00:06:19,640 --> 00:06:22,420 start with the same object and then you start typing and you 134 00:06:22,420 --> 00:06:27,440 auto complete your SQL syntax and it's all one huge graph of methods 135 00:06:27,440 --> 00:06:29,880 that helps you construct your SQL queries. 136 00:06:30,480 --> 00:06:33,720 So Juke has come a long way since 2009 I'd say. 137 00:06:34,340 --> 00:06:35,140 Michael: Yeah, right. 138 00:06:35,380 --> 00:06:39,060 I was looking in your documentation about how many different, 139 00:06:40,080 --> 00:06:44,060 how many different, you call them families of SQL dialects that 140 00:06:44,060 --> 00:06:44,680 you support. 141 00:06:44,680 --> 00:06:47,200 So how many different databases, but also the different versions 142 00:06:47,200 --> 00:06:50,180 of different databases that may or may not support different 143 00:06:50,660 --> 00:06:52,660 syntax and SQL features. 144 00:06:53,940 --> 00:06:55,820 How do you maintain that? 145 00:06:56,200 --> 00:06:59,140 Lukas: Well, first off, luckily the database products are all 146 00:06:59,140 --> 00:07:02,760 very, very backwards compatible, If we take one or two of them, we 147 00:07:02,760 --> 00:07:06,600 don't really care as much, but most of them really value backwards 148 00:07:06,600 --> 00:07:07,580 compatibility a lot. 149 00:07:07,580 --> 00:07:11,040 So supporting new versions is just looking at the new features 150 00:07:11,040 --> 00:07:13,980 and adding support for them, but the old stuff still works. 151 00:07:13,980 --> 00:07:16,860 So at least that's already covered there, more or less. 152 00:07:17,220 --> 00:07:20,600 But other than that, I mean, supporting so many dialect per se 153 00:07:20,600 --> 00:07:24,140 is, yeah, it's a lot of work and a lot of testing, of course, 154 00:07:24,140 --> 00:07:27,680 a lot of automated regression testing to make sure nothing breaks. 155 00:07:28,260 --> 00:07:31,960 And also a Juke really values backwards compatibility a lot, 156 00:07:31,960 --> 00:07:33,200 just like the database products. 157 00:07:33,200 --> 00:07:36,360 So when you embed the joke query into your database application, 158 00:07:36,880 --> 00:07:38,320 you don't want stuff to break. 159 00:07:38,320 --> 00:07:42,260 So if I'm not allowed to break stuff, I'm probably not breaking 160 00:07:42,260 --> 00:07:42,640 stuff. 161 00:07:42,640 --> 00:07:47,180 So this kind of simplifies maintenance as well, because stuff 162 00:07:47,180 --> 00:07:51,220 that once works is probably not going to break just like that. 163 00:07:51,220 --> 00:07:55,280 I mean there's some internal refactorings but there are no really 164 00:07:55,280 --> 00:08:00,800 major, how to put it, major paradigm shifts where everything 165 00:08:00,800 --> 00:08:04,120 works in a different way now and I was breaking everything. 166 00:08:04,120 --> 00:08:07,700 So with automated regression tests, it's not... 167 00:08:08,300 --> 00:08:10,480 It's hard, but it's not that hard, I think. 168 00:08:10,960 --> 00:08:15,540 The hard part is just to find out some weird syntax that implements 169 00:08:15,560 --> 00:08:18,220 a standard feature on this particular dialect. 170 00:08:19,040 --> 00:08:23,680 And that's quite creative at times, especially with the newer 171 00:08:23,680 --> 00:08:28,580 dialects that have just been released recently that are not that 172 00:08:28,580 --> 00:08:32,060 mature yet, or Some dialects are very opinionated. 173 00:08:32,320 --> 00:08:36,960 So you kind of have to think in terms of why did they make those 174 00:08:36,960 --> 00:08:40,360 decisions and how can I map standard SQL onto this database? 175 00:08:40,560 --> 00:08:43,580 But I think that's 1 of the things that people really like when 176 00:08:43,580 --> 00:08:46,520 they work, especially when they work with different database 177 00:08:46,520 --> 00:08:46,920 products. 178 00:08:46,920 --> 00:08:51,100 So if you have a company that works with maybe Postgres and SQL 179 00:08:51,100 --> 00:08:54,020 server, you don't actually have to think about the differences 180 00:08:54,020 --> 00:08:54,720 all the time. 181 00:08:54,720 --> 00:08:58,860 So you can just write substring in the jOOQ API and you don't 182 00:08:58,860 --> 00:09:02,440 care if it's subster or inster or whatever the database product 183 00:09:02,440 --> 00:09:03,260 calls it. 184 00:09:03,260 --> 00:09:06,760 It's just top string and jOOQ will translate it for you. 185 00:09:07,080 --> 00:09:09,100 Nikolay: But there are differences as well, right? 186 00:09:10,440 --> 00:09:12,260 And JSON is a big topic, right? 187 00:09:12,740 --> 00:09:13,480 Lukas: Like huge. 188 00:09:13,700 --> 00:09:14,200 JSON? 189 00:09:14,340 --> 00:09:14,840 Nikolay: Yeah. 190 00:09:15,320 --> 00:09:15,700 Yeah. 191 00:09:15,700 --> 00:09:17,250 Those functions and yeah. 192 00:09:17,250 --> 00:09:18,140 Lukas: It's crazy. 193 00:09:18,140 --> 00:09:21,360 I mean, Postgres was 1 of the first to actually support JSON, 194 00:09:21,380 --> 00:09:22,860 but it didn't actually 195 00:09:22,990 --> 00:09:24,120 Nikolay: before standardizing 196 00:09:25,080 --> 00:09:25,460 Lukas: stuff. 197 00:09:25,460 --> 00:09:28,860 So someone has standardized it, Oracle did a couple of years 198 00:09:28,860 --> 00:09:32,720 ago, and now Postgres started implementing those standards from 199 00:09:32,720 --> 00:09:33,280 what I've seen. 200 00:09:33,280 --> 00:09:38,360 I've still not engaged with that part yet, but you think now 201 00:09:38,360 --> 00:09:41,400 that there's a standard, things have stabilized, but still every 202 00:09:41,400 --> 00:09:42,980 Database product does it differently. 203 00:09:43,440 --> 00:09:44,140 It's crazy. 204 00:09:44,140 --> 00:09:45,560 The devil is in the details. 205 00:09:46,420 --> 00:09:46,920 Nikolay: Yeah. 206 00:09:47,440 --> 00:09:50,420 And have you, like, do you need sometimes to check the standard 207 00:09:50,420 --> 00:09:52,108 or like you don't need it at all? 208 00:09:52,108 --> 00:09:53,092 Lukas: I do it 209 00:09:53,092 --> 00:09:53,480 Nikolay: all the time. 210 00:09:53,480 --> 00:09:53,709 Lukas: Yeah. 211 00:09:53,709 --> 00:09:53,937 Okay. 212 00:09:53,937 --> 00:09:57,840 I'm trying to, the Juke API, if it's not about a vendor-specific 213 00:09:57,880 --> 00:10:00,840 feature, the Juke API really tries to follow the SQL standard 214 00:10:00,840 --> 00:10:01,860 in terms of syntax. 215 00:10:01,920 --> 00:10:06,040 So a vendor-specific feature would be Postgres on conflict clause, 216 00:10:06,320 --> 00:10:08,100 which I think was a mistake. 217 00:10:08,680 --> 00:10:11,780 They should have done merge from the beginning and not invent 218 00:10:11,780 --> 00:10:14,140 something new, but now we have both. 219 00:10:14,140 --> 00:10:18,440 And in that case, Juke also models the on-conflict part like 220 00:10:18,440 --> 00:10:22,340 Postgres did, and then SQLite copied it and a couple of others, 221 00:10:22,340 --> 00:10:22,980 I think. 222 00:10:23,560 --> 00:10:29,540 But for instance, merge is a standard SQL feature and many database 223 00:10:29,540 --> 00:10:33,480 products have some extensions which I then study, but I always 224 00:10:33,480 --> 00:10:34,740 first look at the standard. 225 00:10:34,760 --> 00:10:36,040 What does the standard do? 226 00:10:36,040 --> 00:10:38,940 Because that's a thing that's going to still be there in 50 years. 227 00:10:39,620 --> 00:10:42,680 And different implementations might have their quirks, which 228 00:10:43,060 --> 00:10:46,880 sometimes I hope they deprecate eventually and move towards the 229 00:10:46,880 --> 00:10:47,260 standard. 230 00:10:47,260 --> 00:10:49,680 Because usually I think the standard is quite nicely... 231 00:10:49,840 --> 00:10:53,940 Nikolay: Well, they behave differently, so many people got used 232 00:10:53,940 --> 00:10:54,640 to on conflict. 233 00:10:56,400 --> 00:10:58,840 Lukas: Yeah, I get the point of doing on conflict. 234 00:10:59,180 --> 00:11:03,140 I mean, it's simpler for 80% of the use cases, obviously. 235 00:11:03,400 --> 00:11:07,760 Nikolay: Yeah, also merge only recently received support of returning 236 00:11:08,040 --> 00:11:10,680 clause, which is not standard at all, right? 237 00:11:10,680 --> 00:11:11,500 As I remember. 238 00:11:11,580 --> 00:11:15,700 Lukas: The SQL standard has the table, the data changed out of 239 00:11:15,700 --> 00:11:18,200 the table, which was implemented by DB2. 240 00:11:19,540 --> 00:11:22,340 And then some minor database products like H2 implemented as 241 00:11:22,340 --> 00:11:22,840 well. 242 00:11:23,360 --> 00:11:26,880 And it's not as powerful as returning, especially because it 243 00:11:26,880 --> 00:11:32,420 only allows you to fetch either the data before or after the 244 00:11:32,420 --> 00:11:36,140 updates for update statements, which is also part of merge. 245 00:11:36,280 --> 00:11:39,680 You maybe want to return both versions to the version before 246 00:11:39,680 --> 00:11:41,020 and after the update. 247 00:11:41,880 --> 00:11:44,700 With returning in principle, that's, that's a feasible. 248 00:11:44,700 --> 00:11:46,800 So Oracle implemented this recently. 249 00:11:47,280 --> 00:11:48,680 I'm not in follow. 250 00:11:48,680 --> 00:11:54,140 Did Postgres allow for accessing both versions of the row? 251 00:11:54,140 --> 00:11:55,520 Nikolay: I don't remember as well. 252 00:11:55,520 --> 00:12:00,360 I'm only like returning, well, if you DELETE, definitely you 253 00:12:00,360 --> 00:12:03,580 have ability to return old thing you deleted. 254 00:12:03,600 --> 00:12:05,140 Lukas: Well, there's only the old thing. 255 00:12:05,340 --> 00:12:06,360 Old thing is deleted. 256 00:12:06,360 --> 00:12:07,380 Nikolay: Let me quickly check. 257 00:12:07,800 --> 00:12:08,160 Lukas: Why not? 258 00:12:08,160 --> 00:12:12,840 But with updates, I mean, SQL Server always supported both versions. 259 00:12:12,900 --> 00:12:14,160 So before and after. 260 00:12:15,220 --> 00:12:19,200 And DB2 with the standard syntax supports only, you have to choose 261 00:12:19,200 --> 00:12:21,180 which one, but you can't return both. 262 00:12:21,180 --> 00:12:25,460 And Oracle 23 AI now supports both as well, like SQL Server. 263 00:12:26,040 --> 00:12:29,380 Oracle uses the Postgres syntax, the non-standard syntax also 264 00:12:29,380 --> 00:12:33,300 with the returning keyword, so maybe you can start from there 265 00:12:34,300 --> 00:12:36,680 if you don't do it yet in Postgres. 266 00:12:36,980 --> 00:12:41,920 Nikolay: I think in Postgres for updates we cannot return old 267 00:12:41,920 --> 00:12:42,420 data. 268 00:12:42,740 --> 00:12:45,780 We have access to it in triggers but it's different, right? 269 00:12:47,780 --> 00:12:53,200 So only the new value can be seen there, but for deletes, it's 270 00:12:53,200 --> 00:12:54,260 Lukas: probably more useful. 271 00:12:54,620 --> 00:12:57,560 Nikolay: Well, I can imagine some cases when we want to report, 272 00:12:57,560 --> 00:12:59,680 for example, what happened. 273 00:13:00,100 --> 00:13:00,540 Lukas: Yeah. 274 00:13:00,540 --> 00:13:02,860 When you do auditing, you want to have both. 275 00:13:03,280 --> 00:13:03,780 Nikolay: Yeah. 276 00:13:03,820 --> 00:13:04,640 Maybe let's add it. 277 00:13:04,640 --> 00:13:04,840 Because 278 00:13:04,840 --> 00:13:09,160 Lukas: standard supports this data change delta table, and it 279 00:13:09,160 --> 00:13:10,760 accepts a merge statement as well. 280 00:13:10,760 --> 00:13:14,640 So in principle, in the standard, you could have returning with 281 00:13:14,640 --> 00:13:15,180 a different. 282 00:13:15,180 --> 00:13:19,400 Nikolay: But it feels like more like a heavier approach in terms 283 00:13:19,400 --> 00:13:20,280 of manipulation. 284 00:13:20,640 --> 00:13:22,620 Returning is just like one line, right? 285 00:13:22,660 --> 00:13:24,780 Return a star, that's it. 286 00:13:24,960 --> 00:13:25,600 Lukas: Yes, yes. 287 00:13:25,600 --> 00:13:26,420 Super easy. 288 00:13:27,260 --> 00:13:28,760 It seems easier, yeah. 289 00:13:29,600 --> 00:13:31,260 Also, there are tons of limitations. 290 00:13:31,640 --> 00:13:35,720 I mean, you put your data change delta table inside of a SELECT 291 00:13:35,720 --> 00:13:38,740 query, but then you can hardly do anything with that SELECT query 292 00:13:38,760 --> 00:13:42,260 for instance, I'm not sure what exactly what is forbidden, but 293 00:13:42,260 --> 00:13:45,900 unions I think are forbidden and joins are forbidden, so maybe 294 00:13:45,900 --> 00:13:46,600 even aggregations. 295 00:13:46,780 --> 00:13:49,920 I'm not sure anymore, but you're very limited with what you can 296 00:13:49,920 --> 00:13:50,140 do. 297 00:13:50,140 --> 00:13:53,320 So I'm not sure if there's any benefit with allowing that in 298 00:13:53,320 --> 00:13:55,620 the SELECT statement as opposed to just returning. 299 00:13:56,760 --> 00:13:59,620 But ultimately it's kind of the same thing as what Postgres does 300 00:13:59,620 --> 00:14:02,440 when you put the ML statements in, in with. 301 00:14:02,940 --> 00:14:06,780 So that that kind of behaves the same way, at least from a user 302 00:14:06,820 --> 00:14:07,320 perspective. 303 00:14:07,760 --> 00:14:10,160 Nikolay: Speaking of with, I know Juke supports CTEs. 304 00:14:10,440 --> 00:14:12,060 Does it support recursive CTEs? 305 00:14:13,040 --> 00:14:13,780 Yeah, yeah. 306 00:14:14,060 --> 00:14:14,560 Yeah. 307 00:14:15,060 --> 00:14:17,820 So everything, lateral join and so on, everything. 308 00:14:18,040 --> 00:14:18,540 Yeah. 309 00:14:18,900 --> 00:14:19,300 Cool. 310 00:14:19,300 --> 00:14:22,500 Lukas: There's a high chance if SQL supports something and it's 311 00:14:22,500 --> 00:14:26,280 not really very funky like the match_recognize clause, then Juke 312 00:14:26,280 --> 00:14:27,340 will support it as well. 313 00:14:27,340 --> 00:14:30,900 So match_recognize could be supported as well in Juke, but it's 314 00:14:30,900 --> 00:14:35,180 such an edge case and still only supported in Oracle and some 315 00:14:35,180 --> 00:14:38,220 very esoteric database products that Juke doesn't support yet. 316 00:14:38,220 --> 00:14:42,480 So, so I'm skipping this for now, but with is, is everyone uses 317 00:14:42,480 --> 00:14:48,340 with, so Juke supported as well, including recursive with, I 318 00:14:48,340 --> 00:14:51,360 mean, there's some table valued functions that are like generate 319 00:14:51,360 --> 00:14:54,740 series in Postgres, which have to be emulated elsewhere. 320 00:14:54,760 --> 00:14:58,580 So if you want to ever, probably hardly anyone does that migrate 321 00:14:58,580 --> 00:15:02,580 from Postgres to whatever, and you want to translate your old 322 00:15:02,580 --> 00:15:06,000 generate series queries, then Juke will translate that to a recursive 323 00:15:06,000 --> 00:15:06,500 with. 324 00:15:06,880 --> 00:15:07,380 Michael: Cool. 325 00:15:07,540 --> 00:15:08,040 Nice. 326 00:15:08,180 --> 00:15:08,680 Nice. 327 00:15:10,080 --> 00:15:12,440 In fact, I feel like you skipped over that a little bit, but 328 00:15:12,440 --> 00:15:15,220 that's such a cool feature that basically... 329 00:15:15,360 --> 00:15:15,860 Translation? 330 00:15:16,380 --> 00:15:17,140 Yeah, exactly. 331 00:15:17,320 --> 00:15:23,460 We can get access to standard features that Postgres hasn't implemented, 332 00:15:24,160 --> 00:15:27,100 or that whatever database we're using hasn't implemented yet 333 00:15:27,100 --> 00:15:29,980 because you do the transformation, I guess. 334 00:15:30,120 --> 00:15:32,060 How do you, translation did you say? 335 00:15:32,120 --> 00:15:33,780 Lukas: Yeah, I call it translation. 336 00:15:33,900 --> 00:15:37,580 So there's even, you can use Juke as a purely translate product. 337 00:15:37,640 --> 00:15:39,440 So some customers actually do that. 338 00:15:39,480 --> 00:15:42,840 Mostly when they migrate from Oracle to Postgres, you can just 339 00:15:42,840 --> 00:15:46,120 embed Juke as a JDBC driver into, into your Java application. 340 00:15:46,120 --> 00:15:48,180 And you don't actually have to use Juke directly. 341 00:15:48,420 --> 00:15:52,420 So let's say this is a legacy application and uses JDBC or something 342 00:15:52,420 --> 00:15:56,180 JDBC based like Hibernate or MyBatis, and you just put Juke 343 00:15:56,180 --> 00:16:00,240 in the middle as a translating layer and probably 80% of your 344 00:16:00,240 --> 00:16:02,460 queries will work directly on Postgres. 345 00:16:02,680 --> 00:16:05,660 Of course, there's always edge cases and you have to do testing, 346 00:16:05,740 --> 00:16:09,140 but at least your migration project will go much, much faster 347 00:16:09,140 --> 00:16:13,180 because you can directly use Oracle queries on those other database 348 00:16:13,180 --> 00:16:13,680 products. 349 00:16:13,860 --> 00:16:17,220 Like for instance, Oracle has connect by and that can be translated 350 00:16:17,680 --> 00:16:19,340 to with queries with recursive. 351 00:16:19,820 --> 00:16:20,660 That's very hard. 352 00:16:20,660 --> 00:16:24,280 So some cases don't work, but maybe many cases do. 353 00:16:24,280 --> 00:16:26,980 And you don't have to actually look at those anymore. 354 00:16:27,340 --> 00:16:31,400 Or Oracle special outer join syntax can be translated to left 355 00:16:31,400 --> 00:16:33,680 joins in, in, in standard SQL. 356 00:16:33,680 --> 00:16:36,720 So your migration just goes much faster. 357 00:16:37,260 --> 00:16:40,440 You can also use it not as an embedded library, but just as the 358 00:16:40,440 --> 00:16:44,540 web, the website is free or you use it locally as a CLI tool, 359 00:16:44,540 --> 00:16:47,860 which is to have files, you have input files and Juke translates 360 00:16:47,880 --> 00:16:49,020 those directly. 361 00:16:49,060 --> 00:16:51,420 And you can manually check if it's correct. 362 00:16:51,820 --> 00:16:54,840 So translation is a, is a, I'd say minor use case. 363 00:16:54,840 --> 00:16:59,440 So a lot of companies work with only 1 database product and maybe 364 00:16:59,720 --> 00:17:03,740 10 years, they, they might reconsider their choices and migrate. 365 00:17:03,740 --> 00:17:07,120 But even then most, most companies stick with the database product 366 00:17:07,120 --> 00:17:08,200 they started with. 367 00:17:09,060 --> 00:17:12,840 And so many Juke users don't actually use this feature, but some 368 00:17:12,840 --> 00:17:13,340 do. 369 00:17:14,340 --> 00:17:17,740 Nikolay: May I ask slightly off topic question about JDBC since 370 00:17:17,740 --> 00:17:19,940 you spent a lot of time with Java, obviously. 371 00:17:20,740 --> 00:17:22,780 There is such thing as extra float digits. 372 00:17:23,560 --> 00:17:24,060 Right? 373 00:17:24,140 --> 00:17:25,020 Lukas: Extra what? 374 00:17:25,280 --> 00:17:26,760 Nikolay: Extra float digits. 375 00:17:27,340 --> 00:17:27,840 Okay. 376 00:17:27,980 --> 00:17:30,840 There is such setting, and by default it's 0 in Postgres. 377 00:17:32,520 --> 00:17:33,180 Or 1. 378 00:17:33,180 --> 00:17:34,020 Yeah, no, 1. 379 00:17:34,020 --> 00:17:34,740 It's 1. 380 00:17:34,900 --> 00:17:36,300 Lukas: I've never heard of it. 381 00:17:36,600 --> 00:17:42,240 Nikolay: Okay, but yeah, I remember some bugs in my code, and 382 00:17:42,240 --> 00:17:48,820 I usually try to use pure SQL and psql, and you know, if I can. 383 00:17:49,400 --> 00:17:53,520 I Remember that Java developers implemented the very same thing, 384 00:17:53,520 --> 00:17:55,820 same queries, behavior was very different. 385 00:17:56,400 --> 00:18:01,020 And then I noticed that the DBeaver, who else, other Java-based 386 00:18:01,840 --> 00:18:05,880 IDEs also behaved the same way Java application did. 387 00:18:06,400 --> 00:18:09,600 It was not the same as I saw in psql. 388 00:18:10,240 --> 00:18:12,480 It happened with these extra float digits. 389 00:18:12,700 --> 00:18:16,080 If you didn't notice this, this is maybe some very edge corner 390 00:18:16,080 --> 00:18:16,580 case. 391 00:18:17,080 --> 00:18:22,700 But I wonder like, why did the JDBC keep different default is 392 00:18:22,700 --> 00:18:24,620 like no layers of some defaults. 393 00:18:25,080 --> 00:18:26,780 This can be very annoying. 394 00:18:27,160 --> 00:18:28,020 But okay. 395 00:18:28,140 --> 00:18:31,680 Lukas: Well, the main thing that you always see The main difference 396 00:18:31,680 --> 00:18:37,440 when you work with JDBC is, as opposed to psql is, in psql you 397 00:18:37,440 --> 00:18:40,380 usually don't work with bind variables, so you don't have the 398 00:18:40,380 --> 00:18:43,660 whole kind of effect that you get when you have bind variables, 399 00:18:43,660 --> 00:18:47,240 although in Postgres This might not be that big of a difference, 400 00:18:47,240 --> 00:18:50,880 but when you work with JDBC, you always have prepared statements 401 00:18:52,360 --> 00:18:53,600 and bind variables. 402 00:18:53,600 --> 00:18:57,080 And let's say my background is mostly Oracle. 403 00:18:57,080 --> 00:19:00,800 So with bind variables, you have an execution plan cache and 404 00:19:00,800 --> 00:19:04,020 the cache will store some assumptions about your bind variables, 405 00:19:04,020 --> 00:19:08,220 like a normalized distribution and expectation regarding cardinalities, 406 00:19:08,560 --> 00:19:09,180 et cetera. 407 00:19:09,380 --> 00:19:12,700 So your actual bind variable might be, if your dataset is skewed 408 00:19:12,700 --> 00:19:15,280 and your actual bind variable may be completely different from 409 00:19:15,280 --> 00:19:16,180 what Oracle expects. 410 00:19:16,180 --> 00:19:18,780 And, and then the execution plan will be bad. 411 00:19:19,060 --> 00:19:21,660 But from what I take, Postgres doesn't have this problem because 412 00:19:21,660 --> 00:19:22,860 it doesn't have this feature. 413 00:19:24,720 --> 00:19:26,740 So this would be 1 of the main differences. 414 00:19:27,740 --> 00:19:30,680 But you were referring to some logical error, not... 415 00:19:31,420 --> 00:19:34,900 Nikolay: Yeah, Default and values returned are different from 416 00:19:34,900 --> 00:19:36,500 what I see in PC. 417 00:19:36,500 --> 00:19:36,820 Yeah, but 418 00:19:36,820 --> 00:19:38,220 Lukas: I've never heard of this. 419 00:19:38,480 --> 00:19:41,040 Nikolay: Well, okay, maybe it's already fixed by the way. 420 00:19:41,040 --> 00:19:43,760 It was like 5 years ago or so I saw it. 421 00:19:43,940 --> 00:19:44,840 So, yeah. 422 00:19:45,820 --> 00:19:47,460 Well, on the topic of JDBC, 423 00:19:47,720 --> 00:19:50,140 Michael: I mean, do you have any issues with it? 424 00:19:50,140 --> 00:19:52,840 It seems like it's a remarkable piece of software that's just 425 00:19:52,840 --> 00:19:55,840 chugging along, but equally it doesn't get much love. 426 00:19:55,840 --> 00:19:57,040 It doesn't get much love. 427 00:19:57,040 --> 00:20:00,040 Like I only, I generally only hear negative things about it, 428 00:20:00,040 --> 00:20:03,220 but it must be amazing given how much it's used. 429 00:20:03,600 --> 00:20:06,900 Lukas: I think it's the best API that Java has, even better than 430 00:20:06,900 --> 00:20:08,500 collections and everything else. 431 00:20:08,500 --> 00:20:12,400 It's really, I mean, okay, maybe they stole it from Microsoft 432 00:20:12,400 --> 00:20:15,840 from ODBC, it's kind of the same thing, but It's really the best 433 00:20:15,840 --> 00:20:18,460 thing that Java has because it's such a good standard. 434 00:20:18,560 --> 00:20:22,320 I mean, everything can build on top of it and you don't even 435 00:20:22,320 --> 00:20:23,440 have to write SQL queries. 436 00:20:23,440 --> 00:20:26,760 So there's, for instance, Neo4j, which is a NoSQL database with 437 00:20:26,760 --> 00:20:30,020 their own query language and you can use JDBC with that database 438 00:20:30,020 --> 00:20:34,080 as well as long as you wanna have some tabular results And it's 439 00:20:34,080 --> 00:20:37,580 a very, very good abstraction of the network protocol layer. 440 00:20:37,900 --> 00:20:43,120 And the reason why people hate it, it's not targeted at developers. 441 00:20:43,260 --> 00:20:47,040 So you can use it directly if you want, But it's a bit hard to 442 00:20:47,040 --> 00:20:47,220 use. 443 00:20:47,220 --> 00:20:48,340 You have a lot of objects. 444 00:20:48,340 --> 00:20:50,940 You have the connection object and you have to create a statement 445 00:20:50,940 --> 00:20:53,200 object and you have to remember to close it. 446 00:20:53,200 --> 00:20:56,380 And you have to remember to create a result set object and close 447 00:20:56,380 --> 00:20:57,260 that as well. 448 00:20:57,660 --> 00:21:00,700 And it's not integrated with the rest of the JDK libraries. 449 00:21:00,700 --> 00:21:03,240 Like for instance, when you iterate the result set, you have 450 00:21:03,240 --> 00:21:06,040 your own API methods instead of just a list or iterable. 451 00:21:06,040 --> 00:21:09,640 So you can't use the for each loop on a result set, for instance. 452 00:21:09,960 --> 00:21:13,340 So the ergonomics of JDBC is not, not up to date. 453 00:21:13,340 --> 00:21:18,000 I once tried to convince the spec expert group, it's a mailing 454 00:21:18,000 --> 00:21:20,500 list to, to update their API. 455 00:21:20,740 --> 00:21:24,600 There's a couple of things that I think in, in at the time it 456 00:21:24,600 --> 00:21:27,880 was Java 8, but even now you could improve a couple of things 457 00:21:27,880 --> 00:21:30,460 without changing anything fundamental in JDBC. 458 00:21:30,860 --> 00:21:32,900 For instance, you can skip the statement part. 459 00:21:32,900 --> 00:21:35,700 A lot of times you don't actually have to think about preparing 460 00:21:35,740 --> 00:21:37,040 the statement explicitly. 461 00:21:37,660 --> 00:21:40,580 You have to do it when you want to reuse the prepared statement. 462 00:21:40,940 --> 00:21:44,860 So you can save some time in case there's a resource or some 463 00:21:44,860 --> 00:21:46,160 cache connection directly. 464 00:21:46,860 --> 00:21:50,380 But a lot of times that's not the case in some database products 465 00:21:50,380 --> 00:21:52,540 and a lot of times it doesn't even matter at all. 466 00:21:52,540 --> 00:21:55,100 So you could just have a connection and run the query. 467 00:21:55,640 --> 00:22:00,060 So that's a minor improvement that would just reduce 1 step for 468 00:22:00,060 --> 00:22:01,520 each statement you write. 469 00:22:02,080 --> 00:22:06,760 Yeah, but Oracle couldn't allocate any resources to such a project, 470 00:22:06,760 --> 00:22:11,300 even if the people on the list agreed that many ideas were reasonable. 471 00:22:12,180 --> 00:22:13,260 So I guess that's it. 472 00:22:13,260 --> 00:22:16,340 It's an amazing piece of software for integration products like, 473 00:22:16,340 --> 00:22:20,080 like Juke or Hibernate or MyBatis and everything else to build 474 00:22:20,080 --> 00:22:22,680 on top of and all the drivers they just work. 475 00:22:22,680 --> 00:22:27,360 So a database company can just publish a driver and it will work 476 00:22:27,360 --> 00:22:28,680 in all kind of software. 477 00:22:29,020 --> 00:22:31,840 For instance, if you, if you work with DBeaver, it just works with 478 00:22:31,840 --> 00:22:33,160 all the database products, right? 479 00:22:33,160 --> 00:22:36,260 So you don't have to think about how do you, how do I connect 480 00:22:36,260 --> 00:22:40,120 to this new database thing, like the cows or, or DuckDB or whatever, 481 00:22:40,200 --> 00:22:41,100 it just works. 482 00:22:41,520 --> 00:22:43,860 All these database products, they just work because they all 483 00:22:43,860 --> 00:22:46,000 use the same API in Java. 484 00:22:46,000 --> 00:22:50,380 So it's really a wonderful piece of technology, I think. 485 00:22:50,380 --> 00:22:51,440 So I totally agree. 486 00:22:51,880 --> 00:22:55,660 Nikolay: Yeah, this is exactly where I saw this problem, comparing 487 00:22:55,760 --> 00:22:58,680 to, I mean in DBeaver, comparing to Java. 488 00:22:58,680 --> 00:23:02,180 And I realized, okay, it's also Java application, so yeah. 489 00:23:02,880 --> 00:23:03,380 Good. 490 00:23:04,280 --> 00:23:06,460 Lukas: Yeah, but that was probably just the driver bug. 491 00:23:06,460 --> 00:23:09,900 I don't think it has to do with the database as an API. 492 00:23:10,520 --> 00:23:12,740 I mean, obviously the drivers can have bugs. 493 00:23:13,820 --> 00:23:14,440 Nikolay: Right, right. 494 00:23:15,360 --> 00:23:16,760 So You see bugs often, right? 495 00:23:16,760 --> 00:23:18,940 I mean, in this... 496 00:23:18,940 --> 00:23:20,680 Lukas: Yeah, not necessarily in the drivers. 497 00:23:20,680 --> 00:23:23,480 I mean, the drivers, they really solve a very basic problem. 498 00:23:23,480 --> 00:23:26,820 So it's mostly about network connections and these kind of things 499 00:23:26,820 --> 00:23:31,200 that no 1 actually cares about, unless you have to optimize something, 500 00:23:31,500 --> 00:23:33,360 like multiplexing or whatever. 501 00:23:33,360 --> 00:23:36,080 But I think this stuff kind of just works. 502 00:23:36,140 --> 00:23:39,060 And when I find bugs, it's more SQL related. 503 00:23:39,140 --> 00:23:43,620 So it's inside of the database and I do find a ton of them very 504 00:23:43,620 --> 00:23:44,640 rarely in Postgres. 505 00:23:45,060 --> 00:23:49,140 I have to say, I think in my career I found 2 or 3 bugs in Postgres 506 00:23:49,720 --> 00:23:53,940 only, as opposed to hundreds. 507 00:23:54,520 --> 00:23:56,140 Michael: We didn't pay you to say that. 508 00:23:56,400 --> 00:23:56,900 Lukas: No. 509 00:23:57,800 --> 00:23:59,060 It's really very good. 510 00:23:59,060 --> 00:24:00,000 It's surprisingly good. 511 00:24:00,000 --> 00:24:03,820 I mean, also, absolutely no regressions at all. 512 00:24:03,820 --> 00:24:06,580 So if there's a bug, it's an edge case that no one thought about, 513 00:24:06,580 --> 00:24:09,180 then it's really a very weird kind of thing. 514 00:24:10,080 --> 00:24:10,580 Nikolay: Yeah. 515 00:24:10,680 --> 00:24:12,280 By the way, how do you check regression? 516 00:24:12,440 --> 00:24:14,280 Just conversion from... 517 00:24:15,280 --> 00:24:16,500 Lukas: In the database products? 518 00:24:17,280 --> 00:24:17,780 Nikolay: Yeah. 519 00:24:17,780 --> 00:24:21,980 For Juke, for example, some code is written and then you just 520 00:24:21,980 --> 00:24:25,660 check the conversion to SQL or you check results as well. 521 00:24:26,040 --> 00:24:29,240 Lukas: No, you can't just search the SQL statement. 522 00:24:29,240 --> 00:24:33,960 I mean, Juke's integration tasks have a standard database, which 523 00:24:33,960 --> 00:24:36,200 has about 5 tables. 524 00:24:36,200 --> 00:24:37,120 It's very simple. 525 00:24:37,120 --> 00:24:40,680 So there's a bookstore with books and authors, and it's not a 526 00:24:40,680 --> 00:24:41,940 big database. 527 00:24:41,940 --> 00:24:46,780 So it has 4 books and 2 authors, 4 languages, this kind of thing. 528 00:24:47,080 --> 00:24:50,600 But then I just know there are 4 books and they have these titles 529 00:24:50,600 --> 00:24:54,400 and every kind of query has to return exactly the same result. 530 00:24:54,400 --> 00:24:59,440 So there are so many queries being run in each database product, 531 00:25:00,040 --> 00:25:03,520 maybe about 10, 000 queries, I think, and also update statements 532 00:25:03,520 --> 00:25:07,160 and all kinds of statements that make the assumption that if 533 00:25:07,160 --> 00:25:10,320 you have this kind of input database, then this must be the output. 534 00:25:10,320 --> 00:25:14,620 Or also, if I create a table with Juke, then it has to have these 535 00:25:14,620 --> 00:25:18,480 properties and throughout the API, it has to be the same thing 536 00:25:18,480 --> 00:25:19,840 for every database product. 537 00:25:20,320 --> 00:25:20,820 Nikolay: Right. 538 00:25:21,040 --> 00:25:24,900 I just wonder, earlier you mentioned that usually database systems 539 00:25:25,080 --> 00:25:28,420 maintain reverse compatibility, but sometimes it's broken. 540 00:25:28,420 --> 00:25:33,620 For example, in Postgres 12, CTEs, before they were materialized, 541 00:25:33,840 --> 00:25:35,520 like optimization fans. 542 00:25:35,580 --> 00:25:40,240 So every step is materialized before 12, but in 12 default behavior 543 00:25:40,240 --> 00:25:40,740 changed. 544 00:25:41,440 --> 00:25:41,940 Right. 545 00:25:42,120 --> 00:25:46,320 So, so is it something that Juke should care about or no? 546 00:25:46,800 --> 00:25:49,800 Lukas: I don't think that's a logical regression. 547 00:25:49,960 --> 00:25:53,260 I mean, it was a conscious decision and it's only affects 548 00:25:53,300 --> 00:25:54,400 the performance. 549 00:25:56,200 --> 00:25:58,360 There's no logical difference of the result. 550 00:25:58,400 --> 00:26:02,440 So I mean, databases are allowed to do these kinds of things. 551 00:26:02,980 --> 00:26:07,280 I re I recall there was once a logical change in the UPDATE statement 552 00:26:07,280 --> 00:26:11,980 in Postgres, which was a incompatible change when you UPDATE 553 00:26:12,040 --> 00:26:12,740 a row. 554 00:26:12,740 --> 00:26:15,860 So you had used the row syntax, you use parentheses And you UPDATE 555 00:26:15,860 --> 00:26:20,020 2 Columns at once and suddenly both required the row keyword, 556 00:26:20,020 --> 00:26:21,140 but only there. 557 00:26:21,500 --> 00:26:23,040 This was a very weird. 558 00:26:23,040 --> 00:26:27,160 Nikolay: 1 more recently, 1 more change related to subqueries. 559 00:26:27,240 --> 00:26:28,440 I don't remember exactly. 560 00:26:30,180 --> 00:26:33,660 It was a demonstration included in the generate series, I remember. 561 00:26:34,300 --> 00:26:37,840 And yeah, maybe it was like up to 5 years ago, but there was 562 00:26:37,840 --> 00:26:40,460 some change which was logical as well. 563 00:26:40,840 --> 00:26:44,100 But I understand, you're like SQL standard, don't care about 564 00:26:44,100 --> 00:26:46,300 Indexes, care only about results. 565 00:26:46,300 --> 00:26:46,800 Yeah. 566 00:26:46,880 --> 00:26:48,440 Right, I see, I see. 567 00:26:49,200 --> 00:26:52,200 Lukas: No, I mean, a juke doesn't make any guarantees at all 568 00:26:52,200 --> 00:26:53,800 with respect to performance either. 569 00:26:53,800 --> 00:26:56,760 So, so from a Juke perspective, Juke doesn't try to fix your 570 00:26:56,760 --> 00:26:57,040 SQL. 571 00:26:57,040 --> 00:26:59,880 If you write a really crap query with, I mean, crap, what does 572 00:26:59,880 --> 00:27:00,480 it even mean? 573 00:27:00,480 --> 00:27:04,700 So if you write a bad query with a lot of nested subqueries, 574 00:27:04,920 --> 00:27:07,400 et cetera, et cetera, in principle, there's nothing wrong with 575 00:27:07,400 --> 00:27:07,560 it. 576 00:27:07,560 --> 00:27:07,900 Right? 577 00:27:07,900 --> 00:27:11,540 So there's, you will find hundreds of blog posts that, especially 578 00:27:11,580 --> 00:27:15,240 from, from old times when optimizers were really bad still, where 579 00:27:15,240 --> 00:27:18,480 people advised against using derived tables, then they advised 580 00:27:18,480 --> 00:27:21,780 against using correlated subqueries, and then, but there's nothing 581 00:27:21,780 --> 00:27:23,940 logically wrong with these things, right? 582 00:27:23,940 --> 00:27:27,720 So you can nest as many levels as you want, as long as it's correct, 583 00:27:27,720 --> 00:27:30,040 it's correct, and the optimizer should figure it out. 584 00:27:30,280 --> 00:27:33,960 And Who is Juke to judge you for your query style? 585 00:27:34,440 --> 00:27:38,000 So Juke doesn't fix these things or even assert anything, and 586 00:27:38,000 --> 00:27:40,680 it would be completely wrong for Juke to do these kind of things, 587 00:27:40,680 --> 00:27:43,480 because you want to write exactly this SQL, and Juke should render 588 00:27:43,480 --> 00:27:44,700 exactly this SQL. 589 00:27:45,020 --> 00:27:46,360 You have your reasons, right? 590 00:27:46,360 --> 00:27:46,860 So... 591 00:27:47,360 --> 00:27:48,020 Nikolay: I understand. 592 00:27:48,960 --> 00:27:55,260 And my question is, I'm curious, how do people usually approach 593 00:27:55,280 --> 00:27:55,780 performance? 594 00:27:56,640 --> 00:28:01,500 For example, if some query is written using Juke with a chain 595 00:28:01,500 --> 00:28:06,360 of SELECTs from blah blah, And then it turns out it's slow. 596 00:28:07,360 --> 00:28:11,040 And then we find the fix, and we need to adjust something in 597 00:28:11,040 --> 00:28:13,540 the query if it was a rock SQL. 598 00:28:13,820 --> 00:28:17,000 Is it usually simple to adjust a Juke version of. 599 00:28:17,540 --> 00:28:17,960 Lukas: Yeah. 600 00:28:17,960 --> 00:28:19,200 I mean, there's no difference. 601 00:28:19,200 --> 00:28:24,140 I mean, you're probably going to use the, I'm not, I'm not sure 602 00:28:24,140 --> 00:28:26,660 anymore what the Postgres version is called, but you have your 603 00:28:26,660 --> 00:28:29,540 statistics views in your database, your performance schema, and 604 00:28:29,540 --> 00:28:33,220 you query those to see what's, what went wrong and then you make 605 00:28:33,220 --> 00:28:36,180 assumptions, you rewrite the query and you test again. 606 00:28:36,700 --> 00:28:40,240 Maybe even using a benchmark, of course then the benchmark is 607 00:28:40,240 --> 00:28:42,940 written in Java, but I don't see a difference when you write 608 00:28:42,940 --> 00:28:46,140 jOOQ query or native SQL query, It's the same process. 609 00:28:46,240 --> 00:28:49,860 Michael: I was watching a really good video by Kevin Davin, who 610 00:28:49,860 --> 00:28:52,900 did a talk on jOOQ, but 1 of the things that he really liked 611 00:28:52,900 --> 00:28:57,640 about the product was it logs out to the console, to the actual 612 00:28:57,720 --> 00:28:59,700 SQL statement as well that it ran. 613 00:29:00,040 --> 00:29:02,060 Lukas: When you do a debug level. 614 00:29:02,440 --> 00:29:04,600 Michael: Yeah, which is super helpful for performance. 615 00:29:04,600 --> 00:29:07,700 So if that was slow, you can then run it in your favorite editor 616 00:29:07,700 --> 00:29:11,740 in PSQL wherever with EXPLAIN, ANALYZE, et cetera, et cetera. 617 00:29:11,740 --> 00:29:12,240 BUFFERS. 618 00:29:12,740 --> 00:29:15,520 Yeah, always BUFFERS, hot topic this week. 619 00:29:15,660 --> 00:29:20,660 But yeah, so that seems like a really developer friendly way 620 00:29:20,660 --> 00:29:24,360 of getting back to the SQL and then diagnosing the performance 621 00:29:24,360 --> 00:29:24,860 issue. 622 00:29:24,960 --> 00:29:26,600 Lukas: This was a feature from day 1. 623 00:29:26,600 --> 00:29:29,480 My assumption was that when you develop, you have debug mode 624 00:29:29,480 --> 00:29:29,980 logging. 625 00:29:30,060 --> 00:29:33,540 And when you go to production, you switch that to info or even 626 00:29:33,540 --> 00:29:34,040 warning. 627 00:29:34,200 --> 00:29:37,320 So you're not going to have the performance penalty of re-rendering 628 00:29:37,440 --> 00:29:37,900 the query. 629 00:29:37,900 --> 00:29:40,840 It's even formatted, so you can actually see the query. 630 00:29:40,840 --> 00:29:44,640 It's not just 1 line, which is when you execute the query, it's 631 00:29:44,640 --> 00:29:47,820 just 1 line of SQL string, so to have it more compact. 632 00:29:47,880 --> 00:29:51,940 But when you debug log it, the version contains the bind variables. 633 00:29:51,960 --> 00:29:54,860 So you can just copy paste the whole query with the bind variables 634 00:29:54,860 --> 00:29:56,980 in a formatted way and study it. 635 00:29:57,540 --> 00:29:59,680 I thought that was what everyone wanted. 636 00:29:59,680 --> 00:30:03,240 And Also you get the first 5 records of the result set also in 637 00:30:03,240 --> 00:30:06,180 the debug log, because that's probably also what you want while 638 00:30:06,180 --> 00:30:07,540 you develop at least. 639 00:30:07,660 --> 00:30:07,940 Michael: Yeah. 640 00:30:07,940 --> 00:30:09,400 In a pretty little table. 641 00:30:09,400 --> 00:30:10,120 Nikolay: It's nice. 642 00:30:10,600 --> 00:30:12,900 Lukas: So you can immediately see what's going on. 643 00:30:13,080 --> 00:30:16,260 And even then, I mean, 1 of the things, 1 of the reasons why 644 00:30:16,260 --> 00:30:19,200 I implemented it this way is when you have a huge result set 645 00:30:19,200 --> 00:30:22,620 with a lot of columns, that bothers you while developing. 646 00:30:22,860 --> 00:30:25,440 So you kind of start thinking, do I really actually need all 647 00:30:25,440 --> 00:30:26,140 these columns? 648 00:30:26,540 --> 00:30:28,780 Because if you don't, then you have a better debug log. 649 00:30:28,780 --> 00:30:32,560 So I kind of think you have to punish developers as early as 650 00:30:32,560 --> 00:30:34,420 possible for their performance problems. 651 00:30:35,140 --> 00:30:36,780 I mean, jOOQ doesn't judge you. 652 00:30:37,740 --> 00:30:40,900 It just gives you some tools. 653 00:30:41,260 --> 00:30:44,560 Nikolay: Just giving you some convenience looking at logs. 654 00:30:44,560 --> 00:30:45,060 Okay. 655 00:30:46,160 --> 00:30:47,480 Yeah, that's interesting. 656 00:30:48,220 --> 00:30:49,700 So, reduce number of columns. 657 00:30:49,780 --> 00:30:50,280 Good. 658 00:30:51,460 --> 00:30:54,020 Michael: Changing tact a little bit, I think you've got a really 659 00:30:54,020 --> 00:30:58,680 interesting perspective on Postgres from a broader landscape 660 00:30:58,980 --> 00:31:02,060 of SQL dialects and databases in general. 661 00:31:02,500 --> 00:31:05,440 What kind of things do you see that are relatively unique to 662 00:31:05,440 --> 00:31:05,860 Postgres? 663 00:31:05,860 --> 00:31:08,520 Or do you see people in the Postgres world particularly using 664 00:31:08,520 --> 00:31:09,260 or liking? 665 00:31:09,640 --> 00:31:12,340 Lukas: I mean, the 1 thing that is very unique to Postgres, there 666 00:31:12,340 --> 00:31:14,200 are 2 things from my perspective. 667 00:31:14,260 --> 00:31:16,360 The first 1 is it's very developer-centric. 668 00:31:17,420 --> 00:31:20,780 So you can see that with other database products like Oracle, 669 00:31:20,860 --> 00:31:24,640 DB2 or SQL Server, they're very production centric, very operation 670 00:31:24,840 --> 00:31:25,320 centric. 671 00:31:25,320 --> 00:31:29,180 They used to base their entire sales on these kinds of things. 672 00:31:29,440 --> 00:31:32,080 So this means you have a lot of production tools as well, which 673 00:31:32,080 --> 00:31:32,960 are great actually. 674 00:31:32,960 --> 00:31:36,660 So I'm still waiting for the like of Oracle Enterprise Manager, 675 00:31:36,680 --> 00:31:39,760 if you know it, for Postgres where you can analyze production 676 00:31:39,760 --> 00:31:43,040 workloads and, and, and query what, what kind of workload you 677 00:31:43,040 --> 00:31:45,980 had 5 months ago, and it's still there. 678 00:31:45,980 --> 00:31:46,410 It is still happy. 679 00:31:46,410 --> 00:31:49,020 I mean, you have to pay for these kinds of extras, but it's still 680 00:31:49,020 --> 00:31:49,340 there. 681 00:31:49,340 --> 00:31:52,320 You can still analyze anomalies in terms of performance, what 682 00:31:52,320 --> 00:31:53,560 happened 5 months ago. 683 00:31:53,560 --> 00:31:57,840 So these kinds of tools are a bit lacking, but on the other hand, 684 00:31:57,940 --> 00:32:01,420 because it's so developer focused, you have this whole extension 685 00:32:01,640 --> 00:32:04,060 system, which is unprecedented. 686 00:32:04,280 --> 00:32:06,480 And I haven't seen it since either. 687 00:32:06,480 --> 00:32:10,900 So anyone can extend anything in any layer of the, of the database 688 00:32:10,900 --> 00:32:11,320 product. 689 00:32:11,320 --> 00:32:14,800 So you have these wonderful extensions like PostGIS for instance, 690 00:32:14,800 --> 00:32:17,860 which is, It's not really an extension because it's also part 691 00:32:17,860 --> 00:32:20,720 of the SQL standard, but it has been implemented as an extension 692 00:32:20,740 --> 00:32:25,180 just, I guess, to show what is possible and such a huge additional 693 00:32:25,240 --> 00:32:27,480 product can be made as an extension. 694 00:32:27,540 --> 00:32:32,320 And you have indexes and data types and all kinds of things that 695 00:32:32,320 --> 00:32:36,100 other vendors have to either offer out of the box or they don't 696 00:32:36,100 --> 00:32:36,800 have it. 697 00:32:37,040 --> 00:32:38,880 You can't extend Oracle really. 698 00:32:38,880 --> 00:32:43,040 So that's really something very, very amazing. 699 00:32:43,660 --> 00:32:46,320 But as I said, from the other perspective, from an operations 700 00:32:46,480 --> 00:32:49,780 perspective, this is something that personally, I think Postgres 701 00:32:49,780 --> 00:32:52,700 is still very much behind Oracle, for instance. 702 00:32:52,800 --> 00:32:53,860 So I'm an Oracle guy. 703 00:32:53,860 --> 00:32:57,180 I can't really comment on SQL Server or others, but the Oracle 704 00:32:57,180 --> 00:33:01,020 database where I worked at before I made jOOQ, it could easily 705 00:33:01,080 --> 00:33:04,960 handle very, very complex queries that produced 500 lines of 706 00:33:04,960 --> 00:33:09,500 execution plan on billions of rows, and it would run in milliseconds. 707 00:33:09,660 --> 00:33:11,720 And I don't think you can do that with Postgres. 708 00:33:11,720 --> 00:33:12,980 I wouldn't risk it. 709 00:33:13,080 --> 00:33:17,440 So we all remember the days when there was a hard limit of number 710 00:33:17,440 --> 00:33:21,040 of joins in Postgres, from which, starting from where, you didn't 711 00:33:21,040 --> 00:33:24,660 have any smart optimizer anymore to reorder the joins in the 712 00:33:24,660 --> 00:33:25,360 right order. 713 00:33:25,920 --> 00:33:30,060 I forgot what the limit was, but I think you had to be very careful 714 00:33:30,060 --> 00:33:33,420 if you have more than 10 joins or something like that, because 715 00:33:33,420 --> 00:33:36,100 then it would just join from left to right syntactically, and 716 00:33:36,100 --> 00:33:36,920 this is horrible. 717 00:33:37,120 --> 00:33:38,920 Nikolay: Join collapse limit should be 8. 718 00:33:39,340 --> 00:33:40,580 Lukas: Yeah, that's the join collapse limit. 719 00:33:40,900 --> 00:33:44,220 So, once you know these kind of things, obviously, make the right 720 00:33:44,220 --> 00:33:48,680 choices, and you probably won't run into big issues, but it's 721 00:33:48,680 --> 00:33:53,000 just, it's a very unique focus that I often missed in the past 722 00:33:53,000 --> 00:33:56,360 from Oracle, for instance, or completely ignored developer experience 723 00:33:56,780 --> 00:33:57,680 for decades. 724 00:33:58,140 --> 00:34:00,600 It was like, you don't even have a Boolean type, right? 725 00:34:00,600 --> 00:34:03,040 So this is hard for me to understand. 726 00:34:03,040 --> 00:34:04,600 It's such a simple thing. 727 00:34:04,600 --> 00:34:06,540 You think it's such a simple thing. 728 00:34:06,580 --> 00:34:10,280 So very, very useful, but Oracle wouldn't have implemented it 729 00:34:10,280 --> 00:34:11,140 until recently. 730 00:34:11,280 --> 00:34:16,840 So this is a focus that Postgres has and few of the others had, 731 00:34:16,840 --> 00:34:20,180 even MySQL wasn't very developer friendly from that perspective. 732 00:34:22,160 --> 00:34:23,540 So this is very unique. 733 00:34:24,100 --> 00:34:26,880 If developers were the only ones to choose the database product, 734 00:34:26,880 --> 00:34:30,480 it's always Postgres, because I mean, you're just gonna implement 735 00:34:30,480 --> 00:34:31,700 everything with Postgres. 736 00:34:31,720 --> 00:34:34,160 You can write stored procedures, you can have your own indexes, 737 00:34:34,160 --> 00:34:35,700 you can extend it yourself. 738 00:34:36,040 --> 00:34:38,720 Probably you don't have to because someone already wrote an extension 739 00:34:38,720 --> 00:34:39,940 for whatever you need. 740 00:34:40,200 --> 00:34:43,640 Oh, the second thing is, it's amazing how standards compliant 741 00:34:43,700 --> 00:34:44,380 it is. 742 00:34:44,380 --> 00:34:48,020 So a lot of times, Postgres really waits until something is standardized 743 00:34:48,420 --> 00:34:50,820 before they implement it instead of innovating. 744 00:34:51,420 --> 00:34:55,040 Nikolay: Right now we are waiting on UID version 7 because of 745 00:34:55,040 --> 00:34:55,540 standards. 746 00:34:55,640 --> 00:34:59,960 Like it didn't get into version 17 of Postgres because RFC was 747 00:34:59,960 --> 00:35:00,660 not finalized. 748 00:35:01,480 --> 00:35:04,680 Every library already started to support it, but Postgres decided 749 00:35:04,680 --> 00:35:08,440 to wait on RFC, which is like very conservative decision, right? 750 00:35:09,280 --> 00:35:10,120 Lukas: Most of the times. 751 00:35:10,120 --> 00:35:10,620 Yes. 752 00:35:10,760 --> 00:35:14,180 So I think that's a good thing in the end, because the standard, 753 00:35:14,180 --> 00:35:17,380 in my opinion, in most parts is well-written. 754 00:35:17,720 --> 00:35:20,980 It's a very complicated document, of course, but I think in terms 755 00:35:20,980 --> 00:35:25,740 of syntax, it's kind of everything feels like SQL. 756 00:35:26,040 --> 00:35:29,940 So some database products think they have to invent some syntax 757 00:35:30,040 --> 00:35:32,040 And it just doesn't look like SQL anymore. 758 00:35:32,040 --> 00:35:37,120 It's just very weird, funky thing that doesn't fit the rest of 759 00:35:37,120 --> 00:35:37,720 the language. 760 00:35:37,720 --> 00:35:42,420 And the standard is very, very consistent, I think, in most parts. 761 00:35:42,440 --> 00:35:45,660 And to wait for the standard to appear is a good thing. 762 00:35:45,840 --> 00:35:48,280 So sometimes there's an exception to the rule, as I said, on 763 00:35:48,280 --> 00:35:49,860 conflict was 1 of these exceptions. 764 00:35:50,500 --> 00:35:54,860 JSON as well, in case of which I think it was something everyone 765 00:35:54,860 --> 00:35:58,120 needed and the standard was severely lacking. 766 00:35:58,620 --> 00:36:00,160 And Postgres just did their own thing. 767 00:36:00,160 --> 00:36:04,960 So I kind of think a lot of people kind of regret the heterogeneity 768 00:36:05,320 --> 00:36:06,560 of the JSON API. 769 00:36:06,620 --> 00:36:11,600 So you have to always consult the docs to see if something uses 770 00:36:11,600 --> 00:36:15,380 a JSON path, which is a standard thing in the JSON world, or 771 00:36:15,380 --> 00:36:20,080 if it's just an array of path elements, or if this function returns 772 00:36:20,080 --> 00:36:23,620 a table or a set of something, or whatever data structure. 773 00:36:23,620 --> 00:36:29,020 So I think now with the JSON standard, this is going to be much 774 00:36:29,020 --> 00:36:31,280 cleaner once it's completely rolled out. 775 00:36:31,280 --> 00:36:34,120 So I'm not up to date where the Postgres stands. 776 00:36:34,120 --> 00:36:37,860 I'm still not supporting that syntax, but from what I've seen, 777 00:36:37,860 --> 00:36:39,520 it's going into the right direction. 778 00:36:39,860 --> 00:36:42,280 Nikolay: Yeah, some parts support it, some parts I think are 779 00:36:42,280 --> 00:36:43,040 still bending. 780 00:36:43,260 --> 00:36:43,760 Yeah. 781 00:36:43,860 --> 00:36:44,820 Yeah, because I 782 00:36:44,820 --> 00:36:47,660 Michael: think A lot went in in 17. 783 00:36:47,960 --> 00:36:48,400 Nikolay: Right. 784 00:36:48,400 --> 00:36:48,820 Lukas: Yeah. 785 00:36:48,820 --> 00:36:51,180 And speaking of this, I'm going to be really curious. 786 00:36:51,180 --> 00:36:55,460 I saw a commit recently regarding temporal primary keys. 787 00:36:55,460 --> 00:36:56,260 Is that correct? 788 00:36:56,520 --> 00:36:58,600 And it is absolutely not the standard. 789 00:36:58,900 --> 00:37:00,720 So this is very curious. 790 00:37:00,720 --> 00:37:02,180 I'm very curious about this. 791 00:37:02,240 --> 00:37:03,780 Are you aware of this commit? 792 00:37:03,900 --> 00:37:04,540 Michael: I'm not. 793 00:37:04,540 --> 00:37:05,720 Lukas: It's still on a branch. 794 00:37:05,720 --> 00:37:08,480 I think it's not, it's not going to be merged, but I mean, Postgres 795 00:37:08,480 --> 00:37:09,780 has these range types. 796 00:37:10,380 --> 00:37:10,880 Right. 797 00:37:11,280 --> 00:37:11,520 Nikolay: Right. 798 00:37:11,520 --> 00:37:15,060 Lukas: And they're useful obviously, but the SQL standard since 799 00:37:15,060 --> 00:37:19,920 2011 defined how you should write temporal databases, both by 800 00:37:19,920 --> 00:37:20,420 temporal. 801 00:37:20,460 --> 00:37:23,280 So you have system versioning and then logical versioning. 802 00:37:24,400 --> 00:37:26,760 And this is something that is still lacking in Postgres. 803 00:37:26,820 --> 00:37:29,120 And I think a lot of people would like to see that. 804 00:37:29,540 --> 00:37:29,760 Nikolay: Yeah. 805 00:37:29,760 --> 00:37:33,900 In Postgres, there is an opinion that it's in the past, because 806 00:37:33,900 --> 00:37:34,200 it was 807 00:37:34,200 --> 00:37:34,760 Lukas: in the past, 808 00:37:34,760 --> 00:37:35,700 Nikolay: it was removed. 809 00:37:36,760 --> 00:37:39,180 Michael: So that's a nice temporal joke. 810 00:37:39,920 --> 00:37:41,320 Lukas: An extension I think? 811 00:37:41,320 --> 00:37:45,320 Michael: It was in Postgres, the original Berkeley version. 812 00:37:46,320 --> 00:37:50,420 Nikolay: This is how MVCC inherited from these versions 813 00:37:50,500 --> 00:37:51,320 of tuples. 814 00:37:52,860 --> 00:37:55,800 So your time travel basically was supported very, very 815 00:37:55,800 --> 00:37:57,640 long ago, but then it was removed. 816 00:37:57,640 --> 00:37:58,640 So like interesting idea. 817 00:37:58,780 --> 00:37:59,280 Michael: I think for 818 00:37:59,280 --> 00:38:00,320 performance reasons. 819 00:38:01,560 --> 00:38:03,200 Nikolay: Yeah, So I think so. 820 00:38:03,820 --> 00:38:07,240 But yeah, this is a super interesting, like specific topic. 821 00:38:07,800 --> 00:38:10,140 Sometimes we do need to implement something with triggers and 822 00:38:10,140 --> 00:38:11,460 so on, like, which is very 823 00:38:11,460 --> 00:38:11,720 Lukas: Yeah. 824 00:38:11,720 --> 00:38:11,980 Yeah. 825 00:38:11,980 --> 00:38:13,520 It's very hard to do manually. 826 00:38:13,520 --> 00:38:16,960 I think the standard helps a lot with, with, I mean, you can 827 00:38:16,960 --> 00:38:20,040 write an update statement and it's automatically transformed 828 00:38:20,320 --> 00:38:21,940 into a delete and insert. 829 00:38:21,940 --> 00:38:25,040 So if you have to split the record logically, this is really 830 00:38:25,040 --> 00:38:26,520 a pain to do manually, right? 831 00:38:26,520 --> 00:38:27,040 So it's 832 00:38:27,040 --> 00:38:29,880 Nikolay: not super pain because you can have, for example, Postgres, 833 00:38:29,880 --> 00:38:32,760 you know, like I remember your very old post, everything is a 834 00:38:32,760 --> 00:38:33,260 table. 835 00:38:33,340 --> 00:38:36,880 Or maybe it was a post of some 10 tips or something, and 1 of 836 00:38:36,880 --> 00:38:39,120 the tips was, consider everything as a table. 837 00:38:39,640 --> 00:38:43,720 In Postgres, you can collapse all columns into a single record 838 00:38:43,820 --> 00:38:44,820 type and insert. 839 00:38:45,860 --> 00:38:48,880 For example, in JSON form right now, I would insert it and I 840 00:38:48,880 --> 00:38:50,580 would have a shadow table. 841 00:38:50,580 --> 00:38:52,400 I implemented this several times. 842 00:38:52,500 --> 00:38:56,280 You have a shadow table which serves as like a guy for all tables, 843 00:38:56,280 --> 00:38:59,600 which has, has trigger to track changes. 844 00:38:59,600 --> 00:39:00,100 Right. 845 00:39:00,240 --> 00:39:05,140 And then you, you just track timestamp action, like it was delete 846 00:39:05,140 --> 00:39:08,400 or update, who did it, and then whole row you pack it in a single 847 00:39:08,400 --> 00:39:10,700 column, breaking first normal form. 848 00:39:10,760 --> 00:39:14,620 Lukas: But then you normalize in a very bad way, and secondly, 849 00:39:14,680 --> 00:39:16,360 this sounds like a lot of work. 850 00:39:16,580 --> 00:39:20,280 Nikolay: It's not a lot of work, it's like 3, 5 lines of code, 851 00:39:20,280 --> 00:39:21,060 that's it. 852 00:39:21,500 --> 00:39:24,380 But it's not possible in jOOQ 3 version because triggers are 853 00:39:24,380 --> 00:39:25,420 not supported, right? 854 00:39:25,640 --> 00:39:28,700 Triggers are supported only in Enterprise version. 855 00:39:28,980 --> 00:39:32,580 Lukas: I mean, if you want to write the trigger with jOOQ, yes, 856 00:39:32,600 --> 00:39:36,820 then you have to use the commercial additions, but hardly anyone 857 00:39:36,820 --> 00:39:38,080 writes triggers with jOOQ. 858 00:39:38,080 --> 00:39:41,600 I mean, this is only useful when you have to have, when you support 859 00:39:41,600 --> 00:39:45,240 5 database products and you want to have, you want to write the 860 00:39:45,240 --> 00:39:47,540 trigger only once you write it with jOOQ. 861 00:39:47,960 --> 00:39:50,600 But I mean, jOOQ doesn't care if there's a trigger on the database 862 00:39:50,600 --> 00:39:51,180 table, right? 863 00:39:51,180 --> 00:39:54,740 So if there's 1, so it's transparent to jOOQ. 864 00:39:55,140 --> 00:39:56,060 Yeah, okay. 865 00:39:56,280 --> 00:39:58,380 You can't really say jOOQ doesn't support them. 866 00:39:58,380 --> 00:40:00,980 It's just, you can't create them with jOOQ. 867 00:40:01,200 --> 00:40:01,980 Nikolay: Great answer. 868 00:40:02,220 --> 00:40:03,600 Free version, you mean like an 869 00:40:03,600 --> 00:40:04,160 Lukas: enterprise version? 870 00:40:04,160 --> 00:40:04,960 Free version, yeah. 871 00:40:04,960 --> 00:40:05,460 Nikolay: Yeah. 872 00:40:06,400 --> 00:40:07,200 I'm very curious. 873 00:40:07,200 --> 00:40:08,460 I'm not against this. 874 00:40:08,480 --> 00:40:09,140 It's good. 875 00:40:09,140 --> 00:40:13,040 I mean, it's great that you build both like open source product 876 00:40:13,040 --> 00:40:15,560 on Apache tool license and business. 877 00:40:16,340 --> 00:40:21,300 I was just curious, like, have you ever had thoughts about going 878 00:40:21,300 --> 00:40:24,900 full closed source or fully open source? 879 00:40:25,900 --> 00:40:26,100 Well, 880 00:40:26,100 --> 00:40:28,500 Lukas: it was fully open source in the beginning. 881 00:40:28,500 --> 00:40:31,960 So when I started this, I had no idea this could be a business. 882 00:40:31,960 --> 00:40:35,500 I mean, I just had to try it and then put it out there for people 883 00:40:35,500 --> 00:40:37,220 to see and give me feedback. 884 00:40:37,280 --> 00:40:38,220 Is this a good idea? 885 00:40:38,220 --> 00:40:39,520 Should I do it like that? 886 00:40:39,520 --> 00:40:43,260 And there was a very active early community of people who worked 887 00:40:43,260 --> 00:40:44,780 with the all open source jOOQ. 888 00:40:45,100 --> 00:40:48,420 And then I started seeing even more and more banks and insurance 889 00:40:48,420 --> 00:40:51,800 companies using it, but never contributing as you know, the story. 890 00:40:51,820 --> 00:40:53,320 And I thought it was my fault. 891 00:40:53,320 --> 00:40:54,080 Nikolay: I get it. 892 00:40:54,080 --> 00:40:56,780 Pong Jones, let's, let's generalize Oracle users. 893 00:40:57,260 --> 00:40:59,680 Lukas: Well, that was the choice I made, but, but it doesn't 894 00:40:59,680 --> 00:40:59,960 matter. 895 00:40:59,960 --> 00:41:02,320 You know, It doesn't matter what they're using in the end. 896 00:41:02,320 --> 00:41:03,660 I just never got any... 897 00:41:04,060 --> 00:41:07,320 I mean, I got contributions, but I didn't care about code. 898 00:41:07,360 --> 00:41:09,460 I cared about money much more than code. 899 00:41:09,760 --> 00:41:14,620 So, to make them a sustainable business, in the end, I thought... 900 00:41:15,320 --> 00:41:19,340 There are models where you pay for support, but in my opinion, 901 00:41:19,340 --> 00:41:22,740 if you want to make money with support, then you have to earn 902 00:41:22,740 --> 00:41:25,020 your money by working for it. 903 00:41:25,200 --> 00:41:28,300 So I earn money when I work, and if I have a licensed model, 904 00:41:28,300 --> 00:41:30,560 then I earn money when customers work, right? 905 00:41:30,920 --> 00:41:33,580 So I thought this was going to scale much better. 906 00:41:33,900 --> 00:41:34,740 Michael: I like that a lot. 907 00:41:34,740 --> 00:41:38,720 I also think if you charge for support, you're incentivized to 908 00:41:38,720 --> 00:41:39,940 hurt the experience. 909 00:41:40,160 --> 00:41:43,100 Lukas: And I think, but you're going to say that, but that's 910 00:41:43,100 --> 00:41:43,940 how I think. 911 00:41:43,980 --> 00:41:47,040 Michael: But I think, I think it's not surprising that as somebody 912 00:41:47,040 --> 00:41:50,080 that focuses so much on developer experience and wants to keep 913 00:41:50,080 --> 00:41:53,400 pushing that forward and making that good, it's unsurprising 914 00:41:53,600 --> 00:41:57,380 that you don't want to be incentivized to make it bad like that. 915 00:41:57,380 --> 00:41:58,940 It makes perfect sense to me. 916 00:41:59,680 --> 00:41:59,860 Lukas: Yeah. 917 00:41:59,860 --> 00:42:02,640 I mean, that's a, that's my thought as well. 918 00:42:02,640 --> 00:42:06,900 I, I don't want to name companies or names, but I've heard some 919 00:42:07,240 --> 00:42:11,260 open source products are very, very unusable and you really need 920 00:42:11,260 --> 00:42:12,800 the support and not all of them. 921 00:42:12,800 --> 00:42:13,260 Of course. 922 00:42:13,260 --> 00:42:16,400 I mean, Postgres and Linux are examples, which aren't, which 923 00:42:16,400 --> 00:42:19,820 this isn't the case, but there are some products that are super 924 00:42:19,820 --> 00:42:23,760 complicated and they're just pushed out there and you're free 925 00:42:23,760 --> 00:42:25,800 to use it, but you won't figure it out. 926 00:42:25,800 --> 00:42:30,160 So you have to call someone even to install it and that's what 927 00:42:30,160 --> 00:42:30,640 I'm thinking. 928 00:42:30,640 --> 00:42:34,960 So in those cases, you need a sales representative that pushes 929 00:42:34,960 --> 00:42:37,680 the product down someone's throat and they make a lot of money 930 00:42:37,680 --> 00:42:38,820 with the support contract. 931 00:42:38,860 --> 00:42:43,940 And I think it's less honest than licensing, which people think 932 00:42:43,940 --> 00:42:45,560 about, okay, is this a good product? 933 00:42:45,560 --> 00:42:48,280 Should I really spend money on it because I have to pay it up 934 00:42:48,280 --> 00:42:52,400 front right from the day 1 I have to pay money to use this product 935 00:42:52,440 --> 00:42:55,840 and they're gonna test it really really really well and there's 936 00:42:55,840 --> 00:42:59,080 a lot of competition so I mean people could use hibernate or 937 00:42:59,080 --> 00:43:03,240 whatever but people choose juke despite the cost which in the 938 00:43:03,240 --> 00:43:04,800 end is very cheap for a company. 939 00:43:04,800 --> 00:43:09,240 So I think cost is never the issue, but, they're still going 940 00:43:09,240 --> 00:43:12,220 to evaluate it because they have to still go through purchasing 941 00:43:12,280 --> 00:43:16,560 and make it a business case for, for, for the purchase. 942 00:43:16,560 --> 00:43:20,260 So I really want to have a very, very good experience. 943 00:43:20,460 --> 00:43:24,140 And 1 part of this is I also always answered all the questions 944 00:43:24,140 --> 00:43:27,540 on Stack Overflow within a short period of time, because if I 945 00:43:27,540 --> 00:43:30,520 answer it, it's gonna be authoritative and I'm gonna answer with 946 00:43:30,520 --> 00:43:33,900 all the details and all the caveats and all the edge cases. 947 00:43:34,200 --> 00:43:37,660 So anyone else who finds the question, we'll find this question 948 00:43:37,660 --> 00:43:38,200 and the answer. 949 00:43:38,200 --> 00:43:39,720 And they're not going to ask me again. 950 00:43:39,720 --> 00:43:39,960 Right. 951 00:43:39,960 --> 00:43:43,660 So Google is my friend and now Chat GPT is my friend as well, 952 00:43:43,660 --> 00:43:46,480 because Chat GPT can answer jOOQ questions very well because 953 00:43:46,480 --> 00:43:48,620 of all the answers I've given on Stack Overflow. 954 00:43:49,080 --> 00:43:51,260 Nikolay: Yeah, but open knowledge, right? 955 00:43:51,460 --> 00:43:51,960 Lukas: Yeah. 956 00:43:52,580 --> 00:43:54,290 I don't want to earn money with knowledge. 957 00:43:54,290 --> 00:43:57,480 I mean, I could sell books and all these kind of things, but 958 00:43:57,700 --> 00:44:00,900 it's just the wrong kind of effort, I think. 959 00:44:00,980 --> 00:44:02,120 Nikolay: Destruction, right? 960 00:44:02,640 --> 00:44:03,140 Yeah. 961 00:44:03,400 --> 00:44:04,520 From the core. 962 00:44:04,940 --> 00:44:11,180 I wonder for enterprise, like paid versions, is source available 963 00:44:11,260 --> 00:44:12,360 or it's like kind of 964 00:44:12,360 --> 00:44:13,660 Lukas: yeah you get the source code 965 00:44:13,660 --> 00:44:14,380 Nikolay: well that's great 966 00:44:14,380 --> 00:44:17,620 Lukas: the right to modify so you can if you have a bargain and 967 00:44:17,620 --> 00:44:19,980 Nikolay: you can fix it yourself and propose a fix. 968 00:44:20,460 --> 00:44:23,240 Lukas: Or maybe it's not even a bug, but you think it's a bug, 969 00:44:25,080 --> 00:44:26,980 but you disagree, you can fix it. 970 00:44:26,980 --> 00:44:28,080 And some companies do that. 971 00:44:28,080 --> 00:44:28,520 So this 972 00:44:28,520 --> 00:44:32,460 Nikolay: is what we like in commercial software, like full transparency 973 00:44:32,720 --> 00:44:34,140 and see how it works 974 00:44:34,280 --> 00:44:34,780 Lukas: inside. 975 00:44:34,840 --> 00:44:37,460 I mean, most people don't actually fix stuff, but maybe they 976 00:44:37,460 --> 00:44:39,740 want to debug stuff to better understand it. 977 00:44:39,900 --> 00:44:43,540 And if you've ever worked with a commercial JDBC driver, like 978 00:44:43,540 --> 00:44:46,720 I do, with the Oracle driver, it's always like, Oh my God, there's 979 00:44:46,720 --> 00:44:50,080 a null pointer exception in the middle of the driver and I have 980 00:44:50,080 --> 00:44:52,780 no idea what I'm doing wrong from an API perspective. 981 00:44:52,920 --> 00:44:55,960 So I have to reverse engineer it. 982 00:44:55,960 --> 00:44:59,440 Not, not with the code, but just try an error with API calls 983 00:44:59,440 --> 00:45:02,880 until I find out why the exception, why the bug inside of the 984 00:45:02,880 --> 00:45:03,740 driver happens. 985 00:45:03,740 --> 00:45:06,000 And with source code, it would be much easier, right? 986 00:45:06,260 --> 00:45:09,400 So bugs happen, but if you know exactly why the bug happens, 987 00:45:09,400 --> 00:45:13,300 then you have a much easier life than if you have to guess. 988 00:45:13,780 --> 00:45:14,140 Nikolay: Yeah. 989 00:45:14,140 --> 00:45:15,840 Last question from me. 990 00:45:16,000 --> 00:45:18,180 What do you think about nulls in general? 991 00:45:18,720 --> 00:45:19,220 Lukas: Nulls? 992 00:45:19,760 --> 00:45:20,780 Nulls, yeah. 993 00:45:21,900 --> 00:45:23,480 Or they're a blessing in a curse. 994 00:45:23,480 --> 00:45:26,800 I mean, I understand why they have been introduced, because it 995 00:45:26,800 --> 00:45:28,180 was easy to do, right? 996 00:45:28,260 --> 00:45:31,480 And I mean, There are 2 takes here. 997 00:45:31,480 --> 00:45:36,600 So nulls in most languages are mostly a pain because they lead 998 00:45:36,600 --> 00:45:37,280 to exceptions. 999 00:45:37,960 --> 00:45:41,480 And null in SQL is mostly a pain because it doesn't lead to exceptions. 1000 00:45:41,480 --> 00:45:45,420 It's just a value like many others, but it's a special value 1001 00:45:45,420 --> 00:45:47,060 and it just propagates. 1002 00:45:47,160 --> 00:45:50,880 It's these kind of philosophies and both are right and wrong. 1003 00:45:51,420 --> 00:45:53,600 But what else would you want to do, right? 1004 00:45:53,760 --> 00:45:57,880 I mean, you kind of need an absent value, right? 1005 00:45:57,880 --> 00:46:02,320 I'm not sure if the idea of the unknown value is so useful, but 1006 00:46:02,320 --> 00:46:04,100 an absent value is very useful. 1007 00:46:04,840 --> 00:46:07,840 I mean, sometimes you just, you can't normalize everything to 1008 00:46:07,840 --> 00:46:09,940 6 normal form to avoid NULLs, right? 1009 00:46:09,940 --> 00:46:12,180 So it's very pragmatic. 1010 00:46:12,900 --> 00:46:14,360 Nikolay: It's very pragmatic indeed. 1011 00:46:14,380 --> 00:46:14,620 Yeah. 1012 00:46:14,620 --> 00:46:18,820 But in SQL, we have so many places where people make mistakes 1013 00:46:18,820 --> 00:46:19,620 all the time. 1014 00:46:20,020 --> 00:46:21,760 Lukas: Yeah, SQL is weird about it. 1015 00:46:21,760 --> 00:46:25,080 I mean, this unknown value, I used to do a SQL training and it 1016 00:46:25,080 --> 00:46:29,900 was about 30 minutes about it was just NULLs and how it's different 1017 00:46:29,900 --> 00:46:30,720 here from there. 1018 00:46:30,720 --> 00:46:34,400 For instance, 2 NULLs are not distinct, right? 1019 00:46:34,400 --> 00:46:34,900 Nikolay: Yes. 1020 00:46:35,140 --> 00:46:36,600 Lukas: But they're not the same either. 1021 00:46:36,600 --> 00:46:39,880 So there are different terms in the SQL standard, same and distinctness. 1022 00:46:41,100 --> 00:46:43,020 And then even operators are inconsistent. 1023 00:46:43,360 --> 00:46:48,460 So personally, I think Oracle did the pragmatic thing when they 1024 00:46:48,460 --> 00:46:51,940 concatenate a NULL value to a string, you probably don't want 1025 00:46:51,940 --> 00:46:53,800 the result to be NULL, I think. 1026 00:46:54,140 --> 00:46:56,020 You would just want to ignore the concatenation. 1027 00:46:56,280 --> 00:47:00,480 This is pragmatic, but it's very unlogical and leads to a ton 1028 00:47:00,480 --> 00:47:04,540 of side effects, which probably means that it was a bad idea 1029 00:47:04,540 --> 00:47:05,420 to start with. 1030 00:47:05,740 --> 00:47:08,660 Nikolay: And there are NULLs in other languages, I mean, in regular 1031 00:47:08,660 --> 00:47:09,720 application languages. 1032 00:47:09,960 --> 00:47:14,580 And I remember how I struggled trying to explain this 3 value 1033 00:47:14,580 --> 00:47:18,700 logic and like, observing what people do in code, in regular 1034 00:47:18,700 --> 00:47:20,140 application code with NULLs. 1035 00:47:20,140 --> 00:47:23,400 They do things I would never do like in SQL, right? 1036 00:47:23,400 --> 00:47:25,540 So different philosophies, right? 1037 00:47:25,580 --> 00:47:26,880 Lukas: Yeah, there are different philosophies. 1038 00:47:27,620 --> 00:47:30,320 But I'm not sure if the SQL philosophy is really useful. 1039 00:47:30,320 --> 00:47:34,640 I mean, I'm trying to explain how it works by explaining NOT IN 1040 00:47:34,640 --> 00:47:36,140 in the NOT IN predicate. 1041 00:47:36,900 --> 00:47:39,920 When you have a NULL in NOT IN, everything falls apart. 1042 00:47:39,920 --> 00:47:41,880 I mean, it's very logical and consistent. 1043 00:47:42,100 --> 00:47:45,380 You can explain it with 3 value logic, but it's never useful, 1044 00:47:45,380 --> 00:47:45,880 right? 1045 00:47:46,220 --> 00:47:48,220 This kind of invalidates the whole predicate. 1046 00:47:48,340 --> 00:47:49,840 Nikolay: Yeah, it's a bomb. 1047 00:47:50,460 --> 00:47:51,360 Lukas: It's a bomb? 1048 00:47:52,440 --> 00:47:55,860 And I mean, usually people don't put a NULL value in a NOT IN 1049 00:47:55,860 --> 00:47:57,440 list, but what about a subquery? 1050 00:47:57,440 --> 00:48:00,040 So NOT IN SELECT something, and then you have a NULL value in 1051 00:48:00,040 --> 00:48:01,500 there, and you don't get results. 1052 00:48:00,720 --> 00:48:04,120 And it's not obvious from just reading the code or the query. 1053 00:48:00,040 --> 00:48:01,500 there, and you don't get results. 1054 00:48:01,560 --> 00:48:02,060 Why? 1055 00:48:02,720 --> 00:48:03,480 It's horrible. 1056 00:48:03,900 --> 00:48:04,400 Horrible. 1057 00:48:05,500 --> 00:48:05,660 We 1058 00:48:05,660 --> 00:48:09,140 Nikolay: had the whole episode about no, so if Michael long ago, 1059 00:48:09,140 --> 00:48:12,860 yeah, yeah, let's stop because definitely it's not, it's not 1060 00:48:12,860 --> 00:48:14,620 going to go away. 1061 00:48:14,620 --> 00:48:18,440 And I, I'm quite sure I will make more mistakes with nulls in 1062 00:48:18,440 --> 00:48:19,140 my life. 1063 00:48:19,340 --> 00:48:22,080 Lukas: But maybe the 1 thing I really think SQL should have done 1064 00:48:22,080 --> 00:48:24,400 differently, and there are some database products that actually 1065 00:48:24,400 --> 00:48:28,180 do it differently, is the default data type should be not null, 1066 00:48:28,180 --> 00:48:28,660 right? 1067 00:48:28,660 --> 00:48:31,280 So when you create a table, When you don't write anything at 1068 00:48:31,280 --> 00:48:33,060 all, then it should be not null. 1069 00:48:33,280 --> 00:48:35,960 The fact that the default is nullable is wrong. 1070 00:48:36,740 --> 00:48:38,820 Nikolay: This forces you to define default. 1071 00:48:39,280 --> 00:48:40,740 Or, well, no. 1072 00:48:42,320 --> 00:48:44,160 Michael: The default word is confusing. 1073 00:48:44,240 --> 00:48:50,680 You mean, if I create id int8, I shouldn't have to say not 1074 00:48:50,680 --> 00:48:51,180 null. 1075 00:48:51,340 --> 00:48:55,280 Lukas: Yes, you should have to say I want this to be nullable, 1076 00:48:56,060 --> 00:48:58,780 because then it's a conscious decision and they know what you're 1077 00:48:58,780 --> 00:48:59,060 doing. 1078 00:48:59,060 --> 00:49:02,320 But the fact that maybe by accident you have something not null, 1079 00:49:02,320 --> 00:49:03,220 something nullable. 1080 00:49:03,420 --> 00:49:03,840 Yeah. 1081 00:49:03,840 --> 00:49:04,700 It's just wrong. 1082 00:49:04,920 --> 00:49:06,520 Nikolay: And this isn't standard, right? 1083 00:49:07,040 --> 00:49:08,340 Or if you remember? 1084 00:49:09,060 --> 00:49:10,300 Lukas: I don't remember. 1085 00:49:10,600 --> 00:49:13,880 I think maybe the standard says it's implementation specific. 1086 00:49:13,940 --> 00:49:15,660 Nikolay: If it's a standard, We definitely 1087 00:49:15,660 --> 00:49:17,060 Michael: can't change it now though. 1088 00:49:18,100 --> 00:49:19,040 Lukas: No, you can't. 1089 00:49:19,900 --> 00:49:23,140 But I remember Sybase is 1 of the database parts that does it 1090 00:49:23,140 --> 00:49:26,660 differently and ClickHouse as well. 1091 00:49:27,720 --> 00:49:28,220 Interesting. 1092 00:49:28,780 --> 00:49:29,780 Another 1 I forgot. 1093 00:49:30,040 --> 00:49:32,860 Nikolay: So in ClickHouse every column is by default not null? 1094 00:49:33,440 --> 00:49:33,940 Yes. 1095 00:49:34,020 --> 00:49:34,760 That's cool. 1096 00:49:34,760 --> 00:49:35,220 Okay. 1097 00:49:35,220 --> 00:49:36,940 Lukas: Although they messed it up as well. 1098 00:49:36,940 --> 00:49:39,600 So you can still insert null in a not null column. 1099 00:49:39,620 --> 00:49:40,820 You just get a 0. 1100 00:49:41,300 --> 00:49:41,640 What? 1101 00:49:41,640 --> 00:49:42,840 It makes it worse. 1102 00:49:43,140 --> 00:49:43,640 Nikolay: Okay. 1103 00:49:44,100 --> 00:49:44,440 Okay. 1104 00:49:44,440 --> 00:49:45,260 Lukas: That's terrible. 1105 00:49:45,260 --> 00:49:45,760 Amazing. 1106 00:49:47,540 --> 00:49:49,700 I think this is a performance idea. 1107 00:49:49,900 --> 00:49:53,160 You actually, if you just ignore the check, you kind of don't 1108 00:49:53,160 --> 00:49:57,100 check for NULLs, then you're faster, but whatever. 1109 00:49:58,260 --> 00:50:00,260 Michael: Lukas, I'm super conscious of your time. 1110 00:50:00,260 --> 00:50:02,460 Is there any last things you wanted to say? 1111 00:50:03,420 --> 00:50:06,660 Lukas: I hope there are many many more exciting Postgres releases 1112 00:50:06,660 --> 00:50:07,440 in the near future. 1113 00:50:07,440 --> 00:50:09,820 I'm looking forward to all the good stuff that's coming. 1114 00:50:10,080 --> 00:50:14,840 Especially if it's ever coming, temporal tables from SQL 2011, 1115 00:50:15,140 --> 00:50:16,001 that will be very exciting. 1116 00:50:16,001 --> 00:50:16,004 Michael: Nice, okay cool. 1117 00:50:16,004 --> 00:50:16,011 Nikolay: Thank you for coming, I enjoyed. 1118 00:50:16,011 --> 00:50:16,014 Lukas: Thank you for having me. 1119 00:50:16,014 --> 00:50:16,017 Nikolay: Thank you. 1120 00:50:16,017 --> 00:50:16,028 Michael: Yeah, very much so, and catch you next week Nikolay. 1121 00:50:16,028 --> 00:50:16,034 Nikolay: Bye bye, have a great week.