PgBouncer in front of Postgres

By default, Kratos and Hydra each open a pool of connections to Postgres. At low scale this is fine, at higher scale you'll hit Postgres's max_connections ceiling and pay heavy memory cost. PgBouncer is a connection pooler that fixes both.

When you need it

More than ~100 concurrent backend connections.
You see "remaining connection slots are reserved" errors.
Memory usage on Postgres host is high (each backend connection = 10-20 MB).

Below 100 concurrent, native pooling in Kratos/Hydra is fine.

Architecture

Kratos (pool of 50)  ──┐
Hydra (pool of 50)   ──┼──► PgBouncer ──► Postgres (max 50 backend)
Athena (pool of 30)  ──┘   (transaction pooling)

PgBouncer multiplexes many client connections onto fewer Postgres backends. With transaction pooling: each Postgres backend serves a new transaction (often a new client) each time it's free.

Compose

Add to docker-compose.yml:

ciam-pgbouncer:
  image: edoburu/pgbouncer:latest
  environment:
    DB_HOST: ciam-postgres
    DB_PORT: 5432
    DB_USER: olympus
    DB_PASSWORD: ${POSTGRES_PASSWORD}
    POOL_MODE: transaction
    MAX_CLIENT_CONN: 1000
    DEFAULT_POOL_SIZE: 25
    SERVER_RESET_QUERY: DISCARD ALL
    AUTH_TYPE: scram-sha-256
  depends_on: [ciam-postgres]
  ports:
    - "6432:5432"  # PgBouncer port

Point Kratos/Hydra/Athena at PgBouncer instead of Postgres:

# .env
KRATOS_DSN=postgres://olympus:${POSTGRES_PASSWORD}@ciam-pgbouncer:5432/olympus?sslmode=disable
HYDRA_DSN=postgres://olympus:${POSTGRES_PASSWORD}@ciam-pgbouncer:5432/olympus?sslmode=disable
ATHENA_DB_URL=postgres://olympus:${POSTGRES_PASSWORD}@ciam-pgbouncer:5432/olympus?sslmode=disable

Note sslmode=disable between app and pgbouncer (they're on the same host, same Docker network). Postgres-side SSL is between PgBouncer and Postgres, configured separately.

Pool modes

Session pooling: client owns a backend until disconnect. Like no pooling. Best for clients that use SET commands, listen/notify.
Transaction pooling: backend assigned per transaction. Best multiplexing. Use this for Olympus.
Statement pooling: backend per statement. Forbids multi-statement transactions. Don't use.

Olympus's Go services (Kratos, Hydra) use simple transaction patterns, transaction pooling works.

Pool sizing

MAX_CLIENT_CONN: 1000          # how many client connections we accept
DEFAULT_POOL_SIZE: 25          # backend connections per database+user
RESERVE_POOL_SIZE: 5           # emergency overflow

So at most ~30 backends to Postgres for olympus user. Set Postgres max_connections = 100 to leave room for admin sessions.

Caveats

Transaction-scope features

Anything that requires session affinity breaks in transaction pooling:

LISTEN/NOTIFY (rare in Olympus).
Prepared statements named at session level (modern drivers handle this).
Temporary tables.

Olympus's services don't use these. If you write custom hooks/webhooks that use them, use a separate connection pool that bypasses PgBouncer.

-- From PgBouncer admin port (default disabled; enable in config)
\c pgbouncer
SHOW POOLS;
SHOW STATS;

Watch:

cl_active: active client connections.
sv_active: active server (backend) connections.
maxwait: longest a client waited for a backend (should be < 100ms).

Prometheus exporter: prometheuscommunity/postgres_exporter includes PgBouncer.

Tuning

If you see maxwait > 1s regularly:

Increase DEFAULT_POOL_SIZE.
Profile slow queries, they hog backends.

If you see cl_waiting > 0:

Pool is saturated. Same fix as above.

If Postgres complains "too many connections":

Lower DEFAULT_POOL_SIZE × number of dbs/users to fit within max_connections.

Read replicas

PgBouncer can route read queries to a replica. Configure separate pools:

# pgbouncer.ini
[databases]
olympus = host=ciam-postgres dbname=olympus
olympus_ro = host=ciam-postgres-replica dbname=olympus

Then in your service config, point read-heavy paths at olympus_ro. Olympus doesn't separate read/write by default, manual.