Michael: Hello, hello and welcome to Postgres.FM, weekly show
about all things PostgreSQL.
I am Michael, founder of pgMustard.
I'm joined as usual by Nikolay, founder of Postgres.AI.
Hey Nikolay.
Nikolay: Hey Michael.
How are you?
Michael: Nope, not falling for that.
And we are joined by a wonderful guest, Lukas Eder, founder of
Data Geekery and the creator of jOOQ, a very popular framework.
Thank you so much for joining us, Lukas.
Lukas: Thank you for having me, Michael.
Michael: It's our pleasure.
So, how do you describe jOOQ and what's it helping folks with?
Lukas: So, jOOQ is an internal domain-specific language written
in Java where it models the SQL language directly as a Java API.
This helps Java developers to write SQL queries more efficiently
and more correctly within their Java applications.
There's a code generator that generates the entire schema as
Java objects and you can interact with those objects and create
type safe queries.
That's the base of it.
Michael: Yeah, it's super cool.
But I'm super impressed with how much you focus on developer
experience in general.
I hear quite a few people say they really love jOOQ and they
give about 15 different examples of things they love about it.
But what are the main things you think that people particularly
like about it?
Lukas: Well, the first thing is, and I've studied the market
extensively before making jOOQ and the first thing is it really
looks as much as SQL as it's possible to do when you model an
internal DSL API in Java.
So, of course, there's some limitations.
But when you have in mind there's a group by clause, you just
start typing group by where you expect it, and then it just starts
compiling.
So there's no surprises.
There's almost no surprises in terms of SQL syntax.
So if you know SQL, then you immediately know the jOOQ API.
You don't really have to learn it.
I guess that's 1 of the most popular things, But other than that,
it's also very nicely integrated into the Java language or Kotlin
and Scala, that works as well.
And if you've ever coded PL SQL in Oracle or PL/pgSQL in Postgres,
You kind of like the way how the SQL language is embedded in
the procedural language.
So if you loop, if you iterate over a result set in those procedural
languages, you have the same kind of type safety and you have
the possibility, for instance, to insert bind variables into
your statements at the right spot, and you don't have to worry
about the infrastructure and the logistics of connecting to the
database, and it just feels embedded.
And I think that's what a lot of people really want to do, even
more than having the type safety, is this feeling of embeddedness
into the target language.
And when you map your result sets to Java objects, that also
feels very native.
So it feels like the database is part of the Java of the JVM.
Michael: Yeah.
Even to the point of getting like auto-complete suggestions in
IDEs, but like that is super nice.
Lukas: Yeah.
There are little details like this.
So when you say autocomplete, there's also possibility to comment
on your database tables and comments and that translates to Java
doc.
So whatever you comment directly in the database, it's translated
to documentation inside of your job program you hardly ever have
to go to the database to see what's there so.
Michael: Nice.
Lukas: Yeah.
Michael: In terms of the history how like I did look it up in
or at least the GitHub history How long have you been working
on it and how does that feel?
Lukas: I think the first public version started in 2009.
There were prototypes before that in 2008 I think but 2009 was
the first public version, not on GitHub then, it wasn't first
port.
I don't think GitHub was very popular at the time.
And then Subversion.
That's how old it is already.
I had the idea already back during my university studies at EPFL
in Lausanne.
There was a professor who said you could model any kind of programming
language as an internal DSL.
I'm not sure if you use that term yet.
I think DSL was more coined by...
More recently, but he said you could model any language in terms
of an API if you just have Java interfaces that return interfaces
that are return interfaces.
And this kind of idea stuck with me for all this time, but I
didn't have a use case back then.
And when I started working in the industry, in 2006 was my first
Java job.
I did some PHP work before, but Java started in 2006.
I've seen that all the companies that I've applied to, and even
the 1 that I worked at, they implemented dynamic SQL libraries
because this was, this was something that everyone needs, right?
So everyone has dynamic SQL queries.
When you have a UI with various input boxes and they're all optional,
so you have to compose your SQL queries in a dynamic way.
And everyone wants to avoid string concatenation because it's
not safe and it's boring.
So they did that for both, they had one for SQL and they had one
for Hibernate query language, which is essentially the same thing,
just more limited, but at the same concept.
And no one, no one actually thought about making this a true DSL.
It's well, it was always just a query builder with some weird
API.
So you had Java style methods where you add clauses to the query
object.
And it didn't feel like SQL.
It felt like a tool to solve exactly this dynamic SQL problem,
but it didn't feel like you didn't enjoy using this thing.
And I think JPA still has this thing with their criteria API,
which is still very useful if you want to do dynamic JPQL.
But I've never heard anyone enjoy using that API because it's
just, you have to learn it.
So you have to learn one more thing and it only serves this one purpose
or at least that's how, what it feels like.
It serves this purpose of dynamic JPQL.
And that's when I started prototyping it.
And in the very beginning, I had to first implement the infrastructure
wasn't very diesel style API either so I created the query builder
that could do some select from where very simple conditions But
then I started to really implement this idea because I remembered,
okay, this professor, he had this idea and I'm going to try this.
Is it really possible?
And it turns out it was.
And it's really crazy how many SQL statements today we have in
Juke that are all using this kind of approach where you just
start with the same object and then you start typing and you
auto complete your SQL syntax and it's all one huge graph of methods
that helps you construct your SQL queries.
So Juke has come a long way since 2009 I'd say.
Michael: Yeah, right.
I was looking in your documentation about how many different,
how many different, you call them families of SQL dialects that
you support.
So how many different databases, but also the different versions
of different databases that may or may not support different
syntax and SQL features.
How do you maintain that?
Lukas: Well, first off, luckily the database products are all
very, very backwards compatible, If we take one or two of them, we
don't really care as much, but most of them really value backwards
compatibility a lot.
So supporting new versions is just looking at the new features
and adding support for them, but the old stuff still works.
So at least that's already covered there, more or less.
But other than that, I mean, supporting so many dialect per se
is, yeah, it's a lot of work and a lot of testing, of course,
a lot of automated regression testing to make sure nothing breaks.
And also a Juke really values backwards compatibility a lot,
just like the database products.
So when you embed the joke query into your database application,
you don't want stuff to break.
So if I'm not allowed to break stuff, I'm probably not breaking
stuff.
So this kind of simplifies maintenance as well, because stuff
that once works is probably not going to break just like that.
I mean there's some internal refactorings but there are no really
major, how to put it, major paradigm shifts where everything
works in a different way now and I was breaking everything.
So with automated regression tests, it's not...
It's hard, but it's not that hard, I think.
The hard part is just to find out some weird syntax that implements
a standard feature on this particular dialect.
And that's quite creative at times, especially with the newer
dialects that have just been released recently that are not that
mature yet, or Some dialects are very opinionated.
So you kind of have to think in terms of why did they make those
decisions and how can I map standard SQL onto this database?
But I think that's 1 of the things that people really like when
they work, especially when they work with different database
products.
So if you have a company that works with maybe Postgres and SQL
server, you don't actually have to think about the differences
all the time.
So you can just write substring in the jOOQ API and you don't
care if it's subster or inster or whatever the database product
calls it.
It's just top string and jOOQ will translate it for you.
Nikolay: But there are differences as well, right?
And JSON is a big topic, right?
Lukas: Like huge.
JSON?
Nikolay: Yeah.
Yeah.
Those functions and yeah.
Lukas: It's crazy.
I mean, Postgres was 1 of the first to actually support JSON,
but it didn't actually
Nikolay: before standardizing
Lukas: stuff.
So someone has standardized it, Oracle did a couple of years
ago, and now Postgres started implementing those standards from
what I've seen.
I've still not engaged with that part yet, but you think now
that there's a standard, things have stabilized, but still every
Database product does it differently.
It's crazy.
The devil is in the details.
Nikolay: Yeah.
And have you, like, do you need sometimes to check the standard
or like you don't need it at all?
Lukas: I do it
Nikolay: all the time.
Lukas: Yeah.
Okay.
I'm trying to, the Juke API, if it's not about a vendor-specific
feature, the Juke API really tries to follow the SQL standard
in terms of syntax.
So a vendor-specific feature would be Postgres on conflict clause,
which I think was a mistake.
They should have done merge from the beginning and not invent
something new, but now we have both.
And in that case, Juke also models the on-conflict part like
Postgres did, and then SQLite copied it and a couple of others,
I think.
But for instance, merge is a standard SQL feature and many database
products have some extensions which I then study, but I always
first look at the standard.
What does the standard do?
Because that's a thing that's going to still be there in 50 years.
And different implementations might have their quirks, which
sometimes I hope they deprecate eventually and move towards the
standard.
Because usually I think the standard is quite nicely...
Nikolay: Well, they behave differently, so many people got used
to on conflict.
Lukas: Yeah, I get the point of doing on conflict.
I mean, it's simpler for 80% of the use cases, obviously.
Nikolay: Yeah, also merge only recently received support of returning
clause, which is not standard at all, right?
As I remember.
Lukas: The SQL standard has the table, the data changed out of
the table, which was implemented by DB2.
And then some minor database products like H2 implemented as
well.
And it's not as powerful as returning, especially because it
only allows you to fetch either the data before or after the
updates for update statements, which is also part of merge.
You maybe want to return both versions to the version before
and after the update.
With returning in principle, that's, that's a feasible.
So Oracle implemented this recently.
I'm not in follow.
Did Postgres allow for accessing both versions of the row?
Nikolay: I don't remember as well.
I'm only like returning, well, if you DELETE, definitely you
have ability to return old thing you deleted.
Lukas: Well, there's only the old thing.
Old thing is deleted.
Nikolay: Let me quickly check.
Lukas: Why not?
But with updates, I mean, SQL Server always supported both versions.
So before and after.
And DB2 with the standard syntax supports only, you have to choose
which one, but you can't return both.
And Oracle 23 AI now supports both as well, like SQL Server.
Oracle uses the Postgres syntax, the non-standard syntax also
with the returning keyword, so maybe you can start from there
if you don't do it yet in Postgres.
Nikolay: I think in Postgres for updates we cannot return old
data.
We have access to it in triggers but it's different, right?
So only the new value can be seen there, but for deletes, it's
Lukas: probably more useful.
Nikolay: Well, I can imagine some cases when we want to report,
for example, what happened.
Lukas: Yeah.
When you do auditing, you want to have both.
Nikolay: Yeah.
Maybe let's add it.
Because
Lukas: standard supports this data change delta table, and it
accepts a merge statement as well.
So in principle, in the standard, you could have returning with
a different.
Nikolay: But it feels like more like a heavier approach in terms
of manipulation.
Returning is just like one line, right?
Return a star, that's it.
Lukas: Yes, yes.
Super easy.
It seems easier, yeah.
Also, there are tons of limitations.
I mean, you put your data change delta table inside of a SELECT
query, but then you can hardly do anything with that SELECT query
for instance, I'm not sure what exactly what is forbidden, but
unions I think are forbidden and joins are forbidden, so maybe
even aggregations.
I'm not sure anymore, but you're very limited with what you can
do.
So I'm not sure if there's any benefit with allowing that in
the SELECT statement as opposed to just returning.
But ultimately it's kind of the same thing as what Postgres does
when you put the ML statements in, in with.
So that that kind of behaves the same way, at least from a user
perspective.
Nikolay: Speaking of with, I know Juke supports CTEs.
Does it support recursive CTEs?
Yeah, yeah.
Yeah.
So everything, lateral join and so on, everything.
Yeah.
Cool.
Lukas: There's a high chance if SQL supports something and it's
not really very funky like the match_recognize clause, then Juke
will support it as well.
So match_recognize could be supported as well in Juke, but it's
such an edge case and still only supported in Oracle and some
very esoteric database products that Juke doesn't support yet.
So, so I'm skipping this for now, but with is, is everyone uses
with, so Juke supported as well, including recursive with, I
mean, there's some table valued functions that are like generate
series in Postgres, which have to be emulated elsewhere.
So if you want to ever, probably hardly anyone does that migrate
from Postgres to whatever, and you want to translate your old
generate series queries, then Juke will translate that to a recursive
with.
Michael: Cool.
Nice.
In fact, I feel like you skipped over that a little bit, but
that's such a cool feature that basically...
Translation?
Yeah, exactly.
We can get access to standard features that Postgres hasn't implemented,
or that whatever database we're using hasn't implemented yet
because you do the transformation, I guess.
How do you, translation did you say?
Lukas: Yeah, I call it translation.
So there's even, you can use Juke as a purely translate product.
So some customers actually do that.
Mostly when they migrate from Oracle to Postgres, you can just
embed Juke as a JDBC driver into, into your Java application.
And you don't actually have to use Juke directly.
So let's say this is a legacy application and uses JDBC or something
JDBC based like Hibernate or MyBatis, and you just put Juke
in the middle as a translating layer and probably 80% of your
queries will work directly on Postgres.
Of course, there's always edge cases and you have to do testing,
but at least your migration project will go much, much faster
because you can directly use Oracle queries on those other database
products.
Like for instance, Oracle has connect by and that can be translated
to with queries with recursive.
That's very hard.
So some cases don't work, but maybe many cases do.
And you don't have to actually look at those anymore.
Or Oracle special outer join syntax can be translated to left
joins in, in, in standard SQL.
So your migration just goes much faster.
You can also use it not as an embedded library, but just as the
web, the website is free or you use it locally as a CLI tool,
which is to have files, you have input files and Juke translates
those directly.
And you can manually check if it's correct.
So translation is a, is a, I'd say minor use case.
So a lot of companies work with only 1 database product and maybe
10 years, they, they might reconsider their choices and migrate.
But even then most, most companies stick with the database product
they started with.
And so many Juke users don't actually use this feature, but some
do.
Nikolay: May I ask slightly off topic question about JDBC since
you spent a lot of time with Java, obviously.
There is such thing as extra float digits.
Right?
Lukas: Extra what?
Nikolay: Extra float digits.
Okay.
There is such setting, and by default it's 0 in Postgres.
Or 1.
Yeah, no, 1.
It's 1.
Lukas: I've never heard of it.
Nikolay: Okay, but yeah, I remember some bugs in my code, and
I usually try to use pure SQL and psql, and you know, if I can.
I Remember that Java developers implemented the very same thing,
same queries, behavior was very different.
And then I noticed that the DBeaver, who else, other Java-based
IDEs also behaved the same way Java application did.
It was not the same as I saw in psql.
It happened with these extra float digits.
If you didn't notice this, this is maybe some very edge corner
case.
But I wonder like, why did the JDBC keep different default is
like no layers of some defaults.
This can be very annoying.
But okay.
Lukas: Well, the main thing that you always see The main difference
when you work with JDBC is, as opposed to psql is, in psql you
usually don't work with bind variables, so you don't have the
whole kind of effect that you get when you have bind variables,
although in Postgres This might not be that big of a difference,
but when you work with JDBC, you always have prepared statements
and bind variables.
And let's say my background is mostly Oracle.
So with bind variables, you have an execution plan cache and
the cache will store some assumptions about your bind variables,
like a normalized distribution and expectation regarding cardinalities,
et cetera.
So your actual bind variable might be, if your dataset is skewed
and your actual bind variable may be completely different from
what Oracle expects.
And, and then the execution plan will be bad.
But from what I take, Postgres doesn't have this problem because
it doesn't have this feature.
So this would be 1 of the main differences.
But you were referring to some logical error, not...
Nikolay: Yeah, Default and values returned are different from
what I see in PC.
Yeah, but
Lukas: I've never heard of this.
Nikolay: Well, okay, maybe it's already fixed by the way.
It was like 5 years ago or so I saw it.
So, yeah.
Well, on the topic of JDBC,
Michael: I mean, do you have any issues with it?
It seems like it's a remarkable piece of software that's just
chugging along, but equally it doesn't get much love.
It doesn't get much love.
Like I only, I generally only hear negative things about it,
but it must be amazing given how much it's used.
Lukas: I think it's the best API that Java has, even better than
collections and everything else.
It's really, I mean, okay, maybe they stole it from Microsoft
from ODBC, it's kind of the same thing, but It's really the best
thing that Java has because it's such a good standard.
I mean, everything can build on top of it and you don't even
have to write SQL queries.
So there's, for instance, Neo4j, which is a NoSQL database with
their own query language and you can use JDBC with that database
as well as long as you wanna have some tabular results And it's
a very, very good abstraction of the network protocol layer.
And the reason why people hate it, it's not targeted at developers.
So you can use it directly if you want, But it's a bit hard to
use.
You have a lot of objects.
You have the connection object and you have to create a statement
object and you have to remember to close it.
And you have to remember to create a result set object and close
that as well.
And it's not integrated with the rest of the JDK libraries.
Like for instance, when you iterate the result set, you have
your own API methods instead of just a list or iterable.
So you can't use the for each loop on a result set, for instance.
So the ergonomics of JDBC is not, not up to date.
I once tried to convince the spec expert group, it's a mailing
list to, to update their API.
There's a couple of things that I think in, in at the time it
was Java 8, but even now you could improve a couple of things
without changing anything fundamental in JDBC.
For instance, you can skip the statement part.
A lot of times you don't actually have to think about preparing
the statement explicitly.
You have to do it when you want to reuse the prepared statement.
So you can save some time in case there's a resource or some
cache connection directly.
But a lot of times that's not the case in some database products
and a lot of times it doesn't even matter at all.
So you could just have a connection and run the query.
So that's a minor improvement that would just reduce 1 step for
each statement you write.
Yeah, but Oracle couldn't allocate any resources to such a project,
even if the people on the list agreed that many ideas were reasonable.
So I guess that's it.
It's an amazing piece of software for integration products like,
like Juke or Hibernate or MyBatis and everything else to build
on top of and all the drivers they just work.
So a database company can just publish a driver and it will work
in all kind of software.
For instance, if you, if you work with DBeaver, it just works with
all the database products, right?
So you don't have to think about how do you, how do I connect
to this new database thing, like the cows or, or DuckDB or whatever,
it just works.
All these database products, they just work because they all
use the same API in Java.
So it's really a wonderful piece of technology, I think.
So I totally agree.
Nikolay: Yeah, this is exactly where I saw this problem, comparing
to, I mean in DBeaver, comparing to Java.
And I realized, okay, it's also Java application, so yeah.
Good.
Lukas: Yeah, but that was probably just the driver bug.
I don't think it has to do with the database as an API.
I mean, obviously the drivers can have bugs.
Nikolay: Right, right.
So You see bugs often, right?
I mean, in this...
Lukas: Yeah, not necessarily in the drivers.
I mean, the drivers, they really solve a very basic problem.
So it's mostly about network connections and these kind of things
that no 1 actually cares about, unless you have to optimize something,
like multiplexing or whatever.
But I think this stuff kind of just works.
And when I find bugs, it's more SQL related.
So it's inside of the database and I do find a ton of them very
rarely in Postgres.
I have to say, I think in my career I found 2 or 3 bugs in Postgres
only, as opposed to hundreds.
Michael: We didn't pay you to say that.
Lukas: No.
It's really very good.
It's surprisingly good.
I mean, also, absolutely no regressions at all.
So if there's a bug, it's an edge case that no one thought about,
then it's really a very weird kind of thing.
Nikolay: Yeah.
By the way, how do you check regression?
Just conversion from...
Lukas: In the database products?
Nikolay: Yeah.
For Juke, for example, some code is written and then you just
check the conversion to SQL or you check results as well.
Lukas: No, you can't just search the SQL statement.
I mean, Juke's integration tasks have a standard database, which
has about 5 tables.
It's very simple.
So there's a bookstore with books and authors, and it's not a
big database.
So it has 4 books and 2 authors, 4 languages, this kind of thing.
But then I just know there are 4 books and they have these titles
and every kind of query has to return exactly the same result.
So there are so many queries being run in each database product,
maybe about 10, 000 queries, I think, and also update statements
and all kinds of statements that make the assumption that if
you have this kind of input database, then this must be the output.
Or also, if I create a table with Juke, then it has to have these
properties and throughout the API, it has to be the same thing
for every database product.
Nikolay: Right.
I just wonder, earlier you mentioned that usually database systems
maintain reverse compatibility, but sometimes it's broken.
For example, in Postgres 12, CTEs, before they were materialized,
like optimization fans.
So every step is materialized before 12, but in 12 default behavior
changed.
Right.
So, so is it something that Juke should care about or no?
Lukas: I don't think that's a logical regression.
I mean, it was a conscious decision and it's only affects
the performance.
There's no logical difference of the result.
So I mean, databases are allowed to do these kinds of things.
I re I recall there was once a logical change in the UPDATE statement
in Postgres, which was a incompatible change when you UPDATE
a row.
So you had used the row syntax, you use parentheses And you UPDATE
2 Columns at once and suddenly both required the row keyword,
but only there.
This was a very weird.
Nikolay: 1 more recently, 1 more change related to subqueries.
I don't remember exactly.
It was a demonstration included in the generate series, I remember.
And yeah, maybe it was like up to 5 years ago, but there was
some change which was logical as well.
But I understand, you're like SQL standard, don't care about
Indexes, care only about results.
Yeah.
Right, I see, I see.
Lukas: No, I mean, a juke doesn't make any guarantees at all
with respect to performance either.
So, so from a Juke perspective, Juke doesn't try to fix your
SQL.
If you write a really crap query with, I mean, crap, what does
it even mean?
So if you write a bad query with a lot of nested subqueries,
et cetera, et cetera, in principle, there's nothing wrong with
it.
Right?
So there's, you will find hundreds of blog posts that, especially
from, from old times when optimizers were really bad still, where
people advised against using derived tables, then they advised
against using correlated subqueries, and then, but there's nothing
logically wrong with these things, right?
So you can nest as many levels as you want, as long as it's correct,
it's correct, and the optimizer should figure it out.
And Who is Juke to judge you for your query style?
So Juke doesn't fix these things or even assert anything, and
it would be completely wrong for Juke to do these kind of things,
because you want to write exactly this SQL, and Juke should render
exactly this SQL.
You have your reasons, right?
So...
Nikolay: I understand.
And my question is, I'm curious, how do people usually approach
performance?
For example, if some query is written using Juke with a chain
of SELECTs from blah blah, And then it turns out it's slow.
And then we find the fix, and we need to adjust something in
the query if it was a rock SQL.
Is it usually simple to adjust a Juke version of.
Lukas: Yeah.
I mean, there's no difference.
I mean, you're probably going to use the, I'm not, I'm not sure
anymore what the Postgres version is called, but you have your
statistics views in your database, your performance schema, and
you query those to see what's, what went wrong and then you make
assumptions, you rewrite the query and you test again.
Maybe even using a benchmark, of course then the benchmark is
written in Java, but I don't see a difference when you write
jOOQ query or native SQL query, It's the same process.
Michael: I was watching a really good video by Kevin Davin, who
did a talk on jOOQ, but 1 of the things that he really liked
about the product was it logs out to the console, to the actual
SQL statement as well that it ran.
Lukas: When you do a debug level.
Michael: Yeah, which is super helpful for performance.
So if that was slow, you can then run it in your favorite editor
in PSQL wherever with EXPLAIN, ANALYZE, et cetera, et cetera.
BUFFERS.
Yeah, always BUFFERS, hot topic this week.
But yeah, so that seems like a really developer friendly way
of getting back to the SQL and then diagnosing the performance
issue.
Lukas: This was a feature from day 1.
My assumption was that when you develop, you have debug mode
logging.
And when you go to production, you switch that to info or even
warning.
So you're not going to have the performance penalty of re-rendering
the query.
It's even formatted, so you can actually see the query.
It's not just 1 line, which is when you execute the query, it's
just 1 line of SQL string, so to have it more compact.
But when you debug log it, the version contains the bind variables.
So you can just copy paste the whole query with the bind variables
in a formatted way and study it.
I thought that was what everyone wanted.
And Also you get the first 5 records of the result set also in
the debug log, because that's probably also what you want while
you develop at least.
Michael: Yeah.
In a pretty little table.
Nikolay: It's nice.
Lukas: So you can immediately see what's going on.
And even then, I mean, 1 of the things, 1 of the reasons why
I implemented it this way is when you have a huge result set
with a lot of columns, that bothers you while developing.
So you kind of start thinking, do I really actually need all
these columns?
Because if you don't, then you have a better debug log.
So I kind of think you have to punish developers as early as
possible for their performance problems.
I mean, jOOQ doesn't judge you.
It just gives you some tools.
Nikolay: Just giving you some convenience looking at logs.
Okay.
Yeah, that's interesting.
So, reduce number of columns.
Good.
Michael: Changing tact a little bit, I think you've got a really
interesting perspective on Postgres from a broader landscape
of SQL dialects and databases in general.
What kind of things do you see that are relatively unique to
Postgres?
Or do you see people in the Postgres world particularly using
or liking?
Lukas: I mean, the 1 thing that is very unique to Postgres, there
are 2 things from my perspective.
The first 1 is it's very developer-centric.
So you can see that with other database products like Oracle,
DB2 or SQL Server, they're very production centric, very operation
centric.
They used to base their entire sales on these kinds of things.
So this means you have a lot of production tools as well, which
are great actually.
So I'm still waiting for the like of Oracle Enterprise Manager,
if you know it, for Postgres where you can analyze production
workloads and, and, and query what, what kind of workload you
had 5 months ago, and it's still there.
It is still happy.
I mean, you have to pay for these kinds of extras, but it's still
there.
You can still analyze anomalies in terms of performance, what
happened 5 months ago.
So these kinds of tools are a bit lacking, but on the other hand,
because it's so developer focused, you have this whole extension
system, which is unprecedented.
And I haven't seen it since either.
So anyone can extend anything in any layer of the, of the database
product.
So you have these wonderful extensions like PostGIS for instance,
which is, It's not really an extension because it's also part
of the SQL standard, but it has been implemented as an extension
just, I guess, to show what is possible and such a huge additional
product can be made as an extension.
And you have indexes and data types and all kinds of things that
other vendors have to either offer out of the box or they don't
have it.
You can't extend Oracle really.
So that's really something very, very amazing.
But as I said, from the other perspective, from an operations
perspective, this is something that personally, I think Postgres
is still very much behind Oracle, for instance.
So I'm an Oracle guy.
I can't really comment on SQL Server or others, but the Oracle
database where I worked at before I made jOOQ, it could easily
handle very, very complex queries that produced 500 lines of
execution plan on billions of rows, and it would run in milliseconds.
And I don't think you can do that with Postgres.
I wouldn't risk it.
So we all remember the days when there was a hard limit of number
of joins in Postgres, from which, starting from where, you didn't
have any smart optimizer anymore to reorder the joins in the
right order.
I forgot what the limit was, but I think you had to be very careful
if you have more than 10 joins or something like that, because
then it would just join from left to right syntactically, and
this is horrible.
Nikolay: Join collapse limit should be 8.
Lukas: Yeah, that's the join collapse limit.
So, once you know these kind of things, obviously, make the right
choices, and you probably won't run into big issues, but it's
just, it's a very unique focus that I often missed in the past
from Oracle, for instance, or completely ignored developer experience
for decades.
It was like, you don't even have a Boolean type, right?
So this is hard for me to understand.
It's such a simple thing.
You think it's such a simple thing.
So very, very useful, but Oracle wouldn't have implemented it
until recently.
So this is a focus that Postgres has and few of the others had,
even MySQL wasn't very developer friendly from that perspective.
So this is very unique.
If developers were the only ones to choose the database product,
it's always Postgres, because I mean, you're just gonna implement
everything with Postgres.
You can write stored procedures, you can have your own indexes,
you can extend it yourself.
Probably you don't have to because someone already wrote an extension
for whatever you need.
Oh, the second thing is, it's amazing how standards compliant
it is.
So a lot of times, Postgres really waits until something is standardized
before they implement it instead of innovating.
Nikolay: Right now we are waiting on UID version 7 because of
standards.
Like it didn't get into version 17 of Postgres because RFC was
not finalized.
Every library already started to support it, but Postgres decided
to wait on RFC, which is like very conservative decision, right?
Lukas: Most of the times.
Yes.
So I think that's a good thing in the end, because the standard,
in my opinion, in most parts is well-written.
It's a very complicated document, of course, but I think in terms
of syntax, it's kind of everything feels like SQL.
So some database products think they have to invent some syntax
And it just doesn't look like SQL anymore.
It's just very weird, funky thing that doesn't fit the rest of
the language.
And the standard is very, very consistent, I think, in most parts.
And to wait for the standard to appear is a good thing.
So sometimes there's an exception to the rule, as I said, on
conflict was 1 of these exceptions.
JSON as well, in case of which I think it was something everyone
needed and the standard was severely lacking.
And Postgres just did their own thing.
So I kind of think a lot of people kind of regret the heterogeneity
of the JSON API.
So you have to always consult the docs to see if something uses
a JSON path, which is a standard thing in the JSON world, or
if it's just an array of path elements, or if this function returns
a table or a set of something, or whatever data structure.
So I think now with the JSON standard, this is going to be much
cleaner once it's completely rolled out.
So I'm not up to date where the Postgres stands.
I'm still not supporting that syntax, but from what I've seen,
it's going into the right direction.
Nikolay: Yeah, some parts support it, some parts I think are
still bending.
Yeah.
Yeah, because I
Michael: think A lot went in in 17.
Nikolay: Right.
Lukas: Yeah.
And speaking of this, I'm going to be really curious.
I saw a commit recently regarding temporal primary keys.
Is that correct?
And it is absolutely not the standard.
So this is very curious.
I'm very curious about this.
Are you aware of this commit?
Michael: I'm not.
Lukas: It's still on a branch.
I think it's not, it's not going to be merged, but I mean, Postgres
has these range types.
Right.
Nikolay: Right.
Lukas: And they're useful obviously, but the SQL standard since
2011 defined how you should write temporal databases, both by
temporal.
So you have system versioning and then logical versioning.
And this is something that is still lacking in Postgres.
And I think a lot of people would like to see that.
Nikolay: Yeah.
In Postgres, there is an opinion that it's in the past, because
it was
Lukas: in the past,
Nikolay: it was removed.
Michael: So that's a nice temporal joke.
Lukas: An extension I think?
Michael: It was in Postgres, the original Berkeley version.
Nikolay: This is how MVCC inherited from these versions
of tuples.
So your time travel basically was supported very, very
long ago, but then it was removed.
So like interesting idea.
Michael: I think for
performance reasons.
Nikolay: Yeah, So I think so.
But yeah, this is a super interesting, like specific topic.
Sometimes we do need to implement something with triggers and
so on, like, which is very
Lukas: Yeah.
Yeah.
It's very hard to do manually.
I think the standard helps a lot with, with, I mean, you can
write an update statement and it's automatically transformed
into a delete and insert.
So if you have to split the record logically, this is really
a pain to do manually, right?
So it's
Nikolay: not super pain because you can have, for example, Postgres,
you know, like I remember your very old post, everything is a
table.
Or maybe it was a post of some 10 tips or something, and 1 of
the tips was, consider everything as a table.
In Postgres, you can collapse all columns into a single record
type and insert.
For example, in JSON form right now, I would insert it and I
would have a shadow table.
I implemented this several times.
You have a shadow table which serves as like a guy for all tables,
which has, has trigger to track changes.
Right.
And then you, you just track timestamp action, like it was delete
or update, who did it, and then whole row you pack it in a single
column, breaking first normal form.
Lukas: But then you normalize in a very bad way, and secondly,
this sounds like a lot of work.
Nikolay: It's not a lot of work, it's like 3, 5 lines of code,
that's it.
But it's not possible in jOOQ 3 version because triggers are
not supported, right?
Triggers are supported only in Enterprise version.
Lukas: I mean, if you want to write the trigger with jOOQ, yes,
then you have to use the commercial additions, but hardly anyone
writes triggers with jOOQ.
I mean, this is only useful when you have to have, when you support
5 database products and you want to have, you want to write the
trigger only once you write it with jOOQ.
But I mean, jOOQ doesn't care if there's a trigger on the database
table, right?
So if there's 1, so it's transparent to jOOQ.
Yeah, okay.
You can't really say jOOQ doesn't support them.
It's just, you can't create them with jOOQ.
Nikolay: Great answer.
Free version, you mean like an
Lukas: enterprise version?
Free version, yeah.
Nikolay: Yeah.
I'm very curious.
I'm not against this.
It's good.
I mean, it's great that you build both like open source product
on Apache tool license and business.
I was just curious, like, have you ever had thoughts about going
full closed source or fully open source?
Well,
Lukas: it was fully open source in the beginning.
So when I started this, I had no idea this could be a business.
I mean, I just had to try it and then put it out there for people
to see and give me feedback.
Is this a good idea?
Should I do it like that?
And there was a very active early community of people who worked
with the all open source jOOQ.
And then I started seeing even more and more banks and insurance
companies using it, but never contributing as you know, the story.
And I thought it was my fault.
Nikolay: I get it.
Pong Jones, let's, let's generalize Oracle users.
Lukas: Well, that was the choice I made, but, but it doesn't
matter.
You know, It doesn't matter what they're using in the end.
I just never got any...
I mean, I got contributions, but I didn't care about code.
I cared about money much more than code.
So, to make them a sustainable business, in the end, I thought...
There are models where you pay for support, but in my opinion,
if you want to make money with support, then you have to earn
your money by working for it.
So I earn money when I work, and if I have a licensed model,
then I earn money when customers work, right?
So I thought this was going to scale much better.
Michael: I like that a lot.
I also think if you charge for support, you're incentivized to
hurt the experience.
Lukas: And I think, but you're going to say that, but that's
how I think.
Michael: But I think, I think it's not surprising that as somebody
that focuses so much on developer experience and wants to keep
pushing that forward and making that good, it's unsurprising
that you don't want to be incentivized to make it bad like that.
It makes perfect sense to me.
Lukas: Yeah.
I mean, that's a, that's my thought as well.
I, I don't want to name companies or names, but I've heard some
open source products are very, very unusable and you really need
the support and not all of them.
Of course.
I mean, Postgres and Linux are examples, which aren't, which
this isn't the case, but there are some products that are super
complicated and they're just pushed out there and you're free
to use it, but you won't figure it out.
So you have to call someone even to install it and that's what
I'm thinking.
So in those cases, you need a sales representative that pushes
the product down someone's throat and they make a lot of money
with the support contract.
And I think it's less honest than licensing, which people think
about, okay, is this a good product?
Should I really spend money on it because I have to pay it up
front right from the day 1 I have to pay money to use this product
and they're gonna test it really really really well and there's
a lot of competition so I mean people could use hibernate or
whatever but people choose juke despite the cost which in the
end is very cheap for a company.
So I think cost is never the issue, but, they're still going
to evaluate it because they have to still go through purchasing
and make it a business case for, for, for the purchase.
So I really want to have a very, very good experience.
And 1 part of this is I also always answered all the questions
on Stack Overflow within a short period of time, because if I
answer it, it's gonna be authoritative and I'm gonna answer with
all the details and all the caveats and all the edge cases.
So anyone else who finds the question, we'll find this question
and the answer.
And they're not going to ask me again.
Right.
So Google is my friend and now Chat GPT is my friend as well,
because Chat GPT can answer jOOQ questions very well because
of all the answers I've given on Stack Overflow.
Nikolay: Yeah, but open knowledge, right?
Lukas: Yeah.
I don't want to earn money with knowledge.
I mean, I could sell books and all these kind of things, but
it's just the wrong kind of effort, I think.
Nikolay: Destruction, right?
Yeah.
From the core.
I wonder for enterprise, like paid versions, is source available
or it's like kind of
Lukas: yeah you get the source code
Nikolay: well that's great
Lukas: the right to modify so you can if you have a bargain and
Nikolay: you can fix it yourself and propose a fix.
Lukas: Or maybe it's not even a bug, but you think it's a bug,
but you disagree, you can fix it.
And some companies do that.
So this
Nikolay: is what we like in commercial software, like full transparency
and see how it works
Lukas: inside.
I mean, most people don't actually fix stuff, but maybe they
want to debug stuff to better understand it.
And if you've ever worked with a commercial JDBC driver, like
I do, with the Oracle driver, it's always like, Oh my God, there's
a null pointer exception in the middle of the driver and I have
no idea what I'm doing wrong from an API perspective.
So I have to reverse engineer it.
Not, not with the code, but just try an error with API calls
until I find out why the exception, why the bug inside of the
driver happens.
And with source code, it would be much easier, right?
So bugs happen, but if you know exactly why the bug happens,
then you have a much easier life than if you have to guess.
Nikolay: Yeah.
Last question from me.
What do you think about nulls in general?
Lukas: Nulls?
Nulls, yeah.
Or they're a blessing in a curse.
I mean, I understand why they have been introduced, because it
was easy to do, right?
And I mean, There are 2 takes here.
So nulls in most languages are mostly a pain because they lead
to exceptions.
And null in SQL is mostly a pain because it doesn't lead to exceptions.
It's just a value like many others, but it's a special value
and it just propagates.
It's these kind of philosophies and both are right and wrong.
But what else would you want to do, right?
I mean, you kind of need an absent value, right?
I'm not sure if the idea of the unknown value is so useful, but
an absent value is very useful.
I mean, sometimes you just, you can't normalize everything to
6 normal form to avoid NULLs, right?
So it's very pragmatic.
Nikolay: It's very pragmatic indeed.
Yeah.
But in SQL, we have so many places where people make mistakes
all the time.
Lukas: Yeah, SQL is weird about it.
I mean, this unknown value, I used to do a SQL training and it
was about 30 minutes about it was just NULLs and how it's different
here from there.
For instance, 2 NULLs are not distinct, right?
Nikolay: Yes.
Lukas: But they're not the same either.
So there are different terms in the SQL standard, same and distinctness.
And then even operators are inconsistent.
So personally, I think Oracle did the pragmatic thing when they
concatenate a NULL value to a string, you probably don't want
the result to be NULL, I think.
You would just want to ignore the concatenation.
This is pragmatic, but it's very unlogical and leads to a ton
of side effects, which probably means that it was a bad idea
to start with.
Nikolay: And there are NULLs in other languages, I mean, in regular
application languages.
And I remember how I struggled trying to explain this 3 value
logic and like, observing what people do in code, in regular
application code with NULLs.
They do things I would never do like in SQL, right?
So different philosophies, right?
Lukas: Yeah, there are different philosophies.
But I'm not sure if the SQL philosophy is really useful.
I mean, I'm trying to explain how it works by explaining NOT IN
in the NOT IN predicate.
When you have a NULL in NOT IN, everything falls apart.
I mean, it's very logical and consistent.
You can explain it with 3 value logic, but it's never useful,
right?
This kind of invalidates the whole predicate.
Nikolay: Yeah, it's a bomb.
Lukas: It's a bomb?
And I mean, usually people don't put a NULL value in a NOT IN
list, but what about a subquery?
So NOT IN SELECT something, and then you have a NULL value in
there, and you don't get results.
And it's not obvious from just reading the code or the query.
there, and you don't get results.
Why?
It's horrible.
Horrible.
We
Nikolay: had the whole episode about no, so if Michael long ago,
yeah, yeah, let's stop because definitely it's not, it's not
going to go away.
And I, I'm quite sure I will make more mistakes with nulls in
my life.
Lukas: But maybe the 1 thing I really think SQL should have done
differently, and there are some database products that actually
do it differently, is the default data type should be not null,
right?
So when you create a table, When you don't write anything at
all, then it should be not null.
The fact that the default is nullable is wrong.
Nikolay: This forces you to define default.
Or, well, no.
Michael: The default word is confusing.
You mean, if I create id int8, I shouldn't have to say not
null.
Lukas: Yes, you should have to say I want this to be nullable,
because then it's a conscious decision and they know what you're
doing.
But the fact that maybe by accident you have something not null,
something nullable.
Yeah.
It's just wrong.
Nikolay: And this isn't standard, right?
Or if you remember?
Lukas: I don't remember.
I think maybe the standard says it's implementation specific.
Nikolay: If it's a standard, We definitely
Michael: can't change it now though.
Lukas: No, you can't.
But I remember Sybase is 1 of the database parts that does it
differently and ClickHouse as well.
Interesting.
Another 1 I forgot.
Nikolay: So in ClickHouse every column is by default not null?
Yes.
That's cool.
Okay.
Lukas: Although they messed it up as well.
So you can still insert null in a not null column.
You just get a 0.
What?
It makes it worse.
Nikolay: Okay.
Okay.
Lukas: That's terrible.
Amazing.
I think this is a performance idea.
You actually, if you just ignore the check, you kind of don't
check for NULLs, then you're faster, but whatever.
Michael: Lukas, I'm super conscious of your time.
Is there any last things you wanted to say?
Lukas: I hope there are many many more exciting Postgres releases
in the near future.
I'm looking forward to all the good stuff that's coming.
Especially if it's ever coming, temporal tables from SQL 2011,
that will be very exciting.
Michael: Nice, okay cool.
Nikolay: Thank you for coming, I enjoyed.
Lukas: Thank you for having me.
Nikolay: Thank you.
Michael: Yeah, very much so, and catch you next week Nikolay.
Nikolay: Bye bye, have a great week.