Problem
Every artifact metadata mutation rewrites the entire artifact_records table, so a store with N records pays O(N) JSON + SQL + hashing work per mutation — and startup performs M serialized mutations (session-retirement purges, recovery adoptions), making cold start O(M×N).
Mechanism in packages/storage/src/artifact-store.ts + sqlite-artifact-metadata.ts:
- Every mutation begins with
reloadForMutationUnlocked() → metadataRepository.readAll(): SELECT record_json FROM artifact_records + decodeArtifactRecordJsons() over all N rows.
- Every mutation ends with
metadataRepository.replaceAll(records): DELETE FROM artifact_records, then re-INSERT all N rows, each with a fresh JSON.stringify(record) and a fresh sha256 artifactIdentityKey(record.id).
Measured impact
On a real store with ~11.7k artifact records and hundreds of accumulated Sessions (details in this #4027 comment), a windowed CPU profile of Runtime Host cold start shows the first ~90 s of the ~130 s total saturated by exactly this churn: ~34% of samples in the per-record INSERT, ~11% in the DELETE, ~6.5% in JSON decode of the full read-back, ~5.4% in the SELECT ... all(), and ~5.6% in sha256 (identity keys are re-hashed for every record on every rewrite). After ~105 s the Host is idle; the metadata phase is the residual cold-start bottleneck.
Steady state pays the same O(N) cost on every single-artifact mutation (create / purge / adopt), just without the M multiplier.
Proposed direction
Change-tracked write-back in replaceAll's place: within the same single write transaction, apply only the delta the mutation actually made — targeted INSERT ... ON CONFLICT(storage_key) DO UPDATE / DELETE WHERE storage_key IN (...) — instead of deleting and re-inserting the full snapshot. Additionally, compute the sha256 artifactIdentityKey once per record (it is a pure function of the record id) rather than once per record per rewrite.
Combined with a batched multi-Session purge (separate proposal), this reduces a retirement batch of M Sessions from M full-table rewrites to one O(changed) commit.
Questions for maintainers
- Crash-atomicity contract: today
replaceAll persists the post-mutation snapshot as one atomic transaction. Targeted writes would run inside the same single write transaction, so atomicity should be equivalent — is there any consumer that relies on the table being a full rewrite (e.g. for recovery or consistency checking) rather than just "the persisted state after transaction commit"?
- Source of truth: the in-memory record map is authoritative during a mutation and the table is its persistence. Any objection to keeping that model with delta persistence, or is a deeper redesign (e.g. querying SQLite instead of holding the map) preferred?
- Is a prototype welcome once the direction is confirmed?
Related: #4027 (cold-start investigation), #4031 (bounded purge resolution; review thread identified the O(M×N) structure).
This issue was prepared with AI assistance (Kimi k3-256k), including profiling and analysis.
Problem
Every artifact metadata mutation rewrites the entire
artifact_recordstable, so a store with N records pays O(N) JSON + SQL + hashing work per mutation — and startup performs M serialized mutations (session-retirement purges, recovery adoptions), making cold start O(M×N).Mechanism in
packages/storage/src/artifact-store.ts+sqlite-artifact-metadata.ts:reloadForMutationUnlocked()→metadataRepository.readAll():SELECT record_json FROM artifact_records+decodeArtifactRecordJsons()over all N rows.metadataRepository.replaceAll(records):DELETE FROM artifact_records, then re-INSERT all N rows, each with a freshJSON.stringify(record)and a fresh sha256artifactIdentityKey(record.id).Measured impact
On a real store with ~11.7k artifact records and hundreds of accumulated Sessions (details in this #4027 comment), a windowed CPU profile of Runtime Host cold start shows the first ~90 s of the ~130 s total saturated by exactly this churn: ~34% of samples in the per-record INSERT, ~11% in the
DELETE, ~6.5% in JSON decode of the full read-back, ~5.4% in theSELECT ... all(), and ~5.6% in sha256 (identity keys are re-hashed for every record on every rewrite). After ~105 s the Host is idle; the metadata phase is the residual cold-start bottleneck.Steady state pays the same O(N) cost on every single-artifact mutation (create / purge / adopt), just without the M multiplier.
Proposed direction
Change-tracked write-back in
replaceAll's place: within the same single write transaction, apply only the delta the mutation actually made — targetedINSERT ... ON CONFLICT(storage_key) DO UPDATE/DELETE WHERE storage_key IN (...)— instead of deleting and re-inserting the full snapshot. Additionally, compute the sha256artifactIdentityKeyonce per record (it is a pure function of the record id) rather than once per record per rewrite.Combined with a batched multi-Session purge (separate proposal), this reduces a retirement batch of M Sessions from M full-table rewrites to one O(changed) commit.
Questions for maintainers
replaceAllpersists the post-mutation snapshot as one atomic transaction. Targeted writes would run inside the same single write transaction, so atomicity should be equivalent — is there any consumer that relies on the table being a full rewrite (e.g. for recovery or consistency checking) rather than just "the persisted state after transaction commit"?Related: #4027 (cold-start investigation), #4031 (bounded purge resolution; review thread identified the O(M×N) structure).
This issue was prepared with AI assistance (Kimi k3-256k), including profiling and analysis.