PgBouncer connection pooling

A session-mode PgBouncer fronts Postgres on both staging and production as a Kamal accessory (heatwave-pgbouncer). This is the architecture + decision record; for operations (rebuild/update, bootstrap, failover) see the pgbouncer skill, and for exact bootstrap commands see config/pgbouncer/README.md.

Topology

app (web ./bin/rails, Sidekiq latency + heavy + EDI roles)
   │  DATABASE_HOST / DATABASE_HOST_VERSIONS = heatwave-haproxy:6433
   ▼
heatwave-haproxy    (Kamal accessory, write-VIP failover router, :6433, mode tcp passthrough)
   │  routes every connection to the CURRENT primary's pgbouncer (health-gated; see below)
   ▼
heatwave-pgbouncer  (Kamal accessory, kamal network, :6432, session mode)
   │  pools: heatwave + heatwave_versions
   ▼
heatwave-postgres   (local Postgres accessory, :5432)

Since 2026-06-13 the app no longer points at the bouncer directly — it points at the heatwave-haproxy:6433 write-VIP, a TCP-passthrough failover router that forwards to whichever node is the live primary, so a pg_promote reroutes writes with no databases.ini edit and no app redeploy. PgBouncer is unchanged underneath; HAProxy just sits in front. See HAPROXY.md for the routing layer. (To revert to direct pooling, set DATABASE_HOST / DATABASE_HOST_VERSIONS back to heatwave-pgbouncer:6432.)

The pooler is co-located with the app on each host: the app always talks to a local bouncer (now via the local HAProxy), and the bouncer’s backend is the local Postgres accessory. The heatwave→heatwave_versions FDW is internal to Postgres (loopback) and is unaffected — the bouncer just needs a pool per database.

Why it exists

1. Cap worker connection demand and deploy overlap. The Kamal cutover initially consolidated Sidekiq into 49 worker threads; PR #1072 sized its pool at DB_POOL=55 (after the default-5 pool caused ActiveRecord::ConnectionTimeoutError across workers — AppSignal #5951–#5961). Sidekiq is now split into latency/heavy/EDI pools of 38+20+5 in production, with 52 worker threads across independent failure domains. With web, steady potential demand is 91 per database under PgBouncer’s 92-connection hard ceiling. Across both application databases that ceiling leaves 13 of PostgreSQL’s 197 normal-user slots for monitoring, auth, and direct access. A rolling deploy briefly runs old and new clients together, so the pooler caps server connections and queues that overlap instead of letting PostgreSQL reject it.
2. A repointable connection layer for the PG16→18 ping-pong. During the Dallas↔Chicago migration, failover became a backend repoint on the bouncer rather than an app redeploy. (That migration is complete — prod cut over to PG18.4 in Dallas on 2026-06-10.) See the ping-pong runbook.

Session mode is mandatory

The app relies on three session-scoped behaviours that transaction pooling would silently break (each transaction would land on a different backend):

• Advisory locks — 28 files use with_advisory_lock/pg_advisory* (plus the Rails migration advisory lock).
• LISTEN/NOTIFY — app/models/liquid/order_drop.rb.
• Per-connection SET statement_timeout — config/database.yml variables:.

So pool_mode = session. The trade-off: session mode does not multiplex at steady state (one server connection is bound to a client for its whole session), so the pooler here is a failover-indirection + connection-ceiling layer, not a connection multiplier. default_pool_size covers almost all configured demand; the small steady-state remainder enters the bounded reserve pool after its 1-3 second timeout. max_db_connections is the hard ceiling that only bites during the deploy-overlap storm. A future optimization could route the read-only reading role through a second, transaction-mode port once the Dallas PG18 replica exists.

Current sizing

param	value	why
pool_mode	session	advisory locks + LISTEN/NOTIFY + SET (above)
default_pool_size	88 production / 80 staging	covers base demand; the bounded reserve can add up to 4 production / 10 staging connections
max_db_connections	92 production / 90 staging, per DB	hard ceiling that reserves 13 production / 17 staging PostgreSQL slots outside the two application pools
max_client_conn	2000	absorbs old+new container overlap during a deploy
min_pool_size	10	warm servers for the deploy handoff
query_wait_timeout	60 s production / 30 s staging	deploy-overlap clients queue rather than error
server_idle_timeout	600 s	reap idle servers so steady state tracks the active set

Steady per-database configured demand is 91 in production (web 28 + Sidekiq 38+20+5) and 90 in staging (web 28 + Sidekiq 38+20+4). These are configured client-pool ceilings, not permanently open backend connections. Production keeps one connection unallocated beneath PgBouncer’s ceiling. See SIDEKIQ_OPERATIONS.md before changing any role pool.

Reload versus container replacement

PgBouncer is a session-mode boundary. A Rails connection remains bound to one PgBouncer process and backend session, so replacing that container invalidates every long-lived web and Sidekiq connection through it. An in-place admin RELOAD is appropriate for a reloadable map/config change; a Kamal accessory reboot stops, removes, and recreates the process and is a maintenance event.

For an image, command, environment, or other change that requires a container replacement, first confirm the target environment, record the same live Git revision from every app role as <LIVE_VERSION>, verify through operator coordination that no complete bin/deploy pipeline is running, reserve the maintenance window, and obtain explicit production approval. kamal lock status is useful evidence but cannot see a deploy still building or performing post-deploy work outside Kamal’s narrower mutation lock.

mise exec -- bundle exec kamal app version
mise exec -- bundle exec kamal app details
mise exec -- bundle exec kamal lock status

bin/db maintenance up production
mise exec -- bundle exec kamal accessory reboot pgbouncer
bin/db maintenance down production <LIVE_VERSION>

maintenance up puts the proxy into 503 mode and stops every app role so no client retains a dead pooler session. maintenance down requires the confirmed revision, passes it to kamal app boot, and resumes the proxy only after Kamal has health-gated web and all three Sidekiq roles. A failed stop, boot, or proxy resume returns nonzero; boot failure leaves 503 maintenance enabled. Production also preflights a 30-second timeout/gtimeout wrapper before touching roles, and uses non-interactive SSH for the Databasus controller. An unconfirmed Databasus stop fails the maintenance entry; an unconfirmed restart returns nonzero and reports degraded PITR without taking the recovered app back down. Verify both public /up endpoints, all three worker processes, queue fetch/latency, PgBouncer waiting clients, and PostgreSQL headroom before ending observation. For staging, use the same sequence with staging, the confirmed staging revision, and -d staging where the direct Kamal command requires it.

Do not run this alongside bin/deploy. Kamal’s lock protects each mutating Kamal command, but it does not serialize the wrapper’s earlier build/migration or later post-deploy work. Two operators can therefore overlap most of two release pipelines and execute successive swaps as soon as the first lock is released. Until an end-to-end wrapper lock is implemented, production deploys and datastore accessory maintenance require explicit operator coordination.

2026-07-31 replacement incident

At 21:38:32 UTC PgBouncer received SIGTERM during an accessory replacement and was force-killed ten seconds later; its replacement was accepting traffic at 21:38:48. The existing Puma processes retained unusable session-bound connections instead of recovering cleanly. All 20 request slots eventually blocked, /up returned 502, and service recovered only after web was recycled: Puma listened at about 21:43:15 and /up returned 200 at 21:43:26. The customer-visible interruption was about 4.5 minutes.

This was not host memory exhaustion and was not caused by the three-role Sidekiq topology. The operational error was treating an independent forced pooler replacement as transparent to long-lived clients. If it happens again, first confirm the replacement pooler and Postgres are healthy, then recycle web and every Sidekiq role on the already-running application revision. The symptom/recovery checklist is in Kamal troubleshooting.

Auth (no app password stored)

auth_type = scram-sha-256 + auth_query. A low-privilege pgbouncer login role owns nothing but EXECUTE on pgbouncer.get_auth(text) — a SECURITY DEFINER function (owned by a superuser) that returns a user’s verifier from pg_shadow. PgBouncer authenticates an incoming deploy client against that verifier and relays to the backend via SCRAM pass-through, so the app deploy password is never in the pooler’s files. The only secret stored is the plaintext pgbouncer-role password in /data/pgbouncer/userlist.txt — required because PgBouncer must authenticate as the auth-user to run auth_query, and a stored SCRAM verifier (StoredKey) is one-way and can’t produce a client proof for that login. The pgbouncer role is low-privilege; its password lives in 1Password as Heatwave-PgBouncer-{staging,production}.

The image — our own build

ghcr.io/warmlyyours/heatwave-pgbouncer:1.25.2, built from the upstream release tarball (pinned by sha256) via docker/pgbouncer.Dockerfile, Alpine multi-stage, non-root. We build our own rather than depend on a wrapper (edoburu lags upstream) so the supply chain is ours and rebuildable on the next CVE — 1.25.2 carries the SCRAM (CVE-2026-6665) + auth_query search_path (CVE-2025-12819) fixes that land in our auth path. Rebuild/bump procedure: the pgbouncer skill.

Current state (2026-08-01)

• Staging (dal-latitude-heatwave-01) — live through the bouncer since commit 2c7401da8e; backend Postgres is PG18.
• Production — fronted by the bouncer since the 2026-06-08 Chicago cutover. The PG16→18 ping-pong moved production to Dallas on PG18.4 (2026-06-10). After the three-role Sidekiq rollout, live configured demand is 91 per database under the 92-connection cap. On 2026-08-01 PostgreSQL had 80 of 200 connections, three active sessions, no lock waits, and no sustained PgBouncer waiting clients.

The two clusters differ in DB bootstrap mechanics (staging trust + deploy-owned /data; prod peer auth → connect as the postgres superuser + root-owned /data → sudo) — captured in the pgbouncer skill + config/pgbouncer/README.md.

Related

• Operations (build/update, bootstrap, failover, gotchas): pgbouncer skill
• Exact bootstrap commands: config/pgbouncer/README.md
• Deploys / accessories / 1Password: kamal-deploy skill
• PG16→18 migration that motivated the repointable layer: ping-pong runbook