Database · Pool

Connection pool

Application code never sees the pool: a repository takes a connection and gives it back on its own. This page is about what happens underneath, and about the three decisions a person still makes: how many connections, what to do on failure, and how to tell when the pool is full.

How it works

The main thing PPA gives you. It is modelled on HikariCP from the Java world: the point is not reuse as such, but that connections are kept working.

text

worker
└── pool (per configuration class)
    ├── connection 1  ← coroutine A took it for the request
    ├── connection 2  ← coroutine B
    └── connection 3    free

A coroutine takes a connection on its first database call and returns it automatically when it finishes. There is nowhere you have to release it by hand — the return is attached with defer.

Rules applied on every hand-out

Only idle connections are checked. A connection that lay unused for more than 500 ms is checked before being handed over; a dead one is discarded and replaced with a fresh one. A connection that was just in use is not checked at all.

This is a deliberate trade-off: a SELECT 1 before every query would cost an extra round trip to the server every time. Only what really was idle gets checked.

Rotation by age. A connection older than 30 minutes is replaced in advance, before the server or a firewall closes it. The moment of replacement is jittered slightly so that the whole pool does not renew at once.

Bounded waiting. If every connection is busy, a request waits for a free one for no longer than poolWaitTimeout and then fails with a PpaPoolException. A fast, comprehensible refusal is better than a request that hangs forever.

What happens on a failure

The pool distinguishes two fundamentally different causes of an error:

Cause	How it is detected	What the pool does
The connection died	SQLSTATE class `08`, PostgreSQL codes `57P01/02/03`, MySQL 2006/2013/2055	Discards the connection — the next query gets a new one
The query was rejected	A constraint violation (`23xxx`), syntax (`42xxx`), a deadlock	Nothing: the server is healthy and the connection is fine

PostgreSQL took some work: PDO does not report a lost connection with code 08006. When the socket is already gone there is nowhere to take a SQLSTATE from, and the error arrives as HY000 with the generic libpq code 7 — the same one an ordinary syntax error carries. When the driver’s verdict is that uninformative, the pool checks the connection and decides by the answer.

A failed query is not retried

The pool discards the connection but does not try to run the query again — and it will not. It does not know what managed to happen: the break could have occurred after the server applied the write, and a retry would duplicate it. Retrying one statement out of an aborted transaction is meaningless anyway.

One query ends in an error and the connection is scrapped. The decision to retry is yours, at the business-logic level.

Tuning

Every database configuration has a pool — for 5 connections by default. To set your own values, implement PpaPoolConfigInterface through PpaPoolTrait:

main/MainDbConfig.php

use Flytachi\Winter\Cdo\Config\PgDbConfig;
use Flytachi\Winter\Ppa\Pool\{PpaPoolConfigInterface, PpaPoolTrait};

class MainDbConfig extends PgDbConfig implements PpaPoolConfigInterface
{
  use PpaPoolTrait;

  public int   $poolMaxConnections = 10;
  public float $poolWaitTimeout    = 5.0;

  public function setUp(): void
  {
      $this->host     = env('DB_HOST', 'localhost');
      $this->database = env('DB_NAME', 'app');
      $this->username = env('DB_USER', 'postgres');
      $this->password = env('DB_PASS', '');
  }
}

Property	Default	What it sets
`$poolMaxConnections`	`5`	The connection ceiling per configuration
`$poolWaitTimeout`	`3.0`	How many seconds to wait for a free one
`$keepaliveTime`	`0` (off)	Background checking of idle connections
`$idleTimeout`	`0` (never)	Close connections idle for longer than N seconds
`$minimumIdle`	`0` (lazy)	How many connections to keep ready

The trait supplies a default for every parameter, so a configuration declares only what it changes and does not break when a new one is added to the interface.

The last three switch on a background maintenance timer and work under Swoole only. At zero the timer is not started at all.

Count the ceiling together with the workers

The limit is per worker, not per application. The server has to withstand worker count × poolMaxConnections × number of configurations.

Eight workers with a pool of 10 is 80 connections to the database from a single container. With max_connections = 100 on PostgreSQL, a second such container will not come up.

Seeing what is going on

bash

php call db pool

text

MainMainDbConfig
active 12 · idle 3 · total 15 · maximum 20 · workers 2
saturated  1 of 2 workers                    [SATURATED]
per worker
worker#0  MainMainDbConfig  active=2  idle=3 total=5  max=10  age=0s
worker#1  MainMainDbConfig  active=10 idle=0 total=10 max=10  age=3s

What to look at is the worker lines, not the overall total: a request queues for its own worker’s pool, so one saturated worker means real latency even when there is plenty of room in aggregate.

The console is a separate process and cannot peer into the running server’s memory, so the workers publish their statistics on a timer themselves. The interval is set by the PPA_POOL_TELEMETRY variable (seconds, 5 by default, 0 disables it). The same figures are served by the /actuator/pools endpoint when the actuator is enabled.

Pool load is deliberately not part of /actuator/health: database availability and pool saturation are different questions. A busy but correctly working service should not answer degraded to the check that decides whether to send it traffic.

An application without a database pays nothing

Publishing starts not when the worker starts but when the first pool is created. An application that never touches a database starts no timer, writes no records and creates no storage directory.

`PpaConnectionPool` reference

A static facade. Application code rarely needs it — the repositories use it.

Method	What it does
`db($configClass)`	the connection of the current unit of work: pooled under Swoole, the single one without it
`getConfigDb($configClass)`	the registration config instance — for reading settings, not for connecting
`showDbConfigs()`	every registered config, for health checks
`stats()`	utilisation of each pool: `total`, `idle`, `active`, `maximum`
`reportFailure($configClass, $error)`	report a failure on the borrowed connection
`shutdown()`	close everything this process opened — at worker exit
`reset()`	forget everything without closing — in a child after `fork()`

The difference between the last two matters. shutdown() closes the sockets and releases the housekeeping timer: a live Timer::tick would keep the worker’s reactor from draining. reset() is called in the child after a fork, where the sockets must not be closed — the descriptors are shared with the parent, and closing would tear its connection down.

The kernel does this for you

The fork reset, the shutdown at worker exit, the pool’s logger, the timezone provider and the telemetry storage are all installed at boot — in Kernel::init() and on the worker events. The package fetches none of it itself: it never reaches for the framework’s globals, which is exactly what lets it be used (and tested) without the kernel.

Without Swoole

A process serves one unit of work at a time and there is nothing to distribute: each config gets one self-maintaining connection for the life of the process. Its liveness checks and lifetime are the same, so a long-running CLI worker does not wake up holding a socket the server closed hours ago. Application code is identical either way.

PHP Persistence API — the layer as a whole, and query examples
Database configuration — where credentials and the driver are set
Repositories — what the connection is taken for