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.

What a connection pool is and why

The problem. A database connection is an expensive thing: a TCP handshake, encryption, authentication, and on PostgreSQL a separate process on the server side as well. Classic PHP paid for that on every request, because the process was going to die right after anyway.

A resident worker lives for weeks — and that brings the opposite trouble. A connection opened once does not stay healthy by itself: the database restarted, a firewall dropped an idle socket, the server closed it on its own timeout. The worker learns none of this and goes on treating the socket as working — until the first request that fails.

And a third thing: if a connection is opened per incoming request, their number is bounded by nothing. A thousand simultaneous requests means a thousand connection attempts, and the answer is too many connections, which takes down the whole application rather than just the spike.

The solution. A set of connections opened ahead of time, handed to coroutines for the duration of their work and returned by themselves. The pool checks them before handing them over, replaces dead ones, rotates aged ones and never opens more than allowed. Application code does not change at all: the repository is what takes the connection.

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.

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. poolWaitTimeout is the budget for the whole borrow, not for a single wait: waiting for a free connection, discarding dead ones and opening their replacements all come out of it. When it runs out the request fails with a PpaPoolException. A fast, comprehensible refusal is better than a request that hangs forever.

So the first request after a long pause does not fail even when the server closed every socket meanwhile: the borrow discards them one by one, opens a fresh connection and carries on — at the cost of a single reconnect.

What happens on a failure

The pool distinguishes two fundamentally different causes of an error:

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.

Tuning

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

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', '');
  }
}

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 work under Swoole only and drive a background maintenance timer; at zero it is not started at all. It is on by default, and not by accident: a pool lives in one worker’s memory and is only ever examined when someone borrows from it. An application between bursts would hold its sockets indefinitely and never notice the server or a firewall dropping them — and the first request back would pay for that discovery, burying dead connections and reopening. Instead, idle connections are pinged every two minutes and handed back to the database after ten idle minutes.

The numbers follow HikariCP’s ordering rule — keepaliveTime < idleTimeout < maxLifetime — and the pool drops a keepaliveTime the connection will not live to receive. minimumIdle deliberately stays lazy: a warm floor multiplies by worker count here, unlike a pool that lives once per JVM. Details on the pool policy page.

Seeing what is going on

php call db pool

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.

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.

PpaConnectionPool reference

PpaConnectionPool is a static facade over every pool in the process. Application code rarely needs it: the repository takes the connection, and the kernel handles closing and resetting. The methods are described here because diagnostics go through them, and because two of them do almost the same thing — and mixing those two up is expensive.

Access is static, with no dependency injection: the pool holds process-wide state, and there cannot be a second instance of it.

db()

Returns the connection of the current unit of work.

Under Swoole it borrows a connection from the pool on the first call within a coroutine, caches it in the coroutine’s context and registers a defer that returns it to the pool when the coroutine ends. Every query of one coroutine therefore runs over one connection — which is what transactions rest on.

Without Swoole there is no pool at all: the method hands out the process’s single connection.

Syntax

public static function db(string $configClass): CDO

Parameters

$configClass — the database configuration class, MainDbConfig::class.

Returns

A CDO already bound to the current coroutine. There is no need to release it by hand.

Errors

PpaPoolException — if the borrow never reached a live connection within poolWaitTimeout. The reason differs and the message is worth reading: every connection busy; a connection that could not be opened (the driver’s error is inside); or a window spent on dead connections — the server is reachable but not serving.

getConfigDb()

Returns the registration instance of a configuration.

For reading settings without opening a connection: the driver, the schema, the address. The instance is created once per class and reused — it is the one setUp() was called on.

Syntax

public static function getConfigDb(string $configClass): DbConfigInterface

Parameters

$configClass — the configuration class.

Returns

The configuration instance. No connection is opened.

showDbConfigs()

Returns every configuration registered in the process.

This is the door for health checks and diagnostics: walk the list and poll each database with pingDetail() without knowing in advance how many there are in the project.

Syntax

public static function showDbConfigs(): array

Returns

An array of configuration instances. Only the ones already used appear in it: registration is lazy.

stats()

Returns the utilisation of every pool.

The same numbers call db pool prints and /actuator/pools serves. They are counted for the current worker: each worker has its own pool, and a request sees the one that served it.

Syntax

public static function stats(): array

Returns

An array keyed by configuration class, each value four numbers: total (open in all), idle (free), active (handed out) and maximum (the ceiling).

Without Swoole the array is empty: there are no pools there.

reportFailure()

Reports to the pool a failure that happened on a borrowed connection.

The method decides whether the connection is to blame. A lost connection — the connection is evicted, and the next request gets a new one. A constraint violation or a syntax error — the connection is healthy and nobody touches it.

Repositories call it; you rarely have to write it in application code.

Syntax

public static function reportFailure(string $configClass, Throwable $error): bool

Parameters

$configClass — the configuration whose connection failed.

$error — the exception exactly as CDO or PDO threw it.

Returns

true if the error was classified as a connection loss and the connection was evicted; false if the server is healthy and the statement itself was at fault.

shutdown()

Closes every pool and connection the process owns.

Called when a worker stops. Besides the sockets it releases the housekeeping timer: a live Timer::tick holds a reference to its pool and keeps the worker’s reactor from draining.

Syntax

public static function shutdown(): void

reset()

Forgets every pool and connection without closing them.

Called in a child process straight after fork(). The difference from shutdown() is fundamental: a fork copies file descriptors, so a socket inherited from the parent is physically the same one — close it in the child and you tear down the parent’s connection. The child must simply forget them and open its own.

Syntax

public static function reset(): void

Next

• 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