Postgres FM - Estimating work_mem

Episode Date: August 21, 2026

Nik and Michael are joined by Shaun Thomas to discuss estimating work_mem, memory management in general, and writing an extension to help. Here are some links to things they mentioned: Shaun Thomas https://postgres.fm/people/shaun-thomaswork_mem https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEMpg_stat_database https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEWALTER ROLE SET configuration_parameter https://www.postgresql.org/docs/current/sql-alterrole.html#SQL-ALTERROLE-PARAMS-CONFIGURATION-PARAMETERhash_mem_multiplier https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-HASH-MEM-MULTIPLIERRecent Postgres releases with 28 CVEs fixed https://www.postgresql.org/about/news/postgresql-186-1711-1615-1519-1424-and-19-beta-3-released-3365/Systemic Risks in the Managed PostgreSQL Industry (part 1 of 6, Mehmet Ince) https://mehmetince.net/part-1-6-systemic-risks-in-the-managed-postgresql-industry-extension-risks-are-real-exploiting-postgis-memory-corruption-bug-at-neondb-supabase-and-many-more/Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

Hello, hello. This is PostGos FM. It is Nick, as usual, PostGGIIII. And as usual, my co-host is Michael PGR Master. Hi, Michael. And today we have a guest, which I know for quite some time, I think never met in person. But somehow our paths crossed many times when we write materials or read other people materials, obviously. And also on social media, it's Sean Thomas from PGH. Hello, Sean. Thank you for coming. Dick, Michael, what's going on? Yeah, and it took us some time to choose the topic, because you obviously write about a lot of various stuff.

Yeah, it's a very eclectic grab bag, but I'd like to make sure they cover all the interesting topics as they come up. And I was tempted to choose a Y-related topic, obviously, because I know you also, since the very beginning, GPT4 and so on, and I know you're like very actively used it, right? Yeah, a lot of that's your fault, honestly. You clued me in. I was like, well, that's really a lot more behind the scenes than I would have realized.

Cool. I'm glad to that I influence a little bit. That's great to hear. Thank you. But we chose very boring on one side, like a very technical topic. But I think what important is that this topic is very often misunderstood, especially if you don't spend every day in Postgreas internals a lot of hours, right? So it's easy to forget how to tune memory properly.

how to deal with workmaam, what to choose, and pros and cons and trade-offs about various choices. So I think boring choices are great here. Do you remember when the first time you needed to tune workmaim? It was long ago, right? Yeah, some of these older settings, like there's something you fiddled with sometime 20 years ago, and I'm like, oh, yeah, I did have to fight with that a lot, but it still comes up frequently, like in the mailing lists and in message boards and Discord, Slack, wherever, there's always someone that's wondering about how to set it.

And I wouldn't say it's a black art, but there's definitely some ambiguity there. I think it takes some significant time to understand nuances around it. Because what people expect obviously is that work mem is some limit for whole thing. It's like number one problem. And yeah, it's obviously not solved. So you published a blog post recently. Let's write extension that will help tune workman. I see it like two goals of this blog post.

One is let's write extension. And honestly, my opinion is maybe not popular, but I think we should write extensions less because they are not supported by on managed progress. Unless it's PGTLE. That's okay because it will be possible to run it anywhere. And also like literally yesterday someone posted how many vulnerabilities various third-party extensions bring. And then platforms like Neon, SuperBates and others are badly affected.

So this is one more reason not to write extension. So we can spend some time there, I think, to talk about writing extensions because it's still useful. Of course, I'm like, my opinion is very practical. Of course we should write extensions, but we should understand that probably like will be very limited in terms of use. But obviously the second goal is work memory tuning. And I'm curious, very curious, and my goal for myself today is to see how our visions are aligned

in memory tuning. I checked in the beginning, I checked some old enterprise DB posts. And I saw a very simple formula. And I'm very curious what we think about this. Let's take whole memory, divide by four. Usually it's done with shared buffers, right? And then divide by max connections. And this is our work, man.

What do you think about this? So I actually did a blog post like that while I was at EDB, and I think my formula was slightly different. I said something like divide by max connections and divide by five. Because the assumption there was every query could have some amount of query nodes, and since each of those can create an instance of work mem, assuming one is probably a bad idea. Because only the most simple queries will only have one query node.

Yeah, so five, it's quite like I would say conservative. Yeah, it's aggressive. It's aggressively conservative because I know that's, for one, that's how the Postgres community tends to operate. They use what they call sensible defaults. And it's an easy way to avoid out of memorying yourself until you can have more time to tweak it. That's interesting. Also, if we think about both of these approaches, and you're obviously

more advanced than that simple one, like we understand that each query can consume multiple times of work, ma'am. If we go with this formula to RDS, you know what will happen, right? No, I don't. What will RDS do? Max connections 5,000, even for small clusters. Oh, geez. Yes. All right. And they expect that everyone will probably be.

everyone will probably use RDS proxy but we observe a lot of customers who come to us they don't no Pidge a bouncer no RDS proxy only some poolers or on application side and those guys who run application notes they put it to Kubernetes with auto scaling so nobody knows which probability of reaching those 5,000 connections yeah we could have a whole other conversation on pooling. My opinion there is like you should never connect directly to Postgres, but... Right. Well, yeah, poolers should be inside. Yeah, I agree. But this is the reality, and the RDS is the most popular one, and people come with 16 VCPUs, 25 or 500 max connections. Yeah. At that point, you get to an area where you have to say sample your number of active

connections, and then it's a little more involved, but it's roughly the same idea. Yeah, so this is great. And this is like, for many years, it was with number one like approach. We basically we tune based on observations. Feedback loop, right? But I really want something more like not looking into actual thing, but predicting something, right? Because sometimes you launch new service

or sometimes you expect some growth. And observing production, it just doesn't feel super. It's very practical, of course. And it can work in either way as well. For example, if we take your formula, divide by five and even drop that five just no additional no additional dividing at all and then we say you know what like we still need more memory and we just observe there's a lot of like page cache is huge because work mom it's not allocated immediately right it's located in chunks like it's gradually so work

moment is limit for one operation inside query but it doesn't like it doesn't say that we run a query operation will have work may, maybe less. And if we observe from reality that we actually use much less, like we can overcommit here, right? This is what we do. Like we say, we go beyond theoretical limits because we see that practically we don't have out-of-memory risks. theoretically we have them, but practically no. And I know such clusters, huge clusters, they are running, like just ignoring this formula. And it's okay because majority of queries, they actually, use tiny amount of workman. What do you think about this?

Overall, it's like a bag of very different ideas and how to find better path to some recipe. The recipe I usually use, and the one I started with, honestly, is to use the database statistics. I think it's PG-Stat database and it's not, or it could be in PG database, where it tracks a number of temp files

and the number of temp file size. So what I do is I find out, the average size of the temp files Postgres is producing and then I add that to whatever the workman setting is. So if it starts out at the default form eggs and then I find out that Postgres is producing on average 8 megabyte temporary files, I'll add some padding onto that and add that to the workman so I end up with 12 or 16 would be my setting and that reduces all your disk spills by 90 plus percent and that usually addresses the problem. That's interesting. Let's Let's have some small posts here.

First of all, like obviously you take shared blocks dirtied or not sure, temp blocks dirted, right? Temp blocks dirted or temp files and temp bytes, like literally those two fields. Ah, okay, in PGSI database. And I'm thinking about PGCC statements. This is where I struggle because POSGIS has a huge gap. It doesn't have number of calls. It has only number of transactions.

So you need to divide by calls to have, average temporary bytes written per call, right? But we don't have it in Puget database. Yeah, you just have a very coarse heuristic that just says, I produce this many of temporary files and these are how big they were. So you don't really have enough find-grained ability to see how big they were like for each individual thing. Yeah. That's why I look at Pichs statements because it has calls. Of course, it may be tracks not all queries, but by default only 5,000. Businesserstatement.

Dot max is 5,000. But we have both temporary blocks written and calls. We can divide one by another and get average per call. But then I literally thought about this last week, even before your blog post, because we are also like, we deal with temporary

files generation in many customers. It's normal. They don't tune it and then, yeah, that becomes a problem. Why it's a problem? Because spilling to disk is slow, right? So it affects performance. And I was thinking, okay, we have average.

We know, for example, eight megabytes, as you said, like per call, for example. And we have eight megabytes workmen thinking, okay, we will set up 16. But then I was thinking, what if it's average, right? What if 90% of those queries are tiny? And remaining 10, they need 100 megabytes. Yeah, I usually want to hit situations like that. You just kind of have to live with the disk filling. You obviously don't want to get it past a certain amount where your queries are having to fight for disk I.O.

But at the same time, if the average is too high, you can't make it infinitely high. Yeah. So it's a problem of averages and lack of percentiles again here, right? I've seen, once you understand your workload, I've seen some people tweak it on a per user. Instead of setting it globally, keep the global low, but then set and applicate, like if there's a reporting, that we know only ever fires off one or two queries at a time, set its work mem higher. Have you seen things like that or done that in the past, Sean? I haven't, but that would definitely be another way out of it.

The fact that you can set a user-level session GUCs is a way out of a lot of ways. In the past, I've used that to turn off nested looping, for example. Back when the planner wasn't so great, and it still occasionally runs into stuff like this, you'd get a query that just resists every attempt to get to run with a, same plan, so you just take out a hammer and you knock out the parts you don't want to do and magically starts working. Now that's not so much a problem with Postgres 19 coming with the new hint syntax, so you can tell it to prefer certain paths, but before that you had a CTE or some kind of wall or manifestation

you had to construct to make it act a certain way. And one of those is, yeah, the coarse knobs of adjusting the planner or working with workMEM or some other thing. Yeah. And it's interesting that workMAM is not, if you select Star from PG Settings, workMAM is not sitting in a planner category. It's about memory.

But it affects how planner chooses the plan. So it's also an interesting thing. It might change the plan. Okay, so yeah, and it's a good thing that you can set either at session level or at user level as well, alter role, right? So I don't think people use it often, but it's a good thing for some heavy queries

to move this workload to different user completely. But also this makes understanding of the whole picture harder slightly. Yeah, but if you have a mixed use case system, you have to choose your battles, right? So I think Michael's point is very salient because if you have an OLTP system that's taking, that's 90% of doing what it's doing,

and every once in a while a huge query sneaks there where you've got a batch job you need to run like setting for the long batch job the transaction length to some larger amount so there's lots of other different settings you might do for that one particular thing that you wouldn't want to change for the whole server

so usually in a case like that I'd say make a reporting system so you can direct your LTP stuff there but that's an architectural thing for a later time one thing that's changed in recent years is the introduction of Hashman multiplier and the change of the default of it from one to two. It was really great in your blog post to see you taking that into account when estimating how much a single query is going to take. But I haven't seen that in many other blog posts, even since it was changed.

So do you think maybe some of the formulas you might come across via search or even LLMs these days might be maybe not even conservative enough because there's this multiplier now for? quite a few operations. Yeah, the problem with using the hashmmmim multiplier as part of a generic formula is, unless you know how any hash memory operations are going to occur with a query, you're just guessing. At least with the number of nodes, you can say, I'll say there's like a four or five average or a two or three average or whatever is for your platform, you can say, just multiply it out. But with Hashmem, it's just, it's another factor that complicates the formula in a way that's

not really meaningful, because we're already estimating, right? already just picking a number out of the sky about how many query knows and how many things that's under your estimate. And even the extension that I wrote is very coarse. It's just not every hash node will be 2x. It doesn't handle a pens. It doesn't walk the entire tree. Like it's, it was a first approximation to give a guess at a worst case scenario. Worst case scenario is okay here. Yeah, I agree. I understand that, but I think the worst case scenario has just got worse since the multiplier of two means everybody's worst-case scenario has got worse because those nodes could take more memory now.

To an extent, but I believe the reason they actually put that there was to constrain memory more. Previously, I could have this wrong, but I remember a couple threads in the mailing list where someone was saying that Cash Mem was unbounded by WorkMMM at all. So it was causing them to get OMS because one Quarry would, would create an infinitely large hash and it would just crash the system. So now they actually hash mems honor a limit, which they didn't before. So yes and no.

Yeah, and your extension, it's looking at a plan, right? So it knows exactly how many hashing happened and other operations. And there's a great idea. And I think it's possible to apply that scale. For example, at least if, like, very roughly, we talk about estimates. So what if we take all queries from Pugster statements, 5,000 by default maximum, and just collect generic plans? Yeah.

That's it. So your extension, obviously, it was like at a micro level, just a single query, right? And for those who haven't seen, it's just producing some additional hints, log messages, hints, what peak usage for this query could be. And then you can understand, like, for session. level, at least for this query, you can understand how to avoid the spilling to temporary files, to disk. But we can apply it to macro level, right, with just collecting generic plans. Of course, it won't be super precise because custom plans might have very different opinion,

how many hashing or ordering should happen. But it can be good enough for estimate, I think. Do you agree? Yeah, so the way I wrote it was to, the extension was basically to exercise an example of, here's how I write a function that a user can call. Here's how you can leverage UCs. Here's how you can override hooks. But because of all that, yeah, you've got a user level function you can call

and as a debugging process. I've got this huge query I might want to run in production. Will it blow things up? Oh, it could use up to a gigabyte of memory. Whoops, let me go back and look at that again. Or maybe I want to adjust my work mem to make it use less memory overall in general. in general. But then yeah, you could in theory point whatever's in PG stat statements at it and just send them all through the function in the loop. Ordis is part of a query call and get some

kind of maximum estimate out of that. Yeah, that's, I think, quite doable. And it could help to tune better actually, right? Yeah. That's good that you align here as well. I have a very interesting specific question. Maybe you or Michael knows. So why does PostGGris crash when it reaches, like when there is not enough memory? Instead of crashing one back end somehow, right, it crashes the whole cluster. Of course, if it's crashed the backend normally, I understand, like for protection. Yeah, for shared buffery protection. We need this to restart.

I understand that. But I know RDS had some extension or logic, a proprietary one. to just to give up only those back-ends which cannot be like sweet to memory but not to lose others but then they somehow removed it deprecated and I'm very curious what's happening in this area and why post-gress cannot protect other like others already running give them chance to complete right do you know no like I'm very curious about this talk I think I think it's a Linux thing correct me if I'm wrong Sean but I think I think it's the out-of-memory killer and the fact we've got the postmaster.

So, yeah, if you've got a back end that's using a lot of RAM and post-goats gets killed arbitrarily by the colonel, sure, that'll cause it to complain about stuff. And I believe if there's no memory available and you do a memory request, you'll just get back in air and your back end won't die. I need to run a test to see actually what happens if you run into a situation like that. into a situation like that where we've turned off over commit, for example. So you actually get an out of memory error from your back end if it tries to allocate more than this available. But yeah, I think that's, I think what it is that the OOM killer terminate the back end. And then since that back end can't clean up after itself, you could have corrupted memory

context because Postgres uses arenas with shared memory contexts. And if you can't roll back your arena back or clean out your context, the entire shared state of the buffers could be corrupt in some way that you can't really consider. So as a defensive measure, Postgres shuts the entire thing down and then restarts. But aside from that, I'm not entirely sure. We need someone who's worked with it. If Andre or Mason at work, they would know, or maybe a brar. So yeah, yeah, I'll practically, I would prefer to have errors for specific sessions, but all others are still working. And if we see that,

we're about to be out of memory. That's practically more convenient. Similar cases with out of disk space. Yeah, it's not a good place to be. And I know some people play some file filled by zeros and they just remove it if urgency happens. Anyway, yeah, that's interesting question as well. Good. Okay. Speaking of writing extensions, I have a feeling. 20 years ago, post-gis is extensible, extensions are great, and then managed services, managed providers, they just somehow brought us to the point when extensions are against extensibility, because they need to approve it. And it's like with all those scary stories about vulnerabilities, their approval rates will slow down. Yeah, so the issue with post-gross extensions is they're very

much a double-edged sword, right? It adds this new functionality that Postgres never had, and that's amazing. We wouldn't have PGVectarize, for example, right now, or PostGest or any of those other huge extensions that everyone relies on now without it. But at the same time, the Postgres extension mechanisms, and I now know this firsthand since I've played with it with my tutorials, is awful. Like, for example, the hook system. There's no, like you normally see register hook, some kind of function you'd call that would put your hook on a stack, and it would control that where the hooks are called in. You wouldn't be able to accidentally not call the next extension in the list.

Right now you actually have to check and see if there's a next or previous hook. If there is, save it for later and call it yourself in your extension. And if you don't do that or if someone decides they want to be a bad actor and don't do that in their extension, the whole stack gets broken. The other issue is there's no sandbox. Even browsers, right? Just some user's browser they're using the browser of the web has a sandbox per thing. And Postgres has no even concept of that right now.

So you end up with CVEs that get escalated into the core because the Postgres extensions are literally calling core structures. There's no API for any of this stuff. It's just, oh, you know Postgres function? Go ahead and include it and call it internally. You have direct core access to every part of Postgres. all the time. This would be great to fix, by the way.

Do you know anyone look in this direction? I don't. But I can't imagine that nobody's thought about this or nobody's thought to, can we add sandboxing or can we add a hook management mechanism of some kind. But it's just low priority because right now all the core developers work on Core Postgres. And their interest in extensions is tangential at best, right? Because if they want something, they just put it in the core. And if someone else wants something, they can add it for their own convenience.

And the core just experienced the biggest, in terms of CVs fixed, the biggest minor release, 28. Oh, the 28 of the 28 CVs they fixed in 19, yeah. 18.6, yeah, right, 18.6. And then 18.5 never came out because it got reverted. Which happened last time, it happened in 2008. It's been a while. Yeah, yeah, obviously. But this, exactly this question, three. figures, like question about will next minor release have even more CVs? And this triggers next question, what about extensions we have on managed providers?

Or not managed providers, just if we'll start host workers. Not only will the next one have more, it's going to climb exponentially, in my opinion, because not just because of the visibility, because obviously the 28 number sounds high and people are like, probably take a look at this now and there's going to be more eyes on it, but because of AI. You said we weren't going to focus on that, but if we're getting honest here, everyone is pointing their AI agent at everything they possibly can. And now those can find bugs that we never would have thought about because we just didn't have time or priority or there wasn't a test case for it. And now there's infinite amount of research area there. So not only are we going to, are we going

to see that, we're going to see it accelerate. Is it infinite? I think it's finite, isn't it? There should be plato at some point. The number might be high, and I have no idea if 28's getting even close to the right order of magnitude per release, but it feels like it will accelerate for a while and then hopefully slow down? Yeah, that's a valid point, but I think, at least in the short term, we are definitely going

to see an acceleration. 28 is just a drop in the bucket because I don't know about being honest is the right way to phrase this, but Postgres being written in C has a lot of potential for edge cases that we just haven't even looked at. You have fuzzlers, you have, you have memory leak checkers, you've got pointer checks that are done right now by various different mechanisms we have. We've got our build farms. There's lots of stuff to catch this early, but you don't miss 28 CBEs because you're dumb. It's just there are new attack vectors that nobody thought of. And extensions open that up even more.

So the popular extensions are going to be also attack vectors, and since extensions themselves are a way to get into the Postgres core, if you exploit an extension, you have access to core. So that even adds more vectors that either they have to start to find a way to enforce a sandbox, or is it going to make that discussion a lot more germane. and I saw someone post on X it was a security researcher and he's going to do a four part or a five part series on how he broke into post JIS

for example. When he escalated that all the way to Route CVE where he could arbitrarily affect files on the file system from PostGIS. And that's just part one of five. And he's going to slowly escalate the amount of attacks he does all the way to the point of getting root on the container through Kubernetes.

So it's just ridiculous the amount of analysis that people have really not done to this point because it just wasn't on their radar. But now it is. And it has to be because everyone in their dog has an AI that can be like, oh, I'll just attack this 24 hours a day, seven days a week, until I find something. We should say thank you to Anthropic and Open AI for limiting capabilities. You cannot do it with their latest models right now. they will like completely say I'm not continuing.

Yeah, those won't, but anyone who has a modern Kwan or KMUK3 or any of the local models that's been put through a Laura that is uncensored. And then if you have access to, I don't know, $50,000 worth of equipment, you can direct it at anything. That's interesting because I saw Anthropica was mentioned a few times in the release notes for those minor releases, but I don't remember any other AI systems mentioned. They're just the ones that people are focusing on because they're the frontier models. But anything that's been open-sourced can be repurposed. Yeah, yeah.

We're getting a little bit off the topic. But the reason extensions are at this point, and Postgres, actually really any software now, is because of AI, for good or ill. Yeah, maybe let's connect topics. So we could use AI to tune WorkMem? Yeah, I actually used it to find all the, bugs in my extension because my extension was a perfect concept, right? It's just, here, you could do this. It's kind of fun. Throw away kind of material. So it found all the issues in it.

Oh, you're not looking at append. You're not looking at whatever. You can, you could use the built-in Plan Walker and get all these extra things you missed. And oh, you don't realize that if you run out with these inputs, it causes your extension to crash, which takes down the back end. And it found dozens of things I could fix where I so inclined. So anyone can do that to their extension. It's a huge help. And should do. Should, yes, also. Because the amount of eyes, like, the thing is, even in our company, we don't have my PGI

Janiceball project that I work on for doing distributions of architectures. There's like maybe two other people in the company that can help me do code review on that. But I always have Clod available to look over things or co-Rabbit or whatever tool you want to use. And they'll catch stuff that I didn't consider. I had a question on things you consider. I liked that it was simple. I liked the formulas I could follow them, the examples I could follow them.

But I did wonder if you, like, for example, sequential scans were adding one times work amendment. Like it was every node type it seemed. Yeah, like it was a very naive. Like I just, anything that was a node, I counted it. I just didn't even like check.

The only thing that I gave an extra bonus to was anything that had the word hash in it. Like literally, I just grept for the word hash for the node types and I just put them all on that big list. But yeah, like, that was the naive approach. A more refined approach would be to actually go through and figure out which nodes actually do what. Because the parent hash node is not where the memory gets allocated, it's actually the child

hash elements. So I was actually double counting the hash nodes ironically inflating the results. So yeah, obviously a good opportunity there would be to spend some time refining the algorithm, but my worst case scenario was just like, let's just count all the nodes and then multiply, and then you get like a here's the maximum amount this thing could possibly take. It's off by a little bit. It's better than the estimates we've been relying on. And it was a semi-useful kind of extension,

and it demonstrated the process of writing an extension, except for the fact that I didn't create a memory context. One more question was back to what you said at the start around using max connections as a multiplier. It wouldn't be the same formula, but I wondered if instead it might be sensible to use some multiple of the number of cause. The reason I came to that was thinking parallelism, like a parallel plan could use multiple times the multiple work mems. So like I've seen sorts as part of a parallel plan use kind of four or five times work mem.

But obviously that's a much lower number in most cases. So it would be a very different formula. But I wondered if there was any merit to that maybe in. in formulas. So, yeah, like, in that case, you would look at your max parallel workers per gather option, because that's the maximum number of cores that it would actually leverage in a single query, and then multiply that by the number of backends. But, yeah, you could do that.

Yeah, just thinking if you've got 16 cores, and you've got a bunch of queries trying to fire off lots of parallel workers. The first few might get all of the workers they want, but the next ones are only going to get a single one. Like it won't let you run more than... Yeah, yeah, that would act as a cap. Have you ever done anything like that, Nick? Yeah, I barely understand what you say, Michael.

I think he was asking if you had used the number of cores as part of your estimate for workmen. And I kind of get where he's going with it, because even if you set max parallel workers per gather to limit the amount, eventually they'll run out of, you'll hit your max parallel workers. Right. Not VCPU count, max parallel workers. Postgres has no idea how many cores or how much.

Right. But which is why you'd set the max parallel workers and various other settings so that you wouldn't exceed it. Why? Like, we can exceed it. We can exceed it. You can. You can.

You can. I wouldn't recommend it, but. I agree with you. But looking at guys who come to us like 16 or 13. know 32 course and max connections 5,000, I already have a shift in my mind. I cannot convince them. Like we spent a lot of efforts saying this max connections is abnormal, especially before PostGbiz 13, 14, when Andres Freund improved work with snapshots. Yeah. Which is very related to memory consumption, right? Yeah, we tried like, max connections should be like three, four times

more than VCPU. That's what everyone says, but... No, not anymore. We don't say it anymore because since Postgust 14, it's much better. And I think I had some tests showing... We should revisit this, by the way. I want to revisit with benchmarks and see exactly

how it degrades. But now it degrades less... Yeah, it's not nearly as bad. You still have to fight the kernel process table, but it's not as bad as it was. Right. So this is just reality. These guys come to us with Ardias and telling them that you need to reduce my connections drastically, you need restart for it. And they have, if they don't have proper database site puller, we cannot convince them to get rid of huge amount of vital connections.

They just need them to satisfy application guys' needs because those guys, as I said, they scale their application nose like this. especially e-commerce when Black Friday happens, they just need to scale. Or some news system as well, like social media, they need to be able to scale and they need those idle connections. So this, back to work, ma'am here, I don't know, it depends. Also, sometimes we need to reproduce plants in an environment which is much weaker physically than production because we study behavior of post-gust. And we are okay for some content.

happening in terms of physical resources, but we want the planner to behave exactly like in production. So there are some nuances, right? But I agree with you. Overall, I would like to see average number of sessions below this CPU count. This is great. Yeah, usually that's what I still tell people, not because of necessarily they're going to see a huge drop in performance, because it's let's face it, it's going to be around the 20 or 30 percent mark at maximum, even if they're sending hundreds of thousands. But it's still, I would say, a best practice to do so. Yeah.

Maybe going back to basics a little bit, when you're tuning work memm, is it always to do with latency, just like speed of queries on average? Or are we sometimes trying to look after the disks a little bit? We're trying to increase headroom there a little bit. Or is it mostly just user-facing query times? I guess it depends.

of what hat you're wearing. As a DBA, you're just like, I don't want my database to crash or I don't want the hardware to burst into flames because it's being misused in some way. From that perspective, you're like, okay, I'll set it to be just enough that I avoid lots of disk spilling and then causing disc wear or really slow latency. But there's also a point of diminishing returns, right? If you set it to some infinitely high amount, you're not gaining anything out of it. All you're really doing is making it so that your maximum is higher for no reason, and you end up getting more risk of an out-of-memory error. Really, it's just one of those things like Nick had said. It's how do we set it properly without going overboard? How do we go do it without going too

little? And part of that is taking what you have and using heuristics to come up with some kind of reasonable number, like using PD stat statements and sending it through some kind of estimation process. Or I found out that apparently if you send the query through the pre-examination, the pre-execution step, it actually calculates all the memory that it would allocate, but it doesn't allocate it yet. So in theory I could walk the plan nodes and actually get the estimates directly from the planner, whereas my approach was very coarse and it just did it based on the node types. You could get it directly from the plan output, from the executor step itself, from the pre-executor.

And you could actually pull those bits of data and actually get an exact number of what the allocator would have actually asked for from Postgres. So a better approach, I would say, at least as far as revising my extension, would be to say, use the built-in plan walker, do it after the pre-execution steps, so you have all the estimates of memory usage that it would have done in the first place, and some of those totals instead. Then what you end up with is a real estimate of what all the queries would have taken without them executing. And then you can use that to design your I'll

ideal work memo based on your amount of average active backends and whatnot. But it's just one of those things with Postgres. You have to always go back and retroactively examine how your system is operating. And a lot of that is observation. Do you have a dashboard? Do you have observability and visibility across your entire cluster to see how it's actually operating? And that should be how you drive your systems.

If you see that you're always running out of memory at some point, look at your memory settings, look at shared buffers, look at work mem, look at anything that could possibly allocate stuff and then maybe reduce it a little bit if it's running out memory or increase it if it's not. It's a delicate balancing act and unfortunately there's no one-size-fits-all way to addressing everything, which is why there's so many guides. There's blogs and tutorials and videos and everything gore of how to do it and no one really can agree on one final answer. There is no official runbook, auto documentation. This is said. actually but I can imagine how hard would be to achieve consensus on on the

concrete protocol and actually we somehow avoided the topic of swapping us like so we could enable swap right and this is like instead of temporary file for each query let's enable on that far end and if we achieve that let's swap there one thing and another thing like I just reasoned very like a lot with your words about for example maintenance workma which usually is inherited by auto vacuum work mem being minus one, right? And then we tell everyone we should have more workers for auto vacuum workers. And then PostGGGIS 17 silently, like unexpectedly lifts unspoken limit one gigabyte.

It was not like obvious that we actually were limited. But guys already raised maintenance workmembs to say eight gigabytes and raised number of workers to say 25. And now we have an interesting memory allocation for auto vacuum, which we didn't want. So now we say raise a number of auto-vacom workers, but also limit auto-vacom workmemm by one gigabyte because when you will upgrade to 17. So layers of logic, yeah. I totally forgot about maintenance work-mem and vacuum work-mem because those, you don't really think about those, because the one that really bites everyone is work-mem because they set it to some value and then it explodes on them.

But yeah, the other two definitely are a factor. Swap, like, swap, like, I was trying to avoid swap on. post-gess machines I remember but it's very old I haven't haven't revisited this topic because I remember dealing with database post-gess database which experiences heavy swap since then I always avoided it but then I remember Bruce Momgen said we should just small swap is good I said no it is completely avoided I would better see out of memory and fix my memory settings and so on what's your opinion about having

swap enabled on the machine with post-gues The problem I usually see with swap is you can't really account for what the kernel or the memory pressure systems will do. And I've had problems in the past with previous kernels doing things that they shouldn't. So I try to get as much control as I can. So in that case, I usually set swappiness to one because if you set it too low or zero, the memory pressure systems go wonky. And then I set to some low amount like two gigs, four gigs, some token amount just so the kernel has. area to work with. Then I set over-commit memory to two, so you can't over-commit, and then I set the over-commit KB to the exact amount of physical memory that there is on the system. So it will not use SWAP because it basically can't, because it's been subtracted from the total. Any allocation has to be physically backed by actual RAM, at least as far as the database is concerned, so you don't end up with a commit, and the OOM killer won't kick in because there's not anything using too much RAM.

because it can't. If you make a request, you simply get denied. And then you're back and I'll go, oh, I can't allocate memory, so I won't run this query for you. And I'd rather have a failed query and have someone have to go back and look at their query or revise it or whatever, then take the system down because OMKiller decided that there's a road process. So basically it always comes down to, as a DBA, getting as much control as you can over the system and enforcing it stringently, which is a little harder to do in the Kubernetes context because those limits aren't. enforce the same way. Right.

And there is one more point. We somehow also avoided important topic. We could plan everything very well, shared buffers, maintenance workman for index creation, auto vacuum workers, then our backends with workmen, but somehow we forget about page cache and many systems page cache is super important. Sometimes, like if it's, for example, if we had a situation when it was excessive, you exceeding shared buffers. For example, a simple example. We perform minor upgrade with a start server and we don't think about pre-warming because actually there is page cache sitting

there which helps us to have better performance sooner. We recently had internal discussion about that. Do we need to have a pre-warming, PG pre-warm, automated or not automated? And the question is if there is huge page cache, probably we don't need to bother. But if we start tuning workman, page cache will become thin and very narrow, right? And this can be a problem as well. So it's very tricky, right, to think about macro level and everything. That definitely directs how you will want to choose your hardware, because one of my talks at Postgres Open, I think it was 2012, was about our woes with trying to get pre-warming working. We would have a Postgres crash because we were using EDB at the time and there were

we were using a couple of extensions from EDB that were, let's just say, beta quality, but they were doing something we needed. So occasionally we'd get a crash, fine, whatever. So the database goes down, sudden airship buffers has been invalidated, but we were also finding that the page cache was not sufficient, because a lot of those backends, at least at the time, were Postgres backends, and they had their minimum memory allocation, and the page caches were too small because of all that.

you end up with Postgres RAM, backend allocations, and then a small functional page cache. So what ended up happening is after the crash, Postgres would take an hour to warm up based on user queries. So that whole time your latency jumps by like 20 times because you're running off an old rate array or something. So our fix was to get a fusion I.O. drive, which would be equivalent to 100,000 iop device from, EBS or something, like an I-O-2, or just a really high-end NVME M.M-2 stick or something. Local MBME, yeah. That's great. It was really the only way out at the time because you'd see the spike of activity and the

IO usage from IOSAT, right? It would shoot up to 100% usage for, and it would just stay there for an hour. As soon as we upgraded to the newer device, it was spike rolled once in the beginning, as soon as the the crash was over and then it would just cover around 20%. But that 20% was on 100,000 IOP device. So if it were anything less than that, it would be a lot worse. So even page cache can't really save you in certain circumstances. It really depends on your workload.

And with Postgres needing a quarter of your RAM, well, not needing, we recommend using a quarter of your RAM up to a certain limit. If your page cache is too big, then you're essentially a double buffer. And you have much higher likelihood that temporary files spilling to disk will happen. So this is the whole point to avoid. The case you described exactly like what I'm saying about and having faster disks definitely helps. These days we can have millions of iops with local and VMEs, right? But I also think if we tune auto-work, if you tune work my-am, so we make page cache quite a thing.

And we need to think about how fast or slow disks are. And if they know they are slow, then maybe we should consider automatic pre-warming. Because PG-Prem right now supports automated pre-warming, right? So it can capture maybe this is the exact moment. Slow disks and small page cache and we should start. This actually adds to the whole picture of tuning workmen, right? Yeah, and back when this happened, we, PG-pre-worm wasn't really a thing. so I cheated by just like using DD

so I quarried the catalog figured out which backend files went with the most used tables and I would just before I started the server I would DD them all into memory and then I would start Postgres and then that solved 80% of the problem but that wasn't sustainable long term so that's why we bought the storage but there's ways you can get around it so definitely pre-warm it's extension people don't really think about because it's not really a problem so much anymore because everyone's got infinite

iops it's roughly. But if you don't and you don't want to pay I.O.2 fees or load up your system with expensive storage, then pre-warming is still an option. I agree with you and bigger databases with local enemies. We know a few companies who bet on it heavily, right? But also there are many more smaller clusters, usually single-node clusters which are needed to support some AI building, AI builders' products. They just experiment a lot

and they don't need serious database, but they still need some database. And in that case, tuning, like what we just discussed could be used for them because tuning work, maim,

so queries are good enough in terms of performance, but also you know that restart will help you survive not being super slow for half an hour. So I think there are interesting cases And this case with smaller databases because of AI, I think they will grow a lot. Well, yeah, I mean, that's actually a good point, because especially if you have database branching,

like I know that you have in your product, you've got the ability to fork off like dev incidents of databases, and those are entirely cold. Without the proper backing on them, they're going to be slow for a while, at least after start. In case of DBLAP, it's ZTF, and ZTF has RUFS. We usually allocate half of memory to it. So those blocks are like they are warmed up already usually. So that helps a lot. We have a different concern.

Developers ask us, can you implement cold cache? Because we study explain plans. We want cold cache to see how like the worst case. And this is tricky in this architecture because it's a multi-tenant thing. A lot of things, a lot of post-gust exploration happening on the same VM and we need that cash. actually. So like, how would you even do that? You'd have to move it over to another instance and that's cold. Yeah, it's you cannot do it without losing the common cache. But common cash helps others. So because we have different use cases, it's not always exploration of explain

plans, but also sometimes just preview environments for testing. And those guys want better performance. So it's a complex topic. But we learned very early, actually, that we should match workmen to production. because it affects the planner behavior which I mentioned. This is important for any lab environment. Okay, I'm out of questions. We touched a lot, like we went quite broadly. Touched a lot of additional questions.

Thank you so much. It was very interesting discussion. Yeah, I especially like you confirmed a lot of things I have in my head. So it's great to hear confirmation. But also learned a lot of new stuff. Thank you so much. I will follow your new blog posts. Don't stop writing.

It's very interesting. always. Yeah, I don't plan to. And I always like to remind people that if you've ever heard of Pearl as the pathologically eclectic rubbish lister, that's basically how my brain works. Okay. Okay. Incredible.

Great. Yeah. Well, really nice to meet you. Thanks for joining us. Thank you. You too. Hope you have a good day. You'll have a great week.

Bye.