PostgreSQL: Tuning parameters or Tuning Queries? with Henrietta Dombrovskaya

Speaker 1: Thank you so much for attending and you know what that is oh my gosh it's the last session it's 5 p. m. when I saw it like nobody will come. It's not even Janga and whatever. But So thank you. Thank you everybody who's who stuck here. All right. So uh how many of you know what is Postgres? Okay. How many of you ever worked with Postgres directly not through your beloved ORM? Alright, good, good. Because you know what honestly I know that most times uh developers just use Postgres, but they do not really talk to Postgres. All right, so let's see where we will get this way. Okay. So that's me.

Speaker 1: I'm Heiji Dombrowska. I am from Chicago. I'm a database architect in DRW Holdings, right across the river, but you cannot fly, so you need to use the bridge. Um I'm uh the founder and president of uh Prairie PostgreS Non -for-Profit organization uh dedicated to postgreest education in Midwest states in the United States. Also, I'm um organizer of PG Data conference and uh I have literature about PG Data. Please consider coming, contributing, etc. And uh I run uh Chicago Postgres User Group. I already mentioned a couple of times, it's conveniently this Wednesday And conveniently again right across the river. But you will need to live from here a little bit earlier.

Speaker 1: But I will have pizza including Chicago style dip dish, so Consider, okay. I also am um I have lots lots of inks on my plate. Uh I'm a communication chair of uh SCM Chicago chapter. And I was recently um elected uh the uh on the board of Linux Professional Institute. Oh my god, what I signed for, I did not even know, but now I have to do this. All right, so uh and or also I wrote a book about Postgres. So Monica actually reminded I forgot to include the slides. Oh, and Hetty also wrote the book. So available on Amazon. Okay You can scan quite good. So now to the actual presentation. So uh I actually never uh did talks about parameters. I mean you guys most of you do not know me, but people who know me knows.

Speaker 1: I never do talks about in general tuning parameters. And I only had uh that's the second run of this talk in all my career. So why I am doing it So I will tell you why I'm doing it. So um actually um what does it mean uh that uh to tune your database? I thought I know what it means. I was tuning queries all the time before Postgres, but until I joined EDB, it was a brief moment in my career. I did not know what people mean uh by uh like tuning uh parameters. Okay. Uh so what I found in in ADB. I found that uh tuning database meaning tuning parameters and that was like

Speaker 1: oh my gosh, I do not know why people even want to tune parameters. So horror story. I I never did it before, before I joined the DB. I never tuned parameters at all. And you know why I never did that? And that's what this talk about. Because actually at large uh it does not really matter how whether parameters are tuned or not. Uh often I was not in the position of tuning because I was uh not a DBA, I was a database developer, I was a consultant and coming at like What they're talking about? Why you need to tune something? That's your job. Write this like you know, export, uh, write this interface and get out of here. So um I could not, and also I learned to live without tuning parameters

Speaker 1: Uh so uh still people believe in magic of parameters, and that's what I found when I worked with EDB. So I'm wondering why people like honestly believe that there is some magic like set of perfect Postgres parameters and the switzers from EDB or other consulting companies they know this like world wisdom and they can come and like Tune tune tune tune the database, everything will run with the speed of light. So why people believe in this? Because that's not true. So you know why people believe? Because it's easier Because uh yeah, working on each uh model of your program, you know, working on each query is boring, it takes time and like

Speaker 1: What if you would have a magic? And that's why people believe in magic, because people won't believe in magic because it turns everything automagically. Okay? So my goal today is to show you that. tuning parameters almost doesn't matter. And uh why it's so difficult to show. I actually uh people who people who work with Postgres they know that it doesn't matter. But it's very difficult to demonstrate that it does not matter. You know why it's difficult? Uh because uh we are tuning holistic system through output versus individual queries. So you cannot really run a demo to show that you improve whole system output.

Speaker 1: It's difficult to model real-life workload, it's difficult to model real-life concurrency. So it's difficult to run an example where you can actually say A B matters does not matter And uh I've been struggling to do this also for a while. Uh but again, that is uh the reality. Uh tuning parameters and ask any uh honest uh DBA tuning parameters, uh even if you do the best, even if parameters will not tune at all until you come first time, you can uh make it like Ten, twenty, fifty times better if you maximum, okay? At the same time, tuning queries can make your app application run like several tens times faster. Intuning your application structure that is

Speaker 1: DTRM might uh make it run like hundreds time faster but I was not allowed to talk about it. I actually submitted two talks, one was about this and one about D CRM and guess which one was accepted? Sorry. So uh we were talking about uh parameters versus queries All right. So let's uh look at one query example. So as I said, unfortunately, I cannot model like a system through output. So let's look at this query example. This uh query runs on the Postgres Air database, which we created um for uh demos. of this book for this book postgre optimization. It's available on uh my GitHub. It's the largest publicly available PostgreS database where you can really learn and uh like

Speaker 1: tune uh um like learn the bill indexes etc. So postgres air. Postgres air is about airlines, about flights, about passengers and all these things So let's look at this query. So this query um selects for each flight which um departed from JFK Airport and landed in Ohara International. Uh between August eighth and August twelfth, twenty twenty-three, for each of these flights, it tells you uh the uh uh actual uh departure time and um number of passengers on this flight. Okay, normal query, just two joints like not a big deal. So now we are executing this query on clean database.

Speaker 1: We just installed it. You went to GitHub, like installed Postgres, downloaded database, loaded. Okay, go Uh so I I will make it bigger, no worries. Right. Uh so execution time for this query is uh two point four seconds. And you think uh it's like okay two point four seconds? Uh it's not Right, it's not okay. It's not okay. Right, right. And uh and again, uh I will uh make it bigger. Yeah, that's it, no, that's Does it make an aye? Okay, yeah, yeah, yeah. That's that's uh I I'm showing you this part of the execution plan, which is bad. So you can see here there are lots of reads, and it's like buffer swapping, etc. So um Why uh what what we can improve here uh for this query uh what parameters we can improve to make it run?

Speaker 1: First of all, okay, there was a large table and maybe So you notice there is like single um uh single worker. So actually let me go back through. Yeah. Um number of uh okay, sorry. Yeah, uh number of parallel oh sorry, I what I did, I did wrong. Let me Try to go back. Can I go back? Yes. Yes, I can go back. So number of parallel workers uh in the beginning was zero. Okay. Yay. What sorry, sorry, sorry, sorry Where I was, where I was. Okay, here I was. Okay. So uh number of parallel workers here was zero. Okay. Apologies. Uh moving forward. Okay, so number of parallel workers was zero, shared buffers uh um uh default

Speaker 1: and uh work memory default. Okay, now let's improve it, let's uh make uh max parallel workers pergether two. Okay, and uh we are doing this and we are making uh okay we are making execution time 2. 1 seconds better still not good, right? Okay, because it's still the same problem Okay, now let's uh move it uh okay move it move it move it ay I am turning it in the wrong way sorry I am turning it in the opposite direction Okay, now we are we're talking. Okay. So now we are increasing work memory. We are increasing work memory to for uh half a gigabyte, then we increase work memory for one gigabyte.

Speaker 1: So we are improving 1. 8, still it's not good enough. So last resort, okay, let's uh try to increase shared buffers. We're increasing shared buffers Um it requires restart. So one second. By the way, we if we increase it more, it will be one point one. Oh my gosh Okay. So what we're doing? So by increasing shad buffers, that's it. So we increased it twice, right? Twice and still like okay So let's try something different. Okay? Let's uh look closer at the execution plan. So we have the scan of table uh for the departure days between August 8th and August uh twelfth. So here is this scan. So what we're going to do to

Speaker 1: we scan? Who knows what we're going to do to we scan Index, create an index, right? Creating an index, building an index, and uh the execution time Immediately 0. 7 seconds, hurry, but it's still not good enough. So what else is bad with this express Execution plan. Okay, that's what the bet with this execution plan. We still have another gigantic table, which we still are doing full scan. So we need to build what Another index, correct. We need to build another index. Okay, so this one built another index. Now I know it's a gigantic plan. I will highlight what changed. Okay, here is what changed. Now we have this index and uh execution time

Speaker 1: sixty milliseconds. Okay? So and uh next Do you know what will happen if we will ditch all our parameters improvements and get it back to what it was? Who knows what will happen? Nothing, right? Absolutely nothing. So forget about all these parameter tunings. We build two right indices. Booms. Execution times is like what 20 uh 20 times faster? Okay, so good enough, right? So now uh yeah Uh in texture speed everything well. So now uh why we need parameters? Why why why why we need parameters at all? We need parameters because that's the only way for us to tell Postgres

Speaker 1: how the hardware on which it's running looks like. Without parameters, Postgres doesn't know how much memory it has. how many cores it has, what it can allocate, how many buffers it can read. So without uh properly communicating Postgres will use only small fraction of memories, or will never run parallel query, or will avoid using indices if it would not change random page cost. So the only role of parameters is clearly communicate, hey, Postgres, that's resources you have. Use them. And then it will use it. To the point. Other than that, it's like no magic. Okay? So application change. I know I was not allowed to talk about this, but just telling you

Speaker 1: There are lots of things where you can change the way how you query, not adding indices, like for example, using equal instead of approximate in 95% of time. Why you did this one? I don't know. Actually We can work on this. Avoid uh extra column transformations like uh truncate created at current date. It's column transformation. You can easily do it mo more or less. uh like uh not committing until the end of the badge committing after this our beloved or ams love either committing after each uh insert uh Begin select rollback all all this good stuff. So uh this might drastically improve your performance.

Speaker 1: Now it's almost me so where to find me? Phones, phones, phones, phones up. I'm everywhere by the way. I'm everywhere. Uh Hetty Postgres. You can Google, you will find more than I want you to know about me. So um LinkedIn, GitHub, uh, and uh we have Meetup. So please uh sign up if you are interested Uh and uh uh yeah if you uh sign up I need your first last name by high security building. Uh and uh that's it. And uh thank you so much. And uh yep, any questions?

Speaker 2: So you said the main reason that parameters exist is because Postgres doesn't know how many chords you have, how much memory you have, etc. etc. Yeah. And my reaction is, well, why not? Can't it probe that stuff at startup?

Speaker 1: Okay. Uh so is there a ways to create from Postgres where you are running and find result. But the query planner at the moment when it runs the query planner relies on parameters Query planner reads. Sorry, sorry. Okay. Que a query planner reads uh parameters reads from a PostgreSQL. and it relies on what you communicate to it. And um you know what there are many ways how we can actually artificially tell Postgres things which are not necessarily true but we want Postgres to think this way. It's like all uh like all separate kind of all separate theory. Then you can uh do different allocations based on your workload, for example.

Speaker 1: It's actually rarely the case 'Cause uh almost everybody will tell you or depending on whether you are OLAP or OLTP, you need to set up parameters differently. So, people, you all work in industry. What are our databases? Are they all CPO lab? None. They are hybrid. 95% of our databases are hybrid. So that's also kind of Um like questionable um thing, but again, um like the theory behind this that you can uh tune based uh on your specific workload. And there are situations when you have like one million uh one million uh users I had customer like this or you have like um ten uh

Speaker 1: marketing analytics Who bring your database down? These ten people, you know, I love marketing analytics. You know how they build their models. They like read the whole database into their Python because they cannot do incremental models uh build. So yeah.

Speaker 3: Thanks so much for being here. In the spirit of your other talk about application performance tuning, what re uh what resources would you recommend to look at? uh

Speaker 1: for pl uh for application performance tuning?

Speaker 3: Yes.

Speaker 1: Um that is a good question. Um I uh actually uh You know again it's probably better to recommend it at Django. I do not know book about Janga performance improvement, but Andy Atkinson has amazing book about Postgres for Rails where he but it's like the same basically like dealing with Postgres when you have uh uh object-oriented uh system which uses ORM. That's actually one of the best books about this. So maybe maybe you know somebody will do this for Jenga or for Python or for Bova. Right?

Speaker 4: Six minutes left for questions?

Speaker 1: Okay. Okay, uh you know where to find me, right? Okay. So that's all for this.