Connection Pooling - Learning Module

Loading content...

0/273

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 × 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.

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
┌─────────────────────────────────────────────────────────────────┐
│                        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):

1
2
3
4
5
6
7
8
9
10
11
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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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):

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
; 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 × 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.

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
┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│   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.

Operational Best Practices

Running external poolers in production requires attention to monitoring, high availability, and maintenance procedures.

Monitoring Essentials

•Client connection count — How many applications are connected to the pooler.
•Server connection count — How many connections the pooler has to the database (should be stable and lower than client count).
•Wait time — Time clients spend waiting for a server connection. Should be near-zero under normal load.
•Pool utilization — Percentage of pool capacity in use. Alert at 80%+.
•Query rate — Queries per second through the pooler.
•Error rate — Failed connections, query errors, timeouts.
•Memory usage — Poolers are lightweight but should be monitored.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Connect to PgBouncer admin database
-- psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer
 
-- Overall statistics
SHOW STATS;
-- Columns: database, total_xact_count, total_query_count, total_received, 
--          total_sent, total_xact_time, total_query_time, avg_xact_time, avg_query_time
 
-- Current pools status
SHOW POOLS;
-- Columns: database, user, cl_active, cl_waiting, sv_active, sv_idle, 
--          sv_used, sv_tested, sv_login, maxwait, pool_mode
 
-- Current connections
SHOW CLIENTS;
SHOW SERVERS;
 
-- Configuration
SHOW CONFIG;
 
-- Key metrics to alert on:
-- cl_waiting > 0 for extended periods (client wait)
-- sv_active = max_db_connections (pool exhaustion)
-- maxwait > 1 second (clients waiting too long)

High availability configuration:

HA Best Practices

•Run multiple pooler instances — Minimum 2 for HA, 3+ for larger deployments.
•Use load balancer health checks — TCP or HTTP health endpoints to detect failed poolers.
•Configure connection draining — Graceful shutdown that completes in-flight queries.
•Automate failover — Pooler should handle backend database failover transparently.
•Monitor and alert — Alerting on pool exhaustion, high wait times, error rates.
•Test failover regularly — Chaos engineering to verify HA actually works.

Page Complete

You now understand external connection poolers—PgBouncer for PostgreSQL and ProxySQL for MySQL. These tools are essential for scaling beyond single-application deployments. Key decisions: choose transaction mode for PgBouncer (usually), use ProxySQL when you need read/write splitting or query caching, and deploy in a clustered pattern for high availability. Next, we'll cover application-side pooling patterns for scenarios where external poolers aren't available.

PgBouncer, ProxySQL

The Need for External Connection Poolers

The problem:

Each application instance maintains its own pool. If each has 20 connections:

50 pods × 20 connections = 1,000 potential connections
This exceeds most databases' optimal capacity
Database performance degrades under connection weight

What You Will Learn

PgBouncer for PostgreSQL

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.

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
┌─────────────────────────────────────────────────────────────────┐
│                        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

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):

1
2
3
4
5
6
7
8
9
10
11
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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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):

1
2
3
4
5
6
7
8
9
10
11
12
13
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

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

1
2
3
4
5
6
7
; 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

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

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

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

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.

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
┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│   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

Operational Best Practices

Running external poolers in production requires attention to monitoring, high availability, and maintenance procedures.

Monitoring Essentials

•Client connection count — How many applications are connected to the pooler.
•Server connection count — How many connections the pooler has to the database (should be stable and lower than client count).
•Wait time — Time clients spend waiting for a server connection. Should be near-zero under normal load.
•Pool utilization — Percentage of pool capacity in use. Alert at 80%+.
•Query rate — Queries per second through the pooler.
•Error rate — Failed connections, query errors, timeouts.
•Memory usage — Poolers are lightweight but should be monitored.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Connect to PgBouncer admin database
-- psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer
 
-- Overall statistics
SHOW STATS;
-- Columns: database, total_xact_count, total_query_count, total_received, 
--          total_sent, total_xact_time, total_query_time, avg_xact_time, avg_query_time
 
-- Current pools status
SHOW POOLS;
-- Columns: database, user, cl_active, cl_waiting, sv_active, sv_idle, 
--          sv_used, sv_tested, sv_login, maxwait, pool_mode
 
-- Current connections
SHOW CLIENTS;
SHOW SERVERS;
 
-- Configuration
SHOW CONFIG;
 
-- Key metrics to alert on:
-- cl_waiting > 0 for extended periods (client wait)
-- sv_active = max_db_connections (pool exhaustion)
-- maxwait > 1 second (clients waiting too long)

High availability configuration:

HA Best Practices

•Run multiple pooler instances — Minimum 2 for HA, 3+ for larger deployments.
•Use load balancer health checks — TCP or HTTP health endpoints to detect failed poolers.
•Configure connection draining — Graceful shutdown that completes in-flight queries.
•Automate failover — Pooler should handle backend database failover transparently.
•Monitor and alert — Alerting on pool exhaustion, high wait times, error rates.
•Test failover regularly — Chaos engineering to verify HA actually works.

Page Complete