Postgres FM - Default configuration

Episode Date: February 3, 2023

Nikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.Here are links to a few things we mentioned: shared_buffersAndres Freund tweets about shared_buffers PGTune Leopard Cybertec Configuratorpg_stat_statementsJIT configurationpostgresqlco.nfannotated.confOtterTunework_memrandom_page_costmax_connectionsWhat to logmax_wal_sizeWAL and checkpoint tuning episode effective_cache_sizeIntro to Performance Tuning and Optimization (EDB guide)max_parallel_workers_per_gather ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hello and welcome to PostgresFM, a weekly show about all things PostgresQL. I am Michael, founder of PGMustard, and this is my co-host Nikolai, founder of PostgresAI. Hey Nikolai, what are we talking about today? Hi Michael, let's talk about configuration basics. It's hard to cover them deeply enough for a 30 minutes episode, but let's try to talk about some principles and maybe some pieces of advice for new people or for non-database experts or for developers and so on right yeah exactly um this has been something i've been hoping to cover for a while and we got a request for it last week so i'm excited to there's a bunch of things that i seem to think are sensible to change pretty much

everywhere some cloud providers do some of this for people so maybe it's not so much of an issue these days but there are so many things that i think even just a basic overview could help people with i mean i'm keen to make sure we get give people a few resources they can go to to check in future but also some probably sensible things to change even from the outset. Right. And the original request was about where to start, how to start configuring fresh Postgres. But fresh Postgres these days means very different things. It's not only, of course, first of all, clouds, different cloud providers, they have different defaults.

They're just defaults they adjust defaults but not only that if you for example check the the one of the most famous settings of postgres controlling the size of the buffer pool it's called shared buffers and if you check the default of it it's quite uncertain even documentation, it says 128 megabytes, I think, yes, megabytes. But it's not real default. Real default is eight megabytes, maybe bytes. But packages like for Ubuntu or for Red Hat, they're adjusted. So there are several layers of defaults. And of course, if you get Postgres from some cloud provider, for you, the uppermost default layer is the most important because this is what you will get. And they usually, of course, choose 25% of memory for shared buffers as like kind of golden rule. Maybe

not correct term here, but usually so. Actually, before we move on from that, I think ShareBuffers is a great place to start. I think it highlights a few things or a few themes. And in those themes, we've got things like, by default, a lot of these settings are quite conservative and they were set quite a long time ago. The team behind Postgres seemed to be keen to keep them on the lower side and let people increase them. But some of them have got so low and so old that it's kind of silly at this point. And this feels like a good example. But the 25% advice, I think that's in the docs to probably bump it up to 25%. Yeah, it's not perfect usually.

Of course, the perfect value depends on workload and database and like, it's hard to determine the perfect but 25% is good enough. It's like 8020 rule. It feels like a really sensible place to start except where I read a really good thread by Andres foreign. I'll share it in the notes. I must warn you, let's not dive too much into details because we can discuss shared buffers for 30 minutes easily and i didn't i can tell you how i lost several thousand dollars for experimenting to get a flat curve and understand that experiment was bad and that i paid it out of my pocket and so on so but i think it's i think this is kind of

the point right like i kind of wanted to say that experts will, if you read up on it, if you go down the, like a rabbit hole of advice, you can find arguments to increase it. You can find arguments to decrease it. And that will be true of so many of these settings. But I think shared buffers is like one of the most controversial,

but I do think it is good advice to start. Sorry for interrupting, but if you're expert of course you should choose 80 but you should do it very very carefully with a lot of additional steps but i agree with you it's good good start and let me provide a couple of thoughts regarding shared buffers first of all default terribly low, even that one which is configured by packages, 1288 and so on. Postgres isn't shipped with any analysis of resources,

so it's always some hard-coded values. And when you install it, nobody except you will accept cloud options, of course. So if you install Postgres yourself on some machine during installation it doesn't analyze how many gigabytes of memory you have so it will just put some default like this 128 megabytes and i call it like defaults most defaults in postgres related to performance are very very conservative it. It's like very, very outdated, very, very conservative.

You need to tune a lot to be in a good shape. But there are a couple of configurator online tools. They are quite simple. They are just heuristic-based tools. One is on the APART UA, some Ukrainian engineer.

And it's very popular. It's even mentioned in some books i saw it and another from cybertech they are cybertech maybe slightly more advanced but basically they follow this 80 20 rule if you just use them you're good enough already and of course shared buffers is covered there definitely for all tp and web apps following 25%, I'm quite sure. But defaults, if you don't do it, like I recently tweeted about it, I call it Podgres. Podgres for teapots.

Because you can have a smart teapot and install Podgres there. And even there, probably you would like to configure it because even if you take Raspberry Pi 4, current one, modern, you will get, for example, eight gigabytes of memory and still you would like to have two gigabytes of shared buffers. Only the very first version

had like half a gigabyte where you could use this default. And second thought, there's a problem on the other hand. I think in a few years, the bigger Postgres setups will already experience the problem of reaching maximum. Maximum for shared buffers, we discussed yesterday on Twitter as well, maximum for shared buffers is 8 terabytes. But there are already instances on AWS which have 24 terabytes. So

in several years, we will have instances available on cloud, which where you will not be able to apply rule 25%. So it's interesting for almost neither side fascinating i'm thinking let's go back to some other things that people probably like definitely want to do i i still see quite a few people that don't have for example pgstat statements running and it feels like to me really sensible like extensions that you need they're defined in the config file i don't know if you count those as config things, but for me, that gives so much extra benefit for so little overhead that I'm a little bit

surprised it isn't on by default. Yeah, but it's not about configuration. Well, you can consider it roughly as configuration as well, and there is consensus among Postgres experts that position statements should be enabled by default. I wonder

maybe it should be already in core, right? And maybe installed by default unless you say, no, I don't want it. I never saw any heavily loaded system which would be considered like, no, we don't want Pagista statements. Usually

we always wanted this extension. And this extension brings several interesting knobs as well. Well, we forgot to say that there are so many knobs, more than 350. And do you know that you can add your own knobs? Like user, custom user? I didn't realize that, no. The knob system in Postgres is called GUC. It's like Grand Unified Configuration, if I'm not mistaken.

Like Grand Unified. And it's actually quite powerful in terms of you can apply it at various levels. You can apply it system-wide using configuration file or alter system with reloading of configuration. Or you can apply it at the base level, at user level, so in session, for example, in transaction, if you use set local, so there are many ways. And you can use your own variables if you just use namespaces. So something dot something, blah, blah, blah, dot my variable equals something. And sometimes I used it. But here we talk about configuration for what? For performance or like what are our goals, first of all?

Probably, yeah, probably should have got that in up front. I was thinking if I'm set, yeah, I think the person who's asking about setting up a new server was onto something. If somebody has been given this task and they want, you know, to make sure they've covered at least the basics, what can we point them at? Like, can we put them in a few directions i think that some of these tools

don't freak like the tools you mentioned i don't think they point out some newer things so for example i see quite a lot of people with maybe mixed workloads running into quite a lot of overhead with just in time compilation for example that got turned on by default in postgres 12 which is only a few years ago so it's some of these things haven't kind of had time to to catch that kind of thing so i wanted to mention that possibly if you're running a ltp workload transactional you you probably could get away with just turning JIT off and then maybe coming back and tuning it later. Or if you want to tune it, maybe increasing the cost parameter.

But turning it off for me feels like a really sensible first step if you're not planning to run huge analytical stuff. Yeah, there are many such pieces of advice. And of course, in LTP, I also turn off just-in-time compilation. And unfortunately, I also turn off just-in-time compilation. And unfortunately, I'm checking these tools.

I recommend that PGToon and Leopard in the UA and also CyberTech. At least in the first one, I don't see just-in-time. And I think it's just lagging, right? And I remember there's another, like there are also good, very good resources. One is PostgreSQL code.nf, which is kind of your new interface to configuration options for Postgres. But the advice in that is based on annotated.conf. And that's from Postgres 10. But that's Postgres 10.

Unfortunately, it was maintained by George Berkus, who was in the core team but left to work on with Kubernetes project. And he like shifted his areas of interests. And unfortunately, this project is left unmaintained for a while. I'm not sure I saw some messages probably to be like resurrected. I hope it will because it was very good. Yeah, it's very good. Maybe I'm mistaken, actually. But it was so good that provided a lot of insights, like additional insights from practice, and like, compiling everything actually post this code. And if it gets information from from there as well. But for new knobs like just-in-time, no. So we will not be able to mention all performance-related tuning actions

during half an hour, but I would like to remind about principles. First of all, these tools, which are heuristic-based, they are good enough in 80% of cases. And if you actually consider the idea that sometimes bad, poorly written SQL query affects us much more than poorly tuned shared buffers, those 80% probably become 95%. So I mean, you can fine tune more and more and more and more. And there are many things to tune, of course.

But maybe you should start from this 80-90% tool, then focus on query optimization, and then iteratively approach better shape. That being said, of course, new approaches like AI-based, Autotune, here worth mentioning, right? Autotune. I think they are good only if you have a lot of nodes, like a lot of nodes. For example, I remember, I think somebody from China,

maybe Libaba, maybe no. Somebody reported they had 10,000 instances and they saved like 5% of costs applying AI to tune Postgres configuration. And believe me, I spent some couple of years in this area. My company is called Postgres AI and I definitely considered working in this area until I realized that optimization of queries is more usually usually not in all cases but usually more important topic of course if you're a cloud provider you cannot optimize queries directly but this is my criticism about cloud providers you could provide more tools like visualization

tools more helper tools syntax advisors and so and so on to your customers. I think there is a lot of things to do here for cloud providers. I see some cloud providers not even doing the absolute basics, though. Some of them are doing sensible things. And maybe they're doing the absolute first level, like shared buffers. But a lot of them don't change work mem a lot of them a lot of them don't even change random page cost or a few that i've seen work is tricky the problem with work mem is that you cannot calculate how much you need usually in really loaded systems we are over provisioned so So if you take connection, max connections,

and if you take, for example, workman, you cannot just multiply and say, Okay, this is how much memory will be needed by my backends. The problem is that each back end can can consume multiple times up to workman. But in reality, they don't reach it. They consume multiple times, but only portion of work. And so if you follow the rules, if you if you want to be very conservative, avoid out of memory, on killer and so you will be very using your resources not well enough. So everyone is end up over-provisioning, consciously or unconsciously. And of course, if workload changes,

there is risk to consume a lot of memory. So workmem is tricky. You cannot recommend everyone, oh, raise your workmem, you will be good. Because there is also max connections. And this topic directly related to the lack of connection puller inside postgres it's a big problem right so we need to think about connection puller and and then how many max connections we we need to use i think that's a great max connections is a great point by the way workman i do think there is sensible

advice i think four megabytes even if you're being really cautious four megabytes i think is too low i i see you absolutely agree yeah yeah but but like if a lot of the cloud providers aren't even bumping it up to something also conservative like 16 megabytes or so you know so it's four times more you'd be surprised how much of a difference that can make in even an oltp system sometimes so yeah i that's the kind of thing i'm thinking might be helpful to people out there like what what might be a more sensible default for some of these providers that's one example i've still i've seen some modern like people that have only launched clouds in the last few years still

have random page cost as four which is a setting that was designed for kind of spinning dip like it's not it's not ssd like if you if you were designing that now i think even even a setting of like 2 or 1.5 would be quite conservative so like these are the kind of settings i'm seeing in the wild that i don't i don't understand why they're still the defaults i also don't understand they say okay we still have a lot of magnetic disks, but new systems don't have the most of new systems where you need the defaults. Old systems are already configured.

Forget about them. Yeah, two things. One, old systems are already configured. That's a really good point. But two, if only a small percentage have these magnetic disks, why can't they be the ones that have to change the setting instead of everybody else on the ssds that's what i don't understand there's a big lag in in adoption and changes of defaults and each time defaults are changed i i like i have a bottle of champagne to open. Yeah. For example, recently, log checkpoint

was enabled like logging, it's insanely like, let's produce less of logging, but you cannot see issues and issues come and you don't have logs. And of course, if you want to cover more than these tools covering, by the way, I'm checking back to work ma'am I see that Leopard PGTune Yeah, it recommends for web applications and OTP recommends only 14 megabytes. It's quite low but cybertech they do 64 megabytes which is already good much better. But I need to

check Max connections is hundred only of course if you increase Max connections, you will see workman dropping i guess yeah but actually max can i just make sure we cover max connections quickly because i think this is one where some cloud providers are doing the opposite that i think they're going too far like i i think on rds i've seen really really high max connections by default and then i think heroku as well well in ltp situation it does make sense to have more than number of cores times like two or three connections because i accept cases when they have like weird locking pattern or something it's usually if you reach

already a number of cores in terms of max connections, in terms of active backends, it's already probably slow, you have slowed down already, performance degradation. But again, we can discuss a lot of things here. Let's talk about principles. So we have these tools,

and I think CyberTech is covering more things. It doesn't cover log underscore settings, which I recommend to I usually recommend to use quiet like everything on almost everything. Of course, I don't recommend logging all queries, but I definitely recommend logging all auto vacuum behavior, checkpoint or behavior, everything like that. DDL? DDL, yeah, it's for audit. I think that surprises, but it surprises some beginners. I see it come up quite often in Slack and things

that they don't realize that you can't check a table to see, or you can't check when an index was created, for example. People are surprised by that by default. So I think enabling those kinds of settings can really help ux wise yeah i agree i agree and also of course like memory related connections related settings are very important for planner random patch calls should be close to one or maybe one otherwise you will be getting a lot of sex counts and unexpectedly in some complex queries also a checkpoint or behavior one gigabyte is not enough on modern

hardware. Usually, one gigabyte means that on like, is if I'm not mistaken, divided by three or two, the real size of wall bit, the real distance between two checkpoints, it's like how it will be half of megabytes with these defaults or even 300 megabytes. It's too small. So Checkpointer will be your most writing process if you check IOTOP. If you can check it, of course. I think we covered

that in more detail. There was one episode. But there are many recommendations. There are many of these annotated, still relevant because many things are quite old. We can discuss a lot of things but what i wanted also to mention is developer experience because developers usually spend more time on non-production right it makes sense and non-production usually has a smaller size of RAM, fewer vCPUs, fewer cores, maybe less powerful disks, and so on.

And here, over years, I developed some methodology. And we actually already touched it. Buffers first are your first analysis of performance. Maybe single session, not jumping straight to straight to full fledged benchmarks, right? You can analyze performance using one connection, query by query, or just one query and so on. And you can tune it. If you focus on IO, the most important settings to have reliable testing, database testing is the planner settings plus workman because workman is like I forgot to mention if you check PG settings you will see in modern postgres you will see more than 300 350

360 knobs and they are organized in in categories and there is a category related to planner behavior planner settings and you should take all of those settings from production to your non-production plus workman because workman is outside of this category somehow but workman also affects the planner behavior and this is how you can achieve similar behavior of the planner and non-production even if you have fewer resources. People don't understand sometimes the fact that Postgres doesn't know how many gigabytes you have, how many vCPUs you have, what kind of disks you have. It doesn't know about it anything at all, like zero. And for example, the way we tell Postgres how much RAM we have, even not shared buffers,

because shared buffers is shared buffers is thing which is allocated if production has a terabyte of memory and non-production has only 64 gigabytes okay maybe too big scale like okay production has 128 gigabytes non-production has 16 gigabytes

we still can full Postgres adjusting effective cache size which which is not located. And this is how we tell the planner to behave similar to production. Not similar, exactly like production. The only is like planner settings on WorkMem. And also, like we have two components, this and second, what we have in PG statistic. Plus, rel pages also matter.

So, you're talking about principles, right? The first principle is if you want to do good testing in non-production, the settings need to be as similar as possible to production. Player settings and workmem, not others. Others, I don't care. I personally think it is good practice like if you want to test real behavior why not set like i think the config should be almost identical production sometimes you cannot because you don't have some so much memory and so on sure

but that's what i meant like so the principle here is they should be the same except there's cost implications so because there's cost, what are the trade-offs? Right, right. And also you need to be able to adjust your methodology not to pay too much attention to timing when you tune queries. It's okay to have 2x, 3x, 4x slower execution time, longer index creation time, and so on. And then it's just a matter of, basically,

if you have a similar size database, you will have similar behavior of Postgres. If number of rows matches, that's it. Right? Yep. Again, there is detail like rel pages, it's I like understood it not far, not long ago. Because rel pages, it's like knowledge about bloat and if you have a copy logical copy of database you have different real pages and the planner looks at it definitely in pg class real pages for each table and for each index actually and it it it takes into account how many real pages, so how much various operations would need to be done. And here we come

with random page cost, second page cost and so on. Yeah, probably a bit far. We probably don't need to be worrying about that in terms of beginner config. Yeah, ideal situation is physically provisioned database, so data copied at physical level or from backups with the

same physical distribution, same real pages, same PG statistics, and also the planner settings plus workmem. And it doesn't matter how much CPU and how much RAM, what kind of disks you have, then you will have exactly the same plans, of course, with adjusted timing because resources are smaller and caches are colder and so on. Yep. And this is how you can develop much better much better in my opinion nice any other kind of general uh in fact actually i had one more kind of for the i guess we're coming towards the end of this so one more place to go to for a little bit more information i really liked there was a quite a long EDB guide that they published on a bunch of these things,

starting quite beginner-friendly and then moving into some more advanced things. I thought that was a really good primer and it doesn't take too long to read. So I'll link that one up as well. Yeah, also there were some talks, I remember, quite long and tutorial style and, like, tutorial style, maybe four hours. It's a huge topic, and I'm constantly learning myself from time to time. But we discussed some principles. There is also

maybe the most important principle I usually try to follow in my practice is experiment-based approach. Any change you want to, like, any question you have, go and check. In clouds, it's easy, right question you have, go and check. In clouds, it's easy, right? Just get an instance and check. Check behavior, check, run benchmarks if needed, if it's system level configuration, which is probably not interesting for developers,

like auto vacuum behavior or replication behavior. Benchmarks are needed. The planner behavior, no benchmarks, just just single session experiment. But experimenting is the best approach to learn and to ensure that we are good to perform the change. That's why, of course, having some environments which are like to help you having what if workflow, like what if this setting is increased? What if it's decreased? But sometimes you need extra deep knowledge to conduct an experiment, of course. I've thought of one other, this is like slightly more advanced on the config side,

but probably something a lot of people would change up front so there's nowadays we have parallelism and there's some and there's some parallelism turned on by default so i think you get two parallel workers which means you get three threads by default at most so i i think that's probably something that if you if you're doing oltp stuff you might want to reduce that especially if you're on a really tiny instance and if you're doing analytical stuff you almost certainly want to increase it so that feels like a big one but also one that's relatively tricky to test on a different setup like that one does rely on your resources right like how how do you advise people do that with a different setup to production well experiments again like you you just conduct experiments and check. With a similar number of cores?

Yeah, yeah. Well, in some cases, you need to have this like very similar or like exactly same instance. And also worth checking if sometimes like if you have full-fledged benchmarking, if you need to like for most reliable change, you sometimes need a single session experience, sometimes full fledged benchmark and in the case of a full full fledged benchmarks in cloud, I recommend also developing some small script for micro benchmarks to start with checking CPU, RAM, disk network that it matches expectations, because sometimes you've requested some instance but you've got different CPU family even sometimes. Even if instance family is the same, CPU is different.

Or you have some problematic instance. So it's worth checking before you continue with experimenting itself. So set of micro benchmarks with sysbench and FIO for example as bare minimum is good Just to check CPU RAM and disk By the way, I'm looking at the cybertech Tool and I kind of like it For they propose max wall size 10 gigabyte. It's already good Depends on database, of of course and by the way here i don't

agree but okay well compression enabled which is i think it should be enabled by default there are there are some reports that in some cases it slowed down performance decreased performance but in general in most cases it's helpful to have it on. But they have just-in-time on. They suggest to have it. I don't know. It's like... I don't agree with this, but I agree with most other suggestions.

So I think it's a good tool for 80-20 or 90-10 approach. Awesome. Anything else you wanted to add before we wrap up? Experiment better. Experiment more often. Experiments are a really cool thing to learn

and to make decisions based on data. So experiment, experiment, collect data, experiment, iterate, and so on. Yeah. Oh, actually, last thing to say is a lot of these settings don't require a full restart of Postgres. Like you can read the config. Yeah. Some do. So maybe if you're in a rush to get a new instance provisioned and you want to just check which ones do and don't, and don't fret the other ones.

I usually check PostgreSQL code.nf. I know they pull this data from PgSettings, which is quite trustworthy source, right? Yeah, it's got a really nice interface to show you requires restart, doesn't it? Right, I like that a lot. Exactly, exactly. So, for example, max wall size doesn't require restart,

so you can adjust it. And, for example, max wall size doesn't require a start, so you can adjust it. And, for example, even in some cases, I did a dynamic adjustment for some massive operations, understanding that recovery time in case of failure, like database failure, will be bigger, longer. But shared buffers, unfortunately, requires a start, and it's a big issue in some cases that requires. Yeah, exactly. And I think PG set statements as well.

So like, yeah, shared preload libraries requires restart unfortunately. So those kinds of things, if you can get them in at the beginning, it can be really, well, it can be helpful to avoid that restart. Yeah. Awesome. Good. We covered actually some details as well. I like it. Yeah, I think we did all right but I hope we

did good work covering some principles yeah between us we got there thank you I think you did a good job of doing that

thanks thanks everyone cheers Nikolai see you next week bye bye