RE: huge memory of Postgresql backend process

Hi,
  We plan to convert one large partition table to multiple non-partitioned tables, for example , one 256 HASH partition
tableto 256 small tables. Then application will query small tables directly to get quick sql response time.  Does that
helpreduce backend process memory for "metadata", I can expect to reduce partition related cache of backend process,
butthe  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