Lessons learned: Spring Data Postgres application configuration

Scenario: During development of a Spring Boot application, with a PostgreSQL backend, we randomly observe errors such as:

hikaripool-1 - connection is not available, request timed out after 30000ms

 

FATAL: remaining connection slots are reserved for non-replication superuser connections

 

Solution:

There are a few checks to perform:

1. Ensure that you use the correct Spring annotations to support the JpaRepository, such as the @Transactional and @Modifying annotations.  These help in making sure connections are closed and flushed
2. Pay special attention to places in your code where long running or computationally heavy database queries are made. You can check which queries are currently active on your PostgreSQL database by logging into the psql command line and running the command: “select * from pg_stat_activity“. Ideally, most of the connection slots displayed should be in the “idle” state.
3. The definitive solution in our case was to modify the connection pool properties in the application.properties file of the Spring Boot application, as follows:

spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000