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.

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.