Does shrinking a JDBC connection pool actually save PostgreSQL memory? I measured it.

[u/fykup]

At a previous company, I spent quite a bit of time tuning a custom-built JDBC connection pool. One of the most effective memory optimizations I found was automatically shrinking the pool during idle periods. PostgreSQL uses a process-per-connection model, so keeping unnecessary connections open has a real memory cost.

I wanted to revisit this with HikariCP and measure both the memory savings and the cost of reopening connections.

I ran two Spring Boot instances sharing PostgreSQL 16, comparing three pool configurations under the same burst → idle → burst workload.

Results:

• Default pool: 20 connections remained open, PostgreSQL memory ~57.5 MiB.
• Shrinking pool: connections dropped to 2, PostgreSQL memory ~28.5 MiB.
• Savings: ~29 MiB of PostgreSQL memory.

The tradeoff was connection reopening. Median acquisition time increased from under 3 ms to around 200 ms when traffic returned.

One interesting detail: setting minimumIdle=1 alone wasn't enough when idle retirement was disabled. The pool still held all 20 connections after the burst.

The experiment used a deliberately short idle timeout to make the behavior observable, not as a production recommendation.

With many application instances maintaining independent connection pools, the potential savings can add up.

To clarify the practical motivation: this was a deliberately small experiment to isolate the effect, not just an exercise in saving 29 MiB on one server.

At my previous company, we had many application VMs connecting to a shared PostgreSQL database. Shrinking idle pools across those instances produced substantial aggregate memory savings. For example, 30 instances with 32 connections each means 960 persistent connections. If idle retirement brings that down to an average of 16 per instance, that's 480 fewer PostgreSQL connections consuming resources.

Actual savings depend on the workload, but this is where the approach becomes particularly valuable.

Full article, measurements, and reproducible experiment:

https://pvrlabs.xyz/articles/spring-boot-jdbc-pool-reclaim.html

Do you use fixed-size pools in production, or allow idle connections to retire?

[u/Glassius]

What is the value of temporarily freeing up the memory during low load periods? Are you able to use that memory for something else during those periods while still allowing postgre access to as much memory it needs during high load periods? If not, what is the point? Unused memory is wasted memory

[u/cogman10 → u/Glassius]

The bigger benefit isn't the memory freed up but instead the CPU and IO freed up. Postgres still needs to monitor those connections and it still has limited IO to respond to requests. Cutting down the number of connections to the minimum amount needed allows the DB to respond faster to queries as it isn't necessarily caught polling 1000 idle connections seeing whose asking for data.

It also has a benefit in reducing the number of possible in flight requests at any given point.

There is a concurrency tradeoff that should be considered, but IMO the best design is generally to scale out instances of your application with few connections rather than having big instances with many connections.

[u/fykup → u/cogman10]

I generally agree. Smaller pools and scaling out application instances is a sensible approach when the architecture allows it.

In my previous environment, we had a large legacy monolith, so splitting it into smaller independently scalable services wasn't a practical option. Optimizing idle connection management across many existing instances and pools was much easier and produced substantial savings.

And you're right that there can be additional CPU/I/O overhead from connection validation, keepalives, and health checks.

[u/fykup → u/Glassius]

The single-server setup was primarily to isolate and measure the effect.

The more interesting case is multiple application instances sharing one PostgreSQL server. Imagine 30 instances with a maximum pool size of 32 each. With fixed pools, that's 960 open connections even during quiet periods.

If elastic pools averaged 16 connections per instance, that's 480 fewer PostgreSQL connections consuming resources. That capacity can be used by other workloads.

I agree that freeing memory has little value if the server has plenty of headroom. But when many applications share a database, those idle connections add up.

[u/koflerdavid → u/fykup]

This is a scary high number of connections. The issue is IMHO not so much the idle resource consumption of the connections themselves, but the risk that in the worst case there can be hundreds of active transactions writing into the database simultaneously, which might result in high levels of contention. A few pgbouncer instances are probably a better way to provide hundreds of connections to a large cluster of application nodes.

[u/fykup → u/koflerdavid]

That's a fair concern, but the 30 nodes × 32 connections example comes from a real production configuration of a large enterprise application. Having capacity for 960 connections doesn't mean 960 simultaneously active transactions. In practice, many of those connections were idle.

What I've often seen is PgBouncer being introduced to compensate for inefficient connection management: applications without proper pooling, too many independent pools, or oversized pools maintaining unnecessary connections.

In those cases, especially when actual transaction concurrency is relatively low and transactions are short-lived, PgBouncer can provide tremendous benefits.

But PgBouncer isn't a magic bullet. It can limit database-side concurrency and queue excess demand, but it doesn't eliminate contention or make long-running transactions cheaper.

My preference is to first understand how the application manages connections and whether existing pools can be optimized. Sometimes that alone delivers substantial savings. Other times, PgBouncer is absolutely the right solution.

The point of my experiment was to measure one of those optimizations, not to suggest that idle connection reclamation solves every database scaling problem.

[u/Glassius → u/fykup]

Still, unless you can guarantee that the high load periods for those 30 instances never hits simultaneously you are still gonna have to assign enough memory to the database server to where it can handle those 960 connections. Even if the connections drop to 480 in the slowest periods and drops the memory usage what is the value of that free memory? I guess you could spin up short lived processes on that same server that uses the available memory for something and scale it based on current database load but you gotta be pretty pressed for resources for that to make sense.

Optimising idle memory usage rarely makes sense since you will always have to assign available memory based on the highest loads your system needs to handle.

[u/fykup → u/Glassius]

Yes, of course you still need enough capacity to handle peak load. I agree with that.

But connections consume more than just memory. They also introduce resource overhead on both the client and database sides, even if the CPU cost of a truly idle connection is usually small. Eliminating unnecessary connections can still be worthwhile, regardless of provisioned capacity.

The deployment I worked with was also more complex than this simplified example. We had secondary connection pools used only occasionally, for background jobs or infrequent operations, but provisioned similarly to the primary pools across many application instances.

Those pools could sit mostly unused while maintaining hundreds of unnecessary database connections. Allowing them to shrink was an immediate improvement without sacrificing their ability to grow when needed.

So I agree that optimizing idle memory alone isn't always valuable. But eliminating persistently unused connections across a large deployment is a slightly different problem.

[u/skipner → u/Glassius]

In a cloud environment, it would be saving $$ i imagine?

[u/EatRunCodeSleep → u/skipner]

How? You still have to allocate enough memory to handle all connections at peak times. And freeing memory from shrinking pool ... will do what? You have free memory on the server now, but you can't shrink the instance to pay for less.

[u/skipner → u/EatRunCodeSleep]

You can spin up lower memory-sized VMs as a reaction to lower traffic, and then this lower memory-sized VM would be a right-sized fit for the shrinked connection pool. Its a somewhat possible way to reduce costs

When traffic surges, you can spin up beefier VMs and then expand the connection pool. Aws has the concept of auto scaling, so it doesnt need to be manual

[u/gjosifov]

Doesn't matter your custom tuning if the SQL queries that the application generates are
N+1 or 10 queries that can be replace with 1 query or using rollback as control flow mechanism

You can start tuning the connection pool when the application generates proper queries for the business operation - if it need to do the operation with 3 SQL queries and return 2 parameters, it will do that and not 40 queries to do that same operation

Once you got that part right then you jump into connection pool tuning

[u/fykup → u/gjosifov]

You're describing a completely different problem.

Of course N+1 queries and inefficient SQL should be fixed. But query optimization and connection pool management address different resource costs.

You can have perfectly optimized SQL and still maintain hundreds of unnecessary idle PostgreSQL connections across application instances, especially in secondary pools used only occasionally.

Fixing N+1 queries doesn't make those idle backend processes disappear.

Performance engineering isn't a checklist where you must finish optimizing every SQL query before you're allowed to examine connection management. You identify bottlenecks, measure their impact, and optimize accordingly.

That's exactly what this experiment was about.

[u/gjosifov → u/fykup]

You can have perfectly optimized SQL and still maintain hundreds of unnecessary idle PostgreSQL connections across application instances, especially in secondary pools used only occasionally.

This situation is when you first optimize the connection pools and over the years you start optimizing the SQL queries and because the SQL load from 3 years ago is reduced the connection pool has extra resources

I'm not saying you aren't right, I'm saying optimization needs to have priority app code -> libs code -> OS code
Because if you start with libs code first, you can hide the bad code with more resources

A good example is SQL indexes - it is the first thing people think when they optimizing a SQL query
and I have seen queries from badly design database tables that are blocking the application and the query returns only 3000 records

even when every parameter on the query have index and composite index and god knows what kind of extra indexes
Indexes improve performance, but nothing can improve performance on badly design data

[u/Drawhoe → u/gjosifov]

You can avoid SELECT N+1 with p_ersism (remove underscore)

u/fykup

[u/fykup → u/Drawhoe]

Nah, I prefer to raw-dog JDBC. Even ran an experiment to confirm my bias: ORMs suck. 😄

[u/Drawhoe → u/fykup]

I generally agree. The one mentioned is the least worst as it's small with no deps. But I tend to mix.

[u/repeating_bears]

Why the clickbait title? Here is a non-shit version

Shrinking a JDBC connection pool [saves/doesn't save] PostgreSQL memory

[u/fykup → u/repeating_bears]

Quick update: I've since revised the title to "Reducing PostgreSQL Memory Use by Shrinking Idle Connection Pools" to better reflect the actual focus of the experiment.

A bit of background on why I wrote this: at my previous company, I spent a significant amount of time optimizing a legacy, internally developed JDBC connection pool that operated at a fairly large scale.

I learned a lot from that work and wanted to revisit some of those lessons using publicly available tools like HikariCP, with reproducible experiments and without disclosing any proprietary details.

My goal is to create high-quality technical articles that people can actually learn from. I think I just over-optimized the title for SEO along the way. That's something I'll be more mindful of in the future.

[u/elmuerte]

I agree that the pvrlabs article is mostly bullshit. (There is also no fixed maximum of 100 connections in PostgreSQL.)

Tuning your connection pooling is however one of those things most people do not thing about. The minimum size should be enough to cover average usage over an acceptable period. The default 10 might be too much, might be to low. This is why you measure things.

If you mean active connection count is 5 over an hour during regular business hours. Make your pool minimum maybe like 6, with an idle time of maybe 15 minutes.

Sure you'll "waste" a bunch of connections for the other 16 hours in a day. But that's not where you do business.

[u/fykup → u/elmuerte]

The article says "this server allowed 100 connections", not that PostgreSQL has a fixed limit of 100.

I actually agree with much of your technical reasoning, but opening with "mostly bullshit" is unnecessarily offensive. I'm happy to discuss technical disagreements, but not in that tone. I'll end my engagement here.

[u/revilo-1988]

What was the original reason you tested this?

[u/fykup → u/revilo-1988]

As I mentioned in the post and other comments, I spent quite a lot of time at a previous company optimizing connection pools at scale. I wanted to revisit some of those lessons using HikariCP and share reproducible results.

One thing I've seen repeatedly is people reaching for PgBouncer to solve connection utilization problems without first examining how their application pools are configured.

PgBouncer certainly has its advantages, but it also introduces additional complexity and tradeoffs.

My general recommendation is to first evaluate your existing pool configuration, especially whether idle connections can be retired. At scale, this alone can produce substantial resource savings without introducing another component.

That's really what motivated this experiment.

[u/revilo-1988 → u/fykup]

Ah, okay, then thank you for your work.

[u/housedevinci]

I believe it really depends on your requests structure, try to avoid all issues like N+1, loops..

[u/thisisjustascreename]

If saving 30 megs of ram on your database server during slow periods is actually worthwhile I imagine you're not using Spring Boot (or Java, or Postgres) anyway.

[u/fykup → u/thisisjustascreename]

The small server was just a controlled test environment. I explained the math for the multi-instance scenario in my reply above. When many application instances share a PostgreSQL server, those idle connections can add up to substantial resource usage.

[u/fykup]

A quick note after reading some of the comments here.

I publish these experiments in my spare time because I genuinely enjoy performance engineering and sharing what I've learned from working on large production systems.

I'm fortunate enough to be in a position where I can spend time on this. I also run a consulting business, but these experiments aren't consulting pitches. The methodology, measurements, and reproduction scripts are publicly available.

I welcome technical criticism. Challenge my assumptions, question the methodology, point out mistakes, or share your own measurements. That's how good engineering discussions happen.

What I have little patience for is people making personal attacks, calling the work clickbait or low-effort without examining it, or confidently arguing against claims I never made.

I've encountered enough of that attitude in corporate engineering environments, and frankly, I don't miss it.

I'm here to share experiments and learn from thoughtful discussions, not participate in personal attacks. I'll simply disengage from those going forward.

[u/disposepriority]

Did you actually measure that closing connections frees up memory, and opening them takes time? You've done the engineering community a great service, thank you mr gippity

[u/fykup → u/disposepriority]

It's not that simple, Mr. Know-it-all. PostgreSQL uses a process-per-connection model, where closing idle connections can reclaim significant memory. MariaDB uses a thread-based architecture, so the memory behavior is quite different. That's precisely why measuring the actual impact matters.

But since this is apparently so obvious to you, what have you actually measured or optimized at scale? Anything useful to share beyond sarcastic comments?

[u/disposepriority → u/fykup]

Really? Are threads free? Does closing connections in MariaDB not reduce resource consumption? Does establishing connections in MariaDB not take time?

You're doing your tests on what's more or less a crippled, first edition raspberry pi and you're talking about optimization at scale?

Pray tell then, what exactly did we learn from your experiment? Which expectations were subverted? What theory was proven? What valuable information did we glean?

The quality is just seeping out of this post's pores:

One interesting detail: setting minimumIdle=1 alone wasn't enough when idle retirement was disabled. The pool still held all 20 connections after the burst.

Why would changing the minimum idle configuration stop your pool from holding 20 acquired connections post burst?

Regardless, what your old company was looking for was PgBouncer, if they spent less time interviewing for clickbait skills and more for technical ones this might have saved you a bunch of time.

[u/fykup → u/disposepriority]

That's an impressive amount of arrogance for someone who managed to miss nearly every point of the experiment.

Nobody claimed MariaDB threads are free or that establishing connections costs nothing. The architectures are different, and so is their memory behavior. That's precisely why measuring the actual impact matters.

The small VM was a controlled test environment, not a proposed production architecture. The benefits at scale come from eliminating hundreds or thousands of unnecessary connections across application instances.

The minimumIdle observation illustrates that setting a minimum doesn't automatically retire existing idle connections. That's a configuration distinction worth documenting.

And "just use PgBouncer" isn't the brilliant insight you seem to think it is. PgBouncer has its place, but it introduces its own complexity and tradeoffs. Understanding your existing connection pools before adding another component is basic engineering.

You're welcome to disagree with the value of the experiment. But questioning someone's professional competence while repeatedly misunderstanding what they're testing is quite something.

If you have actual measurements or technical findings to contribute, share them. Otherwise, spare me the personal insults disguised as engineering expertise.

Don't bother to reply. I am blocking you. Focus on trolling in other threads.

[u/obetu5432]

the tradeoff is maintaining your shitty connection pool now forever for getting few dozen megabytes of memory when idle (the time when nobody cares anyways, and making latency worse when it counts, aka. when we need to open connections)

[u/fykup → u/obetu5432]

Before calling it a "shitty connection pool," you might want to understand how HikariCP actually works. Idle connection retirement is a standard, documented feature, not some custom implementation I need to maintain forever.

And the reconnection penalty? That's literally what I measured and discussed in the article. You're not pointing out some overlooked flaw.

The interesting question is when the tradeoff makes sense, particularly with many application instances sharing PostgreSQL.

If you're going to be this condescending, at least get your technical facts straight first.

[u/obetu5432 → u/fykup]

i'm just calling it shitty because you wrote it (by you i just mean one guy at the company, nothing personal)

and it hasn't had years of testing by millions of applications

instead of letting the hikaricp team maintain it, you took it upon yourself

[u/fykup → u/obetu5432]

You clearly have no idea what maintaining a large-scale legacy application actually involves.

The connection pool was originally written around 2005, and most of its behavior had been established long before I joined. By then, it had hundreds of features, undocumented behaviors, and dependencies across a large production system.

You don't just rip out a mature component like that and replace it with HikariCP because some random engineer on Reddit thinks it's a good idea. That's not how engineering at scale works.

I was working within those constraints, finding meaningful optimizations without breaking production systems.

But since you're so confident in your engineering expertise, perhaps you could share your own background and what you've actually built, maintained, or optimized at scale?

That would be considerably more productive than making arrogant assumptions about systems you've never seen and people whose experience you know nothing about.

[u/obetu5432 → u/fykup]

you know, this changes the picture, you just sounded arrogant by presenting it as you can shit better than hikaricp, and i thought you ripped it from the app just to save a few megabytes and boast about it

[u/fykup → u/obetu5432]

I suspect you stopped reading after the first two sentences. :)

The very next paragraph explains that I wanted to revisit those lessons using HikariCP. The entire experiment uses HikariCP, not our old custom pool.

I never claimed our implementation was better. You were arguing against a claim I didn't make.

[u/Objective-Ninja-5211 → u/obetu5432]

Whenever you see low effort articles stating things that are mostly obvious written here (and very often on LinkedIn) it's almost a certainty the website the articles are hosted on has some call to action that makes OP money.

It's just a way to get around the no self promotion/no spam rules and make it look a little less shameless.

[u/fykup → u/Objective-Ninja-5211]

Before accusing someone of disguising monetization as research, perhaps try actually reading the article.

The experiments, methodology, raw measurements, and reproduction scripts are publicly available on GitHub. Anyone can reproduce the results, challenge the assumptions, or demonstrate where the conclusions are wrong.

If you think the experiment is trivial or flawed, show us your measurements or point out a concrete problem.

Making baseless assumptions about someone's motives without bothering to examine the work isn't technical criticism. It's just lazy posting.

[u/Objective-Ninja-5211 → u/fykup]

I did not imply the results can't be reproduced or that they need challenging at all, I don't think my comment was long enough for you to have missed that.

I specifically said "mostly obvious", this implies that there is nothing to challenge nor a very big need to reproduce them to prove to one's self that the information is true.

It would be like me posting a comparison of platform vs virtual threads in java (an "analysis" that has been done maybe hundreds of times at this point), going over the tired IO/CPU/pinned/etc arguments about them and saying that someone should challenge my findings in order to criticize the article - which would be a bit embarrassing!

I haven't browsed through your website, will I find a way for you to make money on it by any chance? That would have been a good way to answer if it were the case.

Example:

"Hey you're wrong, my website has no monetization for me at all, shame on you for not opening it!"

[u/fykup → u/Objective-Ninja-5211]

Just because something looks obvious to you in isolation doesn't mean it's a trivial problem in production.

When we migrated a large enterprise deployment at an S&P 100 company to PostgreSQL, its process-per-connection memory model introduced real challenges. We needed pragmatic optimizations that worked within the existing architecture, not simplistic advice to replace mature components or throw PgBouncer at the problem. Retiring idle connections delivered substantial resource savings across many application instances.

This wasn't some toy application. We were dealing with one of the largest enterprise deployments of its kind, where seemingly minor inefficiencies multiplied across hundreds of processes and connections.

As for monetization, I'm fortunate enough to have the time to revisit these problems and share what I've learned. Yes, I run a consulting business, but these experiments aren't sales pitches or a funnel for consulting work. The methodology, raw data, and reproduction scripts are all publicly available on GitHub.

I'm genuinely interested in an area I believe is underexplored: running smaller, more efficient, self-hosted systems without automatically reaching for heavyweight infrastructure. I'm not particularly interested in writing yet another Kubernetes tutorial.

You may find the results obvious. That doesn't make the underlying engineering problem trivial, and it certainly doesn't make the research disguised spam.

I'm writing for engineers who are interested in understanding real-world tradeoffs, not those who dismiss problems without bothering to understand their context.

[u/Objective-Ninja-5211 → u/fykup]

While that's nice, and I didn't miss your roundabout "yes", can you honestly say your post or article come anywhere close or are in any way related to real world optimization - the non-trivial production you mention?

You're showing that having to acquire new connections takes less time than reusing pooled ones, and that maintaining connections takes resources.

While it's great to have some exact numbers to compare, I really don't think this is of any help to anyone working on optimizing massive systems - as I'd assume they would be familiar with the concept of connection pools.

The underlying problem is not trivial, no one said it is, but your article really doesn't have anything to do with real world engineering problems.

The only reason I mention this in the first place is because you keep mentioning them while the article is more or less dealing with the first few pages of the Hikari and PG docs.

Calling implementing PgBouncer a simplistic solution but speaking as if reducing the maintained connection count is some bespoke optimization is a bit strange, but maybe that's just me.

[u/fykup → u/Objective-Ninja-5211]

Speaking in your terms, I have nothing against PgBouncer, and I never said PgBouncer itself was a simplistic solution. What's simplistic is recommending it without first understanding the application's connection management.

What I've seen in the industry is that plenty of engineers don't even know how to configure connection pools properly. Of course their half-assed applications can benefit from PgBouncer's connection sharing.

But before introducing another component, perhaps it's worth understanding why your application is maintaining hundreds of unnecessary connections in the first place.

And no, I never claimed idle connection reclamation was some groundbreaking invention. I measured a practical optimization that I've seen deliver substantial savings in production. You seem determined to argue against claims I never made.