PgBouncer Connection Pooling for PostgreSQL

PostgreSQL forks a backend process per connection, and each backend costs memory for
sort buffers, work memory, plan caches and OS resources. A server with
max_connections = 500 runs out of RAM under load even when most connections are
idle, because idle connections still hold memory. PgBouncer is the standard answer: a small C
process that multiplexes thousands of client connections onto a fixed server-side pool, adding
roughly half a millisecond. This article covers the three pool modes, how to size them against
max_connections, and the prepared-statement problem that catches almost every
transaction-mode deployment.

The Three Pool Modes

Transaction mode is the right default for most applications and the source of most
confusion, because features that span transactions break: LISTEN/NOTIFY,
session-level SET, advisory locks held across transactions, and SQL-level
prepared statements. Protocol-level named prepared statements are supported in PgBouncer 1.21+
when max_prepared_statements is enabled — that distinction matters, because most
drivers use the protocol-level form.

Install and Configure

sudo apt install -y pgbouncer postgresql-client-17
sudo systemctl enable pgbouncer

; /etc/pgbouncer/pgbouncer.ini
[databases]
orders  = host=10.10.70.20 port=5432 dbname=orders
reports = host=10.10.70.20 port=5432 dbname=reports
; per-database override of the global mode
appdb_session = host=10.10.70.20 port=5432 dbname=appdb pool_mode=session

[pgbouncer]
listen_addr            = 10.10.70.21
listen_port            = 6432
auth_type              = scram-sha-256
auth_file              = /etc/pgbouncer/userlist.txt
admin_users            = pgbouncer_admin
stats_users            = pgbouncer_stats

; Pool behaviour
pool_mode              = transaction
max_client_conn        = 2000
default_pool_size      = 25
min_pool_size          = 5
reserve_pool_size      = 10
reserve_pool_timeout   = 3
max_db_connections     = 100
max_user_connections   = 100

; Timeouts
server_idle_timeout    = 600
server_lifetime        = 3600
query_wait_timeout     = 30
client_idle_timeout    = 0
client_login_timeout   = 60

; TLS to the server side
server_tls_sslmode     = verify-full
server_tls_ca_file     = /etc/ssl/certs/ca.crt

; Logging and observability
log_connections        = 1
log_disconnections     = 1
log_pooler_errors      = 1
stats_period           = 60
ignore_startup_parameters = extra_float_digits

auth_type = scram-sha-256 pairs with PostgreSQL's default password
encryption. If you see password authentication failed while the credentials are
demonstrably correct, the userlist.txt entry is probably a plaintext or MD5 hash
while the server expects SCRAM. Copy the exact rolpassword value from
pg_authid.

# /etc/pgbouncer/userlist.txt
"app_user" "SCRAM-SHA-256$4096:salt$StoredKey:ServerKey"

Sizing the Pool

The sum of all pool sizes across all (database, user) pairs is an upper bound on PostgreSQL
backend processes. It must fit under max_connections with headroom for
administration and replication.

# PostgreSQL
psql -c "SHOW max_connections;"        -- e.g. 200

# Reserve 20 for admin + replication  ->  180 available for PgBouncer
# Two (db, user) pairs: orders/orderapp and reports/reportapp
#   180 / 2 = 90 per pair, use 80 to leave slack

default_pool_size = 80

Two rules of thumb:

• Start at 20-40 per pool for a typical 8-16 core database host. More connections rarely
  mean more throughput once you are past core count; they mean more context switching.
• If pool sizes across all PgBouncer instances approach max_connections, you have
  removed the buffer that protects the primary during a connection storm. That is the point of
  the whole exercise.

Verification and the Console

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

SHOW POOLS;     -- pools and their state
SHOW STATS;     -- per-database aggregates
SHOW CLIENTS;   -- client-side connections
SHOW SERVERS;   -- server-side connections
SHOW CONFIG;    -- effective configuration
SHOW DATABASES;

RELOAD;         -- apply a config change without dropping clients
PAUSE / RESUME / KILL

Reading SHOW POOLS is the core skill. The columns that matter:

• cl_active — clients currently holding a server connection
• cl_waiting — clients queued because no server connection was free
• sv_active / sv_idle — server-side state
• maxwait — how long the longest waiter has been waiting

A persistent non-zero cl_waiting with a rising maxwait means the
pool is too small, or that queries are holding connections too long. Check which before you
raise the pool size — a leak and a busy database look identical from the pooler.

The Prepared-Statement Trap

In transaction mode, a prepared statement created on one server connection may later be
executed through a different one, producing prepared statement "S_1" does not exist.
Three fixes, in order of preference:

• Enable max_prepared_statements (PgBouncer 1.21+) and use protocol-level
  prepared statements — the modern drivers already do.
• Disable statement caching in the driver: JDBC prepareThreshold=0, psycopg
  prepare_threshold=None, Go pgx statement_cache_capacity=0.
• Move that database to session pooling with a per-database override. It costs you
  multiplexing but it always works.

; Per-database escape hatch
appdb_legacy = host=10.10.70.20 port=5432 dbname=appdb pool_mode=session

High Availability and Observability

• Run PgBouncer on the application hosts rather than centrally, so the failure domain is
  small and the network hop is short.
• If you must centralise it, put it behind a floating address —
  Keepalived VRRP and HAProxy virtual IP failover covers that layer.
• Export metrics with pgbouncer_exporter and alert on
  cl_waiting > 0 for more than a minute and on
  query_wait_timeout errors in the log. See Alertmanager routing and notification configuration for the alerting side.
• PgBouncer does not fail over PostgreSQL. Pair it with streaming replication and
  pg_rewind: PostgreSQL streaming replication and pg_rewind failover.
• Common symptom-to-cause map: clients hang → pool too small (check
  cl_waiting); stale prepared statements after a failover → statement pooling with a
  stateful ORM; PostgreSQL refusing connections after reload → combined pool sizes exceed
  max_connections.

原文链接：https://stackharbor.com/en/knowledge-base/postgres-pgbouncer-pooling