PgBouncer is a lightweight connection pooler for PostgreSQL used by the Yemaya platform to efficiently manage database connections.
Overview#
PgBouncer sits between the application and PostgreSQL, providing:
- Connection pooling: Reuses database connections across multiple clients
- Connection limiting: Controls the number of connections to PostgreSQL
- Transaction pooling: Shares connections at transaction boundaries for maximum efficiency
- Reduced overhead: PostgreSQL forks a process for each connection; PgBouncer reduces this
Configuration#
Pool Mode: Transaction#
Yemaya uses transaction pooling mode which:
- Releases connections back to the pool at transaction commit/rollback
- Allows many clients to share few server connections
- Requires session-independent queries (no session-level features)
Connection Limits#
ini
default_pool_size = 20 # Server connections per user/database pair
min_pool_size = 5 # Minimum server connections maintained
reserve_pool_size = 5 # Extra connections for burst traffic
max_client_conn = 200 # Maximum client connections
Timeouts#
ini
server_idle_timeout = 600 # Close idle server connections after 10min
client_idle_timeout = 600 # Close idle client connections after 10min
server_connect_timeout = 15 # Timeout for connecting to PostgreSQL
query_timeout = 120 # Query execution timeout
Environment Variables#
| Variable | Description | Default |
|---|---|---|
POSTGRES_HOST |
PostgreSQL host | postgres |
POSTGRES_PORT |
PostgreSQL port | 5432 |
POSTGRES_DB |
Database name | yemaya |
POSTGRES_USER |
Database user | yemaya |
POSTGRES_PASSWORD |
Database password | - |
PGBOUNCER_ADMIN_PASSWORD |
Admin console password | - |
PGBOUNCER_STATS_PASSWORD |
Stats user password | - |
Usage#
Development (Docker Compose)#
bash
# Start all services including PgBouncer
docker compose up -d
# Connect through PgBouncer (port 6432)
psql "postgresql://yemaya:yemaya_dev_password@localhost:6432/yemaya"
# Direct connection bypassing PgBouncer (port 5432)
psql "postgresql://yemaya:yemaya_dev_password@localhost:5432/yemaya"
Application Configuration#
Set environment variables for your application:
env
# Use PgBouncer for regular connections (recommended)
DATABASE_URL="postgresql://yemaya:password@pgbouncer:6432/yemaya?pgbouncer=true"
# Direct connection for migrations only
DATABASE_URL_DIRECT="postgresql://yemaya:password@postgres:5432/yemaya"
Prisma Configuration#
The @yemaya/database package automatically detects PgBouncer and configures
Prisma appropriately:
typescript
import {
createDatabaseClient,
createDirectDatabaseClient,
} from '@yemaya/database';
// Regular client (uses PgBouncer)
const db = createDatabaseClient();
// Direct client for migrations (bypasses PgBouncer)
const directDb = createDirectDatabaseClient();
Monitoring#
PgBouncer Admin Console#
Connect to the admin console:
bash
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer
Useful commands:
sql
SHOW STATS; -- Query statistics
SHOW POOLS; -- Pool status
SHOW CLIENTS; -- Connected clients
SHOW SERVERS; -- Backend connections
SHOW CONFIG; -- Current configuration
Programmatic Monitoring#
Use the PgBouncerAdmin class from @yemaya/database:
typescript
import { PgBouncerAdmin } from '@yemaya/database';
const admin = new PgBouncerAdmin({
host: 'localhost',
port: 6432,
user: 'pgbouncer_admin',
password: 'admin_password',
});
// Get pool statistics
const pools = await admin.getPoolStats();
// Get summary metrics
const summary = await admin.getSummary();
// Health check
const health = await admin.checkHealth();
Health Checks#
Integrate with the @yemaya/health package:
typescript
import {
HealthChecker,
createPgBouncerCheck,
createPgBouncerDetailedCheck,
} from '@yemaya/health';
import { PgBouncerAdmin } from '@yemaya/database';
const checker = new HealthChecker({ service: 'api', version: '1.0.0' });
const pgbouncerAdmin = new PgBouncerAdmin();
// Basic health check
checker.register(createPgBouncerCheck(pgbouncerAdmin));
// Detailed health check with metrics
checker.register(
createPgBouncerDetailedCheck(pgbouncerAdmin, {
maxWaitingThreshold: 5,
maxWaitingCritical: 20,
serverUtilizationThreshold: 80,
})
);
Troubleshooting#
High Waiting Clients#
If you see many waiting clients:
- Increase
default_pool_size - Check for long-running queries/transactions
- Verify application is closing connections properly
Connection Refused#
Check:
- PgBouncer is running:
docker compose ps - PostgreSQL is healthy:
docker compose logs postgres - Authentication in
userlist.txt
Slow Queries#
If queries are slow through PgBouncer:
- Check pool saturation in
SHOW POOLS - Verify no connection leaks
- Consider increasing pool size
Prisma Migration Errors#
Always use direct connection for migrations:
bash
DATABASE_URL=$DATABASE_URL_DIRECT npx prisma migrate deploy
Security#
- PgBouncer uses MD5 authentication by default
- Admin access is restricted to specific users
- Consider using TLS in production
- Never expose PgBouncer port publicly without authentication
Files#
pgbouncer.ini- Main configuration fileuserlist.txt- User authenticationdocker-entrypoint.sh- Container startup scriptDockerfile- Container image definition