Re: Enforce work_mem per worker

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Sent: Monday, November 29, 2021 16:10
> On Mon, Nov 29, 2021 at 02:01:35PM +0000, Arne Roland wrote:
> > But my main goal is something else. I can't explain my clients, why a chanced statistics due to autovacuum suddenly leads to oom. They would be right to question postgres qualification for any serious production system.
>
> What version postgres was that on ?

It's pg13 and pg14 mostly. I have different servers with similar problems.

> I realize it doesn't address your question, but since PG13, HashAggregate
> respects work_mem.

I haven't run into issues with hash agg personally.

> Depending on the details of the query plan, that's arguably
> a bigger problem than the absence of a global work_mem. At least that one is
> resolved.

I can go around to fix issues with plans. But plans are inherently unstable. And we can't have people becoming wary of autoanalyze.
Having a single wild plan bringing down a whole cluster is just madness.

There are bunch of different problems, that can occur. But where I stand this almost invalidates partition wise hash joins, because you'd generate one hash node per partition. But you can still have sorts with merge append, without partitionwise joins.
To quote your message from 2019:
> gracefully support[...ing] "thousands" of partitions
means using 1000 * work_mem?
Am I wrong here?

Regards
Arne