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.
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.
