
Reducing PostgreSQL Memory Use by Shrinking Idle Connection Pools
At my previous company, many application VMs connected to a shared PostgreSQL database. Shrinking idle connection pools across those instances produced substantial aggregate memory savings. I wanted to revisit that trad…
以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。
At my previous company, many application VMs connected to a shared PostgreSQL database. Shrinking idle connection pools across those instances produced substantial aggregate memory savings. I wanted to revisit that tradeoff in a small, reproducible setup: how much database memory would idle retirement release, and what would it cost when traffic returned?
HikariCP’s fixed-size default is documented behavior. This experiment measures its database-side footprint and the cost of rebuilding capacity. Two quiet Spring Boot instances retained 20 PostgreSQL connections with default pool sizing. Allowing excess idle connections to retire reduced that to 2, associated with approximately 29 MiB lower PostgreSQL cgroup memory. Median connection-acquisition time when traffic returned rose from under 3 ms to about 200 ms in this test.
The reason to care grows with the number of application instances. As an illustrative example, 30 instances retaining 32 connections each means 960 persistent connections. Reducing the average to 16 per instance leaves PostgreSQL with 480 fewer connections to maintain. The two-instance experiment below does not establish a general memory saving per connection.
The experiment record includes the source, reproduction instructions, screenshots, and recorded data. This article uses Spring Boot 4.1.1, HikariCP 7.0.2, and PostgreSQL 16.15 on Java 25.
I ran two copies of a tiny Spring Boot JDBC application using HikariCP against PostgreSQL. Instance A received a short burst; B started before A finished and stayed busy longer. That created three useful observations: both pools under demand, A idle while B remained busy, and both idle before the next burst.
Each configuration ran twice in mirrored order, with fresh application processes. An independent sampler recorded per-instance PostgreSQL connections and Hikari total, active and idle counts. The applications and database shared a two-vCPU, 1 GiB Linux VM with no swap. This kept the setup compact; it was not a distributed application benchmark.
I left minimumIdle and maximumPoolSize unset in the default-sizing profile. Both resolved to ten. Three settings describe the comparison:
Lifetime and keepalive were deliberately disabled for all profiles. This compares default pool sizing, not every untouched Hikari default. Each workload request intentionally held a database connection for one second, making simultaneous occupancy visible.
Both pools reached ten connections in every scenario, with actual elevated overlap verified by the runner. Hikari totals agreed with PostgreSQL counts.
Reclaim released 18 of the 20 connections during the quiet window, while retaining the ability to grow to ten per instance.
Connection counts are medians over ten one-second samples per scenario; peaks are observed maxima. PostgreSQL cgroup memory was requested every fifth sample, giving two readings per ten-second window. The memory medians are of those two readings.
PostgreSQL’s accounted memory moved with the connection count. Cgroup memory is the cluster’s charged memory, including cache, on this PostgreSQL 16 server (shared_buffers 128 MiB).
The same split was already visible before the burst. Default sizing started at 20 connections and about 56–58 MiB. Both elastic profiles started at 2 connections and about 28–29 MiB. Where private PostgreSQL memory was recorded in the quiet window, it was about 12 MiB with two connections and about 36 MiB with twenty. Each application JVM stayed around 190–207 MiB either way. The memory change is on the database server.
A returned to one connection while B still held ten and continued serving its longer burst. Retirement followed each instance’s demand rather than requiring the entire application pair to be quiet.
The no-reclaim profile isolates the mechanism: it started at one connection per instance, allowed each pool to grow to ten, and disabled idle retirement.
After traffic stopped, it retained ten per instance and about 57 MiB of PostgreSQL cgroup memory. A lower minimumIdle did not release the connections opened during the burst while idleTimeout was off. Retirement is what released that capacity, and the PostgreSQL memory went with it.
Reclamation was not free. To test whether reclaimed capacity could be restored when traffic returned, each instance later received ten simultaneous requests. This return of traffic is the revisit. The reclaim pools reopened successfully, but median connection-acquisition times were approximately 194–207 ms, compared with under 3 ms when connections had been retained.
Median total revisit request time was approximately 1.44–1.46 seconds with reclaim, versus 1.02–1.03 seconds with retained pools. Those totals include the common intentional one-second connection hold. The test harness deliberately synchronized the ten requests: each acquired one connection, then waited at a barrier until all ten had acquired theirs before starting its one-second database operation. The slowest acquisition took about 430 ms, explaining much of the additional request latency.
All 2,280 measured requests succeeded. All six scenarios were valid, with zero sampling errors or supporting warnings. After revisits, the pools again held ten connections each, showing successful regrowth after retirement.
These short synthetic waves show an observed cost of rebuilding pool capacity. They do not establish general production latency.
Per-instance medians from ten requests per revisit. Request times include the intentional one-second connection hold.
Idle retirement can free connection slots as well as database memory. This PostgreSQL server allowed 100 connections; the two quiet default pools occupied 20 of them.
Choose maximumPoolSize from how many database operations must run at once and from PostgreSQL's connection capacity. Set minimumIdle below that maximum when you want the pool to shrink, choosing how many connections are worth keeping warm. Leave idleTimeout enabled. Hikari’s configuration documentation gives a 10-minute default. Choose a production timeout based on application demand and the cost of rebuilding connections; this experiment did not validate production timeout settings. minimumIdle=1 is the setting that demonstrated the mechanism here. An application with frequent bursts can keep a higher idle target and still stay below the maximum.
Hikari’s fixed-pool recommendation is aimed at spike latency. The tradeoff in this run was lower idle PostgreSQL resource use versus slower connection acquisition after a quiet period.
PgBouncer can help when PostgreSQL has too many connections, but it adds another component to operate. Its transaction-pooling mode requires care with session-dependent features, such as LISTEN and session-level advisory locks. Modern PgBouncer supports protocol-level prepared statements with a nonzero max_prepared_statements setting. Before introducing a connection pooler, I’d check whether the application is retaining unnecessary idle connections and measure what happens when those connections are allowed to retire. This experiment did not test PgBouncer.
I used StatLite to watch request rate, latency, JVM runtime memory, and CPU during the run. The screenshots below show that recorded workload; connection counts and PostgreSQL memory came from the independent measurements above.
Screenshots show the recorded workload history; the live status cards belong to later inspection apps. DB health: Not reported reflects a deliberate fixture setting: Spring Boot’s DB-health check was disabled so monitoring would not borrow connections and disturb idle retirement. The apps verified database access at startup. The red 5xx spikes came from startup health checks returning HTTP 503 before the apps were ready; the measured JDBC workload requests all returned HTTP 200.
Want to explore the dashboard yourself? The separate StatLite demo uses simulated data.
In both repetitions, releasing 18 idle connections was associated with about 29 MiB lower PostgreSQL cgroup memory. When traffic returned, the pools grew back to 20 connections, and the database memory footprint returned with them, at the cost of slower connection acquisition.
These measurements describe idle PostgreSQL backends in one small-server setup, rather than a general memory cost per connection. Cgroup accounting includes cache as well as connection-private memory; sessions doing more substantial database work can have a different footprint.
The public experiment record includes the application source, pool settings, environment details, and measurements from all six scenarios. The analysis script checks the recorded connection counts, memory readings, and request timings against the summary.
Updated October 17, 2026: revised the title, introduction, and presentation. Experiment results are unchanged.
Experiment record Recorded data Reproduction guide Analysis script
正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。