Re: Failing to allocate memory when I think it shouldn't

## Siraj G (tosiraj(dot)g(at)gmail(dot)com):

> I am getting the same error in postgres 12 (sorry that our version upgrade
> sucks).

In all likelyhood, this is a somewhat different situation, as nothing
here points to JIT.

> I see that hash_mem_multiplier is available from version 13. What
> could we do in version 12?

Obviously, you could upgrade - you already identified the change which
could at least improve matters here (when you do that, don't just stop
at version 13: that is going EOL in a few months, too; and the whole
hash memory allocation got some more polishing in 15).

Also, as you got a standard out-of-memory error and not the dreaded
OOM-kill, I assume that you set vm.overcommit_memory to a non-default
(that is, != 0) value, but you did not mention anything about setting
overcommit_ratio or overcommit_bytes - see upthread for a link to the
documentation. Make sure that you have reasonable settings here.

Thirdly, you should check whether you memory settings are actually
suitable for your workload and machine, considering concurrent
operations and their memory usage against actually available memory.

Then check if that specific hash join grows beyond reasonable size
(can you even execute the statement with the given parameters on
an idle system?), and if it does investigate why the hash map is
so much larger than estimated. You might have a statistics problem.
You could try to reduce work_mem to steer the planner away from
that hash join to a less memory-intensive strategy, or you could
temporarily disable hash joins all together and analyse the impact
on your application.

Regards,
Christoph

--
Spare Space