ADR-0040: Raise the Cloud SQL tier to lift the sync-concurrency ceiling

Status: Accepted
Date: 2026-07-29
Deciders: Seth (accountable); ai-engineer cost-discipline review (docs/superpowers/reviews/2026-07-29-ai-engineer-db-tier-817.md, APPROVE-WITH-CHANGES → g1-small) → ceo-blair APPROVED-WITH-CONDITIONS
Issue: caseproof/memberintel#817 (parent #784)

ceo-blair sign-off conditions:

1. The revisit-trigger CPU alert (Decision §4: Cloud SQL CPU ≥ 70% sustained 15 min) ships in the same PR as the tier change — not a deferred follow-up.
2. When #816 lands and the tier is revisited for dedicated-core, cite this ADR + the CPU numbers that triggered it rather than re-running the full ADR process (fast approve).

Context

Background site-sync runs one tenant at a time per Cloud Run instance:
SYNC_ADMISSION_SLOTS = max(1, (POOL − 1 − INFLIGHT_FLOOR) // INFLIGHT_FLOOR)
= max(1, (6 − 1 − 2) // 2) = 1 (src/memberintel/api/sites/service.py).

That 1 is forced by the connection budget, not by choice:

• Prod DB is db-f1-micro, max_connections = 25 (verified live; 22 usable, Postgres reserves 3 for superusers).
• The pool is 6 conns/instance (_POOL_SIZE=2 + _MAX_OVERFLOW=4) with max_instances=3 → 18 peak, under 22. This exact sizing is the fix for #689, where SQLAlchemy defaults (15/instance × 10 instances = 150 demanded) exhausted the 25-conn DB during a traffic burst and cascaded QueuePool-timeout hangs across unrelated routes.
• The app’s per-in-flight-request floor is 2 — it deliberately opens a second independent Session for quota / rate-limit / error-metric bookkeeping that must commit on its own transaction. So one sync already needs 2 conns, leaving no room in a 6-conn pool for a second concurrent sync after the 1-conn reserve.

db/session.py already names this as “the constraint to revisit first: a larger Cloud SQL tier (or a pooler like PgBouncer) is needed before production traffic actually grows, because 25 connections is a real bottleneck at any meaningful concurrency.” This ADR makes that call.

Two things this ADR is NOT:

• It is not the per-site throughput fix. Sync slowness is dominated by #816 (WishList’s 202-queries/page N+1 → 388s for a 25k site). Slots buy parallelism across tenants, not per-site speed; #817 without #816 just parallelizes slow syncs. The pre-launch justification for #817 is (a) removing the 25-conn ceiling before launch request traffic re-hits #689, and (b) enabling slots > 1 for multi-tenant sync.
• It is not a commitment to PgBouncer (see Alternatives).

Decision

1. Move prod (and staging, kept identical per ADR-0037) from db-f1-micro to db-g1-small (1.7 GB), setting the max_connections database_flag explicitly to 50 (not relying on the tier’s implicit default) → 47 usable after Postgres’ 3 superuser reservations. Rationale (per the ai-engineer cost review): db-g1-small clears the exact same SYNC_ADMISSION_SLOTS=3 bar as a dedicated-core tier — 27 peak conns fits comfortably under 47 just as under 147 — so a db-custom-* tier (~$50/mo/env) is ~$40–50/mo of scale-optimization against pre-launch load that does not exist yet (#816, not this, is the dominant sync cost). Size the change to the actual constraint being relieved (the 25-conn ceiling + slots=3), not to a future workload.

2. Re-tune the pool so SYNC_ADMISSION_SLOTS ≥ 3: _POOL_SIZE=3, _MAX_OVERFLOW=6 (9/instance) → (9 − 1 − 2)//2 = 3. Keep max_instances=3 for now (9 × 3 = 27, +4 reserve = 31, under 47) — raise it only when request traffic warrants, independently.

3. Preserve the #689 invariant and its guard, in the same PR as the tfvars change: MAX_POOL_CONNECTIONS_PER_INSTANCE × max_instances ≤ usable max_connections stays asserted in tests/unit/test_db_session.py against the tfvars. Add a _USABLE_MAX_CONNECTIONS_BY_TIER["db-g1-small"] = 47 whitelist entry (not just a docstring) and update the assertion to the new numbers (27 ≤ 47).

4. Concrete revisit trigger for dedicated-core (replaces “before traffic grows”, which no one checks): escalate db-g1-small → a db-custom-* dedicated-core tier when either (a) a Cloud SQL CPU-utilization alert ≥ 70% sustained 15 min fires (add this alert as part of this work, in the project_globals monitoring module — out-of-band TF state, same path as #809/#810), or (b) at the hard launch-readiness checkpoint (dogfood → alpha), whichever comes first.

5. Defer PgBouncer to a scale-time follow-up; file it when connection pressure or cost actually justifies the operational overhead.

6. Apply is gated, and it goes FIRST — before the pool change merges. Terraform plan + full-diff review, -target-scoped to the instance, on an explicit human go — a tier change restarts the instance (brief connection blip). Not applied as a side effect of any other work.

   Order matters, and it is the reverse of the usual “merge, then apply”. The pool size ships inside the API image, and .github/workflows/deploy.yml auto-deploys staging on every push to main touching src/ — so merging the pool raise before the apply lands points a 27-conn budget at a still-22-conn db-f1-micro and re-opens #689 in the gap between the two. The test_db_session.py guard cannot catch this: it proves the pool fits the tier the tfvars declare, and only the apply makes the live DB match. So:

   1. terraform apply the tier bump to staging and production both (each env’s ci.tfvars), while main still carries the old 6-conn pool — safe in either direction, since a bigger DB under a smaller pool is pure headroom.
   2. Verify live: SHOW max_connections; returns 50 on each instance.
   3. Then merge the pool change (staging auto-deploys; production stays on its manual deploy-prod.yml dispatch).

   If a pool raise ever does merge first, the exposure is real but bounded — treat it as a live #689 risk and apply immediately rather than waiting for a scheduled window.

Consequences

Positive:

• Removes the 25-conn ceiling that re-triggers #689 under launch request traffic.
• SYNC_ADMISSION_SLOTS rises 1 → 3, so multi-tenant sync parallelizes (bounded by #816 per-site cost until that lands).
• Usable connections go 22 → 47, so max_instances can later be raised on request-traffic grounds alone, without a second tier change in the same step.

Negative / costs:

• Recurring spend: db-f1-micro ~$8/mo → db-g1-small ~$25–30/mo (us-central1, non-HA; verify live) — doubled if/when HA is enabled. Two environments (staging + prod identical, ADR-0037) → ~$50–60/mo combined vs ~$16 today.
• A tier change restarts the instance (brief blip; acceptable pre-launch).
• Shared/burstable core: acceptable at pre-launch load, but CPU-steal is the thing the revisit trigger (Decision §4) watches for.
• Connections are still finite (47 usable, not unbounded) — PgBouncer remains the real answer at large scale.

Alternatives considered

• db-custom-1-3840 (dedicated 1 vCPU, ~$50/mo/env) — the escalation target, not now. Dedicated core removes CPU-steal variance and raises the connection ceiling far higher. But it clears the same slots=3 bar as g1-small at pre-launch load, so paying for it now is scale-optimization ahead of economics (the ai-engineer cost-discipline push-back). It is the concrete escalation target under Decision §4’s revisit trigger.
• PgBouncer transaction-mode pooler. Most connection-efficient and cheapest-per-connection at scale, decouples app concurrency from max_connections. Rejected for now: a new component to deploy/secure (VPC + Cloud SQL access)/monitor, and transaction-mode breaks session-scoped features (prepared statements, SET, LISTEN/NOTIFY, session advisory locks — needs an audit; our tenant locks are in-process asyncio, not pg advisory, so likely compatible). Operational overhead not justified pre-launch.
• Do nothing / raise slots without more DB headroom. Rejected: re-triggers #689 (the pool would exceed the DB ceiling), the exact incident this budget was tuned to prevent.