perf(trajectory): connect once per batch, not once per record #143

Merged

coilysiren merged 1 commit from task/view-rebuild-connection-churn into main

2026-08-19 15:33:56 +00:00

coilyco-ops commented

2026-08-19 15:32:36 +00:00

Owner

Refs #142, which carries the remaining structural half.

The problem

save_all on both derived trajectory stores was tuple(self.save(r) for r in records), and every save opened its own SQLite connection. _operational_builder() rebuilds the whole retained ledger through those calls on every request to /v1/trajectory/views/{view_name} and /v1/trajectory/dossiers/{trajectory_id}, so connection setup sat on the critical path of a read endpoint and scaled with ledger size.

Measurements

Synthetic ledger of request-lifecycle event pairs, timed in the steady state where nothing has changed and every write is a no-op.

	before	after
`MaterializationStore.save_all`, 2000 turns	281.6 ms	63.0 ms
full `_operational_builder()`, 2000 turns	617.3 ms	405.3 ms

I had assumed BEGIN IMMEDIATE was the expensive part and was wrong. A microbenchmark isolating the two candidates over 2000 records:

connection plus BEGIN IMMEDIATE each - 158.6 ms
connection each, write lock removed - 151.4 ms
one shared connection - 5.2 ms

Connect is the entire cost. The write lock is roughly 7 ms across the whole batch. So the fix is connection reuse, and leaving the per-record transaction exactly as it was.

What is preserved

Per-record semantics are untouched: each record still gets its own BEGIN IMMEDIATE, the same digest dedupe, and the same insert. with connection commits without closing, which leaves the next BEGIN IMMEDIATE a clean start. save keeps its signature and now closes the connection it opens, which the previous with self._connect() never did (the sqlite3 connection context manager commits, it does not close).

Verification

tests/test_trajectory_store_batching.py, 3 tests. Mutation-checked: restoring the per-record connect fails the suite. The dedupe test drives five consecutive no-op saves over one shared connection, which is the case the transaction reuse could plausibly break.
Full suite 410 passed. just format-check, just lint, just typecheck, and pre-commit run --all-files clean.

What this does not fix

The growth curve. Read cost stays linear in ledger size at roughly 0.2 ms per retained turn, because the rebuild still reads and re-materializes everything. Fixing that means choosing between a staleness window, incremental materialization, or cached views, which is a design decision rather than a mechanical one. Filed with numbers and options as #142.

🤖 Generated with Claude Code

Refs #142, which carries the remaining structural half. ## The problem `save_all` on both derived trajectory stores was `tuple(self.save(r) for r in records)`, and every `save` opened its own SQLite connection. `_operational_builder()` rebuilds the whole retained ledger through those calls on **every** request to `/v1/trajectory/views/{view_name}` and `/v1/trajectory/dossiers/{trajectory_id}`, so connection setup sat on the critical path of a read endpoint and scaled with ledger size. ## Measurements Synthetic ledger of request-lifecycle event pairs, timed in the steady state where nothing has changed and every write is a no-op. | | before | after | |---|---|---| | `MaterializationStore.save_all`, 2000 turns | 281.6 ms | 63.0 ms | | full `_operational_builder()`, 2000 turns | 617.3 ms | 405.3 ms | I had assumed `BEGIN IMMEDIATE` was the expensive part and was wrong. A microbenchmark isolating the two candidates over 2000 records: * connection plus `BEGIN IMMEDIATE` each - 158.6 ms * connection each, write lock removed - 151.4 ms * one shared connection - 5.2 ms Connect is the entire cost. The write lock is roughly 7 ms across the whole batch. So the fix is connection reuse, and leaving the per-record transaction exactly as it was. ## What is preserved Per-record semantics are untouched: each record still gets its own `BEGIN IMMEDIATE`, the same digest dedupe, and the same insert. `with connection` commits without closing, which leaves the next `BEGIN IMMEDIATE` a clean start. `save` keeps its signature and now **closes** the connection it opens, which the previous `with self._connect()` never did (the sqlite3 connection context manager commits, it does not close). ## Verification * `tests/test_trajectory_store_batching.py`, 3 tests. Mutation-checked: restoring the per-record connect fails the suite. The dedupe test drives five consecutive no-op saves over one shared connection, which is the case the transaction reuse could plausibly break. * Full suite 410 passed. `just format-check`, `just lint`, `just typecheck`, and `pre-commit run --all-files` clean. ## What this does not fix The growth curve. Read cost stays linear in ledger size at roughly 0.2 ms per retained turn, because the rebuild still reads and re-materializes everything. Fixing that means choosing between a staleness window, incremental materialization, or cached views, which is a design decision rather than a mechanical one. Filed with numbers and options as #142. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

coilyco-ops added 1 commit

2026-08-19 15:32:36 +00:00

perf(trajectory): connect once per batch, not once per record

ci / smoke (pull_request) Successful in 10s

Details

ci / quality (pull_request) Successful in 34s

Details

5646dd00a0

`save_all` on both derived stores was `tuple(self.save(r) for r in records)`,
and every `save` opened its own SQLite connection. Every operational view and
dossier request rebuilds the whole retained ledger through those calls, so
connection setup sat on the critical path of a read endpoint and scaled with
the ledger.

Measured on a synthetic 2000-turn ledger in the steady state, where nothing
has changed and every write is a no-op:

  MaterializationStore.save_all   281.6 ms  ->  63.0 ms
  full _operational_builder()     617.3 ms  ->  405.3 ms

A microbenchmark isolating the two candidates put the blame squarely on
connect rather than on the write lock: 2000 records cost 158.6 ms with a
connection plus BEGIN IMMEDIATE each, 151.4 ms with the write lock removed,
and 5.2 ms over one shared connection.

Per-record semantics are untouched. Each record still gets its own
BEGIN IMMEDIATE, the same digest dedupe, and the same insert, because
`with connection` commits without closing and leaves the next BEGIN a clean
start. `save` keeps its signature and now closes the connection it opens,
which the previous `with self._connect()` never did.

This removes the largest constant factor. It does not change the growth
curve, which stays linear in ledger size and is filed as #142.

Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
Co-authored-by: Kai Siren <coilysiren@gmail.com>
Co-authored-by: Claude <noreply@anthropic.com>

coilyco-ops requested review from coilysiren

2026-08-19 15:32:36 +00:00

coilyco-ops referenced this pull request

2026-08-19 15:32:48 +00:00

Operational views and dossiers rebuild the whole ledger on every request, so read cost grows without bound #142