Talking Postgres with Claire Giordano

CLAIRE: 00:00:05
Welcome to Talking Postgres, a monthly podcast for developers who love this database. I'm your host, Claire Giordano, and in this pod, we explore the human side of Postgres, databases, and open source, which means why do people who work with Postgres do what they do, and how did they get there? Thank you to the team at Microsoft for sponsoring this community conversation. Today's guest is Tomas Vondra. He is a Postgres major contributor, a committer. He works on the Postgres team at Microsoft. He's very involved with the Prague local user group and is one of the organizers of the, let's see if I get it right, the Prague Postgres Developer Day, which I just call P2D2. He is well known for his work on performance in the Postgres project, and that led to today's topic as well. Welcome, Tomas.

TOMAS: 00:01:04
Hello. Happy to be here.

CLAIRE: 00:01:07
I'm really glad you're here. So today's topic is why it's fun to hack on Postgres performance. And that is because, fairly or unfairly, you have a reputation for working on the performance of Postgres. So hopefully we can dive into why it is that you do that and hopefully get some, I don't know, tips, suggestions, ideas, insights that will benefit other people in their work with Postgres. But before we do any of that, I always like to start with an origin story. Particularly if people are listening who are still getting their schooling or training and are trying to figure out what they're going to do with their career, I think it's always interesting to hear from people about how they got their start.

TOMAS: 00:01:57
Yeah, sure. I guess you want me to explain how I got to work on Postgres and how I got to work on performance stuff, right? So I think I already told this story multiple times, but maybe only to individual people. I think in 2004, 2003 or something like that, I've been still at the university. And I've been studying software engineering and that kind of stuff. And as a part-time job, I've been working at a company that was operating a couple e-commerce websites because that was after the dot-com boom. So a lot of the commerce was already done on the internet. And they've been running a couple pharmacies and that kind of stuff. And most of that was operated on actually MySQL and PHP and like pretty basic stuff. And I think we had at some point like serious performance issues. Essentially, the websites couldn't actually keep up with even just like a couple dozen customers visiting the website. And the management came with the request: we need to solve that. And one of the options was to just buy a commercial database like Oracle. But that was extremely expensive, right? Even then, it was a very small company. And we couldn't actually afford, I think, that amount of money. Or it seemed like unwise investment. So at some point we decided to investigate what are the other options? What other databases could we use? And we actually realized there is another database, which some people suggested was like a better for complex queries and so on. And that was Postgres. So I think at that point it was Postgres 7.4 or maybe 8.0 or something like that. So we gave that a try. We essentially migrated the website and everything to Postgres because we needed to see how that actually performed on that database. And the funny story was that at that point we found that actually it wasn't caused by the database at all. It was just like a really poorly written application in PHP because it was the worst pattern in the code was that it first selected all the products to list on a page, but it only got the IDs, right? And then for each product, it did like individual queries for each other field it needed to lookup. So it was like thousands of queries per page instead of like a single query. And that would kill any database. So we changed how the code actually worked. But in the end we decided to stay with Postgres anyway. Because we just liked some of the features of Postgres which MySQL didn't have. And also it was like much more, I think, reliable than MySQL. Because at that point, MySQL only had MyISAM. It didn't actually have proper like transaction control and all of that. So we stuck with Postgres, even though it actually didn't solve the actual performance problem. But that's how I got to work on Postgres. And it's also how I started working on performance stuff. Because first, we've been solving a performance issue. That's how we got to work with Postgres at all. But also, after we kept using Postgres, we kept running into complex queries and queries that didn't actually perform well and that kind of stuff. And in our team, we needed someone who would actually focus on these problems. And it happened to be me. So I started working.

CLAIRE: 00:06:56
So to be clear, this is a part-time job and you are still in university. [Yes] And so kind of in the very beginning, performance and Postgres in response to the situation. [Exactly. Yes] Oh, so the rest of us are so lucky that happened. I mean, it's kind of happenstance.

TOMAS: 00:07:19
That's a good question I don't know if it was that I was forced to work on these things but also it was like a personal choice right like in the team someone had to do that but I also personally like gravitated to this kind of work. So it was also, if I didn't like working on these problems. I wouldn't be working on that at all. Right? So I wasn't forced, but I had the chance actually to work on that.

CLAIRE: 00:07:53
Okay, speaking of gravitating to things, when we started this conversation a few minutes ago, I think I was pulling you away from something else that you were doing, which is watching the Olympic ice hockey game. [Yes] So what's the score? It's Czech Republic versus Canada. [Yes] Is that right?

TOMAS: 00:08:13
Yes. I don't know. I think, I don't know what the score is right now. I don't know.

CLAIRE: 00:08:21
Aaron is putting into the chat. Aaron's putting into the chat that is three, three. And after a three minute intermission, there will be a 10 minute overtime period of three on three hockey. So that's kind of exciting. [Yeah] Do we need to pause so you can go watch this?

TOMAS: 00:08:39
No, I'm not a huge hockey fan, but also playing Canada is a big deal, I think. So it's fun, right?

CLAIRE: 00:08:51
Yeah and our co-producer who's on the text chat right now Aaron Wislang he is from Canada so I can imagine that he's rooting for the other team and not aligned with you in this in this instance. Well, we'll keep an eye on the text chat as to what's happening and we'll find out whether you should celebrate or not. Okay, so you just answered the question, not only how did you. Get started as a developer, but how you got started with Postgres and how you got started in performance, all in one answer. So let's see. When you think about working on Postgres performance, you just said you gravitated to that kind of work then, but you still gravitate to that kind of work now. So what is it about performance that you find so interesting?

TOMAS: 00:09:48
That's a good question. But I think I'm focusing on query planning and query execution because there is actually a fair amount of mathematics and problem-solving thing. And I think there's also a lot of engineering in making good trade-offs, right? When deciding how to execute a query or like which path to take. I think you need to think about a lot of problems. I do have a personal theory of how the human mind works. And I think there are two extremes. That's how I mentioned that. One is analytical mind. And that's someone who is able to investigate individual problems and go deep into that. And then there is another extreme, which is like synthesis, right? Someone who is able to construct a complex problem and like describe, like construct an API and so on. And I'm very, very clearly in the first group, I think. I'm much better in like problem solving, investigating problems. And a lot of the performance problems that I've been working on are exactly that, right? You need to take a slow query or something that doesn't work well and investigate like why is it happening, right? So, and I think that's why I've been gravitating to this kind of work.

CLAIRE: 00:11:58
What's it feel like when you solve one of these performance problems? And maybe help the listeners understand how big are these problems? When you talk about investigating a performance issue in Postgres and solving it, does it take you an hour? Or does it take you a month or more?

TOMAS: 00:12:22
I think it varies a lot. [I thought you were going to say it depends.] I mean, I think some of the slow queries, right, you will immediately recognize why is it slow and how to fix it. Because a lot of the queries are slow for the same reason. But then there are also problems that are complicated and completely novel, right? It's a problem that no one actually investigated before or maybe not sufficiently. And that can take, I don't know, a week to even just build the understanding of like do enough experiments to actually understand what is happening. Then it can take a while to actually fix it or like figure out if there even is a solution because there may not be, right? So I think each of those problems is like a small puzzle. So if you like solving puzzles and like investigating like why things work the way they do. I think performance issues might be a good topic.

CLAIRE: 00:13:47
I'm curious. Do you do puzzles in your non-database part of your life? You know, like jigsaw puzzles or crosswords on your on your phone or.

TOMAS: 00:14:03
No, not really. Because it's a bit weird, but I always hated doing homeworks because it was made up problem. You are only doing the homework because someone wants you to make homework. While the performance issues are like a puzzle that actually are like meaningful like it's something that needs to be solved for I don't know a customer dashboard actually working fast right so that I think makes it different for me.

CLAIRE: 00:14:44
Okay. So I guess that means you don't read a lot of detective novels or watch murder mysteries on Netflix or anything like that either. That love of puzzles doesn't come into that part of your life either.

TOMAS: 00:15:04
I do like watch movies and so on of course like that's like a different thing but like if I have to make an effort for something like watching a movie is like a positive thing for me right. But if I have to spend like a day working on something, I would rather that to be like meaningful thing.

CLAIRE: 00:15:29
Okay. Got it. Can you, is there an example that comes to mind that you can walk us through to help us understand like a recent performance puzzle, if you will, that you've had to solve in Postgres. I don't know if there's one that is simple enough to make sense on a podcast.

TOMAS: 00:15:59
So I don't know, but a very simple one would be a thing that, like an optimization that I think I did in like 2014 in hash joins, right? For a long time, Postgres had hash tables with chaining, which means that each bucket in the hash table had like 10 tuples in a linked list. And it turns out actually that maybe that was like okay in, I don't know, early 2000s. But at some point, it's better to not have any chaining in the hash table. So just like we had a problem with a slow hash join. After investigating that for a while, I don't know, half a day or a day. I realized that it might be maybe caused by, you know, having a linked list in each bucket of the hash table. So I rebuilt Postgres with a parameter changed in the hash table code. And it was fast right so like much faster like twice as fast maybe after that came like a patch for Postgres changing how the hash table is built and so on it was kind of like experiment but also like a guess, an informed guess, about what the problem could be. So it can be even a small change, but this is one of the things that I've been, and I'm still quite proud of, because even a small change in a code which is used a lot can be like a significant difference, like if you look at all the installations and so on.

CLAIRE: 00:18:20
You know, one of the interesting things, I've worked at a bunch of different companies in my career, and there's a word at Microsoft, we don't own the word, there's many, many people throughout the world that use it, but it's impact. And so when you're doing your performance evaluations at the end of the year or whatever, the thing that gets discussed is what was the impact of your work? Not what was your activity, not how much did you get done, but how much did your work change things? Whether it's in your case, like changing the performance of Postgres. And so I think that's one of the things that must be cool about working on performance is that you can measure the impact. Well, I hope you can. Maybe you can't in all cases. Can you?

TOMAS: 00:19:13
I don't know because how do you measure like the difference from I don't know millions of installations. We don't have any way to collect the metrics and also if you introduce a new feature for Postgres I don't think that's any less meaningful for users that actually can benefit from that I think at some point. I think at some point you just need to believe that what you did actually is useful.

CLAIRE: 00:19:53
You're absolutely right. And I take back what I said. I was thinking about that ability to measure that something is 2x faster in a certain scenario. And that's nice, but you're right. How many people and how many applications are actually going to benefit from that? We don't know. You know, there's certainly no data science group out there that's measuring the number of Postgres installations and their scenarios and how they use things. Okay. So there's a bit of a leap of faith when you work on an open source project like this that doesn't have all that, all of the metrics built in.

TOMAS: 00:20:34
I think there definitely is, but also a lot of the people actually working on Postgres are also using Postgres and they are developing stuff that actually matters for them. So for the first couple of years, I've been actually working at companies either using Postgres to, say, build business intelligence solutions or later in 2ndQuadrant, I've been working for a company in providing services and support, right? So we had a very direct information from customers. And we've been often like solving problems that actually mattered to the customers. That's what we've been paid for. So there is some level of like immediate response or like immediate feedback, people working on stuff that matters for them personally, or, yeah.

CLAIRE: 00:21:35
Got it. Okay, which leads me to a question that I should have asked earlier. That patch example from 2014 relating to hash joins. Was that your first patch? No. Yes?

TOMAS: 00:21:52
No, I think I wrote a couple of patches before. I don't remember like how many patches or smaller patches I wrote before that.

CLAIRE: 00:22:04
So you don't remember your first patch that you submitted.

TOMAS: 00:22:07
I think like, I do remember my first patch. I think, like first contribution, that was like 2010, I think. But there was like a very small change in statistics. I think.

CLAIRE: 00:22:22
Okay. One of the talks that I've given at a few of the recent PGConf.EUs has to do with looking at as many contributions as we could look at in a particular release. So we did it, Daniel Gustafsson and I did it for Postgres 17, and then also for Postgres 18. And each of those has like a 15 month release cycle. But it's very interesting because when you look and compare between releases and you figure out like how many people made their first ever patch contribution to the code, code or docs, because it's the same source base, right? In that release, there was a sizable number of people, but some of those people are probably drive-by contributors. You know, they probably just needed something, had to scratch an itch, came, contributed, and they're probably never going to contribute again. But I guess in your case, you kept coming back as, as is the case with all sorts of other people in the community. Like, did you know when you did that first patch that it was going to be more than a drive-by contribution, that it was going to be the first of many, many, many?

TOMAS: 00:23:37
No, not really. And I haven't been even thinking about that. I didn't have a plan to be a major contributor to Postgres or even a committer. I haven't thought about that. I've been simply hacking on a database, which was cool, but also I felt like a part of a group of people, of the community. That was probably the more important thing what actually kept me working on Postgres, I think. The opportunity to work on interesting stuff. So there was one. But also being able to talk to the other developers like very openly, like meet them at conferences. Because I think like 2009 or something was the first PGConf.EU conference that I attended. And I've been going there ever since. And you could just meet all the other developers who implemented or wrote some of the code and talk to them about the different ways to maybe improve that or problems in the code and that kind of stuff.

CLAIRE: 00:25:05
So, so you're saying that the community, the vibe, the culture is part of what kind of kept you coming back. It's part of what hooked you on working on this open source project. Is that right? [Yes, absolutely] Okay. That'll probably make a lot of people feel good, right? Because there are so many people who invest their time, not just into the code, but also into the conferences and into the culture and the way people treat each other or the way they try to make sure that new people feel welcome. And so that's kind of a nice compliment, what you just said.

TOMAS: 00:25:54
Yeah and it's also true right so. So, and it's also one of the reasons why I'm still like helping to organize the Prague conference. Right. So I think there's like the 15th year that I've been helping to organize.

CLAIRE: 00:26:13
Yeah so I'll make sure to drop a link to P2D2 the Prague Postgres developer day, into the show notes for the episode so that people who are interested in going to Prague can check it out for next year. It happens, well, because of COVID, I think it moved around in those early 2020 years. But most recently, it's been in January, right before FOSDEM. And it just happened a couple weeks ago, right?

TOMAS: 00:26:41
Right. Exactly. And it depends on mostly on availability of the venue from the university so it needs to be in roughly like this time of the year because that's when the university has an exams period right so yeah exactly without students being there for exams or whatever.

CLAIRE: 00:27:14
Yeah, I'm looking at some of the pictures on the website right now, and it looks like the presentation rooms are those kind of classrooms where the chairs go up, right? [Yes] So people in the back row are much, much higher than the speaker is down below, and everybody gets a good view, and the room looks pretty full, too.

TOMAS: 00:27:34
Yes

CLAIRE: 00:27:36
You've got how many people come to this thing?

TOMAS: 00:27:41
So I think this year we had like 300, 320 people but we now have like three tracks so the rooms are not like too full I think.

CLAIRE: 00:27:57
Okay and then people who attend have to choose like that's that's the hard thing about multi-track conferences is that like oftentimes it in that it's like Murphy's Law like there are always two. Things at the same time that I want to go to. But it's just life. Nice.

TOMAS: 00:28:13
Yeah, I don't think we will go for like a fourth track I mean like we could but that wouldn't actually work and the good thing is that of course like the talks are recorded because the university has like a AV system built in so we can actually publish that.

CLAIRE: 00:28:33
That's nice because I know the recording of talks is one of the most expensive parts of putting these Postgres conferences in some cities. I mean, obviously the costs vary city by city. One of the things I really liked, and I wasn't there. I came to FOSDEM this year, but I didn't make it to P2D2 first. But you and Nazir Bilal Yavuz, who's another Postgres contributor, you co-taught a full day workshop called Introduction to Postgres Hacking. And just. I don't know. I'm not mentioning it as an advertisement, but as a thank you. Because I know that putting together a full day workshop is a lot of work. And I just love the fact that, and you're not the only one to do these intro to hacking workshops on the planet. There are other people who've done them as well. But I think it's so valuable to help tomorrow's future contributors and committers to, you know, get their feet wet.

TOMAS: 00:29:41
Yeah, and it's definitely a lot of work to prepare that, and also a bit exhausting to actually do a whole day of like a workshop. But I think it's worth it because I think it ties together to the effort actually building a community, right? Being actually able to help other developers and contributors to actually start hacking on Postgres. So in a way, I'm just trying to give back what I got from the community, I don't know, 20 years ago. All right.

CLAIRE: 00:30:25
I'm looking at the abstract for the intro to Postgres hacking workshop right now, and I love the beginning of the last paragraph. We expect basic knowledge of C, but we see the letter C, the programming language C. But we don't expect you to know the strange and unique C stuff in Postgres. I don't know. To me, that's a hook. Oh, what's the strange and unique C stuff in Postgres? And then you say, that's what the workshop is meant to address. Very cool. [Yeah] Okay, so we're supposed to be talking about performance and why it's so fun to hack on Postgres performance. So we're going to circle back to that in a minute. But before we do, speaking of you giving back and doing things in the community to help tomorrow's future contributors and committers, one of the other things that I think you started doing sometime in the last year that I think is pretty cool are office hours. Which like the concept has been around forever. Those of us who went to university probably went. [Yes] To office hours held by our teaching assistants or professors or whatever. Like, okay, so it's not our new concept, but your office hours are like open to the public, right? Anybody who's trying to hack on Postgres? How does it work? Tell me.

TOMAS: 00:31:53
Right. So why I started doing office hours is that sometimes, like for new contributors, especially, it can be intimidating to actually post the first patch to the mailing list. Right and maybe they don't even like understand how the mailing list like works or like how how to do that properly and that kind of stuff or sometimes people can feel like not sure if if the patch they are working on or like is it even a good idea or is it something that like we would want actually to in Postgres and so on or how to prepare the patch properly and so on and then they don't actually end up sending the patch at all right like because just like give up on that and sometimes the people like the new contributors do not actually realize that they can actually write an email to the individual contributors right you don't need to communicate with just the mailing list you can just write to the actually contributors and like ask for help or like ask for opinions and so on like the worst thing that could happen is that they will just not respond right because they have like too much work or something but I wanted to make this like explicit right like to make it sure like you can you can kind of like advertise that you can actually reach out to me and maybe I know nothing about the patch that you are working on I will not have an opinion but I could still give you advice how to how to submit a patch to the mailing list or something or suggest who to talk to or something like that or give you maybe a little bit of encouragement to do stuff that's what my office hours are I do that I do have a slot like every week people can just send me an email and like ask for a bit of time I expect people to tell me like what they want to talk about so that I can prepare like to keep the you know half an hour or something to keep it productive but beyond that it's up to up to you what you want to talk about like it should be obviously like a thing about like Postgres coding or like Postgres stuff but it can be like you can you can suggest like a completely new patch idea or maybe you already have a patch and you are just asking you're wondering how to best submit that to the mailing list like what should you be careful about like if you are forgetting about some sort of test or I don't know or maybe you already submitted a patch and it's kind of like stuck right and that's quite common issue for any developer because there's just so much work on being done on the mailing list so maybe I can give you advice like how to try to unstuck that I don't have like perfect solutions but I will at least give you an advice what I would do or what I would try doing so it's more just like an offer to have a chat right.

CLAIRE: 00:36:01
I think it's great that you're doing it. And I know you're not the only Postgres committer who has office hours. I am hoping that somewhere, I don't know where the right place is, whether there should be a channel on the Postgres hacking Discord or a page on the Postgres wiki that's Google searchable or whatever, where those of you who do have office hours kind of have them all listed in one place. So somebody, a new contributor who's looking for it is more likely to know about it or discover it. But I did, I will drop a link to your blogs about page, which has the information about like how to sign up for your office hours. So I'll include that in the show notes for this episode too.

TOMAS: 00:36:48
Yeah thank you I think the office hours are kind of like an extension of hallway track in at conferences right because I and that I think a very important part of a conference which is not transferable to talk recordings right and also that's how and where people discuss patches in face to face right so I'm trying to do something similar with like office hours, yes.

CLAIRE: 00:37:26
Speaking about office hours at conferences, excuse me, speaking about hallway tracks at conferences, like, I never understood what that meant when I was earlier in my career. You know, I remember going to like USENIX conferences and people were talking about the hallway track. I'm like, what do they mean? What is that? Like, I just didn't get it. And I think it's because I just didn't know enough people yet. I didn't know how to comport myself, how to just walk up to strangers and introduce myself and ask questions. Like, I was too intimidated, I think. I'm not anymore. But I was then. So anyway, it is true that some of these Postgres conferences have the most amazing, welcoming, interesting hallway tracks. And you can learn as much in the hallway as you do by attending talks. And there's one in particular that's coming up in May, which if anyone is listening and they are thinking about becoming a contributor, or maybe they're in early days to Postgres, it's called PGConf.dev. It happens annually in Canada. This year, it's going to be in Vancouver, Canada in May, May 19 through 22. And I'll drop a link to it in the show notes as well, just in case people want to go check it out. And I know they have really good pricing for... I think particularly they probably have student pricing, but the pricing to attend the conference is deliberately low to make it easy for people to be part of it. And let's see, it's two days. Okay, there's a pre-conference day, which is actually chock full of amazing sessions this year on Tuesday. So I would say it's not optional. You should be there for Tuesday. Wednesday and Thursday are normal conference days and Fridays and unconference. So basically four days. And you are going, right, Tomas?

TOMAS: 00:39:22
Yeah, I definitely plan to go. Yes. And I kind of have to because I have a talk there.

CLAIRE: 00:39:31
Yes, I plan to go as well. I have a panel and a talk. And it's just so much fun. Particularly if you are, if your idea of fun is hanging out with people who enjoy working on databases. It's just, it's, I don't know, it's one of my favorites. Okay. We talked about office hours and we talked about the intro to hacking workshop and organizing a P2D2 and other ways that you're focused on giving back to the community and helping train like the next generation of contributors. Before we flip back to performance, is there anything else we should talk about in terms of your community work that's interesting?

TOMAS: 00:40:23
I don't know. I think maybe we should just like mention what actually hallway track means. Because we've been just talking about like. [Okay, let's do it] I think hallway track is the time between talks and after the conference essentially like when you can talk to the other engineers and like maybe have a tea or coffee or beer or whatever and discuss the stuff that was maybe mentioned in the talks or like any other problem or any other patch feature that you are like working on and the amazing part of the Postgres community is that it's not like a single company right like we are working for different companies for different customers different fields and we still collaborate a lot on all these things so. So I think the hallway track is the most important part of the conferences, at least for me. And I think it's important to actually introduce the new people coming to the community to actually to this concept, to how important that is. And also kind of like encourage that. And I think, for example, last year at PGConf.dev, I think it was a great idea that there was like organized dinner, right? Where, yes, I mean, instead of, because what often happens at conferences like this is that you see the people that you know for, I don't know, 20 years, right?

CLAIRE: 00:41:54
Oh, the meet and eats.

TOMAS: 00:42:08
So you go to the dinner with them and the new attendees are kind of like stuck and like left to do their own thing.

CLAIRE: 00:42:19
Yeah, they don't have an invitation to the dinners that exist because they don't know the people yet.

TOMAS: 00:42:21
And yeah, right, exactly. And I think last year there was like a random selection or random grouping, right, of people for the dinner. So you actually got to meet anyone randomly and have a chat with them I think it was interesting I was a bit jet lagged but other than that I think it was wonderful.

CLAIRE: 00:42:54
Yeah, and I know that at the meet and eat dinner I went to, people just went around the table and introduced themselves. There's also a fun run for people who are runners who want to go for a run one evening. This year, like I said, the Tuesday content is being completely revamped. And it hasn't been announced yet, but the intention is to make one of the tracks on Tuesday super interesting to newcomers, as well as people who have been involved forever. So, and I think the team is going to be successful with that. Okay, well, there is one other thing I want to also talk about in the community area, which is the Postgres Hacking Discord, which is somewhat new. We should include a link in the show notes as well. It's only been around for, oh my gosh, it's almost two years, but it's more than a year and a half now. Started in summer of 2024, right after PGConf.dev. And anyway, I think there is a monthly hacking workshop that happens there that you have been part of on occasion. Is that right?

TOMAS: 00:44:05
I've been and actually I will be on the workshop next month I think again.

CLAIRE: 00:44:10
Oh, cool. So what is this? Tell us, please.

TOMAS: 00:44:15
So the hacking workshop is a monthly session organized by Robert Haas where the attendees pick a recorded talk from one of the past conferences then they watch the talk on their own and then there is a session like an hour where everyone joins a Zoom I think or like video conference and they can ask questions to the to the speaker of from the talk recording right so they can ask like discuss the thing like they are not watching the recording together but they can actually discuss what was in the talk and ask questions and that kind of stuff so.

CLAIRE: 00:45:12
So it's like peeling the onion. For a whole hour, which is a lot more than the normal like five, ten minutes of Q&A at the end of a talk.

TOMAS: 00:45:16
Yeah yes exactly and like sometimes the discussion kind of like you know gets distracted and like starts talking about stuff that wasn't in the talk but is kind of like related to that and that's fine, I think it's like online hallway track in a way like being able to discuss technical stuff right kind of like a brainstorming or so on.

CLAIRE: 00:45:50
I was up in Redmond last week. I don't, even though I work for Microsoft. I don't actually go up to Seattle very often. Maybe like twice a year at the most. And while I was there. I gave a talk at the Seattle Postgres meetup. And I think it was the talk about contributions to Postgres during PG 18. And at the end, and so of course I mentioned the Discord and the monthly hacking workshops and how that Discord initially started as like just the Discord for the mentoring program and then expanded its scope and its activity. And anyway, there was a developer who walked up to me at the end and said, oh my gosh, that was such a smart move. He said to create a Discord for the Postgres hackers. He said, if you want this generation, and we're all on Discord all the time, to kind of be more participative. Is that a word? He said that was a good call and just wanted to give it a plus one. So I've been meaning to tell Robert Haas that, and I haven't had a chance yet. So maybe he'll listen to this episode. He'll discover the compliment that way.

TOMAS: 00:47:00
Yeah I personally attended a number of those you know sessions and I it was always like interesting thing it's it would be better to meet face to face and like have a chat about the problem, about that personally, right? But this is the next best thing. And for many people who do not go to Postgres conferences for whatever reason or can't go, it's the only option possible, right? So I think I agree that it was a good thing that Robert, you know, came up with this idea and started actually organizing that. And it definitely is a fair amount of work to actually keep it running. So, yeah.

CLAIRE: 00:47:53
Okay, so you said that one of your talks is going to be the focus of an upcoming monthly hacking workshop. Is it one of your performance related talks or, huh, are all your talks performance related?

TOMAS: 00:48:12
So the next month is going to be, I think, the talk from... What was that? PGConf.EU 2024, I think, which was about the performance archaeology or something, I think. About like how the performance evolved over the years. Let me check. I know that I'm...

CLAIRE: 00:48:45
I did a little research right before we started today's podcast. [Okay] And just here are my proof points for why I think so many of your talks are about performance. Next month in March at Nordic PG Day, which is happening in Helsinki this time, you're giving a talk on efficiently approximating and estimating percentiles and histograms, which you also gave that talk in FOSDEM PGDay, a couple of weeks ago, right? [Yes] Okay, and then for POSETTE 2026, and I have insider information here, you're going to be giving a talk about random page cost in Postgres, like why the default is 4.0 and whether you should lower it for SSDs and things like that. Last year at POSETTE, you gave a talk, the performance archaeology talk, 20 years of improvements. And then PGConf.EU, you last fall, you talked about fast path locking. [Right] So I don't know. Do you ever give a talk about anything besides performance?

TOMAS: 00:49:49
So I would say that actually the percentage, the estimated percentage is not really performance-related, or at least not about Postgres improvements itself, because this is about extensions and about a concept of sketches that is from streaming databases and so on. So, yes.

CLAIRE: 00:50:16
Okay. But I want to debate you on that because the whole reason people use approximation algorithms is because they want to get to an answer faster.

TOMAS: 00:50:25
Right. So, yeah, I do work in the field of performance stuff. So, my talks are about performance. So, that's definitely true. Yes. The percentile talk is a bit different in that it's not really about Postgres feature, right?

CLAIRE: 00:50:50
It's not about the core, you mean?

TOMAS: 00:50:50
It's about, yes, it's not about the Postgres core.

CLAIRE: 00:50:54
Okay, I'll give you that.

TOMAS: 00:50:54
It's about like research papers implemented using Postgres, but it's like a different thing.

CLAIRE: 00:51:00
But also, you're the maintainer of the tea digest extension for Postgres. Am I right?

TOMAS: 00:51:07
Yes. So, I mean, it's my hobby to read research papers and like, do stuff using Postgres, which is amazing that we can actually use the extensibility of Postgres to do this kind of stuff like very easily.

CLAIRE: 00:51:10
So I... Okay, so I don't want to elevate you on a pedestal and put myself down. But it's my hobby to read detective novels. And it's your hobby to read research papers on mathematical concepts that speed things up. So that's one way in which you and I are different, I guess. Oh, speaking of you, really quick side note. Can someone in the text chat tell us who won that hockey game? It must be over by now.

TOMAS: 00:51:48
I think it was Canada.

CLAIRE: 00:51:50
Oh, really? I'm sorry. Okay. Well, I'm happy for Aaron and I'm sad for you, Tomas. [Happens] Yeah. Okay. All right. So let's flip back to performance for a second. I don't know if you can articulate this. I don't actually know if... Sometimes it's very hard for me to describe my emotions, but I'm trying to imagine like if there's a special feeling that you associate with solving a performance problem, like getting to the answer, realizing it's going to speed things up a lot. I don't know. How does that feel?

TOMAS: 00:52:41
I don't remember like any explicit emotions but it's definitely like a good feeling that you solve the problem right so there is definitely some like amount of like satisfaction or like feeling of satisfaction and achievement and I think like everyone needs a little bit of that when working that you actually achieve like feeling of like achievement something so I definitely do have that sometimes it's like it takes a long time actually to get it done right now I'm working on the index prefetching with Peter Geoghegan and that's a patch that I think I started working on like three years ago and it's still not committed right so hopefully for like Postgres 19 we will have something, at least part of that committed.

CLAIRE: 00:53:47
When you say index prefetching, I know that means something to some of our listeners, but there's other listeners who will be like, so what? Who's that going to help and in what scenario?

TOMAS: 00:54:01
So it will help anyone who is using index scans or like indexes in Postgres because indexes are index scans are the main source of random I/O which especially on like regular storage to get good performance you need to actually issue the I/O request like early enough so that you don't have to wait for the actual data once you get to actually need that. So it's part of the asynchronous I/O work which is done by like Andres and Thomas Munro and Melanie and various other people both in the Microsoft team and in the Postgres community in general. And it's a way to better utilize the storage, right? Which for database is an important thing.

CLAIRE: 00:55:15
Okay. So maybe Postgres 19, which the last commit fest is next month, right?

TOMAS: 00:55:23
Right. I mean, commit fests are, yes.

CLAIRE: 00:55:25
For feature freeze.

TOMAS: 00:55:27
I mean, the feature freeze is like early April. First week of April.

CLAIRE: 00:55:31
Yeah. I don't mean the last one ever. I mean the final commit fest prior to feature freeze for Postgres 19, specifically.

TOMAS: 00:55:38
Yeah, I mean, commit tests are a bit obsolete at this point, but we still use them. But yes, next month is like when we need to actually get it into Postgres, into the Git repository.

CLAIRE: 00:55:55
So one of the things that I think might not be obvious to someone who's just starting out, like if someone isn't sure they would be good at performance, they might conclude that they're not going to be good because they see a performance problem and they have no idea how to solve it in the beginning. So when you start tackling a performance issue, do you know how to solve it in the beginning?

TOMAS: 00:56:19
No. I mean, that's how I started like 25 years ago. You just have a slow query and you need to somehow figure out why is it slow or how to make it faster. Or like, is it missing an index? Maybe. Is the query written in an efficient way? Could be also the case. And I'm still in this situation like nowadays, right? Because there's so much code and so much functionality in Postgres that I just don't know all the pieces. So I think the approach is you need to be systematic. You need to look at the slow query and start by profiling. Maybe you can try a couple things, like rewrite the queries a couple times, or you can use explain, analyze to investigate which part of the query is slow, and that kind of stuff. [And when you say profiling, can you tell me more about what you mean?] Right, like well, profiling means either explain, analyze or using like one of the tools from the operating system to actually profile CPU time and that kind of stuff. So like perf, for example, in Linux, right?

CLAIRE: 00:57:53
Okay.

TOMAS: 00:57:55
I assume that like on Windows, there's like a different profiler and so on. But I definitely use like perf. You could probably also use things like DTrace and that kind of stuff.

CLAIRE: 00:58:12
Shout out to Bryan Cantrill there. And Solaris. [Yeah] And OpenSolaris. Sorry. Past life.

TOMAS: 00:58:18
Yeah, and I think for profiling the website I use most of the time to actually, you know, find the proper incantation of like whatever tool, that's Brendan Gregg's like a website. So that's a good source, right?

CLAIRE: 00:58:39
And Brendan is another person who once worked at Sun Microsystems. [Yes] So, sorry for anyone who's listening who doesn't know that I used to work in the kernel group at Sun. I owe a lot of my formative years to those people and that team. Okay. So one of the other words you used earlier is talked about experimenting in trying to tackle performance problems. And I'm assuming you do a bunch of benchmarking too, as you progress further. along, am I right?

TOMAS: 00:59:16
Yes well I think that's natural right like if you are working on performance then if your goal is to improve performance and behavior of the system then like you need to prove that your patch actually does that right so you just need to do a lot of benchmarking for that reason. But I also learn by doing benchmarks. I learn about how the system works by actually trying different things, like exposing the system to different inputs and kind of like learning how the heuristics in the algorithm, for example, behaves in practice. Because I definitely am not one of the people who can look at piece of code and kind of like immediately predict how it's going to behave in performance. Is it going to be fast? Is it going to be slow? Or like, I don't know. So I definitely need to do a bunch of testing and actually figure out like are there maybe some weird like cases where it doesn't behave correctly or something like that.

CLAIRE: 01:00:44
Do you find that you also, as you progress along this, I'll call it a journey for each particular performance problem that you tackle, do you find that you also have to benchmark to make sure that you're not slowing down other parts of Postgres? You know, like speeding up and slowing something else?

TOMAS: 01:01:03
Oh, yeah, absolutely. I think that's like, for example, I mentioned the index prefetching work. And making sure we are not causing regressions in cases, in queries that do not actually benefit from asynchronous I/O. Or like from prefetching is a huge part of the benchmarking. Making sure that we are not causing trouble to people who will not benefit from the patch is important I think and it's definitely important for other patches as well right.

CLAIRE: 01:01:53
What are your go-to benchmarking tools? If somebody wants to delve into this more, where do they start? Where do they look at?

TOMAS: 01:02:03
So I that's a really difficult question [I'm good at that] I mean because it depends on I mean. The tool that I use most of the time is probably like pgbench all right but I'm not really using that with the built-in workload. pgbench can also be used to orchestrate your custom benchmark, just run queries that you specify. And what exactly those queries will be heavily depends on what kind of patch are you working on. So, in a way, like constructing the benchmark is actually a way to learn about the patch, right? So, for example, if I'm going to work on a patch that supposedly, for example, optimizes aggregation, like group by queries, then the built-in workload in pgbench is completely useless. Because it doesn't have a single group by. So I will have to construct queries and data sets. And then I can actually use the pgbench to actually run that. And it will generate random queries and that kind of stuff. But I have to construct the workload actually test. Similarly, there are patches that are optimizing, you know, a join planning, like a join search, like in which order to join tables and so on. And again, the built-in pgbench workload is completely useless for this because it doesn't have any joins. So I will have to benchmark that patch. I will have to think about, okay, so I need to test queries with different numbers of joins, maybe different types of, you know, data sets, different data distributions, and that kind of stuff. So even just constructing the benchmark is a way to actually learn about the patch. So for example this is a great way to do a review of a patch that you know nothing about yet [Oh] so just going to the mental exercise of actually so how would I test this right like if I want to actually show that the patch helps I will have to do this right like the data set would look like this. But if I want to show that, what's the worst case, in which cases the patch will perform poorly, those are like mental exercises that you need to go through during the review. And maybe the patch misses one of those cases, right? And then you have a review. So, yes.

CLAIRE: 01:05:32
So what you're saying is that you can build up your skills of assessing performance or assessing performance impact not just by working on these patches yourself but by doing patch review and coming up with creative ways to look at the ramifications of something that's being proposed. [Yes] cool I like it.

TOMAS: 01:05:52
I think, and I think this is also like extremely important part of the review and extremely valuable part of the review. Because sometimes the review can be quite superficial. You will point out typos and spelling mistakes and naming issues in functions, and that's fine. But especially for more complex patches, it's important to actually talk about these trade-offs, actually like a design architecture and so on and also about these you know aspects of like is it actually properly implemented or like does it actually address all the, all the corner cases and so on so yeah and as I said I mean like when I have a patch that I haven't looked at before and I need to start doing like a review. One way is to look at the, you know, at the diff and like think about like what the patch should be doing and so on. What works for me better is actually doing some experiments with the patch and like trying to do, you know, trying to expose the patch to cases that are kind of like weird.

CLAIRE: 01:07:25
Okay. So maybe I'm going to make an observation or two, actually, that may be obvious, but you tell me. What I'm hearing from all of this conversation is that the mentality that you bring to your work on performance and Postgres is that you start off by being very curious. When you've walked through your examples, you're asking lots of questions. And so I feel like if someone's wondering if they are a future performance hacker on a database, that curiosity might be a prerequisite that you kind of need. Am I... is that fair?

TOMAS: 01:08:07
So it definitely is for me right like I need to be able to ask questions and I'm trying even even during like when reviewing a patch by other people I'm trying to more to ask questions than like point out issues right but I don't know if like other people work the same way probably not.

CLAIRE: 01:08:36
Okay, all right. I only have, I'm generating something from a sample set of one, which we all know is not a good thing to do. So, so that's fair. But I still, I don't know. I don't think I'm generating from a sample set of one. I've worked with a lot of performance engineers in my life. And I used to manage a performance engineering team. And I do feel like curiosity is one of the hallmark traits. Doesn't mean you can't be uncurious and succeed in this space. You probably can but I don't know it seems important or it seems commonly shared.

TOMAS: 01:09:15
Yeah, I think curiosity in general is probably important and required. But I know that other hackers in the Postgres community definitely do work in different ways. So, for example, I'm now collaborating with Peter Geoghegan. And his approach to problems is very different. And I'm not saying it's wrong. I actually do enjoy the collaboration because of that, right? Because we kind of like both complement each other in some way.

CLAIRE: 01:09:48
Okay. So you're like a super team.

TOMAS: 01:09:58
Yeah, I think I may be a sidekick, but yes.

CLAIRE: 01:09:59
Superhero? Okay so the second observation I have is that I saw a diagram once that had it was like a tree with branches or you could think of it as a road system with a lot of dead ends on it and it was about the process of iteration and in my in my career I have collaborated extremely well with people who are comfortable iterating to make something better. And I've collaborated poorly, like not well, with people who are like, just want to get to the answer, just want to get there fast, good enough for government work. No, no, no, we don't need to revise this again. Revising it again is a waste of my time. Don't waste my time. And anyway, I feel like iteration is important. And maybe that's because that's my process. And what you described. It sounded like you were willing to take multiple steps, come at a problem from multiple angles, like go down dead ends and come back. But you never gave up, like in solving these problems, even if you didn't know as you were going, if you're going down the right path. Am I off base?

TOMAS: 01:11:17
Yeah, I would say that like dead ends are part of the game so you just need to once in a while like explore something that actually is not the proper solution like or like figuring out what is the right trade-off in some of the algorithms or solutions I think that's important yes.

CLAIRE: 01:11:48
Very cool. I like hearing that because it validates my world philosophy. But it's okay. Not everybody has to enjoy going down dead ends and iterating. That's fine.

TOMAS: 01:11:59
Yes I think it's like a brainstorming in a way like figuring out like what is the right solution.

CLAIRE: 01:12:12
Okay, so before we wrap, I'm trying to figure out if I have covered all of the examples or things that we should be talking about with regards to why it's so fun to hack on Postgres performance. I'm trying to make sure that we have met the promise of the title of this episode. So is there anything else that's really fun about it that we haven't covered?

TOMAS: 01:12:43
Not that I can think of. I'm sure there is a lot of interesting, funny stories about different patches, but I don't remember anything that I would mention right now I guess.

CLAIRE: 01:12:59
Okay. If you think of anything right after we hang up today, if you have a link to a particular patch that is like the canonical reference or like, this was a good example of a good first performance patch or something like that. I don't know. If anything comes to you, I can include it in the show notes as long as I get it by tomorrow. So I don't want to overwhelm people with 35 links, but one or two of them might be interesting. Which actually leads me to mention, do you tag certain patch ideas as patches that need to be done, like problems that need to be tackled? Do I remember correctly that you have a list somewhere? Scrolled away?

TOMAS: 01:13:49
In what sense? [Maybe it's a good first patch. Is it good first patch?] I mean, like I've been, so I've been posting on my blog. I've been like trying to propose a couple ideas for like that might be a good idea for a good topic for like first patch for new contributors. I think those are on my blog I'm not sure if there are some still some of those ideas you know because people are already working on a couple of those I think so I'm not sure if there is something still available I would need to check.

CLAIRE: 01:14:41
Okay. What is the name of that tag? Do you remember? Off the top of your head?

TOMAS: 01:14:47
I think it was a patch idea probably.

CLAIRE: 01:14:51
Okay. All right. Well, if there are any interesting ones that are available that have that tag, we can include it in the show notes. We'll do a search after.

TOMAS: 01:15:01
Yeah I think this is also something people can discuss with me on during the office hours because one thing I found, and I'm judging by my personal experience, is that I'm much better when I hack on something that actually I personally am interested in. So when someone just gives you a topic for a patch and tells you: do this. I think people are, it's very easy to lose interest give up after a while so I think it's better to actually look for a topic that actually is interesting for you personally or that is interesting because maybe it's from a field of mathematics that you've been like working on before or maybe it's interesting for your employer or something right like so there's some sort of like interest so what I do suggest people is to maybe go through the commit fest app and like look at the topics and like figure out like what what patches could be like interesting but it's also something that I'm open to discuss during my office hours, right? So if you send me an email with a note that you don't know what to work on, I'm okay having like a chat. Then I can suggest you a couple of things. It's not going to be maybe a new patch. It's going to be like a patch someone else is already working on. But reviewing patches written by other people is extremely important or extremely useful way to actually learn about code. So that's what I would recommend, actually.

CLAIRE: 01:17:05
So, and that's, I think that's interesting because at least for me initially, and this was a very superficial conclusion, I was wrong. But I used to assume that patch review was all about like QA. It was all about ensuring the quality of the committed patch, right? Doing the review to make sure. But it turns out that's only half the motivation right there. And the other half the motivation is to skill up everybody who's involved on the Postgres project. It's a great way for new people to start to learn and understand the system and the code. Agree, disagree?

TOMAS: 01:17:44
Yeah I think it definitely is like a very important way to learn about the code but also it is a QA but also I think like having a patch doesn't mean that this is the right solution right there is always maybe a better way to actually solve the problem or maybe sometimes it happens that the patch actually is not like a problem worth solving so that can also happen of course right.

CLAIRE: 01:18:33
When I think about patch review I'm sometimes reminded of I think it was a year ago exactly Robert Haas was on this podcast and he was talking about the mentoring program for Postgres. Robert, of course, is a Postgres committer or a major contributor, works at EDB, has been involved in the project for a long time. I'm not sure whether it's longer or not as long as you, but you both have been involved for decades, it seems.

TOMAS: 01:18:58
Oh, Robert is definitely longer.

CLAIRE: 01:19:02
Than you? [Yeah] Okay. But one of the things he said is one of the first ways that he got involved, he, of course, went to the mailing list and was reading pgsql-hackers and trying to spin up that way. And then as he started to participate in patch review, because he figured out this is a great way to learn, right? And he wanted to learn. He asked himself as he approached a patch review, can I say what Tom Lane would say before Tom Lane says it? Because Tom Lane, of course, is brilliant and a very long-term Postgres contributor and clearly had a lot of smart things to say, still does to this day. And I just thought that was funny. Can I say what Tom Lane is going to say before Tom says it? And that's how he challenged himself. And I do think it's interesting. What that reminds me of is that when artists who are learning how to paint go to art school, one of the things that they are asked to do is to like paint a copy of very famous paintings from previous generations masters and they're asked to paint it because replicating what is what has been done before and what is considered great can be a way to learn you then have to forge your own path as an artist, create your own unique designs and approaches and everything. But, and the same is true with writers. Sometimes like you can be given an assignment, which is like, okay, take the first paragraph from this very, very famous book and then evolve it in a different direction, but start by typing that in and starting there. And I don't know. I think it's interesting channeling people that have come before as a way of learning. All right. That's a little random rabbit hole from me. I will include a link. I found that the tag on your blog was patch idea. So I'll include a link in the show notes. There's only four of them on that list right now. Maybe if you find out they're all being done or have recently been done, then I can exclude it. Just let me know, cool?

TOMAS: 01:21:25
Yeah, I definitely will post a couple more ideas in the future. So I think the tag is definitely valid.

CLAIRE: 01:21:32
Okay. I'll include the link then. And the other thing I wanted to chime in with for anybody listening who is thinking about going to PGConf.dev in Vancouver in May, one of the organizers, Melanie Plageman dropped into the chat during today's recording. And she clarified that the meet and eat dinners, which are open to anybody who's attending, will be on both Tuesday and Thursday this year. So there'll be two nights where you have an opportunity to connect. I'm assuming Wednesday there's a evening reception that is part of the event as well, because there always is on that day. And the other thing I'll say is I don't have any knowledge of this, but my suspicion is that next year, PGConf.dev won't be in Vancouver. It might be back in Montreal, which is where it was last year. So if you do live nearby, maybe you're on the West Coast and you're thinking, oh, I won't go this year. I'll wait till next year. Don't do that because it may not be on the West Coast next year. So, all right. Thank you, Tomas. This has been fun. I've really enjoyed our conversation. And I'm not the only one who appreciates what you do, both for the Postgres code and performance, as well as for the community. So thank you for coming on the show.

TOMAS: 01:22:56
Thank you for inviting me and thanks for the chat. It was quite pleasurable, I think.

CLAIRE: 01:23:03
Awesome. And for those of you who are listening, if you liked today's episode and you want to hear more of these Talking Postgres episodes, you should subscribe on Apple and Spotify and YouTube or wherever you get your podcasts. And please tell your friends because word of mouth is one of the best ways for a podcast to grow a listenership, if that's a word. You can always get to past episodes and get links to subscribe on the different platforms at talkingpostgres.com. And we include transcripts on the episode pages on talkingpostgres.com too. We work very hard to make sure everything is correct, Postgres is spelled properly, etc., etc. And a big thank you to everybody who joined today's live recording and participated in the text chat on Discord.