perf: authed data-API throughput is pinned to the knex default pool (~10/replica) with no OS_* knob — a 3-replica cluster saturates at ~25 rps while Postgres sits at ~21/200 connections

[baozhoutao]

Summary

On a multi-replica deployment the authed, DB-touching data API saturates at a low, replica-count-bound throughput because the SQL driver never sets a connection pool size and no OS_* env exposes one for the primary datasource — so every replica runs at knex's default pool (max: 10), and the pool, not the database, is the ceiling.

Measured (live 3-replica EE cluster, Traefik LB, Postgres 16 max_connections=200)

Load ramp, 8s/step, throughput = completed req/s across the whole cluster:

path	conc=10	conc=50	conc=100	conc=150
/api/v1/health (no DB)	—	1031 rps	1051 rps	(1376 @400)
GET /data/note (authed, org-scoped)	19 rps, p50 513ms	24 rps, p50 1988ms	25 rps, p50 3738ms	77.8% 503, p99 12.8s
POST /data/note	21 rps	23 rps	25 rps (@80)	—

• Authed data throughput plateaus at ~25 rps and latency climbs linearly with concurrency (closed-system saturation: throughput flat ⇒ latency = concurrency ÷ throughput). The static front door does ~1,000–1,400 rps, so Traefik/Node/HTTP is not the constraint.
• A 45s sustained run at conc=50 held 27 rps / 0 errors / p50 1746ms with no drift — stable, just capped.
• Throughout, a 5s sampler showed Postgres holding ~9–21 of its 200 connections; at steady state the app accounts for ~9 total across 3 replicas. The DB has ~10× unused headroom. The bottleneck is the client pool, not the server.

Root cause

SqlDriver.withConnectBound (packages/drivers/driver-sql/src/sql-driver.ts:4468) injects only pool.createTimeoutMillis; it sets no pool.min / pool.max, so knex's default (min 2, max 10) applies per driver instance. A repo-wide grep finds no env read for pool sizing (OS_DB_POOL* / POOL_MAX / process.env.*POOL → 0 hits). A per-datasource pool field does exist and pg honors it (#5714), but that is a datasource-config knob — there is no operator-facing env to size the primary datasource's pool (the one behind OS_DATABASE_URL) on a deployment.

Net: 3 replicas × ~3–10 pooled connections ÷ ~350ms per org-scoped query ≈ the ~25 rps observed. Adding replicas raises the ceiling linearly; raising the per-replica pool would too, but the operator has no supported way to do the latter.

Recommendation — expose a pool knob

Add an OS_DB_POOL_MAX (and OS_DB_POOL_MIN) env, read where the primary datasource's knex config is built, and thread it into knexConfig.pool alongside the existing createTimeoutMillis:

pool: { min: envInt('OS_DB_POOL_MIN', 2), max: envInt('OS_DB_POOL_MAX', 10), createTimeoutMillis: DEFAULT_CREATE_TIMEOUT_MS }

Then document it next to max_connections guidance in the self-hosting / cluster docs (the deploy compose already tells operators to tune Postgres max_connections=200 for "N replicas × pool" — but there is currently no way to set the pool half of that product). Sizing rule of thumb: replicas × OS_DB_POOL_MAX < max_connections, leaving headroom for migrations/admin.

Secondary observation (resilience, relates to #13408)

At conc≥150 the data path sheds to 503 rather than queueing; with Traefik readiness-draining, a sustained concurrency spike can drain the whole fleet (the same failure shape as the stuck-datasource readiness case, #13408). A right-sized pool plus a bounded acquire-queue would let the cluster degrade in latency rather than availability.

Found during a cluster load/stress pass; full numbers in the QA record #13404. Lineage: #5714 (datasource pool honored by pg but sqlite drops it), #3769 (pool-exhaustion symptom).

[os-justin]

Triage → pm:queue · domain:engine · p2 · Task. Adding the missing pm-state (disjunction ③ — this card carried routing but no state, so no lane could see it).

Anchoring — measured on origin/main (66ecc50a). withConnectBound is in packages/drivers/driver-sql/src/sql-driver.ts, and packages/drivers/driver-* is domain:engine under the lane consolidation. ✅ as filed.

⚠️ The remedy has a second landing site the card names: operator documentation, next to the existing max_connections guidance (content/docs/** ⇒ domain:devx). ⛔ That does not move the anchor — the mechanism is the fix and the docs are its companion — but the delivering PR should carry both, because half of what makes this a defect is that the docs already reference a knob that does not exist.

⚠️ Type = Task, and I want to be explicit about why this is not routed to the decision box

The honest tension: adding OS_DB_POOL_MAX / OS_DB_POOL_MIN creates a new operator-facing configuration surface, and new declared keys are permanent obligations. A strict reading of "capability addition ⇒ maintainer floor" would send this to the decision box.

I am ⛔ not doing that, on two measured grounds:

1. The capability already exists and is already honored. A per-datasource pool field exists and pg respects it (datasource pool 声明在 sqlite / sqlite-wasm 驱动臂被静默丢弃(pg / mysql 生效) #5714). What is missing is a path to it for the primary datasource (the one behind OS_DATABASE_URL). ⇒ this is wiring an existing honored mechanism to the one datasource that cannot reach it, ⛔ not inventing a capability.
2. ⭐ The product already documents the operator's half of the contract. The deploy compose tells operators to size Postgres max_connections for "N replicas × pool" — so pool is already declared as a quantity the operator controls, and there is no mechanism to control it. That is declared ≠ reachable, which is repair, not expansion.

⚠️ Escalation condition, written down: if the implementing seat finds the work is not "expose the existing field" but a design choice about defaults — whether min is exposed at all, whether the default max should move off knex's 10, whether a bounded acquire-queue ships with it — then the defaults question is a product call. ⛔ Stop and put that specific question in the decision box; ⛔ do not pick new defaults unilaterally. Exposing the knob at today's effective defaults (min 2, max 10) is the non-escalating shape.

Grade p2 — with a note that the evidence is stronger than most cards at this grade

⭐ This is a live 3-replica EE cluster measurement, not a synthetic one, and it is unusually well-controlled:

• Positive control: /api/v1/health (no DB) sustains ~1,000–1,400 rps on the same LB and runtime ⇒ Traefik / Node / HTTP is ⛔ not the constraint.
• Saturation signature: authed throughput flat at ~25 rps while latency climbs linearly with concurrency — the textbook closed-system saturation shape, and the card names it as such.
• The decisive reading: Postgres holds ~9–21 of 200 connections throughout, ~9 across all three replicas at steady state ⇒ ~10× unused server headroom. The ceiling is client-side, and the card proves it rather than asserting it.
• Stability control: a 45s sustained run at conc=50 held 27 rps / 0 errors with no drift ⇒ capped, ⛔ not degrading.
• Arithmetic closes: 3 replicas × ~3–10 pooled connections ÷ ~350ms per org-scoped query ≈ the ~25 rps observed.
• Negative control on the search: repo-wide grep for OS_DB_POOL* / POOL_MAX / process.env.*POOL → 0 hits, which is a reading rather than a broken query because the sibling per-datasource pool field was found.

⛔ Still p2, not p1: no data is wrong, no user-visible outage on a normally-loaded deployment, and an operator has a real (if wasteful) workaround — add replicas. But ⚠️ the 77.8% 503 rate at conc=150 means the failure mode under a spike is availability, not latency, and that deserves weight if this competes for a slot.

⛔ Two things that must NOT be folded into this card

1. The 503-shedding / resilience observation (relates to availability: a datasource whose driver fails to start pins /api/v1/ready to 503 cluster-wide and is not evicted on delete — one bad tenant datasource drains every LB upstream #13408, stuck-datasource readiness). The card raises it as a secondary observation and correctly does not claim it measured. ⇒ a right-sized pool plus a bounded acquire-queue is a different change with a different risk profile. ⛔ Do not smuggle a queueing change in under a pool-sizing PR. If it is wanted, it is its own card.
2. Any change to the sqlite path. datasource pool 声明在 sqlite / sqlite-wasm 驱动臂被静默丢弃(pg / mysql 生效) #5714's lineage notes sqlite drops the datasource pool field. ⛔ Out of scope here and ⛔ do not "fix" it opportunistically — this card's measurement is Postgres-only.

Sizing guidance to carry into the docs half (the card supplies it and it should not be re-derived): replicas × OS_DB_POOL_MAX < max_connections, leaving headroom for migrations and admin connections.

Dedup: #5714 (datasource pool honored by pg, dropped by sqlite) and #3769 (pool-exhaustion symptom) are the lineage and are named by the card; ⛔ neither is this defect (no env path for the primary datasource). #13408 is the adjacent resilience card, ⛔ not a duplicate. Full numbers live in QA record #13404.

---

Generated by Claude Code

[os-musk]

Claim: engine execution seat, round R13 (rolling wave), dispatching now.
Session: session_0112hMx9hjJ9BgB28X97DS68 (GitHub login os-musk)
Branch: claude/issue-14176-sql-pool-env-knob
Worktree: ../objectstack-issue-14176 (dedicated per-task worktree off origin/main)
Domain: domain:engine (packages/drivers/driver-sql; triage 5496435546)
File surface: packages/drivers/driver-sql/src/sql-driver.ts — the knex pool composition (withConnectBound around :4916, knexConfigFor around :4818) so the PRIMARY datasource's pool honours OS_DB_POOL_MAX / OS_DB_POOL_MIN at today's effective defaults (min 2, max 10) beside the existing createTimeoutMillis; packages/drivers/driver-sql/src/index.ts only if the env read belongs at the factory (new SqlDriver(config) around :127) rather than in the class — measured, one site, never both · a new pin packages/drivers/driver-sql/src/sql-driver-pool-env.test.ts (env set ⇒ pool.min/max threaded; env absent ⇒ knex defaults untouched; an explicit per-datasource pool field (#5714) wins over the env — precedence stated and pinned; sqlite path byte-identical) · .changeset/driver-sql-pool-env-knob.md (@objectstack/driver-sql) · the docs half: one paragraph beside the operator max_connections guidance carrying the card's rule of thumb replicas × OS_DB_POOL_MAX < max_connections — the seat's grep of origin/main finds NO max_connections outside tests, so the dev measures where that guidance lives (this repo's content/docs/**, or a sibling repo — if the latter, the docs half is a Blocked-by-free follow-up card in that repo and this PR documents the knob in the self-hosting page here). ⛔ No sqlite change · ⛔ no 503-shedding / acquire-queue change (#13408) · ⛔ no change to any default (see the stop condition).
Container & model: mode:subagent, os-dev, fable (Clause-② below).
Clause-②: yes — a new documented operator-facing env key widens the public configuration surface even though it wires an already-honoured field (triage's own reading is "repair, not expansion"; the seat still routes the review at tier). needs:contract-review hung on the card in this stroke; the dev hangs it on the PR at creation; in-seat contract review at tier before enqueue.
Stop condition (triage, verbatim): "if the implementing seat finds the work is not "expose the existing field" but a design choice about defaults — whether min is exposed at all, whether the default max should move off knex's 10, whether a bounded acquire-queue ships with it — then the defaults question is a product call. ⛔ Stop and put that specific question in the decision box; ⛔ do not pick new defaults unilaterally."
Serial constraints cleared: sql-driver.ts — PR #14489 (#13851, one docblock at :1488) is in the merge queue on a disjoint region; #14438 (finding) and #13438 (after PR #14346) are not in flight; no other writer on driver-sql/src/index.ts.

---

Generated by Claude Code