Postgres Performance at Any Scale with Lukas Fittl

Get ready to discover some of the most actionable DevOps techniques and tooling, including performance and reliability with some of the world's smartest engineers. Hey, I'm Joe Colantonio, host of the DevOps Toolchain podcast and my goal is to help you create DevOps toolchain awesomeness. Hey, it's Joe and welcome to another episode of the Test Skill DevOps Toolchain podcast. Really excited about today's episode. Today we'll be talking with Lucas all about Postgres performance at any scale. If you don't know, Lucas is a self made engineer and entrepreneur carving out his own path to success. Really interesting journey he's taken. He started at Product Hunt as one of the lead, I think engineers, which I think we'll dive into, and Citus Data, where he also helped shape the business ecosystem still thriving today, which I think is important. Just shows to the skills he must have. Also, he's known for creating open source game changers for the Postgres systems. Basically I could tell by LinkedIn he has over 12 years of experience in this area. So definitely he's an innovator and a developer's ally. His latest venture is PJ Analyze and helps propel tech giants like Atlassian, Doordash and a bunch of others forward with tools to optimize for Postgres performance. A really interesting topic I think a lot of people miss out on. You don't want to miss this show. Check it out. This episode is brought to you by SmartBear. As businesses demand more and more from software, the jobs of development teams gets harder and harder. They're expected to deliver fast and flawlessly, but too often they're missing the vital context to connect each step of the process. That's how SmartBear helps. Wherever you are in the testing lifecycle, they have a tool to give you a new level of visibility and automation so you can unleash your development teams full awesomeness. They offer free trials for all the tools, no credit cards are required and they even back it up with a responsive award winning support team show on your path to great software. Learn more@smartbear.com today.

Hey Lucas, welcome to the Guild.

Thank you for having me.

Awesome to have you. So as I mentioned, the bio, you've been at this for a while. I guess the first thing is what got you into databases and Postgres for sure.

Yeah. So the first time I used Postgres was back in 2007. So by now that's what like 16 years something. And back then Postgres was already old. Like Postgres this year turned 27 years old. So it's been around the block and at the time Postgres was just the best choice essentially in terms of open source database. And I still think today that if I have data to store, and especially if I have relational data to store, postgres is my main choice. And it's followed me throughout my career either as a user of Postgres or as somebody who extends Postgres and builds businesses or products or technology, open source libraries on top of Postgres.

So why choose Postgres? Because there's so many innovations and I'm sure there's other options now, tons of them. Why still stay with Postgres?

No, I think it's a great question. I think people should always ask themselves the question, right? I think it's easy to kind of cargo call things and just be like, you should always use Postgres. I don't think that's true. I think think the reason I personally prefer Postgres over others is extensibility. So I think what made Postgres unique is that, you know, it's again, it's 27 years old, right. So it has kind of grown up over a long time. But what really kept it alive, I would say in many ways is extensibility. So you can do things like PostGIS for storing geospatial data. You can now do vector embeddings with pgvector in Postgres. And all these are extensions to Postgres that don't require the core system to be modified. The other aspect that I find really fascinating about Postgres is it's a true community project. So there's not a single company that runs postgres that you know, like could take it away one day. It's really multiple companies, multiple engineers coming together and creating this community project in the open. Right. It's kind of Linux kernel in that sense. And I think that to me is the kind of project that I would want to see thrive essentially.

So this may sound like a dumb question. So when I started we used to have DBAs and they were like gods almost, you know. And now as we go towards DevOps type deal, who's responsible for databases? Is it still, do we still have a DBA for postgres or is it more now a team that's responsible when they're in a Sprint or something like that?

Yeah, it has really changed, I think, as you say, right. Like 20 years ago you would definitely have had somebody with the Job title, dba. And I think that has mostly gone away. Like people have sometimes like application DBA type job titles or like they are called DBAs. But really the role has changed, right? I think we used to have this DBA role that really owned a lot of the database and kind of tuned the indexes, tuned the settings and these kind of things. And I think what we've seen is that it kind of splits up where the application teams that develop the features and develop the applications, they own a lot more essentially of the indexing, for example, of the database. And then we kind of see the emergence of data platform teams that essentially run the data platform or run the data team.

Right.

And they usually are essentially like in most companies I've seen you have, you know, like larger setups, you have like 20 or something application teams, for example, and you'd have then a core team of like data engineers that is, you know, four or five people off and that that team essentially maintains the postgres databases, makes sure the right automation tooling is available for these application teams. But then, you know, a lot of the choices that the DBAs used to make are now actually shifted to the application teams versus that central team just becomes kind of the escalation path if there is a true, you know, downtime problem, for example.

Absolutely. So I assume Postgres been around for 27 years. There are probably known performance issues to begin with that an experienced person would know about. But if you no longer have the role of dba, are there some issues you see teams encountering when they have to use Postgres? Because maybe they just don't have the historical background or the database background of an admin. Yeah, for sure.

I mean, I think, as mentioned, I think the biggest challenge we see is that responsibilities have shifted towards the application teams, but they don't necessarily have the right tools either or have the right experience always. Right. And so the most, like the example that I've spent the most time on would be indexes, right? So choosing which indexes to create, which index types to use, you know, how to look at your queries. Because oftentimes people use orm so they don't even think of queries, right. They think of function calls on the application side. And so I think that really, you know, has made it challenging essentially for people to even learn in the first place how things work. And I think the good thing, of course is that Postgres, again is open source, is something you could always, if you spend the time, you'll find out how it Works, right. You just need to dive deep enough, essentially. And that really is then a question. Who becomes that expert in a company who becomes interested in actually solving the performance problems, let's say.

So I know like we mentioned the bio, you've done a lot with postgres. You've created a bunch of different tools. One of them is pganalyze. I guess before we go any further, what is PG Analyze and why did you create it?

For sure, yeah. So I started Pganalyze initially as a site project a little bit over 10 years ago now. So it's been a while as well. And essentially the starting point of pganalysis was how can I see which queries have run on the database over time? Essentially kind of the role that an APM tool solves for applications where you can see which requests have run and which transactions have happened and such. How can I do the same thing? But I can do it on the database side. And pganalysis wasn't the first tool to do that in the database space in general, but it was, I think one of the first ways of doing this for postgres is initially just, you know, solving my own essentially problem. And then kind of as a SaaS solution is what PGAanalyst has grown into over time is from that initial query performance monitoring perspective to essentially the, I would argue, best tool in, you know, a either application DBA or you know, data team or application teams tool belt to work with postgres and to understand postgres performance. So it's not just query metrics, it's, you know, which indexes are being used, which indexes to create also. So it has like a recommendation system, what would you call the advisors essentially and a lot of other things I could talk more about. The gist of it is right, like it helps people make their database faster through being essentially a service that sits besides your database and helps you make it better.

So I'm always looking for tools to help people with their day to day operations. And I know there's other database performance monitoring type tools. What would you say would make your solution different? Or if someone's listening going, oh, that's the feature I really think would help.

Us, I think, I mean we could talk about features all day long probably. I think the maybe in terms of principles, right? Like what when we build the business and the product, like what do we care about? And for example, one decision we made really early is that it is about postgres, not about any database, right? So we are very intentionally saying this. If you're using MySQL, maybe there's a better purpose built tool for MySQL right. But we are really just building this for Postgres and we make this the best product for Postgres to optimize performance. What that allows us to do, for example, one thing we do behind the scenes is we actually run the Postgres Planner. So kind of the query planner that says this index is to be used or this other index is to be used, we actually have a copy of that inside the application. And it lets us answer things like what should the ideal index be? What would postgres want to see here in this kind of query planning mode? So these kind of things, they're only possible because we said Postgres is our bread and butter. That's what we focus on. And so we kind of have a lot of functionality that really tailors to that.

Nice. So, you know, SQL statements and queries have always been the downfall of any database. Back in the day that I used to have to deal with, it seems to still be an issue. So. And it was really difficult to identify which queries were causing issues when I was doing like a performance test or something. So it sounds like, do you have like a sniffer then that listens to your application? Then it automatically knows when someone does a transaction it can track over time, like, oh, this, this is a query you should look at. Or this is the slowest one. How does that work? Sure.

So the good news is, on the postgres side, there's an extension to come back to the extensibility portion called PGStatStatements. And PGStatStatements is part of the contrib set of extensions. So contrib is kind of the. It's a folder in the postgres source tree. And so it's kind of officially maintained. So, you know, it's made sure that it's released with each new version. And so PGStatStatements essentially tracks all the queries that have run. And why it's so useful is because it tracks both fast and slow queries. And it does that as the query executor essentially enters the execution phase. It kind of remembers this is the query it's executing. And then when it's finished, it saves the runtime, it saves some other metrics like how much I O did it take and such. And so at the simplest kind of aspect here is essentially taking that data and diffing that over time. So postgres essentially just has counter values that count up each time the query runs and it kind of groups them together. And so we kind of take that and we diff it and we say, you know, in this timeframe, which queries have essentially finished executing in that timeframe. And that's the core data point there, I would say.

Nice. So I guess if someone's new to databases or all this, why do you think slow queries can be the downfall of an application? Sure.

And I think the way I would like to think about it usually is in most applications, like imagine you have a web request, right? And a web request issues a bunch of queries. Most applications will issue queries sequentially, right. So you don't actually like. Even if you. Let's say you do 20 queries as part of your request, usually you run them after each other because you kind of next queries based on the first query's result. Maybe you do an API call based on the queries and stuff like that. And so the problem really becomes, you know, let's say you have these 20 queries and then each query takes one millisecond, right. Then your whole web request, in the simplest math, takes 20 milliseconds, right? That's great. That's probably acceptable. Now, let's imagine each query took 10 milliseconds, right? Then your whole request takes 200 milliseconds, probably still acceptable. But what if each query took 100 milliseconds or 1000 milliseconds? Then suddenly you get into territory where your web request is visibly slow, right? Like there's a frustrated user at the end that essentially has to wait for that SQL query ultimately to finish. And so that's why I think the query performance is oftentimes bad query performance leads to bad user experience. And that's ultimately why you should care, Right. Is that it causes bad experiences.

Absolutely. And what little I know about databases, I always think of indexes. Oh, have an index. That'll help. So how does indexes in postgres affect query performance, if at all?

I mean, they do. So it's the same way that indexes in general help with performance. So I like to think of indexes as a kind of caching data structure. Right. So imagine maybe if you're writing applications, the difference between a list and a hash map, where you can just look things up easily. Right. So an index is just a way to access the data more efficiently. So if you don't have an index, what postgres and any other database system will do usually is they will just walk through the data. So imagine a big file on disk, right? And postgres will just read that file and go through it sequentially and read each row and compare it with what you're searching for. And obviously, if you have a large file, if you have a large table that's slow, that just takes time. And so the way to think about an index as a very general way of saying it is just a way to get to the rows you're searching for faster. Now, postgres has different index types. For example, a B tree index is a very common index type. Like B trees have been around longer than Postgres, I think. And B trees are very useful because they 90% of the time will help you actually answer that. Which rows are matching question really fast? Because usually especially let's say you're doing an equality comparison. So you're saying which of my users has this email address, right? And you have a B tree index of email, you essentially walk the tree and then you find the matching kind of node and then you're able to say either here's the result data if it's in the index, or sometimes it goes back to the table to actually get the full row. But that type of lookup is much faster and requires much less I O.

Is there anything people can do when they're developing the database with tables that can help with performance overall that they should keep in mind?

Yeah, no, definitely you can, right? I think to me it always comes back to the database has a logical model, but ultimately that maps to a certain physical structure, right? So, for example, when you think of which columns to put in a table, it's important to think of alignment, for example, so the order of your columns matters because ultimately what postgres will do is it will write out things in the order that you put them in, right? So the best example here is, let's say you have a platform that has like 4 byte type alignment or 8 byte alignment, and you have very small fields. It's actually better to put the very small fields like a Boolean or small int next to each other, right? So you're essentially more efficiently packing things. Similarly, it's usually more efficient to put a variable length field, like a text field at the end of your columns, because that means that the fixed size portion essentially can be accessed more efficiently. So even such a small, seemingly very small detail. Right. Matters because ultimately, again, you're working with files, right? The database just works with the same basic primitives that each other kind of server software essentially has to work with. I think when we're thinking of multiple tables. Right. So when we're saying, well, should I put this into this table or the other table? It does come a little bit to a very important postgres called vacuum. Right? And vacuum is something that I would say, you know, is particular about Postgres. It's not a unique problem. So if you looked at, for example, MySQL and InnoDB, it also has, you know, some similar problems in some cases. But vacuum, I think, has been talked about a lot, especially recently. I think there's some ways where new projects try to go away with the need for vacuum. But I think in reality, most people today will need a vacuum. Now, what vacuum does is essentially cleans up that rows and. Well, I'll give you a quick version of this because it helps set the context for widen the table structure matter. So, essentially, when you're doing an update on your table or delete on your table, postgres will not remove that data right away. Instead, it will mark that data as being dead, essentially. So it kind of sets a little flag at the beginning of the row, and then later the vacuum or autovacuum process comes around and cleans up that dead row. The reason it's built that way is because postgres allows you to essentially have a lot of concurrent sessions. And so when these sessions are concurrent, you want to make sure they have a consistent view of the database. And so that method essentially is built this way because it allows you to, you know, essentially delete it in one session, but still be able to read it in the other session. And when the other session is finished, then essentially it can be cleaned up by the autovacuum process. There's other ways to do this, but in Postgres, it's built this way. The reason this matters is because autovacuum actually has to scan over your table, at least portions of your table. And so if your tables are small, that's faster. Right. And so, for example, it helps if you have a table where you're updating a lot and you're updating just a single field. Like the most common example would be, maybe you have a queue type table or you have a timestamp you're always updating. It actually helps to put that into its own table. Right. Kind of keep that separate, because then you're kind of essentially like making that background process work better.

Absolutely. So it sounds like you have to know. It depends on the context of your application, what you're doing. I think a lot of people also probably get in trouble with Configurations, how do they know how to configure postgres for their particular database needs? Are there any best practices for that to get the best performance?

Yeah, and I think it's interesting now also in the cloud, right, because you like, if you use a managed service like Amazon rds for example, or Amazon Aurora, the providers will often do a, I would say basic tuning for you. So they will make it dependent on the size of the instance you're using. And so there's some, I think, general best practices just in terms of like one example in Postgres, for example, would be in the context of vacuum. Vacuum has a control of how fast it's running and you would want to make that kind of size it based on your instance size and based on your instance I O, for example. And so if you use Amazon rds for example, they'll just set some parameters for you that are a good starting point, right. Where it really falls down is once it becomes workload specific. Right? So where a provider won't help you, a managed service provider won't help you is when it comes to you have again this table, for example. This had this particular update pattern. And so there are settings that are table specific that you can set for vacuum or for other purposes. And so with those situations, it's actually important to understand where in your system is a bottleneck, right? Which table is essentially being the bad one, so to say, right? Like which table needs the attention where you actually go in and you say autovacuum run more often or the fill factor of a table, reduce that to encourage more hot updates. It's a long story, right? But essentially there's these table specific tunings that really are hard to do generically, essentially. Right. So you really need to understand the activity of your database to do that.

So besides that piece with the tables can identify any other issues, say like I o bottlenecks that might come up or especially as you go towards cloud, like you mentioned, does that let you know what disk type to use or anything like that?

Yeah, I mean, I think in a sense, you know, it's kind of sad because with the cloud and with network storage in the cloud, we are actually dealing with a worse storage situation from a database perspective, right. So the, if you want your database to be fast, the best choice of storage would be like local instance local storage, right? So if you use like the I series in aws, for example, right. They have a local nmve drive that's right there on that same server and that's going to be Best for relational database in terms of speed, because the latency to the disk is very low, right? And oftentimes postgres will actually have to wait for an I O to complete to then do the next step in this process. So in that sense, you know, if you can, like, if it works for you instance, local storage is best. Now, in practice, that doesn't work for a lot of people, right? And so I think what's helpful if you do build your own VM with your, you know, your own storage configuration, just make sure you test that, right? Like, just make sure you test storage latency, because storage latency is going to be the biggest driver of problems, I think, followed by throughput. So this is also improved. But historically, one challenge in aws, for example, used to be that each instance had a very limited bandwidth that it was allowed. And so like bandwidth in terms of talking to the storage and getting data back. If you're doing like a very big select, for example, then postgres has to read a lot from the file, right? That essentially bottlenecks just in the instances my O throughput limit, not in the storage itself.

So the rage lately is containerization Kubernetes. Do you have to think about anything with Postgres if you're using that type of environment?

You could. So I mean, there is, I think, what's great about Postgres, right? There is more than one way to do things. So like, people are running Postgres in Kubernetes, for example, in production. There's a lot of operators nowadays for Postgres and kubernetes. I have some colleagues over, ex colleagues over at Crunchy Data who I used to work with in a different company. And Crunchy, for example, builds the crunchy postgres operator, which I think is one of the, you know, good choice essentially, if you want to use Postgres and kubernetes. And really what that operator solves for you is kind of all the pieces where, you know, Postgres interacts with the world outside it, right? So be it. You know, how do I do backups, right? And where do I store these backups? Or how is configuration managed? Or, you know, how do I handle a failover, right? So like, how do I make sure if I'm switching from one pod to another, how is that handled? That's where these operators really help you. Nice.

So I know development teams are really busy, so I assume does PJ Analytes, does it, does it integrate with cicd and does it bubble up insights to say, hey, this is happening or does someone have to actually go out every day and look it up? Does it. How does it work? I guess, what's the flow?

Yeah, it's a good question. So I think, you know, I wish the answer today was it does it for you, but the truth is it doesn't yet. So today it's more of the latter, which is, you know, people actually do have to go will either go into the PGA's kind of tool or they can get slack notifications, for example. But it's really about your production database. Today one of the things we are currently exploring, especially around indexing is imagine you opened a pull request and PGNLY's index advisor would come and would say, hey, you're missing this index. Right. But at pull request time, not at production time. So before it hits production. And technically that's possible. So the way we've built our system, we can support that and we've actually like. What we're able to do is essentially use your pull requests schema and your pull requests queries, but then run it against production size tables to do the estimations we use for the index recommendations. So we have built a system with that in mind, but we still need to implement the actual plumbing to deliver that experience. But it's one of the things that's kind of on our roadmap to really bring that into the developer experience. Because I think ultimately today what happens, people do best effort and then they push it to production and then no.

One knows what did it, what happened or who did it.

Right, exactly. Then suddenly you gets flagged and then things go bad, worst case. Right. And then you realize, oh, it's the missing index and then you take action. So we are kind of interested in shifting that earlier and shifting left, so to say.

Yeah, I think that'd be a killer feature, shifting left. When they fully check in, have it do that quick scan. For sure.

I fully agree. Yeah.

So I guess we have to talk about it might be stupid AI machine learning everywhere. Why do I have to care about performance? Can't someone just create some sort of algorithm that will just figure it all out for me? What's the way do you see AI machine learning if at all fitting into. As we go further into the new years with postgres for sure.

Yeah. And we've, I mean, you know, I try to keep an open mind. I'll probably say I'm more of an AI skeptic than an AI enthusiast, but that's more, you know, I think AI's impact on our Society rather than, you know, specifically in databases. I think in the context of databases like indexing, for example. So there's a lot of research around this topic of index selection. Right. So how do you find the right set of indexes for your queries? And so there have been a lot of papers in the research community around what is the best algorithm for that. And there, for example, there have been machine learning based algorithms that try to find the best index. But what's interesting is that they've only recently actually gotten competitive in a sense that there are better ways to do it that are not machine learning based, that actually perform better than machine learning. I think where machine learning is interesting is it helps you provide a human language Interface. Right. So LLMs are interesting, I think, because they can make things more approachable. But I'm very skeptical when it comes to actually giving a machine learning system control over things. So if it's about auto tune the setting, for example, I think what really gets lost is the context of why that choice was made and why that is the right setting. And so I think maybe eventually we'll see a system that can actually explain its actions well, but I think today that's not the case. Right. So today there are projects out there to try to do this and ultimately they are really, you know, just a magic box that does something. And you know, to me at least when I want to tune my database, I'd rather have something deterministic essentially that, you know, it has, it looks at a bunch of data. Right. But the algorithms it uses are not neural networks, not, you know, machine learning type systems.

So I guess we'll wrap it up. But we talked a bit about PGA Analyze, but is there anything I missed? Like any features you think people should know about that we may not have covered that you think would help them? Sure.

I mean one thing that we're excited about right now is coming back to vacuum, right. So this maintenance process in postgres. So we are about to launch what's called Vacuum Advisor. And so that's similar to our Index Advisor that does index recommendations. Vacuum Advisor is a deterministic system that essentially looks at your database statistics and then makes recommendations which settings to tune on, which tables also so make it being very specific. And so that's something that we've worked on, I would say for roughly six months now. And our team is really excited about this because it will help you like if you are in these production situations where your postgres database just either vacuums all the time or doesn't Vacuum enough or you see table bloat. This new system essentially will let you know what settings to change, but we'll also be able to explain to you why and how it came to that conclusion. So I'm really excited about that new feature. We'll be launching it very soon and then, you know, that I think will be a big splash.

Very cool. You know, you mentioned that I always like to figure to give people an idea of what solution's the right for them if they're using Postgres. I think they definitely should check this out. Is there any other use cases or users that if they're listening, this would be the perfect solution for them?

You mean outside of Postgres? Yeah, I mean, not really. Right. As mentioned, we are really, you know, focused on Postgres. I think the. What I would say right is like, if you work with a database, I think it's always very good to understand how it works. Right. Even if you work with a totally different system. I think what I found really useful when we were building PG Analyze is actually diving into Postgres source code and understanding here is how Postgres makes this decision. So I think what's interesting about databases is that they have a lot of longevity. So it's not like it's going to change its behavior tomorrow. For most part, it's very stable behavior. And so if you find yourself, for example, building internal tooling, then I would say really try to, you know, identify what are the things that are permanent, what are the things that are changing all the time. And for example, the query planner in Postgres is such a thing where it made a lot of sense for us to really dive into how that query planner works and how, you know, like how it makes its decisions, because that's still going to be the same, similar in five years from now, for example.

Okay, Lucas, before we go, is there one piece of actual advice you can give to someone to help them with their DevOps database Postgres performance efforts? And what's the best way to find contact or learn more about pganlyze? For sure.

Yeah. So in terms of advice, I would say if you find yourself, you know, switching to bigger and bigger instances, then it's probably time to split up your database into multiple kind of systems. So don't wait until last minute. This is not something that PGA Analyst helps you with necessarily. It will help you identify, you know, how bad your system is behaving, essentially. But if you are, you know, truly at, you know, a scale, where you need to scale out more than one instance, do that early. There are ways nowadays. For example, the Citus extension that I used to work with is great for this. There's ways to do application side charting, there's ways to do replicas, logical replication. There's a lot of tooling, but it does require time, right? So I think from a DevOps perspective, identify these problem databases early and start your tooling work early as well so that you can kind of get ahead of that curve where you can no longer go bigger. Essentially, I think from PGL's perspective, if you're interested in what we're doing, I host a weekly show called 5 Minutes of Postgres on YouTube where I just share my personal, you know what I find interesting about Postgres each week. So if you want to dive more into postgres, that's the way to do it. We have a PG analytics newsletter. So if you want to keep up with PG analytics the product, just go to our website and then there's newsletter subscription there.

And for links of everything of value we covered in this DevOps toolchain show, head on over to test guild.com P121 and while you're there, make sure to click on the SmartBear link and learn all about SmartBear's awesome solutions. And to give you the visibility you need to deliver great software, that's smartbear.com so that's it for this episode of the DevOps Toolchain Show. I'm Joe. My mission is to help you succeed creating end to end full stack DevOps toolchain awesomeness. As always, test everything and keep the good. Cheers. Hey, thanks again for listening. If you're not already part of our awesome community of 27,000 of the small artists, testers, DevOps and automation professionals in the world, we'd love to have you join the fam@testguild.com and if you're in the DevOps automation software testing space or you're a test tool provider and want to offer real world value that can improve the skills or solve a problem for the Guild community, I'd love to hear from you. Head on over to test guild.info and let's make it happen.