PostgreSQL Connection Pool Optimization: 6 Production Patterns from PgBouncer to Pgpool-II

数据库

FATAL: sorry, too many connections — The 3 AM Alert

At 3 AM, your phone is bombarded with alerts: FATAL: sorry, too many connections already. 50 microservice instances, each with 20 connections, but PostgreSQL's max_connections is only 200. A connection storm causes the database to reject all new connections, and the entire system cascades. In 2026, PostgreSQL connection pool optimization is the lifeline of high-concurrency systems — from PgBouncer's three pooling modes to Pgpool-II's load balancing, from Odyssey's high-performance architecture to connection capacity planning, choosing the right solution enables a single machine to support 100K concurrent connections.

This article starts from PostgreSQL's connection model and guides you through 6 production-grade connection pool patterns, from configuration to monitoring, from pitfalls to advanced optimization.

PostgreSQL Connection Pool Core Concepts

Concept Description
Connection Pool Reuses database connections, avoiding overhead of frequent creation/destruction
PgBouncer Lightweight PostgreSQL connection pooler, supports Session/Transaction/Statement modes
Transaction Pooling Releases connection back to pool after transaction ends, maximum reuse rate
Session Pooling Releases connection only when client disconnects, best compatibility
Statement Pooling Releases connection after each SQL statement, highest reuse but worst compatibility
Pgpool-II PostgreSQL middleware, supports connection pooling, load balancing, read/write splitting
Odyssey High-performance PostgreSQL connection pooler by Yandex, supports multi-database multi-user routing
max_connections PostgreSQL maximum connections, each connection is an OS process, ~10MB memory

PostgreSQL Process Model and Connection Overhead

PostgreSQL Process-Per-Connection Model:
┌─────────────┐     ┌──────────────┐
│  Application │────→│  Postmaster  │  (Main process, listening on port)
└─────────────┘     └──────┬───────┘
                           │ fork()
                    ┌──────▼───────┐
                    │  Backend     │  (One process per connection)
                    │  Process     │  ≈ 10MB memory
                    │  (PG_CONN_1) │  ≈ 1 OS process
                    └──────────────┘
                    ┌──────────────┐
                    │  Backend     │
                    │  Process     │  1000 connections ≈ 10GB memory
                    │  (PG_CONN_2) │  Massive context switch overhead
                    └──────────────┘
                        ...N more

With Connection Pooler:
┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│  App (1000)  │────→│  Pooler (20) │────→│  PostgreSQL  │
│  connections  │     │  reused      │     │  real conns  │
└─────────────┘     └──────────────┘     └──────────────┘

Problem Analysis: 5 Major PostgreSQL Connection Pool Challenges

  1. Connection storms: Microservices start/restart simultaneously, instantly exhausting max_connections, database rejects all new connections
  2. Connection leaks: Applications fail to properly release connections, idle connections accumulate, pool exhaustion blocks new requests
  3. Mode selection difficulty: PgBouncer's three modes each have tradeoffs — Session wastes connections, Transaction is incompatible with SET/PREPARE, Statement has worst compatibility
  4. Capacity planning gaps: No formula for calculating connection limits, experience-based configuration leads to resource waste or insufficient connections
  5. Monitoring blind spots: Missing connection pool metrics, connection exhaustion and queue timeouts discovered too late

Step-by-Step: 6 Production-Grade Connection Pool Patterns

Pattern 1: PgBouncer Session Mode Configuration

Session mode is closest to native PostgreSQL behavior with the best compatibility, suitable for scenarios requiring session state (SET variables, PREPARE statements, temporary tables).

; /etc/pgbouncer/pgbouncer.ini — Session mode configuration
[databases]
; Production database connection
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production
; Read replica
myapp_readonly = host=10.0.1.101 port=5432 dbname=myapp_production

[pgbouncer]
; Listen address and port
listen_addr = 0.0.0.0
listen_port = 6432

; Session mode: release connection only when client disconnects
pool_mode = session

; Pool size: max server connections per database/user pair
; In Session mode, this must be >= client concurrency
default_pool_size = 50

; Minimum pool size, pre-warm connections
min_pool_size = 10

; Reserve connections for admin emergency access
reserve_pool_size = 5
reserve_pool_timeout = 3

; Idle connection timeout (seconds), reclaim long-idle server connections
idle_timeout = 600

; Server connection max lifetime (seconds), prevent long-lived connection issues
server_lifetime = 3600

; Client idle timeout (seconds)
client_idle_timeout = 0

; Server login retry timeout (seconds)
server_login_retry = 3

; Connection wait timeout (seconds), client wait time when pool is full
query_timeout = 30
query_wait_timeout = 30

; Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Admin interface
admin_users = pgadmin
stats_users = pgmonitor

; Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
verbose = 0

# Create authentication file
echo '"myapp_user" "SCRAM-SHA-256$4096:...hash..."' > /etc/pgbouncer/userlist.txt

# Start PgBouncer
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# Verify Session mode
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SHOW pool_mode;"

# Python application connecting via PgBouncer (Session mode)
import psycopg2
from contextlib import contextmanager

POOL_CONFIG = {
    "host": "10.0.1.50",
    "port": 6432,
    "database": "myapp_db",
    "user": "myapp_user",
    "password": "secure_password",
    "connect_timeout": 5,
    "options": "-c search_path=public"
}

@contextmanager
def get_connection():
    conn = psycopg2.connect(**POOL_CONFIG)
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

with get_connection() as conn:
    cur = conn.cursor()
    cur.execute("SET LOCAL work_mem = '256MB'")
    cur.execute("SELECT * FROM orders WHERE user_id = %s", (12345,))
    print(cur.fetchall())

Pattern 2: PgBouncer Transaction Mode — The High-Concurrency Choice

Transaction mode releases connections back to the pool immediately after a transaction ends, making it the best choice for high-concurrency scenarios. 1000 clients may only need 20 real database connections.

; /etc/pgbouncer/pgbouncer.ini — Transaction mode configuration
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; Transaction mode: release connection after transaction ends
pool_mode = transaction

; Pool size: can be much smaller than client count in Transaction mode
; Formula: pool_size = ((core_count * 2) + effective_spindle_count)
; 16 cores + SSD → about 40-50 connections
default_pool_size = 40

min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3

; In-transaction idle timeout (seconds), prevent idle transactions
idle_transaction_timeout = 60

; Idle connection timeout
idle_timeout = 600

; Server connection max lifetime
server_lifetime = 1800

; Server health check interval (seconds)
server_check_query = SELECT 1
server_check_delay = 30

; Critical: wait timeout in transaction mode
query_timeout = 30
query_wait_timeout = 60

; Maximum client connections
max_client_conn = 2000

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

admin_users = pgadmin
stats_users = pgmonitor

# Best practices for Transaction mode: explicit transaction control
import psycopg2
from psycopg2 import pool

threaded_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    host="10.0.1.50",
    port=6432,
    database="myapp_db",
    user="myapp_user",
    password="secure_password"
)

def transfer_funds(from_id, to_id, amount):
    conn = threaded_pool.getconn()
    try:
        cur = conn.cursor()
        cur.execute("BEGIN")
        cur.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_id)
        )
        cur.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_id)
        )
        cur.execute("COMMIT")
    except Exception as e:
        cur.execute("ROLLBACK")
        raise e
    finally:
        threaded_pool.putconn(conn)

def get_user_orders(user_id):
    conn = threaded_pool.getconn()
    try:
        cur = conn.cursor()
        cur.execute(
            "SELECT id, status, total FROM orders WHERE user_id = %s",
            (user_id,)
        )
        return cur.fetchall()
    finally:
        threaded_pool.putconn(conn)

-- Features NOT compatible with Transaction mode
-- ❌ SET statements (reset after transaction ends)
SET work_mem = '256MB';  -- Lost after transaction

-- ❌ PREPARE statements
PREPARE get_user AS SELECT * FROM users WHERE id = $1;  -- Lost after transaction

-- ❌ Temporary tables
CREATE TEMP TABLE tmp_cache AS SELECT * FROM products;  -- Lost after transaction

-- ❌ LISTEN/NOTIFY
LISTEN channel_name;  -- Cannot receive notifications across transactions

-- ✅ Correct approach: use SET LOCAL within transaction
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT;  -- work_mem only effective within this transaction

Pattern 3: Pgpool-II — Load Balancing and Read/Write Splitting

Pgpool-II is not just a connection pool but a PostgreSQL middleware, supporting read/write splitting, load balancing, and automatic failover.

; /etc/pgpool-II/pgpool.conf — Pgpool-II configuration
; Listen configuration
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/pgpool'

; Backend node configuration
backend_hostname0 = '10.0.1.100'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILBACK'
backend_application_name0 = 'primary'

backend_hostname1 = '10.0.1.101'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILBACK'
backend_application_name1 = 'replica1'

backend_hostname2 = '10.0.1.102'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILBACK'
backend_application_name2 = 'replica2'

; Connection pool configuration
connection_cache = on
num_init_children = 50
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0

; Load balancing
load_balance_mode = on
; SELECT queries automatically distributed to replicas
; Write operations automatically routed to primary

; Read/write splitting
; Default: primary handles writes, replicas handle reads
; Use hints for precise control

; Health check
health_check_period = 10
health_check_timeout = 5
health_check_user = 'pgpool_health'
health_check_password = 'health_check_pass'
health_check_max_retries = 3

; Failover
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %H %P %m %M'
failback_command = '/etc/pgpool-II/failback.sh %d %h %p %D %H %P'
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %H %P'

; Auto failback
auto_failback = off
failover_on_backend_error = off

; Logging
log_connections = on
log_disconnections = on
log_statement = off
log_per_node_statement = off

# Pgpool-II read/write splitting verification
# Write operation → Primary
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
  "INSERT INTO orders (user_id, status) VALUES (1, 'pending');"

# Read operation → Auto load balanced to replica
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
  "SELECT * FROM orders WHERE user_id = 1;"

# Use hint to force routing to primary
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
  "/*NO_LOAD_BALANCE*/ SELECT * FROM orders WHERE user_id = 1;"

# Check node status
psql -h 127.0.0.1 -p 9999 -U pgpool_admin -c "SHOW pool_nodes;"

# Python application using Pgpool-II
import psycopg2

def get_pgpool_connection(read_only=False):
    conn = psycopg2.connect(
        host="10.0.1.50",
        port=9999,
        database="myapp_production",
        user="myapp_user",
        password="secure_password"
    )
    if read_only:
        conn.set_session(readonly=True, autocommit=True)
    return conn

# Write operation
write_conn = get_pgpool_connection(read_only=False)
cur = write_conn.cursor()
cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
write_conn.commit()
write_conn.close()

# Read operation (auto load balanced)
read_conn = get_pgpool_connection(read_only=True)
cur = read_conn.cursor()
cur.execute("SELECT * FROM accounts WHERE id = 1")
print(cur.fetchall())
read_conn.close()

Pattern 4: Odyssey — High-Performance Connection Pooler

Odyssey, developed by Yandex, is designed for high-throughput scenarios, supporting multi-database multi-user routing with higher performance than PgBouncer.

; /etc/odyssey/odyssey.conf — Odyssey configuration
storage myapp_production {
    type remote
    host 10.0.1.100
    port 5432
}

storage myapp_replica {
    type remote
    host 10.0.1.101
    port 5432
}

database default {
    storage default
    pool default {
        pool_mode session
        pool_size 0
    }
}

database myapp_db {
    storage myapp_production
    pool myapp_pool {
        pool_mode transaction
        pool_size 50
        pool_timeout 5000

        ; Idle timeout (milliseconds)
        idle_timeout 600000

        ; Server connection lifetime
        server_lifetime 1800000

        ; Auto prepared statement support
        discard_idle 600000
    }
}

database myapp_readonly {
    storage myapp_replica
    pool readonly_pool {
        pool_mode transaction
        pool_size 30
        pool_timeout 5000
    }
}

; User routing rules
user myapp_user {
    authentication scramsha256
    password "SCRAM-SHA-256$4096:...hash..."

    ; Default route to primary
    database default {
        pool myapp_pool
    }

    ; Read-only route to replica
    database myapp_readonly {
        pool readonly_pool
    }
}

; Listen configuration
listen {
    host 0.0.0.0
    port 6432
}

; Logging
log_format "%p %t %l [%c %s] %m\n"
log_debug 0
log_config 0

; Statistics
stats_interval 60

# Start Odyssey
odyssey /etc/odyssey/odyssey.conf

# Connection test
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SELECT version();"

# Read-only connection
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_readonly -c "SELECT pg_is_in_recovery();"

# Python application using Odyssey
import psycopg2
from psycopg2 import pool

class OdysseyConnectionManager:
    def __init__(self):
        self.write_pool = pool.ThreadedConnectionPool(
            minconn=2, maxconn=10,
            host="10.0.1.50", port=6432,
            database="myapp_db",
            user="myapp_user", password="secure_password"
        )
        self.read_pool = pool.ThreadedConnectionPool(
            minconn=2, maxconn=20,
            host="10.0.1.50", port=6432,
            database="myapp_readonly",
            user="myapp_user", password="secure_password"
        )

    def get_write_conn(self):
        return self.write_pool.getconn()

    def get_read_conn(self):
        return self.read_pool.getconn()

    def put_write_conn(self, conn):
        self.write_pool.putconn(conn)

    def put_read_conn(self, conn):
        self.read_pool.putconn(conn)

manager = OdysseyConnectionManager()

# Write operation
conn = manager.get_write_conn()
try:
    cur = conn.cursor()
    cur.execute("INSERT INTO events (type, data) VALUES (%s, %s)", ("click", "{}"))
    conn.commit()
finally:
    manager.put_write_conn(conn)

# Read operation
conn = manager.get_read_conn()
try:
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) FROM events")
    print(cur.fetchone())
finally:
    manager.put_read_conn(conn)

Pattern 5: Connection Sizing Formula and Capacity Planning

Capacity planning is the foundation of connection pool optimization — without formulas, you're flying blind.

Connection Sizing Formulas:

1. PostgreSQL max_connections planning:
   max_connections = (CPU cores × 2) + effective_spindle_count

   Example: 16 CPU cores + SSD (equivalent to 4 spindles)
   max_connections = (16 × 2) + 4 = 36

   Production recommendation: max_connections = 100 (with headroom), control real connections via pooler

2. PgBouncer pool size:
   pool_size = CPU cores × 2 + effective_spindle_count

   Example: 16 cores + SSD
   pool_size = 16 × 2 + 4 = 36 → round to 40

3. Application-side pool size (per instance):
   app_pool_size = pool_size / instance_count

   Example: 40 total connections / 10 instances = 4 per instance

4. Maximum client connections:
   max_client_conn = instance_count × app_pool_size × 1.5

   Example: 10 × 4 × 1.5 = 60

5. Memory estimation:
   PostgreSQL total memory ≈ max_connections × 10MB + shared_buffers
   Example: 100 × 10MB + 4GB = 5GB

-- View current connection distribution
SELECT
    datname AS database,
    usename AS username,
    state,
    COUNT(*) AS connection_count,
    AVG(EXTRACT(EPOCH FROM (now() - query_start))) AS avg_query_duration_sec
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname, usename, state
ORDER BY connection_count DESC;

-- View connection count historical trends (requires pg_stat_statements)
SELECT
    EXTRACT(HOUR FROM query_start) AS hour,
    COUNT(*) AS query_count
FROM pg_stat_activity
WHERE query_start > now() - interval '24 hours'
GROUP BY hour
ORDER BY hour;

-- Check connection configuration
SHOW max_connections;
SHOW superuser_reserved_connections;

-- Calculate active connection ratio
SELECT
    state,
    COUNT(*) AS count,
    ROUND(COUNT(*)::numeric / (SELECT COUNT(*) FROM pg_stat_activity WHERE datname IS NOT NULL) * 100, 2) AS percentage
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY state
ORDER BY count DESC;

# Connection sizing planning tool
def calculate_pool_config(cpu_cores, disk_type, instance_count, workload="mixed"):
    effective_spindles = {
        "hdd_1": 1, "hdd_4": 4, "hdd_8": 8,
        "ssd": 4, "nvme": 8
    }.get(disk_type, 4)

    pg_max_connections = (cpu_cores * 2) + effective_spindles
    pg_max_connections = max(pg_max_connections, 20)

    pool_size = (cpu_cores * 2) + effective_spindles

    if workload == "read_heavy":
        pool_size = int(pool_size * 1.2)
    elif workload == "write_heavy":
        pool_size = int(pool_size * 0.8)

    app_pool_per_instance = max(pool_size // instance_count, 2)
    max_client_conn = int(instance_count * app_pool_per_instance * 1.5)

    shared_buffers_gb = max(cpu_cores * 0.5, 2)
    total_memory_gb = pg_max_connections * 0.01 + shared_buffers_gb

    return {
        "cpu_cores": cpu_cores,
        "disk_type": disk_type,
        "instance_count": instance_count,
        "workload": workload,
        "pg_max_connections": min(pg_max_connections + 20, 200),
        "pgbouncer_pool_size": pool_size,
        "pgbouncer_min_pool_size": max(pool_size // 4, 5),
        "app_pool_per_instance": app_pool_per_instance,
        "max_client_conn": max_client_conn,
        "shared_buffers_gb": shared_buffers_gb,
        "estimated_total_memory_gb": round(total_memory_gb, 1),
        "pool_mode": "transaction" if workload in ("read_heavy", "mixed") else "session"
    }

config = calculate_pool_config(
    cpu_cores=16,
    disk_type="ssd",
    instance_count=10,
    workload="mixed"
)

for key, value in config.items():
    print(f"{key}: {value}")

Pattern 6: Production Monitoring and Alerting Setup

A connection pool without monitoring is a ticking time bomb — comprehensive monitoring and alerting is the baseline for production deployment.

# prometheus-pgbouncer-exporter configuration
# /etc/prometheus/pgbouncer_exporter.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'pgbouncer'
    static_configs:
      - targets: ['10.0.1.50:9127']
    metrics_path: /metrics
    params:
      pgbouncer_addr: ['10.0.1.50:6432']

# Prometheus alerting rules
# /etc/prometheus/rules/pgbouncer_alerts.yml
groups:
  - name: pgbouncer_alerts
    rules:
      - alert: PgbouncerPoolExhausted
        expr: pgbouncer_pools_server_active >= pgbouncer_pools_server_max
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PgBouncer pool exhausted"
          description: "Database {{ $labels.database }} pool is full, active connections at limit"

      - alert: PgbouncerClientWaitQueue
        expr: pgbouncer_pools_client_active > pgbouncer_pools_server_active * 2
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PgBouncer client wait queue too long"
          description: "Database {{ $labels.database }} client wait count is 2x server count"

      - alert: PgbouncerHighWaitTime
        expr: pgbouncer_pools_client_wait_time_seconds > 5
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "PgBouncer wait time too high"
          description: "Database {{ $labels.database }} wait time exceeds 5 seconds"

      - alert: PostgreSQLTooManyConnections
        expr: pg_stat_activity_count > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL connection count too high"
          description: "Current active connections {{ $value }}, approaching max_connections limit"

-- PgBouncer admin commands
-- Connect to PgBouncer admin database via psql
-- psql -h 127.0.0.1 -p 6432 -U pgadmin pgbouncer

-- View pool status
SHOW POOLS;

-- View client list
SHOW CLIENTS;

-- View server list
SHOW SERVERS;

-- View database list
SHOW DATABASES;

-- View statistics
SHOW STATS;

-- View active sockets
SHOW ACTIVE_SOCKETS;

-- Force release idle connections
PAUSE myapp_db;
RESUME myapp_db;

-- Close all connections for a database
KILL myapp_db;

#!/bin/bash
# /etc/pgbouncer/health_check.sh — PgBouncer health check script
PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_ADMIN_USER="pgadmin"
ALERT_WEBHOOK="https://hooks.slack.com/services/xxx"

check_pool_status() {
    local result=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT \
        -U $PGBOUNCER_ADMIN_USER pgbouncer -c "SHOW POOLS;" -t 2>/dev/null)

    if [ -z "$result" ]; then
        send_alert "PgBouncer unreachable, may be down"
        return 1
    fi

    echo "$result" | while read line; do
        active=$(echo "$line" | awk '{print $5}')
        max_conn=$(echo "$line" | awk '{print $7}')
        if [ -n "$active" ] && [ -n "$max_conn" ] && [ "$active" -ge "$max_conn" ]; then
            send_alert "PgBouncer pool exhausted: active=$active, max=$max_conn"
        fi
    done
}

check_postgresql_connections() {
    local conn_count=$(psql -h 10.0.1.100 -p 5432 -U monitor -d myapp_production \
        -t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'myapp_production';" 2>/dev/null)

    conn_count=$(echo $conn_count | tr -d ' ')
    if [ "$conn_count" -gt 80 ]; then
        send_alert "PostgreSQL connection count too high: $conn_count / 100"
    fi
}

send_alert() {
    local message="$1"
    curl -s -X POST "$ALERT_WEBHOOK" \
        -H "Content-Type: application/json" \
        -d "{\"text\": \"[ALERT] $message\"}" > /dev/null
    echo "$(date): $message" >> /var/log/pgbouncer_health.log
}

check_pool_status
check_postgresql_connections

Pitfall Guide: 5 Common Traps

Pitfall 1: Using SET Statements in Transaction Mode

-- ❌ Wrong: SET lost after transaction ends
SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
-- Next query: work_mem reverts to default

-- ✅ Correct: Use SET LOCAL within transaction
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT;

Pitfall 2: Double Pooling — Application Pool + PgBouncer

# ❌ Wrong: Large HikariCP pool + PgBouncer
# HikariCP: maximumPoolSize=50
# PgBouncer: default_pool_size=40
# Result: 50 client connections compete for 40 server connections, massive waiting

# ✅ Correct: Small app pool + PgBouncer
# HikariCP: maximumPoolSize=5 (per instance)
# PgBouncer: default_pool_size=40
# 10 instances × 5 connections = 50 client connections, 40 server connections sufficient

# HikariCP configuration (with PgBouncer Transaction mode)
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-test-query=SELECT 1

Pitfall 3: PgBouncer server_lifetime Too Long

; ❌ Wrong: server_lifetime too long, server connections may carry stale session state
server_lifetime = 86400

; ✅ Correct: Reasonable server_lifetime with periodic connection refresh
server_lifetime = 1800
; Combined with server_check_query for periodic health checks
server_check_query = SELECT 1
server_check_delay = 30

Pitfall 4: Ignoring superuser_reserved_connections

-- ❌ Wrong: max_connections=100, no reserved superuser connections
-- When connections are exhausted, even admins cannot log in

-- ✅ Correct: Reserve superuser connections
ALTER SYSTEM SET superuser_reserved_connections = 3;
SELECT pg_reload_conf();

-- Emergency: Use superuser connection
psql -h 10.0.1.100 -U postgres -d myapp_production -c \
  "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'idle_app' AND state = 'idle';"

Pitfall 5: Insufficient Pgpool-II Health Check User Permissions

-- ❌ Wrong: Using a regular user for health checks
-- CREATE USER health_check WITH PASSWORD 'pass';

-- ✅ Correct: Grant sufficient permissions to health check user
CREATE USER pgpool_health WITH PASSWORD 'health_check_pass' LOGIN;
GRANT pg_monitor TO pgpool_health;

-- Or minimal permissions
GRANT CONNECT ON DATABASE myapp_production TO pgpool_health;

Error Troubleshooting Table

Error Message Cause Solution
FATAL: sorry, too many connections already Connection count exceeds max_connections Add connection pooler, reduce default_pool_size, check for connection leaks
FATAL: remaining connection slots are reserved for non-replication superuser connections Normal user connections reached limit Increase max_connections or use superuser to clean idle connections
FATAL: no pg_hba.conf entry for host Client IP not in whitelist Add client IP or pooler server IP to pg_hba.conf
FATAL: password authentication failed for user Authentication credentials incorrect Check PgBouncer userlist.txt or auth_query configuration
ERROR: connection not open PgBouncer cannot connect to PostgreSQL Check backend database config, network, PostgreSQL status
ERROR: query_wait_timeout Client wait for connection timed out Increase query_wait_timeout or default_pool_size
ERROR: idle_transaction_timeout Idle time within transaction too long Check app for uncommitted long transactions, increase idle_transaction_timeout
ERROR: server_login_retry failed PgBouncer reconnect to PostgreSQL failed Check PostgreSQL reachability, pg_hba.conf allows connection
ERROR: prepared statement "s1" does not exist PREPARE statement lost in Transaction mode Switch to Session mode or use prepared_statement_cache_size=0
ERROR: cannot drop temporary table because it is being used Temporary table used across transactions in Transaction mode Switch to Session mode or use permanent table instead of temporary

Advanced Optimization

1. PgBouncer auth_query Dynamic Authentication

; /etc/pgbouncer/pgbouncer.ini — Dynamic authentication configuration
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production auth_query="SELECT usename, passwd FROM pg_shadow WHERE usename=$1"

[pgbouncer]
auth_type = md5
; No need to maintain userlist.txt, query PostgreSQL directly for passwords
; Suitable for scenarios with frequent user changes

-- Create read-only user for auth_query
CREATE USER pgbouncer_auth WITH PASSWORD 'auth_query_pass' LOGIN;
GRANT pg_monitor TO pgbouncer_auth;

-- Configure pg_hba.conf to allow pgbouncer_auth connection
-- host    myapp_production    pgbouncer_auth    10.0.1.50/32    md5

2. Multi-Tier Connection Pool Architecture

Multi-Tier Connection Pool Architecture:

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  App Instance1│  │  App Instance2│  │  App Instance3│
│  HikariCP(5) │  │  HikariCP(5) │  │  HikariCP(5) │
└──────┬───────┘  └──────┬───────┘  └──────┬───────┘
       │                 │                 │
       └────────┬────────┴────────┬────────┘
                │                 │
       ┌────────▼────────┐ ┌─────▼──────────┐
       │  PgBouncer      │ │  PgBouncer      │
       │  (Write-Session)│ │  (Read-Transact)│
       │  pool_size=20   │ │  pool_size=40   │
       │  :6432          │ │  :6433          │
       └────────┬────────┘ └──────┬──────────┘
                │                 │
       ┌────────▼────────┐ ┌─────▼──────────┐
       │  PostgreSQL     │ │  PostgreSQL     │
       │  Primary        │ │  Replica ×2     │
       │  max_conn=100   │ │  max_conn=100   │
       └─────────────────┘ └─────────────────┘

Key Design:
- Write operations: Session mode (compatible with PREPARE/SET)
- Read operations: Transaction mode (maximum reuse)
- Small app pool + Large PgBouncer pool
- Read/write splitting at connection pool layer

3. Connection Pool Auto-Scaling

# Load-based connection pool auto-scaling
import psycopg2
import time
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("pool_autoscaler")

class PoolAutoScaler:
    def __init__(self, pgbouncer_host, pgbouncer_port, admin_user, admin_db="pgbouncer"):
        self.conn_params = {
            "host": pgbouncer_host,
            "port": pgbouncer_port,
            "user": admin_user,
            "database": admin_db
        }
        self.min_pool = 10
        self.max_pool = 100
        self.scale_up_threshold = 0.8
        self.scale_down_threshold = 0.3

    def get_pool_stats(self):
        conn = psycopg2.connect(**self.conn_params)
        cur = conn.cursor()
        cur.execute("SHOW POOLS;")
        pools = cur.fetchall()
        cur.execute("SHOW DATABASES;")
        databases = cur.fetchall()
        conn.close()
        return pools, databases

    def calculate_utilization(self, active, total):
        if total == 0:
            return 0
        return active / total

    def scale_pool(self, database, new_size):
        new_size = max(self.min_pool, min(self.max_pool, new_size))
        conn = psycopg2.connect(**self.conn_params)
        cur = conn.cursor()
        cur.execute(f"SET default_pool_size = {new_size} FOR {database};")
        conn.close()
        logger.info(f"Pool scaled: {database} → {new_size}")

    def run(self):
        pools, databases = self.get_pool_stats()
        for pool in pools:
            database = pool[0]
            active = pool[4] if len(pool) > 4 else 0
            total = pool[6] if len(pool) > 6 else 1
            utilization = self.calculate_utilization(active, total)

            if utilization > self.scale_up_threshold:
                new_size = int(total * 1.5)
                self.scale_pool(database, new_size)
            elif utilization < self.scale_down_threshold:
                new_size = int(total * 0.7)
                self.scale_pool(database, new_size)

if __name__ == "__main__":
    scaler = PoolAutoScaler("10.0.1.50", 6432, "pgadmin")
    while True:
        try:
            scaler.run()
        except Exception as e:
            logger.error(f"Auto-scaler error: {e}")
        time.sleep(60)

Connection Pool Solution Comparison

Feature PgBouncer Pgpool-II Odyssey PostgreSQL Built-in SQLAlchemy Pool
Pooling Modes Session/Transaction/Statement Session/Transaction Session/Transaction None Session
Read/Write Split ❌ Requires external config ✅ Built-in ✅ Config routing ❌ ❌
Load Balancing ❌ ✅ Built-in ✅ Config routing ❌ ❌
Failover ❌ ✅ Built-in ❌ ❌ ❌
Performance Very High (C) High (C) Very High (C) N/A Medium (Python)
Multi-DB Routing ✅ ✅ ✅ ❌ ❌
Memory Usage Very Low (~2MB) Medium Low N/A Low
Deployment Complexity Low High Medium None None
Use Case Most scenarios Need RW split + LB Ultra-high concurrency + multi-tenant Dev/test Single Python app
PREPARE Support Session mode ✅ ✅ ✅ ✅
Recommendation ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐ ⭐⭐⭐⭐ ⭐⭐ ⭐⭐⭐

Summary

PostgreSQL Connection Pool Optimization Key Points: 1) Understand Process-Per-Connection overhead — each connection ≈ 10MB memory; 2) High-concurrency scenarios prefer PgBouncer Transaction mode — 1000 clients need only 20-40 real connections; 3) Need read/write splitting + load balancing → Pgpool-II; 4) Ultra-high concurrency + multi-tenant → Odyssey; 5) Connection sizing formula: pool_size = CPU cores × 2 + effective_spindle_count; 6) Small app pool + large PgBouncer pool, avoid double pooling; 7) Production must have monitoring and alerting — pool exhaustion is a P0 incident; 8) Reserve superuser_reserved_connections to prevent lockout when connections are exhausted.

Try these browser-local tools — no sign-up required →

#PostgreSQL#连接池#PgBouncer#Odyssey#性能优化#2026#数据库