Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL - what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt. Here are some links to things they mentioned: Modern SQL (site) https://modern-sql.com Use the index, Luke! (site) https://use-the-index-luke.com SQL Performance Explained (book) https://sql-performance-explained.com Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown IS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-from UNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinct Modernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winand Markus’ website https://winand.at ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...