Connection Pooling Realities

Postgres connections are expensive in a way that most database users don't fully internalize until they hit the wall. Each connection spawns a dedicated backend process, allocates shared memory for its working set, and holds locks and transaction state. At a few hundred connections, Postgres starts spending more time on connection overhead than on query execution. The right response is not to raise max_connections indefinitely—it's to put a pooler in front that multiplexes thousands of application connections onto a small, stable set of Postgres backends.

Why Connections Are Not Cheap

A Postgres backend is a full OS process (fork-on-connect model). Each one:

• Allocates work_mem on demand (potentially multiple times for sort/hash operations)
• Occupies a slot in pg_stat_activity and holds open file descriptors
• Contributes to lock manager overhead proportional to connection count
• Adds to checkpoint and WAL writer scheduling pressure

The benchmark rule of thumb: Postgres performance peaks at roughly 2–4× CPU core count in terms of active concurrent backends. Beyond that, throughput stays flat or drops while latency rises. For a 16-core instance, that's 32–64 active backends—not 500.

PgBouncer Pooling Modes

PgBouncer offers three pooling modes that make fundamentally different guarantees about when a server connection is assigned to a client connection.

Session Pooling

A server connection is assigned when the client connects and released when the client disconnects. This is equivalent to not pooling at all—it reduces overhead from repeated TCP handshakes and authentication, but the server connection count equals the client count.

Use session pooling when: your application uses session-level features (SET, temp tables, advisory locks, LISTEN/NOTIFY) that must persist for the lifetime of the client connection.

Transaction Pooling

A server connection is assigned at the start of a transaction and returned to the pool when the transaction commits or rolls back. This is the mode that enables real multiplexing—a pool of 20 server connections can serve hundreds of concurrent clients whose transactions are short.

Client 1: BEGIN → [gets server conn 7] → COMMIT → [releases conn 7]
Client 2: BEGIN → [gets server conn 7] → ROLLBACK → [releases conn 7]
Client 3: SELECT → [gets server conn 3] → done → [releases conn 3]

Statement Pooling

A server connection is assigned per statement and released immediately after. Requires that all statements be auto-committed. Almost never appropriate for transactional workloads.

What Breaks Under Transaction Pooling

Transaction pooling is the right default for most OLTP workloads, but several application patterns implicitly assume session continuity and break silently:

Session-Level SET Commands

-- This SET applies to server connection 7
SET search_path = tenant_42, public;
 
-- If PgBouncer reassigns a different server connection next transaction,
-- the SET is gone. The next query runs with the wrong search_path.
 
-- Fix: use set_config() in a transaction, or configure search_path
-- in the PgBouncer connection_parameters or at the role level
ALTER ROLE app_user SET search_path = tenant_42, public;

Prepared Statements

Prepared statements are scoped to a server connection. Under transaction pooling, the next transaction may land on a different server connection where the statement was never prepared.

-- This fails intermittently under transaction pooling:
PREPARE get_user (bigint) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(42);
-- If the next connection doesn't have this prepared statement: ERROR
 
-- PgBouncer 1.21+ supports protocol-level prepared statement tracking
-- For older versions: disable prepared statements at the driver level
-- e.g., in psycopg2: connect(... prepare_threshold=None)
-- or in node-postgres: use simple query protocol

Advisory Locks

Advisory locks are session-scoped. Under transaction pooling they appear to work (transaction-level advisory locks are fine) but session-level advisory locks do not persist across transaction boundaries.

-- Session-level advisory lock: BROKEN under transaction pooling
SELECT pg_advisory_lock(12345);
-- Next request may go to a different server connection; lock is orphaned
 
-- Transaction-level advisory lock: SAFE under transaction pooling
SELECT pg_advisory_xact_lock(12345);
-- Released automatically at transaction end

Temporary Tables

Temporary tables are session-scoped and persist until the session ends. Under transaction pooling, a temp table created in one transaction may not exist in the next, and may be visible to a different client that gets the same server connection.

-- UNSAFE under transaction pooling:
CREATE TEMP TABLE staging AS SELECT ...;
-- Subsequent transaction may or may not see this table
 
-- Fix: use CTEs or WITH queries instead of temp tables
WITH staging AS (SELECT ...)
INSERT INTO target SELECT * FROM staging;

PgBouncer Configuration

[databases]
app = host=postgres-primary port=5432 dbname=app
 
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25         ; server connections per database+user combo
min_pool_size = 5
reserve_pool_size = 5          ; extra connections for brief spikes
reserve_pool_timeout = 3       ; seconds before using reserve pool
 
server_idle_timeout = 600      ; close idle server connections after 10m
client_idle_timeout = 0        ; don't close idle clients (let app manage)
server_lifetime = 3600         ; recycle server connections every hour
server_reset_query = DISCARD ALL  ; run after each transaction in session mode
 
; Prepared statement support (PgBouncer 1.21+)
max_prepared_statements = 100

Sizing the Pool

The target server connection count is not the number of application threads—it's the number of concurrently active (in-flight) queries at the database layer.

pool_size ≈ (CPU cores × 2) + effective_io_concurrency

For a 16-core instance with NVMe storage: (16 × 2) + 8 = 40 server connections as a starting point. Measure avg_query_duration × requests_per_second to estimate true concurrency; add 20% headroom.

-- Measure current connection utilization
SELECT
  datname,
  count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE wait_event_type = 'Lock') AS lock_waiting
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY datname;

Pgpool-II vs PgBouncer

PgBouncer is a purpose-built, high-performance pooler—low overhead, predictable behavior, and a single clear responsibility. Pgpool-II adds load balancing, query routing, and connection pooling but at the cost of complexity and additional failure modes. For most deployments, PgBouncer in front of a primary + read replicas (with application-layer read routing) is the simpler and more reliable architecture.

Key Takeaways

• Each Postgres backend is an OS process; effective concurrency peaks at roughly 2–4× CPU core count—a connection count far lower than most applications assume.
• Transaction pooling enables real multiplexing and is the right default; session pooling is only appropriate when session-level state (SET variables, temp tables, advisory locks) must persist.
• Prepared statements, session-level SET commands, session-level advisory locks, and temporary tables all break under transaction pooling; audit your application before switching modes.
• PgBouncer 1.21+ supports protocol-level prepared statement tracking; for older versions, disable prepared statements at the driver level or use simple query protocol.
• Size default_pool_size based on expected query concurrency (active in-flight queries), not application thread count—overly large pools defeat the purpose of pooling.
• Always run DISCARD ALL as server_reset_query in session pooling mode to clear all session state before the connection is returned; in transaction mode, PgBouncer handles this automatically.