Import perf, round 2: the POST phase is 82% of a real cross-org import at a flat 8 rec/s

[Chouffe]

Follow-up to the #347/#350/#351 series, which fixed the API server's import
path. This one is about the remaining cost, measured against a remote target
rather than localhost: a real cross-org week takes ~44 min, and ~82% of that
is the POST phase, at a flat 8 rec/s that no amount of client concurrency
currently moves.

Measurement

Run: 2026-07-25..2026-07-31, 19 organizations (all but pyroadmins/demo),
alert-API admin credential, importer on a laptop, target = the test server at
162.19.113.48 (docker-compose.server.yml), IMAGE_TRANSFER=url.
Workload: 1,014 alert sequences → 1,009 after filtering → 1,166 object
sequences → 17,368 records to post.

Phase	Wall clock	Share
Fetch + object split (step 1)	480s	18%
POST to annotation API	~36 min (17,368 rec @ 8.05/s)	82%

Throughput was sampled at 30s intervals over two independent windows:
8.08 rec/s (120s window) and 8.05 rec/s (92s window). It is flat —
this is a steady state, not a warm-up artefact.

What dominates, and why

1. Per-detection server latency ≈ 1.5s (the main cost)

The importer keeps ~12 POSTs in flight (api_posting = 3 sequences ×
detection_per_sequence = 4, from the default max_workers=4). 12 in flight
at 8.05 rec/s ⇒ ~1.5s of server time per detection. Contributors, in
descending order of suspicion:

• upload_file_from_url builds a new httpx.AsyncClient per detection
  (storage.py:444, inside the function). Every image download pays a fresh
  TCP+TLS handshake to the alert API's S3. This is the same class of problem
  perf(api): run blocking S3 work off the event loop #350 fixed for boto3, left unfixed for the download leg.
• The test server runs ONE uvicorn process. perf(api): run uvicorn with 2 workers and resize the DB pool #351 added
  --workers $${UVICORN_WORKERS:-2} to the root docker-compose.yml
  (line 101), but docker-compose.server.yml on the VM still runs plain
  uvicorn app.main:app --host 0.0.0.0 --port 5050 --proxy-headers — and
  carries none of perf(api): run uvicorn with 2 workers and resize the DB pool #351's DB-pool env either. The deployment that people
  actually import into silently kept the pre-perf(api): run uvicorn with 2 workers and resize the DB pool #351 serving profile. Evidence:
  /status (a trivial endpoint) answered in 205–390ms while the import
  ran, against ~14ms to connect.
• No HTTP connection reuse in the importer. app/clients/annotation_api.py
  uses bare requests.post per call; there is no Session, so every one of
  the 17,368 records re-handshakes to the API. Already listed as deferred in
  the perf(import): reuse the posting token for annotation creation #347/perf(api): run blocking S3 work off the event loop #350/perf(api): run uvicorn with 2 workers and resize the DB pool #351 spec; a remote target is where it starts to matter.

2. list_cameras costs 19.1s in a single call

Measured directly: 19.12s to return 157 rows (list_orgs 0.98s,
list_sequences for a day 1.45s, per-sequence detections median 0.54s over 40
calls). It is ~4% of this import — but load_alert_api_metadata runs once
per run_import, and connector_import.py calls run_import once per
day (trailing_days default 3). So a daily connector run pays ~57s of
metadata refetch, and on the common steady-state day — everything already
imported, so the skip set zeroes out both the detection fetches and the POSTs —
that call is effectively the entire cost of the run.

Options: cache the camera index across days within one connector run (it does
not change between days of the same run), or ask the alert API why
/cameras/?include_non_trustable=true takes 19s for 157 rows.

3. Smaller, known items

• Fetch concurrency is 4 (max_workers), so 1,009 detection GETs at a 0.54s
  median ≈ 2.3 min. Raising it is cheap if the alert API tolerates it.
• fetch_sequences_for_date fans 7 date listings out over a
  ProcessPoolExecutor (sequence_fetching.py:444) — process spawn for
  pure I/O. Already flagged as deferred in the earlier spec.

Suggested order

1. Fix docker-compose.server.yml to match root (workers + pool env). Cheapest
   possible win; it is a deploy gap, not a code change, and until it lands
   every measurement taken against the test server understates the fixed code.
2. Reuse one httpx.AsyncClient for image downloads (module-level or
   app-lifespan client), mirroring perf(api): run blocking S3 work off the event loop #350's boto3 fix.
3. requests.Session in the importer's annotation-API client.
4. Cache camera metadata across days inside one connector run.

Items 2–4 should each be measured against a remote target — localhost hides
exactly the handshake costs this ticket is about, in the same way plain MinIO
inverted #350's result.

Any change here must pass the output-equivalence gate from
docs/specs/2026-08-07-import-performance-design.md: same date range ⇒
byte-identical id-free DB projection, annotation detection_id rewritten to
the detection's alert_api_id.

[Chouffe]

Split into three implementation tickets:

• Import perf: upload_file_from_url builds a new httpx.AsyncClient per detection #359 — upload_file_from_url builds a new httpx.AsyncClient per detection (contributor 1)
• docker-compose.server.yml never received #351: the test server runs one uvicorn worker #360 — docker-compose.server.yml never received perf(api): run uvicorn with 2 workers and resize the DB pool #351, so the test server runs one uvicorn worker (contributor 2; land first — until it does, before/after numbers for the other two are measured on the wrong serving profile)
• Import perf: annotation-API client has no connection reuse (no requests.Session) #361 — annotation-API client has no connection reuse (contributor 3)

The list_cameras finding (19.1s per call, effectively the entire cost of a steady-state connector day) stays here.