Passa al contenuto principale

Connection pooling Postgres/pgBouncer

Akira usa pgBouncer davanti a Postgres per separare le connessioni client applicative dalle connessioni fisiche verso il database.

Tiers​

  • Backend, worker e job async parlano con pgBouncer sulla porta 6432.
  • pgBouncer ascolta su 127.0.0.1 e sull'IP Tailscale del nodo DB: loopback serve exporter, admin console e smoke test locali; Tailscale serve i client applicativi sul nodo management.
  • pgBouncer usa pool_mode = transaction e mantiene un pool ridotto di connessioni server verso Postgres 5432.
  • Postgres resta il backend dati canonico; Alembic e operazioni DBA possono bypassare pgBouncer e usare 5432 quando servono feature di sessione.

DSN applicativi​

Staging/prod:

postgresql+asyncpg://akira_app:<password>@<db-node-tailscale>:6432/akira

Dev locale:

postgresql+asyncpg://akira_app:<password>@127.0.0.1:5432/akira

DATABASE_REPLICA_URL e' opzionale. In staging/prod punta allo stesso pgBouncer del primary usando l'alias database akira_replica; il backend espone get_replica_db() e replica_db_session() per query read-only/reporting. Quando e' assente, il helper ricade sul primary.

Placement decision​

pgBouncer e' deployato sui nodi DB (db-01, db-02) e ascolta sull'IP Tailscale del nodo. Il backend FastAPI su mgmt-01 si connette via Tailscale a db-01:6432 per il primary e all'alias akira_replica per le sessioni read-only.

Trade-off accettato:

  • Pro: deploy semplice, pgBouncer co-located con Postgres e configurazione in un solo ruolo stateful.
  • Contro: backend -> pgBouncer passa da TCP via Tailscale invece che loopback, con latenza stimata +1-3ms accettabile per l'OLTP Akira.

TD-110: migrare al pattern canonical "pgBouncer local sidecar on management" quando si scala management orizzontalmente, si osserva overhead di latenza misurato >5ms p95 in produzione, oppure il routing read replica diventa piu' complesso. Il pattern sidecar e' il riferimento community/cloud-proxy, ma il placement corrente e' funzionalmente equivalente per il deploy pilot single-management-node.

Transaction pooling constraints​

Con pool_mode = transaction non vanno usate feature legate alla sessione fisica Postgres: prepared statement server-side persistenti, temp table session-level, LISTEN/NOTIFY applicativo e advisory lock long-lived.

Il backend disabilita le cache prepared statement di asyncpg:

prepared_statement_cache_size = 0
statement_cache_size = 0

Alembic deve usare una DSN diretta verso Postgres 5432 quando esegue migration o operazioni che richiedono una sessione stabile.

Secrets​

pgBouncer usa auth_type = scram-sha-256 e legge /etc/pgbouncer/userlist.txt. I verifier SCRAM richiesti nel vault sono:

  • vault_postgres_app_scram
  • vault_postgres_ro_scram
  • vault_pgbouncer_admin_scram
  • vault_pgbouncer_stats_scram

pgbouncer_exporter deve autenticarsi alla console admin, quindi richiede anche vault_pgbouncer_stats_password in chiaro nel vault cifrato. Generare i verifier con scripts/gen_pgbouncer_scram.py.

Monitoring​

Prometheus scrape pgbouncer_exporter sulla porta 9127 dei nodi DB e carica alert per exporter down, client in attesa e saturazione delle connessioni server.