---
# === IDENTITY ===
id: software/debugging/postgresql-connection-pool/2026
canonical_question: "How do I fix PostgreSQL connection pool exhaustion?"
aliases:
  - "PostgreSQL too many clients"
  - "PostgreSQL connection pool exhaustion"
  - "PgBouncer setup"
  - "PostgreSQL max_connections"
  - "PostgreSQL FATAL too many connections"
  - "PostgreSQL connection leak"
  - "PgBouncer transaction pooling"
  - "PostgreSQL idle connections"
  - "pg_stat_activity connections"
  - "PostgreSQL connection limit"
entity_type: software_reference
domain: software > debugging > postgresql_connection_pool
region: global
jurisdiction: global
temporal_scope: 2010-2026

# === VERIFICATION ===
last_verified: 2026-02-23
confidence: 0.94
version: 2.0
first_published: 2026-02-19

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 17 transaction_timeout (2024-09)"
  next_review: 2026-08-22
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "Never raise max_connections above 300-500 without a connection pooler -- performance degrades super-linearly due to OS scheduler and lock contention"
  - "PgBouncer transaction pooling is incompatible with server-side prepared statements (PREPARE/EXECUTE), advisory locks, LISTEN/NOTIFY, SET statements, and temporary tables"
  - "idle_session_timeout requires PostgreSQL 14+ -- use PgBouncer client_idle_timeout or application-level timeouts on older versions"
  - "transaction_timeout requires PostgreSQL 17+ -- do not set on older versions or the parameter will be rejected"
  - "PgBouncer SCRAM-SHA-256 auth requires PgBouncer 1.14+ -- use md5 auth_type on older PgBouncer versions"
  - "Changing max_connections requires a full PostgreSQL server restart -- it cannot be changed with pg_reload_conf()"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Slow queries rather than too many connections (query execution time, not connection count)"
    use_instead: "software/debugging/postgresql-slow-queries/2026"
  - condition: "MySQL connection issues rather than PostgreSQL"
    use_instead: "software/debugging/mysql-connection-issues/2026"
  - condition: "Redis connection or memory issues"
    use_instead: "software/debugging/redis-memory-issues/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: postgresql_version
    question: "What PostgreSQL version are you running?"
    type: choice
    options: ["PostgreSQL 12-13", "PostgreSQL 14-16", "PostgreSQL 17+"]
  - key: has_pooler
    question: "Do you already have a connection pooler (PgBouncer, Pgpool-II, PgCat) deployed?"
    type: choice
    options: ["No pooler", "PgBouncer", "Pgpool-II", "PgCat/Odyssey", "Not sure"]
  - key: deployment_type
    question: "What is your deployment environment?"
    type: choice
    options: ["Self-hosted / bare metal", "Managed (RDS, Cloud SQL, Supabase)", "Kubernetes / containers"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/debugging/postgresql-connection-pool/2026"
suggested_citation: "Source: knowledgelib.io -- AI Knowledge Library (verified 2026-02-23)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Query Optimization"
    - id: "software/debugging/mysql-deadlocks/2026"
      label: "MySQL Deadlocks"
  solves:
    - id: "software/debugging/redis-memory-issues/2026"
      label: "Redis Memory Issues"
  often_confused_with:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Query Optimization (slow queries vs connection exhaustion)"

# === SOURCES (5-8 authoritative sources) ===
sources:
  - id: src1
    title: "PostgreSQL Documentation -- Server Configuration: max_connections"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/runtime-config-connection.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src2
    title: "PgBouncer Documentation & Changelog"
    author: PgBouncer Project
    url: https://www.pgbouncer.org/config.html
    type: official_docs
    published: 2025-11-09
    reliability: authoritative
  - id: src3
    title: "PostgreSQL Documentation -- pg_stat_activity"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/monitoring-stats.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src4
    title: "PgBouncer vs Pgcat vs Odyssey on VPS in 2025: Performance Benchmarks"
    author: Onidel Cloud
    url: https://onidel.com/blog/postgresql-proxy-comparison-2025
    type: technical_blog
    published: 2025-06-01
    reliability: high
  - id: src5
    title: "PostgreSQL 17: transaction_timeout"
    author: dbi services
    url: https://www.dbi-services.com/blog/postgresql-17-transaction_timeout/
    type: technical_blog
    published: 2024-10-01
    reliability: high
  - id: src6
    title: "PgBouncer vs. Pgpool-II: Choosing the Right Tool for PostgreSQL"
    author: Horkan
    url: https://horkan.com/2025/01/01/pgbouncer-vs-pgpool-ii-choosing-the-right-tool-for-your-postgresql-environment
    type: technical_blog
    published: 2025-01-01
    reliability: high
  - id: src7
    title: "Benchmarking PostgreSQL Connection Poolers: PgBouncer, PgCat and Supavisor"
    author: Tembo
    url: https://legacy.tembo.io/blog/postgres-connection-poolers/
    type: technical_blog
    published: 2024-08-01
    reliability: high
  - id: src8
    title: "Postgres Connection Pooling: Comparing PgCat and PgBouncer"
    author: pganalyze
    url: https://pganalyze.com/blog/5mins-postgres-pgcat-vs-pgbouncer
    type: technical_blog
    published: 2024-06-01
    reliability: high
---

# How Do I Fix PostgreSQL Connection Pool Exhaustion?

## TL;DR

- **Bottom line**: PostgreSQL uses a **process-per-connection** model -- each connection consumes ~5-10MB RAM and a dedicated OS process. When connections exceed `max_connections` (default: 100), new connection attempts fail with `FATAL: sorry, too many clients already`. The fix: deploy **PgBouncer** in transaction pooling mode, which multiplexes thousands of app connections through a small, fixed pool of actual server connections.
- **Key tool/command**: `SELECT state, count(*) FROM pg_stat_activity GROUP BY state;` -- shows active, idle, and `idle in transaction` connections. High idle counts = connection leak; high `idle in transaction` = long transactions.
- **Watch out for**: Simply raising `max_connections` works short-term but **costs memory** (each connection needs shared memory) and **degrades performance** beyond ~300-500 connections due to OS scheduler and lock contention. Use PgBouncer instead.
- **Works with**: PostgreSQL 9.6+. PgBouncer 1.25+ recommended (latest). PgBouncer 1.14+ minimum for SCRAM auth. PostgreSQL 17+ adds `transaction_timeout`.

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- **Never raise `max_connections` above 300-500 without a connection pooler** -- performance degrades super-linearly due to OS context switching (1 process per connection) and PostgreSQL internal lock contention. [src1]
- **PgBouncer transaction pooling breaks certain PostgreSQL features**: server-side prepared statements (`PREPARE`/`EXECUTE`), advisory locks (`pg_advisory_lock`), `LISTEN`/`NOTIFY`, persistent `SET` statements, and temporary tables are all incompatible with `pool_mode = transaction`. Disable these in app config or use session pooling selectively. [src2]
- **`idle_session_timeout` requires PostgreSQL 14+** -- on older versions, use PgBouncer's `client_idle_timeout` (added in PgBouncer 1.24) or implement application-level connection release. [src1]
- **`transaction_timeout` requires PostgreSQL 17+** -- this parameter was added in PG17 (2024-09). Do not set it on older versions or the server will reject the configuration. [src5]
- **PgBouncer SCRAM-SHA-256 auth requires PgBouncer 1.14+** -- PostgreSQL 14+ defaults to SCRAM authentication. Use `auth_type = md5` on older PgBouncer versions. [src2]
- **Changing `max_connections` requires a full server restart** -- unlike most GUC parameters, `max_connections` cannot be changed with `pg_reload_conf()`. Plan maintenance windows. [src1]

## Quick Reference

| # | Symptom / Goal | Command / Solution | Notes |
|---|---|---|---|
| 1 | Check current connection count | `SELECT count(*) FROM pg_stat_activity;` | Compare to `max_connections` [src3] |
| 2 | Check connection limit | `SHOW max_connections;` | Default: 100 [src1] |
| 3 | See connections by state | `SELECT state, count(*) FROM pg_stat_activity GROUP BY state;` | active, idle, idle in transaction [src3] |
| 4 | Find which apps are consuming connections | `SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC;` | Pinpoints the culprit [src3] |
| 5 | Kill idle connections immediately | `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < NOW() - INTERVAL '10 minutes';` | Emergency fix only [src3] |
| 6 | Kill `idle in transaction` connections | `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < NOW() - INTERVAL '5 minutes';` | Frees locked resources [src3] |
| 7 | Check superuser reserved connections | `SHOW superuser_reserved_connections;` | Default: 3 (reserved from max_connections) [src1] |
| 8 | Check PgBouncer pool status | `SHOW POOLS;` in PgBouncer admin | `cl_waiting > 0` = exhaustion [src2] |
| 9 | Check PgBouncer overall stats | `SHOW STATS;` in PgBouncer admin | req/s, latency, errors [src2] |
| 10 | Set idle-in-transaction timeout | `idle_in_transaction_session_timeout = 30000` in postgresql.conf | Kills stuck transactions after 30s [src1] |
| 11 | Set statement timeout | `statement_timeout = 30000` in postgresql.conf | Kills queries running > 30s [src1] |
| 12 | Set transaction timeout (PG17+) | `transaction_timeout = 60000` in postgresql.conf | Limits total transaction duration to 60s [src5] |
| 13 | Reload config without restart | `SELECT pg_reload_conf();` | For GUC parameters that don't need restart [src1] |
| 14 | Increase max_connections (temporary) | Edit `max_connections` in `postgresql.conf`, restart | Requires server restart; see caveats [src1] |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (33 lines)

```
START -- "FATAL: sorry, too many clients already" or connection timeout
|
+-- Step 1: Diagnose -- assess current connection state
|   +-- SHOW max_connections;                         # check limit
|   +-- SELECT count(*) FROM pg_stat_activity;        # check usage
|   +-- SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
|       +-- HIGH "active"              -> real load -> need PgBouncer [src2]
|       +-- HIGH "idle"                -> connection leak -> fix app pool [src4]
|       +-- HIGH "idle in transaction" -> transaction leak -> set idle_in_transaction_session_timeout [src1]
|
+-- Step 2: Emergency mitigation
|   +-- Kill idle connections -> pg_terminate_backend() [src3]
|   +-- Set idle_in_transaction_session_timeout -> prevents future stalls [src1]
|   +-- Temporarily increase max_connections -> restart required, temporary [src1]
|
+-- Step 3: Permanent fix
|   +-- No connection pooler?
|   |   +-- INSTALL PgBouncer in transaction pooling mode [src2, src6]
|   |       -> 10,000 app connections -> 20-100 PostgreSQL connections
|   +-- PgBouncer installed but pool_size too small?
|   |   +-- Increase default_pool_size in pgbouncer.ini [src2]
|   +-- Application connection leak?
|   |   +-- Add missing connection.close() / context managers
|   |   +-- Set server_idle_timeout in PgBouncer [src2]
|   +-- Long-running transactions?
|   |   +-- idle_in_transaction_session_timeout + statement_timeout [src1]
|   |   +-- transaction_timeout (PostgreSQL 17+) [src5]
|   +-- High-scale (>50 concurrent per core)?
|       +-- Consider PgCat (multi-threaded, 59K tps) [src4, src7, src8]
|       +-- Consider Odyssey (enterprise, multi-threaded) [src4]
|
+-- Step 4: Monitor
    +-- Alert when pg_stat_activity count > 80% of max_connections
    +-- SHOW POOLS; -- cl_waiting > 0 = problem
    +-- Grafana + postgres_exporter for dashboards
```

## Step-by-Step Guide

### 1. Diagnose the current state

Always start with `pg_stat_activity` before making changes. [src3]

```sql
-- 1. How many connections are open vs the limit?
SHOW max_connections;
SELECT count(*) AS current_connections FROM pg_stat_activity;

-- 2. What are those connections doing?
SELECT state, count(*) AS cnt
FROM pg_stat_activity
GROUP BY state
ORDER BY cnt DESC;
-- Healthy: most in "active" or small "idle" pool
-- Problematic: many "idle" (leak) or "idle in transaction" (transaction leak)

-- 3. Who is consuming connections?
SELECT
  usename,
  application_name,
  client_addr,
  state,
  count(*) AS connections
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()  -- exclude this query
GROUP BY 1, 2, 3, 4
ORDER BY connections DESC
LIMIT 20;

-- 4. Find long-running or stuck connections
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - backend_start AS connection_age,
  now() - query_start   AS query_age,
  left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > INTERVAL '5 minutes'
ORDER BY query_age DESC;
```

### 2. Emergency: terminating problem connections

For immediate relief when hitting the connection limit. [src3]

```sql
-- Kill idle connections older than 10 minutes
SELECT pg_terminate_backend(pid), usename, application_name, state
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < NOW() - INTERVAL '10 minutes'
  AND pid <> pg_backend_pid();

-- Kill idle-in-transaction connections older than 5 minutes
SELECT pg_terminate_backend(pid), usename, application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND pid <> pg_backend_pid();

-- Check how much headroom remains after superuser reservation
SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('superuser_reserved_connections')::int AS reserved,
  current_setting('max_connections')::int
    - current_setting('superuser_reserved_connections')::int AS available_to_users,
  (SELECT count(*) FROM pg_stat_activity) AS currently_used;
```

### 3. Set timeouts to prevent future exhaustion

Add to `postgresql.conf` and reload. [src1, src5]

```ini
# postgresql.conf
# Kill transactions idle in a transaction after 30 seconds
idle_in_transaction_session_timeout = 30000   # ms

# Kill statements running longer than 30 seconds (override per-session as needed)
statement_timeout = 30000   # ms

# Kill connections that are completely idle for 10 minutes
# (PostgreSQL 14+)
idle_session_timeout = 600000   # ms = 10 minutes

# Limit total transaction duration to 60 seconds
# (PostgreSQL 17+ ONLY -- do not set on older versions)
transaction_timeout = 60000   # ms

# Reload without restart:
# SELECT pg_reload_conf();
```

### 4. Install PgBouncer (the permanent fix)

PgBouncer sits between your application and PostgreSQL, multiplexing many app connections through a small pool. [src2, src6]

```ini
# /etc/pgbouncer/pgbouncer.ini

[databases]
# Format: db_alias = host=... port=... dbname=...
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
# Listen for app connections
listen_port = 6432
listen_addr = *

# Authentication
auth_type = scram-sha-256     # PostgreSQL 14+; use md5 for older
auth_file = /etc/pgbouncer/userlist.txt

# Pooling mode (CRITICAL SETTING)
# session    = one server connection per client session (weakest, like no pooling)
# transaction = server connection reused per transaction (RECOMMENDED for web apps)
# statement  = server connection reused per statement (not compatible with transactions)
pool_mode = transaction

# Maximum server connections PER DATABASE PER USER
# Formula: DB_max_connections ~ (num_cpu_cores * 2) to (max_connections / num_apps)
default_pool_size = 25

# Maximum app clients that can wait for a server connection
max_client_conn = 10000

# Maximum server connections per pool beyond default_pool_size (peak handling)
reserve_pool_size = 5
reserve_pool_timeout = 3      # seconds before reserved pool is used

# Timeout for idle server connections (returns to pool)
server_idle_timeout = 600     # seconds

# Timeout for idle client connections (PgBouncer 1.24+)
client_idle_timeout = 0       # 0 = no timeout (let app control)

# Connection limits per user / per database (PgBouncer 1.24+)
# max_user_client_connections = 500
# max_db_client_connections = 2000

# Transaction timeout (PgBouncer 1.25+)
# transaction_timeout = 60    # seconds

# Notify queued clients after 5s wait (PgBouncer 1.25+)
# query_wait_notify = 5

# Prepared statements (enabled by default in 1.24+)
max_prepared_statements = 200

# Connection limits
min_pool_size = 0             # pre-create N server connections on startup

# Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

# Admin interface
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

[users]
```

```bash
# /etc/pgbouncer/userlist.txt (scram-sha-256 hash, not plain password)
# Get hash: psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='myuser'"
"myuser" "SCRAM-SHA-256$4096:..."
```

```bash
# Start PgBouncer
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

# Monitor pools
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"
# Fields to watch:
# cl_active  = clients currently executing a query
# cl_waiting = clients waiting for a server connection (> 0 = exhaustion)
# sv_active  = server connections in use
# sv_idle    = server connections available in pool

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW STATS;"
```

### 5. Configure your application pool correctly

Even with PgBouncer, your application's own connection pool must be tuned. [src4, src7]

```python
# SQLAlchemy (Python) -- correct pool configuration for PgBouncer
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:pass@pgbouncer_host:6432/mydb",
    pool_size=5,           # persistent connections per process
    max_overflow=10,       # extra connections allowed during peaks
    pool_timeout=30,       # seconds to wait for a connection from app pool
    pool_recycle=1800,     # recycle connections after 30 min (prevents stale)
    pool_pre_ping=True,    # test connection health before use

    # CRITICAL for PgBouncer transaction pooling:
    # Disable prepared statements -- PgBouncer transaction mode can't route them
    connect_args={
        "options": "-c statement_timeout=30000"
    },
    execution_options={
        "no_parameters": True  # avoid server-side prepared statements
    }
)

# Django (settings.py)
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'pgbouncer_host',
        'PORT': '6432',
        'CONN_MAX_AGE': 60,  # reuse connections for 60s (Django persistent connections)
        'OPTIONS': {
            'options': '-c statement_timeout=30000'
        },
    }
}
```

```javascript
// Node.js pg (node-postgres) -- correct pool for PgBouncer
const { Pool } = require('pg');

const pool = new Pool({
  host: 'pgbouncer_host',
  port: 6432,
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  max: 10,               // max app pool connections per process
  idleTimeoutMillis: 30000,   // release idle connections after 30s
  connectionTimeoutMillis: 5000,  // fail fast if can't get connection
  allowExitOnIdle: false,
});

// Always use pool.query() or release() -- never leave connections open
async function getUser(id) {
  const client = await pool.connect();
  try {
    const res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return res.rows[0];
  } finally {
    client.release();  // CRITICAL -- always release
  }
}
```

## Code Examples

### SQL: real-time connection health dashboard

> Full script: [sql-real-time-connection-health-dashboard.sql](scripts/sql-real-time-connection-health-dashboard.sql) (36 lines)

```sql
-- Input:  Run this query on the PostgreSQL primary
-- Output: Connection health summary with recommendations
WITH connection_summary AS (
  SELECT
    state,
    count(*) AS cnt,
# ... (see full script)
```

### Python: connection pool monitor with alerting

> Full script: [python-connection-pool-monitor-with-alerting.py](scripts/python-connection-pool-monitor-with-alerting.py) (95 lines)

```python
#!/usr/bin/env python3
"""
Input:  PostgreSQL DSN + alert thresholds
Output: Connection health report + alerts
Requirements: pip install psycopg2-binary
# ... (see full script)
```

### Bash: PgBouncer health check and pool report

> Full script: [bash-pgbouncer-health-check-and-pool-report.sh](scripts/bash-pgbouncer-health-check-and-pool-report.sh) (42 lines)

```bash
#!/bin/bash
# Input:  PgBouncer admin connection params
# Output: Pool health report with warnings
PGB_HOST="${PGB_HOST:-127.0.0.1}"
PGB_PORT="${PGB_PORT:-6432}"
# ... (see full script)
```

## Anti-Patterns

### Wrong: Simply increasing max_connections as a fix

```ini
# BAD -- higher max_connections is not a substitute for a connection pooler [src1, src4]
max_connections = 1000   # -> ~5-10 GB RAM consumed by connections alone
                         # -> context switching overhead for 1000 OS processes
                         # -> lock contention grows super-linearly beyond ~300 connections
                         # -> still fails when you hit 1000 under microservice load
```

### Correct: Deploy PgBouncer with a modest max_connections

```ini
# GOOD -- PgBouncer handles thousands of app connections; PostgreSQL handles few [src2, src6]
# postgresql.conf
max_connections = 100    # PgBouncer manages app connections; Postgres only sees pooled ones

# pgbouncer.ini
max_client_conn = 10000  # accept 10,000 app connections
default_pool_size = 25   # but only 25 actual PostgreSQL connections per pool
pool_mode = transaction  # return connection to pool after each transaction
```

### Wrong: Connection leak -- not using context managers

```python
# BAD -- connection never released if exception occurs [src4]
def get_users():
    conn = pool.getconn()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users")  # if exception here...
    rows = cur.fetchall()
    pool.putconn(conn)                  # ...this line is never reached
    return rows
```

### Correct: Always use context managers or try/finally

```python
# GOOD -- connection is always released [src4]
def get_users():
    conn = pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM users")
            return cur.fetchall()
    finally:
        pool.putconn(conn)    # always returned to pool, even on exception

# Or with SQLAlchemy (recommended):
from sqlalchemy.orm import Session

def get_users(engine):
    with Session(engine) as session:    # auto-closes and returns to pool
        return session.execute(text("SELECT * FROM users")).fetchall()
```

### Wrong: Using PgBouncer in session mode for web apps

```ini
# BAD -- session pooling barely improves over no pooling [src2, src6]
pool_mode = session
# Each app "connection" holds a server connection for its entire lifetime
# For web apps with 500 simultaneous users -> 500 server connections
# Same problem as before
```

### Correct: Use transaction pooling for web applications

```ini
# GOOD -- transaction pooling multiplexes maximally [src2, src6]
pool_mode = transaction
# Each app connection holds a server connection ONLY during a transaction
# For web apps with 500 users doing 50ms transactions:
# -> ~25 server connections serve all 500 users (500 x 50ms / 1000ms = 25)

# Not compatible with transaction pooling (must disable in app):
# - SET statements that persist across transactions
# - Server-side prepared statements (PREPARE/EXECUTE)
# - Advisory locks (pg_advisory_lock)
# - LISTEN/NOTIFY
# - Temporary tables (use unlogged tables instead)
```

### Wrong: Not setting any timeouts

```ini
# BAD -- no timeout safety net [src1, src5]
# postgresql.conf with NO timeout settings:
# idle_in_transaction_session_timeout = 0   # disabled
# statement_timeout = 0                      # disabled
# idle_session_timeout = 0                   # disabled
# transaction_timeout = 0                    # disabled (PG17+)
# One stuck transaction can hold locks and connections indefinitely
```

### Correct: Defense-in-depth timeout strategy

```ini
# GOOD -- layered timeouts catch different failure modes [src1, src5]
# postgresql.conf
idle_in_transaction_session_timeout = 30000   # kills stuck BEGIN with no COMMIT
statement_timeout = 30000                      # kills runaway queries
idle_session_timeout = 600000                  # kills abandoned connections (PG14+)
transaction_timeout = 60000                    # caps total transaction time (PG17+)
```

## Common Pitfalls

- **`max_connections` includes superuser reserved slots**: The effective limit for regular users is `max_connections - superuser_reserved_connections` (default: 100 - 3 = 97). Reserve more for emergencies with `superuser_reserved_connections = 5`. [src1]
- **Increasing `max_connections` requires a restart**: Unlike most PostgreSQL config changes, `max_connections` requires a full server restart (not just `pg_reload_conf()`). Plan maintenance windows accordingly. [src1]
- **Prepared statements break PgBouncer transaction pooling**: Server-side prepared statements (`PREPARE`/`EXECUTE`) are connection-scoped. In transaction mode, PgBouncer may route the `EXECUTE` to a different server connection than the `PREPARE`. PgBouncer 1.24+ has `max_prepared_statements = 200` enabled by default, which helps but doesn't fully solve cross-transaction usage. Disable in app config (SQLAlchemy `use_native_hstore=False`, Django `DISABLE_SERVER_SIDE_CURSORS=True`). [src2]
- **PgBouncer's connection pool sizing formula**: A common rule of thumb is `pool_size = (num_cpu_cores x 2)` per database. For a 4-core DB server: `default_pool_size = 8`. Going higher often hurts due to PostgreSQL's internal lock contention. [src2, src6]
- **`idle in transaction` holds locks**: An `idle in transaction` connection is holding all the row locks from its open transaction. Even one stuck transaction can block other queries. Set `idle_in_transaction_session_timeout` to automatically kill these. [src1, src3]
- **Connection pools in multi-process apps**: Tools like Gunicorn, uWSGI, or Celery spawn multiple worker processes. Each worker has its own pool. With `pool_size=10` and 8 workers + 4 threads, you get 8 x 4 x 10 = 320 potential connections to PgBouncer. Size PgBouncer's `max_client_conn` accordingly. [src4, src7]
- **PgBouncer is single-threaded**: PgBouncer uses async I/O on a single thread. For high-throughput scenarios (>50 active queries per CPU core), consider multi-threaded alternatives like PgCat (Rust, 59K tps peak) or Odyssey (C, enterprise). PgBouncer peaks at ~44K tps. [src4, src7, src8]
- **PgBouncer CVE-2025-2291**: PgBouncer versions before 1.24.1 did not check the `VALID UNTIL` field of user passwords when using `auth_query`. Upgrade to 1.24.1+ or 1.25.0+. [src2]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (39 lines)

```sql
-- === Connection overview ===
SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT count(*) FROM pg_stat_activity;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;
# ... (see full script)
```

```bash
# PgBouncer admin commands (connect to pgbouncer database)
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

# Inside PgBouncer admin:
SHOW POOLS;       # pool status (cl_waiting = problem)
SHOW STATS;       # request rates, latency
SHOW SERVERS;     # server-side connections
SHOW CLIENTS;     # client-side connections (idle state new in 1.25)
SHOW CONFIG;      # current config
RELOAD;           # reload config file (no restart needed)
PAUSE mydb;       # pause all queries to a DB (for maintenance)
RESUME mydb;      # resume after pause
```

## Version History & Compatibility

| Feature | Available Since | Notes |
|---|---|---|
| `pg_stat_activity` | PostgreSQL 7.4 | Essential monitoring view [src3] |
| `pg_terminate_backend()` | PostgreSQL 8.4 | Terminate connections gracefully [src3] |
| `idle_in_transaction_session_timeout` | PostgreSQL 9.6 | Kills stuck transactions automatically [src1] |
| `statement_timeout` | PostgreSQL 7.3 | Kills slow statements [src1] |
| `idle_session_timeout` | PostgreSQL 14 | Kills completely idle connections [src1] |
| `transaction_timeout` | PostgreSQL 17 (2024-09) | Limits total transaction duration -- closes gap between statement_timeout and idle_in_transaction_session_timeout [src5] |
| PgBouncer transaction pooling | PgBouncer 1.0 (2007) | Core feature; stable for 18+ years [src2] |
| PgBouncer SCRAM-SHA-256 auth | PgBouncer 1.14 (2020) | Required for PostgreSQL 14+ default auth [src2] |
| PgBouncer `client_idle_timeout` | PgBouncer 1.24 (2025-01) | Per-user idle client timeout [src2] |
| PgBouncer `max_prepared_statements` default | PgBouncer 1.24 (2025-01) | Enabled by default (200); was opt-in before [src2] |
| PgBouncer `max_user_client_connections` | PgBouncer 1.24 (2025-01) | Per-user connection limits [src2] |
| PgBouncer LDAP auth | PgBouncer 1.25 (2025-11) | LDAP authentication via HBA file or auth_ldap_options [src2] |
| PgBouncer client-side direct TLS | PgBouncer 1.25 (2025-11) | Supports PostgreSQL 17 faster TLS setup [src2] |
| PgBouncer `transaction_timeout` | PgBouncer 1.25 (2025-11) | Global and per-user transaction timeout at pooler level [src2] |
| PgBouncer `query_wait_notify` | PgBouncer 1.25 (2025-11) | NOTICE to queued clients after configurable wait time [src2] |
| `pg_stat_activity.client_port` | PostgreSQL 9.1 | Identify client process [src3] |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| `FATAL: sorry, too many clients already` error | Slow query performance (not connection count) | PostgreSQL query optimization (EXPLAIN ANALYZE) |
| Web app with >50 concurrent database connections | Single-user CLI or batch jobs | Direct PostgreSQL connection (no pooler needed) |
| Microservices each opening their own connection pool | Need read replicas + load balancing + failover | Pgpool-II or PgCat (multi-feature proxy) |
| Connection count approaching `max_connections` | Already using managed pooling (Supabase, RDS Proxy) | Managed pooler settings (tuning, not replacing) |
| High idle connection count from connection leaks | PgBouncer single-thread bottleneck at >50K tps | PgCat or Odyssey (multi-threaded) [src4, src7, src8] |

## Important Caveats

- **PostgreSQL 17 `transaction_timeout`** closes a gap: before PG17, a transaction with many short statements and short idle pauses could run indefinitely even with `statement_timeout` and `idle_in_transaction_session_timeout` set. `transaction_timeout` caps the total wall-clock time of any transaction. [src5]
- **PgBouncer 1.24+ enables prepared statements by default** (`max_prepared_statements = 200`). Older versions required explicit opt-in. If upgrading from an older PgBouncer, test that your application's prepared statement usage is compatible with the pooling mode. [src2]
- **Managed databases** (RDS, Cloud SQL, Supabase, Neon) often have their own connection pooling layer. Check whether your provider already runs PgBouncer or Supavisor before deploying your own. Stacking poolers (app pool -> PgBouncer -> managed pooler -> PostgreSQL) adds latency without benefit. [src7]
- **CVE-2025-2291 and CVE-2025-12819**: PgBouncer had two security vulnerabilities in 2025. Ensure you run PgBouncer 1.25.0+ which fixes both. The first allowed expired passwords, the second allowed unauthenticated SQL execution during auth. [src2]

## Related Units

- [PostgreSQL Slow Query Optimization](/software/debugging/postgresql-slow-queries/2026)
- [MySQL Deadlocks](/software/debugging/mysql-deadlocks/2026)
- [Redis Memory Issues](/software/debugging/redis-memory-issues/2026)