Connection pool

Connection lifecycle

New connection create

When a backend application connects to the PostgreSQL database, the parent process in the database server spawns a worker process that listens to the newly created connection. Spawning a work process each time also causes additional overhead to the database server. As the number of simultaneous connections increases, the CPU and memory resources of the database server also increase

Where to place the Database Connection Pool?

Advantages

• Low latency since the pool is on the same box as the requester

• Better security since the connections are constrained to one client

Drawbacks

• It can be difficult to monitor and control connections if we use too many microservices

The connections are created in the Connection pool by software like PgBouncer and all the microservice instances will share those.

Pros:

• Flexible — database can be swapped out

• Centralized control of connections, which makes it easier to monitor and control connections

Cons:

• Introducing a new layer. could add latency

• Single point of failure for database calls across all clients

• Potential security issues since you are sharing connections between layers

⭐HikariCP

Github page with benchmarks

• minimum idle property

  • The property controls the minimum number of idle connections that HikariCP tries to maintain in the pool, including both idle and in-use connections. If the idle connections dip below this value, HikariCP will make a best effort to restore them quickly and efficiently

for the best performance in response to spike demands, HikariCP recommends a fixed-size pool LINK

Pool with smaller size performs better than with bigger size

given a single CPU resource, executing A and B sequentially will always be faster than executing A and B "simultaneously" through time-slicing LINK

connections = ((core_count * 2) + effective_spindle_count)