[IN-followup] Bound the TypeORM connection pool and set pgBouncer pooling deliberately

[Gavinok]

Context

Split out of #156, where it was tracked as a prerequisite for enabling the API HPA. It has nothing to do with OIDC keys and deserves its own issue.

libs/database/src/database.module.ts originally configured TypeORM without a poolSize or extra.max, so every pod silently got node-postgres' default pool of 10 connections.

Partially addressed. Commit 3cdaced on the AU-01 branch bounds the pool explicitly via DB_POOL_MAX (default 10), DB_POOL_MIN (2), and DB_POOL_IDLE_TIMEOUT_MS (30000), documented in .env.example and covered by tests. What remains is the sizing decision: choosing values that fit the deployment topology, which depends on the pgBouncer question below.

Measured cluster state (2026-08-06)

Environment	Postgres	max_connections	Proxy
4a9599-dev	single instance (#123)	100 (14 in use)	none
4a9599-test	Crunchy, 2 instances	500	pgBouncer, replicas: 1
4a9599-prod	Crunchy, 2 instances	500	pgBouncer, replicas: 1

Postgres' own max_connections is not the constraint. pgBouncer is. Neither PostgresCluster sets pool_mode or default_pool_size under spec.proxy.pgBouncer, so pgBouncer runs on its defaults:

pool_mode = session
default_pool_size = 20
max_client_conn = 100

In session pooling a client connection holds its server connection for the entire session, and TypeORM holds long-lived pooled connections. So the effective ceiling is default_pool_size = 20, not 500. values-prod.yaml at replicaCount: 3 with the HPA to 8 wants roughly 80 connections, plus the worker and the migration Job, which also pushes against max_client_conn = 100.

Work

• Decide whether the app connects through pgBouncer or directly to -primary. values-test.yaml and values-prod.yaml still contain literal <TEST_DB_HOST> / <PROD_DB_HOST> placeholders, so this has not been chosen yet. Note that the comment added in 3cdaced on database.module.ts currently asserts a direct -primary connection; either make that true when filling the placeholders, or correct the comment.
• Make the TypeORM pool bound explicit and configurable (DB_POOL_MAX/DB_POOL_MIN/DB_POOL_IDLE_TIMEOUT_MS, 3cdaced).
• Choose actual DB_POOL_MAX values per environment once the topology below is settled, and set them in values-test.yaml / values-prod.yaml.
• If connecting via pgBouncer, set pool_mode and default_pool_size deliberately in the PostgresCluster spec, sized against max replicas x pool size.
• Note that pgBouncer at replicas: 1 is a single point of failure in front of a 2-instance HA cluster. Decide whether that is acceptable or should be scaled.
• Confirm the migration Job and worker are accounted for in the total, not just API pods.

Notes

Not urgent: neither test nor prod has an app deployment yet, and dev at replicaCount: 1 with a pool of 10 against max_connections: 100 is comfortable. This needs to be settled before test/prod first deploy with the HPA enabled.

Related: #125 (test and prod Crunchy databases), #156 (the OIDC follow-up this was split from, where the explicit pool bound landed).