166: Speedy Database Queries with Lukas Fittl

0:00A:Programming Throwdown Episode 166: Speedy Database Queries with Lucas Fiddle. Take it away, Patrick.

0:24B:Hey everyone, welcome to another episode. Pretty excited about this one. Well, I think I say that every time; it's true every time. Try to bring you guys good content today talking a little bit about some part of databases we haven't talked about. We've had a fair amount of people on to talk about various aspects of database and learned a lot. Today Lucas is here, and he's going to help us understand some—some a little bit lower-level stuff, some optimizations and queries. We had a bunch of good thoughts even in the pre-recording. I took notes; I don't know how many we're going to get to, but I'm excited to have Lucas here. Lucas is the founder at PgAnalyze. Welcome to the show, Lucas. Thank you. Thanks for having me. All right, so we normally start off by talking a little bit about how people got into tech. So the question we normally tee up at the beginning is: what was your first sort of like computer programming experience? Like do you have like a formative moment where you're like, 'Oh yeah, that's it! That's magical!' Yeah.

1:19C:Good question. So how did they get into tech? Was probably—I think it was my dad's laptop, which was probably running, you know, Windows 95 or whatever was before that at that point. So I'm in my 30s now for context. And I remember what fascinated me back then: there was this game where you had these apes throwing bananas at each other, and you could program it because it was just written in—I think a version of Visual Basic or something, or like BASIC, I think, just at the time. And so I think how I got into programming, you could argue, is going in and modifying that game. So, you know, the ape would throw the banana a bit stronger, essentially. And so that kind of stuff was like fascinating for me early on. And how I think I actually got into serious programming was probably with game programming from a hobby perspective, right? So I wanted to build my own game engine; I wanted to build my own games. Let's say the most I got to was—there was this competition over the weekend where people were building games with Pygame, and so I ended up building a really simple game where you were like rolling a ball for a tube and like going around like obstacles, and that game actually worked compared to all the other ones that I, you know, coded up the engine for but didn't create any assets for. But that's really how I got started to, you know, kind of programming as something that I enjoyed at the time. And then more, you know, later how I actually got into the tech industry was—I actually left school. I don't wouldn't recommend it, but I left school at the age of 16 and essentially just started working. My first job was a hosting company, essentially, you know, putting servers in racks like back when you still had physical servers, and also writing code to support, you know, somebody provisioning in the back end for that hosting company. And so really that's, you know, where I would say I got my first professional experience actually, you know, working with customers, working with, you know, applications, working with databases. And that's, you know, a long time since—since I was 16.

3:16C:Back in, if I remember correctly, in 2007, we started a company together where I was essentially the, you know, one of the co-founders and technical like CTO, I think at some point as well—I forget. But essentially, you know, creating a blogging network so something like Tumblr or Twitter or X if you call it these days, but, you know, much earlier, essentially, right? And so the backing database for that was PostgreSQL. And so my interest in PostgreSQL really started back then in that startup where we essentially had that, you know, blogging site that got a lot of visitors and had a lot of traffic coming into that site, but ultimately it was always database query that, you know, fetched your results if the bird cached. And so that's really where my interest in PostgreSQL and the database world came in—was, you know, from an application perspective as an application engineer encountering frustrating slow experiences.

4:02B:Wow, oh, that was awesome. That was a whirlwind tour, I think, that, you know, mentioning these various sort of like stops along the road and sort of even—I don't want to say meager—that there's an underbinding like, 'Oh, working at a database hosting company and putting racks on.' I feel like everyone or even myself, I look around and see everyone where we are at today. And you know, you're at a company, you know, Jason and I are at tech companies or whatever. Like these kinds of things people look at them and say, 'Oh hey, like wow, it must have been this like, you know, meteoric rise.' And I guess I do know some people who this kind of is, but then others it's just like this story of just like no, it's like really basic job after basic job and just like continuing to sort of roll it forward. So I guess like that's an encouragement to me to hear it, but also to other folks out there listening, like, 'Oh man, sometimes it takes that first job.' It's like all about the next step or whatever. Maybe some people will sort of like innately have everything set up for them to just sort of like go directly into a quote-unquote dream job, but some people it just really takes finding it—finding your way, growing—not to say that maybe you actually loved all those jobs, but sometimes you look back at them and like, 'Wow, I can't believe I did that.' At least for me.

5:11C:And it was funny. You know, like so I worked at Microsoft a couple years back, and my first big company job at Microsoft. I was like, 'Wow, you know, I never thought it would get here because I remember thinking I could never get a job at Apple or Google.' Right? Like I was not, you know, the Ivy League graduate who could actually have that background that they look for in those interviews. But there's always a backdoor of sorts into those companies, right? But it's just not for the front door, right? Like if your resume looks like you dropped out of school at 16, they're not just going to be like a rubber stamp. But if you have a way of showing your skills and showing your work, right, in some other way, then there's always a way in the system essentially to, you know, find that dream job of yours if that's what you're going for. I have opinions on big companies by the way, but if that's what you want, there is a way to.

5:54B:Do it? Yeah, I feel like there's—I have opinions about Ivy League schools and about big companies, but maybe, maybe you'll have to save those up for another time or yeah, one thing: bonus episode or something. One.

6:04A:Thing. I remember when I got my first tech job it was, you know, much less than half of what a starting engineer makes at one of these big Big Tech companies or like FAANG companies, let's say. I remember this is a kind of a really weird thing, but I remember—I did—I had to do something like go get a pen from the cabinet because I didn't have a pen at my desk, and I walked back and I realized, 'Oh, I made like 10 cents in the walk,' or maybe it's like three cents or something like that. But it was such a big deal. It's like, 'Wow, you know, they paid me a quarter to go and get this pen and this notebook.' And that just blew my freaking mind that like oh, the salary—like you get paid as part of everything. And yeah, it's true. It really is kind of stepping stones. I feel like now, you know, and Patrick maybe can attest to this in management in leadership. There's just always kind of a crisis, and I feel like, you know, you have to kind of build the battle scars to walk into the office every day where there's some kind of crisis and say, 'All right, let's—let's get this figured out and let's move on to the next one.' I feel like that would have just completely destroyed me as a new as a new hire. Yeah.

7:25C:And it's fascinating, I think, you know, to me it is. You know, that's why I now run a small company, right? And so to me it is this notion of there's nobody else—like everything stops with me ultimately as a CEO, right? So if things don't work, yes, of course I can tell people to improve, but ultimately, you know, I need to be the one making sure that it happens. And that's a really different mindset than being a cog in a wheel, so to say, which sometimes can happen in some jobs, right, where you're just like doing what you're told, but you're like your buck stops really early, right? Like you're just shipping the code and you're not even shipping it; you're just writing the code. You're committing it, and you're done, right? Versus in a startup, you actually have to care about the outcome of the code.

8:00B:Yeah, that's an important distinction and lesson. And I'll also say one of the interesting things—and you're kind of mentioning it—that it never stops happening is people believe this, like you said, 'You're just there's gonna be an issue,' and you're just gonna tell someone to take care of it. And it's like, I mean, maybe at some level or if you've grown a really good team, you can kind of claim through your hard work that you've gotten to that point. But I was trying to explain to some people the other day—it's, you know, have a team of people work with me, but there's you can't tell them what to do. You have to get them to buy in, convince them, like explain, teach them, like to make those decisions jointly. So any expertise I have is only as good as like how much I can imbue into others the same feelings and same understandings and context and, you know, having to trust each other to make good decisions. But it's this weird thing where everyone believes—or even, you know, I was explaining to my kids—and have this thing like, 'Oh, you know, you've been there a long time, like you just tell everyone what to do.' It's like, 'Yeah, no. That doesn't work.' Like it's all about trying to explain what you would like, explain a plan, like trying to convince others to buy in. And so people believe it's this like bullhorn flag that just gets flipped, like, 'Oh, you're senior,' or 'You're a manager,' or 'You're whatever,' and that means anyone without that flag, you get to tell what to do. And it's like, no, no, it does not work like that.

9:14B:All right, so I guess trying to get us onto our story arc today. You mentioned something in your sort of intro there that you started—you got introduced to PostgreSQL and you were sort of talking about, and I think this is a, you know, interesting segue: a lot of people get very focused on what tool to do a job, right? Not not what like kind of tool, but the specific name brand on the thing. And you see it in other hobbies; it's not like it's unique to programmers. But, you know, I don't know—I'm gonna pick something I don't know anything about. Oh, you know, running. 'I want to go running. What exact shoe do I need?' Every Nike? You know, is it going to be Reebok? Like they get very hyper-focused. And then you hear people just say, 'No, just go run.' And like, 'No, no, you don't understand; I got to know which shoe.' And so I think sometimes people can get caught up with choosing a specific database, you know, brand or specific program or which one they're going to run. And I think we're talking about a bunch of stuff today that's going to cut across—hopefully a lot of different things in the sort of generic, the underworkings, the underpinnings—and often I'll say in some ways are more similar than they are different. And being aware of the difference is important, but being aware of the similarities is more important, maybe same important, equally important. Well, we'll figure it out. Oh, but you had mentioned something in the intro about PostgreSQL, so that was really, really interesting. And we'll drop it here just because I want to make sure we hit it, and you kind of mentioned it, which is PostgreSQL unlike a lot of other databases is open source. And so being open source, the community vibe is a little bit different. Can you maybe speak a little like your introduction to PostgreSQL? And I think even now you're still working with PostgreSQL, so like obviously it's sort of stuck with you. And why does it continue to be something that you work with?

10:52C:For sure. Yeah, and I think it's interesting you mentioned open source as the qualifier. I actually, you know, these days open source is a term; it's gotten a bit muddled because a lot of databases are open source or open core in some way or form. I think what really differentiated PostgreSQL to me is that it's not like run by a single entity; it's a community project, right? And so if you think the Linux kernel, for example, it's a very similar community project. I mean, there's actually the one big difference is in Linux kernel—there's Linus Torvalds, you know, at the head of everything versus PostgreSQL doesn't have that person necessarily. But what's interesting to me with PostgreSQL in particular is that it's 27 years old now. So, you know, it's almost as old as I am—not as not all the way, but almost there. And I'm sure some are older than some of you listening here. And I think what's interesting is that it has survived the test of time, right? Like it wasn't the fad that gone away; it actually, you know, survived the like changes that happened over the years—cloud databases, right? Like if people deploy a database in the cloud today, a lot of times it's PostgreSQL. Probably more than half the relational databases deployed are going to PostgreSQL today in the cloud. You know, caveat, caveat, but I think the like where it really comes back to, right, is that it's a community project. Everybody can, you know, contribute to it. It's, you know, many ways had all the downsides of community projects, so, you know, sometimes you might have to look for tools beyond, you know, the core product, right? So one of the things that do at my company is provide a monitoring and optimization tool for PostgreSQL called PgAnalyze. And the idea there is essentially to, you know, add the parts to PostgreSQL that, you know, are not there yet. And similarly, you know what I think has stood out of PostgreSQL over the test of time also is the fact that you can extend PostgreSQL. So we don't do much of that personally, but what many people have done—you know, there's companies like TimescaleDB or Citus Data that I used to work at personally—that extend PostgreSQL by creating extensions for PostgreSQL and that allows you to ultimately build a very different database into the.

12:47C:Core Engine. And so I think coming back to which database should you choose, I think I'm cool with any choice. So I will never fault anyone for choosing, you know, MySQL or I'm going to be it's all cool. The reason I personally choose PostgreSQL as my default is because I think it has the extensibility that covers a lot of different use cases. And so you kind of have that riddle room like if you're suddenly looking to store column data there is a way to do it in PostgreSQL. If you're looking to do time series data, there's a way to do it in PostgreSQL. And if there's if you want to work embeddings and your AI system, there's a way to do it in PostgreSQL with vector. And so those flexibilities are very convenient, right? Because oftentimes if you choose a specialized database too early, then you will just hit a, you know, brick wall, and then you can't do anything.

13:31B:Yeah, I think this is something I've heard echoed a lot. And I'll guess you kind of said stood the test of time, I guess like the other word there is like, you know, fad. So there's like fad databases. I don't know people who like you said choose let's say a specialized database and I think maybe we can talk about it briefly, but like sort of document store databases or just key-value stores with no relational part, no real query engine leave people on a lurch when they realize they need to do a query, right? Like, oh hey, I have this key-value store. It's super cool. It's super fast. Like I feel really good about myself. And then you know someone comes in and we're going to talk about this a little later, hopefully, but like someone comes in and says, 'Oh, I want some aggregation.' I want whatever. And you're like, 'Uh, okay, there's no SQL. I gotta write this myself,' or you find yourself like writing an SQL engine over your thing, like, you know. And oftentimes that's some form of premature optimization, I guess. Like people are trying to like narrow in for some future concern of what happens if I have a billion visitors to my website and you don't have one. It's like, okay, first get, you know, a handful, make some good decisions or whatever. But yeah, you kind of mentioned that like early specialization or going to a niche database. And it's interesting that you say sort of PostgreSQL has, I guess I knew that but I didn't know it, which is like I hear about extensions for all sorts of things for PostgreSQL, and it's really a way to customize it later potentially for lower cost than sort of completely ripping out and switching to a new database. It's sort of like layering on an extension do I do? I sort of have that model.

14:57C:Kind of right. Yeah, I think so. I mean, you could—I think one good example is to PostgreSQL, you know, has a lot of data types. So PostgreSQL can be typed, right? So it's not just a, you know, throw in text and it's always text. You can actually say, 'Hey, this is a URL,' and then there's some validation because URLs have certain structure. And so one of the most basic extensibilities of PostgreSQL is that you can do your own custom data types. So if you know a way that you want your data to be validated and you want your data to be stored but you want the input to be this particular text format, you can write an extension that does that for you in PostgreSQL. And so it's that right? It's the fact that you don't have to contribute a change to the core database to add a new data type. You can actually just do CREATE EXTENSION my_data_type, and that's all you need to do, right? Like ultimately, you write a C extension. If you do that kind of stuff and low-level parts, there's also a way to do this in Rust these days. So if you want to have more type safety, there's a good way to write complex extensions in Rust. And then sometimes extensions can be as simple as just an SQL script, right? Sometimes it's just literally, 'I want to have this function in all my projects,' so I can just say CREATE EXTENSION my_function, and then that's just a way to package it essentially.

16:03B:Cool. Maybe let's dive real deep here for a second. And then we'll see how this goes. This is if it goes bad, it'll be on me, not not on Lucas, but we're going to try to dive deep into database. Okay? So I guess like not every database, but a lot of databases you'll sort of hear the sort of storage and I guess even, you know, maybe this goes back to a lot like spinning metal hard drives. Maybe people don't even know what that is anymore. But, you know, sort of people talk about using a B-tree. And we don't have to go into like that's always really hard. Like describe to me what a B-tree is. No, no, let's not do that, please. But like, you know, have this B-tree, which is a generalization of a binary tree. So it's this ordered tree structure that sort of lives and we're going to insert data into. And so the idea here being we want to store something and then later we're gonna, you know, kind of query for it. So I guess like when you start at this level, this feels like not that hard, right? Like, oh, I'm just gonna insert something into a hash map and then I'm just gonna, you know, query it back out. And this is really, you know, no big deal. And so I guess sort of like the first thing is like making sure it goes on disk. So like well, it's not in memory, but it actually needs to be on disk that way if you're, you know, service dies or whatever, you know, it's preserved and you can kind of kind of load it back up. But leaving that as sort of the base and maybe I set you up poorly, but like, you know, sort of the base there, like what are what are some of the like mechanics that a database offers? We want to try to get to ultimately talking about like why queries get slow. Um, but I start putting data in this, you know, tree and the tree is, you know, ordered in some manner, and I want to write, you know, queries against it. Sort of what what kind of is the growth that leads me from there to like all of a sudden, you know, something was fast and and now it's not fast? Yeah, I think.

17:48C:This is a great topic. We'll have to go into a lot of details there, but all right. Yeah, let's go. So I think let's forget about data structures for a moment. So I think let's forget about trees or hash maps. I don't think like from a fundamental perspective they don't necessarily matter, right? Like we can definitely talk about how B-tree works, but I don't think that's actually the most important thing. I think the most important thing is to understand that ultimately what most databases that are not in memory databases do for you is that they store data in a file on disk, right? An actual file as in on your desktop in a sense. And make access to that portions of that file very efficient, right? Because like usually what that means is in a relational database, you have a table in PostgreSQL, for example. A table gets represented as a file. And then if you have indexes, each index also gets its own file. But you don't always want to look at the whole file, right? What you want to do is you want to look at a portion of the file just enough to answer the question you're asking, right? Which might be, 'Give me this particular person's birth date by email address,' or something. Right? Or like, 'Give me the top 10 users of my website,' or something. And so really what the database does for you is figure out the best way to work with those files both from a reading and a writing perspective so that you don't have to, right? Because the alternative here is that you and your own code and your own software do the same thing, right? You do file open and then file read and file write. But ultimately what you would usually most likely—what I would do is I would just literally read the whole file and then put it in the hash map in a Python or Ruby script, right? And then just look at it. And it's just not workable if you're talking gigabytes or terabytes of data. Like it's just too slow. And so really the big job of database is to make that interaction way more effective, way more efficient and then also let you write queries in a way where you don't have to write the code to do all these lookups, right? But you can actually express your intent using SQL, and then database figures out how to locate the data you're looking for. Okay.

19:43B:That was much better than I did. All right. So what I hear here is that's all right. I give it to you. No, no, no. The separation of concerns is how I would sort of term it, which is like, you know, you're writing some application that like you said sort of wants quick access to a wide variety of data past the point where you know you're going to necessarily want to just have it all in memory and all these other benefits. And so the database job is to—I think you said really well—like make the smallest portion of data available or need the smallest portion of data available in order to answer your questions, and then provide sort of an API in this case SQL query so that you can tell the database what it is that you're looking for. So and then its job is to as efficiently as possible give you back your answer. That's.

20:28C:Right. And I think from performance perspective, the one thing I'll mention that's really important to understand is that oftentimes you will—the database will look at a lot more data than what isn't the result it gives back to you, right? So the oftentimes performance issues are like you might just be looking for a single row, like in this case of the birthday and the email address, right? But the database actually has to look at the whole user's file, aka table in this case, right? And actually in the worst case read the whole file until it finds the matching row. And so really, oh go ahead.

20:58B:I was just gonna say, so what would cause that? So if I if I have a table, I have a file, like what would be the difference between able to efficiently go to the specific row versus have to look at the whole?

21:10C:Thing. So I think like ultimately what it—I mean, so the simplest thing you could do, so maybe again important to remember is that usually in systems like PostgreSQL and I think this applies to at least most relational systems like MySQL and such is that the index and the main table are separate, right? So the main table is usually—so I'll talk a little bit in detail just because it's helpful to think about this visually in a sense, right? So a the main table in PostgreSQL is usually separated in what's called pages, and they're eight-kilobyte pages. And so each eight-kilobyte page has, you know, a number attached to it like just an integer number that counts up. And then in each page you can have one or more rows. PostgreSQL calls them tuples, often for various reasons may not have to get into that, but the point is these rows that you're looking for, they're in these pages. Now if you knew which page you're looking for, right? You could very much like a phone book, right? You just look in the phone book on page 20 and you knew that there's your row somewhere on that page. Now the challenge is that oftentimes you don't know that, right? You don't know on which page the data is you're looking for. And so short of, you know, just reading through the whole like each individual page, right, until you get to the matching row. Really what you want is an index that sometimes can also answer the thing directly, but usually in many cases what the index does is just point you back at the right page, right? So the index just says, 'Hey, I know you're looking for this user of this email address that's in page like 67.' And you know, you just then read that one page of eight kilobytes. Right? This is an eight-kilobyte read essentially. And then, you know, that in that eight-kilobyte read somewhere is going to be that row that you're looking for. And that's really where indexes come in. Is just reducing that lookup that you have to do. Now the important thing to remember of course, the index is again a file. And so we—we don't just have to do I/O for the main page, the main table, the page on the main table, but we also have to do I/O for the index itself. And that's where it gets, I think conceptually more challenging. Like even I have challenges.

23:07C:These days when you ask me how much you know overhead is it going to be if you read something from an index? It's really hard to estimate that because in a B-tree, right, you have to walk the trees. You ultimately start at the root page, figure out, okay, like go do I go left, do I go right, do I go left to go right, right? At some point you end up on that, you know, thing that's essentially matching your query or multiple like index entries that are matching your query. And then these index entries will have a pointer back to one or more pages, and so it's hard to do a conceptual model for that, but I think that's roughly how I would try to describe.

23:36B:It. So right after you said it's rough, I'm going to attempt it because I want to—I want to make sure I kind of understand this. So the index is so the data that you want to feed into the query, so one or more entries like let's say email address. So it's like a mapping of email address to page number for the rest of the data associated with that row. But it could be—I don't know what the right term is—compound? It could be composite. It could be more than one thing. And so the idea is the index file gets opened and then searched for finding the sort of entries in the key there, and then it gets the value of the page, and then it goes to the actual table file, opens that, and then efficiently goes to that page sort of by number lookup because you have it. And then is when people talk about building a database table and saying this thing is the key so that rows are have some unique identifier or whatever, is that is that inherently different? Or is it still just an index file but like happens to have a shorthand name because it's so common? Yeah.

24:40C:It depends a bit in the database. So in PostgreSQL, that I'm most familiar with, it's mainly a convention in a sense that so if you have a primary key, right? Let's say we have a users table. The users table has free columns an ID and email and a birth date. Usually the ID would be the primary key. You could also make the email a primary key. And really one of the distinctions of primary keys is that they're usually—they always have to be unique. So email might not be the best choice if you want to support, you know, the same email used twice. Um so let's suppose for a moment that ID column is the primary key. Now in this scenario we're describing where we're looking for the email, the ID column actually does not get involved at all unless we're looking for the ID, right? But if you're just looking for the birth date value and we're just doing SELECT birth_date FROM users WHERE email equals something, then we never need to look at the ID. Now in PostgreSQL specifically primary keys do matter most mostly when you're joining tables and you're like trying to say this is, you know, kind of like I'm talking about this one record and there's only ever going to be one record because I already kind of am grouping in a certain way. And so it's it's mostly relevant if you're joining things and you're like using the ID column in a join. Um then it matters a lot from a performance optimization perspective, but otherwise it doesn't make a big difference if you're looking for an ID value versus an email value. They're essentially both indexes in this case, right? So primary key is an index.

26:00B:So then reversing that, I think you said birthdate. So if you're using birthdate and you didn't know in advance that you were going to be searching on birthdate, then this is where the database has no option. It doesn't have an efficient way to find birthdate, and so this is where it needs to end up going through a table or does it do some magic there as well?

26:17C:No, I think ultimately like if you just said select, let's say select email from users where birthdate equals something, but you don't have an index on birthdate, right? Um then there would be no way for the database to do it efficiently beyond just like looking through the table until it finds matches. And if unless you're doing a limit one in your query, it may have to look at the whole table, right? If you're doing limit one, then it gets lucky and the first page has that row, right? Then you know with 50 average or whatever.

26:44B:Yeah. Um and I...

26:45C:Mean this is also again where it matters. Like how queries are written matters in a huge way—how the database can optimize things for you, right? So if you're very specific, you're like I want this order and I want, you know, the top five, then that's going to be more expensive than I don't care about the order and I just want one, um because that's going to be faster.

27:06B:Okay. Yeah. So maybe actually that's a decent segue. So all right, so we have these sets of tables there the database is managing them. We have some indexes over the, and then now we're sort of talking—you were kind of moving to the next level, sort of like writing queries. And then as you said, there's um I feel like this happens in programming as well. Uh I at least I made this a lot and I've seen other people do it when you first start writing program, you're worried you didn't write your conditional correct? So you write your conditional multiple times, like or or other variations of it. Like you don't trust like oh if i is less than 50, and then like a couple layer lines later you'll say like as if i does not equal 50. And it's like well, it can't equal 50 because you said it can't be less or maybe I'm messing up anyways. You sort of like repeat conditionals that don't need to be um or you put a lot of parentheses in your math operations. And so you know, I come from C++. So this is like a big deal because everyone's always performance nerdy, but like you know oh I have a constant and a you know another constant, but I put my parentheses in such a way that I'm telling the compiler I don't want to let you, you know, pre-multiply these constants together at compile time because I'm over prescriptive about what I'm doing. Um so when we're writing queries, you're sort of saying if you're over prescriptive in some way, like you're very, very, very stringent, then you're sort of limiting the hand of the database and sort of how it can do things. Can you maybe like unpack that a little? Like what exactly are the kinds of things it does or doesn't care about? Uh and what would you sort of be looking for as like code smells, I guess, and uh you know you're making it slower than it needs to be?

28:37C:Right. And I think I—I would say well, I mean in general, you need to do what the intent is you're trying to implement, right? So sometimes you may just have to do an order with three columns and a filter clause with five different like where clause of five different um things you're looking for, right? So I think it depends a lot on what you're trying to do. I think you actually mentioned an interesting case of compilers, right? So I think that situation where you're like optimizing for certain things because the compiler, you know, does things a certain way, and so you know you have to write them with all these parentheses um unfortunately at least today you also have to do a little bit of that with databases, right? So in PostgreSQL specifically, I wouldn't even say here's one right way or wrong way to do it. I would rather say learn how to learn about the database, right? So like how do you figure out what the database is doing and how do you understand the different choices available um like in terms of how the database can find your data ultimately? Um Because generally, I would say yes, it's better to not over-specify like the where clauses, but there are exceptions. There are actually cases where I've, you know, I've seen real life situations where I'm joining two tables and adding a where clause actually improves performance drastically because it allowed a different kind of join to happen versus if there was no where clause. It had to join like read one table first and then do a nested loop over the other one. And so it really does depend a bit um, and what was most important in that situation for me was able to impose because there is a command EXPLAIN, and if you put EXPLAIN in front of a query, what the database will give you is essentially the query plan that it—well, if you explain analyze actually executes and says here is the plan I used and here is, you know, how long each part took. If you just do EXPLAIN, it just gives you the plan that it is most likely going to be used if you run it again. Um And so what that really tells you is what the database is doing. And so one of the most important feedback loops, right, like when programming might be a REPL—right? Like you're typing something in an interactive shell and gives you back a result. And it's one of the most important feedback loops in database world is I would say that I write a query, I do an EXPLAIN, I see the query plan. I think...

30:32C:It's a bad query plan. I change my query, right? Um maybe add an index so that database, you know, I try different indexes, I see what sticks essentially, right? Um But that interaction, you can't really get around like that. That just has to happen, right? Even if you have best practices, even if you, you know, don't over-specify your conditions, um sometimes you just have to like get in and do that feedback loop and do that.

30:54B:Iteration. So when you run EXPLAIN on a query and you get back the query plan, I'm sure I've done this before. I know I've done it before, but it's not something I do routinely. So it's not top of mind to me. Um But is so like let's say PostgreSQL and I send the query plan to the—I guess there's some sort of execution engine that is going to run my thing. And you were saying and you sort of mentioned something interesting, which is what keyed me off, which is like that it thinks it's going to run. So is it true that like if we had two databases that were the same schemas but yours was an order of magnitude more data than mine, and I send my query to mine, you send it to yours, would we get back the same EXPLAIN? That is like is it invariant? And it's like just a like schema and the execution engine same version, or is it somehow like understanding the size of your data, the like it's monitored queries in the past and is somehow like trying to do this like at what level is it sort of building that plan? Yeah?

31:51C:I think to a big extent this again does depend on the system, right? So I'll speak to PostgreSQL, but definitely this does vary between database systems. Um so generally speaking, it's definitely deterministic or invariant, however we're going to call it, right? But essentially it's the planner in PostgreSQL with the exception of essentially one feature I can think of right now is if you give it the exact same files on disk and you run it on different server, it's going to give you the same plan. Now the problem is that usually you're not really aware of all those details. Um I would say the most important things—um let me actually take a step back just for people to follow along more easily, right? So again like think through you're sending this query to your database. So what does the database actually do? Maybe just to like give you a visual because I find that helpful. So like query comes in, right? So the first thing the database does is actually parses the query. So the PostgreSQL engine, in this case, PostgreSQL turns that query into a parse tree. And that parse tree then essentially gets, you know, analyzed and says hey, you're looking for this particular table and such. Now the part we're talking about is after that initial parsing, right? After the database essentially knows, you know, this is what you're looking for these tables you're querying, then it actually has to figure out, you know, how do I get that data? And so that's that component that is trying to, you know, come up with a career plan is called usually the planner, or in PostgreSQL sometimes the optimizer. And that's really the part where it looks at that, you know, parse tree that comes in like the query that comes in in combination with a couple of other things I'll talk about in a second and then says here is the plan. And then what happens is that in PostgreSQL called the executor goes and executes that plan, right? And then ultimately the executor is what sends you back your query result. Now if we're doing EXPLAIN, what we're doing is we're just looking at the output of that planner component without doing the execution unless you're doing the EXPLAIN ANALYZE I mentioned earlier. Um Now looking into what does this planner actually do? Right? Like how does it come up with its result? And in most systems today, the way this works is it essentially does a cost-based estimation. Um So it tries to say if I were to go ahead and execute this or if my friend the executor would go...

33:50C:Ahead and execute? Um how expensive would it be, right? So if is it more expensive to use this index or that index? Is it more expensive to, you know, join this table first with this other table? So it's trying to essentially go through all these different variations um of how to how to get the data. And the way it does that is it attaches a cost to it like an actual cost value, which is a floating point value. Um and it says, you know, it's a tree that it builds ultimately. So it says, you know, join this first and then do that. And so like ultimately it comes up with a cost at the end, right? So the top there always sits a cost on the plan. Um And that cost is the lowest cost, right? Like that's kind of what I mean. There's there's again caveats there, but like generally speaking, it's the lowest cost that it tries to find, and then that's the best plan from database perspective. Now in terms of it being reproducible between systems, it what's important for us to look for is what is the data it actually looks for when it makes these cost estimations, right? Um And so we talked about an example earlier where you're doing that SELECT star from table limit one and you don't care about the order. That's actually going to be cheaper in terms of cost, right? Because database can estimate well most likely, you know, I'll not have to look at the whole table because I'll just find branching row at some point, and so it will actually discount the cost for that, right? It will actually reduce the cost because of that. And the way it can do that is because it has statistics about the data in the database, right? It wouldn't want to read the whole data, but it has statistics about how frequent are certain values, right? And so if it knows actually when you're looking for a birthdate that everybody has like maybe everybody inputs like 2000, the January 1st, 2000, for some reason, right because they're just like being funny. Um And let's say you're looking for that, um the database will know that that's the most common value in that table and so we'll actually be able to do different optimizations because of that and give you a different plan. Um And so when you compare between databases what matters is that those statistics that database stores are actually the same because if they're different, then you know it's going to give you a different plan. Um One last note on that just to make...

35:48C:Sure. I included that is the other thing that's really important is the actual physical size of the table and of the indexes. So an issue that a lot of people run into is they're trying to compare their development environment with a production environment, and statistics are a problem, yes. But the other issue is that the planner will know how big your table is. And if your table is 100 gigabytes, it's not going to do sequential scan, but if the table is just one megabyte, it is going to suggest a sequential scan. And so sometimes I see people asking why is my database not using my index? And the reason is well, you're running this locally and the database thinks your table is tiny, so it's just gonna not use an index because it...

36:26B:Doesn't need one. Oh wow, that's actually like several steps more complicated than I was thinking it would be. So we mentioned a bunch of really interesting stuff there: so understanding the data sizing, I can kind of see that like it's sort of a function of like you know how big various caches are and speeds out to the hard drive and the amount of RAM you're using, and this kind of thing. The statistics one is somewhat intriguing to me, which is like uh, you know, kind of understanding what are useful statistics to measure, and then presumably whenever data is modified or inserted into the table rather than re-scanning and computing the statistics, presumably it's sort of like incrementally updating them as it goes. But then using that—that actually I don't know, that's not obvious to me—that like you know, like you said birthdates, right? We know for instance there can only be, you know, in a if you have month and day only 366, I guess, birthdays that there can be, and so chances of collision are really high. The Birthday Paradox or whatever that's called. The Birthday—I don't know what it's called anyways. Uh, so so I mean not that it's getting that clever, but as you mentioned like some things uh, you know, have like skew, really left like all really small values and very few really big numbers. And you there are various techniques for kind of understanding that. And so that's actually really interesting. Are there hints you provide uh, you know, at the table to say hey, as I'm building this, I kind of know in advance I'm going to be interested in these kinds of things or these are queries I'm going to want to run later? Or that's it's just not kind of worth it? Yeah.

37:54C:And I would say it again depends very much on your database system, right? Like what it provides. So the way this works in PostgreSQL is these statistics are collected by a process called ANALYZE. There's also an automatic process called AUTOANALYZE, and so AUTOANALYZE will just run on a periodic schedule depending on the amount of change in a table. Essentially, if you do a lot of updates, a lot of inserts at some point, like that, AUTOANALYZE counter essentially has gone to the point where it kicks off an ANALYZE. But what it does then is it actually samples your table. So it looks for, I think by default 30,000 records—don't quote my discipline, I look it up in PostgreSQL documentation—but the point is it looks at a certain amount of default part of the table and then it actually by default takes just a hundred records from that. And so there is a way to customize a couple of things here in PostgreSQL specifically. For example, you can tell it to actually look at more data, right? So don't just look at like those hundred ultimately that it saves, but look at a thousand for example. So oftentimes people say, 'You know, store more statistics so we know more in more detail.' You know how often, like let's say birthdays, right? If we want to cover every birthday and we just do day a month, then you know we could just say let's raise that statistics target to 400. Keep it, you know, easier. Don't know, it to do 366, just do 400. And then PostgreSQL will actually remember how exactly how often these values showed up in its sample, right? The other thing and I'll just drop this here in case you ever get to this point but just to know that the database also offers this is PostgreSQL has a way to do what's called EXTENDED STATISTICS, and that's essentially where it collects even more statistics that are slightly more expensive to calculate, which is why they don't always do it. But then you can have things like, 'Is this column dependent on this other column?' Right? Like for example, do people with the name Lucas always have birthdays in August? I know my birthday is not August, but point is very like if there are correlations between those values, then often sometimes that matters a lot for complex career plans. And

39:50C:there is a way to instruct the database to measure that information specifically so that you get better career plans.

39:57B:Yeah, I can imagine probably similar to compiler to one. There's like a whole in-depth rabbit trail of complexity. So I guess slightly shifting off of that, the other thing I guess when you were explaining, trying to build these costs is the one thing as someone who kind of doesn't all but always kind of boggles my mind is handling transactions. So if you're doing some read modify, you know, right? You know kind of cycle or you're doing something transactionally into the database, I imagine like how the plan executes, is it just trying to minimize the sort of cost still, or is it thinking a bit about like the probability that like a write comes in and interrupts the query and it has to go again? Or how is that sort of component? Maybe maybe I'm off. It's fine if this is not relevant, but I guess that's always one of those things that all of this has to be done—yes, but it all has to be done in the phase of like data that could be changing out from under you and it has to be done consistently, right?

40:53C:And I think this is where you'll once again—you'll hear me say this a couple of times—but you'll once again see that PostgreSQL is one way and other databases do it differently. So PostgreSQL has a system called MVCC, Multi-Version Concurrency Control. And I mean, it's more of a general term, right? Others have to too, but PostgreSQL has a particular way of implementing MVCC, and PostgreSQL has received criticism for that too. Like people sometimes criticize that for performance reasons and such. But one of the most important things that the MVCC implementation in PostgreSQL does is it solves exactly the scenario you're describing, right? Where I'm doing a SELECT but whilst my SELECT is running—because maybe I'm looking at a big portion of the table, right? So it takes some time—then a write comes in. And so there's two choices here: either we block that, right? Right? So I finish my SELECT and the write is blocked, or they are able to run concurrently. Now if you implement this naively, if you built your own database, you should probably just block, right? Like keep it simple. But in a concurrent real-world system, that doesn't work, right? Because you just have too much stuff going on at the same time. And so that's really where what PostgreSQL does at the most fundamental level is if you think back to those pages, right, with those rows. So each of those rows I mentioned earlier that they're actually called a tuple in PostgreSQL. Now that distinction matters because it's technically a row version, not a row, because there can be rows that are physically there in that page that you're not actually seeing because they're, for example, an insert that came in after you started your SELECT, right? And so PostgreSQL has a way to essentially say, 'Well, this is something from the future,' right? So you can see these like even as you're doing your SELECT. The PostgreSQL executor might encounter things from the future that it's just going to ignore because they're from the future, right? And it's really fascinating because like you have to think about this in performance optimization too because you can also encounter things that are in the past and not relevant to you anymore but they're still physically there, right? Like the—because again it's a physical structure. And so it like it really the part where it sucks for you ultimately is that there are situations where you can have data that you need to

42:52C:look at but because of these visibility rules, it's actually no longer there, right? Like you're not supposed to see it. It's just you have to physically walk over it because it's in that file still.

43:01B:Okay, so it's trying to put these things in a place where you'll see them. But then like you said there's some I guess I always think like some counter, some timestamp. It is like, yeah, your query is like at some counter version and it knows like anything less than this or greater than this. Like it needs the highest value up to but not greater than your ticker value, I guess. And so it's scanning and it's seeing stuff that's crazy. I don't know. This starts to get really—and this is why I guess people always sort of have this caution where as you said if you're building your own, it's better just a block. People have this; they slowly end up building their own database and then not thinking about these things, right? Oh, I just have a file now. I need to have it updated now. I need to—and then you slowly backing your way into some of this complexity. So the query planner has this EXPLAIN function, and it's giving you these costs when it goes to the execution. It has to kind of respect all these all these rules. So as someone writing the query plan, this is we've mostly been talking about reads like joins and order bys. Are these important for inserts as well? Or or composite statements? Like how is the sort of breakdown of when is it—like yeah, yeah, it's probably not something worth worrying about. Yeah.

44:13C:I would say it depends. Yeah, I mean definitely. So anything that's more complex is going to be more interesting to think about, right? So if you're joining tables—even if it's just two tables—oftentimes that can already be an important question, which is what you're joining first? Like are you first looking at Table A or Table B, right? Or can you look at them both at the same time? It gets much more relevant of course if you're joining five tables together or you're looking only for a small part of this table then you're using that to find something else in another table. If you're just inserting data, I don't think so. PostgreSQL technically still makes a query plan for that because what you can do in PostgreSQL is you can do INSERT INTO the table and then SELECT and then you actually run a query to insert the data, right? So you are essentially running a full query just to get the data you're inserting. But if you're just passing data to the server, there is no magic to that, right? It's literally just writing it out. There's different ways to do it like there's COPY in PostgreSQL which is way faster than INSERT blah blah, but it's generally not something you need to worry about from a planning perspective. And the same applies to, you know, like let's say you're changing a schema or something. Like those are called utility statements in PostgreSQL; they're totally separate from query plans. So really it matters most of your reading data and

45:21B:then what is it? So when you're writing your queries and they're complex and you're looking at your query plans like on the balance, I guess there's this decision between like you said maybe you can't optimize your query; it has to be a certain way. Or there's just like yes, it's frustrating, but it's no good. And then you can kind of sort of look to—I guess indexes we were talking about that—but also I guess to schema definitions. Like what is this sort of troubleshooting, debugging go from? Like, 'Okay, I have this query; it's not doing what I want.' You know, we talked about the query plan, which is sort of like I guess how to mutate the query in some form to make it better. Do you've exhausted that? What is this sort of trip down this optimization look like?

46:04C:What I would do in practice, I'll just walk you through my personal debugging approach here. So imagine we have the query, right? And we—so my situation is I'm the application engineer. We're going to be back an engineer who got handed, you know kind of a SQL query that's bad from the data team or something and they're like, 'This is so slow.' You're like, 'Making the whole database slow? Fix it!' Right? And so I'm like, 'Okay, what do I do?' And so the first thing I would do again is go look at a query plan to understand what is—you know what's actually happening, right? Because I might up until now have just thought about this function call in my application which uses an ORM. So I don't even see the SQL, right? So like first step is actually looking at the SQL. The next step is looking at the query plan. And then really what oftentimes helps in PostgreSQL specifically and the same applies to other systems is looking at the physical IO that's being done, right? So don't just look at the plan, but look at how much data does actually have to fetch. And so the way you do this in PostgreSQL is you pass—so first of all, you do EXPLAIN ANALYZE, which actually executes the query. So it doesn't just say, 'Here is the plan,' but it also says when it executes the plan what part takes how long, right? And this helps you then say, 'Well, you know, in my really complex query plan we're doing joins and insert—like sorry, index selection and stuff and whatnot. This is this index scan is slow, right? Like this particular index scan we looked at the whole index, and that was really slow.' And the reason, you know, this is by doing EXPLAIN ANALYZE versus EXPLAIN. Now EXPLAIN ANALYZE has an option called buffers, and if you pass buffers—it's kind of this terminology thing which is confusing. So a buffer in PostgreSQL is also a page. PostgreSQL uses those interchangeably. So when we say buffer, is really what we mean is those eight-kilobyte pages that I talked about earlier, right? So these portions of the file on disk. And so if you pass that buffers option, it will show you how much of the file ultimately it had to read. And so it will tell you, 'I had to read 100 buffers.' And then you have to do the mental math and say 100 times eight kilobytes is, you know, this much in actual bytes. And so that way, you know.

48:02C:like which part of the plan not only how slow or fast it was but also, you know, then you know this actually took this much IO essentially. What I would do next is kind of what you were alluding to earlier, right, which is try to think about: Do you want to like—is this an indexing problem or is this more of a data modeling type problem, right? Or a query structure problem, right? So I think there are all different directions. Right? I can change the way my query is written. I can change my table definition, or I could add indexes. Oftentimes changing the query like how you write it or adding or sometimes removing or changing an index—those are the easier choices, right? Because they're really fast to do. Like indexes or cache structures of sorts, right? So you can just create new ones and the PostgreSQL planner will choose them if they're better most of the time. And so most of the time I would say, you know, start with probably start with the understanding what the query is trying to do. If there's a simple change you can make in the query, try that first because it's going to be fastest, right? Then next look at indexes and then really only if that doesn't work, then it helps to look at the actual data model. I'll give you one example of where the data model makes a big difference is—um again think back to this physicality of looking at those portions of the file on disk. If your rows like in your table, if each row is very wide, right? So you have a lot of columns in them, maybe a lot of text columns that are like have a lot of text in them, then they will take up a lot of space in each page. And so what happens is that, you know, like let's say you have this eight-kilobyte page and you have each row taking one kilobyte. So at most you'll have probably seven rows, maybe eight rows to hang out how math works out per page. Now imagine instead you had—instead of that one megabyte sized row, you had a hundred-kilobyte sized row. Suddenly you can fit 10 times more into each page, right? And so the thing that matters there is if your data, if you have a really wide table that can sometimes be a big problem. And so it does sometimes make sense to

50:00C:essentially think about making tables small from a physical perspective in terms of each row being small, like having less columns or having less text columns in particular because then you're optimizing for that. Yeah.

50:12B:So that's I mean, I guess that's like a pretty universal like a data locality thing which is like, 'Oh hey, I want to if I have a JPEG attached to the row, it might be better to like insert it with an ID and then put the ID in the row.' So that way, like as you're mentioning, you don't have to physically read in pages to get through the data to get through the JPEG. Maybe handle JPEG separately, but you know you don't have to like physically read in multiple pages trying to get to the next page of actual data that you're interested in. You're trying to make it really compact so that the number of rows through the reader go as fast as possible, like the highest throughput. And you do that by moving data that's unlikely—like you're not querying for text inside a JPEG, you know, it's stored it sort of stores externally or differently so that you know your queries run fast. That's right.

50:57C:And I'll add one quick thing just so that people don't get confused. If at some point you do dive into this detail, one important thing to know in PostgreSQL in particular is that if your data gets beyond a certain size, there's actually a separate storage for it. So PostgreSQL—would you just describe? PostgreSQL has a way of doing this, right? So if you for some reason do decide to store a JPEG, which you shouldn't do—don't store images in the database—but if you do that for some reason and they are like multiple megabytes in size, PostgreSQL will actually store it in what's called TOAST. Not the, you know, thing you eat, but the extended, was it? The oversized attribute storage technique, I think. Okay? But the point is it's a thing. And so if you have really large values, they're actually less of a problem. The issue is more if you have these medium-sized values, right? So these things that are kind of—they're not large enough to be stored separately but they're large enough to mess up your page structure. That's the issue.

51:48B:Okay, so like it's basically you confuse PostgreSQL. Like it doesn't know if this is something I'm actually going to need to access or if it's just a blob. And so there's like some ambiguous overlap where yeah, like it doesn't know. It doesn't know where to put them. Okay? That makes sense. And then good database recommendation: don't put your JPEGs in the database, but I know people are still going to do it, so definitely happens. Okay. So it has the indexing, you know, like you said lastly about the schemas, and I think this is like one of those things too that get fairly debated and approached. But I'm a big fan of thinking about your data models and in general, even for code, I think it's underthought about. And so people don't really sit down and think like how does my data actually need to look? What goes in it? People just sort of start writing code and shoving stuff into classes, structures, tables—like it's sort of all manifesting the same sort of problem. Sitting down, but you get into this discussion where I guess you hear people talk about normalizing or denormalizing your data, like you know, you're all like the star pattern and data warehousing versus anything. I mean, feel free to just be like, 'No, I don't want to talk about that.' But any commentary or thoughts on like is it better to put as much stuff locally into the row, put it into separate and do joins? Like what is your philosophy sort of?

53:05C:I mean, the one thing I'll mention: so denormalizing or normalizing is important, right? So the—I mean, the most let's put this in layman's terms. It's like do I have one copy of my data or multiple copies of my data, right? So sometimes it makes sense to essentially write the exact same value to multiple tables because then you normalize it, and the benefit of that can be that you don't have to look at the other table to get the data, right? But maybe even like—so this is one case, and it's very situational—but I would say don't denormalize unnecessarily. Like in the simplest case, start out by just sorting your data once. Like it's going to be smaller, it's going to be easier. And then if you encounter issues where that's not feasible, then think about denormalizing. The other thing I'll mention because I think there's oftentimes we talk about this very beginning about document stores versus relational databases, and one of the things that PostgreSQL actually has—this data type is called JSONB. And JSONB is essentially a binary variant of JSON which is slightly more optimized for indexing. And so the big benefit of JSONB is that instead of you having to specify each of your columns that you're going to need, you actually just have this, you know, JSON column or JSONB column rather. And this JSONB column is just storing a JSON document, right? It's just storing key-values, and it can be nested and all that stuff. And so what you can do that way is you can just throw your data into PostgreSQL and then query it without having to have that rigid structure there. There are limitations, and it's not going to be always—it's definitely going to be slower in some ways than just having separate columns, but if you're unsure about the structure of your data, right? If you don't know yet maybe there's method data attached to your objects, then using something unstructured like a JSONB field is the way to go and is what I would do oftentimes. So I guess it just is: don't optimize too early, right? Like rather keep things normalized, do a JSONB column where you need them to have that unstructured information, and then if you need to, then optimize the structure later on.

54:54B:This has been super useful. I've—you know, know what databases are, and in fact, I've tried to encourage people; we probably should be using more of them, but I've generally avoided them in my life. So I've never had to run down this, but I always just sort of it's one of those things that kind of like—it's a little confusing. It's pretty in-depth, and I feel—and I don't know—and we're going to kind of try to transition this here, but for me, I feel like it almost became these techniques you're talking about are critical and useful, but it once became like oh, when I was coming out of school there was like a Database Admin was like a big thing, and then people did not want to be Database Admins. I don't know, like the current zeitgeist around Database Admins, but a lot of people just got this like, 'You know, I do not want to do looking at queries,' and 'I do not want to do optimization.' Like that is a dead-end job. You do not want it. You're going to be like, you know, stuck racking servers or whatever the—like the mindset was. I don't know where it came from, but I feel like there's like lingering aftereffects through even to today where people have this hesitancy to like engage with, you know, databases. Yet we see like all the major tech companies very reliant on quite traditional uses of databases. You know, they gussy them up—is that? I don't know anyway, make them fancy by like, you know, sharding them and distributed and all of this. And we've had great podcast interviews with folks working on that, but I feel still like at the engineer level, to engineer level, there are a lot of people who just would rather avoid it for—I don't say like stigma reasons, but it's been great to hear your explanation of this, and like it's really not that different than compiling and debugging stuff you would do or at least I do, you know, day-to-day, just like in sort of C++ code and making code run faster and don't use more data than you need to. It's all the same concepts.

56:44C:Right, and I would say, you know, administration is uncool, but you don't have to call it administration, right? Like it's the same with System Administrators. Like people didn't want to be called System Administrators anymore. Maybe other new people coming into industry didn't want to be called administrators. And so now we have DevOps engineers, right? Like it's the same thing with databases. Now people are called Data Platform Engineers instead of like DBAs. Like I think you pointed out one thing that, if I think about database performance work, right? The part that I really enjoy is I can make a difference pretty easily often, right? Like it's oftentimes like you can get drastic performance improvements. Like something takes multiple seconds and people have a slow experience to milliseconds, and you know, it's super fast, and that is really rewarding, right? Like as somebody working on that, as somebody as an engineer working on it, it's really rewarding to kind of get that kind of performance benefit, the performance improvement by doing boring administration in a sense. I'm—

57:37B:With you. I mean running the like debug tool timing code and like seeing literally like 7,000x speed ups, right? You know, like you said something somebody has some program it takes minutes to run, and now it runs in like a few milliseconds. This is something I find enormously rewarding, but it's this grunge work of going in and putting the logging in, like paying attention. But maybe you and I are broken the same way. I find that enormously rewarding because it's just like ha ha, you did this, and here's my—it's like code golf, I guess, or whatever. Like, 'You know, here's my speed. It's so much better than your speed.' So all right, I was going to transition it now. So we opened up with that your founder at this company PgAnalyze—I guess tipped off by you've already said the word Analyze and PostgreSQL shortening to PG. There, I think we might be teed off to what is a little but maybe help explain like what your company does and sort of like a little bit about it and just sort of like tell folks what you're up to.

58:31C:Sure. And I'll try to put this in, you know, from the perspective of why I as an engineer care about that and why I started a company. So let me give you just a little bit of background on how the company came to be. So ultimately, I started as a side project which is close to 11 years ago now, so it's been a while, but I've only been, you know, kind of full-time in it in the last couple of years. And so we now have a small team, essentially supporting it. And ultimately what I set out to do with PgAnalyze at the beginning was giving me better introspection to database, right? So back then again all focus on PostgreSQL, right? Pointed out PG for PostgreSQL. And so the like the reason that I started that as a project back then was that I tried to say, 'You know, what does the database think is going on?' Right? Like if I look at the database and I see like CPU utilization or IO utilization doesn't really tell me much. And so the very first thing that we did back then was just query performance metrics essentially. And so it was just saying, 'You know, here is this query that was running,' and 'This query took the most time.' And there's various ways in PostgreSQL to get that data and to kind of say, 'You know, this is a query that's essentially making the system busy.' And so that's how we kind of set out to, you know, kind of just have a way to say here is what's most slow on a database. Now over the years, and especially more recently, what we've turned this into is not just, you know, kind of that monitoring and observability side, but really also giving recommendations. And so we already touched upon indexes earlier, but one of the things that I'm—I think reasonably proud of is our implementation of how we make index recommendations. Now it's actually not as good as I'd like it to be, nowhere near it, but it is a, you know, I think a system that is a really good starting point that says, 'You know, here is my query workload. Here are your suggested indexes.' Essentially, like, 'Here is what's missing.' Right? Like, 'You're querying for these things, you're doing these WHERE clauses and these drawing clauses. We think, you know, this index would be helpful to make your queries faster. Go try it out.' Like human-in-the-loop type system, right? Like actually try it out and see if it makes a difference.

1:00:28B:Well, that's awesome. So so you guys—so how I mean, so you're an extension? You're sort of like how does that—how does it like integrate? I have a database I'm running, like your service comes alongside and sort of monitors what mine is doing, and then I'm able to sort of get these suggestions and try them out. So,

1:00:45C:We'd love to be an extension, but we're not. So the problem with extensions—this is kind of a technicality—but the sad part is extensions require you to usually have access to a database server. And what we find in practice is that most people today don't run their own database service. They use managed services like AWS RDS or Google SQL or Azure Database. And so the issue is that you can't really write the custom extension and run it, have your customers expect to run it, right? I mean, you can yourself can certainly run it, but we intentionally did not require any custom extension. What we do is we, you know, have people install an agent, and that agent essentially sits next to the database, right? Like it's in, you know, a container or in a virtual machine, and essentially it runs SQL queries itself to get data from database about like statistics that are happening. And then the other thing it's doing is looking at the error logs. So we do a lot of log parsing because that's where we get additional information, like which query plan happens at which time. Like there's ways to tell PostgreSQL to log query plans, and so we essentially pick up those query plans to then kind of put them in a more easily accessible UI. Oh.

1:01:47B:Wow, that's awesome. That's interesting. Yeah, I guess I didn't really think about that, but you're right. Like most people are probably not like, you know, installing locally PostgreSQL database and running it, and they're running it somewhere in the cloud. So like adapting to that and still being able to make it work—I guess like it's even less obvious to me that it would work, but yeah, the fact that that you guys here that's actually really cool. And so this started as like sort of a side project is clearly something you're passionate about. I mean, like we've sort of touched on these subjects sort of all together and building a tool that sort of like helps you do the things that you would do naturally but like faster and, you know, for people who maybe don't have that background or expertise. And really helping people who, you know, run into slow queries and be able to sort of like help break them open and and sort of look at them. Have you guys felt like that people is it much—is it people who are like pretty experienced and saying, 'Oh, these are actually just incredibly time-saving,' and I know I know that I can tell what it's doing is smart and good and I like it and I want to use it to save time? Or is it more people are like, 'Yeah, I have no clue. Like I'm just going to, you know, click yes whatever it says,' and I'm just going to trust you to be in charge of everything?' Yeah.

1:02:56C:I would definitely say it's both. You know, it really depends. So, you know, the good thing is we have enough customers that I definitely don't know all their names and you know I don't know all their use cases either. But I think one pattern that I definitely see, and this is for the folks listening if you're currently in college, for example—you may this may be if like at least conceptually a long way from where you're at—but what's out there often in industry these days is that you have usually what's called Data Platform Team. And so there's often in big organizations there's a central team that operates data stores, right? In databases. And so one of the things that we found is that those centralized teams that manage the databases, they ultimately work with application engineers, right? Because application engineers write the code; they make all these feature changes. And so the challenge that they have is that they are usually just a small team. Like you might have 100 application engineers, 200 application engineers, but there's just like five Data Platform Engineers that are like wrangling all the databases. And you know if any of the databases is down or slow, everything is on fire, but there's still just this tiny team. And so really where we come in and where we found making the biggest impact to say is just enabling those teams to then ultimately hand off more things to the application teams, right? So to give better tooling to the application team so that the application team can say, 'Hey, maybe this is an obvious issue that PgAnalyze can help me identify,' so I don't need to spend as much time with that, you know, really, you know, over-whelmed Data Platform Team. And so it just becomes that way of kind of collaborating more effectively. That's awesome. And then—

1:04:28B:How like I guess like a bit a bit on an adjacent topic but a bit off topic, sort of taking something that I think you had been doing for a lot of years, you know, had kind of been like you said a side project turning it into a company. How has that experience been? Like any sort of introspective tips or suggestions like people out there? This is like for anyone who has—is sort of new. You will have these thoughts. You will sit someday in a big company if you ever go there, and you will think, 'Why am I here? I could do this on my own. I could be making more doing this on my own.' If you're not at a big company, you're going to say, 'Why am I doing this? I could just be at a big company.' You know. So these thoughts are at least for everyone I've ever spoken to; these are always sort of at war in our heads. But from someone who's sort of—I think you said you had been at Microsoft a while now, you're sort of doing this on your own, you've kind of played both sides of it. I guess like any any sort of thoughts or tips from the trenches?

1:05:19C:Yeah, I think you know there's the saying: if you like drinking coffee at a coffee shop, don't create your own coffee shop. Right? So, like, don't start your coffee shop because you would wish there was a coffee shop around the corner, because running a coffee shop is not the same as drinking coffee at a coffee shop. So this is

1:05:35B:Great. I'm paraphrasing, but

1:05:37C:You know what I mean? Right? Like there's like running a business is definitely not the exact same thing as you imagining using that business as an end user or imagining it exists. Right? So I think there is something to be said about do you actually want to run a business because it—it's great, don't get me wrong. I really enjoy working on my own terms and you know having a team of folks working, you know, with me, and kind of like that's all great, but it is not necessarily the same as saying I'll just go and create a project for fun. Right? So I think there's an important distinction there. Now, I will say that it's definitely possible, but you have to have a lot of patience. So um, I'm very much—I mean, we bootstrapped the company. We have no outside funding, and you know because we bootstrapped it, right? That's why it was a side project for a long time is because you know revenue just wasn't there in the beginning. Right? It just took a long time to even be able to pay one person's salary. Um, and if you're willing to do that, I personally believe that most people listening to this will be able to create their own business if they really go for it. Right? But the challenge is that you have to have that longevity. You have to have the motivation. And so I think what I would do if I would do it again is focus on something that you enjoy working on, um B can use yourself. Right? So ideally, you're your own customer in some way or form because then it's just much more motivating. Um and you know C, you actually enjoy building the business side of it, not just, you know like you actually want to run the coffee shop.

1:06:55B:Yeah, I guess like for folks out of the industry—I mean, I guess that like 'eating your own dog food' was like a very weird term to me the first time I sort of heard it. Everyone sort of takes it for granted now, but like I was like what? It is a weird term. I was like this is, but this is like interior, and people even say 'Oh, Dog Food Programs.' And like there's all these, and there's even other variants I won't go into for different big companies, but this is like a very common term which is you know what Lucas is referring to here. Is like if you can build something and be your own customer for that thing and like force yourself to use it. Like that just makes the iteration cycle better. Like you're teaching yourself something. You're staying engaged with it. Um I think, you know, we talked about at the beginning, you guys started playing video games. I feel like video games are a very obvious example because you're like, well, of course I would play this video game. And you—you I don't know anyone who would sit down and say, 'I'm going to build a video game I don't want to play.' Like everyone sits down and says, 'I'm going to build a video game that I want to play.' And so they're inherently saying, 'I want to build something that I know whether or not they enjoy playing it by the—hey, that's a different question.' Um but maybe this loses some of its obviousness as people sit down and they try to say, 'I just want to build a business.' Um And then this thing you mentioned, I think was great too. This like for folks who don't know this: bootstrap versus like taking external investment is a very tough decision. And you know, I haven't been myself, so I won't pass any knowledge or wisdom there, but it's it's great to hear someone like you know share some examples. I think you hear a lot of pros and cons of both sides, but clearly a really big passion project for you, you can kind of like hear it come across that you know even in the beginning I shared, you know Lucas making sure people want to know that you know he's technical, but I mean, I think that's pretty clear from the explanations. But uh you know, I think like it's exciting and you know it's been great talking to you, and I learned a lot today. I mean, this is like a huge gap in my knowledge. You know, from like what actually happens. Like I know—I'd like a really low level. I know what a B2 is at a really high level. I know what a PostgreSQL server is, but like

1:08:54B:kind of unboxing some of the middle parts and some of these optimizations and what happens to you as well as like a ton of tips and tricks along the way for sort of heuristics about when to. This has been great, Lucas. Is there anything else you wanted to like tee up? We'll have the link to pganalyze, just pganalyze.com, so it's pretty easy to find. Anything else you want to like send people to or you know recommend them to look at for

1:09:18C:Sure. And I'll actually add one more thing that we talked a little bit about earlier but I just make sure to mention it for this audience in particular is if you're interested in databases. Um well, there's two things I'll point out. So if you're interested in database in general, CMU actually has a course on databases where they publish all the lectures online. It's really good on databases more broadly. Um so if you want to look for just CMU and Database um things, and Andy Pavlo is the one who runs that. That's I would say you know one of the best online materials to learn about databases more broadly. Um if you're interested in PostgreSQL in general, one thing I would recommend is Postgres like talks about everything in the open. So PostgreSQL has mailing lists. It's old school in that sense. Um so you can actually follow along, which is really cool. You can actually follow along PostgreSQL development. It's really technical, right? But if you ever are really interested in this like at the lowest level, um you could just subscribe to the mailing list and you can just see what people are discussing and how new features get contributed. Um So that can be really fascinating if you're into that side of the house. Um And if you know want to contribute to PostgreSQL, there's also each year Google Summer of Code where PostgreSQL participates, and so you can actually you know kind of have an official project and a mentor um that help you kind of you know contribute to PostgreSQL. Now on pjanalyze, um we do um I do a weekly video series called Five Minutes of Postgres. So if you're interested in Postgres, um each week on the pjanalyze YouTube channel there's just a five-minute video where I talk about what I found interesting that week. So sometimes that's usually it's other people's blog posts that I use as a starting point, but I'll talk about things like the slow career optimization that we talked about earlier today, new features and new Postgres releases, so just a way to stay on top of Postgres. Um And then pjanalyze speechanalyze.com um we're also on Twitter speechanalyze awesome.

1:10:59B:And uh also kudos for like the remembering multiple punch list things that to go through and get back onto onto topic. That was very smooth, very impressive, Lucas. Well, I've enjoyed having you on the show. I think I hope you know folks find this as enjoyable as I did, as educational. And databases are a very important topic for our industry and lots of different avenues and directions. And so I've learned a lot today, and it's been great to have you on the show. So thank you for coming.

1:11:28C:You so much for hosting all

1:11:30B:Right? And we'll see everyone next time. See everyone later. Music by Eric Barndoller.

1:11:49A:Programming Throwdown is distributed under a Creative Commons Attribution-ShareAlike 2.0 license. You're free to share, copy, distribute, transmit the work to remix, adapt the work, but you must provide attribution to Patrick and I and ShareAlike in kind for her for her.

1:12:24B:For her for her