Connection issue from Spring Boot projects to PostgreSQL database while connecting

Question

I am working on a micro service oriented multi-tenant application using Spring MVC, Spring Boot and PostgreSQL. In my service domain, I have different 30 number of Spring Boot projects connecting with same database.

Issue

When I am starting more than 11 Spring Boot project parallel, I am not able to retrieve data from database. I am only getting blank JSON response. But If I am starting only less than 11 projects, then at that time, I am able to get the micro service result. Problem getting when I am starting from 12th service.

Troubleshooting and my Investigation

According to reading, I made little bit changes as follows,

I set PostgreSQL config file, max_connections = 100 and shared_buffers = 128MB.

But that not solved my problem. And also added additional lines in my application.property for hikari connection pool like the following,

spring.datasource.hikari.minimumIdle=3
spring.datasource.hikari.maximum-pool-size=3

Even this also not fixing my issue. Still I am only able to start maximum 12 Spring Boot projects at one time.

On pom.xml added the following,

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 <exclusions>
    <exclusion>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </exclusion>
  </exclusions>
</dependency>
<dependency>
  <groupId>org.apache.tomcat</groupId>
  <artifactId>tomcat-jdbc</artifactId>
  <version>9.0.10</version>
</dependency>

application.property updated with the following,

spring.datasource.tomcat.initial-size=15
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=20
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=5
spring.datasource.tomcat.default-auto-commit=true

Updates

As I mentioned above, in total I have 100 max connection. When I am starting first microservice and monitoring the DB connection by querying (below query), I am getting 10 connections.

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
 (select count(*) used from pg_stat_activity) t1,
 (select setting::int res_for_super from pg_settings where 
  name=$$superuser_reserved_connections$$) t2,
 (select setting::int max_conn from pg_settings where 
 name=$$max_connections$$) t3

Activity screenshot - When I started only 1 microservice I am getting like these,

Activity screenshot 2:

So here, for each micro service starting, 10 Database connections are initiating at the back end database level. So if I am starting more than 30 microservice application, it will go to more than 300 connection.

Confusion

1. Is this because of any database default settings?
2. Or is this because of Tomcat problem when connecting to DB engine?

Even I tried to use connection pool tool pgbouncer in my database node. Then also I am getting the same problems.

If this is due to database default settings or configuration, how can I override this?

Answer 1

A micro-service architecture is a kind of architecture in which an application is broken down into multiple independent services which are easily to understand, develop, test and are error-prone.

Now according to above definition, each independent service should have their own separate independent databases. But those services can inter-communicate for data if needed.

Each service should have a selected entities in their particular database.

If you have a common database for all those independent services, then you are violating the rule of micro-service architecture.

In this case, there is a high risk of data redundancy.

May be in your case, it is happening that services are containing duplicate version of the data and potentially using up the whole connection pool.

Better approach is to have separate database (containing only the entities specific to that service only) for separate independent service.

Clarification:

1. Explain above.

2. Maintain separate datasource hikari connection pool for all the spring boot modules/projects.

For example : Suppose you have two datasource for two modules/projects -

In application.properties.

## datasource for first service/module ##
datasource1.url=jdbc:postgresql://localhost:3306/service1
datasource1.username=service1
datasource1.password=password1
datasource1.driver-class-name=org.postgresql.Driver
datasource1.initial-size=15
datasource1.max-wait=20000
datasource1.max-active=20
datasource1.max-idle=20
datasource1.min-idle=5
datasource1.default-auto-commit=true

## datasource for second service/module ##
datasource2.url=jdbc:postgresql://localhost:3306/service2
datasource2.username=service2
datasource2.password=password2
datasource2.driver-class-name=org.postgresql.Driver
datasource2.initial-size=15
datasource2.max-wait=20000
datasource2.max-active=20
datasource2.max-idle=20
datasource2.min-idle=5
datasource2.default-auto-commit=true

Follow this links to read how to configure :

1. https://blogs.ashrithgn.com/multiple-data-source-in-spring-boot-using-spring-boot-data-starter/

2. https://www.ru-rocker.com/2018/01/28/configure-multiple-data-source-spring-boot/

Or you may need to go for Hibernate Multi-tenancy with Hikari-CP.