Best practices for Spring Boot connection pool configuration

Question

I am configuring JDBC connection pool for Spring Boot app and Postgres DB to use HikariCP connection pool and trying to find best practices for configuration setup, unfortunately there is not much info on this theme in the web.

I am preparing my own performance testing for different setups but would appreciate any help. The average throughput is around 20 req/sec for application node

I am the most interested in the most optimal values for such properties as:

    minimumIdle: ?
    maximumPoolSize: ?
    idleTimeout: ?
    maxLifetime: ?
    connectionTimeout: ?

Especially would be great to know the most optimal value for the maximumPoolSize There are plenty of other options available for connection pool setup would appreciate any advice about their impact on application performance.

Answer 1

@aliaksei-stadnik 20 req/sec is, in the grand scheme of things, pretty low. So, I wouldn't be overly concerned about pool tuning; more important is to focus on query performance. The lower your query times, the more requests you can handle with a smaller number of connections.

We always recommend running HikariCP as a fixed-size pool for best performance (leaving minimumIdle and idleTimeout unset). The maximumPoolSize is probably the key number you need to tune, and as the referenced link above says, it depends primarily on the number of CPU cores that your database server has.

With an average query time of 2ms, even a single connection could handle ~500 req/sec, and an average query time of 10ms would yield ~100 req/sec per connection. However, at the cost of a single request possibly waiting up to one second to be serviced. Additional connections would, in that case, serve to reduce the queueing time of requests.