User story
As a release maintainer, I want the staged-changes diff job to keep its memory footprint bounded so that large but legitimate CIEL diffs do not pressure the shared worker host.
Use case
The nightly refresh_snapshot job compares CIEL HEAD against the latest released version. To find each HEAD concept's counterpart it loads the entire released export into an in-memory dict keyed by id, which grows linearly with CIEL and has no ceiling.
Requirements
Acceptance criteria
More details (collapsible)
Measurements, rationale and priority
Context
Measured on 2026-08-24 in the dev environment, sampling real concepts from the Valkey HEAD cache and recording RSS deltas.
HEAD holds 58,773 concepts averaging ~3.3 KB of JSON each, roughly 188 MiB as raw JSON but ~440 MiB once parsed into Python dicts, an inflation of about 2.3x. The released export is the same order of magnitude, so the released side alone accounts for roughly 440 MiB. The worker currently sits at 1.83 GiB RSS with no memory limit configured, on a 47 GiB host that already has ~24.7 GiB in use by containers, including a 4.2 GiB Elasticsearch from the oclapi2 stack.
Notes on the storage decision
Random access by id, benchmarked over 20,000 concepts: the current dict costs 150 MiB of RAM and serves 2,973,418 lookups per second; an ephemeral SQLite index costs 0 MiB of RAM and serves 80,393 lookups per second. SQLite is about 37x slower per lookup, but across the whole job that is roughly 0.7 seconds added, against a saving of about 440 MiB.
Sequential append and full scan, same sample: NDJSON writes in 0.09s, completes two full scans in 0.40s and occupies 63 MiB on disk; SQLite writes in 0.46s, scans in 0.42s and occupies 95 MiB. The B-tree and PRIMARY KEY index are pure overhead when nothing is ever looked up by key.
The resulting rule for this codebase is that sequential access uses NDJSON and random access by key uses ephemeral SQLite. The two should not be unified.
Plain NDJSON is not viable for the released side. Without a key index each lookup means scanning the whole file, which is 58,773 lookups against 58,773 lines. At roughly 0.5s per full scan that is on the order of 8 hours, O(n squared) by construction.
Reference
Relevant lines in api/src/services/ocl_staged_changes_service.py: the released export load and split at 755-775, the never-freed released_payloads at 755, the cold-storage ZIP read at 551 inside _load_head_full_mappings starting at 522, and the HEAD mapping index at 788.
sqlite3.connect("") creates a private on-disk temporary database that SQLite deletes automatically when the connection closes, verified in this environment. An explicit path under _HEAD_SNAPSHOT_DIR is preferred anyway so a stuck job leaves something inspectable.
Suggested priority
Peak-RSS logging and the worker memory limit come first, since they provide the before and after baseline. The one-line del released_payloads is next at essentially zero risk. The SQLite index is the main win and justifies itself on the measured numbers. The cold-storage ZIP streaming should be reassessed once the index lands and peaks are visible.
Excluded
The granular diff row spill is already shipped and is not part of this issue. _DiffAccumulator now writes rows to a temporary JSONL file and inserts them in batches of 5,000, which removed the 50,000-item cap that had been silently truncating legitimate diffs and causing the Concepts and Mappings sections to render "No items." on a snapshot reporting 59,580 created and 553 updated mappings.
User story
As a release maintainer, I want the staged-changes diff job to keep its memory footprint bounded so that large but legitimate CIEL diffs do not pressure the shared worker host.
Use case
The nightly
refresh_snapshotjob compares CIEL HEAD against the latest released version. To find each HEAD concept's counterpart it loads the entire released export into an in-memory dict keyed by id, which grows linearly with CIEL and has no ceiling.Requirements
refresh_snapshotrun.ciellab-workerservice in compose.released_payloadsimmediately after the split into id-keyed structures.released_concepts_by_idandreleased_mappings_by_idwith an ephemeral SQLite index.get(id)._HEAD_SNAPSHOT_DIR.head_full_mappings_by_idshould stream the export ZIP from cold storage or build the mapping index lazily for changed ids only.Acceptance criteria
refresh_snapshotrun, when it completes, then peak RSS is logged and measurably lower than the pre-change baseline.stats_jsonand the totalocl_staged_change_itemscount are identical.More details (collapsible)
Measurements, rationale and priority
Context
Measured on 2026-08-24 in the dev environment, sampling real concepts from the Valkey HEAD cache and recording RSS deltas.
HEAD holds 58,773 concepts averaging ~3.3 KB of JSON each, roughly 188 MiB as raw JSON but ~440 MiB once parsed into Python dicts, an inflation of about 2.3x. The released export is the same order of magnitude, so the released side alone accounts for roughly 440 MiB. The worker currently sits at 1.83 GiB RSS with no memory limit configured, on a 47 GiB host that already has ~24.7 GiB in use by containers, including a 4.2 GiB Elasticsearch from the oclapi2 stack.
Notes on the storage decision
Random access by id, benchmarked over 20,000 concepts: the current dict costs 150 MiB of RAM and serves 2,973,418 lookups per second; an ephemeral SQLite index costs 0 MiB of RAM and serves 80,393 lookups per second. SQLite is about 37x slower per lookup, but across the whole job that is roughly 0.7 seconds added, against a saving of about 440 MiB.
Sequential append and full scan, same sample: NDJSON writes in 0.09s, completes two full scans in 0.40s and occupies 63 MiB on disk; SQLite writes in 0.46s, scans in 0.42s and occupies 95 MiB. The B-tree and PRIMARY KEY index are pure overhead when nothing is ever looked up by key.
The resulting rule for this codebase is that sequential access uses NDJSON and random access by key uses ephemeral SQLite. The two should not be unified.
Plain NDJSON is not viable for the released side. Without a key index each lookup means scanning the whole file, which is 58,773 lookups against 58,773 lines. At roughly 0.5s per full scan that is on the order of 8 hours, O(n squared) by construction.
Reference
Relevant lines in api/src/services/ocl_staged_changes_service.py: the released export load and split at 755-775, the never-freed
released_payloadsat 755, the cold-storage ZIP read at 551 inside_load_head_full_mappingsstarting at 522, and the HEAD mapping index at 788.sqlite3.connect("")creates a private on-disk temporary database that SQLite deletes automatically when the connection closes, verified in this environment. An explicit path under_HEAD_SNAPSHOT_DIRis preferred anyway so a stuck job leaves something inspectable.Suggested priority
Peak-RSS logging and the worker memory limit come first, since they provide the before and after baseline. The one-line
del released_payloadsis next at essentially zero risk. The SQLite index is the main win and justifies itself on the measured numbers. The cold-storage ZIP streaming should be reassessed once the index lands and peaks are visible.Excluded
The granular diff row spill is already shipped and is not part of this issue.
_DiffAccumulatornow writes rows to a temporary JSONL file and inserts them in batches of 5,000, which removed the 50,000-item cap that had been silently truncating legitimate diffs and causing the Concepts and Mappings sections to render "No items." on a snapshot reporting 59,580 created and 553 updated mappings.