Eve library

PgBouncer Connection Pooler

Other

Revision 1

On this page

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:

  1. Increase default_pool_size
  2. Check for long-running queries/transactions
  3. Verify application is closing connections properly

Connection Refused#

Check:

  1. PgBouncer is running: docker compose ps
  2. PostgreSQL is healthy: docker compose logs postgres
  3. Authentication in userlist.txt

Slow Queries#

If queries are slow through PgBouncer:

  1. Check pool saturation in SHOW POOLS
  2. Verify no connection leaks
  3. 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 file
  • userlist.txt - User authentication
  • docker-entrypoint.sh - Container startup script
  • Dockerfile - Container image definition