PgBouncer and PostgreSQL Connection Pooling

There is a specific kind of 3 a.m. that belongs exclusively to database engineers: the kind where your monitoring fires because pg_stat_activity shows 487 connections and max_connections is 500, your app servers are queuing requests, and you are watching maxwait in PgBouncer climb past 30 seconds while trying not to wake your entire team. If you have not lived that experience, this post is your inoculation. If you have, this is the reference you wish you had beforehand.

Connection pooling is not a nice-to-have optimization in PostgreSQL. For almost any application beyond a single-server hobby project, it is a structural necessity. This post covers why that is, how PgBouncer works and how to configure it correctly, what breaks in transaction pooling mode (and why you should use it anyway), how to monitor a running pooler, and how PgBouncer compares to its alternatives. We will go deep on the operational details — this is not a ten-minute getting-started guide.

The current stable release as of this writing is PgBouncer 1.25.2 (released May 2026), which includes security fixes for several CVEs plus the major features added in 1.24 and 1.25: per-user and per-database connection limits, LDAP authentication, direct TLS support for PostgreSQL 17 clients, a transaction_timeout setting, and significantly improved SCRAM authentication performance.

The PostgreSQL Connection Model and Why It Breaks at Scale

PostgreSQL uses a process-per-connection model. This is not threads, not async I/O on a single event loop — each client connection causes the PostgreSQL postmaster to fork() a new backend process. This design has real advantages: process isolation means a crashing backend does not corrupt other sessions, and the memory model is simple. But it has a hard cost floor per connection that does not disappear regardless of what the connection is doing.

Each backend process consumes roughly 5 to 10 MB of resident memory for its working set, stack, and shared memory attachments. That is before it touches any data. On a database server with 32 GB RAM, you can support perhaps 2,000 to 3,000 connections from a memory standpoint alone — but you will find performance degrading badly long before that. Every active backend participates in lock table scans, checkpoint coordination, and vacuum signaling. At some threshold — typically somewhere between 200 and 500 connections depending on workload — the operating system context-switching overhead and kernel scheduler pressure start eating into query throughput measurably. More connections, slower queries, for the same amount of actual work.

Connection establishment itself costs time. A new connection requires a TCP handshake, TLS negotiation if configured, PostgreSQL authentication (password hashing, pg_hba.conf matching), backend process fork and initialization, and loading of session-level state. This typically takes between 1 and 5 milliseconds on local network. For a request handler that runs a 2ms query, spending 3ms to establish the connection is a 150% overhead tax. Multiply this across thousands of requests per second from application servers that open a fresh connection for each request — the common pattern in PHP applications and some serverless environments — and you are spending the majority of your database time on connection overhead rather than query execution.

The max_connections parameter in postgresql.conf sets the hard ceiling. The default is 100. When the limit is reached, new connection attempts receive this response immediately:

FATAL:  sorry, too many clients already

That error propagates up to your application as an exception, your framework may or may not retry it, and in the worst case it triggers a cascade: all your app servers start trying to reconnect simultaneously, each attempt fails, they retry with backoff, backoff timers expire roughly simultaneously, and you get a connection storm — hundreds of reconnect attempts hitting PostgreSQL in a tight window, each failing because the others are occupying the slots.

The naive fix is to raise max_connections. This is almost always wrong. Each increment to max_connections increases the size of PostgreSQL’s shared lock table (which scales with the maximum possible connections, not current connections), increases memory reservations for per-connection data structures, and widens the blast radius of the scheduler pressure described above. A PostgreSQL instance configured with max_connections = 1000 and seeing 800 active connections will often perform worse than the same instance with max_connections = 200 and a well-configured pooler keeping server-side connections at 50. The resource that matters is the number of backend processes PostgreSQL is actually running, not the number of clients waiting in a pooler queue.

The idle connection problem is particularly insidious because it is invisible in normal operation. Consider a Django application deployed with 20 app server processes, each using Django’s default persistent connection behavior (CONN_MAX_AGE set to a positive value). Each app server process maintains its own connection to PostgreSQL. That is 20 connections from your app tier. Fine. Now you scale the application to 10 hosts, each running the app in a gunicorn server with 4 worker processes. That is 80 connections, most of which are idle at any given time — your database is doing nothing, but PostgreSQL has forked 80 backend processes, each consuming memory and table slots. Scale to 30 hosts and now you have 240 idle connections before a single user has made a request. This is the double-dip cost of persistent connections without a pooler.

PgBouncer Architecture

PgBouncer is a single-process, event-driven connection pooler written in C. It sits between your application and PostgreSQL, presenting itself to the application as a PostgreSQL server and presenting itself to PostgreSQL as a client. Its job is to maintain a small pool of real PostgreSQL connections and multiplex a much larger number of application connections across them.

                     Application Layer
  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐
  │ App Srv 1│  │ App Srv 2│  │ App Srv 3│  │ App Srv 4│
  │ (conn 1) │  │ (conn 2) │  │ (conn 3) │  │ (conn 4) │
  │ (conn 5) │  │ (conn 6) │  │ (conn 7) │  │ (conn 8) │
  │ (conn 9) │  │ (conn 10)│  │ (conn 11)│  │ (conn 12)│
  └────┬─────┘  └────┬─────┘  └────┬─────┘  └────┬─────┘
       │              │              │              │
       └──────────────┴──────────────┴──────────────┘
                              │
                     ┌────────▼────────┐
                     │   PgBouncer     │
                     │  (port 6432)    │
                     │                 │
                     │  client pool:   │
                     │  100 virtual    │
                     │  connections    │
                     │                 │
                     │  server pool:   │
                     │  10 real        │
                     │  connections    │
                     └────────┬────────┘
                              │
              ┌───────────────┼───────────────┐
              │               │               │
       ┌──────▼──┐     ┌──────▼──┐     ┌──────▼──┐
       │PG Srv   │     │PG Srv   │     │PG Srv   │
       │Backend 1│     │Backend 2│     │Backend 3│
       └─────────┘     └─────────┘     └─────────┘
                     PostgreSQL Server

The key distinction: connections from application servers to PgBouncer are virtual — they are TCP connections that PgBouncer accepts and manages using libevent. Connections from PgBouncer to PostgreSQL are real backend processes. PgBouncer assigns a real server connection to a virtual client connection when the client needs to execute a query, and returns that server connection to the pool when the client is done with it (when exactly “done” means depends on the pooling mode). A client that is idle — connected but not actively running a query or transaction — holds no server connection and costs almost nothing.

Authentication in PgBouncer is worth understanding carefully because it has two layers. PgBouncer authenticates clients using its own credential store, configured via auth_type in pgbouncer.ini. It then uses its own credentials to connect to PostgreSQL as the proxied user. The supported auth_type values are:

• trust: accept any connection without password (LAN-only environments)
• plain: accept plaintext passwords (only over TLS)
• md5: traditional PostgreSQL MD5 password hashing
• scram-sha-256: modern SCRAM authentication (recommended)
• hba: use PostgreSQL-style HBA rules for per-client decisions
• ldap: LDAP authentication (added in 1.25.0)
• pam: PAM authentication

Passwords are stored in a userlist.txt file in the format "username" "password_or_hash", or PgBouncer can be configured with auth_query to delegate password lookups to PostgreSQL itself via a query against pg_shadow — useful when you want a single source of truth for credentials.

The PgBouncer admin console is a pseudo-database named pgbouncer. You connect to it like any other database and issue plain-SQL-style commands to inspect and control the pooler. Access requires a user listed in the admin_users parameter.

1

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

The Three Pooling Modes

This is where most PgBouncer discussions either gloss over the tradeoffs or bury the important parts. The pooling mode is the single most consequential configuration decision, and choosing the wrong one causes subtle, hard-to-reproduce bugs.

Session Pooling

In session mode, a server connection is assigned to a client when the client connects and is released back to the pool only when the client disconnects. From the application’s perspective, this is functionally identical to a direct connection — all session-level state (temporary tables, advisory locks, prepared statements, SET variables) persists for the duration of the client connection. The only benefit is that PgBouncer limits the total number of server connections even when many clients are connected — idle clients do not hold server connections if there are not enough to go around, they simply wait.

Session mode is the least valuable mode. It does not allow the reuse of server connections across multiple application requests unless the application explicitly disconnects and reconnects between logical “jobs.” Most connection pooling frameworks already do this kind of connection reuse internally, making PgBouncer in session mode largely redundant. Use it only as a transitional measure while you work toward transaction mode.

Transaction Pooling

In transaction mode, a server connection is assigned to a client for the duration of a single transaction — BEGIN through COMMIT or ROLLBACK. The moment a transaction ends, the server connection is returned to the pool and becomes available for any other waiting client. A client that sends an autocommit query (no explicit BEGIN) gets a server connection for just that single statement.

This is the mode that makes PgBouncer genuinely powerful. A server pool of 20 connections can efficiently serve hundreds of concurrent clients because most clients spend the vast majority of their time between transactions — waiting for the application to process results, making HTTP requests to other services, waiting for a job queue item. The fraction of time any client is actually in a transaction is typically small, and that is the only time they need a server connection.

Transaction mode is what you should almost always be running in production. It requires accepting some real tradeoffs, covered below.

Statement Pooling

Statement mode assigns a server connection per individual SQL statement. Even within an explicit transaction, each statement may be executed on a different server connection. This means multi-statement transactions are completely broken — a BEGIN on connection A, followed by an INSERT routed to connection B, followed by a COMMIT on connection A commits nothing on connection B. Statement mode is essentially unusable for any real application that uses transactions, which is all of them. It exists as a curiosity and is documented primarily to explain why you should not use it.

Mode Comparison: When Server Connections Are Held

The following diagram shows a sequence of client operations and when a server connection is occupied in each mode. The client performs: connect, run query 1, run query 2 inside a transaction, disconnect.

Client operations:
  [CONNECT] [Q1] [BEGIN] [Q2a] [Q2b] [COMMIT] [DISCONNECT]

Session mode:
  Server conn: ├──────────────────────────────────────────────┤
               (held entire client session)

Transaction mode:
  Server conn:         ├──┤  ├──────────────────┤
                        Q1    BEGIN..COMMIT block
               (held only during each transaction/autocommit)

Statement mode:
  Server conn:         ├──┤  ├───┤ ├───┤ ├─────┤
                       (held per individual statement only)

What Breaks in Transaction Pooling Mode

This section is not optional reading. Transaction pooling is extremely effective but it silently breaks a set of PostgreSQL features because those features rely on session-level state persisting between transactions. In transaction mode, each transaction may execute on a different server connection, so session state is not reliable. Here is the complete accounting:

SET statements: SET search_path = myschema, SET timezone = 'America/New_York', SET statement_timeout = 5000 — all of these set session-level variables that reset when the server connection returns to the pool. PgBouncer runs server_reset_query (DISCARD ALL by default) on server connections before returning them, which explicitly clears all session variables, temporary tables, prepared statements, and advisory locks. Any application that issues SET and expects it to persist is broken under transaction pooling.

Named prepared statements: PostgreSQL’s wire-protocol prepared statements (PREPARE foo AS SELECT ..., or the binary protocol’s Parse/Describe/Execute flow) are session-scoped. In transaction mode, a prepared statement created on connection A may not exist on connection B. This is the most common source of cryptic errors when adding PgBouncer to an existing application — ORMs and drivers that transparently use prepared statements start failing with “prepared statement does not exist.”

Advisory locks: pg_advisory_lock() and pg_advisory_xact_lock() have different scope. Transaction-scoped advisory locks (pg_advisory_xact_lock) work fine in transaction pooling because they release with the transaction. Session-scoped advisory locks (pg_advisory_lock) do not — they are released when the server connection returns to the pool, not when the application calls pg_advisory_unlock(). Application code that uses session-scoped advisory locks for distributed coordination will silently lose its locks.

LISTEN / NOTIFY: The LISTEN command subscribes a connection to a notification channel. Since PgBouncer routes transactions to arbitrary server connections, there is no guarantee that the connection that receives a NOTIFY is the connection your application is listening on. LISTEN/NOTIFY requires a dedicated, persistent connection outside of the PgBouncer pool. Many applications run a separate direct connection for pub/sub.

WITH HOLD cursors: A DECLARE ... WITH HOLD cursor persists past a transaction commit and lives until the end of the session. Since transaction pooling does not guarantee the same server connection for the next transaction, these cursors are lost. Regular (non-hold) cursors are fine because they are transaction-scoped.

Temporary tables: CREATE TEMPORARY TABLE creates a table that exists for the current session. Under transaction pooling, a temporary table created in one transaction may not exist in the next transaction if PgBouncer assigned a different server connection. Note that DISCARD ALL (the default server_reset_query) also explicitly drops temporary tables as part of its cleanup.

Two-phase commit: PREPARE TRANSACTION is a session-level operation that persists a transaction for later COMMIT PREPARED or ROLLBACK PREPARED. This is incompatible with transaction pooling mode.

*PgBouncer 1.21+ added experimental prepared statement tracking in transaction mode. As of 1.25.x, max_prepared_statements can be set to a non-zero value to enable PgBouncer’s protocol-level prepared statement caching, which intercepts Parse/Bind/Execute protocol messages and re-prepares statements as needed on whichever server connection is assigned. This partially resolves the ORM prepared statement problem but adds overhead and has limitations under high concurrency. Verify behavior under your specific ORM version before relying on it.

pgbouncer.ini Configuration

PgBouncer is configured through a single INI file, typically at /etc/pgbouncer/pgbouncer.ini. The file has two mandatory sections: [databases] which maps client-visible database names to real PostgreSQL databases, and [pgbouncer] which configures the pooler itself.

Here is a complete, production-grade configuration for a Django or FastAPI application with annotations:

  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
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193

;;;
;;; PgBouncer configuration
;;; /etc/pgbouncer/pgbouncer.ini
;;;

[databases]

; Map the client-facing name "appdb" to the real PostgreSQL database.
; Clients connect to pgbouncer:6432/appdb and are proxied to postgres:5432/appdb.
; You can override any connection parameter here.
appdb = host=127.0.0.1 port=5432 dbname=appdb

; Example: route a different client name to a different host/database
; analytics = host=pg-replica.internal port=5432 dbname=analytics pool_mode=session

; Wildcard entry: any database name not explicitly listed maps to itself on localhost.
; Useful in development, dangerous in production — comment out if you want explicit control.
; * = host=127.0.0.1

[pgbouncer]

;;;
;;; Network settings
;;;

; Listen on all interfaces. In production, restrict to loopback or internal network.
listen_addr = 127.0.0.1
listen_port = 6432

; Enable Unix socket as well (useful for local app servers, avoids TCP overhead).
; unix_socket_dir = /var/run/postgresql

;;;
;;; Authentication
;;;

; Use SCRAM-SHA-256 for client authentication. Requires PostgreSQL 10+.
; Use md5 if you have older clients or hashed passwords in userlist.txt.
auth_type = scram-sha-256

; Path to the password file. Each line: "username" "password_or_hash"
auth_file = /etc/pgbouncer/userlist.txt

; Alternatively, delegate password lookups to PostgreSQL:
; Requires a superuser or a user with SELECT on pg_shadow.
; auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
; auth_user = pgbouncer_auth

; Users allowed to connect to the admin console (pgbouncer database).
admin_users = pgbouncer

; Users allowed to run SHOW commands but not admin commands.
stats_users = monitoring

;;;
;;; Pooling mode
;;;

; transaction mode is the right choice for stateless applications.
; session mode if your app uses SET, prepared statements, or advisory locks.
pool_mode = transaction

;;;
;;; Connection limits
;;;

; Maximum total client connections PgBouncer will accept across all databases.
; This is the hard ceiling. Set to match your app tier's maximum connection demand.
; Rule of thumb: num_app_servers * connections_per_server_process * 1.5
max_client_conn = 500

; Server connections per (database, user) pair. This is the pool size for each
; database+user combination. This is the number of actual PostgreSQL backends
; PgBouncer maintains. Start conservative (10-25) and raise based on maxwait.
default_pool_size = 20

; Keep at least this many server connections alive even when idle.
; Eliminates connection setup latency during low-traffic periods.
; Set to 2-5 for most applications.
min_pool_size = 5

; Extra server connections available when the main pool is exhausted.
; These are borrowed against max_connections headroom. Use sparingly.
reserve_pool_size = 5

; How long (seconds) a client can wait before reserve_pool is activated.
; 1 second is a reasonable threshold before escalating to reserve.
reserve_pool_timeout = 1

; Maximum time (seconds) a client can wait for a server connection before
; receiving an error. Prevents runaway connection queuing.
; 0 = wait indefinitely (not recommended in production).
client_login_timeout = 60

;;;
;;; Timeouts
;;;

; Close idle server connections after this many seconds.
; Reduces backend process count during low-traffic periods.
; Set lower than your firewall/NAT idle timeout.
server_idle_timeout = 600

; Close idle client connections after this many seconds.
; Reclaims resources from clients that have connected and gone silent.
client_idle_timeout = 0

; Kill a server connection if it has been connected for longer than this.
; Forces periodic reconnection; useful for picking up config changes.
server_lifetime = 3600

; Maximum time a transaction can be active before the client connection is closed.
; Added in PgBouncer 1.25.0. Helps catch runaway transactions.
; transaction_timeout = 0

;;;
;;; Query reset
;;;

; Run this query after every transaction in transaction mode to clean up session state.
; DISCARD ALL clears: SET variables, prepared statements, advisory locks, temp tables.
; Required for transaction pooling safety.
server_reset_query = DISCARD ALL

; Only run server_reset_query if the client sends a RESET command.
; Set to 0 to always run it (correct for transaction mode).
server_reset_query_always = 0

; Health check query. Sent to a server connection before giving it to a client.
; SELECT 1 is the standard minimal check. Set to empty to disable (reduces latency).
server_check_query = select 1
server_check_delay = 30

;;;
;;; Prepared statement support (PgBouncer 1.21+)
;;;

; Number of prepared statements PgBouncer tracks per server connection.
; 0 = disabled (safest default for transaction mode).
; Set to a positive value to enable protocol-level prepared statement caching.
; Verify compatibility with your ORM and driver before enabling in production.
max_prepared_statements = 0

;;;
;;; TCP keepalive settings
;;;

; Essential for detecting stale connections through firewalls and NAT.
; Without keepalives, a connection through an AWS security group can silently
; die and PgBouncer will keep trying to use it.
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

;;;
;;; TLS configuration
;;;

; Terminate TLS from clients while using plaintext to local PostgreSQL.
; This is the common pattern: TLS on the client-facing port, plaintext on loopback.
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key
; client_tls_ca_file = /etc/pgbouncer/ca.crt   ; for mutual TLS

; For direct TLS (PostgreSQL 17+ clients), supported since PgBouncer 1.25.0:
; client_tls_sslmode = require
; No additional config needed — PgBouncer auto-detects direct vs STARTTLS.

; Connection from PgBouncer to PostgreSQL.
; If PostgreSQL is on localhost, plaintext is fine. For remote: use require or verify-full.
server_tls_sslmode = prefer

;;;
;;; Logging
;;;

; Log to syslog or a file. In containerized environments, log to stdout.
; logfile = /var/log/pgbouncer/pgbouncer.log
; syslog = 1

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

; verbose = 0  ; Set to 1 or 2 for debugging. Very noisy under load.

;;;
;;; Process
;;;

pidfile = /var/run/pgbouncer/pgbouncer.pid

userlist.txt stores credentials. For SCRAM-SHA-256, store the SCRAM verifier from PostgreSQL directly:

1
2

-- On your PostgreSQL server, extract the verifier for each user:
SELECT usename, passwd FROM pg_shadow WHERE usename = 'appuser';

Copy the full SCRAM verifier string (starts with SCRAM-SHA-256$) into userlist.txt:

"appuser" "SCRAM-SHA-256$4096:...base64data...=:...verifier..."
"pgbouncer" "SCRAM-SHA-256$4096:..."

For MD5 (legacy), the hash is md5 + MD5(“password” + “username”):

1
2
3

echo -n "mysecretpasswordappuser" | md5sum
# Take the hash, prefix with "md5"
# Result: "appuser" "md5a3f8b29c1e77d4f2e6a9b3c8..."

Monitoring with SHOW Commands

Connect to the PgBouncer admin console from any host that is allowed in admin_users:

1

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

SHOW POOLS

SHOW POOLS is the primary operational view. Run it when investigating slow queries, connection wait times, or pool exhaustion.

1

pgbouncer=# SHOW POOLS;

Example output (formatted):

 database | user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
----------+---------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 appdb    | appuser |        15 |          0 |             0 |        12 |         8|        0 |         0 |        0 |       0 |          0 | transaction
 pgbouncer| pgbouncer|         1 |          0 |             0 |         0 |         0|        0 |         0 |        0 |       0 |          0 | statement

Field-by-field breakdown:

When you see cl_waiting > 0 and maxwait climbing, you have a pool exhaustion event. The immediate options are: increase default_pool_size for this database/user pair (if PostgreSQL has headroom in max_connections), kill long-running transactions that are holding server connections, or reduce connection demand from the application.

SHOW CLIENTS

1

pgbouncer=# SHOW CLIENTS;

Shows each connected client with its address, state (active, used, waiting, idle), current query if active, and how long it has been waiting. Useful for identifying which specific application servers are accumulating waiting connections.

SHOW SERVERS

1

pgbouncer=# SHOW SERVERS;

Shows each actual PostgreSQL backend connection PgBouncer has open — state (active, idle, used, tested, login), how long it has been connected, and which client it is currently assigned to. Use this to verify that your default_pool_size is being utilized as expected.

SHOW STATS

1

pgbouncer=# SHOW STATS;

Per-database aggregates: total requests, bytes sent/received, total and average query/transaction/wait time. The avg_query_time and avg_wait_time columns (in microseconds) are useful for trending — a rising avg_wait_time indicates growing pool pressure before it manifests as maxwait alerts.

SHOW CONFIG

1

pgbouncer=# SHOW CONFIG;

Displays all current configuration values with their types and whether they can be changed at runtime. Many parameters can be updated without a restart using SET:

1
2

pgbouncer=# SET default_pool_size = 30;
pgbouncer=# RELOAD;

PAUSE and RESUME for Maintenance

One of PgBouncer’s most operationally useful features: PAUSE drains all active server connections and stops accepting new queries, queuing clients until RESUME is called. This enables zero-downtime PostgreSQL maintenance:

1
2
3
4
5
6
7
8

-- Pause a specific database (blocks until all active transactions complete)
pgbouncer=# PAUSE appdb;

-- Perform PostgreSQL maintenance (failover, upgrade, reconfig)
-- ...

-- Resume when ready
pgbouncer=# RESUME appdb;

Clients see elevated latency during the pause window but do not receive errors (as long as client_login_timeout is not exceeded). This is the correct way to perform a PostgreSQL primary failover when PgBouncer is in front.

Prometheus Metrics

The pgbouncer_exporter (available as a standalone binary or Docker image at prometheuscommunity/pgbouncer-exporter) scrapes all SHOW command output and exposes it as Prometheus metrics. Key metrics to alert on:

• pgbouncer_pools_client_waiting_connections > 0 — pool pressure
• pgbouncer_pools_server_idle_connections == 0 AND pgbouncer_pools_client_active_connections > 0 — no idle connections available
• pgbouncer_pools_maxwait_seconds > 1 — clients waiting too long
• pgbouncer_pools_server_active_connections / default_pool_size > 0.9 — pool near saturation

Running PgBouncer

Installation

On Debian/Ubuntu, the package is in the main repository:

1
2
3

apt install pgbouncer
systemctl enable pgbouncer
systemctl start pgbouncer

The package installs configuration to /etc/pgbouncer/pgbouncer.ini and the userlist to /etc/pgbouncer/userlist.txt. The systemd unit runs PgBouncer as the postgres user by default.

PgBouncer 1.25.0 added support for Type=notify-reload in the systemd unit (requires systemd 253+), which allows systemctl reload pgbouncer to trigger a config reload cleanly.

Docker Compose Sidecar

Running PgBouncer as a sidecar in a Docker Compose stack is the standard pattern for containerized deployments:

 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

services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_DB: appdb
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:latest   # well-maintained community image
    environment:
      DB_HOST: postgres
      DB_USER: appuser
      DB_PASSWORD: ${POSTGRES_PASSWORD}
      DB_NAME: appdb
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 200
      DEFAULT_POOL_SIZE: 20
      AUTH_TYPE: scram-sha-256
    ports:
      - "6432:5432"  # Expose on 6432 locally, map to container's 5432
    depends_on:
      postgres:
        condition: service_healthy

  app:
    build: .
    environment:
      DATABASE_URL: postgresql://appuser:${POSTGRES_PASSWORD}@pgbouncer:5432/appdb
    depends_on:
      - pgbouncer

volumes:
  pgdata:

Health Checks

pg_isready works against PgBouncer the same as PostgreSQL:

1

pg_isready -h 127.0.0.1 -p 6432 -U appuser -d appdb

For a deeper check that actually exercises the connection:

1

psql -h 127.0.0.1 -p 6432 -U appuser -d appdb -c "SELECT 1" -q -t

Connection Pooling with ORMs and Frameworks

The Double-Pooling Problem

Every major ORM and database driver has its own connection pooling layer. SQLAlchemy has QueuePool. Django has CONN_MAX_AGE. node-postgres has a Pool class. When you put PgBouncer in front of these, you have two layers of pooling, and the ORM’s pool holds actual server-side connections in PgBouncer’s server pool. The ORM pool is the client, PgBouncer is the server — but the connections the ORM holds are real PostgreSQL connections from PgBouncer’s perspective.

This is not inherently wrong, but it means you need to size both pools correctly and understand their interaction.

Django

Django’s CONN_MAX_AGE setting controls how long a database connection is reused between requests. The default is 0 (close after each request). A positive value keeps connections open, implemented as a per-thread connection cache.

When using PgBouncer in transaction mode, the correct Django configuration is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': '6432',
        'NAME': 'appdb',
        'USER': 'appuser',
        'PASSWORD': os.environ['DB_PASSWORD'],
        'CONN_MAX_AGE': 0,  # Disable Django's persistent connections
        # Let PgBouncer handle all connection reuse
    }
}

Setting CONN_MAX_AGE = 0 means Django opens a new connection to PgBouncer for each request and closes it when the request ends. From PgBouncer’s perspective, clients connect and disconnect rapidly, but PgBouncer’s server-side connections to PostgreSQL persist. This is the intended pattern — PgBouncer does the pooling, Django does not.

If you set CONN_MAX_AGE to a positive value with transaction pooling, each Django thread holds a persistent connection to PgBouncer, which in turn holds that client’s server connection for the duration of any active transaction. This works but is less efficient: you are relying on Django’s connection count (threads × hosts) staying within PgBouncer’s max_client_conn, and PgBouncer’s transaction pooling still works for individual transactions within the persistent connection.

Django’s DatabaseWrapper does not use named prepared statements at the protocol level, so prepared statement conflicts are not an issue.

SQLAlchemy

SQLAlchemy’s connection pool interacts with PgBouncer in two important ways:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://appuser:password@127.0.0.1:6432/appdb",
    # pool_pre_ping sends "SELECT 1" before giving a connection to application code.
    # Essential with PgBouncer: catches server connections that were closed by
    # server_idle_timeout or a PostgreSQL restart before you try to use them.
    pool_pre_ping=True,
    # Size your ORM pool relative to PgBouncer's default_pool_size.
    # A pool_size of 10 across 4 app processes = 40 client connections to PgBouncer,
    # each competing for default_pool_size (20) server connections.
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
)

Prepared statements are the most common source of SQLAlchemy + PgBouncer problems. psycopg2 uses the PREPARE/EXECUTE protocol under the hood for statements executed more than prepare_threshold times (default: 5). When transaction pooling routes subsequent executions of that prepared statement to a different server connection, you get:

ProgrammingError: (psycopg2.errors.InvalidSqlStatementName)
    prepared statement "s1" does not exist

The fix is to disable prepared statements at the driver level:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# psycopg2: use a specific options string
engine = create_engine(
    "postgresql+psycopg2://appuser:password@127.0.0.1:6432/appdb?options=-c%20statement_cache_size%3D0",
    pool_pre_ping=True,
)

# Or with psycopg3 (psycopg), set prepare_threshold=0:
engine = create_engine(
    "postgresql+psycopg://appuser:password@127.0.0.1:6432/appdb",
    connect_args={"prepare_threshold": 0},
    pool_pre_ping=True,
)

For serverless or very short-lived processes where connection setup cost is negligible, NullPool tells SQLAlchemy to not pool at all — it opens and closes a connection for each context manager block, and PgBouncer handles all the real pooling:

1
2
3
4
5
6

from sqlalchemy.pool import NullPool

engine = create_engine(
    "postgresql+psycopg2://appuser:password@127.0.0.1:6432/appdb",
    poolclass=NullPool,
)

node-postgres (pg)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

const { Pool } = require('pg');

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,
  database: 'appdb',
  user: 'appuser',
  password: process.env.DB_PASSWORD,
  // Do not exceed PgBouncer's default_pool_size.
  // If you run 10 Node processes each with max:5, that is 50 client connections
  // competing for default_pool_size server connections.
  max: 5,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Node’s pg driver does not use named prepared statements by default in pool mode, so prepared statement conflicts are generally not an issue. However, if you use client.query() in statement mode with $1 parameters, the driver uses protocol-level parameters without preparing, which is safe.

GORM (Go)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

dsn := "host=127.0.0.1 user=appuser password=secret dbname=appdb port=6432 sslmode=require"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
if err != nil {
    panic("failed to connect")
}

sqlDB, _ := db.DB()
// Match OpenConns to PgBouncer's default_pool_size or less.
sqlDB.SetMaxOpenConns(20)
sqlDB.SetMaxIdleConns(10)
// Keep idle connections shorter than PgBouncer's server_idle_timeout.
sqlDB.SetConnMaxIdleTime(5 * time.Minute)
sqlDB.SetConnMaxLifetime(1 * time.Hour)

GORM uses pgx as its underlying driver by default (gorm v2). pgx does use named prepared statements. For PgBouncer transaction mode compatibility:

1
2
3

// Disable prepared statement cache in pgx
config, _ := pgxpool.ParseConfig(dsn)
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

PgBouncer vs Pgpool-II vs Supavisor vs RDS Proxy

These are not interchangeable tools solving the same problem in different ways — they occupy different positions on the complexity/capability spectrum, and choosing among them requires clarity about what you actually need.

PgBouncer has one job: connection pooling. It does it extremely well. The codebase is small, the configuration surface is manageable, and its event-driven single-process architecture means it adds approximately 50-100 microseconds of overhead per query with minimal CPU and memory cost. It handles tens of thousands of client connections on modest hardware. Its limitations are those described throughout this post: no query routing, no read/write splitting, no high-availability management, no built-in clustering.

Pgpool-II is a middleware layer with connection pooling as one of several features. It also provides: automatic load balancing of read queries across a primary and multiple replicas, query result caching, watchdog-based high availability with automatic failover, and parallel query execution across multiple PostgreSQL servers. The tradeoff is significant complexity. Pgpool-II uses a process-per-connection model itself (not event-driven), which limits its scalability and increases its per-connection overhead. Configuration is substantially more complex than PgBouncer. If you need read replica load balancing and are not already using a dedicated proxy layer (HAProxy, Patroni with built-in load balancing, or your cloud provider’s endpoint), Pgpool-II is worth evaluating. If you only need connection pooling, it is overkill that will cause you maintenance burden without commensurate benefit.

Supavisor is Supabase’s open-source Elixir-based connection pooler, initially developed for Supabase’s managed PostgreSQL platform and open-sourced in 2023. It is designed for multi-tenant SaaS deployments — the connection string encodes the tenant identifier, and Supavisor routes to the correct PostgreSQL instance. It supports both session and transaction pooling, exposes native Prometheus metrics, and is built on the BEAM VM, which handles massive concurrency efficiently. As of the current release, Supavisor has matured significantly and is worth serious consideration for new deployments, particularly in multi-tenant architectures or cloud-native environments where you want built-in observability. Note that as of early 2025, Supabase deprecated session mode on port 6543 in their hosted offering (port 6543 is transaction-only; session mode uses direct connections on port 5432). Supavisor’s self-hosted version retains both modes. Active development continues at github.com/supabase/supavisor.

RDS Proxy is AWS’s managed connection pooler, available for RDS PostgreSQL and Aurora PostgreSQL. It integrates with IAM authentication, AWS Secrets Manager, and provides automatic failover handling. The appeal is zero operational overhead — you configure it in the RDS console and point your application at the proxy endpoint. The cost is real: RDS Proxy is charged per vCPU of the underlying database, which can add meaningfully to your RDS bill. It also adds latency compared to PgBouncer running on the same host as your application. It only supports session and transaction modes, and has some limitations around the types of PostgreSQL features it supports. For AWS shops with compliance requirements around credential management (IAM auth, automatic rotation via Secrets Manager) and tolerance for the cost, it is a reasonable managed solution.

Troubleshooting Common Issues

“sorry, too many clients already” — This comes from PostgreSQL, not PgBouncer. PgBouncer has exceeded max_connections on the PostgreSQL server. Check sv_active + sv_idle + sv_login across all pools with SHOW POOLS. The sum should be well below PostgreSQL’s max_connections. Also account for connections from monitoring tools, migration runners, and other processes that connect directly to PostgreSQL outside of PgBouncer.

“no more connections allowed (max_client_conn)” — This comes from PgBouncer. You have hit the max_client_conn ceiling. Check if your application is opening connections correctly and not leaking them. If your client count is genuinely that high, increase max_client_conn — it is cheap in PgBouncer (no process fork), but verify you are not masking an application connection leak.

maxwait growing — clients stuck in queue — Pool exhausted: all default_pool_size server connections are in use and new clients are waiting. Diagnose which connections are holding the pool: SHOW SERVERS to see which server connections are active, then check pg_stat_activity on PostgreSQL to see what those backends are doing. Common culprits: slow queries holding server connections longer than expected, lock waits, or missing COMMIT/ROLLBACK in error paths. Short term: increase default_pool_size if PostgreSQL has headroom. Long term: fix the slow queries.

“prepared statement X does not exist” — Your ORM or driver is using protocol-level prepared statements, and transaction pooling is routing subsequent executions to a different server connection that does not have the prepared statement. Fix: disable prepared statement caching at the driver level (see ORM section above), or enable PgBouncer’s built-in prepared statement support via max_prepared_statements. PgBouncer 1.24 turned on prepared statement support by default, so if you upgraded without testing this interaction, verify your driver configuration.

Authentication failures after updating userlist.txt — PgBouncer caches credentials at startup. After editing userlist.txt, send RELOAD via the admin console or systemctl reload pgbouncer. Check that the password format matches auth_type — a SCRAM verifier in userlist.txt with auth_type = md5 will fail.

Connections not being returned to pool / pool permanently exhausted — Long-running transactions that never commit or roll back hold server connections indefinitely. In transaction mode, a client that opens a BEGIN and then goes idle (network partition, application hang, forgotten transaction in code) holds a server connection until the connection is closed or the transaction times out. Use transaction_timeout (added in PgBouncer 1.25.0) and idle_transaction_timeout in PostgreSQL itself to bound how long an open transaction can sit idle.

Stale connections after NAT/firewall timeout — A connection through an AWS security group or a corporate NAT gateway can be silently closed by the network device after the idle timeout (typically 350-900 seconds in AWS). Neither PgBouncer nor PostgreSQL knows the connection is dead. The next attempt to use that connection fails with a cryptic socket error. Fixes: set tcp_keepalive = 1 in PgBouncer with tcp_keepidle shorter than the NAT timeout, and set server_idle_timeout in PgBouncer to also be shorter than the NAT timeout. Both mechanisms serve the same goal by different means — keepalives detect dead connections actively, idle timeout recycles them proactively.

SCRAM authentication performance — If you see high CPU on SCRAM authentication with many short-lived connections, PgBouncer 1.25.0 added a scram_iterations configuration option that reduces the PBKDF2 iteration count. The default of 4096 is the PostgreSQL default; reducing it to 1000 or even 100 is a reasonable tradeoff if authentication is a CPU bottleneck in your specific environment. Note that this only applies to new passwords set after changing the setting.

Sizing and Operational Recommendations

There is no universal formula for default_pool_size, but the following reasoning produces a good starting point for most OLTP applications:

Identify the maximum sustainable query throughput you need. For a typical web application running 5ms average query time, a single PostgreSQL connection can handle roughly 200 queries per second (1000ms / 5ms). If you need to handle 2,000 queries per second, you need at least 10 server connections actively running queries at all times. Add 50% headroom for spikes: default_pool_size = 15. Add reserve_pool_size = 5 for burst protection.

Monitor SHOW STATS avg_query_time and SHOW POOLS maxwait over time. If maxwait is consistently near zero and sv_idle is always large, you may be able to reduce default_pool_size. If maxwait is regularly above 100ms, increase it.

Keep the total server connections across all pools well below PostgreSQL’s max_connections. A reasonable rule: sum(default_pool_size across all pools) + sum(reserve_pool_size) + 20 (for direct connections) < max_connections. The 20 is headroom for monitoring, migrations, psql debugging sessions, and anything else that connects directly.

PgBouncer itself is not a single point of failure in the sense that if it crashes, your application is down — but it does need to be highly available in production. Run two PgBouncer instances behind a virtual IP (Keepalived/VRRP) or use a load balancer in front of multiple PgBouncer instances. PgBouncer’s statelessness (server connections are re-established on startup) makes active-passive failover straightforward. In Kubernetes, running PgBouncer as a sidecar in each application pod eliminates the single point of failure concern entirely at the cost of more total server connections to PostgreSQL.

Connection pooling solves a real structural problem in PostgreSQL, but it is not a substitute for query optimization. A pool full of slow queries is a pool full of connections — pooling buys you headroom to handle connection count, but if each of those connections is running a 10-second query that should take 10 milliseconds, no amount of pooling tuning will make your database perform well. PgBouncer gives you the data to see this: rising avg_query_time in SHOW STATS is the signal that query performance, not connection management, is the bottleneck.