Connection pooling — PgBouncer, ProxySQL, why it matters

Concept

Connection pooling is the practice of maintaining a pool of pre-established database connections that can be reused across requests, rather than opening and closing a new connection for every request.

Why it matters: Establishing a database connection is expensive:

• TCP handshake.
• TLS negotiation (if encrypted).
• Authentication.
• Initial session setup.

For a typical MySQL connection, this takes 1-10ms. If you have 100 req/s, that's 100ms+ wasted per second just on connection setup. With pooling: connections are opened once, then handed out to requests and returned to the pool.

PHP's stateless model: PHP-FPM spawns worker processes. Each worker handles one request then becomes idle. Each worker maintains its own persistent connection (via pconnect / PDO persistent). This is process-level persistence — not true pooling, but similar effect.

PgBouncer (PostgreSQL): A standalone connection pooler. Sits between your app and PostgreSQL. Modes:

• Session mode: One real DB connection per client connection. Safe for all SQL features.
• Transaction mode: Pool connection is held only during a transaction. Very efficient. BUT: incompatible with prepared statements, SET statements, advisory locks.
• Statement mode: Connection returned to pool after every statement. Most efficient, most restrictive.

ProxySQL (MySQL): MySQL-specific proxy. Does connection pooling + query routing (send writes to primary, reads to replicas) + query caching.

Laravel persistent connections: 'options' => [PDO::ATTR_PERSISTENT => true] in config/database.php. Reuses PDO connection across requests in the same PHP-FPM worker. Simple option, no extra infrastructure.

When to add a pooler: When you see "too many connections" errors. When your connection count approaches max_connections (PostgreSQL default: 100). When you have many short-lived PHP workers.

Code Example

<?php
// Laravel persistent connections — config/database.php
'pgsql' => [
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST', '127.0.0.1'),
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'options'  => [
        \PDO::ATTR_PERSISTENT => true,  // reuse connection in same PHP-FPM worker
    ],
],

; PgBouncer config: /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432          ; app connects to 6432, PgBouncer connects to 5432
pool_mode   = transaction   ; most efficient for PHP apps
max_client_conn = 1000      ; max clients PgBouncer accepts
default_pool_size = 20      ; actual PostgreSQL connections (keep low!)
auth_type   = md5

# App connects to PgBouncer on port 6432 (not PostgreSQL's 5432)
# .env
DB_HOST=127.0.0.1
DB_PORT=6432

-- Monitor connections in PostgreSQL
SELECT count(*) FROM pg_stat_activity;  -- current connections
SHOW max_connections;                   -- limit

-- Monitor connections in MySQL
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- ProxySQL (MySQL) — view connection pool stats
SELECT * FROM stats_mysql_connection_pool;

<?php
// Signs you need connection pooling:
// 1. SQLSTATE[HY000] [1040] Too many connections (MySQL)
// 2. FATAL: remaining connection slots are reserved for non-replication superuser connections (PostgreSQL)
// 3. Connection time spikes under load
// 4. DB connections ≈ max_connections at peak traffic