PostgreSQL Connection Pooling: PgBouncer Modes, Pitfalls, and Production Config

PostgreSQL connections are expensive. Each backend process consumes roughly 10 MB of resident memory, holds a socket, and runs a separate process for authentication and query handling. When a thousand application threads each open their own connection, you’re not just using memory — you’re paying for context switching, increased lock contention on shared buffers, and the CPU cost of forking and authenticating new backends. The result is the classic “too many connections” failure mode: the database spends more time managing connections than executing queries.

The standard answer is connection pooling — maintaining a small set of real database connections and multiplexing client requests across them. But pooling comes in several flavors, each with different trade-offs around compatibility, performance, and correctness. Understanding these trade-offs is the difference between a pool that doubles your throughput and one that introduces subtle session-state bugs.

Three Pooling Modes

PgBouncer, the most widely deployed PostgreSQL connection pooler, offers three modes: session, transaction, and statement. The mode determines when a server connection returns to the shared pool and becomes available to other clients.

Session pooling assigns a server connection to a client for the entire lifetime of the client’s connection. When the client disconnects, the server connection goes back to the pool. This is the safest mode — full PostgreSQL compatibility, including prepared statements, temporary tables, advisory locks, and session-level SET commands. It’s also the least efficient, because most application connections spend the majority of their time idle between queries.

Transaction pooling releases the server connection at transaction commit or rollback. Between transactions, the same server connection can serve different clients. This is dramatically more efficient — ratios of 10:1 or more active clients per server connection are common. But it breaks any feature that relies on session state persisting across transactions.

Statement pooling returns the connection after every individual statement. Even multi-statement transactions are disallowed. This is rarely used in practice — the compatibility constraints are too severe for most applications.

The Transaction Pooling Compatibility Trap

Transaction pooling is where teams get into trouble. The fundamental constraint: since a client may run consecutive transactions on different server connections, any session-level state set in one transaction does not carry over to the next. This breaks several common PostgreSQL patterns.

Session variables set with plain SET (which defaults to SET SESSION) will bleed across clients. Client A sets SET statement_timeout = '5s', then releases the connection. Client B picks it up and unexpectedly inherits that timeout. The fix is SET LOCAL, which scopes the change to the current transaction:

-- WRONG: persists across transactions (and across clients)
SET statement_timeout = '10s';

-- CORRECT: scoped to this transaction only
BEGIN;
SET LOCAL statement_timeout = '10s';
SELECT long_running_report();
COMMIT;

Prepared statements are bound to a specific server connection in transaction pooling. If you PREPARE in one transaction and EXECUTE in the next, you’ll hit a “prepared statement does not exist” error because the second transaction likely runs on a different backend. PostgreSQL 14 and later supports discarding prepared statements, but the real solution is to keep prepare and execute in the same transaction, or use the protocol-level describe/execute flow that some drivers handle transparently.

Temporary tables suffer the same fate. A temp table created in one transaction isn’t visible if the next transaction lands on a different backend. The pattern that works: create, use, and drop the temp table within a single transaction block. Using ON COMMIT DROP ensures cleanup:

BEGIN;
CREATE TEMP TABLE staging_orders
    (LIKE orders INCLUDING DEFAULTS)
    ON COMMIT DROP;

-- Bulk insert, transform, then insert into the real table
INSERT INTO staging_orders SELECT * FROM incoming_orders;
INSERT INTO orders SELECT * FROM staging_orders WHERE valid = true;
COMMIT;
-- staging_orders is gone, regardless of which backend runs next

Configuring PgBouncer

PgBouncer runs as a lightweight process in front of PostgreSQL. The critical configuration lives in pgbouncer.ini. Here’s a production-oriented starting point for transaction pooling:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=production

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; Recommended mode for most web/mobile workloads
pool_mode = transaction

; Max connections PgBouncer will accept from clients
max_client_conn = 5000

; Max server connections per database/user pair
default_pool_size = 25

; Minimum size of the pool
min_pool_size = 10

; How long to wait before closing idle server connections
server_idle_timeout = 600

; Query timeout for individual statements
query_timeout = 30

; Don't run RESET ALL automatically (performance optimization
; in transaction mode where session state isn't expected)
server_reset_query = DISCARD ALL
server_reset_query_always = 0

The default_pool_size is the key tuning parameter. A common rule of thumb: the total number of server connections across all pools should not exceed max_connections on PostgreSQL (minus a few reserved for maintenance). If PostgreSQL allows 100 connections and you have 4 databases, a pool size of 20 per database leaves headroom. Monitor pg_stat_activity and PgBouncer’s SHOW POOLS command to find the sweet spot.

Application-Side Pooling

PgBouncer solves the server-side problem, but applications also need their own connection pool to avoid opening a TCP connection per query. In Go, database/sql handles this with three knobs:

import (
    "database/sql"
    "time"

    _ "github.com/jackc/pgx/v5/stdlib"
)

func openDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return nil, err
    }

    // Max simultaneously open connections.
    // When using PgBouncer in transaction mode, this can be higher
    // than the server can handle directly.
    db.SetMaxOpenConns(20)

    // Max idle connections kept in the pool
    db.SetMaxIdleConns(10)

    // How long an idle connection stays alive before closing
    db.SetConnMaxIdleTime(5 * time.Minute)

    // Max lifetime per connection (prevents stale connections)
    db.SetConnMaxLifetime(30 * time.Minute)

    return db, nil
}

When using PgBouncer in transaction mode, keep MaxOpenConns moderate — PgBouncer handles the multiplexing, so having hundreds of application-level connections doesn’t help. The application pool’s job is to avoid TCP handshake overhead for consecutive queries, not to replicate the server-side pool.

Monitoring and Tuning

PgBouncer exposes a virtual administrative database. Connect to it and run SQL-like commands to inspect pool health:

-- Connect to PgBouncer's admin database
-- psql -p 6432 -U pgbouncer pgbouncer

-- Show active pools and their connection counts
SHOW POOLS;

-- See current client/server connections
SHOW SOCKETS;

-- Check if clients are waiting for server connections
SHOW STATS;

The metric to watch is cl_waiting — the number of clients waiting for a server connection. If this is consistently above zero, your default_pool_size is too small for your workload. But don’t just increase it blindly: check PostgreSQL’s pg_stat_activity to understand what those connections are doing. Long-running queries holding connections are a signal to optimize the query, not expand the pool.

Wrapping Up

Connection pooling is one of the highest-leverage optimizations for PostgreSQL-backed applications. Moving from session pooling to transaction pooling can easily 10x your concurrent connection capacity, but it requires discipline: use SET LOCAL instead of SET, scope temporary tables with ON COMMIT DROP, and keep prepared statements within a single transaction. PgBouncer’s transaction mode is the right default for most web and API workloads, with session pooling reserved for background jobs that genuinely need session-level features.

The layered approach — a modest application-side pool feeding into PgBouncer’s transaction pool — gives you the best of both worlds: fast connection acquisition in the application and efficient backend utilization on the server. Start with conservative pool sizes, monitor cl_waiting and pg_stat_activity, and scale up only when the data tells you to.