Connection Pooling on FoundryDB: PgBouncer and ProxySQL Deep Dive

Every database connection costs memory. PostgreSQL forks a backend process per connection (roughly 5-10 MB each), and MySQL allocates per-thread buffers that scale with your buffer configuration. At 200 concurrent connections, the overhead is manageable. At 2,000, you are spending gigabytes of RAM on connection state instead of query execution.

Connection pooling sits between your application and the database, multiplexing many client connections over a small number of backend connections. FoundryDB provides PgBouncer for PostgreSQL and ProxySQL for MySQL, both managed through the API with no manual SSH or config file editing required.

Why Connection Pooling Matters

Without a pooler, every application process (every serverless function invocation, every container replica, every worker thread) opens its own connection to the database. This creates two problems:

• Connection overhead. Each backend connection consumes memory and CPU regardless of whether it is actively running a query. At scale, you hit max_connections limits before you hit actual query throughput limits.
• Connection storms. When an application scales horizontally (auto-scaling containers, Lambda cold starts), dozens of connections open simultaneously. The database spends more time authenticating and forking processes than answering queries.

A pooler solves both by maintaining a fixed pool of backend connections and assigning them to client requests on demand. Your application can open 5,000 client connections while the database sees only 50 active backends.

PgBouncer for PostgreSQL

PgBouncer runs on every node of your PostgreSQL service, listening on port 6432. Enable it with a single API call.

Enabling PgBouncer

curl -u user:password -X POST \
  https://api.foundrydb.com/managed-services/{serviceId}/pooler/enable \
  -H "Content-Type: application/json" \
  -d '{
    "database_name": "defaultdb",
    "database_user": "app_user",
    "pool_mode": "transaction",
    "default_pool_size": 25,
    "max_client_conn": 2000
  }'

The database_user must be an existing application user (create one via the Users API first). Superuser accounts like postgres and internal system users are blocked for security.

The response returns a list of task_ids, one per node. PgBouncer is ready once all tasks reach completed status.

Pool Modes

The pool_mode parameter controls when a backend connection is returned to the pool.

Mode	Backend returned after	Best for	Caveats
transaction	Each transaction commits/rolls back	Web apps, APIs, microservices	Cannot use SET, LISTEN/NOTIFY, or session-level advisory locks
session	Client disconnects	Apps that rely on session state, prepared statements, temp tables	Lower multiplexing ratio, needs more backend connections
statement	Each statement completes	Simple read-only workloads	Incompatible with multi-statement transactions

Start with transaction mode. It provides the best multiplexing ratio for most web applications. Switch to session only if your application explicitly uses session-scoped features like SET search_path, advisory locks, or temporary tables.

Tuning Pool Size

The default_pool_size sets how many backend connections PgBouncer keeps open per (user, database) pair. The right value depends on your workload.

Too small: Clients queue waiting for a backend connection. You will see max_wait_ms climb in the stats output and query latency increase.

Too large: You lose the benefit of pooling. The database handles nearly as many connections as it would without a pooler.

A good starting point: set default_pool_size to your PostgreSQL max_connections divided by the number of (user, database) pairs you are pooling, leaving 10-20% headroom for direct admin connections.

# Update pool size and timeouts without restarting PgBouncer
curl -u user:password -X PATCH \
  https://api.foundrydb.com/managed-services/{serviceId}/pooler/config \
  -H "Content-Type: application/json" \
  -d '{
    "default_pool_size": 40,
    "server_idle_timeout_seconds": 600,
    "query_wait_timeout_seconds": 120
  }'

After updating, reload PgBouncer to apply the changes to all nodes without dropping active connections:

curl -u user:password -X POST \
  https://api.foundrydb.com/managed-services/{serviceId}/pooler/reload

ProxySQL for MySQL

ProxySQL serves the same purpose for MySQL, listening on port 6033 by default. It adds connection multiplexing and built-in health monitoring.

Enabling ProxySQL

curl -u user:password -X POST \
  https://api.foundrydb.com/managed-services/{serviceId}/proxysql/enable \
  -H "Content-Type: application/json" \
  -d '{
    "database_user": "app_user",
    "listen_port": 6033,
    "max_connections": 500
  }'

Like PgBouncer, the database_user must be an existing application user. System accounts (root, admin, mdb_replication, proxysql_monitor, and others) are blocked.

Tuning ProxySQL

ProxySQL exposes a rich configuration surface. The most common tuning parameters:

curl -u user:password -X PATCH \
  https://api.foundrydb.com/managed-services/{serviceId}/proxysql/config \
  -H "Content-Type: application/json" \
  -d '{
    "max_connections": 1000,
    "default_max_connections": 200,
    "default_query_timeout_ms": 30000,
    "connect_timeout_ms": 5000,
    "multiplexing_enabled": true
  }'

Parameter	Default	Description
max_connections	200	Total maximum backend connections across all servers
default_max_connections	200	Max connections per backend server
default_query_timeout_ms	0 (disabled)	Kill queries exceeding this threshold (max 86400000)
connect_timeout_ms	1000	Timeout for establishing backend connections (100-60000)
multiplexing_enabled	true	Reuse backend connections across client sessions
monitor_enabled	true	ProxySQL health checks on backend servers
ping_interval_ms	10000	How often to ping backends for health checks

After changes, reload ProxySQL to apply them at runtime:

curl -u user:password -X POST \
  https://api.foundrydb.com/managed-services/{serviceId}/proxysql/reload

Monitoring Pool Statistics

Both poolers expose real-time statistics through the API.

PgBouncer Stats

# Request stats collection (returns task_ids for polling)
curl -u user:password \
  https://api.foundrydb.com/managed-services/{serviceId}/pooler/stats

The response includes per-node metrics: active_clients, waiting_clients, active_servers, idle_servers, max_wait_ms, and avg_query_time_us. Watch waiting_clients and max_wait_ms closely. If clients are consistently waiting, increase default_pool_size.

ProxySQL Stats

curl -u user:password \
  https://api.foundrydb.com/managed-services/{serviceId}/proxysql/stats

ProxySQL stats include total_connections, active_connections, idle_connections, connection_errors, and per-server breakdowns with connections_used, connections_free, and query counts.

When to Use Pooling vs. Direct Connections

Connection pooling is not always the right answer. Here is a quick decision guide.

Use a pooler when:

• Your application opens more than 100 concurrent connections
• You run serverless functions or auto-scaling containers that create short-lived connections
• You want to protect the database from connection storms during deployment rollouts
• Your application framework does not include built-in connection pooling

Skip the pooler when:

• You have fewer than 50 total connections and they are long-lived
• Your application relies heavily on session-level features (prepared statements, temp tables, LISTEN/NOTIFY) and cannot use transaction mode
• You are running batch jobs that hold connections for minutes at a time, where pooling provides no benefit

Application-level poolers vs. FoundryDB poolers: If your framework already provides connection pooling (like HikariCP for Java or pgx pool for Go), you can still benefit from a server-side pooler. The application pool handles connection lifecycle in your process, while PgBouncer/ProxySQL handles multiplexing at the database level. They complement each other, especially in multi-service architectures where several applications share one database.

What's Next

• PostgreSQL documentation for connection strings and TLS configuration
• Read replicas guide to combine pooling with horizontal read scaling
• Connection pooling API reference for the full list of configuration parameters

Connection pooling is one of the highest-impact optimizations you can make with minimal application changes. On FoundryDB, it takes one API call to enable and a few parameters to tune. Start with transaction mode for PostgreSQL or multiplexing_enabled: true for MySQL, monitor the stats for a week, and adjust from there.

Create a free FoundryDB service and enable connection pooling in under five minutes.