# PgBouncer — Lightweight Connection Pooler for PostgreSQL

> PgBouncer is a lightweight connection pooler for PostgreSQL that reduces database connection overhead. It sits between your application and PostgreSQL, multiplexing thousands of client connections over a small pool of server connections.

## Install

Save the content below to `.claude/skills/` or append to your `CLAUDE.md`:

# PgBouncer — Lightweight Connection Pooler for PostgreSQL

## Quick Use
```bash
# Install on Debian/Ubuntu
sudo apt install pgbouncer
# Minimal config in /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
# Start
sudo systemctl start pgbouncer
```

## Introduction
PgBouncer is the standard connection pooler for PostgreSQL deployments. PostgreSQL creates a new process for each connection, which is expensive at scale. PgBouncer maintains a pool of reusable server connections and maps incoming client connections to them, dramatically reducing resource usage and connection latency.

## What PgBouncer Does
- Pools PostgreSQL connections to reduce per-connection process overhead
- Supports three pool modes: session, transaction, and statement-level
- Handles thousands of client connections with minimal memory (about 2KB per connection)
- Provides online restart and configuration reload without dropping clients
- Exposes an admin console for live monitoring and pool management

## Architecture Overview
PgBouncer is a single-threaded event-loop process using libevent. Client connections are accepted and authenticated, then mapped to server connections from a per-database pool. In transaction mode, a server connection is assigned only for the duration of a transaction, then returned to the pool. The lightweight design means a single PgBouncer instance uses very little CPU and memory even with thousands of concurrent clients.

## Self-Hosting & Configuration
- Install via package manager or compile from source (pure C, minimal dependencies)
- Configure databases section to map logical names to actual PostgreSQL hosts
- Set `pool_mode` to `transaction` for most web applications (best multiplexing)
- Tune `default_pool_size` based on your PostgreSQL `max_connections` setting
- Use `auth_type = hba` or `md5` to authenticate clients against PostgreSQL

## Key Features
- Extremely lightweight: 2KB per connection vs PostgreSQL's ~10MB per process
- Transaction-mode pooling maximizes connection reuse for web workloads
- Online restart preserves client connections during upgrades
- DNS-based host resolution for failover and load balancing
- Compatible with all PostgreSQL client libraries transparently

## Comparison with Similar Tools
- **Pgpool-II** — More features (replication, load balancing) but heavier and more complex
- **Odyssey** — Multi-threaded pooler from Yandex for higher throughput on many cores
- **Supavisor** — Elixir-based multi-tenant pooler for serverless; PgBouncer is simpler
- **Built-in pooling (application side)** — Per-app pools waste connections; PgBouncer centralizes
- **Amazon RDS Proxy** — Managed equivalent for AWS; PgBouncer is self-hosted and free

## FAQ
**Q: Which pool mode should I use?**
A: Transaction mode for most web apps. Session mode if your app uses prepared statements or session-level features. Statement mode only for simple autocommit workloads.

**Q: Can PgBouncer handle TLS?**
A: Yes, it supports TLS on both client-facing and server-facing connections.

**Q: Does it work with prepared statements?**
A: In transaction mode, prepared statements require PgBouncer 1.21+ with `prepared_statement` tracking enabled.

**Q: How many connections can a single PgBouncer handle?**
A: Easily 10,000+ client connections depending on workload. It is single-threaded, so CPU-bound at very high throughput you might run multiple instances.

## Sources
- https://github.com/pgbouncer/pgbouncer
- https://www.pgbouncer.org/config.html

---
Source: https://tokrepo.com/en/workflows/pgbouncer-lightweight-connection-pooler-postgresql-e04026b0
Author: AI Open Source