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.