# Postgres Connection Pooling with PgBouncer: Production FAQ

> Answers to the most common production questions about Postgres connection pooling with PgBouncer — pool modes, sizing, transaction mode caveats, and common failure patterns.

- **Published:** 2025-12-19
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, pgbouncer, connection-pooling, production, database
- **Canonical URL:** https://clixo.sh/blog/postgres-connection-pooling-pgbouncer-production-faq

Connection exhaustion is one of the most common production Postgres incidents, and it almost always comes as a surprise. The application works fine in development with a handful of connections, gets deployed, and under real traffic suddenly throws errors like `remaining connection slots are reserved for non-replication superuser connections`. Connection pooling via PgBouncer is the standard fix — but it comes with its own set of configuration decisions and caveats. This FAQ covers what teams actually run into.

## What Does PgBouncer Actually Do?

Postgres creates a process for each client connection. These processes consume memory and the database has a hard ceiling on the total number of connections (`max_connections`, which defaults to 100 on many managed instances). When your application opens more connections than Postgres can handle, requests fail.

PgBouncer sits between your application and Postgres. The application maintains a pool of connections to PgBouncer. PgBouncer maintains a much smaller pool of connections to Postgres, and multiplexes application requests across them. Your application can have hundreds of open connections to PgBouncer while Postgres sees only tens of actual connections.

```mermaid
flowchart LR
  A1["App instance 1"] --> PB[PgBouncer]
  A2["App instance 2"] --> PB
  A3["App instance N"] --> PB
  PB -->|"small server pool (pool_size)"| DB[("PostgreSQL")]
  PB -.->|"max_client_conn: hundreds"| A1
```

## What Are the Three Pool Modes?

### Session Mode

Each client connection gets a dedicated Postgres server connection for the duration of the session. PgBouncer in session mode provides no connection reuse — it behaves like a proxy that simply caps total connections. Rarely useful.

### Transaction Mode

A Postgres server connection is held only for the duration of a transaction, then returned to the pool. This is the most efficient mode and the one most production deployments use. A pool of 20 server connections can serve hundreds of application connections if transactions are short.

**Caveats in transaction mode:** Several Postgres features do not work correctly in transaction mode because they rely on per-session state:

- `SET` and session-level configuration (the configuration resets when the connection returns to the pool)
- Prepared statements (unless PgBouncer is configured to track them per client)
- Advisory locks
- `LISTEN` / `NOTIFY`
- Temporary tables (they are destroyed when the connection is returned)

If your application uses any of these features, configure them at the connection level or use a dedicated connection that bypasses PgBouncer for those queries.

### Statement Mode

Each individual SQL statement gets a server connection. Even more efficient than transaction mode but incompatible with multi-statement transactions. Rarely appropriate for application workloads.

## How Many Connections Should the Pool Have?

The rule of thumb: the number of server connections in the pool should match the number of CPUs on the Postgres host, multiplied by a small factor based on your I/O wait characteristics. For an I/O-bound workload, the multiplier can be 2 to 4. For a CPU-bound workload, stay closer to 1 to 2.

A pool larger than the database can actually utilize adds queue time without adding throughput — connections wait in PgBouncer's queue rather than being served faster.

**A reasonable starting point for a 4-core Postgres instance:**

```ini
pool_size = 10
max_client_conn = 500
```

Ten server connections serve five hundred application connections. Tune from there based on latency and queue depth metrics.

## How Do I Handle Prepared Statements in Transaction Mode?

The most common incompatibility between ORMs and PgBouncer transaction mode is prepared statements. Most ORMs (Prisma, SQLAlchemy, Django ORM, Rails ActiveRecord) use prepared statements by default.

**Option 1: Disable prepared statements in the ORM.**

In Prisma, set `pgbouncer=true` in the connection string. In ActiveRecord, configure `prepared_statements: false`. In SQLAlchemy, set `prepare_args={'prepared_statement_cache_size': 0}` in certain adapters.

**Option 2: Enable `server_reset_query` in PgBouncer.**

Set `server_reset_query = DISCARD ALL` in PgBouncer configuration. This resets all prepared statements when a server connection is returned to the pool. It adds a small overhead per transaction.

**Option 3: Use session mode.**

Accept lower connection efficiency in exchange for full compatibility. Appropriate when connection counts are manageable and transaction mode caveats are a maintenance burden.

## What Is the `server_reset_query` Setting?

`server_reset_query` is the SQL command PgBouncer runs on a server connection before returning it to the pool, to clean up session state. The default is `DISCARD ALL`, which clears temporary tables, prepared statements, advisory locks, and session-level settings.

In transaction mode, `server_reset_query` is not run between transactions by default — only when a connection is about to be assigned to a different client. This is why session state leaks between clients are possible in transaction mode if session-scoped commands are used without explicit cleanup.

## How Do I Monitor PgBouncer in Production?

PgBouncer exposes a virtual database called `pgbouncer` with admin commands:

```sql
-- Connect to the pgbouncer admin
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- Show pool status
SHOW POOLS;

-- Show client connections
SHOW CLIENTS;

-- Show server connections
SHOW SERVERS;

-- Show configuration
SHOW CONFIG;
```

Key metrics to monitor:

- `cl_waiting`: clients waiting for a server connection. If this is non-zero regularly, your pool is undersized.
- `sv_idle`: idle server connections available in the pool. If this is consistently zero, connections are fully utilized.
- `avg_query_time`: average query time through the pool. Spikes here indicate slow queries, not pool problems.

Export these metrics to your monitoring system (Prometheus via `pgbouncer_exporter` is common).

## What Are the Most Common PgBouncer Failure Patterns?

**`cl_waiting` queue buildup:** All server connections are in use and new requests queue up. Symptoms: latency spikes, eventually timeout errors. Fix: increase pool size, reduce query duration, or add a read replica.

**Connection string not routing through PgBouncer:** After adding PgBouncer, the application still connects directly to Postgres on port 5432 instead of PgBouncer on port 6432. Verify the connection string in each service.

**Prepared statement errors after pool reassignment:** Statements prepared on one server connection are not available on another. Fix: disable prepared statements in the ORM or use `DISCARD ALL` as `server_reset_query`.

**PgBouncer single point of failure:** A single PgBouncer process is itself a bottleneck and a failure point. For production workloads, run multiple PgBouncer instances behind a load balancer, or use a managed pool (AWS RDS Proxy, Supabase's built-in pooler).

---

If you are setting up a Postgres deployment for a production product and want to get connection pooling, monitoring, and reliability right from the start, [start a build](https://clixo.sh/#contact) with Clixo and we can help you design the full database infrastructure.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)