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

From: Siraj G <[email-masked]>
Date: Wed, 30 Jul 2025 19:27:11 +0530

Hello Christoph

I am getting the same error in postgres 12 (sorry that our version upgrade
sucks). I see that hash_mem_multiplier is available from version 13. What
could we do in version 12?

The error is:
      Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496
usedindex info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used
: pg  _ErrorContext: 8192 total in 1 blocks; 7936 free (4 chunks); 256 usedt
s_Grand total: 110141936 bytes in 2085 blocks; 2842528 free (149 chunks);
107299408 used
dict_oid_index
    index info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used:
pg_event_trigger_oid_index
    index info: 3072 total in 2 blocks; 1216 free (3 chunks); 1856 used:
pg_conversion_default_index
    index info: 3072 total in 2 blocks; 1136 free (2 chunks); 1936 used:
pg_operator_oprname_l_r_n_index
    index info: 2048 total in 2 blocks; 680 free (2 chunks); 1368 used:
pg_trigger_tgrelid_tgname_index
    index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used:
pg_enum_typid_label_index
    index info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used:
pg_ts_config_oid_index
    index info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used:
pg_user_mapping_oid_index
    index info: 2048 total in 2 blocks; 704 free (3 chunks); 1344 used:
pg_opfamily_am_name_nsp_index
    index info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used:
pg_foreign_table_relid_index
    index info: 2048 total in 2 blocks; 952 free (1 chunks); 1096 used:
pg_type_oid_index
    index info: 1024 total in 1 blocks; 48 free (0 chunks); 976 used:
pg_aggregate_fnoid_index
    47 more child contexts containing 80896 total in 78 blocks; 25784 free
(51 chunks); 55112 used
  PrivateRefCount: 8192 total in 1 blocks; 2624 free (0 chunks); 5568 used
  MdSmgr: 8192 total in 1 blocks; 5528 free (0 chunks); 2664 used
  LOCALLOCK hash: 16384 total in 2 blocks; 4600 free (2 chunks); 11784 used
  Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496 used
  ErrorContext: 8192 total in 1 blocks; 7936 free (4 chunks); 256 used
Grand total: 74714976 bytes in 1007 blocks; 2893968 free (151 chunks);
71821008 used
10.3.2.133,2025-07-30 19:03:04
IST,431246,orchids_letseduvate_db,autoscaling,1,ERROR:  out of memory
10.3.2.133,2025-07-30 19:03:04
IST,431246,orchids_letseduvate_db,autoscaling,2,DETAIL:  Failed on request
of size 32800 in memory context "HashBatchContext".

We have these memory settings:
work_mem=2GB
maintenance_work_mem=2GB
shared_buffers=48GB
max_parallel_workers=8

This issue is happening in the REPLICA instance.

Regards
Siraj

On Wed, Sep 18, 2024 at 12:35 AM Christoph Moench-Tegeder <
[email-masked]> wrote:

> Hi,
>
> ## Thomas Ziegler ([email-masked]):
>
> > Except for pgAudit, I don't have any extensions, so it is probably the
> > JIT. I had no idea there was a JIT, even it should have been obvious.
> > Thanks for pointing this out!
>
> There is - it even has it's own chapter in the documentation:
> https://www.postgresql.org/docs/current/jit.html
> Most importantly, you can disable JIT per session ("SET jit=off")
> or globally in the configuration file (jit=off, reload is
> sufficient) or with any of the other usual configuration mechanisms.
> If that fixes your problem, congratulations (and the problem is
> somewhere down between bytecode generation and what and how llvm
> (in its particular version) generates from that).
>
> > Is the memory the JIT takes limited by 'work_mem' or will it just take
> > as much memory as it needs?
>
> The latter.
>
> Regards,
> Christoph
>
> --
> Spare Space
>
>
>

---

From: Christoph Moench-Tegeder <[email-masked]>
Date: Fri, 1 Aug 2025 20:35:26 +0200

## Siraj G ([email-masked]):

> 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

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