I am using a Postgres database. I have configured it to support multi-tenancy with a different schema for each tenant.
Overnight, my Spring Boot application runs several processes in parallel (around 100+, one for each tenant). Each process operates on a different schema and executes some long-running queries.
It is not easy to quantify the exact average duration because it varies greatly from one schema to another depending on the amount of data.
As the number of tenants and schemas (and therefore the number of concurrent task) increases, my current HikariCP connection configuration cannot keep up with the growing number of requests, and I am starting to see exceptions like this frequently: Connection is not available, request timed out after 80000ms
Current configuration:
spring: datasource: hikari: connection-timeout: 80000 maximum-pool-size: 10 I have read conflicting information online about what the optimal parameters for datasource.hikari.connection-timeout and datasource.hikari.maximum-pool-size should be, especially in a growing environment with long-running queries, so any feedback would be appreciated.
In the future, I plan to reduce and limit the maximum number of simultaneous threads, but until then I need to avoid receiving timeout exceptions.
I am thinking of increasing the maximum pool size to around 100, but I am a little afraid to increase the connection timeout because I need to avoid blocking completely.