perf(recall): one prepare_context issues 178 SQL statements; batch the Atomic Memory authority filter and pin a statement ceiling #2075

Summary

Measured on master at b388a18, one prepare_context call over a single Scope issues 178 SQL
statements once 16 Atomic Memories exist, and 94% of them are the same query shape re-issued once
per candidate. The batched repository primitives already exist and are already used earlier in the
same request; only the authority filter stage is per-item.

I am not proposing any DDL change. The 66 declared tables are contracted by tests (see "Why the table
count is not the lever"). The read amplification per request is not.

On embedded SQLite this currently costs ~36 ms and is therefore invisible; on a networked backend the
same 178 statements are 178 round trips, which is the budget application.py:362-364 is already
trying to leave room for. Numbers below.

Measurements

Setup, no confounders: Python 3.12, pip install -e ".[builtin,server]", in-memory SQLite
(SQLiteConfig()), no embedding model configured, no reranker configured, one Scope, seeded
with runtime.atomic_memory.for_scope(scope).create(...), then one
runtime.context.for_scope(scope).prepare(PrepareContextRequest(query="contract gateway")). Seeding
statements are excluded. Counting uses event.listen(Engine, "before_cursor_execute", ...) — the same
harness as tests/e2e/test_sqlite_vec.py:59 and tests/builtin/runtime/test_statistics.py:556.

Two things follow from the shape of that curve:

1. Marginal cost is ~11 statements per additional memory up to 8, ~6 from 8 to 16, then 0 — the
   candidate pool ceiling (memory_candidate_limit = 16, runtime/prepared_context.py:147).
2. From 8 memories onward the output stops growing (2204 → 2209 bytes) while the statement count
   keeps climbing to 178. Those additional reads do not change what the caller receives.

Statement shapes at 16 memories: 178 statements, 25 distinct shapes, 168 of 178 inside a shape issued
more than once.

Why this is a backend-portability problem rather than a today-is-slow problem

Same path, same 178 statements, measured at 12 repetitions after 3 warm-ups:

The per-statement delay column is an injected model, not a measured OceanBase or seekdb run — I do not
have those numbers, and I am labeling it accordingly. What is measured is the shape: local cost is
concentrated in statement count rather than in execution or disk IO (9.8 ms of 36.5 ms, and file-backed
is not slower than in-memory). A backend where each statement is a round trip converts that statement
count almost 1:1 into wall time.

That matters because the project already budgets for it in the opposite direction: application.py:362
comments "Leave room for database reads and assembly within the default one-second Hook request" and
then sets _CONTEXT_MEMORY_EMBEDDING_TIMEOUT_SECONDS = 0.25. With a remote engine at 2 ms per statement,
the reads alone consume ~356 ms of that one second before assembly starts. seekdb and OceanBase are
supported backends, and #2031 is provisioning dedicated databases for them, so this is the intended
deployment and not a hypothetical.

Cause

• _filter_current_candidates (builtin/runtime/atomic_memory.py:673) loops the candidate tuple
  (:694) and calls _current_record (:383) per item, which issues single-item artifacts.latest(...)
  (persistence/artifacts.py:461) plus service.states.get(...). That is the 25x/24x/24x/24x/17x/16x
  above.
• The batched equivalents already exist and already run on the same request: _validate_search_hits
  (atomic_memory.py:551) calls artifacts.get_many (:605; persistence/artifacts.py:414, whose own
  docstring reads "Load exact Revisions and ordered lineage with bounded batch queries") and
  _load_lineage_many (artifacts.py:706). So this is wiring an existing owner into a second stage, not
  new machinery.
• The whole filter runs twice per request whenever a reranker is configured: once from
  application.py:1171 (_available_atomic_candidates) and again from atomic_memory.py:729 inside
  _rerank. No reranker was configured in my runs, so 178 is the floor, not the worst case.
• The snapshot pin executes and discards its rows:
  await connection.execute(select(ARTIFACT_HEADS_TABLE.c.artifact_id).where(scope_id).limit(1)) at
  atomic_memory.py:688-692; same pattern at :446-450 and runtime/atomic_memory_processing.py:601.
• Scope resolution re-reads the same three Scope tables 6 times each within one request
  (scope/repository.py:134-182). runtime/_scope_cache.py holds leases, locks and counts only — it does
  not cache descriptors.

Proposal

1. Batch the authority filter over the candidate refs, mirroring _validate_search_hits. This should
   collapse the 25x/24x/24x/24x groups to one statement each.
   On the constraint RFC 1803 already states for this ("Batch content completion adds a read and must use
   the same versions and consistent state as retrieval", docs/en/rfcs/1803-artifact-search-projections.md
   Drawbacks): the filter already runs inside application.database.transaction(consistent_snapshot=True)
   (atomic_memory.py:685-686), so the batched version can reuse that same connection and that same pin.
   The batched call would read exact hit.artifact_ref revisions, and the existing per-item equality
   checks (current.ref == candidate.hit.artifact_ref, state_version match, ACTIVE) would be applied
   to the batched rows unchanged. I read this as a mechanical translation, but it is your call whether the
   invariant should be restated rather than assumed.
2. Make the second authorization pass one batched re-check instead of 6 × candidates. The comment at
   atomic_memory.py:728 — "Reauthorize the exact candidate bodies immediately before an external rank
   model" — is a real security boundary, so I am not proposing to delete the second pass, only to
   batch it. I would want a maintainer view on whether the per-item form buys an ordering guarantee that
   a batched form would lose; if it does, this item is dropped.
3. Drop the discarded pin selects, or replace them with a snapshot handle the transaction helper can
   already expose, if one exists.
4. Add a statement-count ceiling for prepare_context. Nothing pins this today, so the number can only
   grow. Precedents in-repo: tests/builtin/runtime/test_statistics.py:553 asserts each budgeted table is
   read exactly once regardless of Scope count, with the docstring "A selection must not cost one query per
   Scope; see issue bug(server): POST /v1/stats latency degrades sharply under low concurrency on file-backed SQLite #1599"; and tests/e2e/test_sqlite_vec.py:117 asserts < 40.

No new table, no column, no migration, no new HTTP/MCP operation, no public contract change.

Why the table count is not the lever

The repo declares 66 tables (52 of them in builtin/persistence/tables.py, listed as
BUILTIN_TABLES:1334). Reducing that number is deliberately blocked:

• tests/builtin/persistence/test_mysql_schema.py:152-169 parses the --table '…' lists out of
  docs/en/docs/operate/troubleshoot.md and docs/zh/…, requires en == zh, requires 4 restore layers,
  requires every FK parent in a strictly earlier layer, and asserts
  set(restored) == {t.name for t in BUILTIN_TABLES}. Renaming, merging or dropping one table fails on
  both the code and the docs side.
• :171-189 pins max(index key budget) == 2640; merging tables widens composite keys toward that ceiling.
• The apparent "head + version" duplication is not duplication: pc_artifact_heads carries a lifecycle
  CHECK machine (tables.py:250-266), pc_candidate_heads a discriminated-union CHECK (:434-450), and
  the Topic Memory projections are reconciled against their publications at startup, raising
  TopicMemoryStorageInvariantError on drift (persistence/topic_memory.py:105-215).
• The 4 extra SQLite tables are engine-required FTS5/vector helpers; OceanBase expresses the same thing as
  columns and indexes on the shared tables.

The genuine storage-side duplication is real but already adjudicated: one body is stored as
pc_artifacts.content (:199), then pc_artifact_heads.searchable_text (:234), then mirrored into FTS;
Topic Memory stores a body three times; RFC 1803's Drawbacks already concede "Wide tables may duplicate
content". Not proposed here — listed so it is visible that I looked and found it already decided.

What I did not measure

• No MySQL/OceanBase or seekdb run. The 0.5 ms and 2 ms rows are injected per-statement delays, not
  observed latencies, and the 1:1 round-trip argument is reasoning on top of a measured statement count.
• The reranker-on path (proposal 2), mode="vector", tag_filter, multi-Scope prepare, and cold caches.
• Token counts, which are orthogonal and owned by feat: reduce tokens in memory extraction and context delivery #1989 / docs(rfc): propose input and output gates for token optimization #1990.

Related

#1599 (fixed doctrine, statistics path) · #1631 (merged N+1 fix, SQLite vector completeness) · #1864
(rejects "just make prepare_context cheaper" as a decision-cost optimization — this issue does not
disagree; it is about statements per admitted entry, and batching is the cheapest way to serve that same
manifest) · #1866 (its rejected alternative is "do not scan inside recall, recall is the hot path" —
agreement, not conflict) · #2039 / #2040 (timeout and token chain, no overlap; the budget arithmetic above
is offered as context, not as a claim about their findings) · #1937 / #2005 (optional rerankers, which would
make proposal 2 load-bearing) · #2031 (dedicated seekdb/OceanBase databases, which is the deployment where
the round-trip cost lands).