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.