# ============================================================================= # AI Software Factory — PgBouncer (transaction mode) — TASK-0135 pooling. # # Why a pooler (and not per-app Prisma pools): # Prisma 5.22 has NO client-side pool (PrismaClientOptions = {datasources, # datasourceUrl, errorFormat, log, transactionOptions} — connectionLimit / # poolTimeout are Prisma 6). Connection pooling is therefore owned HERE, # in front of Postgres, in **transaction mode** (Prisma's required mode: # the client opens a connection, runs one transaction, and may be handed a # different backend next time). # # Auth: # `auth_query` runs `SELECT usename, passwd FROM pg_catalog.pg_shadow # WHERE usename = $1` as the `factory` role (which has the privilege to read # pg_shadow's own scram verifier) to verify each incoming app user. So the # apps keep connecting with REAL roles (api/worker/realtime/web) — pgbouncer # proves each one to the primary before pooling it. The app roles + the # read-only `reader` role are created by the primary's first-boot SQL # (postgres/configmap.yaml `zz-app-roles.sql`) and granted the `factory` # role's privileges, so they can use the same `factory` database. # # How the apps connect (per-app bounded pools, one role each): # api: postgresql://api: @factory-pgbouncer.factory.svc:6432/factory # worker: postgresql://worker: @factory-pgbouncer.factory.svc:6432/factory # realtime: postgresql://realtime:@factory-pgbouncer.factory.svc:6432/factory # web: postgresql://web: @factory-pgbouncer.factory.svc:6432/factory # (DATABASE_REPLICA_URL, the db.read() path: # postgresql://reader:@factory-pgbouncer.factory.svc:6432/factory) # In dev there is no pooler — DATABASE_URL points at Postgres directly as # `factory` and pool sizing is meaningless (one Prisma client per process). # # Pool sizing (per-app pool_size, transaction mode — the deployment.md # §pooling table): # api: 20 (the 1k-rps load path — busiest writer/reader) # worker: 20 (BullMQ steady state; FILES_WORKER_CONCURRENCY ≤ 8) # realtime: 10 (Yjs ops are Redis-first; PG is the flush/commit path) # web: 5 (server-side rendering reads only) # reader: 15 (the read-replica pool — db.read() tree/listing/search) # Max total ≈ 70 server connections, comfortably under the PG16 default # max_connections=100 with headroom for superuser + maintenance. # # Staging check (the spec's "connection counts stay under the documented pool # limit under the api-load suite") — query the pooler's stats during api-load: # psql "postgres://factory:factory@factory-pgbouncer.factory.svc:6432/pgbouncer" \ # -c "SHOW POOLS;" -- current_pool / max_client_conn per db+user # -c "SHOW STATS;" -- total_xact_count, avg_query_time # current_pool per user must stay ≤ that app's pool_size (above). # ============================================================================= apiVersion: v1 kind: ConfigMap metadata: name: factory-pgbouncer labels: app.kubernetes.io/name: pgbouncer app.kubernetes.io/part-of: ai-software-factory data: pgbouncer.ini: | [databases] # Per-app pools (dbname;user → pool_size). Each app connects with its own # role (created + granted by the primary's first-boot SQL). All pools hit # the SAME `factory` database on the primary — separate roles = separate # bounded pools, so one app's burst cannot starve another. factory;api = host=factory-db.factory.svc port=5432 dbname=factory pool_size=20 factory;worker = host=factory-db.factory.svc port=5432 dbname=factory pool_size=20 factory;realtime = host=factory-db.factory.svc port=5432 dbname=factory pool_size=10 factory;web = host=factory-db.factory.svc port=5432 dbname=factory pool_size=5 # Read-replica pool (DATABASE_REPLICA_URL → db.read()). The `reader` role # is read-only (see zz-app-roles.sql); the replica is a hot standby that # rejects writes at the DB. A stray write fails there, not at the pooler. factory;reader = host=factory-db-replica.factory.svc port=5432 dbname=factory pool_size=15 # pgbouncer's own stats database (SHOW POOLS / SHOW STATS). pgbouncer = host=factory-db.factory.svc port=5432 dbname=postgres pool_size=2 [pgbouncer] # transaction mode — the ONLY mode compatible with Prisma (the client # opens a connection, runs one transaction, and is handed a possibly # different backend next time). pool_mode = transaction # Authentication: a static userlist.txt of scram-sha-256 verifiers, one # line per role pgbouncer accepts (api/worker/realtime/web/reader/ # factory). The verifiers are generated ONCE against the factory-db # secret's password and stored in a `factory-pgbouncer-auth` secret # (mounted as /etc/pgbouncer/userlist.txt). Format: # "api" "SCRAM-SHA-256$:$:" # ... (one per role, same password as the factory-db secret) # Generate with: psql -c "SELECT 'SCRAM-SHA-256$' || ... " (the pg # crypto) or the `passfile` pgbouncer ships. See the header's staging note. auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt listen_addr = 0.0.0.0 listen_port = 6432 max_client_conn = 250 max_db_connections = 100 default_pool_size = 20 server_lifetime_sec = 3600 server_idle_timeout = 300 log_connections = 1 log_disconnections = 1 stats_period = 30 admin_users = factory ignore_startup_parameters = search_path,extra_float_digits application_name_add_host = 1 # STAGING-VERIFICATION NOTE: the pooler's auth wiring (auth_query + the app # roles' scram verifiers + the edoburu ini env-expansion) must be confirmed # against the real factory-db secret at first staging apply — there is no # cluster in this repo to test it. The app-role creation (zz-app-roles.sql, # postgres/configmap.yaml) + the DATABASE_URL users in the deployment.md # §pooling table are the contract this manifest implements. --- apiVersion: apps/v1 kind: Deployment metadata: name: factory-pgbouncer labels: app.kubernetes.io/name: pgbouncer app.kubernetes.io/part-of: ai-software-factory spec: replicas: 2 selector: matchLabels: app.kubernetes.io/name: pgbouncer template: metadata: labels: app.kubernetes.io/name: pgbouncer spec: containers: - name: pgbouncer image: edoburu/pgbouncer:1.23.1 ports: - name: pgbouncer containerPort: 6432 volumeMounts: - name: conf mountPath: /etc/pgbouncer/pgbouncer.ini subPath: pgbouncer.ini readOnly: true # The scram verifiers (factory-pgbouncer-auth secret, generated # from the factory-db secret's password — see the ini header). - name: auth mountPath: /etc/pgbouncer/userlist.txt subPath: userlist.txt readOnly: true readinessProbe: tcpSocket: { port: 6432 } initialDelaySeconds: 3 periodSeconds: 5 resources: requests: { cpu: 100m, memory: 128Mi } limits: { cpu: 500m, memory: 512Mi } volumes: - name: conf configMap: { name: factory-pgbouncer } - name: auth secret: { secretName: factory-pgbouncer-auth } --- apiVersion: v1 kind: Service metadata: name: factory-pgbouncer labels: app.kubernetes.io/name: pgbouncer app.kubernetes.io/part-of: ai-software-factory spec: selector: app.kubernetes.io/name: pgbouncer ports: - name: pgbouncer port: 6432 targetPort: 6432