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-thomas work_mem https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM pg_stat_database https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW ALTER ROLE SET configuration_parameter https://www.postgresql.org/docs/current/sql-alterrole.html#SQL-ALTERROLE-PARAMS-CONFIGURATION-PARAMETER hash_mem_multiplier https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-HASH-MEM-MULTIPLIER Recent 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
Nik: Hello, hello, this is Postgres.FM It is Nik, as usual, PostgresAI, and as usual, my co-host is Michael pgMustard. 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's materials, obviously, and also on social media. It's Shaun Thomas from pgEdge. Hello, Shaun. Thank you for coming.
Shaun: Hi, Nik, Michael. What's going on? Nik: Yeah, and it took us some time to choose the topic because you obviously write about a lot of various stuff. Shaun: Yeah, it's a very eclectic grab bag, but I like to make sure that cover all the interesting topics as they come up. Nik: And I was tempted to choose an AI-related topic, obviously, because I know you also, since the very beginning of GPT-4 and so on, I know you're like very actively use it, right?
Shaun: Yeah, a lot of that's your fault, honestly. You pulled me in, I was like, well, that's really a lot more behind the scenes than I would have realized. Cool. Nik: I'm glad that I influenced a little bit. That's great to hear. Thank you. But we chose very boring on 1 side, like a very technical topic, but I think what's important is that this topic is very often misunderstood, especially if you don't spend every day in Postgres internals a lot of hours, right? So it's easy to forget how to tune memory properly, how to deal
with work_mem, what to choose, and pros and cons and trade-offs about various choices. So I think topic choice is great here. Do you remember when 1st time you needed to tune work_mem? It was long ago, right? Shaun: Yeah, that's... 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. Nik: 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 the whole thing. It's like the number 1 problem. And yeah, it's obviously not so. So you published a blog post recently, Let's Write Extension, that will help tune work_mem.
I see it like 2 goals of this blog post. 1 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 managed providers. Unless it's pg_tle. That's okay, because it will be possible to run it anywhere. And also, like yesterday, someone posted how many vulnerabilities various third-party extensions bring.
Then platforms like Neon, Supabase and others are badly affected. So this is 1 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, my opinion is very practical. Of course, we should write extensions, but we should understand that probably it will be very limited in terms of use. But obviously, the 2nd goal is work_mem tuning. And I'm very curious, and my goal for myself today is to see
how our visions are aligned in memory tuning. In the beginning, I checked some old EnterpriseDB posts and I saw a formula, a very simple formula. And I'm very curious what you think about this. Let's take whole memory, divide by 4, usually it's done with shared_buffers, right? And then divide by max_connections. And this is our work_mem. What do you think about this? Shaun: 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 5. 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 1 is probably a bad idea, because only the most simple queries will only have 1 query node. Nik: Yeah, so 5 is quite, like I would say, conservative. Shaun: Yeah, it's aggressive. It's aggressively conservative because I know that's, for 1,
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. Nik: That's interesting. Also, if we think about both of these approaches, and you're obviously more advanced than that simple 1. Like we understand that each query can consume multiple times of work_mem. If we go with this formula to RDS, you know what will happen, right?
Shaun: No, I don't. What will RDS do? Nik: max_connections, 5, 000. Even for small clusters. Oh, geez. Yes. Shaun: All right. Nik: And they expect that everyone will probably use RDS Proxy But we observe a lot of customers who come to us. They don't know PgBouncer no, this proxy only some poolers or on application side And those guys who run application nodes, they put it to Kubernetes with auto-scaling. So nobody knows which, like, probability of reaching those 5,
000 connections. Shaun: Yeah, we could have a whole another conversation on pooling. My opinion there is like you should never connect directly to Postgres. Nik: Right, yeah, poolers should be inside. Yeah, I agree. But this is the reality and RDS is the most popular 1 And people come with 16 vCPUs, 2,500 or 5,000 max_connections. Shaun: 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. Nik: Yeah. So this is great.
And this is like for many years. It was with number 1 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 5, and even drop
that 5, just no additional dividing at all. And then we say, you know what, we still need more memory. And we just observe there's a lot of like page cache is huge Because work_mem is not allocated immediately, right? It's allocated in chunks like it's gradually So work_mem is limit for 1 operation inside query But it doesn't like it doesn't say that within a query, each operation will have work_mem, maybe less. If we observe from reality that we actually use much less, we
can over-commit here. 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 work_mem. What do you think about this? Overall it's like a bag of very different ideas and how to find
better like path to some recipe? Shaun: The recipe I usually use and the 1 I started with, honestly, is to use the database statistics. I think it's pg_stat_database, or it could be in pg_database, where it tracks the 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 work_mem setting is. So if it starts out at the default 4 megs 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 work_mem 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. Nik: That's interesting. Let's have some small pause here. 1st of all, obviously you take shared blocks dirtied? Oh, not shared, temp blocks dirtied, right?
Shaun: Temp files and temp bytes, like literally those 2 fields. Nik: Ah, okay, in pg_stat_database. I'm thinking about pg_stat_statements. This is where I struggle, because Postgres has a huge gap. It doesn't have a number of calls. It has only a 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 the pg_stat_database. Shaun: Yeah, you just have a very coarse heuristic that just says, I produced this many of temporary files and these are how
big they were. So you don't really have enough fine-grained ability to see how big they were like for each individual thing. Nik: Yeah, that's why I look at pg_stat_statements because it has calls. Of course, it maybe tracks not all queries, but by default only 5, 000. pg_stat_statements.max is 5, 000. But we have both temporary blocks written and calls. We can divide 1 by another and get average per call. But then I literally thought about this last week, even before
your blog post, because we also, we deal with temporary files generation in many customers, it's normal, they don't tune it and then it becomes a problem. Why it's a problem? Because spilling to disk is slow, right? So it affects performance. And I was thinking, OK, we have average. We know, for example, 8 megabytes, as you said, like per call, for example. And we have 8 megabytes work_mem. Thinking, OK, 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 the remaining 10, they
need 100 megabytes? Shaun: Yeah, I usually run into situations like that. You just kind of have to live with the disk spilling. 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. Nik: Yeah. So it's a problem of averages and lack of percentiles again here. Right. Michael: 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 an application, like if there's a reporting application that we know only ever fires off 1 or 2 queries at a time, set its work_mem higher. Have you seen things like that or done that in the past, Shaun? Shaun: 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 get a query that just resists
every attempt to get to run with the same plan. So you just, You take out a hammer and you knock out the parts you don't want to do and magically it 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 of materialization you had to construct to make it act a certain way. And 1 of those is, yeah, the coarse knobs of adjusting the planner or working with work_mem or some other thing.
Nik: Yeah, and It's interesting that work_mem is not, if you select star from pg_settings, work_mem is not sitting in the 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. So yeah, and the good thing that you can set either at session level or at user level as well, alter role. So I don't think people use it often, but it's a good thing for
some heavy queries to move this workload to a different user completely. But also this makes understanding of the whole picture harder, slightly. Shaun: Yeah, but if you have a mixed use case system, you have to choose your battles, right? Nik: So Shaun: 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 in 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 1 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 OLTP stuff there but that's a architectural thing for a later time. Michael: 1 thing that's changed in recent years is the introduction of hash_mem_multiplier and the change of the default of it from 1 to 2. 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. Shaun: Yeah, the problem with using the hash_mem_multiplier as part of a generic formula is, Unless you know how many hash memory operations are going to occur within a query, you're just guessing. At least with the number of nodes, you can say, I'll say there's
like a 4 or 5 average or a 2 or 3 average or whatever it is for your platform, you can say, just multiply it out. But with hash_mem_multiplier, 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? We're already just picking a number out of the sky about how many query nodes and how many things that's going to be in your estimate. And even the extension that I wrote is very coarse, it's just Not every hash node will be 2x. Not every, it doesn't handle Appends, it doesn't walk the entire tree. Like it was a 1st approximation to give a guess at a worst case
scenario. Nik: Worst case scenario is a key here, yeah, I agree. Michael: I understand that, but I think the worst case scenario has just got worse since the, so the multiplier of 2 means everybody's worst-case scenario has got worse because those nodes could well could take more memory now. Shaun: To an extent but I believe the reason they actually put that there was to constrain it, constrain memory more. Previously, I could have this wrong, but I remember a couple of threads in the mailing list where someone was saying that
hash memory was unbounded by work_mem at all. So it was causing them to get OOMs because 1 query 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. Nik: Yeah, and your extension is looking at the plan, right? So it knows exactly how many hashing happened and other operations. That's a great idea. I think it's possible to apply it at scale.
For example, at least if, very roughly, we talk about estimates. What if we take all queries from pg_stat_statements, 5, 000 by default, maximum, and just collect generic plans. Yeah. That's it, right? 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 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? Shaun: Yeah. So the way I wrote it was, the extension was basically to exercise
an example of, here's how you write a function that a user can call. Here's how you can leverage GUCs. Here's how you can override hooks. But because of all that, yeah, you can, you've got to use a little function you can call and it's like 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. 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, or just as part of a query call and get some kind of maximum estimate out of that. Nik: Yeah, that's I think quite doable. And it could help to tune better, actually, right? Yeah, that's good that you aligned here as well. I have a very interesting specific question. Maybe you or Michael knows. So Why does Postgres crash when it reaches, like when there is
not enough memory? Instead of crashing 1 backend somehow, right? It crashes the whole cluster. Of course, if it's crashed the backend abnormally, I understand like for protection. We need this to restart. I understand that. But I know RDS had some extension or logic, proprietary 1, to just to give up only those backends which cannot be, cannot like switch 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 Postgres cannot protect other, like others already running give them a chance to complete, right? Do you know, no? Like, I'm very curious about this. I think Michael: it's a Linux thing. Correct me if I'm wrong, Shaun, but I think it's the out-of-memory killer and the fact we've got the postmaster. Shaun: So yeah, if you've got a backend that's using a lot of RAM and Postgres gets killed arbitrarily by the kernel, 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 an error and your backend won't die. I need to run a test to see actually what happens if you run into a situation like that where you've turned off over commit, for example, so you actually get an out of memory error from your backend if it tries to allocate more than is available. But yeah, I think that's, I think what it is that the OOM killer terminates the backend, and then since that backend can't clean
up after itself, you could have corrupted memory context because Postgres uses arenas with shared memory context. 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. Andres or Masahiko Sawada, they would know.
Or maybe Álvaro. Nik: So yeah, I 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 case is with out-of-disk space. It's not a good place to be. And I know some people place some file filled by zeros and they just remove it if urgency happens, right? Anyway, yeah, that's interesting question as well.
Good, okay. Speaking of writing extensions, I have a feeling 20 years ago, Postgres 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. Shaun: Yeah, so the issue with Postgres extensions is they're
very much a double-edged sword. It adds this new functionality that Postgres never had, and that's amazing. We wouldn't have pgvector, for example, right now, or PostGIS or any of those other huge extensions everyone relies on now, without it. But at the same time, the Postgres extension mechanisms, and I now know this first-hand 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 whatever 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
to browse 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. Nik: This would be great to fix, by the way. Do you know anyone looking in this direction?
Shaun: 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 they, 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. Nik: And the core just experienced the biggest, in terms of CVEs
fixed, the biggest minor release, 28. Shaun: Oh, the 28 of the 28 CVEs they fixed in 19, yeah. Nik: 18.6, yeah. Shaun: Yeah, right, 18.6. And then 18.5 never came out because it got reverted. Nik: Which happened last time, it happened in 2008, yeah. Shaun: It's been a while. Nik: Yeah, Yeah, obviously. But exactly this question triggers the question about will the next minor release have even more CVEs? And this triggers the next question, what about extensions we
have on managed providers? Or not managed providers, just if we'll stay close to focus. Shaun: Not only will the next 1 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, we should probably take a look at this now and there's gonna be more eyes on it, but because of AI. You said you didn't want, we weren't gonna focus on that, but if we're being 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 see that, we're going to see it accelerate. Michael: Is it infinite? I think it's finite, isn't it? Nik: There should be a plateau at some point. Michael: The number might be high, and I have no idea if 28 is
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? Shaun: 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 fuzzers, 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 CVEs 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 it's 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 PostGIS, for example. And he escalated that all the way to a root RCE where he could arbitrarily affect files on the file system from PostGIS. And that's just part 1 of 5.
And he's actually 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 and their dog has an AI that can be like oh I'll just attack this 24 hours a day 7 days a week until I find something. Nik: We should say thank you to Anthropic and OpenAI for limiting
capabilities. You cannot do it with their latest models right now. They will like completely say I'm not continuing. Shaun: Yeah, those models, but anyone who has a modern Qwen or K3 or any of the local models that's been put through a LoRA 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. Nik: That's interesting, because I saw Anthropic was mentioned a few times in the release notes for those minor releases, but
I don't remember any other AI systems mentioned. Shaun: 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. Nik: Yeah, yeah, Shaun: yeah. We're getting a little bit off the topic, but the reason extensions are at this point and Postgres, and actually really any software now, is because of AI, for good or ill. Nik: Yeah, maybe let's connect topics so we could use AI to tune work_mem.
Shaun: Yeah, I actually used it to find all the bugs in my extension because my extension was a proof of concept, right? It's just, here, you can do this. It's kind of fun, throwaway 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 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 backend. And it found dozens of things I could fix, were I so inclined. So anyone can do that to their extension.
And it's a huge help. Nik: And should do. Shaun: Should, yes, also. Nik: At this point, already, yeah. Shaun: Because the amount of eyes, like, the thing is, even in our company, we don't have my pgEdge Ansible project that I work on for doing distributions of architectures, there's like maybe 2 other people in the company that can help me do code review on that. But I always have Claude available to look over things or CodeRabbit or whatever tool you want to use and they'll catch stuff that I didn't consider. Michael: I had a question on things you considered.
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 1 times work_mem, like it was every node type it seemed. Shaun: 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 grepped 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, but 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. Michael: 1 more question was Back to what you said at the start around using max_connections as a multiplier I wondered it wouldn't be the same formula, but I wondered if instead it might be sensible to use some multiple of the number of cores 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 4 or 5 times work_mem, But obviously that's a much lower number in most cases, so it'd be a very different formula. But I wondered if there was any merit to that maybe in formulas. Shaun: 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. Michael: 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 1st few might get all of the workers they want, but the next ones are only going to get a single 1. Like, it won't let you run more than. Shaun: Yeah, yeah, that would act as a cap. Michael: Have you ever done anything like that, Nik? Nik: Yeah, I barely understand what you're saying, Michael. Shaun: I think he was asking if you had used the number of cores
as part of your estimate for work_mem. 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. Nik: Right, not vCPU count, max_parallel_workers. Postgres has no idea how many cores or how much. Shaun: Right, but which is why you set the max_parallel_workers and various other settings so you wouldn't exceed it. Nik: Why? Like we can exceed it, we can exceed it. Shaun: You can, you can, you can.
I wouldn't recommend it. Nik: I agree with you But looking at guys who come to us like 16 or 32 cores and max_connections 5000, I already have a shift in my mind. I cannot convince them. We spent a lot of efforts saying this max_connections is abnormal. Especially before Postgres 13, 14 when Andres Freund improved work with snapshots, which is very related to memory consumption, right?
Yeah, we tried like max_connections should be like 3, 4 times more than vCPU count. Shaun: That's what everyone says, but... Nik: No, not anymore. We don't say it anymore because since Postgres 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. Shaun: 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. Nik: Right, so this is just reality.
These guys come to us with RDS and telling them that you need to reduce max_connections drastically, you need restart for it. And if they don't have proper database side pooler, we cannot convince them to get rid of huge amount of idle connections. They just need them to satisfy application guys needs because those guys, as I said, they scale their application nodes like this, especially e-commerce when Black Friday happens, they just
need to scale. Or some new system as well, like social media, they need to be able to scale and they need those idle connections. So this, back to work_mem here, I don't know, it depends. Also, sometimes we need to reproduce plans in an environment which is much weaker physically than production, because we study behavior of Postgres. And we are okay for some contention happening in terms of physical resources, But we want the planner to behave exactly like in
production. So there are some nuances. But I agree with you. Overall, I would like to see average number of sessions below vCPU count. This is great. Shaun: Yeah, that's usually that's what I still tell people. Not because necessarily they're going to see a huge drop in performance because it's let if we let's face 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 Michael: yeah I'm going Maybe going back to basics a little bit,
when you're tuning work_mem, 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? Are we trying to increase headroom there a little bit? Or is it mostly just user-facing query times? Shaun: I guess it depends on 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 disk 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 your maximum is higher for no reason, and you end up getting more risk of an out-of-memory error. Really, it's just 1 of those things like Nik 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 pg_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-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 1st place, and sum 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 ideal work_mem based on your amount of average active backends and whatnot. But it's just 1 of those things with Postgres. You have to always go back and retroactively examine how your system's been 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 what, 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 of memory or increase it if it's not. It's a delicate balancing act and unfortunately there's no 1 size fits all way to addressing everything, which is why there's so many guides, there's blogs and tutorials and videos and everything
galore of how to do it, and no 1 really can agree on 1 final answer. Nik: There is no official runbook how-to documentation. This is sad actually, but I can imagine how hard it would be to achieve consensus on the concrete protocol. Actually, we somehow avoided the topic of swap as well. We could enable swap, right? This is like instead of temporary file for each query, let's enable on that far end. If we achieve that, let's swap there, 1 thing. And another thing, like I just resonate very, like a lot with
your words about, for example, maintenance_work_mem, which usually is inherited by autovacuum_work_mem, being minus 1, right? And then we tell everyone we should have more workers for autovacuum workers. And then Postgres 17 silently, like unexpectedly, lifts unspoken limit 1 gigabyte. It was not like obvious that we actually were limited, but guys already raised maintenance_work_mem to say 8 gigabytes and raise number of workers to say 25.
And now we have interesting memory allocation for autovacuum, which we didn't want. So now we say raise the number of autovacuum workers, but also limit autovacuum_work_mem by 1 gigabyte, because when you will upgrade to 17, so Layers of logic, yeah. Shaun: I totally forgot about maintenance_work_mem and autovacuum_work_mem because those, you don't really think about those because the 1 that really bites everyone is work_mem because they set it to some value and then it explodes on them. But yeah, the other 2 definitely are a factor. Nik: Swap, like swap, like I always try to avoid swap on Postgres
machines, I remember, but it's very old. I haven't revisited this topic because I remember dealing with database, Postgres database, which experiences heavy swap. Since then, I always avoided it. But then I remember Bruce Momjian 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 Postgres? Shaun: 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 1, because if you set it too low or to 0, the memory pressure systems go wonky. And then I set to some low amount, like 2 gigs, 4 gigs, some token amount just so the kernel has area to work with. Then I set overcommit_memory to 2 so you can't overcommit.
And then I set overcommit_kbytes 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 overcommit 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 your backend will 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 OOM killer decided that there's a rogue 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 enforced the same way. Nik: Right, and there is 1 more point. We somehow also avoided an important topic.
We could plan everything very well, shared_buffers, maintenance_work_mem for index creation, autovacuum workers, then our backends with work_mem, But somehow we forget about page cache. And in many systems, page cache is super important. Sometimes, like if it's, for example, if we had a situation when it was exceeding shared_buffers, for example, a simple example, we perform minor upgrade, we restart 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 an internal discussion about that. Do we need to have a pre-warming, pg_prewarm, 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 work_mem, 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.
Shaun: That definitely directs how you will want to choose your hardware because 1 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 that 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, suddenly our shared_buffers have 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 of an old RAID array or something. So our fix was to get a Fusion-io drive, which would be equivalent to a 100, 000 IOPS device from EBS or something, like an io2, or just a really high-end NVMe M.2 stick or something. Nik: Local NVMe, yeah. It's great. Shaun: It was really the only way out at the time because you'd see the spike of activity and the I/O usage from iostat, 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 would spike once in the beginning, as soon as the crash was over, and then it would just hover around 20%. But that 20% was on a 100, 000 IOPS 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 0.25 of your RAM, well, not needing, we recommend using 0.25 of your RAM up to a certain limit.
If your page cache is too big, then you're essentially double buffering. Nik: Yeah, and you have much higher likelihood that temporary files spilling to disk will happen, right? So this is the whole point to avoid. This, the case you just described exactly like what I'm saying about, and Having faster disks definitely helps. These days we can have millions of IOPS with local NVMEs, right? But I also think if we tune work_mem, so we make page cache quite
thin, and we need to think about how fast or slow disks are. And if we know they are slow, then maybe we should consider automatic pre-warming. Because pg_prewarm right now supports automated pre-warming, so it can capture maybe this is the exact moment, slow disks and small page cache and which should start. This actually adds to the whole picture of tuning work_mem, right? Shaun: Yeah, and back when this happened, pg_prewarm wasn't really a thing. So I cheated by just like using dd.
So I queried 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 pg_prewarm. It's an 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 io2 fees or load
up your system with expensive storage, then pre-warming is still an option. Nik: I agree with you and like bigger databases with local NVMes, it's like 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_mem, so queries are good enough in terms of performance, but also you know that restart will help you survive not being super slow for 0.5 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.
Shaun: Well, 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 instances of databases, and those are entirely cold. Without proper backing on them, they're going to be slow for a while, at least after start. In the Nik: case of DBLab, it's ZFS, and ZFS has ARC. We usually allocate 0.5 of memory to it, so Those blocks 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 the worst case. And this is tricky in this architecture because it's a multi-tenant thing. A lot of Postgres exploration happening on the same VM and we need that cache actually. Shaun: Yeah, how would you even do that? You'd have to move it over to another instance, and that's cold. Nik: Yeah, you cannot do it without losing the common cache.
But common cache helps others, 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 work_mem 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 a very interesting discussion. 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. Shaun: Yeah, I don't plan to. And I always like to remind people that if you've ever heard of Perl as the pathologically eclectic rubbish lister, that's basically how my brain works. Nik: Okay, okay.
Great, yeah. Michael: Well, really nice to meet you. Thanks for joining us. Nik: Thank you. Shaun: You too. Hope you have a good day. Nik: You have a great week.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...