Request assembly is O(N) sequential DB round-trips (N+1 per-message parts query): ~20 min before first token on a 6,400-message session

title: "Request assembly is O(N) sequential DB round-trips (N+1 per-message parts query): ~20 min before first token on a 6,400-message session"

Summary

On a long-lived session (6,397 messages / 24,215 parts / 41 MB of part data), model turn spends ~20 minutes assembling the request before any HTTP traffic is issued. The user sees "Thinking" with zero output and zero server activity. The root cause is an N+1 loading pattern in session message streaming: messages are paged 50 at a time, and each message triggers one awaited parts4(messageID) query — ~6,525 sequential awaited round-trips per turn, dominated by per-await runtime overhead rather than actual I/O.

Observed behaviour

Real session (agent work spanning ~1 week, single session, opencode desktop 1.18.29):

• User prompt at 10:33:01 UTC. First assistant output token at 10:54:47 UTC — 21 min 46 s gap with no streamed tokens of any kind (including reasoning, which streams normally once generation starts).
• vLLM server metrics during the gap: num_requests_running mostly 0, GPU 0%, request_prompt_tokens_count advanced by only a few hundred tokens (small auxiliary calls only). The 121K-token request finally hit the server at 10:54.
• The session DB state at the time: 6,397 messages, 24,215 parts, 41.0 MB of part JSON.

After starting a fresh session in the same workspace, turns are back to 1-3 s.

Root cause (from the 1.18.29 desktop bundle, identical in the CLI binary)

session service, stream9(sessionID):

function stream9(sessionID) {
  const size16 = 50;                  // page size
  return Effect.gen(function* () {
    let before;
    while (true) {
      const next3 = yield* page({ sessionID, limit: size16, before })...
      for (const row of next3.items) {
        ...
        parts: yield* parts4(input.messageID)   // one awaited query PER message
      }
    }
  })
}

toModelMessagesEffect is then invoked over the result. So per turn:

• 6,397 / 50 = 128 page queries
• + 6,397 per-message parts4() queries
• ≈ 6,525 awaited round-trips, all sequential.

Measurements (local benchmarks on the same machine / same SQLite file)

SQLite is not the bottleneck: 6,525 point queries would take ~0.2 s raw. The observed ~20 min ÷ 6,525 round-trips ≈ 180 ms per round-trip, which is Effect-runtime/IPC/layer overhead per awaited call, not storage cost. The cost scales O(session size) and is paid on every turn.

Expected behaviour

Assembling the model request for an N-message session should require a bounded number of queries (2: one for messages, one batched for parts), independent of N — e.g. SELECT ... FROM part WHERE message_id IN (batch) per page of 50, or a single JOIN. On our hardware this would bring per-turn assembly from ~20 min to well under a second.

Suggested fix

Batch parts retrieval per page inside stream9 (or join parts in the page query). Optionally raise/eliminate the fixed page size of 50 for this path, since the result is consumed wholesale by toModelMessagesEffect anyway.

Environment

• opencode desktop 1.18.29 (also reproduced logically against CLI 1.18.11 binary, same constants)
• Windows 11, session stored in opencode.db (SQLite, WAL)
• Any provider (observed with an OpenAI-compatible self-hosted endpoint)