Database Connection Pooling: PgBouncer, ProxySQL, and Application-Level Patterns#

Database connections are expensive resources. PostgreSQL forks a new OS process for every connection. MySQL creates a thread. Both allocate memory for session state, query buffers, and sort areas. When your application scales horizontally in Kubernetes – 10 pods, then 20, then 50 – the connection count multiplies, and most databases buckle long before your application pods do.

Connection pooling solves this by maintaining a smaller set of persistent connections to the database and sharing them across many application clients. Understanding pooling options, deployment patterns, and sizing is essential for any production database workload on Kubernetes.

The Connection Math#

Consider a typical Kubernetes deployment: 10 application pods, each configured with a connection pool of 20 connections. That is 200 connections to the database. Scale to 30 pods during peak traffic, and you are at 600 connections.

Most databases have practical connection limits far below what applications request. PostgreSQL defaults to max_connections = 100. AWS RDS for PostgreSQL defaults vary by instance size but are typically 100-400. Even with a large instance, each connection consumes memory (roughly 5-10MB per connection in PostgreSQL for work_mem, sort buffers, and session state). At 500 connections, that is 2.5-5GB of RAM consumed by connection overhead alone.

Without pooling, this math becomes untenable. Pooling reduces the number of actual database connections to a fraction of what applications request, multiplexing many client connections across a smaller pool of database connections.

PgBouncer (PostgreSQL)#

PgBouncer is the standard connection pooler for PostgreSQL. It sits between your application and PostgreSQL, accepting client connections and proxying them through a smaller pool of server connections.

Pool Modes#

PgBouncer supports three pool modes that determine when a server connection is returned to the pool:

Transaction mode (recommended). A server connection is assigned to a client for the duration of a single transaction and returned to the pool when the transaction completes. This provides the best multiplexing – 50 application connections can share 10 database connections if transactions are short. This is the right choice for web applications and microservices where each request runs one or a few short transactions.

Session mode. A server connection is held for the entire client session (until disconnect). This provides no multiplexing benefit over direct connections but can still be useful for connection queuing – PgBouncer will queue clients when the pool is full rather than rejecting them immediately.

Statement mode. A server connection is returned after each individual statement. This breaks multi-statement transactions and is rarely appropriate. Only use it for simple, single-query workloads where you know transactions are unnecessary.

Configuration#

Key PgBouncer settings in pgbouncer.ini:

[databases]
mydb = host=postgres.default.svc.cluster.local port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15
query_wait_timeout = 120
  • max_client_conn: Maximum total client connections PgBouncer will accept. Set this high – PgBouncer handles thousands of idle connections with minimal overhead.
  • default_pool_size: Number of server connections to maintain per database/user pair. This is the actual number of connections to PostgreSQL. Size this based on what PostgreSQL can handle.
  • reserve_pool_size: Extra connections allowed when the main pool is full and clients are waiting. Provides burst capacity.
  • server_lifetime: Maximum time (seconds) a server connection can live before being recycled. Prevents stale connections.
  • query_wait_timeout: How long a client waits for a free server connection before getting an error. Set this to match your application’s timeout expectations.

Deployment Patterns#

Sidecar container (per-pod). Deploy PgBouncer as a sidecar in each application pod. The application connects to localhost:6432. This is simple – each pod manages its own pool. The downside: each pod gets its own pool, so 10 pods with default_pool_size=20 still creates 200 database connections.

containers:
  - name: app
    image: myapp:latest
    env:
      - name: DATABASE_URL
        value: "postgresql://user:pass@localhost:6432/mydb"
  - name: pgbouncer
    image: bitnami/pgbouncer:latest
    ports:
      - containerPort: 6432
    env:
      - name: POSTGRESQL_HOST
        value: "postgres.default.svc.cluster.local"
      - name: PGBOUNCER_POOL_MODE
        value: "transaction"
      - name: PGBOUNCER_DEFAULT_POOL_SIZE
        value: "10"

Centralized deployment. Deploy PgBouncer as a standalone Deployment with a Service. All application pods connect to the PgBouncer Service. This gives you a single pool shared across all applications, providing the best connection multiplexing. The downside: PgBouncer becomes a single point of failure (deploy multiple replicas behind the Service) and adds network hops.

For the centralized pattern, set default_pool_size to the total connections you want to PostgreSQL (for example, 50), and let all application pods share that pool.

Monitoring#

PgBouncer exposes stats through a virtual pgbouncer database:

-- Connect to PgBouncer admin
psql -p 6432 -U pgbouncer pgbouncer

SHOW STATS;     -- request counts, bytes, durations per database
SHOW POOLS;     -- active, waiting, server connections per pool
SHOW CLIENTS;   -- connected clients and their state
SHOW SERVERS;   -- backend server connections and their state

Key metrics to watch: cl_waiting (clients waiting for a connection – non-zero means pool exhaustion), sv_active vs sv_idle (active vs idle server connections), and avg_query_time (increasing query time may indicate database overload).

ProxySQL (MySQL)#

ProxySQL is the MySQL equivalent of PgBouncer, with additional features for query routing and caching.

Connection Multiplexing#

Like PgBouncer, ProxySQL maintains a pool of connections to MySQL backends and multiplexes client connections across them. Configure the pool size per backend server in the mysql_servers table.

Read-Write Splitting#

ProxySQL’s most powerful feature is query routing. Define hostgroups for the primary (writes) and replicas (reads), then create query rules:

-- Hostgroup 10: primary (read-write)
-- Hostgroup 20: replicas (read-only)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (10, 'mysql-primary.default.svc', 3306),
  (20, 'mysql-replica-0.default.svc', 3306),
  (20, 'mysql-replica-1.default.svc', 3306);

-- Route SELECTs to replicas, everything else to primary
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) VALUES
  (1, '^SELECT.*FOR UPDATE', 10),
  (2, '^SELECT', 20);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

This transparently splits read traffic to replicas without application changes. SELECT ... FOR UPDATE is routed to the primary because it acquires locks.

Configuration#

ProxySQL uses an admin interface (MySQL protocol on port 6032) for runtime configuration. Changes are applied with LOAD ... TO RUNTIME and persisted with SAVE ... TO DISK. Key settings:

  • mysql-max_connections: Total backend connections
  • mysql-connection_max_age_ms: Recycle connections after this age
  • mysql-query_retries_on_failure: Automatic retry on connection failure

Application-Level Pooling#

Most programming languages and frameworks include built-in connection pooling.

HikariCP (Java) is the standard for JVM applications. It is fast, lightweight, and the default pool for Spring Boot. Key settings: maximumPoolSize (max connections), minimumIdle (min idle connections), connectionTimeout (max wait for a connection), maxLifetime (max connection age).

SQLAlchemy (Python) includes a connection pool by default. Configure with pool_size (number of connections), max_overflow (additional connections beyond pool_size), pool_timeout (seconds to wait for a connection), pool_recycle (seconds before recycling a connection).

database/sql (Go) manages a pool natively. Configure with SetMaxOpenConns (max connections), SetMaxIdleConns (max idle connections), SetConnMaxLifetime (max connection age).

Pool sizing formula from the PostgreSQL wiki: pool_size = (number_of_cores * 2) + spindle_count. For SSDs (spindle count effectively 0), this simplifies to pool_size = cores * 2. For a 4-core database server, start with a pool of 8-10 connections. This formula applies to the total connection count at the database, not per-pod.

Pool Sizing Strategy#

Pool sizing is a balancing act:

  • Too small: Application threads wait for connections. Response times increase. Under load, connection wait time dominates request latency.
  • Too large: Database is overwhelmed. Each connection consumes memory. Context switching between hundreds of active connections degrades database performance.

Start with 5-10 connections per application pod in sidecar mode, or 20-50 total connections in centralized mode. Monitor wait times and database performance, then adjust. The goal is zero wait time for connections with the minimum number of database connections.

For centralized PgBouncer: default_pool_size should roughly equal the number of concurrent transactions your database can sustain. With a 4-core PostgreSQL instance, this is typically 20-50.

Kubernetes Patterns#

PgBouncer sidecar – simplest deployment, pod-level pooling. Each pod has its own PgBouncer. Good when you have a small number of pods and the total connection count is manageable.

PgBouncer Deployment – cluster-level pooling. A Deployment with 2-3 replicas behind a Service. All application pods connect to this Service. Best for large deployments where per-pod pooling creates too many database connections.

CloudNativePG built-in pooler – if you use the CloudNativePG operator for PostgreSQL on Kubernetes, it includes an integrated PgBouncer pooler that is configured as part of the PostgreSQL cluster CRD. This is the simplest option when using CloudNativePG.

Cloud Managed Pooling#

AWS RDS Proxy sits between your application and RDS/Aurora. It handles connection pooling, failover, and IAM authentication. It supports PostgreSQL and MySQL. RDS Proxy adds cost (per-vCPU pricing) but eliminates the operational overhead of managing PgBouncer or ProxySQL.

Azure PgBouncer is built into Azure Database for PostgreSQL Flexible Server. Enable it in the server settings – no separate deployment needed. It runs PgBouncer in transaction mode by default.

GCP Cloud SQL Auth Proxy handles authentication and TLS for Cloud SQL but does not provide connection pooling. You still need PgBouncer or application-level pooling alongside it.

Common Gotchas#

Transaction pooling breaks prepared statements. In PgBouncer transaction mode, each transaction may use a different server connection. Prepared statements are scoped to a connection, so a statement prepared in one transaction may not exist in the next. Similarly, session-level settings (SET search_path, SET timezone), advisory locks, and LISTEN/NOTIFY do not work across transaction boundaries. If your application relies on these, use session mode (sacrificing multiplexing) or refactor to avoid session-level state.

Pool exhaustion causes application hangs. When all connections in the pool are in use and a new request needs one, the request blocks until a connection is freed or the timeout expires. If the timeout is long (or infinite), the application appears to hang. Always configure a query_wait_timeout (PgBouncer) or connectionTimeout (HikariCP) and monitor the number of waiting clients. Alert on non-zero wait queues before they become user-facing outages.

Sidecar pools multiply total connections. A common mistake: deploying PgBouncer sidecars with 20 connections each across 30 pods, creating 600 database connections. The sidecar pattern provides per-pod isolation but does not reduce total connection count unless each sidecar’s pool is small (2-5 connections). For large deployments, prefer centralized pooling.