Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

360
Views
Debugging high PostgreSQL memory usage (per connection)

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

over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

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

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!