On Fri, Apr 2, 2021 at 04:59:16PM +0200, Laurenz Albe wrote: > On Fri, 2021-04-02 at 13:31 +0000, Campbell, Lance wrote: > > It feels like there needs to be work_mem and work_mem_stack_size. When work memory is > > needed a process “pops” a token off of a stack. When it is done processing it “puts” > > the token back on the stack. If the stack is empty then don’t allocate memory just > > write to disk for work_mem. > > > > This does two key things: > > > > 1) It allows for a real world understanding of how much memory is really needed on a > > day to day basis. You can track how often a stack is empty. You can also look at the > > number of temp files to see when work exceeds the work_mem allocation. There is no > > “art” to setting these values. You can use logical analysis to make choices. > > > > 2) This also prevents out of memory issues. You are protecting yourself from extreme loads. > > If I get you right, you want another memory limit per session. > > I see the point, but then we wouldn't need "work_mem" any more, right? > What is the point of limiting the memory per plan node if we have an > overall limit? > > In practice, I have never had trouble with "work_mem". I usually follow > my rule of thumb: max_connections * work_mem + shared_buffers < RAM > > While some backend may need more, many will need less. Only bitmaps, hashes > and sorts are memory hungry. This blog entry discusses how work_mem might be improved: https://momjian.us/main/blogs/pgblog/2018.html#December_10_2018 -- Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
