Another good piece of advice from ! What do you think about our very naive attempt to improve on the bare default at github.com/citusdata/pg_a ?
Quote Tweet
Let's talk a bit more about setting work_mem in #PostgreSQL. 1/n
Show this thread
1
4
Conversation
Well the goal is to offer a better starting point than the bare default which doesn’t depend on the available memory, nothing more than that...
2
I think one big missing building block is a way to handle/detect out-of-memory cases with a smaller hammer than overcommit_memory=2. Which unfortunately is really hard to do in a cross-platform way :(. Right now we need to be ridiculously conservative setting work_mem...
3
1
Having a `max_work_mem` setting that worked globally across the system would be very useful.
1
1
Have you tried tuning hash_mem_multiplier from Postgres 13, so that hash-based nodes get more memory? It's ultimately just a band-aid, but I bet it would help a lot in some cases.
The core problem is that Postgres needs a memory admission controller, and work_mem and its ilk are a very poor substitute.
1
Don't think a memory admission controller would end up working convincingly, without first having a query-global work_mem aware planner. You'll just end up with a lot of blocking because there's no way to control qry memory usage.
Although I think thinks differently?
3
Show replies