Thanks for you suggestion, yes, we reduced partition count from 256 to 128, then to 64. The memory got reduced from
170Mto 91MB then to 60M.
It's from Postgresql JDBC client, preparedStatementCacheQueries=(default 256), reduce that from JDBC parameter can
helpreduce "cached plan context" ?
Thanks,
James
-----Original Message-----
From: David Rowley <dgrowleyml@gmail.com>
Sent: Friday, September 9, 2022 5:35 AM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: huge memory of Postgresql backend process
On Fri, 9 Sept 2022 at 02:08, James Pang (chaolpan) <chaolpan@cisco.com> wrote:
> 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 ?
I see you've been asking this question in various places around the mailing lists.
It seems what you're complaining about is mentioned in our documents under [1]. Namely:
"Another reason to be concerned about having a large number of partitions is that the server's memory consumption may
growsignificantly over time, especially if many sessions touch large numbers of partitions. That's because each
partitionrequires its metadata to be loaded into the local memory of each session that touches it."
What that section of the documents does not clearly mention is that this "metadata" is kept for the life of the backend
process.This is probably implied by the "significantly over time", but maybe we could have been more clear that we
don'tfree the relcache entries.
James, if you're having OOM problems due to this then the solution is to reduce the number of partitions in the
partitionedtable, or change things so that you don't query as many partitions from as many backends. The number of
relcacheentries stored for partitions will be number_of_partitions_you_query_in_the_backend * the_number_of_backends.
Soyou'll either want to review the number of partitions or the number of backends (aka max_connections)
David
[1] https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES