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.