> For the complete documentation index, see [llms.txt](https://amartyushov.gitbook.io/tech/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://amartyushov.gitbook.io/tech/tools/jdbc/connection-pool.md).

# Connection pool

## Connection lifecycle

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2Fy5exZCPpi05WOBDJjnec%2FScreenshot%202023-01-05%20at%2009.47.12.png?alt=media&amp;token=19018ca9-b850-4735-a69c-7b1c7424f833" alt=""><figcaption></figcaption></figure>

### New connection create

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2FpL8BY2kq74aOy2pDcDuh%2FScreenshot%202023-01-05%20at%2009.49.46.png?alt=media&amp;token=6ce833f3-a31b-4405-add9-820dfb5d1d01" alt=""><figcaption></figcaption></figure>

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&#x20;

### Where to place the Database Connection Pool? <a href="#id-188e" id="id-188e"></a>

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2Fn203CRPf1z8jJdUM86p7%2FScreenshot%202023-01-05%20at%2009.55.37.png?alt=media&amp;token=467a0ad6-3e86-41df-93bd-c4c85577234f" alt=""><figcaption></figcaption></figure>

**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

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2F1O140nGuWvueCnQoNh2b%2FScreenshot%202023-01-05%20at%2009.56.25.png?alt=media&amp;token=c5cc6965-222e-4c65-bfec-3c5a78e004da" alt=""><figcaption></figcaption></figure>

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

## :star:HikariCP

[Github page with benchmarks](https://github.com/brettwooldridge/HikariCP)

* `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

{% hint style="info" %}
*for the best performance in response to spike demands, HikariCP recommends a **fixed-size pool*** [*LINK*](https://github.com/brettwooldridge/HikariCP/blob/dev/documents/Welcome-To-The-Jungle.md)
{% endhint %}

{% hint style="info" %}
*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](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)

**connections = ((core\_count \* 2) + effective\_spindle\_count)**
{% endhint %}