Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Thu, Jan 2, 2025 at 7:21 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>
> I'm not opposed to having a some sort of "workload management" (similar
> to what's available in some databases), but my guess is that's (at
> least) an order of magnitude more complex than introducing the memory
> limit discussed here. I can only guess, because no one really explained
> what would it include, how would it be implemented. Which makes it easy
> to dream about a solution that would fix the problem ...

I agree -- anyway, I will try to start a new thread sometime next
week, with a more concrete proposal. (I wanted to get general feedback
first, and I got a lot of it -- thanks!)

> What I'm afraid will happen everyone mostly agrees a comprehensive
> workload management system would be better than a memory limit (be it
> per-backend or a global one). But without a workable proposal how to
> implement it no one ends up working on it. And no one gets to work on a
> memory limit because the imagined workload management would be better.
> So we get nothing ...

That seems to be where this thread is heading...

> FWIW I have a hard time imagining a workload management system without
> some sort of a memory limit.

Yes, and more strongly; you can't create a system to manage resources,
unless you first have some way of managing those resources. Today, I
can't say: query X gets 50 MB of RAM, while query Y gets 200 MB of
RAM, even if I wanted to -- at least, not in any useful way that
doesn't involve waiting for the query to exceed its (hidden!) memory
limit, and then killing it.

Before we can discuss how much memory queries X and Y should get, and
whether X can steal memory for Y, etc. -- we need a way to force X and
Y to respect the memory limits we impose! Otherwise, we always end up
back at: I have a secret memory limit for X, and if X exceeds that
memory limit, I'll kill X. I think there's been some rationalization
that "just kill X" is a reasonable response, but I think it really
isn't. Any workload management system whose only available tool is
killing queries is going to be incredibly sensitive / unstable.

It would be better if, when someone -- either workload management, or
a GUC -- decides that query X gets 50 MB of memory, we informed query
X of this limit, and let the query do its best to stay within it. (Yes
it makes sense to have the option to "kill -9", but there's a reason
we have other signals as well...)

The good news is, PostgreSQL operators already try to stay within
work_mem [* hash_mem_multiplier], so the problem of how to get query X
to stay within 50 MB of RAM breaks down into splitting that 50 MB into
per-operator "work_mem" limits, which is (as you point out!) at least
an order of magnitude easier than a general workload management
solution. Once we have per-operator "work_mem" limits, existing
PostgreSQL logic takes care of the rest.

Thanks,
James