Michael: Hello and welcome to
PostgresFM, a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard.

And today I am joined by Markus
Winand, creator of the popular

sites, 'Use the index, Luke,' Modern
SQL, author of SQL Performance

Explained.

Performance trainer and consultant.

And according to your LinkedIn,
SQL Renaissance ambassador.

It's a pleasure to have you here
today, Markus.

Thank you for joining us.

Markus: Yes, thank you.

Michael: Awesome.

Well, I suggested the topic this
week, but it is very much your

topic.

It's modern SQL.

And I'd love for you to give us an
intro into what you mean by modern

SQL and also how you came to be
such an ambassador for it.

Markus: So, it basically goes back
when I realized that many,

many people think that the old
SQL, so what is more technically

described as SQL-92, is pretty
much the only SQL.

Like we have joins, yeah, we have
them for decades and people

think that's it because that's
what they learned like decades

ago and so on.

And they didn't realize that the
story didn't stop there.

It went on.

And we got what I refer to as more
modern features, which are

not rooted in the relational idea,
like window functions or recursive

queries, or more modernly like
JSON support, document support

at all.

We have XML for quite a while in
SQL.

This is what I kindly refer to
as, yeah, well, modern SQL, as

opposed to the grandparents' SQL,
everybody has learned like decades

ago.

Michael: So do you see this more
as an issue with people that

have been around for a few decades
and got stuck in their ways?

Or do you see it also with new
folks coming along and getting

taught by those people or the lessons
they're learning only include

the most basic, rudimentary SQL?

Markus: I would draw the line a
little bit differently there.

I think the problem is more related
to ORM users and SQL users.

So you see SQL users, there are
also 2 kinds of users, those

who just learned it once and never
got an update.

That definitely exists.

But I think the most problematic
area is actually in the ORM

world or rooted in the ORM world,
where the people just really

think because the ORM tool is doing
the best because well, they

are specialized in using SQL, all
sorts. The ORM tool.

So this is probably the best you
can do.

But that's unfortunately totally
wrong.

So the whole idea that SQL is just
about relational things is

just outdated.

That was right in '92 but it was
already wrong in '99 and somehow

all the people managed to miss
that message.

They didn't get the memo, so to
say.

Michael: Yeah, that's such an interesting
point.

I hadn't put 2 and 2 together with...

I've listened to your talks before
on this around the change

from being relational only to being
able to handle other data

types or other data models.

But the R in ORM, is that what
you mean by the relational part

of it?

Markus: Yeah, also the whole idea
that the database is just for

persistency.

Look at Java, JPA, the Java Persistence
API.

Just the idea that the database
just stores data.

That's a wrong idea for SQL.

It might be a right idea for some
other systems.

But for SQL, the fundamental idea
of SQL is that we have a separation

of the persistent data layout and
the transient data layout that

we need for 1 query right now.

Because 1 other thing that I like
to put emphasis on is that

SQL is not a query language.

SQL is more like a transformation
language.

If you look at query languages,
Go to any web page, open the

CSS and look at the selectors of
CSS.

This is a query language.

A query language can pick out 1
small piece of something larger,

and that's it.

But now look at SQL.

With SQL, we are most of the time
focusing on transforming data,

transforming the persistent data
we have stored on the disk to

last forever, more or less, to
answer a question we have at hand

right now.

Michael: Yeah.

Markus: That might be a different
question tomorrow.

It's most certainly a different
question.

And the whole idea of SQL is that
we normalize, yeah, there is

this bad word, normalization, we
normalize the data persistently

on the disk so that the shape of
the data does not change often.

Yeah, the shape not, the data itself,
yeah, the contents of the

table, but not the shape.

Not so often.

Now compare how static is the on-disk
schema, if you have probably

normalized it, compared to the
questions you try to answer with

those data.

You get every day new questions
from marketing or from reporting

or from whatever.

And the idea of SQL is to have
a flexible transformation between

the rather static schema you have
on disk, because once you have

collected terabytes of data on
disk, then it's hard to change

The model you have there.

No matter how you do it, it will
be hard, compared to the dynamic

we have on the question side.

SQL is the glue between these two
layers.

Michael: One thing you mentioned
there is normalization being a

bad word.

I'm curious what you meant by that.

Markus: Yeah, with normalization
we have all of us have learned

these levels.

First Normal Form, second normal
form, third normal.

Okay, of course, that's totally
correct and useful.

But to another extent, for the
layman people, just causing more

confusion than it is helpful.

What I like to explain normalization
is just, okay, don't focus

on the third normal or on any of
those.

Just try to figure out in which
tables would the data feel comfortable.

And naturally there are some things
like if you have a one-to-many

relationship, then it needs to
be two tables in SQL.

Just keep it on that level.

Don't go into the scientific definitions
of all of these six normalization

levels and some extras.

Try to keep it simple and you will
still get the gain from the

normalization.

So that you have a rather static
schema on disk.

Michael: I really like in what
tables would the data feel comfortable.

I'm going to steal that.

I think that should be on T-shirts.

If you make them, I'll buy them.

Awesome.

Right back to the modern SQL topic
a little bit more.

You mentioned that a lot of people
are stuck a few decades ago

with very basic SQL.

The counter argument might be,
well, they're solving their problems.

They can do a lot of things.

What are the big downsides or limitations
for those folks who

haven't explored some of the newer
features that have been added.

Markus: I'll give you an example.

With SQL '92, it was quite common
to do self-joins.

It was required to do self-joins
because some problems could

only be solved by using a self-join.

But self-joins are troublesome
in two ways.

First of all, they are complex.

And second of all, they are slow,
because they are touching every

data item twice.

Michael: And

Markus: now we have pretty much
for every situation, we have

some other syntax, most importantly
window functions.

Window functions are one of the big
self-join killers.

And now they become, actually the
syntax is more concise.

It's more clear actually when you
look at it, rather than scratching

yourself this way, you just scratch
yourself this way.

So it's actually better to read
the query.

It's actually also better to write
the query once you know the

syntax.

And it will perform better.

And you will have less maintenance
nightmares because you don't

have the redundancy in the code.

Like with a self join, if you add
a condition to one of the tables,

you might need to add it to the
other one as well or to the join

condition.

And this is easy to forget, then
you get wrong results.

Michael: Yeah, I love those.

So clarity and performance.

So clarity not just for yourself
writing things, but also for

people coming back to it, reading
it yourself in future.

And performance is the man after
my own heart on that front.

I think a lot of people assume
that, or looking at new features,

they assume they'll get new functionality
and forget that those

other two things are the bigger part.

And because of the way SQL works,
we likely could have done it

before.

That's a really good point.

Markus: But it's actually true
because SQL 92 was from one perspective,

it was quite complete.

Michael: So it

Markus: was completing the relational
idea.

And of course, you can solve almost
everything.

Asterisk recursion.

Almost everything with the old
SQL.

And that also makes it difficult
to learn new stuff because,

well, I can solve it already.

So why do I need to learn something
new that worked for me like

decades?

It's still working.

So what is it about?

And that's, of course, a little
bit more difficult to explain

people.

Michael: Well, I love how simply
you did it.

So clarity of what's happening
when you look at the SQL and performance.

What can they what choices
does the planner have when executing

that?

Yeah, really cool.

And also, I guess, edge cases as
well.

These features can be designed
to be more...

Well, we've done whole episodes
on handling nulls.

And they can have thought about
those more than, or if you have

to roll your own versions of these
features, you have to handle

nulls yourself, which can be painful,
to say the least.

Markus: So one of my favorite features
in this regard is the is

not distinct from operator.

It's the null-safe operator so
that if you have nulls on

both sides then you still get true.

Yeah, you could say it's a modern
SQL feature.

According to my definition, it
was added after SQL '92.

And if you look at on my website,
Modern SQL, you can check out

which systems support that operator.

And yeah, unfortunately, not so
many.

Michael: Interesting.

Markus: Although I consider it
something quite essential.

But still, also the vendors didn't
get the memo that there is

something new they need to catch
up with.

Michael: There I ask, I actually
haven't used it.

Is it in PostgreSQL, do you remember?

Markus: It is there.

Yes.

It does work syntactically, you
get the right results.

Michael: Okay.

Markus: But there are some gotchas
when it comes to optimization.

Michael: Interesting performance.

There we go.

Markus: So I have had just like
a month ago, I've had a case

where I would like using it, but
it didn't work out for performance

reasons.

Michael: That's so good to know.

That's very, very interesting.

I love those charts on Modern SQL.

When you look up one of these
newer features, and you have the

charts of one row per database,
per DBMS, and then a timeline of

whether it supported it, at which
point it supported it, whether

there are any caveats.

And every time I look at that,
I think, wow, this is impressive.

But also, this must be so much
work to maintain.

I was gonna ask, how do you do
it?

Markus: Yeah, I'm happy you asked
that because this is one of the

untold stories.

So I'm just a one-man show, and doing
the job site as a side project,

actually.

I have kind of a day job as well.

Yeah.

So the way I make it nowadays is
that everything is covered by

tests, by tests that I can run
automatically.

I test very deeply, like I always
check for the null case.

I always check for collations.

I always check for this and that
and a lot of, down to the SQL

state.

And yeah, well, then I get the
result.

And if everything is going fine,
then updating the website for

a new version takes me like maybe
an hour of work.

It really depends on the system.

Like I'm also featuring Google
BigQuery, which is a cloud-only

system.

You can imagine that I have higher
latencies compared to the

VM on my box.

So for that, it takes a little
bit longer to run the tests.

But after that, it's basically
just saying make and everything

is rendered again and updated again.

And yeah, well, that kind of works.

And now I'm working on getting
actually more features in there.

Michael: Oh, nice.

You mean, so what's coming up?

Markus: Yeah, so I try to catch
up, like, of course, I like to

focus on the modern SQL features.

Michael: Yeah.

Markus: And I also like to focus
on features that are already

implemented in some systems, because
this is more valuable, of

course.

And I also have always an eye on
the new features added by some

systems.

Like if we talk about Postgres,
I think it was even in 15 that

we got the functionality of the
unique constraints where we can

choose how it should treat the
null values.

Michael: So this is whether you
can determine that you are only

allowed to have a single null value.

Exactly.

So

Markus: it's the nulls distinct.

Let me just check up my own.

Michael: Oh, I like it.

Markus: Nulls distinct.

So yeah, it was introduced with
15.

So now you can choose for a unique
constraint if it has a nullable

column, whether one null should rule
out other nulls or not.

And that was now added to the new
standard of SQL we have in

the year 2023, so last year, we
got a new standard and it's in

there.

But even a year before it was put
into the standard, it arrived

in Postgres.

Michael: Oh, nice.

Markus: So, this is kind of the
features I try to catch up

with the new stuff introduced in
the standard as well as in newer

releases of the systems.

Michael: Awesome.

I watched a talk of yours, I think
it was about 5 years ago now,

though.

So I've only heard your opinions
of like the previous SQL standards.

not the latest one.

What were your main thoughts on
2023?

Markus: So 2023, it took quite
a long while.

The one before was 2016.

So that's longer than intended.

So that there's a target of like
4 to 5 years, something like

that.

Oh, really?

Cool.

The big thing in the latest release
of the standard is the query

language for querying graphs in
a more native syntax.

So if you know the Cypher query
language, then you get the idea.

It's basically about drawing with
SQL a path through a graph

that you want to match on.

And there is now a standard of
its own next to SQL, which is

basically independent of SQL.

So it's a new language, a new international
standard that defines

the query language, generally the
language to work with such

databases, like how to insert something
into a graph and how

to query the graph.

So this is kind of independent
of SQL, except that the same people

who do the SQL standard also do
this new standard.

And obviously, we would like to
have that capability also in

SQL.

So additionally, besides that entirely
new standard, there is

now a new part of the SQL standard
because the SQL standard is

actually made up of several parts
and now there's a new part

so that we can use the query functionality
part of the new standard

inside SQL in our queries.

Basically in the FROM clause, we
have match operations and we

can use that SQL-art kind of querying
directly in SQL.

And that's most certainly the killer
feature of 2023.

But of course, it may take a while
until vendors catch up.

There are some commercial implementations
are available, but

they predate the standard.

Therefore, they don't implement
the standard down to the last

sentence.

So I'm curious how this evolves
over time and if the standard

can still have the power to unify
these languages.

That's something that we have to
watch out for.

But besides that one mega topic,
and that's probably the reason

why it took so long.

Besides that, we have got many
small things like the null handling

that I was mentioning.

One really nice thing is that we
can now put into literal numeric values.

So if you write a number like 1000,
you can now put in the underscore

as a separator for let's say thousands
of digits.

So that you can easily spot when
it's a million or a billion

what it is, so that you can make
it more readable.

You see, it doesn't change anything
what you can do with the

language.

It just makes it nicer and more
maintainable.

And there are also, in respect
to JSON, there are also some additions,

actually quite fundamental additions.

Now, the standard has an actual
JSON type.

It didn't have one before.

The 2016 standard introduced a
lot of JSON functionality.

Most importantly, the JSON query
language.

That's a query language, the JSON
parse language, but it didn't

introduce a type for JSON.

And now with 2023, they have added
the type for JSON, along with

some other simplifications and
extensions.

So there are quite a few small
enhancements that just make everyday's

life easier.

But the big thing is I think that
the property graph language.

Michael: Yeah, makes sense.

I imagine that's gonna be a heck
of a lot of work for folks to

implement.

But well,

Markus: If it's gonna be implemented
at all.

True.

Because the SQL standard, it has
the core functionality which

every product is supposed to implement
and by testing it, I know

it's not working out.

And on top of that, it has optional
features.

And all the advanced features are
basically optional features.

So vendors can choose to either
implement it or not, but if

they implement it, then they should
follow the standard.

Michael: Got it.

Okay.

And I think I read somewhere, but
maybe this is misguided or

outdated, that vendors can claim
that they support SQL if they

support SQL 92.

But how does it work for the non-optional
features for the core?

Yeah.

Markus: True.

The short answer is not at all.

Okay.

Yeah.

And that's part of the story behind
modern SQL and the charts

you mentioned.

I think a standard is nice.

Okay.

But if nobody cares about it, then
it doesn't have any power

to unify the language.

But as soon as you see the differences
and the mistakes the products

have, then there is a motivation,
then there's an outside pressure

to actually comply with the standard.

And I can see it, I've actually
noticed it today, that when I

publish something on my website,
the vendors are looking there.

Michael: Nice.

Markus: And they are questioning
when I say, okay, it doesn't

work in that product, then they
ask, hey, I thought this is supposed

to work, why not?

And then I can show them the test
case I have and that it doesn't

work.

And some of them fix those issues
quite quickly, like in 2 weeks.

Michael: Brilliant.

Markus: So sometimes it just happens.

I publish a new article mentioning
10 systems and 1 of them is

behaving differently, maybe wrong.

And just coincidentally, 2 weeks
later, there's a new release

by that system that behaves correctly.

So that happened, yeah, that happens.

Michael: Would you like to give
any kudos or praise to the folks

that are managing to turn those
around so quickly?

Markus: In that case, it was the
H2 database.

The H2 database, yeah.

On the other hand, of course, there
are systems that have longer

cycles and then it takes longer
or maybe happens never.

But on the other hand, if I look
at Oracle, if I may talk about

Oracle briefly.

Michael: Yeah, yeah, please do.

Markus: The latest release, the
23 release, finally fills the

gaps that are there for decades.

Like they finally introduced a Boolean
type.

They have ignored it for so many
decades and have just said,

okay, make it a char 1, why?

No, and that's fine.

And now ultimately, there's movement
coming in there.

Even from Oracle.

Even from Oracle.

The whole industry has actually
much more movement than like

10 years before.

Like that MySQL is getting Window
function support and recursion

support and all of that was something
I didn't expect actually.

When I was starting with modern
SQL in 2015, it was rather static.

There were a few products doing
the modern stuff and there were

a few products not doing the modern
stuff.

These were the two things.

And I did not expect the non-modern
stuff to catch up.

It was more like, yeah, well, they
will stick there forever.

But then ultimately, they started
to get a lot of dynamics into

the language support that they
offer.

Michael: Nice, and we all benefit,
right?

And thanks for bringing up Oracle.

I think they're a great case.

I think they often are one of the
first to support some of these

newer things, even if they have
had in the past some of these

gaps.

It definitely gets brought up quite
a lot when people choose,

like when people mention why they
chose PostgreSQL, standard

compliance and things working as
expected, which aren't exactly

the same thing, but quite close
to the same thing, gets brought

up quite often as one of the reasons
they trust Postgres over other,

at the time, open-source databases,
but even yeah, commercial

databases.

So it is important and we all benefit
ultimately if everybody

can work together on these things.

If it works as expected.

That's cool.

Other than your site, of course,
being very important in the

shift, what else do you think might
have helped with this change?

Markus: I think the MySQL move
is actually pretty important because

now the most commonly used database
actually in the world, besides

SQLite of course, but even SQLite
has moved.

Those systems supporting these
features means these are not exotic

features of a few vendors.

I think as soon as this is there
in the MySQL documentation,

then it becomes more official being
SQL rather than just being

Postgres or Oracle or whatever.

I think the fact that MySQL 8
got all of some of these modern

features makes quite a big difference
how people look at these

features.

Michael: Yeah, that was a huge,
huge release.

I remember MySQL, it was one of the
8 releases that included and

this is not related to SQL, well
not related to SQL standard

at all, but they got explain, analyze as well, which was a

big step forward in the things
I look at.

So yeah, huge, huge release.

And we're going to get hate mail
for this, but perhaps Oracle

deserves some credit for that now
being the stewards of the MySQL

codebase and project.

Markus: Yeah, I was not expecting
that when MySQL ended up at

Oracle.

I was rather expecting it like
being just come over to the real

database like this kind of thing.

But that didn't happen.

And if we now look at MariaDB,
which is basically the continuation

of the old team and at MySQL, then
we can see, well, they diverge

quite a lot.

And while MySQL was rather focusing
on getting the basics right.

MariaDB is more focusing on getting
features in.

And on the long run, I think the
basics are worth it.

And I think we can see that with
Postgres because in Postgres

the basics were right from the
beginning, I would say, at least

most of them, and that pays off
on the long run.

So I'm also happy to see MariaDB
being still there.

It's another competitor, especially
against MySQL in the open

source world.

But on the long run, let's see
how long they can survive.

Michael: Yeah, and as users, we
benefit from the competition,

right?

Like, obviously as community members,
I'm definitely very biased,

but I do like, I want there to
be competitors.

It would be, I don't think Postgres
as a project would benefit

from being the only relational
database standing in 20 years

time.

Historically, I don't think that's
been the place where most

innovation happens or the most
like customer benefit happens.

So, yeah, I love to see it and
appreciate your work on this as

well.

So I did.

I didn't want to minimize that.

I do think you have had an impact,
but it's cool to see other

reasons as well.

Markus: Of course, there are many
reasons for that.

Yeah.

You also see the noSQL movement,
remember?

Yeah.

In the meanwhile, most of the noSQL
vendors that are still there

have some QL, whether they claim
it is SQL or it is just some

similar QL.

Then we have new vendors that start
up with new SQL dialects,

you know, DuckDB and so on, that
also bring fresh ideas into

the SQL dialect.

I think some of the ideas they
are bringing up into their own

dialect are actually worth thinking
about bringing into the standard

and maybe other products like in
either way.

So, as I said, there's a lot of
dynamics at the moment.

Michael: Let's go back to what
you would like to see.

Let's say if somebody's like, well,
you know what, I do mostly

just use the SQL I learned when
I first learned.

Yeah, okay, maybe I do a bit of
JSON stuff here and there.

I've learned, I've had to use a
lateral join or window function

once, but you know, that's about
it.

What should they be doing?

How could they go about learning
more?

Markus: Well, this is pretty much
the same for the last, not

20, but 10 years.

The most undervalued feature of
modern SQL is and always was

window functions.

If you don't master window functions
yet, then well, stop listening

now, we will wait for you and learn
window functions, yeah?

And then continue listening.

So this is really the single most
important and powerful feature.

That's definitely my recommendation
if you are not into that

yet.

Other than that, let me give you
1 problem I see more often nowadays,

to change the mind about how to
use new features, because you

we're mentioning JSON as well.

Yeah.

I see JSON used in a way that
I'm not sure it's the best way.

Michael: Okay, great.

Markus: Quite often.

And the way I'm explaining it is
that data types like JSON or

JSONB, or even like Boolean, yeah,
they have several use cases,

actually 3 use cases.

A data type can be used in the
persistent schema.

It's in a great table, you can see
a data type.

But also important, a data type
can be used during the transformation.

And I'll give you an example there.

Like the Boolean type, we use most
of the time just during the

transformation.

Because in the WHERE clause, in
the ON clause, in the WHEN clause

of case, and so on, and so on,
and so on, we have Boolean expressions

and we're using Boolean values
just during the transformation.

It's not coming from the base table
from the disk.

It's not going up to the client
through the wire, just for the

transformation.

That's the second use case for
data types.

And the third use case is the API
layer.

So meaning how do we communicate
with the client?

And of course we think about a
tabular representation.

Yeah, of course, of course, of
course, and maybe a normalized

1.

But here, I say, no, the normalization
is fine for the disk,

but it's not so much fine for the
API layer.

And here I love JSON.

Yeah.

Because if you think of a tag system,
you have some entities

and you can add tags on them.

Then obviously the normalized way
to do it is to have 2 tables.

I still think that's a good approach
in many, many cases.

But on the API layer, if you join
them, you will get multiples

of the entity data, which is meaningless.

That doesn't make sense.

But here comes basically the JSON
array_agg() and you aggregate

the tags into a JSON field that
you then give to the client API.

And then you just keep the 1 row
of the entity and have the list,

a list, yeah, of text there.

And here you see, this is where
I personally think that that's

the most important use case for
JSON.

It's not about storing JSON on
disk.

Yeah, that can make sense sometimes
as well, no question.

Yeah.

But the main use case I see for
JSON is on the API side.

Michael: Yeah.

Or for what it was originally intended,
right?

Markus: Yeah, maybe.

But what I see the people when
they say, yeah, we're using JSON,

what they mean is they store it
on disk.

Michael: Yes, yes.

Good point.

Awesome.

Well, is there anything you'd point
people at?

Have you given a talk on anything
like that before?

Any good materials for people?

Markus: Not so recently, you know,
there was this corona

break.

So no conferences.

I'm still not talking a lot in
public like there was another

podcast recorded a German one like
a few months back, but that's

it.

So follow my blog, modern-sql.com
and also use the indexloop.com.

This is where I publish my updates.

And what you can expect is basically
these matrices, who can

do what, use cases for the features
that I test.

That's of course also important.

What is the benefit of knowing,
okay, that feature does work

in that system, but I don't know
what to use that feature for.

Yeah, well, that's what you can
expect on a more or less regular

basis like once a month or twice
a month, something like that.

Michael: Nice, and I think I just,
might have even been this

morning, was reading your half
year update as well, which was

cool.

So yeah, highly recommend those
resources.

We've shouted them out a few times
on the podcast in previous

episodes, and your book as well,
SQL Performance Explained was

fantastic.

So thank you for that.

Actually on that note, when's Modern
SQL the book coming?

Markus: Yeah, well, I started in
2015 with the intention to make

it a book.

Michael: Oh really?

Markus: Yes, that was the original
intention.

So when I did the other book, the
SQL Performance Explained,

it also started as a blog and then
was later on put into a book.

And the blog is still available
for free on use-the-index-loop.com.

And the idea was to do the same
thing with Modern SQL, but then

happened what I've already told,
something which I thought was

static became very dynamic.

Michael: Yeah.

Markus: And now, yeah, it's hard
to catch up, but there is progress

and there will be a book.

Nice.

I'm still working on it, I'm using
the draft as material in my training.

So it is getting better but there
is no definite timeline yet

for it.

Michael: As should always be the
way with things in the future.

Well, good luck with that.

Thank you so much for coming on.

It's been a pleasure having you.

Best of luck.

Markus: Yeah, thank you and thanks
for having me.

Bye-bye.