Connection pooling: why apps need a database connection pool

Connection opening fee

Opening a new connection to the database involves a process of network handshake, authentication, and server-side resource allocation. If each application request opens a new connection, this charge will be repeated continuously.

What is connection pool

Connection pool is a collection of connections that have been opened before and used interchangeably by incoming requests. After completion of use, the connection is returned to the pool, not closed.

// Contoh konfigurasi pool sederhana pada driver PDO (php-pgsql, php-mysql, dll.)
// umumnya diatur lewat konfigurasi koneksi persisten
$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_PERSISTENT => true,
]);

Reasonable pool size

A pool that is too small makes a request to wait for a connection to become available. Pools that are too large can overload the database because each connection uses memory on the server side. The ideal size is usually adjusted to the number of application worker processes and the maximum connection limit of the database.

Stuck connection

Connections that are not returned to the pool, for example due to unhandled exceptions, can cause the pool to run out of slots. Make sure the code always returns or closes the connection through the guaranteed block that is always running.

Pools at the application level vs. infrastructure level

In addition to the built-in pool of drivers, there are also special proxies such as PgBouncer for PostgreSQL that manage pools outside of the application process. This approach is useful when many application instances share a single database connection limit.

practice;

Check the database connection configuration of your current application, then find out what is the maximum number of connections the database allows and compare it with the number of processes of the application worker.