# PgBouncer Connection Pooling

> Lightweight PostgreSQL connection pooler for high-performance applications.

- Category: Database Stacks
- Difficulty: intermediate
- Components: pgbouncer, postgresql, pgadmin
- Tags: pgbouncer, postgresql, connection-pooling, performance
- Source: https://docker.recipes/databases/pgbouncer-pool

## Overview

PgBouncer is a lightweight connection pooler for PostgreSQL developed to address connection overhead and resource exhaustion in high-concurrency applications. Unlike direct database connections that consume significant memory and processing power per connection, PgBouncer maintains a small pool of persistent connections to PostgreSQL while serving hundreds or thousands of client connections efficiently. This middleware layer dramatically reduces connection establishment overhead and prevents PostgreSQL from being overwhelmed by connection storms. This stack combines PgBouncer with PostgreSQL 16 and pgAdmin to create a complete database solution with built-in connection pooling. The configuration uses transaction-level pooling, where connections are returned to the pool after each transaction completes, maximizing connection reuse while maintaining transaction isolation. Applications connect to PgBouncer on port 6432, which intelligently routes queries through a managed pool of 20 connections to the underlying PostgreSQL instance. High-traffic applications, SaaS platforms, and microservices architectures benefit most from this setup. Organizations experiencing PostgreSQL connection limits, slow application response times due to connection overhead, or seeking to optimize database resource utilization will find immediate value. The transaction pooling mode makes this particularly effective for web applications with short-lived queries, API backends serving multiple clients simultaneously, and environments where connection counts significantly exceed optimal database connection limits.

## Features

- Transaction-level connection pooling with configurable pool sizes (20 default, 5 minimum, 5 reserve)
- Support for up to 1,000 concurrent client connections through intelligent connection multiplexing
- PostgreSQL 16 with Alpine Linux base for enhanced performance and security features
- pgAdmin 4 web interface for visual connection pool monitoring and database administration
- Health check integration ensuring PgBouncer only starts after PostgreSQL is ready
- Automatic connection recycling and idle connection management
- SQL query routing and protocol translation between clients and PostgreSQL
- Real-time connection statistics and pool utilization metrics through pgAdmin

## Use cases

- Web applications with hundreds of concurrent users experiencing PostgreSQL max_connections limits
- Microservices architectures where multiple services need database access without connection proliferation
- SaaS platforms serving multiple tenants requiring efficient database resource sharing
- API backends with burst traffic patterns that overwhelm direct PostgreSQL connections
- Development environments needing production-like connection pooling for realistic testing
- Legacy applications that create excessive database connections without connection management
- High-frequency trading or analytics applications requiring minimal connection establishment latency

## Prerequisites

- Minimum 1.5GB RAM (512MB PostgreSQL + 256MB pgAdmin + 128MB PgBouncer + overhead)
- Docker Engine 20.10+ with Docker Compose v2 support
- Available ports 5432 (PostgreSQL), 6432 (PgBouncer), and 8080 (pgAdmin)
- Understanding of PostgreSQL connection limits and pooling concepts
- Basic knowledge of environment variable configuration for database credentials
- Familiarity with transaction vs session vs statement pooling modes

## docker-compose.yml

```yaml
services:
  pgbouncer:
    image: edoburu/pgbouncer:latest
    ports:
      - "6432:6432"
    environment:
      DATABASE_URL: postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 20
      MIN_POOL_SIZE: 5
      RESERVE_POOL_SIZE: 5
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - pg-net
    restart: unless-stopped

  postgres:
    image: postgres:16-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - pg-net
    restart: unless-stopped

  pgadmin:
    image: dpage/pgadmin4:latest
    ports:
      - "8080:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    depends_on:
      - postgres
    networks:
      - pg-net
    restart: unless-stopped

volumes:
  postgres_data:
  pgadmin_data:

networks:
  pg-net:
    driver: bridge
```

## .env

```bash
# PostgreSQL
POSTGRES_USER=app
POSTGRES_PASSWORD=secure_postgres_password
POSTGRES_DB=app

# PgAdmin
PGADMIN_EMAIL=admin@example.com
PGADMIN_PASSWORD=secure_pgadmin_password
```

## Usage notes

- Connect applications to PgBouncer at localhost:6432
- Direct PostgreSQL at localhost:5432
- PgAdmin at http://localhost:8080
- Transaction pooling mode for optimal performance

## Troubleshooting

- PgBouncer connection refused: Verify PostgreSQL health check passes before PgBouncer starts using docker-compose logs postgres
- Application timeout errors: Increase DEFAULT_POOL_SIZE or check for long-running transactions blocking pool connections
- pgAdmin cannot connect to database: Use postgres:5432 as hostname, not localhost, when configuring server connections
- Pool exhaustion warnings: Monitor connection usage patterns and adjust MAX_CLIENT_CONN or pool size parameters
- Authentication failed through PgBouncer: Ensure DATABASE_URL credentials match PostgreSQL POSTGRES_USER and POSTGRES_PASSWORD exactly
- High connection latency: Switch to session pooling mode if application maintains transaction state across requests

