On Thu, Jan 2, 2025 at 7:21 PM Tomas Vondra <to...@vondra.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
