When rebuild from partitioned to non-partitioned tables with same data size, same test load only use 24-25MB/per
backendserver. From auto_explain that show both DELETE and UPDATE use partition pruning , so the sql running very fast.
Butonly see process memory increased quickly , with hundreds of connections to do similar "update/delete" on multiple
partitiontables
test, server physical memory got used up and a lot of "out of memory" error dumped to pglog, but from dumped memory
contextstats , 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/deletefrom partition tables, and actually not used so much memory. we found that never free back to Operating
systemafter 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@sss.pgh.pa.us>
Sent: Thursday, September 8, 2022 9:48 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: huge memory of Postgresql backend process
"James Pang (chaolpan)" <chaolpan@cisco.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
metadatafor 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