Debugging high PostgreSQL memory usage (per connection)

Question

Is there any way to check how memory assigned to each connection is actually used?

After upgrading from PostgreSQL 9.3 to PG 12 the memory usage for each PostgreSQL connection doubled or even tripled. So I had to go from 32GB machine with: shared_buffers = 8GB memory used for connections = 8GB memory for disk buffers = 16GB To: 64GB machine with: shared_buffers = 8GB memory used for connections = 40GB memory for disk buffers = 16GB

And it is still not enough. It is not uncommon for single connection to reach use 170MB of private ram (Private in smaps, as described in https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ ), not shared with other processes.

What can be a cause for such high memory usage? As far I can tell it is persistent - memory isn't released until connection is closed. As I am using connection pools managed by Wildfly they are reused and it is rare for them to be closed and recreated.

Here is my datasource definition:

<datasource jta="true" jndi-name="java:/MainDS" pool-name="MainDCPool">
    <connection-url>jdbc:postgresql://dbhost/</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <driver>postgres</driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>30</max-pool-size>
    </pool>
    <security>
        <user-name>dbuser</user-name>
        <password>password</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
        <validate-on-match>true</validate-on-match>
        <background-validation>false</background-validation>
    </validation>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
    <timeout>
        <idle-timeout-minutes>1</idle-timeout-minutes>
    </timeout>
</datasource>

As far I call tell setting 'idle-timeout-minutes=1' and min-pool-size=1 didn't have much if any impact. It looks like WildFly selects random connection from pool (when requested by application), so it is unlikely that any of them stays idle for extended period of time and pool size won't ever drop below ~20 connections

Answer 1

It turned out the reason was histogram (pg_stats). It is stored in memory per each connection, and in database with huge amount of partitions it causes significant overhead. Columns that contain large/long values have significant impact. Lowering STATISTICS attribute on 4 columns of single table and its partitions cut the memory usage (per connection) by 35%. Upgrade itself wasn't at fault, but - as part of upgrade procedure - whole database was ANALYZEd, appling new default_statistics_target value to all tables.

Checking histogram size: Finding the histogram size for a given table PostgreSQL