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) because the planner
can't use partition pruning.  One workaround might be to forcefully
prevent plan caching by setting plan_cache_mode to
'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