35 — Hatchet PostgreSQL Connection Pooler (PgBouncer via CNPG)

Problem

Hatchet engine and API connect directly to hatchet-pg-rw (CNPG primary). With 2 engine replicas + 2 API replicas, each opening multiple idle connections, PostgreSQL's max_connections ceiling (default 100) is exhausted. Non-superuser connections (hatchet role) are rejected:

FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute (SQLSTATE 53300)

Why Direct Connections Are Dangerous in Production

Concern	Direct connection	With PgBouncer
Connection count	1 connection per goroutine × replicas	Pool of ~20–50 server connections shared
OOM / restart	Stale connections linger until TCP timeout	Pool drains cleanly on app restart
Scale-out	More replicas → linear connection growth	More replicas → same server connections
Failover	App must retry DNS (hatchet-pg-rw) manually	PgBouncer reconnects transparently

Production rule: every workload that is not a CLI tool uses a connection pooler. Direct connections to PostgreSQL primaries are reserved for DBA tooling.

---

Architecture

hatchet-engine (×2)  ──┐
hatchet-api    (×2)  ──┤── hatchet-pg-pooler:5432 (PgBouncer, transaction mode)
                        │         │
                        └──────── hatchet-pg-rw:5432 (CNPG primary, max_connections=200)

CNPG's Pooler CR deploys PgBouncer as a Deployment in the same namespace, managed by the CNPG operator. It reads the superuser secret to authenticate to PostgreSQL and presents the same hatchet user/password interface to clients.

---

Step 35.1 — Immediate Fix: Raise max_connections

While the pooler is being deployed, raise max_connections to stop the crash loop immediately. This is a stopgap only — the pooler in Step 35.2 is the production fix.

Edit mod-ask-devops/applications/ask-db/hatchet/hatchet-pg-cluster.yaml:

spec:
  postgresql:
    parameters:
      max_connections: "200"
      # superuser_reserved_connections default is 3 — leave it

CNPG applies this via a rolling restart (primary switchover + replica restart). No data loss.

Commit + push:

git add applications/ask-db/hatchet/hatchet-pg-cluster.yaml
git commit -m "fix(hatchet-pg): raise max_connections to 200 (stopgap before pooler)"
git push origin main

Verify the change applied:

kubectl exec -n ask hatchet-pg-1 -- psql -U postgres -c "SHOW max_connections;"
# Expected: 200

---

Step 35.2 — Production Fix: Deploy CNPG Pooler (PgBouncer)

Create mod-ask-devops/applications/ask-db/hatchet/hatchet-pg-pooler.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: hatchet-pg-pooler
  namespace: ask
  annotations:
    argocd.argoproj.io/sync-wave: "3"   # after CNPG cluster (wave 2)
spec:
  cluster:
    name: hatchet-pg

  # rw → targets the primary (read-write workloads)
  type: rw

  instances: 2          # HA: 2 PgBouncer pods, anti-affinity spread across nodes

  pgbouncer:
    poolMode: transaction   # transaction mode: connection returned to pool after each txn
    parameters:
      max_client_conn: "500"       # clients (Hatchet goroutines) that can queue
      default_pool_size: "25"      # server connections opened to PostgreSQL
      reserve_pool_size: "5"       # extra connections for bursting
      reserve_pool_timeout: "3"    # seconds before error if no conn in reserve
      server_idle_timeout: "600"   # close idle server connections after 10 min
      log_connections: "0"
      log_disconnections: "0"

  # Service exposed as hatchet-pg-pooler:5432 inside the cluster
  # Hatchet DATABASE_URL must point here, not hatchet-pg-rw
  template:
    metadata:
      labels:
        app: hatchet-pg-pooler
    spec:
      topologySpreadConstraints:
        - maxSkew: 1
          topologyKey: kubernetes.io/hostname
          whenUnsatisfiable: DoNotSchedule
          labelSelector:
            matchLabels:
              app: hatchet-pg-pooler
      resources:
        requests:
          cpu: 50m
          memory: 64Mi
        limits:
          cpu: 200m
          memory: 128Mi

Commit:

git add applications/ask-db/hatchet/hatchet-pg-pooler.yaml
git commit -m "feat(hatchet-pg): add CNPG PgBouncer pooler (transaction mode, 25 server conns)"
git push origin main

---

Step 35.3 — Update DATABASE_URL to Use the Pooler

The hatchet-secret in Vault at secret/ask/hatchet/auh1-dev contains DATABASE_URL. It must point to hatchet-pg-pooler (the PgBouncer service), not hatchet-pg-rw.

Check current value:

export VAULT_ADDR=https://vault.cl1.sq4.aegis.internal
vault kv get -field=DATABASE_URL secret/ask/hatchet/auh1-dev
# Current: postgresql://hatchet:<pass>@hatchet-pg-rw.ask.svc.cluster.local:5432/hatchet

Update:

HATCHET_DB_PASS=$(vault kv get -field=DATABASE_URL secret/ask/hatchet/auh1-dev \
  | grep -oP '(?<=hatchet:)[^@]+')

vault kv patch secret/ask/hatchet/auh1-dev \
  DATABASE_URL="postgresql://hatchet:${HATCHET_DB_PASS}@hatchet-pg-pooler.ask.svc.cluster.local:5432/hatchet"

PgBouncer transaction mode and prepared statements

Hatchet uses pgx driver. In transaction pooling mode, session-level features are not available — prepared statements cached across transactions will fail. pgx by default uses the extended protocol with prepared statements. Set ?pool_mode=transaction&statement_cache_mode=describe in the DSN if you hit prepared statement already exists errors.

The safer DSN for transaction pooling:

postgresql://hatchet:<pass>@hatchet-pg-pooler.ask.svc.cluster.local:5432/hatchet?pool_mode=transaction&statement_cache_mode=describe

Force ESO to re-sync the secret:

kubectl annotate externalsecret hatchet-secret -n ask \
  force-sync=$(date +%s) --overwrite

Then rollout restart Hatchet (engine + api pick up new secret):

kubectl rollout restart deployment/hatchet-engine deployment/hatchet-api -n ask

---

Step 35.4 — Diagnose Connection Counts (Before/After)

# Total connections to hatchet-pg
kubectl exec -n ask hatchet-pg-1 -- psql -U postgres -c \
  "SELECT usename, state, count(*) FROM pg_stat_activity GROUP BY usename, state ORDER BY count DESC;"

# Max connections setting
kubectl exec -n ask hatchet-pg-1 -- psql -U postgres -c "SHOW max_connections;"

# How many are left
kubectl exec -n ask hatchet-pg-1 -- psql -U postgres -c \
  "SELECT max_conn, used, res_for_super, max_conn - used - res_for_super AS available
   FROM (SELECT count(*) used FROM pg_stat_activity) q,
        (SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') mx,
        (SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') rs;"

# PgBouncer stats (after pooler deployed)
kubectl exec -n ask deploy/hatchet-pg-pooler -- psql -U hatchet pgbouncer -c "SHOW POOLS;"
kubectl exec -n ask deploy/hatchet-pg-pooler -- psql -U hatchet pgbouncer -c "SHOW STATS;"

---

Checklist

• [ ] max_connections: 200 applied — SHOW max_connections returns 200 (immediate fix)
• [ ] Hatchet engine crash loop stopped — kubectl get pods -n ask | grep hatchet-engine
• [ ] CNPG Pooler CR deployed — kubectl get pooler -n ask hatchet-pg-pooler
• [ ] Pooler pods running — kubectl get pods -n ask -l app=hatchet-pg-pooler
• [ ] DATABASE_URL in Vault updated to point to pooler
• [ ] ESO secret re-synced — kubectl get externalsecret hatchet-secret -n ask
• [ ] Hatchet engine + API restarted and healthy
• [ ] Connection count post-fix: server connections via PgBouncer ≤ 30

---

Troubleshooting

prepared statement already exists

PgBouncer transaction mode doesn't support session-level prepared statements. Add ?statement_cache_mode=describe to the DATABASE_URL DSN. See Step 35.3.

FATAL: pg_hba.conf rejects connection from PgBouncer

CNPG auto-configures pg_hba to allow the CNPG-managed service account. If the pooler's pod IP range isn't in pg_hba, add pg_hba entries to the CNPG Cluster CR:

spec:
  postgresql:
    pg_hba:
      - "host hatchet hatchet 10.0.0.0/8 md5"

Pooler not created / stays Pending

Check CNPG operator logs:

kubectl logs -n cnpg-system deploy/cnpg-controller-manager --tail=50 | grep -i pooler