Database connection budget & graceful-deploy standard

This is the standard for keeping the cluster's Postgres connection demand under the instance's ceiling — across steady state and deploys — to prevent SQLSTATE 53300 ("remaining connection slots are reserved …" / "too many clients already"). It is the deliverable of epic memql#1778.

What the Tiger cutover changed, and what it did not (epic memql#3842 / #3848). The database is now self-hosted CloudNativePG in every environment, so max_connections is ours. Presets and instance overrides under deploy/k8s/components/cnpg-db and the instance overlay set the limit; verify the rendered Cluster and the running database agree after a change. Tiger's per-tier ceiling — ~59 usable slots on the managed tier, which is the scarcity this whole standard was written around — is gone, and with it the two scripts that managed it (conn-surge-watch.sh, deployer-pool-reap.sh, retired) and the leaked managed control-plane deployer pool. Count every pool and rollout overlap. The main pool's configured limit does not bound the process's separate direct and startup migration pools. Their combined peak must fit under max_connections - reserved, because every Postgres backend is a process with its own memory and raising the ceiling trades RAM for headroom. scripts/deploy/conn-headroom-check.sh was kept and rewritten for the same reason — it is the one thing standing between a replica-count bump and the storm that caused memql#1817. Platform: database-platform.md.

Environment-agnostic by design. The connection lifecycle is identical in every environment — a bounded per-pod pool, graceful pool close on shutdown, leak-free reconnect, and a deploy that never exceeds the budget. Only the numbers differ per installation as config (per-pod pool size, the DB's max_connections, replica counts). Local (k3d + CloudNativePG) and Azure (AKS + CloudNativePG) run the same code paths; they just plug in different limits.

#The budget formula

per_process       =  main_pool_limit + direct_pool_limit + migration_pool_limit
peak_connections  =  Σ_over_workloads( peak_concurrent_processes × per_process )
                  +  separate_jobs_and_monitors + operator_and_backup_headroom
 
REQUIRE:  peak_connections  ≤  max_connections − reserved_connections

• reserved_connections = Postgres superuser_reserved_connections (+ any reserved_connections); on Tiger Cloud the platform also reserves a few.
• The dangerous term is deploy-time overlap, not steady state: a full-stack roll briefly runs old + new pods together. Size the budget for the peak, not the average. Include terminating old pods until they close their connections, new replicas, surge, and any blue-green preview/drain overlap. A conservative rolling bound is 2 × replicas + maxSurge processes per Deployment.
• Include every engine and product workload sharing the database, plus active Jobs and scheduled monitors. Do not count an in-process startup migration again as a separate Job; count actual independent Jobs separately.
• With the shipped main pool limit of four, a configured direct pool adds up to four and a startup migration adds one: budget up to nine per process. If no direct DSN is configured, direct calls share the main pool. Any additional independent database pool must be counted too.

#The knobs (per-pod pool — component/database/database.go)

Source of truth is the code; defaults are conservative and overridable per env:

MAX_OPEN_CONNS is a hard cap on that pool, not on the whole pod. The memory-nodes instance reads the MEMORY_NODES_DATABASE_ prefix; the shared memql-db-pool ConfigMap sets its main open/idle limits to four/two. The direct pool has a separate fixed limit of four and ignores these knobs; startup SQL migrations can use one dedicated connection. Check component/database/database.go when those implementation bounds change. Right-size the combined budget against the actual instance ceiling before changing replicas or adding a pooler.

#Defense in depth (already in code)

• 53300 retry (component/database/conn_retry.go, memql#1076): Connect() retries transient slot-exhaustion with bounded jittered backoff instead of failing the query. Covers the residual spike; it deliberately does not retry forever (that just piles pressure on an exhausted server). It does not leak — a connection is only returned on success.

• Graceful shutdown (memql#1778 child D): Database.Stop() calls db.Close() on the pool, so a pod releases its connections on SIGTERM. Ensure the workload's terminationGracePeriodSeconds allows the pool to close before SIGKILL.

• Idle reaping (child G): CONN_MAX_IDLE_TIME_MS + CONN_MAX_LIFETIME_MS bound how long an app connection lingers, and the app stamps idle_session_timeout + idle_in_transaction_session_timeout as per-session params so a wedged app client can't hold a slot indefinitely (Tiger Cloud / local both support it).

• Watch for non-app client leaks (memql#1861): the app fleet reaps its own idle connections, but a separate deploy/migrate/promote tool or exporter that opens a pool and never closes it has no such reaper — it will pin idle slots for hours and eat the budget. In the cloud this showed up as ~28 idle backends stamped application_name=deployer, connecting as the postgres superuser, growing ~1 per 30 min — a Tiger managed control-plane artifact that no longer exists (memql#3848; deployer-pool-reap.sh retired with it). The general shape of the problem survives the provider, so the detection does too — any tool that opens a pool and never closes it:

  sql⧉copy

  SELECT application_name, client_addr, state, count(*),
         min(now() - state_change) AS idle_for
    FROM pg_stat_activity
   WHERE datname = current_database() AND state = 'idle'
   GROUP BY 1,2,3 ORDER BY 4 DESC;

  The durable fix is to stop the leaking client (identified by its client_addr) and make it close its pool / set an idle timeout. What is new is that idle_session_timeout is now ours to set on the Cluster, so a server-side backstop is available where Tiger offered none. The in-cluster deploy + migration cycle does not leak (the migrate Job closes its pool on exit and stamps application_name=memql-migrate, memql#1933).

• Blue-green drain window (child E, memql#1780): the bff Rollout's scaleDownDelaySeconds was cut 3600→300 so a promotion stops holding a full extra bff color (pods + pools) for an hour. That Rollout belongs to the deployment that runs one; this repository's deploy/k8s ships plain Deployments.

#Graceful deploy runbook (cloud)

Ordering and ArgoCD facts that a deploy MUST respect:

1. ArgoCD ignores /spec/replicas on Deployments (ignoreDifferences, no HPA). A manual scale-to-0 to drain connections sticks — ArgoCD will not restore it, and re-enabling auto-sync only reconciles the pod template. Any drain-based recovery must explicitly scale the fleet back up.
2. Bring identity up first. The deploy-gate's authenticated query, the node bootstrap, and every JWT verifier need identity/JWKS. Draining identity aborts the bff blue-green gate.
3. Don't roll the whole DB-heavy fleet at once if the budget is tight — sequence it, or rely on the per-pod cap + scaleDownDelay so peak stays under the ceiling.

#53300 recovery (when the cluster is already wedged)

The fleet has leaked/accumulated connections and every engine pod is 0/1 (/healthz 503, logs spamming 53300):

1. Suspend ArgoCD auto-sync (so a scale-down isn't reverted).
2. Scale the engine fleet to 0 (drains every app connection).
3. Wait ~2 min for Postgres to reap the closed backends.
4. Re-enable auto-sync and explicitly scale the fleet back up (identity first) — ArgoCD will not restore replicas on its own.

#Connection pooling (hybrid endpoint split) — SHIPPED (memql#1925)

The POOLER moved; the SPLIT stayed (memql#3848). What follows describes Tiger Cloud's managed transaction pooler, which is gone. The two-DSN split it created is not — MEMQL_DATABASE_DSN for bulk traffic, MEMORY_NODES_DATABASE_DIRECT_DSN for migrations and session-scoped work — because that split is about transaction-mode pooling breaking session state, which is a property of PgBouncer rather than of Tiger. Today both DSNs point at memql-db-rw and there is no pooler in the path: self-hosting removed the ceiling that made one mandatory, and adding one against a ceiling that no longer exists would be a second network hop and a second thing to fail over for no benefit. A PgBouncer Pooler ships ready but not enabled (deploy/k8s/components/cnpg-db/optional/pooler); the signal to enable it is cnpg_backends_total approaching max_connections in steady state. If you do enable it, the direct DSN must stay direct. The migrations take advisory locks, and an advisory lock acquired in one transaction and released in another is, on a transaction-pooled connection, released by whoever holds that backend next. (This paragraph used to add MEMQL_DB_SEARCH_PATH, a session GUC that was the staging/production boundary. Epic memql#3943 removed the environment concept and that variable with it: one installation, one schema, the default path.)

Right-sizing + scaleDownDelay kept the budget under the ceiling at steady state, but a full-stack roll still tipped it: blue-green bff + rolling mesh briefly doubles the pod count, and pods × MAX_OPEN_CONNS + the leaked deployer pool blew past Tiger's ~88 usable direct slots → the 53300 storm that wedged 0.9.87. The structural fix is Tiger Cloud transaction-mode pooling (PgBouncer) via a hybrid endpoint split:

• Bulk traffic (the bun pool — all queries + mutations, every mesh pod) → MEMQL_DATABASE_DSN points at the transaction pooler (db tsdb_transaction, pooler port 39578). Client connections decouple from Postgres backends, so a deploy surge no longer maps 1:1 to direct slots — the surge-killing multiplier.
• Session-stateful traffic (the 4 advisory-lock / leader components + migrations) → MEMORY_NODES_DATABASE_DIRECT_DSN points at the direct (non-pooled) endpoint (db tsdb). A transaction-mode pooler recycles a server backend between statements, which would drop a held session-scoped advisory lock; these few, bounded connections take a direct slot instead.

Code: Database.DirectBunDB() returns the direct pool when DIRECT_DSN is set, else falls back to the main pool (env-agnostic — local/dev without a pooler is unaffected). bun's pgdriver speaks the simple query protocol (no server-side prepared statements), so transaction pooling is safe. Wiring + the kubectl create secret recipe: deploy/k8s/base/README.md. Local parity: the PgBouncer pod in the k3d cluster (deploy/k8s/base).

#Budget under the pooler

The dangerous deploy-overlap term now applies only to the direct budget, which carries just the session-stateful set — a handful of leader-lock holders (cron leader, topology reconciler, planner admission) plus the one-shot migrate Job — each a single held connection, not replicas × MAX_OPEN_CONNS. Bulk pods multiplex through the pooler, whose server-side pool to Postgres is capped (default_pool_size) regardless of client count. So:

direct_backends  ≈  Σ(session-stateful holders)  +  migrate (1, transient)
pooler_backends  ≤  pooler default_pool_size                 (independent of pod/client surge)
REQUIRE:  direct_backends + pooler_backends  ≤  max_connections − reserved

#Verifying a deploy surge no longer storms (memql#1935)

Prove the storm is gone by re-running the 0.9.87 scenario under the pooler and watching the instance backend count stay under budget throughout.

1. Cut the cluster onto the pooler (one-time): recreate memql-secrets with DSN → pooler and DIRECT_DSN → direct, then roll the pods (identity first). Exact command: deploy/k8s/base/README.md. With DIRECT_DSN unset this is a no-op (single-pool fallback), so the cutover is the trigger.
2. Start the watcher against the instance (read-only; queries the direct endpoint, which sees every backend on the instance):

   shell⧉copy

   # conn-surge-watch.sh was retired with Tiger (memql#3848). The same view,
   # against our own instance:
   kubectl exec -n <ns> memql-db-1 -c postgres -- \
     watch -n5 'psql -U postgres -d memql -c "
       SELECT application_name, count(*)
         FROM pg_stat_activity WHERE datname = current_database()
        GROUP BY 1 ORDER BY 2 DESC;
       SHOW max_connections;"'

   Mesh pods stamp memql-<type>, the migrate Job memql-migrate. Watch the total against max_connections through the roll; the peak is what matters.
3. Trigger a full-stack roll — the wedging scenario: blue-green bff + the rolling mesh (all node-types). e.g. bump the cloud overlay digests / run the normal deploy so every Deployment rolls and the bff Rollout does its blue-green cutover.
4. Watch for SQLSTATE 53300 in the pods throughout the roll (the authoritative signal):

   shell⧉copy

   kubectl logs -n memql -l app.kubernetes.io/part-of=memql --tail=-1 --prefix \
     | grep -iE '53300|remaining connection slots' || echo "no 53300 — good"

5. Stop the watcher (Ctrl-C) and read its summary.

Pass / fail (acceptance):

• PASS — the full roll completes with zero SQLSTATE 53300 and no manual connection-reaping / scale-to-0 recovery; the watcher's peak backend count stayed under budget (capture peak vs budget in the epic).
• FAIL — any 53300, or the peak approached/exceeded the budget (the surge still pressures direct slots — re-check that bulk DSN actually points at the pooler and only the session-stateful set is on DIRECT_DSN).

Quick manual cross-check while a roll is in flight (run via the direct DSN):

-- total backends vs the instance ceiling
SELECT count(*) AS backends,
       (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max_conn
FROM pg_stat_activity WHERE datname IS NOT NULL;
 
-- who holds them (the #1817 application_name attribution)
SELECT coalesce(nullif(application_name,''),'(none)') AS app,
       count(*) FILTER (WHERE state='active') AS active,
       count(*) FILTER (WHERE state LIKE 'idle%') AS idle,
       count(*) AS total
FROM pg_stat_activity WHERE datname IS NOT NULL
GROUP BY 1 ORDER BY total DESC;

#Continuous monitoring + live deploy gate (memql#1958)

The 0.9.88 cutover stormed because nothing watched the live direct budget: an external deployer leak (Tiger control plane, #1822) had silently consumed most of the slots and the deploy cold-started into the remainder. Two layers now guard against that:

1. Continuous monitor — deploy/k8s/base/conn-monitor-cronjob.yaml runs every 5 min (read-only postgres:16-alpine, DIRECT_DSN from memql-secrets). It logs total backends vs budget + the per-application_name breakdown and emits greppable lines a log-based alert can key on:

• CONN-MONITOR WARN: backends N/B (P%) >= 70% ...
• CONN-MONITOR CRIT: backends N/B (P%) >= 90% ...
• CONN-MONITOR WARN: foreign app 'deployer' holds N conns ... possible leak The operator-facing richer version is scripts/ops/conn-monitor.sh (same thresholds; runnable ad-hoc with DIRECT_DSN=... scripts/ops/conn-monitor.sh). A leak — any non-mesh application_name (not memql%) growing past the threshold — is surfaced with its client_addr while it is still small.

2. Live pre-deploy gate — scripts/deploy/conn-headroom-check.sh --live extends the projected-demand gate (#1820) to ALSO read the instance's real max_connections and subtract the live foreign (non-mesh) backends from the budget before the peak check. So a deploy into an already-near-full instance fails fast (CONN-HEADROOM-FAIL) instead of cold-starting into a storm:

# in CI / pre-deploy, with a DSN available:
DIRECT_DSN="$(tiger db connection-string xahn9ru4v6 --with-password)" \
  scripts/deploy/conn-headroom-check.sh --live
# budget = live max_connections - reserved - live foreign backends

Without --live the gate is the pure manifest projection (unchanged). With it, the gate would have BLOCKED the 0.9.88 deploy (budget 105-17-63=25 < projected peak), preventing the storm.

#Future levers (tracked, not yet needed)

• Connection pooler — SHIPPED (epic memql#1925). Tiger Cloud transaction-mode pooling now fronts bulk traffic so pod count no longer maps 1:1 to DB backends; see "Connection pooling (hybrid endpoint split)" below.
• Deploy-gate connection-headroom check (child F): have the gate assert DB connection headroom before promoting. Partly covered today by the gate's startup/auth resilience (memql#1782); a dedicated headroom metric is the next step if budget pressure returns.