PgBouncer Production Guide: Connection Pooling for Django, FastAPI, Node.js, and Java

It's 2:00 AM on a Thursday. Your monitoring dashboard lights up red. The API is returning 500s across the board. You SSH in, tail the logs, and there it is — the one PostgreSQL error message that has ruined more weekends than any syntax bug ever could:

FATAL: sorry, too many clients already

Your Django API is running 8 Gunicorn workers across 3 pods. Your FastAPI service has 4 Uvicorn workers. A Node.js analytics service is running 6 cluster forks. And a Java Spring Boot batch processor just kicked off its nightly job with 20 HikariCP connections. That's over 200 connections hammering a PostgreSQL instance whose max_connections is set to 100. Nobody did anything wrong. You just scaled past the point where direct connections work.

PostgreSQL uses a process-per-connection model. Every time a client connects, Postgres forks an entire operating system process — allocating 5MB to 10MB of RAM, kernel scheduling overhead, and a slot in the connection table. At 50 connections, this is fine. At 500, your database server is spending more time context-switching between processes than actually executing queries.

PgBouncer fixes this. It's a lightweight connection pooler that sits between your applications and PostgreSQL, multiplexing hundreds (or thousands) of application connections into a small, fixed pool of real database connections. Your apps think they have an open line to the database. In reality, PgBouncer is a traffic controller handing out a handful of PostgreSQL backends only when a query actually needs to run.

This guide covers everything: how PgBouncer works, how to set it up for production, and — most importantly — ready-to-use connection configurations for Django, FastAPI, Node.js, and Java Spring Boot, including the framework-specific gotchas that will break your app if you don't handle them. Fill in your database details below, and every code block in this article updates instantly.

What you'll learn:

• Why PostgreSQL's process-per-connection model collapses at scale, and exactly what PgBouncer does about it.
• The difference between session, transaction, and statement pooling — and why transaction mode is what you almost certainly want.
• Production-ready pgbouncer.ini configuration with inline explanations.
• Copy-paste connection configs for Django, FastAPI (asyncpg + SQLAlchemy), Node.js (node-postgres), and Java (Spring Boot + HikariCP).
• The 5 production traps that break every team once: prepared statements, session state bleed, migration crashes, advisory locks, and dirty connections.
• How to monitor PgBouncer with SHOW POOLS, SHOW STATS, and zero-downtime reloads.

---

Set Your Variables

Enter your database and PgBouncer details below. Every code block in this article updates instantly to reflect your values, making every config ready to copy and paste directly into your project.

---

1. The Problem: Why Direct PostgreSQL Connections Don't Scale

When your application connects directly to PostgreSQL, every connection — active or idle — consumes a dedicated backend process on the database server. Here's what that looks like in practice:

┌────────────────────────────────────────────────────────────────┐
│                Without PgBouncer (Direct Connections)          │
│                                                                │
│  Django (8 workers)     ──── 8 connections ────┐               │
│  FastAPI (4 workers)    ──── 4 connections ────┤               │
│  Node.js (6 forks)      ──── 6 connections ────┤──> PostgreSQL │
│  Java Spring (20 pool)  ──── 20 connections ───┘    (38 procs) │
│                                                                │
│  Scale to 3 replicas: 38 × 3 = 114 connections                 │
│  PostgreSQL max_connections default: 100                        │
│  Result: FATAL: sorry, too many clients already                 │
└────────────────────────────────────────────────────────────────┘

Each of those 114 processes allocates memory, competes for CPU scheduling, and holds a slot even while the application is busy processing the result set in Python, Node, or Java — doing zero database work. Studies show that in a typical web application, a connection is actively executing a query less than 5% of the time. The other 95% it is idle, sitting there, consuming resources for nothing.

Increasing max_connections to 500 or 1,000 is not the answer. PostgreSQL's performance degrades sharply beyond 200–300 connections due to lock contention, shared buffer pressure, and CPU context-switching overhead. The official PostgreSQL documentation itself warns against this.

---

2. How PgBouncer Solves It

PgBouncer is a single-threaded, event-loop-based proxy that multiplexes client connections onto a small, fixed pool of real PostgreSQL backends:

┌────────────────────────────────────────────────────────────────┐
│                  With PgBouncer (Pooled)                       │
│                                                                │
│  Django (8 workers × 3 pods)    ── 24 ─┐                      │
│  FastAPI (4 workers × 3 pods)   ── 12 ─┤                      │
│  Node.js (6 forks × 3 pods)    ── 18 ─┼──> PgBouncer (:6432) │
│  Java Spring (20 pool × 3 pods) ── 60 ─┘    (Multiplexer)     │
│                                                 │              │
│                                    $DEFAULT_POOL_SIZE real     │
│                                     connections               │
│                                                 │              │
│                                                 ▼              │
│                                          PostgreSQL (:$POSTGRES_PORT)│
│                                       ($DEFAULT_POOL_SIZE processes) │
└────────────────────────────────────────────────────────────────┘

114 application connections become $DEFAULT_POOL_SIZE real PostgreSQL connections. The database server is calm, memory usage is flat, and your applications don't notice the difference — they connect to PgBouncer on port $PGBOUNCER_PORT instead of PostgreSQL on port $POSTGRES_PORT, and everything else stays the same.

Why this matters: PgBouncer itself uses roughly 2KB of memory per connection. Compared to PostgreSQL's 5–10MB per connection, that's a 2,500× to 5,000× reduction in memory overhead. A single PgBouncer instance can comfortably handle 10,000 client connections on a modest server.

---

3. Pooling Modes: Session vs. Transaction vs. Statement

PgBouncer's pool_mode setting controls when a real PostgreSQL backend connection is handed to a client and when it's returned to the pool. This single setting determines everything about how your application interacts with PgBouncer.

Mode	When the backend is assigned	When it's returned	Session features work?	Use case
Session	When the client connects	When the client disconnects	✅ Yes (temp tables, SET, LISTEN/NOTIFY, prepared statements)	Legacy apps that depend on session state
Transaction	At BEGIN (or first query)	At COMMIT / ROLLBACK	❌ No — each transaction may get a different backend	95% of web apps: Django, FastAPI, Node.js, Spring Boot
Statement	At each individual statement	After each statement completes	❌ No, and multi-statement transactions are forbidden	Extremely simple, read-heavy workloads

Transaction mode is what you almost certainly want. It gives you the highest connection reuse ratio: a backend that finishes a 2ms SELECT is instantly available for the next client, instead of sitting idle while your Python code serializes the response to JSON.

The trade-off: Transaction mode means the backend connection your app gets for transaction #1 is probably not the same backend it gets for transaction #2. Any state set with SET search_path, SET timezone, temporary tables, or server-side prepared statements will either disappear or — worse — leak to another client. We cover the fixes for each of these below.

---

4. Installing and Configuring PgBouncer

Install PgBouncer

On Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer -y

Verify the installation:

pgbouncer --version

Configure PgBouncer

The main configuration file is /etc/pgbouncer/pgbouncer.ini. Here's a production-ready config with every important parameter explained:

;; /etc/pgbouncer/pgbouncer.ini

[databases]
;; Map the database name to the upstream PostgreSQL instance
;; Clients connect to PgBouncer asking for "$DB_NAME"
;; PgBouncer forwards to PostgreSQL at $POSTGRES_HOST:$POSTGRES_PORT
$DB_NAME = host=$POSTGRES_HOST port=$POSTGRES_PORT dbname=$DB_NAME

[pgbouncer]
;; ── Network ──
listen_addr = $PGBOUNCER_HOST
listen_port = $PGBOUNCER_PORT

;; ── Authentication ──
;; md5 is widely compatible; scram-sha-256 is stronger (PgBouncer 1.14+)
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; ── Pooling ──
pool_mode = $POOL_MODE

;; max_client_conn: how many total client connections PgBouncer accepts
;; This is the "front door" capacity
max_client_conn = $MAX_CLIENT_CONN

;; default_pool_size: how many REAL PostgreSQL connections PgBouncer opens
;; per user/database pair. This is the "kitchen staff" count.
default_pool_size = $DEFAULT_POOL_SIZE

;; min_pool_size: keep at least this many backends warm at all times
;; Prevents cold-start latency after idle periods
min_pool_size = 5

;; reserve_pool_size: emergency overflow connections when the pool is full
reserve_pool_size = 5

;; reserve_pool_timeout: seconds to wait before dipping into the reserve
reserve_pool_timeout = 5

;; ── Connection Hygiene ──
;; CRITICAL: Reset session state between clients in transaction mode
;; Without this, SET commands from one client leak to the next
server_reset_query = DISCARD ALL

;; server_check_query: lightweight health check before handing a backend to a client
server_check_query = SELECT 1

;; server_check_delay: seconds between health checks (0 = check every time)
server_check_delay = 30

;; ── Timeouts ──
;; How long a client can wait for a server connection before timing out
query_wait_timeout = 120

;; Kill idle client connections after this many seconds (0 = disabled)
client_idle_timeout = 0

;; Kill server connections idle longer than this (seconds)
server_idle_timeout = 600

;; ── Logging ──
;; Disable verbose connection logging in production
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

;; ── Admin ──
admin_users = $DB_USER

The most important line in the entire file is server_reset_query = DISCARD ALL. Without it, session-level settings (SET search_path, SET timezone, temporary tables) from one client's transaction will bleed into the next client that gets the same backend connection. DISCARD ALL resets everything between uses.

Set Up Authentication

PgBouncer needs to know how to authenticate incoming clients. The simplest approach is a flat file:

sudo nano /etc/pgbouncer/userlist.txt

"$DB_USER" "md5_hashed_password_here"

To generate the MD5 hash, run this in psql:

SELECT 'md5' || md5('$DB_PASSWORD' || '$DB_USER');

Copy the output (e.g., md5abc123def456...) and paste it as the password in userlist.txt.

Better: Use auth_query (No Flat File)

For production, maintaining a flat file across servers is fragile. Instead, let PgBouncer query PostgreSQL directly for credentials. Add this to pgbouncer.ini:

;; Replace auth_file with auth_query
auth_type = md5
auth_user = pgbouncer_auth_user
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Then create the lookup role in PostgreSQL:

CREATE ROLE pgbouncer_auth_user LOGIN PASSWORD 'auth_lookup_password';
GRANT SELECT ON pg_shadow TO pgbouncer_auth_user;

You still need a minimal userlist.txt containing only the auth_user itself:

"pgbouncer_auth_user" "md5_hash_of_auth_lookup_password"

PgBouncer uses this user to connect to PostgreSQL and look up every other user's credentials dynamically. No more syncing password files across servers.

Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

# Verify it's listening
sudo ss -tlnp | grep $PGBOUNCER_PORT

---

5. Connecting Your Application

This is where teams get burned. Every framework has specific PgBouncer gotchas — mainly around prepared statements and server-side cursors. Here are production-ready configs for each.

A. Django

Django uses the psycopg2 driver, which relies on server-side cursors for QuerySet.iterator() and can conflict with transaction pooling. Update your settings.py:

# settings.py — Application traffic via PgBouncer

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '$DB_NAME',
        'USER': '$DB_USER',
        'PASSWORD': '$DB_PASSWORD',
        'HOST': '$PGBOUNCER_HOST',       # PgBouncer host
        'PORT': '$PGBOUNCER_PORT',       # PgBouncer port, NOT 5432
        'CONN_MAX_AGE': 0,               # Close after each request in txn mode
        'DISABLE_SERVER_SIDE_CURSORS': True,  # CRITICAL for transaction pooling
    },
}

Why DISABLE_SERVER_SIDE_CURSORS = True? Server-side cursors are PostgreSQL session objects. In transaction pooling, the cursor created in one transaction may reference a backend that's already been handed to a different client by the next query. Django will throw cursor "..." does not exist. Setting this to True forces Django to use client-side cursors, which work perfectly with PgBouncer.

Why CONN_MAX_AGE = 0? In transaction mode, there's no benefit to Django holding a persistent connection to PgBouncer, since PgBouncer itself manages the persistent connections to PostgreSQL. Setting it to 0 ensures Django opens a fresh PgBouncer connection per request and closes it cleanly. If your workload is high-throughput (thousands of requests per second per worker), you can set CONN_MAX_AGE = 600 for TCP handshake savings — PgBouncer handles the multiplexing either way.

The Migration Escape Hatch

Django migrations issue DDL statements (CREATE TABLE, ALTER TABLE) and often use implicit transactions and advisory locks that require a stable session. Always run migrations against PostgreSQL directly, bypassing PgBouncer:

# settings.py — Direct connection for migrations only

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '$DB_NAME',
        'USER': '$DB_USER',
        'PASSWORD': '$DB_PASSWORD',
        'HOST': '$PGBOUNCER_HOST',
        'PORT': '$PGBOUNCER_PORT',
        'CONN_MAX_AGE': 0,
        'DISABLE_SERVER_SIDE_CURSORS': True,
    },
    'direct': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '$DB_NAME',
        'USER': '$DB_USER',
        'PASSWORD': '$DB_PASSWORD',
        'HOST': '$POSTGRES_HOST',        # Direct to PostgreSQL
        'PORT': '$POSTGRES_PORT',        # PostgreSQL port, NOT PgBouncer
    },
}

Run migrations against the direct connection:

python manage.py migrate --database=direct

Your application traffic goes through PgBouncer on port $PGBOUNCER_PORT. Migrations go directly to PostgreSQL on port $POSTGRES_PORT. Clean separation.

---

B. FastAPI (asyncpg + SQLAlchemy Async)

FastAPI's async architecture pairs naturally with PgBouncer, but two gotchas can ruin your day: asyncpg's aggressive prepared statement caching and double pooling (SQLAlchemy's pool on top of PgBouncer's pool).

# database.py — FastAPI + SQLAlchemy async + PgBouncer

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.pool import NullPool

DATABASE_URL = (
    "postgresql+asyncpg://$DB_USER:$DB_PASSWORD"
    "@$PGBOUNCER_HOST:$PGBOUNCER_PORT/$DB_NAME"
)

engine = create_async_engine(
    DATABASE_URL,
    # CRITICAL: Disable SQLAlchemy's internal pool.
    # PgBouncer is your pool. Two pools fighting each other
    # causes connection leaks, stale backends, and random timeouts.
    poolclass=NullPool,
    connect_args={
        # CRITICAL: Disable asyncpg's prepared statement cache.
        # asyncpg caches prepared statements per-connection. In transaction
        # mode, the next query may land on a different PostgreSQL backend
        # that has no knowledge of the cached statement.
        # Without this: ERROR: prepared statement "..." does not exist
        "statement_cache_size": 0,
        # Also disable the implicit "prepared_statement_cache"
        "prepared_statement_cache_size": 0,
    },
)

async_session = async_sessionmaker(engine, expire_on_commit=False)


async def get_db():
    async with async_session() as session:
        try:
            yield session
        finally:
            await session.close()

Usage in a FastAPI route:

# routes.py

from fastapi import FastAPI, Depends
from sqlalchemy import text
from database import get_db

app = FastAPI()

@app.get("/users/{user_id}")
async def get_user(user_id: int, db=Depends(get_db)):
    result = await db.execute(
        text("SELECT id, name, email FROM users WHERE id = :id"),
        {"id": user_id},
    )
    user = result.mappings().first()
    return user

Why NullPool? SQLAlchemy's default QueuePool maintains its own pool of connections. With PgBouncer in front of PostgreSQL, you'd have two layers of pooling: SQLAlchemy holds connections to PgBouncer, and PgBouncer holds connections to PostgreSQL. The two pools interfere with each other — SQLAlchemy might hold a PgBouncer connection "open" for reuse while PgBouncer's server_idle_timeout closes the underlying PostgreSQL backend. NullPool disables SQLAlchemy's pool entirely, letting PgBouncer handle all pooling.

---

C. Node.js (node-postgres)

The pg library for Node.js uses the extended query protocol by default, which creates implicit prepared statements. In transaction mode, this breaks because the prepared statement is bound to a specific PostgreSQL backend process.

// db.js — Node.js + node-postgres + PgBouncer

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

const pool = new Pool({
  host: '$PGBOUNCER_HOST',
  port: $PGBOUNCER_PORT,
  user: '$DB_USER',
  password: '$DB_PASSWORD',
  database: '$DB_NAME',

  // Keep the app-level pool modest — PgBouncer handles global pooling
  max: 10,

  // Timeout: how long a client waits for a connection from the local pool
  connectionTimeoutMillis: 5000,

  // Idle timeout: release connections back to PgBouncer after 30s of inactivity
  idleTimeoutMillis: 30000,

  // CRITICAL: Disable prepared statement caching for PgBouncer compatibility
  // Without this, you'll get:
  //   ERROR: prepared statement "..." already exists
  //   ERROR: prepared statement "..." does not exist
  statement_timeout: 30000,
});

// Force simple query protocol (no prepared statements)
const query = async (text, params) => {
  const client = await pool.connect();
  try {
    // Using parameterized queries with the simple protocol
    const result = await client.query({ text, values: params, rowMode: 'array' });
    return result;
  } finally {
    client.release();
  }
};

module.exports = { pool, query };

Usage in an Express route:

// routes/users.js

const { pool } = require('../db');

app.get('/users/:id', async (req, res) => {
  const { rows } = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [req.params.id]
  );
  res.json(rows[0]);
});

Prisma with PgBouncer

If you use Prisma as your ORM, it has first-class PgBouncer support. You need two URLs: one for PgBouncer (application queries) and one direct to PostgreSQL (migrations):

# .env — Prisma PgBouncer config

# Application queries go through PgBouncer
DATABASE_URL="postgresql://$DB_USER:$DB_PASSWORD@$PGBOUNCER_HOST:$PGBOUNCER_PORT/$DB_NAME?pgbouncer=true"

# Migrations go directly to PostgreSQL (bypassing PgBouncer)
DIRECT_URL="postgresql://$DB_USER:$DB_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$DB_NAME"

// schema.prisma

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

The ?pgbouncer=true flag tells Prisma to disable prepared statements and use the simple query protocol automatically.

---

D. Java / Spring Boot (HikariCP)

Spring Boot uses HikariCP as its default connection pool. Like SQLAlchemy's pool in Python, you now have two pools: HikariCP inside your Java app, and PgBouncer in front of PostgreSQL. The key is to keep HikariCP's pool small and disable JDBC prepared statement caching.

# application.properties — Spring Boot + HikariCP + PgBouncer

# Point to PgBouncer, NOT directly to PostgreSQL
spring.datasource.url=jdbc:postgresql://$PGBOUNCER_HOST:$PGBOUNCER_PORT/$DB_NAME?prepareThreshold=0
spring.datasource.username=$DB_USER
spring.datasource.password=$DB_PASSWORD

# HikariCP pool settings — keep small, PgBouncer handles global pooling
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.connection-timeout=5000

Why ?prepareThreshold=0? The PostgreSQL JDBC driver (pgjdbc) automatically promotes queries to server-side prepared statements after 5 executions by default (prepareThreshold=5). In PgBouncer's transaction mode, the prepared statement created on backend A is invisible to backend B. Setting prepareThreshold=0 forces the driver to always use the simple query protocol — no server-side caching, no cross-backend confusion.

For YAML-based configs:

# application.yml

spring:
  datasource:
    url: jdbc:postgresql://$PGBOUNCER_HOST:$PGBOUNCER_PORT/$DB_NAME?prepareThreshold=0
    username: $DB_USER
    password: $DB_PASSWORD
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      idle-timeout: 30000
      max-lifetime: 600000
      connection-timeout: 5000

Running Flyway/Liquibase Migrations

Just like Django and Prisma, schema migration tools should bypass PgBouncer. Configure a separate datasource for Flyway:

# Flyway connects directly to PostgreSQL
spring.flyway.url=jdbc:postgresql://$POSTGRES_HOST:$POSTGRES_PORT/$DB_NAME
spring.flyway.user=$DB_USER
spring.flyway.password=$DB_PASSWORD

---

6. The 5 Production Traps

These are the issues that bite every team exactly once. Now they won't bite you.

Trap 1: Prepared Statements

The error: prepared statement "..." already exists or prepared statement "..." does not exist.

Why it happens: Prepared statements in PostgreSQL are bound to a specific backend session. In transaction mode, Client A prepares a statement on Backend 1. The transaction ends. PgBouncer hands Backend 1 to Client B. Client B tries to prepare the same statement name — it already exists. Or Client A's next transaction lands on Backend 2, where the statement was never created.

The fix (per framework):

• Django: Not affected (psycopg2 doesn't use named prepared statements by default)
• FastAPI (asyncpg): statement_cache_size=0 in connect_args
• Node.js (pg): Use the simple query protocol or avoid named statements
• Java (JDBC): ?prepareThreshold=0 in the connection URL
• PgBouncer 1.21+: Set max_prepared_statements = 1000 in pgbouncer.ini to let PgBouncer manage protocol-level prepared statements transparently

Trap 2: Session State Bleed

The error: Queries return data from unexpected schemas, timestamps are in the wrong timezone, or one user's SET commands affect another user's queries.

Why it happens: A client runs SET search_path TO my_schema; or SET timezone TO 'Asia/Kathmandu';. The transaction ends, and PgBouncer hands that backend (with its modified session state) to a completely different client.

The fix: The server_reset_query = DISCARD ALL setting in pgbouncer.ini resets all session state after every transaction. This is why it's the most important line in the config. If you need per-user defaults, set them at the role level in PostgreSQL instead:

ALTER ROLE $DB_USER SET search_path TO public, my_schema;
ALTER ROLE $DB_USER SET timezone TO 'UTC';

Role-level settings are applied automatically when the backend authenticates, regardless of which client gets which backend.

Trap 3: Migration Crashes

The error: Migrations hang indefinitely, fail with lock timeout errors, or corrupt schema state.

Why it happens: Schema migrations issue DDL statements (CREATE TABLE, ALTER TABLE) that acquire AccessExclusiveLock. They also use advisory locks to prevent concurrent migration runs. In transaction mode, PgBouncer may route different statements of the same migration to different backends, causing the advisory lock to be acquired on Backend 1 while the DDL runs on Backend 2.

The fix: Always run migrations against PostgreSQL directly, bypassing PgBouncer:

• Django: python manage.py migrate --database=direct
• Prisma: Uses directUrl automatically
• Flyway / Liquibase: Configure a separate spring.flyway.url pointing to port $POSTGRES_PORT
• Node.js (Knex / TypeORM): Set a MIGRATION_DATABASE_URL env var pointing to port $POSTGRES_PORT

Trap 4: LISTEN/NOTIFY and Advisory Locks

The error: LISTEN events are never received. pg_advisory_lock() doesn't actually prevent concurrent access.

Why it happens: Both features are session-scoped. LISTEN registers a notification channel on a specific backend process. If PgBouncer routes the next transaction to a different backend, the NOTIFY lands on Backend 1 while your application is now talking to Backend 2.

The fix: If your application relies on LISTEN/NOTIFY (e.g., for real-time updates) or advisory locks (e.g., for distributed locking), configure those specific connections to use session pooling. You can set per-database pool modes in pgbouncer.ini:

[databases]
;; Application traffic: transaction pooling
$DB_NAME = host=$POSTGRES_HOST port=$POSTGRES_PORT dbname=$DB_NAME pool_mode=$POOL_MODE

;; Real-time listener: session pooling
${DB_NAME}_realtime = host=$POSTGRES_HOST port=$POSTGRES_PORT dbname=$DB_NAME pool_mode=session

Your application connects to $DB_NAME for normal queries and ${DB_NAME}_realtime for LISTEN/NOTIFY channels.

Trap 5: Dirty Connections (Missing server_reset_query)

The error: Random, impossible-to-reproduce bugs. One user sees another user's temporary table data. A SET LOCAL leaks to the next transaction.

Why it happens: Without server_reset_query = DISCARD ALL, PgBouncer returns backends to the pool with whatever session state the previous client left behind. The next client inherits that state silently.

The fix: Never remove or comment out server_reset_query = DISCARD ALL. It adds a tiny overhead (one extra round-trip per transaction) but eliminates an entire class of insidious, cross-client contamination bugs.

---

7. Monitoring and Administration

PgBouncer exposes a virtual database called pgbouncer that you can query with psql to inspect the pool's state in real time.

Connect to the Admin Console

psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $DB_USER pgbouncer

Essential Commands

SHOW POOLS — Pool utilization

SHOW POOLS;

 database  |   user   | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
-----------+----------+-----------+------------+-----------+---------+------------
 $DB_NAME  | $DB_USER |        12 |          0 |         3 |      22 | transaction

• cl_active: Clients currently linked to a server connection and executing queries
• cl_waiting: Clients waiting for a server connection (if this is consistently > 0, increase default_pool_size)
• sv_active: Server connections currently in use
• sv_idle: Server connections sitting in the pool, ready for the next client

SHOW STATS — Throughput metrics

SHOW STATS;

Shows total queries, bytes sent/received, and average query duration per database. Use this to spot anomalies.

SHOW CLIENTS — Who's connected

SHOW CLIENTS;

Lists every client connection, its state, the database it's connected to, and how long it's been connected.

SHOW SERVERS — Backend connections

SHOW SERVERS;

Lists every real PostgreSQL backend connection, its state, and which client (if any) it's currently serving.

Zero-Downtime Operations

-- Reload config without dropping connections
RELOAD;

-- Pause all traffic (for PostgreSQL maintenance)
PAUSE $DB_NAME;

-- Resume after maintenance
RESUME $DB_NAME;

PAUSE waits for all active transactions to complete, then holds new queries in a queue until you call RESUME. This lets you restart PostgreSQL, run a vacuum, or apply patches without any client seeing an error.

---

8. Sizing: How to Set default_pool_size

The right pool size depends on your PostgreSQL server's resources and your query profile. Here's a practical starting formula:

default_pool_size = (2 × CPU cores) + effective_spindle_count

For a typical cloud database instance with 4 vCPUs and SSD storage:

default_pool_size = (2 × 4) + 1 = 9 → round up to 10

For a beefier 16-core dedicated server:

default_pool_size = (2 × 16) + 1 = 33 → use 30 or 35

The counterintuitive truth: A smaller pool often gives you better throughput than a larger one. With fewer concurrent backends, PostgreSQL spends less time on lock contention and context switching, and more time actually executing queries. Start small, measure cl_waiting in SHOW POOLS, and increase only if clients are consistently waiting.

Set max_client_conn high (1,000–10,000) so your applications can always connect to PgBouncer, even if they have to wait briefly for a backend. PgBouncer queues them efficiently in memory.

---

9. Production Checklist

Before going live with PgBouncer, run through this:

• pool_mode = transaction set (unless you have a documented reason for session mode)
• server_reset_query = DISCARD ALL set — non-negotiable
• default_pool_size tuned to (2 × CPU cores) + 1 as a starting point
• max_client_conn set high enough to handle all application instances
• min_pool_size set to 3–5 to avoid cold-start latency
• Authentication configured via auth_query (not a flat file synced across servers)
• Django: DISABLE_SERVER_SIDE_CURSORS = True and CONN_MAX_AGE = 0
• FastAPI (asyncpg): statement_cache_size = 0 and poolclass = NullPool
• Node.js: Prepared statement caching disabled, simple query protocol preferred
• Java (JDBC): ?prepareThreshold=0 in the connection URL
• All frameworks: Migrations configured to run directly against PostgreSQL on port $POSTGRES_PORT, not through PgBouncer on port $PGBOUNCER_PORT
• SHOW POOLS confirms cl_waiting = 0 under normal load
• PgBouncer enabled as a systemd service (systemctl enable pgbouncer)
• Monitoring dashboard tracks cl_active, cl_waiting, sv_active, and sv_idle

---

That's PgBouncer from zero to production. One lightweight proxy, a few lines of framework-specific config, and your PostgreSQL instance goes from drowning in 500 forked processes to calmly serving a handful of real connections while thousands of application workers share the pool.

The pattern is always the same, regardless of language: point your app at port $PGBOUNCER_PORT instead of $POSTGRES_PORT, disable whatever prepared statement or session feature your driver uses by default, and run migrations directly against PostgreSQL. The database does less work. The application doesn't notice the difference. And your 2 AM self will thank you.