Re: huge memory of Postgresql backend process

On Sat, 10 Sept 2022 at 22:53, James Pang (chaolpan) <chaolpan(at)cisco(dot)com> wrote:
> With 256 hash partition tables, we got huge backend memory increase with JDBC driver client prepared statements, even when reduce partition count from 256 to 64, 27 partition tables insert/update/delete sql still consumes 60mb. We plan to convert one large partition table to multiple non-partitioned tables, for example , one 256 HASH partition table to 256 small tables. Then application will query small tables directly to get quick sql response time. Does that help reduce backend process memory for "metadata", I can expect to reduce partition related cache of backend process, but the table/index count is same.

There are two things to consider here. The "metadata", aka relcache is
something that's stored for every table or partition you access from a
backend. When you're using prepared statements, you're also storing
query plans for pre-prepared queries in the backend memory too.
Plans, especially UPDATE/DELETE plans to partitioned tables tend to be
larger than plans for non-partitioned tables, so the plans to
partitioned tables will consume more memory. If you start to query
non-partitioned tables then these plans are likely to get smaller,
therefore your backend is likely to consume less memory.

You may also want to look at [1]. In particular:

"Server-prepared statements consume memory both on the client and the
server, so pgJDBC limits the number of server-prepared statements per
connection. It can be configured via preparedStatementCacheQueries
(default 256 , the number of queries known to pgJDBC), and
preparedStatementCacheSizeMiB (default 5 , that is the client side
cache size in megabytes per connection). Only a subset of statement
cache is server-prepared as some of the statements might fail to reach
prepareThreshold."

It's likely if you're querying individual partitions then you'll hit
that 256 query limit more quickly since you'll have more unique
queries that you're running against the backend. You might just want
to consider passing in some value less than 256 and still query
partitioned tables instead.

David

[1] https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements