veza/infra/ansible/roles/pgbouncer

pgbouncer role — connection pool in front of pg_auto_failover

Sits between the Veza backend and the pg_auto_failover primary. Pools 1000 client connections down to 50 server connections in transaction mode — the backend pays the ~1ms Postgres fork overhead 50 times per pool refresh, not once per HTTP handler.

Wiring

veza-backend-api ──libpq──▶ pgaf-pgbouncer:6432 ──libpq──▶ pgaf-primary:5432
                            (1000 client conn cap)         (single backend per pool slot)

Backend DATABASE_URL in prod:

postgresql://veza:PASSWORD@pgaf-pgbouncer.lxd:6432/veza?sslmode=prefer

NOT the formation URI directly — that bypasses the pool and re-creates the connection-storm problem.

Pool sizing — v1.0.9 Day 7 baseline

knob	default	rationale
max_client_conn	1000	acceptance target: 500 concurrent + headroom
default_pool_size	50	matches Postgres max_connections=100 with margin
min_pool_size	10	warm pool — first request after idle is fast
reserve_pool_size	10	overflow when default_pool_size is exhausted
reserve_pool_timeout	5s	wait this long before opening a reserve slot
server_idle_timeout	600s	aggressive idle reclaim — saves Postgres backend RAM
query_wait_timeout	120s	hard ceiling: a request waiting longer fails fast

Transaction mode — what it forbids

Transaction mode means a connection is returned to the pool after each transaction. Things that break:

• Cross-transaction session state — SET (without SET LOCAL), session GUCs persisted across queries
• Cross-transaction prepared statements (PREPARE outside a tx)
• LISTEN/NOTIFY (the listener returns to the pool, the notification goes nowhere)

Things that work fine — and the Veza backend stays inside this set:

• SET LOCAL inside a tx (scoped to the tx)
• Advisory locks scoped to a tx (pg_advisory_xact_lock)
• Plain SELECT / INSERT / UPDATE / DELETE / DDL within a tx

If a future feature needs LISTEN/NOTIFY (e.g. a real-time invalidation channel), connect that worker straight to Postgres bypassing PgBouncer — separate connection budget, no pool conflict.

Failover behaviour (current scope)

Day 7 ships pgbouncer pointed at pgaf-primary.lxd directly. After a pg_autoctl perform failover, pgbouncer's pool is stranded on the demoted node until DNS TTL (60s) + the role re-renders config and reloads. RTO < 60s for the backend round-trip even in this state — most pool slots fail-over via DNS within the existing TTL window.

W2 day 8 (or v1.1) hardens this with a pg_autoctl state-change hook that issues RELOAD on the pgbouncer admin console as soon as the formation elects a new primary — sub-second pool migration.

Admin / observability

# From any container on the bridge:
psql -h pgaf-pgbouncer.lxd -p 6432 -U postgres pgbouncer

# Then:
SHOW POOLS;       -- per-(database,user) pool stats
SHOW CLIENTS;     -- active client connections
SHOW SERVERS;     -- pool→postgres connections
SHOW STATS;       -- request rate, query duration percentiles
RELOAD;           -- re-read pgbouncer.ini without dropping clients

Acceptance test

bash infra/ansible/tests/test_pgbouncer_load.sh

Spawns pgbench from the host with 500 concurrent clients × 30s and asserts zero connection errors. Pool size = 50 + reserve 10 forces 60 server-side connections to serve 500 client-side, exercising the queue.