PostgreSQL & PgBouncer

Overview

PostgreSQL 17 is the primary database. PgBouncer sits in front as a connection pooler to manage database connections efficiently, especially under Octane's long-lived worker model where each worker maintains persistent connections.

---

Architecture

Octane Workers → PgBouncer (port 5432) → PostgreSQL (port 5432 internal)

PgBouncer runs in transaction mode — connections are returned to the pool after each transaction, not after each worker request. This prevents worker count × query concurrency from overwhelming the database.

---

Environment Variables

DB_CONNECTION=pgsql
DB_HOST=pgbouncer        # Points to PgBouncer, not directly to PostgreSQL
DB_PORT=5432
DB_DATABASE=sutomo
DB_USERNAME=sutomo
DB_PASSWORD=...

# Direct connection (maintenance only)
# DB_HOST=postgres
# DB_PORT=5432

---

Why PgBouncer?

Without PgBouncer	With PgBouncer
Each Octane worker opens N connections	Workers share a pool of connections
Max connections = workers × query concurrency	Max connections = pool size (configurable)
PostgreSQL can run out of connections	PgBouncer queues excess queries
Connection storms on restart	Gradual reconnection

---

Docker Compose

services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_DB: sutomo
      POSTGRES_USER: sutomo
      POSTGRES_PASSWORD: ${DB_PASSWORD}

  pgbouncer:
    image: bitnami/pgbouncer:latest
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: sutomo
      PGBOUNCER_DATABASE_USER: sutomo
      PGBOUNCER_DATABASE_PASSWORD: ${DB_PASSWORD}
      PGBOUNCER_MAX_CLIENT_CONN: 100
      PGBOUNCER_POOL_MODE: transaction
    ports:
      - "5432:5432"

---

Key Files

File	Purpose
docker-compose.yml	PostgreSQL + PgBouncer service definitions
config/database.php	Laravel database connection configuration
.env	DB_* connection variables