spike: 014 embedding-API kernel — Send+Sync handle over a streaming VDBE

[dpsiderius]

Context

Spec 013 (Embedding API, PR #678, Status: Proposed) specifies a public
Connection/Statement/Transaction facade. Two of its seven
requirements are architectural rather than ergonomic, and a wrong choice
in either forces a rewrite of the facade built on top:

• Req 4 — a Send + Sync handle over a connection-owned worker
  thread, because Rc<dyn PageSource>/Rc<RefCell<Pager>> are !Send
  (23 sites in src/).
• Req 7 — incremental row access, because every entry point today
  (execute_with_db, execute_with_db_and_params,
  execute_transaction_step) returns a fully materialized
  Vec<Vec<Value>>.

These interact: streaming across a thread boundary means the statement
handle holds cursor state on the worker while the caller holds a Send
handle. That interaction is the actual risk, and neither requirement's
cost is known.

A static read of the tree already narrows it. Vm is public
(Vm::with_db, bind_params, rows), but the step loop is not:

• src/vdbe/exec.rs:704 — fn dispatch(...) is private
• src/vdbe/exec.rs — fn run(...) is private
• src/vdbe/exec.rs:619 — ResultRow calls emit_row, which pushes
  into a Vec inside the Vm; Vm::rows() hands back the
  accumulated slice

So Req 7 is not implementable as an external layer over today's
public API, and Req 4 probably is. This spike measures both instead of
assuming either.

Hypothesis

A Send + Sync connection handle over a worker-thread-owned Rc engine
graph can serve incremental per-row results and a rows-affected
count, without modifying the engine's Rc/RefCell ownership or the
VDBE opcode-dispatch loop.

The static read above predicts the hypothesis fails on its second
clause — streaming will require an additive engine change. The spike's
job is to find the minimal such change, prove it is additive (no
behaviour change for existing callers), and measure what streaming costs
or saves.

Design

Three prototypes over one fixture, mirroring spike 013's
three-implementation shape:

1. batch — control. Today's public API
   (execute_with_db_and_params) on the caller's thread. No engine
   change.
2. worker_batch — connection owns a thread; commands cross a
   channel; the whole result set returns per statement. Isolates Req 4.
   Expected to need no engine change.
3. worker_stream — same worker, rows yielded one at a time, cursor
   state living on the worker. Exercises Reqs 4 and 7 together. Requires
   the minimal engine change, which is part of the deliverable.

Correctness gates timing: all three prototypes must return byte-identical
rows for the same query before any measurement is trusted (spike 013's
all_three_comparators_agree_on_every_pair precedent).

Success Criteria

1. Send + Sync on the handle proven at compile time (static assertion),
   not asserted in prose.
2. All three prototypes agree on every row of every fixture query.
3. Per-row latency and total throughput measured for all three.
4. Peak memory measured for a large result set — Req 7's "without
   materializing" is a claim that needs a number.
5. Drop-mid-iteration releases the worker's cursors (Req 7's second
   scenario) and a dead engine surfaces as an error, not a hang.
6. The minimal src/ diff for streaming is stated as a diff, with a
   verdict on whether it is additive for existing callers.
7. A go/no-go on spec 013's Req 4 + Req 7 as written, including any
   correction the spec needs before it merges.

Spike Output

• tests/spike/014_embedding_api/ — standalone crate, own [workspace],
  path dep on the parent, Makefile (test/bench), spike-014 target
  in the root Makefile.
• README.md — Hypothesis / Setup / Design / Results / Finding /
  Conclusion, with an explicit ADR-proposed-or-not verdict.
• Any src/ patch stays on the spike branch, never merged; the
  shippable change lands later via its own ticket.

Complexity

Estimate: medium
Reasoning: Three prototypes, but only one is novel — batch is a
copy of examples/query.rs, and worker_batch is a standard
channel-plus-thread wrapper. The cost sits in worker_stream: finding
the minimal step/yield primitive in src/vdbe/exec.rs without
disturbing the dispatch loop, plus a criterion harness and a memory
measurement. Bounded by being throwaway code that ships nothing.

Context

Feeds PR #678's review — the spec is still Status: Proposed, so a
correction found here is cheaper than one found after merge. Does not
block, and is not blocked by, the spec-010/Req-8 autoindex corruption
bug, which is independent.

Refs: 013/Req-4, 013/Req-7, 010/Req-8 (independent), #678, #621

[dpsiderius]

Findings

Spike complete. Branch spike/014_embedding_api (pushed, no PR — throwaway per
convention). Run with make spike-014, bench with make spike-014-bench.

Verdict: GO on spec 013's Reqs 4 and 7, with three corrections to the spec
before it merges, and one ordering change.

The hypothesis was half right, and the half that failed is the useful part.

---

1. Value is !Send — Req 4's stated rationale is incomplete

Value::Text(Rc<str>) and Value::Blob(Rc<[u8]>)
(src/record/value.rs:15-17) make Value itself !Send.

Spec 013/Req-4 and ADR-0034 both locate the Send problem in the pager
(Rc<dyn PageSource>, Rc<RefCell<Pager>>) and conclude that a worker which
"creates its Rc graph on a thread it owns and never lets it leave" is
sufficient. It is not: a result row is a Vec<Value>, and rows are exactly
the thing that has to leave the worker.

The worker-thread strategy survives — but the boundary needs an owned
representation and pays one allocation-and-copy per text and blob value.
Integers, reals and nulls cross free, so the cost tracks text/blob payload
rather than row count. Neither the spec nor the ADR accounts for this today.

2. Streaming bounds peak heap at the page cache, not near zero

Rows read (one 1M-row DB, varied by LIMIT)	batch peak	stream peak	Ratio
400,000	62.6 MB	8.41 MB	7.4x
1,000,000	137.7 MB	8.68 MB	15.9x
scaling for 2.5x rows	2.20x	1.03x

Batch grows without bound. Streaming plateaus on the page-cache ceiling
(DEFAULT_PAGE_CACHE_CAPACITY = 2000 pages x 4096 B = 8.19 MB,
src/pager.rs:63).

Establishing this took three attempts and the two failures matter, because the
intermediate result looked like the hypothesis failing:

• Attempt 1 varied database size and result size together — the page cache
  grew for a reason unrelated to the API.
• Attempt 2 held the DB fixed and varied LIMIT; streaming's peak still
  scaled 4.92x for 5x rows. Cause: the 50,000-row fixture is 369 pages against
  a 2000-page capacity, so the whole DB fit and nothing ever evicted. Peak was
  tracking pages touched.
• Attempt 3 needed table leaf pages past 2000. A 300,000-row fixture is
  2,326 pages, but ~700 belong to the unused t_bucket index that a table scan
  never touches — still under the bound. 1,000,000 rows forced eviction and the
  plateau appeared.

Req 7's wording should change. "A large result is read without materializing
it" invites an acceptance test asserting near-zero growth, which would fail for
reasons that have nothing to do with the API. The accurate claim is bounded by
the page cache, independent of result size.

3. One row per channel message is unusable — and chunking removes the cost entirely

Full drain, 50,000 rows:

Prototype	Time	Throughput	vs batch
batch (control)	5.62 ms	8.90 Melem/s	1.00x
worker_batch	7.43 ms	6.73 Melem/s	1.32x
worker_stream (1 row/msg)	224.33 ms	222.9 Kelem/s	39.9x
worker_chunked/16	21.12 ms	2.37 Melem/s	3.76x
worker_chunked/64	11.09 ms	4.51 Melem/s	1.97x
worker_chunked/256	9.81 ms	5.10 Melem/s	1.75x
worker_chunked/1024	7.30 ms	6.85 Melem/s	1.30x
worker_chunked/4096	7.09 ms	7.05 Melem/s	1.26x

Time to first row, 50,000-row result:

Prototype	Time	vs batch
batch	5.36 ms	1x
worker_stream	44.7 µs	120x faster
worker_chunked/256	123.0 µs	44x faster
worker_chunked/1024	328.4 µs	16x faster

Per-row handoff costs ~4.5 µs. At chunk 1024 the full drain is 7.30 ms
against worker_batch's 7.43 ms — chunking overhead reaches zero, and the
residual 1.30x vs batch is the thread boundary plus finding 1's copies, not
the streaming. Chunk 4096 buys nothing more (inside 1024's CI).

The trade is mild: chunk 1024 still delivers row 0 in 328 µs vs batch's 5.36 ms.

Req 7 must require internal chunking, with the chunk size an implementation
detail rather than API surface. A literal one-row-per-message
Statement::next_row is 40x slower than today's API on a full scan — and the
correctness tests pass at 2,000 rows without a hint of it, so this would
otherwise be found late.

4. Req 7 needs an engine change; Req 4 does not

fn dispatch (src/vdbe/exec.rs:704) and fn run are private, and
ResultRow pushes into a Vec inside the Vm via emit_row. Streaming is
therefore not implementable as an external layer.

The minimal change is one public struct, +128/-49:

pub struct Execution<'p> { /* vm, program, pc, steps, done, pending */ }
impl<'p> Execution<'p> {
    pub fn new(vm: Vm, program: &'p Program) -> Self;
    pub fn next_row(&mut self) -> Result<Option<Vec<Value>>, ExecError>;
    pub fn autocommit(&self) -> bool;
}

run() is rewritten as a wrapper collecting next_row into the same Vec it
always returned, so batch and streaming are literally the same loop rather than
two paths that can drift. Additivity measured: cargo test --locked gives
1562 passed / 0 failed before and after, every test binary reporting identical
counts; clippy, cargo fmt --check and check-mod-files clean.

One trap found by reading emit_row's callers rather than by a failing test:
pragma::integrity_check emits N rows from a single dispatch, so a drain
assuming one row per step (popping off the back) would silently reverse
PRAGMA integrity_check output. Execution uses a FIFO because of it.

5. Compile-time proof, not prose

const _: () = assert_send_sync::<Connection>(); — Req 4's claim fails the
build if it stops holding. The Arc-shared-across-four-threads,
drop-releases-worker and abandoned-stream-reuse scenarios all pass (9 tests).

---

Ordering consequence

Req 7 should land before Reqs 2/3. Statement::next_row has to sit on the
streaming primitive; building the facade on today's materializing
execute_with_db and retrofitting streaming afterwards means rewriting it.

Scope notes

• worker_chunked was not in this issue's design. It was added after
  worker_stream measured 40x slower than the control, rather than shipping
  "streaming is slow" as the finding.
• Req 1 (rows-affected) was out of scope and not reached. The static finding
  stands: no such counter exists anywhere in src/.
• The src/ patch on the spike branch is evidence, not a deliverable. The
  shippable version lands via its own ticket with its own tests and ADR.
• Fixtures deliberately avoid composite PRIMARY KEY, so this spike does not
  measure the sqlite_autoindex_* corruption of spec 010/Req-8 — which remains
  independent and higher-priority.
• Noted in passing, not fixed: spikes 010-013 have no root Makefile targets;
  the convention lapsed after 009. spike-014 follows 001-009.

Spend: matched the medium estimate, plus one unplanned prototype
(worker_chunked) and two discarded memory experiments.