Hi,
With 256 hash partition tables, we got huge backend memory increase with JDBC driver client prepared statements, even
whenreduce partition count from 256 to 64, 27 partition tables insert/update/delete sql still consumes 60mb. We plan
toconvert one large partition table to multiple non-partitioned tables, for example , one 256 HASH partition table to
256small tables. Then application will query small tables directly to get quick sql response time. Does that help
reducebackend process memory for "metadata", I can expect to reduce partition related cache of backend process, but the
table/index count is same.
Thanks,
James
-----Original Message-----
From: Amit Langote <amitlangote09@gmail.com>
Sent: Friday, September 9, 2022 4:42 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: David Rowley <dgrowleyml@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists
<pgsql-bugs@lists.postgresql.org>
Subject: Re: huge memory of Postgresql backend process
Hi,
On Fri, Sep 9, 2022 at 5:19 PM James Pang (chaolpan) <chaolpan@cisco.com> wrote:
> 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" ?
Interesting that you mention "cached plan context".
Cached plans for queries over partitioned tables tend to be big (in terms of memory they take up in the plan cache)
becausethe planner can't use partition pruning. One workaround might be to forcefully prevent plan caching by setting
plan_cache_modeto 'force_custom_plan', which makes a backend rebuild the plan on every execution of a prepared
statement.
--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com