RE: huge memory of Postgresql backend process

When rebuild from partitioned to non-partitioned tables with same data size, same test load only use 24-25MB/per backend server. From auto_explain that show both DELETE and UPDATE use partition pruning , so the sql running very fast. But only see process memory increased quickly , with hundreds of connections to do similar "update/delete" on multiple partition tables
test, server physical memory got used up and a lot of "out of memory" error dumped to pglog, but from dumped memory context stats , we only saw tens of MB memory used, instead of 160mb.
Looks like Postgresql backend server try to allocate one large memory suddenly based on some estimation rule when update/delete from partition tables, and actually not used so much memory. we found that never free back to Operating system after the backend process idle long time. From OS pmap command, almost of memory " xxxxx rw--- [ anon ]". Maybe it's an overestimated memory allocation from OS than it's real usage ?

Thanks,

James

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, September 8, 2022 9:48 PM
To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
Cc: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: huge memory of Postgresql backend process

"James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> writes:
> We just switch from Oracle to PGV13, all of these tables got partition (256 HASH partition) , when UPDATE/DELETE ... WHERE . The memory increase verify quickly until >130M. not too much data in these tables. When we switch to PGV14.5 , test again, still see similar memory consumption issue.

I see no particular reason to think this is a bug. If you have a lot of partitions, there is going to be a lot of metadata for them.
Moreover, HASH partitioning is effectively incapable of being pruned, so that every query is going to touch every partition.
(IMO, hash partitioning is basically never a good idea.)

regards, tom lane