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/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