Some suggestions for improving support for dynamically sized connection pool

[CodePlayer]

It is well known that the HikariCP connection pool has two key parameters: minimumIdle and maximumPoolSize.

By default, these values are equal (both default to 10). This fixed-size connection pool design delivers near-optimal performance in the vast majority of scenarios.

However, real-world use cases are more complex. Most applications inevitably encounter some slow-query connections.
Once these slow queries occupy a large portion of the connection pool, the overall application performance can suffer significantly.

I am aware that the official recommendation is to isolate "fast" and "slow" connections into separate connection pools to achieve optimal results.
Yet, reality is often harsh. For example:

1. It’s difficult to pre-classify connections as fast or slow. Determining this in advance is cumbersome. Moreover, as data volume grows and traffic fluctuates, queries that were once fast may degrade into slow ones.
2. Multiple connection pools introduce complexity at the application level. Using multiple pools inevitably means managing multiple data sources. Each business method must explicitly specify which data source to use, and transactions must be handled correctly across them—both of which are non-trivial tasks. Undoubtedly, this also increases application code complexity.

Therefore, although we understand that isolating fast and slow connections via multiple pools is the best practice,
in reality, most small and medium-sized enterprises—lacking dedicated DBA teams like large enterprises—must compromise by using a dynamically sized connection pool rather than a fixed one.

In such a compromise scenario, consider the following example: assume minimumIdle = 10 and maximumPoolSize = 100.

1. Time point 1: No requests → pool size = 10 (active = 0, idle = 10)
2. Time point 2: 5 concurrent requests → pool size = 15 (active = 5, idle = 10)
3. Time point 3: 3 concurrent requests → pool size = 13 (active = 3, idle = 10)
4. Time point 4: 50 concurrent requests → pool size = 60 (active = 50, idle = 10)
5. Time point 5: 8 concurrent requests → pool size = 18 (active = 8, idle = 10)
6. Time point 6: 120 concurrent requests → pool size = 100 (active = 100, idle = 0)

From this example, we can observe that:
When minimumIdle and maximumPoolSize are not equal, as long as the number of concurrent requests is below maximumPoolSize,
the total number of connections in the pool will constantly fluctuate with the request load—regardless of the minimumIdle setting.
This leads to frequent creation and destruction of connections, causing unnecessary performance overhead.

Thus, in such scenarios, introducing a minimum pool size option might be more reasonable.
For instance, adjust the configuration to minimumPoolSize = 10, minimumIdle = 5, maximumPoolSize = 100. Then:

1. Time point 1: No requests → pool size = 10 (active = 0, idle = 10)
2. Time point 2: 5 concurrent requests → pool size = 10 (active = 5, idle = 5)
3. Time point 3: 3 concurrent requests → pool size = 10 (active = 3, idle = 7)
4. Time point 4: 50 concurrent requests → pool size = 55 (active = 50, idle = 5)
5. Time point 5: 8 concurrent requests → pool size = 13 (active = 8, idle = 5)
6. Time point 6: 120 concurrent requests → pool size = 100 (active = 100, idle = 0)

In this setup, except during traffic peaks, the pool size remains relatively stable under normal conditions, avoiding frequent connection churn and the associated performance cost of constantly creating and destroying connections.

Of course, there is still significant room for optimization.
After all, even if the number of concurrent requests exceeds minimumPoolSize, it doesn’t necessarily mean slow queries are present—it could simply be a burst of fast requests.
In such cases, maintaining a small, fixed number of default connections combined with a well-tuned request queue would yield better performance.

Furthermore, by dynamically monitoring both the connection pool and the request queue, and triggering pool expansion only when certain thresholds are exceeded—up to maximumPoolSize—
we could achieve a more balanced solution: one that maintains high performance while also preventing slow queries from monopolizing a fixed-size pool.

For example, keep the previous settings (minimumPoolSize = 10, maximumPoolSize = 100), meaning 10 connections are initialized by default, and additionally:

1. Introduce a threshold parameter such as queueGrowthThreshold = "100/20", meaning that every time the request queue size exceeds 100 (checked periodically, e.g., every 1–2 seconds), one additional connection is added for every extra 20 queued requests.
2. Alternatively, if the queue is non-empty—even if the above condition isn’t met—but all requests in the queue remain unprocessed beyond a specified timeout, the system should still add a few extra connections proactively.

Connections would be added (or removed, conversely) in this manner until maximumPoolSize is reached.

With this approach, even if concurrent requests exceed minimumPoolSize, as long as the queue size stays below the defined threshold, it indicates the current pool can handle the load—no expansion is needed.
Dynamic scaling would only be triggered when the consumption rate of the connection pool consistently and meaningfully lags behind the incoming request rate.