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

coilyco-ops commented

2026-08-19 15:31:01 +00:00

Owner

What

_operational_builder() in app/trajectory/api.py runs on every request to /v1/trajectory/views/{view_name} and /v1/trajectory/dossiers/{trajectory_id}. Each call:

re-materializes every retained event (materialize_retained_events, which itself reads the full events table)
reads the full events table a second time for events = tuple(raw.iter_events())
re-assembles every evaluation record
writes both result sets back through save_all

A dossier request for one trajectory_id therefore pays for the entire ledger, then filters to one row at the end. The events table is append-only, so the cost of every read grows with everything the service has ever retained.

Numbers

Measured on a synthetic ledger of request-lifecycle event pairs, steady state (already materialized, so nothing actually changed). Times are one _operational_builder() call.

200 turns - 36 ms
500 turns - 136 ms
1000 turns - 247 ms
2000 turns - 405 ms

Growth is linear at roughly 0.2 ms per retained turn. These are post-fix figures, with the per-record connect from the PR below already removed. Before that fix the same points were 54 / 146 / 293 / 617 ms.

Extrapolating that slope past what was measured, and taking issue #140's observed rate of about 375 completions a day:

one day retained - about 75 ms per request
one month - about 2.2 s per request
one year - about 28 s per request

Two things make it worse than the wall-clock suggests

It writes on a read. MaterializationStore.save_all takes BEGIN IMMEDIATE per record. The write lock is cheap in isolation (measured at about 7 ms across 2000 records) but it serializes against the hot-path trajectory emitter, which connects with timeout=30. A large view rebuild can stall ingest.

The dossier endpoint needs almost none of it. OperationalViewBuilder.dossier uses exactly one materialized trajectory plus that trajectory's evaluations. MaterializationStore.latest(trajectory_id) and EvaluationStore.for_trajectory(trajectory_id) already exist and are both index-backed.

Why this is not just a patch

The rebuild is what makes a view reflect events ingested since the last request. Reading only stored materializations would be O(1) for a dossier but could serve a result that misses a just-ingested event. That is a freshness-versus-cost decision, not a mechanical fix, which is why it is filed rather than folded into the connection-churn PR.

Roughly, the options:

Accept a staleness window. Serve dossiers straight from latest plus for_trajectory, and rebuild on a timer or on ingest rather than on read. Cheapest to build, and it changes what a read promises.
Materialize incrementally. Advance from a stored watermark and touch only trajectories with new events. Keeps read-your-writes, and needs a trajectory_id index on events plus a filtered iter_events, neither of which exists today.
Cache the built views with invalidation on ingest. Keeps freshness for the view endpoints, does nothing for a cold first request after a write.

Reproducing

Ingest N request-lifecycle event pairs into a TrajectoryStore, call _operational_builder() once to warm it, then time a second call. The second call does no useful work and still costs the full amount.

Connection-churn fix, which removes the largest constant factor but not the growth: the pull request referenced below.
#140 for the trace-volume numbers this extrapolation uses.

## What `_operational_builder()` in [`app/trajectory/api.py`](app/trajectory/api.py) runs on **every** request to `/v1/trajectory/views/{view_name}` and `/v1/trajectory/dossiers/{trajectory_id}`. Each call: * re-materializes every retained event (`materialize_retained_events`, which itself reads the full events table) * reads the full events table a second time for `events = tuple(raw.iter_events())` * re-assembles every evaluation record * writes both result sets back through `save_all` A dossier request for **one** `trajectory_id` therefore pays for the entire ledger, then filters to one row at the end. The events table is append-only, so the cost of every read grows with everything the service has ever retained. ## Numbers Measured on a synthetic ledger of request-lifecycle event pairs, steady state (already materialized, so nothing actually changed). Times are one `_operational_builder()` call. * 200 turns - 36 ms * 500 turns - 136 ms * 1000 turns - 247 ms * 2000 turns - 405 ms Growth is linear at roughly **0.2 ms per retained turn**. These are post-fix figures, with the per-record connect from the PR below already removed. Before that fix the same points were 54 / 146 / 293 / 617 ms. Extrapolating that slope past what was measured, and taking issue #140's observed rate of about 375 completions a day: * one day retained - about 75 ms per request * one month - about 2.2 s per request * one year - about 28 s per request ## Two things make it worse than the wall-clock suggests **It writes on a read.** `MaterializationStore.save_all` takes `BEGIN IMMEDIATE` per record. The write lock is cheap in isolation (measured at about 7 ms across 2000 records) but it serializes against the hot-path trajectory emitter, which connects with `timeout=30`. A large view rebuild can stall ingest. **The dossier endpoint needs almost none of it.** `OperationalViewBuilder.dossier` uses exactly one materialized trajectory plus that trajectory's evaluations. `MaterializationStore.latest(trajectory_id)` and `EvaluationStore.for_trajectory(trajectory_id)` already exist and are both index-backed. ## Why this is not just a patch The rebuild is what makes a view reflect events ingested since the last request. Reading only stored materializations would be O(1) for a dossier but could serve a result that misses a just-ingested event. That is a freshness-versus-cost decision, not a mechanical fix, which is why it is filed rather than folded into the connection-churn PR. Roughly, the options: * **Accept a staleness window.** Serve dossiers straight from `latest` plus `for_trajectory`, and rebuild on a timer or on ingest rather than on read. Cheapest to build, and it changes what a read promises. * **Materialize incrementally.** Advance from a stored watermark and touch only trajectories with new events. Keeps read-your-writes, and needs a `trajectory_id` index on `events` plus a filtered `iter_events`, neither of which exists today. * **Cache the built views** with invalidation on ingest. Keeps freshness for the view endpoints, does nothing for a cold first request after a write. ## Reproducing Ingest N request-lifecycle event pairs into a `TrajectoryStore`, call `_operational_builder()` once to warm it, then time a second call. The second call does no useful work and still costs the full amount. ## Related * Connection-churn fix, which removes the largest constant factor but not the growth: the pull request referenced below. * #140 for the trace-volume numbers this extrapolation uses.

coilyco-ops referenced this issue from a commit

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

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

coilyco-ops referenced this issue

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

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

coilyco-ops commented

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

Author

Owner

The connection-churn half is now open as #143. It removes the largest constant factor (save_all at 2000 turns goes 281.6 ms to 63.0 ms, the full rebuild 617.3 ms to 405.3 ms) and deliberately leaves the growth curve alone, because that is the part needing the freshness-versus-cost decision described above.

The post-fix numbers in this issue's body were measured against that branch, so they are what remains after #143 lands.

The connection-churn half is now open as #143. It removes the largest constant factor (`save_all` at 2000 turns goes 281.6 ms to 63.0 ms, the full rebuild 617.3 ms to 405.3 ms) and deliberately leaves the growth curve alone, because that is the part needing the freshness-versus-cost decision described above. The post-fix numbers in this issue's body were measured against that branch, so they are what remains after #143 lands.

coilyco-ops referenced this issue from a commit

2026-08-19 15:45:21 +00:00

perf(trajectory): read the ledger once per view request, not twice

coilyco-ops referenced this issue

2026-08-19 15:45:39 +00:00

perf(trajectory): read the ledger once per view request, not twice #144

coilyco-ops commented

2026-08-19 15:45:52 +00:00

Author

Owner

#144 takes the second half that needed no decision: the builder read the whole events table twice per request, once inside materialize_retained_events and again for the evaluation half.

That second read was also a latent correctness problem. The two calls are separate SQLite snapshots, so an event ingested between them appeared in the evaluation half and not the materialized half, letting one request build its view from two different ledgers. Passing one snapshot to both removes it by construction.

Combined with #143, a 2000-turn rebuild is down from 617.3 ms to 262.3 ms:

500 turns - 109.6 ms to 60.0 ms
1000 turns - 186.8 ms to 121.9 ms
2000 turns - 369.0 ms to 262.3 ms

Still open here: the growth curve. Read cost stays linear in ledger size because the rebuild still materializes everything on every request, and closing that changes what a read promises. The three options in the body above are unchanged, and the choice between them is still the blocker.

#144 takes the second half that needed no decision: the builder read the whole events table twice per request, once inside `materialize_retained_events` and again for the evaluation half. That second read was also a latent correctness problem. The two calls are separate SQLite snapshots, so an event ingested between them appeared in the evaluation half and not the materialized half, letting one request build its view from two different ledgers. Passing one snapshot to both removes it by construction. Combined with #143, a 2000-turn rebuild is down from 617.3 ms to 262.3 ms: * 500 turns - 109.6 ms to 60.0 ms * 1000 turns - 186.8 ms to 121.9 ms * 2000 turns - 369.0 ms to 262.3 ms **Still open here:** the growth curve. Read cost stays linear in ledger size because the rebuild still materializes everything on every request, and closing that changes what a read promises. The three options in the body above are unchanged, and the choice between them is still the blocker.

coilyco-ops referenced this issue

2026-08-19 15:53:16 +00:00

Decide how operational views stay cheap as the ledger grows: staleness, incremental, or cache #145

coilyco-ops commented

2026-08-19 15:53:30 +00:00

Author

Owner

Superseded by #145 and closing.

Two of the three things described here landed: #143 (a SQLite connect per record) and #144 (a second full read of the events table). Together they took a 2000-turn rebuild from 617 ms to 297 ms.

What remains is the growth curve, which is a decision rather than a defect, so it moved to #145 on its own. That issue also corrects two things stated here:

the growth is mildly superlinear, not linear at 0.2 ms per turn. Per-turn cost climbs from 0.130 ms at 500 turns to 0.188 ms at 8000, roughly O(n^1.15). This body only measured to 2000.
incremental materialization was listed here as a straightforward option. It is the most invasive of the three, because trajectory_id is derived by unioning correlations rather than stored, so there is no event-to-trajectory mapping to index.

The numbers in the body above are stale and #145 carries fresh ones measured on 4fa0bbe.

Superseded by #145 and closing. Two of the three things described here landed: #143 (a SQLite connect per record) and #144 (a second full read of the events table). Together they took a 2000-turn rebuild from 617 ms to 297 ms. What remains is the growth curve, which is a decision rather than a defect, so it moved to #145 on its own. That issue also corrects two things stated here: * the growth is mildly superlinear, not linear at 0.2 ms per turn. Per-turn cost climbs from 0.130 ms at 500 turns to 0.188 ms at 8000, roughly O(n^1.15). This body only measured to 2000. * incremental materialization was listed here as a straightforward option. It is the most invasive of the three, because `trajectory_id` is derived by unioning correlations rather than stored, so there is no event-to-trajectory mapping to index. The numbers in the body above are stale and #145 carries fresh ones measured on 4fa0bbe.

coilyco-ops closed this issue

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

Rows
Columns

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

What

Numbers

Two things make it worse than the wall-clock suggests

Why this is not just a patch

Reproducing

Related