# PgBouncer for the per tenant databases

Transaction pooling in front of the control plane and every tenant database. The API keeps its
pgxpool per database; PgBouncer multiplexes those onto a small, bounded set of real Postgres
backends so per tenant pools scale to many tenants without exhausting max_connections.

## Why transaction mode is safe here

Every tenant scoped read and write in this codebase runs inside one transaction through
RunInTenantTx, which sets the org GUC with set_config('app.current_org_id', $1, true), the
transaction scoped form. Nothing tenant scoped relies on session state across transactions, so
transaction pooling cannot leak one org's GUC into another's connection. Do not route anything
session scoped (LISTEN or NOTIFY, SET without LOCAL, session advisory locks) through this pool.

## Staging enablement (macOS host, Homebrew)

1. brew install pgbouncer
2. Copy pgbouncer.ini to /opt/homebrew/etc/pgbouncer/pgbouncer.ini and adjust the [databases]
   section to the databases present on the host.
3. Generate the real userlist.txt on the host per userlist.txt.example, chmod 600.
4. Start: pgbouncer -d /opt/homebrew/etc/pgbouncer/pgbouncer.ini
5. Point the staging DSNs at port 6432 (only the port changes):
   postgres://missionmatch_app:...@127.0.0.1:6432/missionmatch_staging
6. Verify: psql "postgres://missionmatch_app:...@127.0.0.1:6432/missionmatch_staging" -c "select 1"
   and SHOW POOLS on the pgbouncer admin console.

## Production notes

The same ini shape ships to the cloud hosts (systemd unit instead of a manual start). Per tenant
provisioning adds one [databases] line per new tenant database; the onboarding runbook owns that
step. Pool sizing starts at default_pool_size 12 per database and is tuned from SHOW POOLS
saturation, never guessed.