Database connection pooling

suggest change

Opening a database connection is expensive. The client opens a TCP connection, maybe negotiates TLS, authenticates, and the server sets up a session (PostgreSQL even starts a new OS process for each connection). A connection pool keeps connections open and hands them out again instead of creating a new one for every request.

PHP is different from Java, Go or Node here. In the classic PHP-FPM / Apache model every request starts with nothing and everything is thrown away at the end, so there is no place to keep a pool of shared objects. PHP has three answers to this:

The examples use PostgreSQL via pdo_pgsql, but the ideas are the same for MySQL. Basic PDO usage is covered in PDO and transactions in Database Transactions with PDO.

How expensive is connecting?

Let's measure 200 queries, once with a new connection for every query and once with a single reused connection:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';
$n = 200;

$t = hrtime(true);
for ($i = 0; $i < $n; $i++) {
    $pdo = new PDO($dsn, 'postgres', '');
    $pdo->query('SELECT 1')->fetchColumn();
    $pdo = null; // closes the connection
}
$connectEach = (hrtime(true) - $t) / 1e6;

$t = hrtime(true);
$pdo = new PDO($dsn, 'postgres', '');
for ($i = 0; $i < $n; $i++) {
    $pdo->query('SELECT 1')->fetchColumn();
}
$reuse = (hrtime(true) - $t) / 1e6;

printf("new connection per query: %7.1f ms total, %.2f ms per query\n", $connectEach, $connectEach / $n);
printf("one reused connection:    %7.1f ms total, %.2f ms per query\n", $reuse, $reuse / $n);
printf("connecting is %.0fx slower\n", $connectEach / $reuse);

Output (local PostgreSQL 16 on Windows, no TLS, no password):

new connection per query:  6426.0 ms total, 32.13 ms per query
one reused connection:       88.3 ms total, 0.44 ms per query
connecting is 73x slower

The exact numbers depend on the OS and network. On Linux, connecting to a local server takes a few milliseconds, and to a remote database with TLS and SCRAM authentication it's often 10-50 ms. Either way, connecting costs far more than a simple query. On a page that runs 3 queries in 5 ms, the connection can be most of the response time.

Setting $pdo = null closes the connection only when it was the last reference to the PDO object. Statements (PDOStatement) hold a reference too.

Persistent connections

Pass PDO::ATTR_PERSISTENT => true to the constructor. When the request ends, PHP doesn't close the connection but stores it inside the PHP worker process. The next new PDO() with the same DSN, user name and password in that process gets the stored connection back:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';

function connect(bool $persistent): PDO
{
    global $dsn;
    return new PDO($dsn, 'postgres', '', [
        PDO::ATTR_PERSISTENT => $persistent,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
}

foreach ([false, true] as $persistent) {
    echo $persistent ? "persistent:\n" : "regular:\n";
    for ($request = 1; $request <= 3; $request++) {
        $pdo = connect($persistent);
        $pid = $pdo->query('SELECT pg_backend_pid()')->fetchColumn();
        echo "  request $request -> backend pid $pid\n";
        $pdo = null;
    }
}

Output:

regular:
  request 1 -> backend pid 27084
  request 2 -> backend pid 30328
  request 3 -> backend pid 20628
persistent:
  request 1 -> backend pid 7660
  request 2 -> backend pid 7660
  request 3 -> backend pid 7660

pg_backend_pid() returns the ID of the server process handling the connection. Without persistence every connection is new. With it, the same server session is reused.

Important things to know:

If the value of ATTR_PERSISTENT is a string, it becomes part of the key, so you can keep separate persistent connections with the same credentials:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';
$pid = fn(string|bool $p) => (new PDO($dsn, 'postgres', '', [PDO::ATTR_PERSISTENT => $p]))
    ->query('SELECT pg_backend_pid()')->fetchColumn();

echo "true:      ", $pid(true), "\n";
echo "'reports': ", $pid('reports'), "\n";
echo "'reports': ", $pid('reports'), "\n";
echo "true:      ", $pid(true), "\n";

Output:

true:      23136
'reports': 36324
'reports': 36324
true:      23136

Session state leaks between requests

A reused connection is the same server session, so anything the previous request changed in the session is still there. PDO only cleans up one thing for you: an open transaction is rolled back when the PDO object is freed. Everything else leaks:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';

function connect(): PDO
{
    global $dsn;
    return new PDO($dsn, 'postgres', '', [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
}

// "request" 1 changes session state and forgets about it
$pdo = connect();
$pdo->exec("SET TIME ZONE 'Asia/Tokyo'");
$pdo->exec("CREATE TEMP TABLE cart (item text)");
$pdo->exec("INSERT INTO cart VALUES ('book')");
$pdo->query("SELECT pg_advisory_lock(42)");
$pdo->beginTransaction();
$pdo->exec("INSERT INTO cart VALUES ('uncommitted')");
$pdo = null; // end of request 1

// "request" 2 gets the same connection
$pdo = connect();
echo "time zone:      ", $pdo->query("SHOW TIME ZONE")->fetchColumn(), "\n";
echo "temp table:     ", implode(',', $pdo->query("SELECT item FROM cart")->fetchAll(PDO::FETCH_COLUMN)), "\n";
echo "advisory locks: ", $pdo->query("SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid()")->fetchColumn(), "\n";
echo "in transaction: ", var_export($pdo->inTransaction(), true), "\n";

Output:

time zone:      Asia/Tokyo
temp table:     book
advisory locks: 1
in transaction: false

The uncommitted insert was rolled back, but the time zone, the temporary table with its committed row, and the advisory lock all moved into the next request. That request might belong to a different user. The lock is the worst case: it's held until the PHP process exits, and every other process that tries to take it will block.

Rules for using persistent connections safely:

The mysqli extension handles this better. Its persistent connections (host name prefixed with p:, e.g. new mysqli('p:localhost', ...)) are reset automatically by default before reuse. PDO_MYSQL persistent connections are not reset.

Dead connections

What if the database closed the stored connection while it was idle (server restart, failover, idle_session_timeout, a firewall dropping idle TCP connections)? pdo_pgsql checks the stored connection before handing it out and reconnects if it's dead:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';
$opts = [PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

$pdo = new PDO($dsn, 'postgres', '', $opts);
$pid = $pdo->query('SELECT pg_backend_pid()')->fetchColumn();
echo "persistent connection pid $pid\n";
$pdo = null;

// simulate a server restart / idle timeout killing the connection
$admin = new PDO($dsn, 'postgres', '');
$admin->query("SELECT pg_terminate_backend($pid)");
usleep(200_000);

$pdo = new PDO($dsn, 'postgres', '', $opts);
try {
    echo "next request pid ", $pdo->query('SELECT pg_backend_pid()')->fetchColumn(), "\n";
} catch (PDOException $e) {
    echo "error: ", $e->getMessage(), "\n";
}

Output:

persistent connection pid 22640
next request pid 31524

This only protects against connections that died between requests. A connection that dies in the middle of a request still throws PDOException.

Count your connections

Every database has a connection limit. PostgreSQL's default max_connections is 100 and each connection is a separate server process using several MB of memory. Going over the limit is a hard error:

<?php
// the server was started with max_connections = 20
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';
$connections = [];
try {
    for ($i = 1; $i <= 30; $i++) {
        $connections[] = new PDO($dsn, 'postgres', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    }
} catch (PDOException $e) {
    echo "connection $i failed: ", $e->getMessage(), "\n";
}

Output:

connection 21 failed: SQLSTATE[08006] [7] connection to server at "127.0.0.1", port 54917 failed: FATAL:  sorry, too many clients already

With persistent connections, the worst case number of open connections is:

web servers × FPM pools × pm.max_children × distinct DSN/user combinations

For example 4 servers × 1 pool × 50 children = 200 connections, even at 3 in the morning when the site is idle. Add cron jobs, queue workers and migrations on top. Without persistent connections the count is lower when traffic is low, but at peak it reaches the same number, since every busy FPM child holds one connection.

That's why serious PHP deployments of PostgreSQL usually put a pooler in front of the database.

External pooler: PgBouncer

PgBouncer is a small proxy that speaks the PostgreSQL protocol. PHP connects to PgBouncer exactly as if it were PostgreSQL, and PgBouncer multiplexes many client connections onto a few server connections. Connecting to PgBouncer is cheap (no new server process, often over a Unix socket on the same machine), so PHP can open a fresh, non-persistent connection for every request.

A minimal pgbouncer.ini:

[databases]
app = host=10.0.0.5 port=5432 dbname=app

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 2000      ; PHP connections PgBouncer accepts
default_pool_size = 20      ; real PostgreSQL connections per database/user pair
max_prepared_statements = 200

PHP only changes the port:

$pdo = new PDO('pgsql:host=127.0.0.1;port=6432;dbname=app', 'app', $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

The pool mode decides when a server connection goes back to the pool:

pool_mode server connection is returned notes
session when the client disconnects safe for everything, but saves little for PHP
transaction after every transaction (or statement outside one) the useful one; session state doesn't work
statement after every statement multi-statement transactions are not allowed

In transaction mode, two consecutive statements of one PHP request may run on different server connections. Anything tied to a session breaks: SET (use SET LOCAL inside a transaction instead), session advisory locks (use pg_advisory_xact_lock), temporary tables that outlive a transaction, LISTEN.

Prepared statements behind a pooler

By default pdo_pgsql creates real server-side prepared statements, named pdo_stmt_00000001 and so on. The statement exists only on the server connection where it was prepared:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';

foreach ([false, true] as $emulate) {
    $pdo = new PDO($dsn, 'postgres', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => $emulate,
    ]);
    $stmt = $pdo->prepare('SELECT :n::int * 2');
    $stmt->execute(['n' => 21]);
    $result = $stmt->fetchColumn();
    $names = $pdo->query('SELECT name FROM pg_prepared_statements')->fetchAll(PDO::FETCH_COLUMN);
    printf("emulate=%-5s result=%d server-side statements: %s\n",
        var_export($emulate, true), $result, $names ? implode(', ', $names) : '(none)');
}

Output:

emulate=false result=42 server-side statements: pdo_stmt_00000001, pdo_stmt_00000002
emulate=true  result=42 server-side statements: (none)

PgBouncer before 1.21 in transaction mode fails with errors like prepared statement "pdo_stmt_00000001" does not exist. You have two options:

For MySQL the equivalent tool is ProxySQL. Managed databases often include a pooler too (Amazon RDS Proxy, Supabase's Supavisor, Azure's built-in PgBouncer).

In-process pool for long-running PHP

Under Swoole, RoadRunner, FrankenPHP worker mode, ReactPHP or Amp, one PHP process serves many requests and lives for hours. Here a normal pool object in a long-lived variable works, and you need one: a single shared connection isn't enough once requests run concurrently (Fibers or coroutines), and a worker has to survive the database dropping its connection.

A pool needs to:

Here is a complete pool for PDO in about 100 lines:

<?php
final class PoolExhaustedException extends RuntimeException {}

final class ConnectionPool
{
    /** @var list<array{PDO, float}> idle connections and when they were returned */
    private array $idle = [];
    private int $open = 0;
    private int $waiting = 0;

    public function __construct(
        private readonly Closure $factory,
        private readonly int $maxSize = 10,
        private readonly float $maxIdleSeconds = 60.0,
        private readonly float $checkAfterSeconds = 5.0,
    ) {}

    public function acquire(): PDO
    {
        while (true) {
            // 1. reuse an idle connection (most recently used first)
            while ($entry = array_pop($this->idle)) {
                [$pdo, $returnedAt] = $entry;
                $idleFor = microtime(true) - $returnedAt;
                if ($idleFor > $this->maxIdleSeconds) {
                    $this->discard($pdo);
                    continue;
                }
                if ($idleFor > $this->checkAfterSeconds && !$this->isAlive($pdo)) {
                    $this->discard($pdo);
                    continue;
                }
                return $pdo;
            }
            // 2. open a new one if we're below the limit
            if ($this->open < $this->maxSize) {
                $pdo = ($this->factory)();
                $this->open++;
                return $pdo;
            }
            // 3. pool is full: inside a Fiber, yield and try again later
            if (Fiber::getCurrent() === null) {
                throw new PoolExhaustedException("all {$this->maxSize} connections are in use");
            }
            $this->waiting++;
            try {
                Fiber::suspend();
            } finally {
                $this->waiting--;
            }
        }
    }

    public function release(PDO $pdo): void
    {
        try {
            $this->reset($pdo);
            $this->idle[] = [$pdo, microtime(true)];
        } catch (PDOException) {
            $this->discard($pdo); // broken connection, don't reuse it
        }
    }

    /** Borrow a connection for the duration of $fn, always give it back. */
    public function with(callable $fn): mixed
    {
        $pdo = $this->acquire();
        try {
            return $fn($pdo);
        } finally {
            $this->release($pdo);
        }
    }

    public function stats(): array
    {
        return ['open' => $this->open, 'idle' => count($this->idle), 'waiting' => $this->waiting];
    }

    private function reset(PDO $pdo): void
    {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        $pdo->exec('DISCARD ALL'); // PostgreSQL: drop temp tables, session settings, locks, prepared plans
    }

    private function isAlive(PDO $pdo): bool
    {
        try {
            $pdo->query('SELECT 1');
            return true;
        } catch (PDOException) {
            return false;
        }
    }

    private function discard(PDO $pdo): void
    {
        $this->open--;
        // the connection closes when the last reference to $pdo goes away
    }
}

Design notes:

Using it sequentially, for example in a queue worker:

<?php
require 'Pool.php';

$pool = new ConnectionPool(
    factory: fn() => new PDO('pgsql:host=127.0.0.1;port=54917;dbname=postgres', 'postgres', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]),
    maxSize: 2,
);

$pids = [];
for ($job = 1; $job <= 5; $job++) {
    $pid = $pool->with(fn(PDO $db) => $db->query('SELECT pg_backend_pid()')->fetchColumn());
    $pids[$pid] = true;
    echo "job $job ran on backend $pid\n";
}
echo "distinct connections: ", count($pids), "\n";
print_r($pool->stats());

// state set by one job doesn't leak into the next
echo "default time zone:     ", $pool->with(fn(PDO $db) => $db->query('SHOW TIME ZONE')->fetchColumn()), "\n";
$pool->with(fn(PDO $db) => $db->exec("SET TIME ZONE 'Asia/Tokyo'"));
echo "time zone in next job: ", $pool->with(fn(PDO $db) => $db->query('SHOW TIME ZONE')->fetchColumn()), "\n";

// an exception inside with() still returns the connection, mid-transaction work is rolled back
try {
    $pool->with(function (PDO $db) {
        $db->beginTransaction();
        $db->exec('CREATE TABLE IF NOT EXISTS t (n int)');
        throw new RuntimeException('job failed');
    });
} catch (RuntimeException $e) {
    echo "caught: ", $e->getMessage(), "\n";
}
echo "table exists: ", var_export($pool->with(fn(PDO $db) => $db->query("SELECT to_regclass('t') IS NOT NULL")->fetchColumn()), true), "\n";
print_r($pool->stats());

// holding both connections and asking for a third outside a Fiber fails fast
$a = $pool->acquire();
$b = $pool->acquire();
try {
    $pool->acquire();
} catch (PoolExhaustedException $e) {
    echo get_class($e), ": ", $e->getMessage(), "\n";
}
$pool->release($a);
$pool->release($b);

Output:

job 1 ran on backend 20820
job 2 ran on backend 20820
job 3 ran on backend 20820
job 4 ran on backend 20820
job 5 ran on backend 20820
distinct connections: 1
Array
(
    [open] => 1
    [idle] => 1
    [waiting] => 0
)
default time zone:     Europe/Warsaw
time zone in next job: Europe/Warsaw
caught: job failed
table exists: false
Array
(
    [open] => 1
    [idle] => 1
    [waiting] => 0
)
PoolExhaustedException: all 2 connections are in use

Sequential jobs never need more than one connection. The SET TIME ZONE was undone by DISCARD ALL, and the failed job's transaction (including the CREATE TABLE, which is transactional in PostgreSQL) was rolled back.

Recovering from a dropped connection

The health check replaces a connection the server closed while it sat in the pool:

<?php
require 'Pool.php';

$dsn = 'pgsql:host=127.0.0.1;port=54917;dbname=postgres';
$pool = new ConnectionPool(
    factory: fn() => new PDO($dsn, 'postgres', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]),
    maxSize: 2,
    checkAfterSeconds: 0.5,
);

$pid = $pool->with(fn(PDO $db) => $db->query('SELECT pg_backend_pid()')->fetchColumn());
echo "first job on backend $pid\n";

// the database drops the idle connection (restart, failover, idle timeout)
(new PDO($dsn, 'postgres', ''))->query("SELECT pg_terminate_backend($pid)");
sleep(1);

$pid = $pool->with(fn(PDO $db) => $db->query('SELECT pg_backend_pid()')->fetchColumn());
echo "second job on backend $pid\n";
print_r($pool->stats());

Output:

first job on backend 25896
second job on backend 17088
Array
(
    [open] => 1
    [idle] => 1
    [waiting] => 0
)

Concurrent requests with Fibers

The pool earns its keep when requests run concurrently. In an async framework, a request suspends its Fiber while it waits for the database and other requests run in the meantime. When all connections are busy, acquire() suspends too and retries when it's resumed.

PDO itself is blocking, so this example simulates the waiting with Fiber::suspend() and drives the Fibers with a minimal round-robin scheduler. Real event loops (Revolt, used by Amp and ReactPHP) do the same thing with real I/O:

<?php
require 'Pool.php';

$opened = 0;
$pool = new ConnectionPool(
    factory: function () use (&$opened) {
        $opened++;
        return new PDO('pgsql:host=127.0.0.1;port=54917;dbname=postgres', 'postgres', '', [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]);
    },
    maxSize: 2,
);

// 5 concurrent "requests"; each one suspends while holding a connection,
// the way an async database driver yields while waiting for a result
$fibers = [];
for ($i = 1; $i <= 5; $i++) {
    $fibers[] = new Fiber(function () use ($pool, $i) {
        $pool->with(function (PDO $db) use ($pool, $i) {
            $pid = $db->query('SELECT pg_backend_pid()')->fetchColumn();
            echo "request $i got backend $pid, ", json_encode($pool->stats()), "\n";
            Fiber::suspend(); // pretend the query takes a while
        });
        echo "request $i done\n";
    });
}

// a minimal round-robin scheduler
foreach ($fibers as $f) {
    $f->start();
}
while ($fibers = array_filter($fibers, fn(Fiber $f) => !$f->isTerminated())) {
    foreach ($fibers as $f) {
        $f->resume();
    }
}
echo "connections opened: $opened\n";

Output:

request 1 got backend 36548, {"open":1,"idle":0,"waiting":0}
request 2 got backend 33548, {"open":2,"idle":0,"waiting":0}
request 1 done
request 2 done
request 3 got backend 33548, {"open":2,"idle":1,"waiting":2}
request 4 got backend 36548, {"open":2,"idle":0,"waiting":1}
request 3 done
request 4 done
request 5 got backend 36548, {"open":2,"idle":1,"waiting":0}
request 5 done
connections opened: 2

Five concurrent requests were served by two connections. Requests 3-5 waited until a connection was released. More on Fibers and event loops in Asynchronous programming.

Ready-made pools

In production use the pool that comes with your runtime rather than writing your own:

Which one to use

Setup Recommendation
PHP-FPM, small site, one server plain connections. Try ATTR_PERSISTENT if connect time shows up in profiles and you don't use session state
PHP-FPM, several servers or many FPM children PgBouncer in transaction mode (PostgreSQL) or ProxySQL (MySQL), non-persistent connections from PHP
Swoole, Amp, ReactPHP (concurrent requests in one process) the runtime's connection pool, sized well below the database's max_connections divided by the number of processes
Queue workers, CLI daemons one connection per worker, reconnect on failure, reset state between jobs

Whatever you choose, add up the maximum connections every component can open and keep the total below the database limit. That's the number that takes a site down at peak traffic.

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents