Postgres connection growing memory usage over time! This right after the connections in the pool are closed and opened again.

Hi,

We use postgres in a multitude of systems for various solutions that we
offer.

We have been using the native JSON, JSONB Features in postgres more and
more in the past year.

Recently, in a rewrite of one of our key applications we noticed a behavior
whe JSON Payloads and issues with Out of Memory issues and heavy swap
usage.

What we have discovered is if you leave a connection open and reuse that
connection while resulting in JSON connection memory usage skyrockets over
time.

We use connections from a pool manager of sorts. The tooling we use allows
us to create a connection and cache a pointer to it for reference.

When we leave a connection open for regular row results we never see this
issue, but if we result in JSON this occurs in all situations.

This is the configuration of one system that I have been working on to
isolate this problem and attempt to resolve it.

PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
> 8 CPU
> 40 GiB Memory
> 20 MB Work Memory
> 1 GB Maint Work Memory
> 3 GB Shared Buffers.

Live connections 31

Max Connections 100

This is the query that we are running that creates the issue.

<SQLText>
> Select json_agg(row_to_json(t))::text from ( Select id , auditpkey
> from auditrepo.atnaexport
> where 1 = 1
> and (processed = false or processed is null)
> order by id asc limit 100) t
> </SQLText>
> var result = dbConn1.executeCachedQuery(sql.toString());
>

Those two values are BigInt, ID is a Primary Key for the table and
auditpkey is a primary key from another table.

This graph shows what happens over time when the connection is left alive
for the read of JSON, the end of this graph at 1:00 shows a change
that I implemented to close a connection after 1000 statements are
processed to the DB. In this use case there are 8 connections in the pool.

These 8 connections make approximately 8,000 transactions at the DB in 14
minutes or so.
[image: Screen Shot 2023-01-09 at 3.43.53 PM.png]

The JSON query above was isolated to a single connection to trace the issue
better.

You can see in the image below that there is one connection that is growing
memory usage over time and the rest are not.
[image: Screen Shot 2023-01-09 at 4.22.48 PM.png]
This image shows the memory growing on that single connection that the JSON
is processed and the other connections that the memory used is stable and
not growing. This is just before the connections are closed and opened
again in the pool.
[image: Screen Shot 2023-01-09 at 4.25.00 PM.png]

We are using:
Java: Eclipse Adoptium, OpenJDK Runtime Environment, 11.0.14.1+1
JDBC postgresql-42.2.19

Thank you,

Ron Wilson
Vice President of Engineering Services
Zen Healthcare IT
Phone: 949.396.1295 Ext. 124 <949.396.1295,,124#>
Direct: 361.881.4967

Click HERE to call me right now.
<https://service.ringcentral.com/ringme/ringme.asp?uc=64AE254A5A220509EBBEF15E9DC56EB035582736217012,0,124,1,0&s=no&v=2&s_=1210>

www.consultzen.com

Zen’s Gemini Integration-as-a-Service Platform
<https://mailtrack.io/trace/link/8629a8603a487386929eff821e7579e2de19f260?w=cm9ud0Bjb25zdWx0emVuLmNvbQ==&url=https%3A%2F%2Fconsultzen.com%2Fgemini-integration-platform%2F&userId=6917954&signature=6c365ad39ec553e2>
is now officially HITRUST CSF r2 Certified! Zen Healthcare IT is incredibly
proud to announce that our Gemini Integration-as-a-Service Platform and HIE
3rd Party hosting services are HITRUST CSF Risk-based, 2-year certified!
This includes Gemini modules such as our Stargate IHE Gateway
<https://mailtrack.io/trace/link/4c253911bc089ecad156960b79b6c28ef92e7b2c?w=cm9ud0Bjb25zdWx0emVuLmNvbQ==&url=https%3A%2F%2Fconsultzen.com%2Fstargate-ihe-gateway%2F&userId=6917954&signature=320b594f5ca38cc6>
and Gravity FHIR Aggregator
<https://mailtrack.io/trace/link/e98bb465a952610c9b0d1ccbfcd17f15e8758b66?w=cm9ud0Bjb25zdWx0emVuLmNvbQ==&url=https%3A%2F%2Fconsultzen.com%2Fgravity-fhir-data-repository%2F&userId=6917954&signature=72c9e2755d8869d3>.
For the fastest path to interoperability without sacrificing security – contact
the Zen Team
<https://mailtrack.io/trace/link/9489c15320f9c225d8f39868e4c0885355450974?w=cm9ud0Bjb25zdWx0emVuLmNvbQ==&url=https%3A%2F%2Fconsultzen.com%2Ffree-interoperability-expert-consult%2F&userId=6917954&signature=3f82d2eefe298035>.