System Design (HLD)Connection Pooling

Connection Pooling: Managing Database Connections at Scale

LevelIntermediate

Duration75 mins

TopicConnection Pooling

4 / 5

PgBouncer, ProxySQL

The Need for External Connection Poolers

Application-level connection pools work well for single-process applications, but modern production systems rarely consist of a single process. When you have 50 Kubernetes pods, 100 serverless functions, or 20 microservices all connecting to the same database, application-level pooling becomes insufficient—or even harmful. The problem: Each application instance maintains its own pool. If each has 20 connections: 50 pods \times 20 connections = 1,000 potential connections This exceeds most databases' optimal capacity Database performance degrades under connection weight The solution: External connection poolers sit between applications and databases, aggregating many application connections into a smaller number of database connections. This is where PgBouncer (PostgreSQL) and ProxySQL (MySQL) become essential infrastructure.

What You Will Learn

By the end of this page, you will understand PgBouncer and ProxySQL architectures, pooling modes, production configurations, deployment patterns, and operational considerations. You'll know when to deploy external poolers and how to configure them for different workload patterns.

PgBouncer for PostgreSQL

PgBouncer is a lightweight connection pooler for PostgreSQL. Despite its simple design, it's battle-tested at massive scale—used by GitLab, Discord, and many other high-traffic PostgreSQL deployments. Architecture: PgBouncer operates as a proxy between applications and PostgreSQL. Applications connect to PgBouncer (typically on port 6432), which then maintains a pool of connections to the actual database.

┌─────────────────────────────────────────────────────────────────┐
│                        Applications                             │
├───────────┬──────────┬──────────┬──────────┬──────────┬────────┤
│  Pod 1    │  Pod 2   │  Pod 3   │  Pod 4   │  Pod 5   │  ...   │
│ (pool:5)  │ (pool:5) │ (pool:5) │ (pool:5) │ (pool:5) │        │
└─────┬─────┴────┬─────┴────┬─────┴────┬─────┴────┬─────┴────────┘
      │          │          │          │          │
      └──────────┴──────────┼──────────┴──────────┘
                            │
                   50+ application connections
                            │
                            ▼
            ┌───────────────────────────────┐
            │         PgBouncer             │
            │   Connection Multiplexer      │
            │                               │
            │  Maintains ~30 DB connections │
            │  for 500+ app connections     │
            └─────────────┬─────────────────┘
                          │
                   30 database connections
                          │
                          ▼
            ┌───────────────────────────────┐
            │      PostgreSQL Server        │
            │   (max_connections = 100)     │
            └───────────────────────────────┘

Key PgBouncer features:

PgBouncer Strengths

•Extremely lightweight — Uses ~2KB memory per connection. Can handle thousands of client connections on minimal hardware.
•Single-threaded, event-driven — Simple architecture that's easy to understand and debug. Scales vertically well on modern CPUs.
•Multiple pooling modes — Session, transaction, and statement pooling for different use cases.
•Online reconfiguration — Most configuration changes can be applied without disconnecting clients.
•Administrative console — Built-in SQL-like console for monitoring and control.
•Pause/Resume — Can pause traffic for maintenance without dropping connections.
•Database routing — Can route to different backends based on database name.

Why PostgreSQL Especially Needs PgBouncer

PostgreSQL forks a new process for each client connection (unlike MySQL's thread-per-connection). This makes PostgreSQL connections particularly expensive—each process consumes 1-10MB of memory and adds context switching overhead. PgBouncer is almost universally recommended for production PostgreSQL deployments with more than a few application instances.

PgBouncer Pooling Modes

PgBouncer offers three pooling modes, each with different tradeoffs. Choosing the right mode depends on your application's usage patterns. Session Pooling (pool_mode = session):

Client connects → Gets dedicated server connection
  All queries use SAME server connection
  Prepared statements work ✓
  Session variables work ✓
  SET statements persist ✓
  LISTEN/NOTIFY works ✓
Client disconnects → Server connection returned to pool
 
Efficiency: LOW (1:1 client:server while connected)
Compatibility: HIGH (full PostgreSQL features)
Best for: Applications requiring session state

Transaction Pooling (pool_mode = transaction):

Client connects → No server connection yet
Client starts transaction → Gets server connection
  All queries in transaction use SAME connection
  Can use prepared statements WITHIN transaction ✓
  SET LOCAL works within transaction ✓
Transaction ends → Server connection returned to pool
Client can do another transaction → Gets potentially DIFFERENT server
 
Efficiency: HIGH (many clients share few servers)
Compatibility: MEDIUM (no session state between transactions)
Best for: Most web applications, stateless query patterns
 
LIMITATIONS:
- Prepared statements not shared across transactions
- SET (without LOCAL) doesn't persist
- LISTEN/NOTIFY doesn't work
- Advisory locks don't persist

Statement Pooling (pool_mode = statement):

Client sends query → Gets server connection for ONE query
Query completes → Server connection immediately returned
Next query → May get DIFFERENT server connection
 
Efficiency: HIGHEST (maximum connection sharing)
Compatibility: LOW (no multi-statement transactions!)
 
SEVERE LIMITATIONS:
- Transactions NOT supported (autocommit only)
- Each statement may run on different server
- Only for simple, independent queries
 
Best for: Simple analytics dashboards, read-only APIs

PgBouncer Mode Comparison
Mode	Efficiency	Transactions	Prepared Stmts	Session State	Use Case
Session	Low (1:1)	Full support	Works	Preserved	Legacy apps, full compatibility
Transaction	High (N:1)	Works	Per-transaction	Not preserved	Most web apps (recommended)
Statement	Highest	Not supported	Not supported	Not preserved	Simple read queries only

Standard Recommendation

Transaction mode is the default recommendation for most applications. It provides excellent connection efficiency while supporting transactions—the most common pattern in web applications. Only use session mode if your application specifically requires session-level features like prepared statements cached across transactions or LISTEN/NOTIFY.

PgBouncer Production Configuration

A well-configured PgBouncer instance can handle thousands of connections reliably. Here's a production-grade configuration with explanations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
;; Database configuration
[databases]
; Format: logical_name = connection_string
myapp = host=primary.db.internal port=5432 dbname=myapp
myapp_readonly = host=replica.db.internal port=5432 dbname=myapp
 
; Wildcard for dynamic routing (use with caution)
; * = host=db.internal port=5432
 
[pgbouncer]
;;; Connection settings
listen_addr = 0.0.0.0           ; Listen on all interfaces
listen_port = 6432              ; Standard PgBouncer port
unix_socket_dir = /var/run/pgbouncer
 
;;; Authentication
auth_type = scram-sha-256       ; Strong auth (or md5 for compatibility)
auth_file = /etc/pgbouncer/userlist.txt
; auth_query for auth from database (preferred for many users):
; auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
 
;;; Pool configuration
pool_mode = transaction          ; Transaction pooling (recommended)
default_pool_size = 25           ; Connections per user/database pair
min_pool_size = 5                ; Minimum connections to keep open
reserve_pool_size = 5            ; Extra connections for peaks
reserve_pool_timeout = 3         ; Seconds before using reserve
 
;;; Connection limits
max_client_conn = 1000           ; Maximum client connections
max_db_connections = 50          ; Maximum connections to each database
max_user_connections = 50        ; Maximum connections per user
 
;;; Timeouts
server_connect_timeout = 10      ; Seconds to connect to server
server_login_retry = 3           ; Seconds between connection retries
server_lifetime = 3600           ; Seconds before server conn recycled
server_idle_timeout = 600        ; Seconds before idle server closed
client_idle_timeout = 0          ; 0 = no client idle timeout
client_login_timeout = 60        ; Seconds for client auth
 
;;; Query handling
query_timeout = 0                ; 0 = no query timeout (handled by app)
query_wait_timeout = 120         ; Seconds to wait for server connection
 
;;; TLS configuration
server_tls_sslmode = require     ; Encrypt to PostgreSQL
; server_tls_ca_file = /etc/ssl/certs/ca.pem
client_tls_sslmode = require     ; Encrypt from applications
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
 
;;; Logging and monitoring
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60                ; Stats logging interval
admin_users = pgbouncer_admin    ; Users allowed admin access
 
;;; Process settings
pidfile = /var/run/pgbouncer/pgbouncer.pid
logfile = /var/log/pgbouncer/pgbouncer.log
; For systemd: logfile = /dev/null and use journald

; Format: "username" "password_hash"
; Generate hash: echo -n "password" | md5sum, prefix with "md5"
; Or for scram-sha-256, copy from PostgreSQL pg_authid
 
"appuser" "SCRAM-SHA-256$4096:salt$client_key:server_key"
"readonly" "SCRAM-SHA-256$4096:salt$client_key:server_key"
"admin" "SCRAM-SHA-256$4096:salt$client_key:server_key"

Critical Configuration Points

max_db_connections is the key setting—this limits how many connections PgBouncer opens to your database. Set it to roughly (database_cores \times 2), leaving room for admin connections. max_client_conn can be much higher since PgBouncer is efficient with client connections.

ProxySQL for MySQL

ProxySQL is a high-performance MySQL proxy and connection pooler. It's significantly more feature-rich than PgBouncer, offering query caching, query routing, and sophisticated traffic management. Architecture: ProxySQL sits between applications and MySQL servers, providing connection multiplexing plus additional features like read/write splitting and query caching.

ProxySQL Features

•Connection multiplexing — Share backend connections across many client connections.
•Read/write splitting — Route reads to replicas, writes to primary automatically.
•Query caching — Cache query results in memory for repeated queries.
•Query rules — Route, rewrite, or block queries based on patterns.
•Load balancing — Distribute queries across multiple backend servers.
•Connection limiting — Limit connections per user, per server, or globally.
•Query mirroring — Send queries to multiple backends for testing.
•SQL firewall — Block dangerous queries by pattern matching.
•Failover support — Automatic detection and handling of backend failures.

ProxySQL vs PgBouncer:

ProxySQL vs PgBouncer Comparison
Feature	ProxySQL (MySQL)	PgBouncer (PostgreSQL)
Connection pooling	Yes	Yes
Read/write splitting	Yes (built-in)	No (use separate entries)
Query caching	Yes	No
Query rewriting	Yes	No
Runtime reconfiguration	Yes (SQL interface)	Yes (RELOAD)
Resource usage	Moderate	Very low
Complexity	Higher	Lower
Monitoring	Rich metrics	Basic stats

When to Use ProxySQL

Use ProxySQL for MySQL when you need connection pooling plus any of: read/write splitting, query caching, query routing rules, or sophisticated traffic management. For basic connection pooling only, MySQL's thread pool plugin or application-level pooling may suffice.

ProxySQL Production Configuration

ProxySQL configuration is done through its admin interface using SQL commands. Here's a complete setup for a typical production deployment with read/write splitting.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- Connect to ProxySQL admin interface
-- mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> '
 
-- =====================================================
-- STEP 1: Configure Backend MySQL Servers
-- =====================================================
 
-- Add primary server (hostgroup 0)
INSERT INTO mysql_servers (
    hostgroup_id, hostname, port, weight, 
    max_connections, max_replication_lag
) VALUES 
    (0, 'primary.db.internal', 3306, 1000, 100, 0);
 
-- Add replica servers (hostgroup 1 for reads)
INSERT INTO mysql_servers (
    hostgroup_id, hostname, port, weight,
    max_connections, max_replication_lag
) VALUES 
    (1, 'replica1.db.internal', 3306, 1000, 100, 10),
    (1, 'replica2.db.internal', 3306, 1000, 100, 10);
 
-- =====================================================
-- STEP 2: Configure MySQL Users
-- =====================================================
 
-- Add application user (frontend)
INSERT INTO mysql_users (
    username, password, default_hostgroup,
    max_connections, transaction_persistent
) VALUES 
    ('appuser', 'hashed_password', 0, 500, 1);
 
-- =====================================================
-- STEP 3: Configure Query Rules for Read/Write Split
-- =====================================================
 
-- Route SELECT queries to read hostgroup (1)
INSERT INTO mysql_query_rules (
    rule_id, active, match_pattern, destination_hostgroup,
    apply
) VALUES 
    (10, 1, '^SELECT.*FOR UPDATE', 0, 1),  -- FOR UPDATE goes to primary
    (20, 1, '^SELECT', 1, 1);               -- Other SELECTs to replicas
 
-- Everything else goes to default hostgroup (0 = primary)
 
-- =====================================================
-- STEP 4: Global Variables Configuration
-- =====================================================
 
-- Connection pooling settings
UPDATE global_variables SET variable_value='true' 
    WHERE variable_name='mysql-connection_pooling_enabled';
 
-- Connection limits
UPDATE global_variables SET variable_value='2000' 
    WHERE variable_name='mysql-max_connections';
 
-- Connection timeouts
UPDATE global_variables SET variable_value='10000' 
    WHERE variable_name='mysql-connect_timeout_server';
UPDATE global_variables SET variable_value='30000' 
    WHERE variable_name='mysql-wait_timeout';
 
-- Multiplexing configuration
UPDATE global_variables SET variable_value='true' 
    WHERE variable_name='mysql-multiplexing';
UPDATE global_variables SET variable_value='true' 
    WHERE variable_name='mysql-connection_pooling_enabled';
 
-- Query caching (optional)
UPDATE global_variables SET variable_value='true' 
    WHERE variable_name='mysql-query_cache_size_MB';
 
-- =====================================================
-- STEP 5: Apply Configuration
-- =====================================================
 
-- Load configuration to runtime
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
 
-- Save to disk for persistence across restarts
SAVE MYSQL USERS TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL VARIABLES TO DISK;

ProxySQL's Runtime Reconfiguration

ProxySQL's killer feature is runtime reconfiguration . You can add/remove servers, change query rules, and update users without restarting or disconnecting clients. This enables zero-downtime maintenance and dynamic scaling.

Cloud-Managed Alternatives

Major cloud providers offer managed connection pooling proxies, eliminating operational overhead for teams using their database services. AWS RDS Proxy:

AWS RDS Proxy Features

•Fully managed — No servers to manage, automatic scaling and patching.
•IAM authentication — Integrates with AWS IAM for secure credential management.
•Automatic failover — Seamlessly handles primary/replica failover.
•Essential for Lambda — Eliminates connection explosion from serverless functions.
•Connection sharing — Efficiently multiplexes connections from many sources.
•Pay per connection — Pricing based on connections, not fixed infrastructure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// Connection string uses RDS Proxy endpoint instead of direct RDS
const connectionString = `postgresql://${process.env.DB_USER}:${process.env.DB_PASSWORD}@myproxy.proxy-xxx.us-east-1.rds.amazonaws.com:5432/myapp`;
 
// With IAM authentication
import { RDSDataClient, ExecuteStatementCommand } from "@aws-sdk/client-rds-data";
 
const client = new RDSDataClient({ region: "us-east-1" });
 
// Using Data API (no connection management needed)
const command = new ExecuteStatementCommand({
  resourceArn: "arn:aws:rds:us-east-1:123456789:cluster:mycluster",
  secretArn: "arn:aws:secretsmanager:us-east-1:123456789:secret:mydbsecret",
  sql: "SELECT * FROM users WHERE id = :id",
  parameters: [{ name: "id", value: { longValue: userId } }],
});
 
const result = await client.send(command);

Other cloud options:

Cloud Connection Pooling Options
Provider	Service	Databases	Key Features
AWS	RDS Proxy	PostgreSQL, MySQL	Serverless integration, IAM auth, automatic failover
Google Cloud	Cloud SQL Auth Proxy	PostgreSQL, MySQL, SQL Server	Secure tunnel, IAM auth, sidecar pattern
Azure	Built into Flexible Server	PostgreSQL, MySQL	PgBouncer built-in, simple enable/disable
Prisma	Prisma Accelerate	Multiple	Edge caching, connection pooling, query caching
PlanetScale	Built-in	MySQL (Vitess)	Automatic horizontal scaling, serverless-native

When to Use Managed vs Self-Hosted

Use managed poolers when: using serverless functions, running on that cloud provider, want minimal operational overhead, budget allows premium pricing. Self-host PgBouncer/ProxySQL when: running on-premises, cost-sensitive, need fine-grained control, cross-cloud scenarios, or using features not in managed offerings.

Deployment Patterns

There are several ways to deploy external connection poolers, each with tradeoffs. Pattern 1: Standalone Pooler Cluster Dedicated servers/pods running the pooler, separate from applications.

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│   App Pod   │  │   App Pod   │  │   App Pod   │
└──────┬──────┘  └──────┬──────┘  └──────┬──────┘
       │                │                │
       └────────────────┼────────────────┘
                        │
              ┌─────────┴─────────┐
              │   Load Balancer   │
              └─────────┬─────────┘
                        │
       ┌────────────────┼────────────────┐
       │                │                │
┌──────┴──────┐  ┌──────┴──────┐  ┌──────┴──────┐
│  PgBouncer  │  │  PgBouncer  │  │  PgBouncer  │
│   Pod 1     │  │   Pod 2     │  │   Pod 3     │
└──────┬──────┘  └──────┬──────┘  └──────┬──────┘
       │                │                │
       └────────────────┼────────────────┘
                        │
              ┌─────────┴─────────┐
              │    PostgreSQL     │
              └───────────────────┘
 
Pros: Centralized management, clear separation
Cons: Additional load balancer, network hop

Pattern 2: Sidecar Pattern Pooler runs as a sidecar container alongside each application pod.

┌────────────────────────────────────────────────────┐
│                   Kubernetes Pod                   │
│  ┌──────────────────────┐  ┌────────────────────┐  │
│  │    App Container     │  │ PgBouncer Sidecar  │  │
│  │                      ├──┤                    │  │
│  │  connects to         │  │  connects to DB    │  │
│  │  localhost:6432      │  │  over network      │  │
│  └──────────────────────┘  └─────────┬──────────┘  │
└──────────────────────────────────────┼─────────────┘
                                       │
                              ┌────────┴────────┐
                              │   PostgreSQL    │
                              └─────────────────┘
 
Pros: No network hop for app→pooler, easy per-pod configuration
Cons: More pooler instances, each maintains connections

Pattern 3: Database-Adjacent Pooler runs on the same machine/network as the database.

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│   App Pod   │  │   App Pod   │  │   App Pod   │
└──────┬──────┘  └──────┬──────┘  └──────┬──────┘
       │                │                │
       └────────────────┼────────────────┘
                        │
                        ▼
       ┌─────────────────────────────────────────┐
       │          Database Server                │
       │  ┌─────────────────────────────────┐    │
       │  │         PgBouncer               │    │
       │  │    (localhost / unix socket)    │    │
       │  └────────────┬────────────────────┘    │
       │               │                         │
       │  ┌────────────┴────────────────────┐    │
       │  │         PostgreSQL              │    │
       │  │    (unix socket connection)     │    │
       │  └─────────────────────────────────┘    │
       └─────────────────────────────────────────┘
 
Pros: Minimal latency between pooler and DB
Cons: Couples pooler lifecycle to DB, single point of failure

Recommended Pattern

For Kubernetes: Standalone pooler cluster with 2-3 replicas, fronted by a ClusterIP service. This provides high availability, centralized management, and clear separation between application and data layers. Use HPA to scale pooler pods based on connection count.

4 / 5

┌─────────────────────────────────────────────────────────────────┐ │ Applications │ ├───────────┬──────────┬──────────┬──────────┬──────────┬────────┤ │ Pod 1 │ Pod 2 │ Pod 3 │ Pod 4 │ Pod 5 │ ... │ │ (pool:5) │ (pool:5) │ (pool:5) │ (pool:5) │ (pool:5) │ │ └─────┬─────┴────┬─────┴────┬─────┴────┬─────┴────┬─────┴────────┘ │ │ │ │ │ └──────────┴──────────┼──────────┴──────────┘ │ 50+ application connections │ ▼ ┌───────────────────────────────┐ │ PgBouncer │ │ Connection Multiplexer │ │ │ │ Maintains ~30 DB connections │ │ for 500+ app connections │ └─────────────┬─────────────────┘ │ 30 database connections │ ▼ ┌───────────────────────────────┐ │ PostgreSQL Server │ │ (max_connections = 100) │ └───────────────────────────────┘

Client connects → Gets dedicated server connection All queries use SAME server connection Prepared statements work ✓ Session variables work ✓ SET statements persist ✓ LISTEN/NOTIFY works ✓ Client disconnects → Server connection returned to pool Efficiency: LOW (1:1 client:server while connected) Compatibility: HIGH (full PostgreSQL features) Best for: Applications requiring session state

Client connects → No server connection yet Client starts transaction → Gets server connection All queries in transaction use SAME connection Can use prepared statements WITHIN transaction ✓ SET LOCAL works within transaction ✓ Transaction ends → Server connection returned to pool Client can do another transaction → Gets potentially DIFFERENT server Efficiency: HIGH (many clients share few servers) Compatibility: MEDIUM (no session state between transactions) Best for: Most web applications, stateless query patterns LIMITATIONS: - Prepared statements not shared across transactions - SET (without LOCAL) doesn't persist - LISTEN/NOTIFY doesn't work - Advisory locks don't persist

Client sends query → Gets server connection for ONE query Query completes → Server connection immediately returned Next query → May get DIFFERENT server connection Efficiency: HIGHEST (maximum connection sharing) Compatibility: LOW (no multi-statement transactions!) SEVERE LIMITATIONS: - Transactions NOT supported (autocommit only) - Each statement may run on different server - Only for simple, independent queries Best for: Simple analytics dashboards, read-only APIs

PgBouncer Mode Comparison

Mode

Efficiency

Transactions

Prepared Stmts

Session State

Use Case

Session

Low (1:1)

Full support

Works

Preserved

Legacy apps, full compatibility

Transaction

High (N:1)

Works

Per-transaction

Not preserved

Most web apps (recommended)

Statement

Highest

Not supported

Not preserved

Simple read queries only

;; Database configuration [databases] ; Format: logical_name = connection_string myapp = host=primary.db.internal port=5432 dbname=myapp myapp_readonly = host=replica.db.internal port=5432 dbname=myapp ; Wildcard for dynamic routing (use with caution) ; * = host=db.internal port=5432 [pgbouncer] ;;; Connection settings listen_addr = 0.0.0.0 ; Listen on all interfaces listen_port = 6432 ; Standard PgBouncer port unix_socket_dir = /var/run/pgbouncer ;;; Authentication auth_type = scram-sha-256 ; Strong auth (or md5 for compatibility) auth_file = /etc/pgbouncer/userlist.txt ; auth_query for auth from database (preferred for many users): ; auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1 ;;; Pool configuration pool_mode = transaction ; Transaction pooling (recommended) default_pool_size = 25 ; Connections per user/database pair min_pool_size = 5 ; Minimum connections to keep open reserve_pool_size = 5 ; Extra connections for peaks reserve_pool_timeout = 3 ; Seconds before using reserve ;;; Connection limits max_client_conn = 1000 ; Maximum client connections max_db_connections = 50 ; Maximum connections to each database max_user_connections = 50 ; Maximum connections per user ;;; Timeouts server_connect_timeout = 10 ; Seconds to connect to server server_login_retry = 3 ; Seconds between connection retries server_lifetime = 3600 ; Seconds before server conn recycled server_idle_timeout = 600 ; Seconds before idle server closed client_idle_timeout = 0 ; 0 = no client idle timeout client_login_timeout = 60 ; Seconds for client auth ;;; Query handling query_timeout = 0 ; 0 = no query timeout (handled by app) query_wait_timeout = 120 ; Seconds to wait for server connection ;;; TLS configuration server_tls_sslmode = require ; Encrypt to PostgreSQL ; server_tls_ca_file = /etc/ssl/certs/ca.pem client_tls_sslmode = require ; Encrypt from applications client_tls_key_file = /etc/pgbouncer/server.key client_tls_cert_file = /etc/pgbouncer/server.crt ;;; Logging and monitoring log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 stats_period = 60 ; Stats logging interval admin_users = pgbouncer_admin ; Users allowed admin access ;;; Process settings pidfile = /var/run/pgbouncer/pgbouncer.pid logfile = /var/log/pgbouncer/pgbouncer.log ; For systemd: logfile = /dev/null and use journald

; Format: "username" "password_hash" ; Generate hash: echo -n "password" | md5sum, prefix with "md5" ; Or for scram-sha-256, copy from PostgreSQL pg_authid "appuser" "SCRAM-SHA-256$4096:salt$client_key:server_key" "readonly" "SCRAM-SHA-256$4096:salt$client_key:server_key" "admin" "SCRAM-SHA-256$4096:salt$client_key:server_key"

ProxySQL vs PgBouncer Comparison

Feature

ProxySQL (MySQL)

PgBouncer (PostgreSQL)

Connection pooling

Yes

Read/write splitting

Yes (built-in)

No (use separate entries)

Query caching

Yes

Query rewriting

Yes

Runtime reconfiguration

Yes (SQL interface)

Yes (RELOAD)

Resource usage

Moderate

Very low

Complexity

Higher

Lower

Monitoring

Rich metrics

Basic stats

-- Connect to ProxySQL admin interface -- mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> ' -- ===================================================== -- STEP 1: Configure Backend MySQL Servers -- ===================================================== -- Add primary server (hostgroup 0) INSERT INTO mysql_servers ( hostgroup_id, hostname, port, weight, max_connections, max_replication_lag ) VALUES (0, 'primary.db.internal', 3306, 1000, 100, 0); -- Add replica servers (hostgroup 1 for reads) INSERT INTO mysql_servers ( hostgroup_id, hostname, port, weight, max_connections, max_replication_lag ) VALUES (1, 'replica1.db.internal', 3306, 1000, 100, 10), (1, 'replica2.db.internal', 3306, 1000, 100, 10); -- ===================================================== -- STEP 2: Configure MySQL Users -- ===================================================== -- Add application user (frontend) INSERT INTO mysql_users ( username, password, default_hostgroup, max_connections, transaction_persistent ) VALUES ('appuser', 'hashed_password', 0, 500, 1); -- ===================================================== -- STEP 3: Configure Query Rules for Read/Write Split -- ===================================================== -- Route SELECT queries to read hostgroup (1) INSERT INTO mysql_query_rules ( rule_id, active, match_pattern, destination_hostgroup, apply ) VALUES (10, 1, '^SELECT.*FOR UPDATE', 0, 1), -- FOR UPDATE goes to primary (20, 1, '^SELECT', 1, 1); -- Other SELECTs to replicas -- Everything else goes to default hostgroup (0 = primary) -- ===================================================== -- STEP 4: Global Variables Configuration -- ===================================================== -- Connection pooling settings UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-connection_pooling_enabled'; -- Connection limits UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-max_connections'; -- Connection timeouts UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-connect_timeout_server'; UPDATE global_variables SET variable_value='30000' WHERE variable_name='mysql-wait_timeout'; -- Multiplexing configuration UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-multiplexing'; UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-connection_pooling_enabled'; -- Query caching (optional) UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-query_cache_size_MB'; -- ===================================================== -- STEP 5: Apply Configuration -- ===================================================== -- Load configuration to runtime LOAD MYSQL USERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME; LOAD MYSQL VARIABLES TO RUNTIME; -- Save to disk for persistence across restarts SAVE MYSQL USERS TO DISK; SAVE MYSQL SERVERS TO DISK; SAVE MYSQL QUERY RULES TO DISK; SAVE MYSQL VARIABLES TO DISK;

// Connection string uses RDS Proxy endpoint instead of direct RDS const connectionString = `postgresql://${process.env.DB_USER}:${process.env.DB_PASSWORD}@myproxy.proxy-xxx.us-east-1.rds.amazonaws.com:5432/myapp`; // With IAM authentication import { RDSDataClient, ExecuteStatementCommand } from "@aws-sdk/client-rds-data"; const client = new RDSDataClient({ region: "us-east-1" }); // Using Data API (no connection management needed) const command = new ExecuteStatementCommand({ resourceArn: "arn:aws:rds:us-east-1:123456789:cluster:mycluster", secretArn: "arn:aws:secretsmanager:us-east-1:123456789:secret:mydbsecret", sql: "SELECT * FROM users WHERE id = :id", parameters: [{ name: "id", value: { longValue: userId } }], }); const result = await client.send(command);

Cloud Connection Pooling Options

Provider

Service

Databases

Key Features

AWS

RDS Proxy

PostgreSQL, MySQL

Serverless integration, IAM auth, automatic failover

Google Cloud

Cloud SQL Auth Proxy

PostgreSQL, MySQL, SQL Server

Secure tunnel, IAM auth, sidecar pattern

Azure

Built into Flexible Server

PostgreSQL, MySQL

PgBouncer built-in, simple enable/disable

Prisma

Prisma Accelerate

Multiple

Edge caching, connection pooling, query caching

PlanetScale

Built-in

MySQL (Vitess)

Automatic horizontal scaling, serverless-native

┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ App Pod │ │ App Pod │ │ App Pod │ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │ │ │ └────────────────┼────────────────┘ │ ┌─────────┴─────────┐ │ Load Balancer │ └─────────┬─────────┘ │ ┌────────────────┼────────────────┐ │ │ │ ┌──────┴──────┐ ┌──────┴──────┐ ┌──────┴──────┐ │ PgBouncer │ │ PgBouncer │ │ PgBouncer │ │ Pod 1 │ │ Pod 2 │ │ Pod 3 │ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │ │ │ └────────────────┼────────────────┘ │ ┌─────────┴─────────┐ │ PostgreSQL │ └───────────────────┘ Pros: Centralized management, clear separation Cons: Additional load balancer, network hop

┌────────────────────────────────────────────────────┐ │ Kubernetes Pod │ │ ┌──────────────────────┐ ┌────────────────────┐ │ │ │ App Container │ │ PgBouncer Sidecar │ │ │ │ ├──┤ │ │ │ │ connects to │ │ connects to DB │ │ │ │ localhost:6432 │ │ over network │ │ │ └──────────────────────┘ └─────────┬──────────┘ │ └──────────────────────────────────────┼─────────────┘ │ ┌────────┴────────┐ │ PostgreSQL │ └─────────────────┘ Pros: No network hop for app→pooler, easy per-pod configuration Cons: More pooler instances, each maintains connections

┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ App Pod │ │ App Pod │ │ App Pod │ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │ │ │ └────────────────┼────────────────┘ │ ▼ ┌─────────────────────────────────────────┐ │ Database Server │ │ ┌─────────────────────────────────┐ │ │ │ PgBouncer │ │ │ │ (localhost / unix socket) │ │ │ └────────────┬────────────────────┘ │ │ │ │ │ ┌────────────┴────────────────────┐ │ │ │ PostgreSQL │ │ │ │ (unix socket connection) │ │ │ └─────────────────────────────────┘ │ └─────────────────────────────────────────┘ Pros: Minimal latency between pooler and DB Cons: Couples pooler lifecycle to DB, single point of failure

PgBouncer, ProxySQL

The Need for External Connection Poolers

PgBouncer for PostgreSQL

PgBouncer Pooling Modes

PgBouncer Production Configuration

ProxySQL for MySQL

ProxySQL Production Configuration

Cloud-Managed Alternatives

Deployment Patterns

There’s more to understand.

PgBouncer, ProxySQL

The Need for External Connection Poolers

PgBouncer for PostgreSQL

PgBouncer Pooling Modes

PgBouncer Production Configuration

ProxySQL for MySQL

ProxySQL Production Configuration

Cloud-Managed Alternatives

Deployment Patterns

There’s more to understand.