Two Gigabytes Becomes Thirty Megabytes

How the report engine stopped holding an entire stock universe in RAM — by parking every symbol in a hybrid blob-and-MySQL build cache the moment it arrives, and reading back only what each renderer actually needs

The Problem, Measured on a Real Run

A Russell 2000 report covers 1,938 symbols. The report engine used to download every symbol's complete financial history — statements, prices, metrics, analyst coverage — into one dictionary and hold all of it for the entire 35-minute run, because a dozen downstream consumers each expected to iterate the whole thing.

Measure	Value
Per-symbol raw record, serialized	538 KB mean
Total serialized symbol data in one run	1,019 MB
Live .NET object graph (UTF-16 strings, headers, list slack)	~2–3 GB
Report VM total RAM	3.9 GB
Swap configured	none

One large report needed most of the machine to itself. The goal was stated in one sentence: hold in RAM only what is being worked on at this moment, and let everything else live somewhere cheaper.

The Two Observations That Shaped the Design

First: the data was already being externalized. The engine writes every symbol's raw JSON to blob storage as a product feature — users can download it. The platform was paying to persist a gigabyte of data and holding the same gigabyte in memory. The bytes were never the problem; holding them all simultaneously was.

Second: almost nothing needs every symbol at full fidelity at once. Of the twelve passes consumers made over that dictionary, the dashboard needs a ~2 KB summary row per symbol; the statistics workbooks need nine small computed rows per symbol; the ML forecasters process one symbol at a time by construction. Only the sort — which company grew fastest — is genuinely cross-symbol, and you can sort 2 KB rows without holding 500 KB records.

ARCHITECTURE

rendering diagram…

flowchart TB
    subgraph INGEST[Ingest - 10 symbols in flight, then DROPPED from RAM]
      F[Fetch symbol<br/>12 provider calls]
      P[Compute projections<br/>summary row, stats rows,<br/>quarterly cash flows]
    end
    BLOB[(Blob storage<br/>fat records - the raw_data<br/>files users download anyway)]
    SQL[(MySQL<br/>projections ~15 MB/job<br/>+ build bookkeeping, 24h TTL)]
    F --> P
    F -- full record, once --> BLOB
    P -- small rows --> SQL
    subgraph RENDER[Report phases - each reads only its shape]
      G[Stats workbooks + HTML + JSON<br/>read sorted rows]
      M[ML forecasting<br/>one fat record at a time]
      I[Individual reports<br/>one fat record at a time]
      D[Dashboard summary<br/>reads summary rows]
    end
    SQL --> G
    SQL --> D
    BLOB --> M
    BLOB --> I

Ingest stores each symbol the moment it arrives — the fat record to blob storage (where it was already going), the small projections to MySQL — then drops it from memory. Every later phase reads back only its own shape of the data.

Blob or MySQL? Measured, Not Argued

The store could have lived entirely in either system, so both were benchmarked from the VM that runs the engine — not estimated:

Operation	Latency	What it means
MySQL round trip	~0.5 ms	batched small reads are effectively free
MySQL keyed read, 71 KB	~2.3 ms	12× faster than blob per read
Blob GET, 607 KB	~28 ms flat	latency-bound: the 607 KB transfer added ~3 ms

Neither number dominates a 35-minute job, so the decision turned on shape, not speed. Blob is terrible at the ~21,000 tiny projection reads a build makes (each pays the full 28 ms); MySQL is the wrong place for 130 MB per job of large-object churn on the same burstable server that runs the live application. And the decisive fact: the fat records were already in blob storage. A blob-backed fat store required zero additional writes.

So the answer is a hybrid: fat records in blob (uploaded once at ingest, read back one at a time), projections and build bookkeeping in MySQL (~15 MB per job, batched reads, deleted at job end with a TTL sweeper behind it). Each store does the half it is good at.

Where the Time Goes

Externalizing a gigabyte mid-job sounds expensive; measured against the job, it is noise. The ingest writes cost nothing user-visible at all — the blob upload and projection inserts happen while the next symbol's paid API fetches are already in flight, so they hide entirely inside time the engine was spending anyway.

The reads are the only genuinely new work. The two statistics-workbook passes each re-read every fat record — 1,938 symbols twice over at ~28 ms per blob GET — but at 8-way parallelism that is ~15 seconds total. The ~21,000 tiny projection reads arrive in batches and cost seconds. Net added wall clock: under a minute on a ~35-minute job — the price of giving back roughly two gigabytes of RAM.

The Refactor Paid a Second Dividend

Before this work, the statistics math existed three times: once in the Excel writer, once in the HTML writer, once in the JSON builder — with a comment begging future developers to keep all three in sync, because a divergence would mean the dashboard showing different numbers than the spreadsheet the user downloads.

Computing each symbol's rows once at ingest — while the fat record is in hand anyway — and having all three renderers consume the same assembled row bundles retired that hazard outright. The Excel sheet, the HTML table, and the dashboard JSON now render literally the same objects. The sync comment was deleted, not updated.

Failure Degrades to the Old Behavior, Never to a Broken Report

The dictionary was not deleted — it was demoted to a fallback. When the build store accepts a symbol, the symbol is dropped from memory; when it cannot (table missing, transient database error), that symbol stays resident and every consumer transparently reads it from the fallback first. A run with a dead build cache is exactly the old engine: heavy, but correct. A run with a healthy one is 98% lighter.

Missing projection at assembly time? Recomputed from the fat record on the spot — a symbol is never dropped from a report because a cache row went missing.
Crashed job? Projections carry a 24-hour TTL; the next run's sweeper collects the orphans.
Successful job? Evicts its own projections on the way out. The fat blobs stay — they were user-facing artifacts all along.

The Expected Outcome

	Before	After
Peak RAM, 2,000-symbol job	~2–3 GB	~200–350 MB
Concurrent large jobs on one small VM	1 (marginal)	4–6
Added wall clock	—	under a minute
New infrastructure purchased	—	none

The refactor shipped with the full suite green — 1,227 automated tests across three projects — and the end-to-end engine test now runs through the cache path, storing at ingest and reading back per phase, rather than around it.

What Comes Later

Everything above sits behind one interface, IReportBuildStore, and that seam is not decorative: a Redis stage is already designed behind it. The keys mirror the MySQL schema, every entry carries a TTL so a crashed job self-cleans, and the plan deliberately shares one instance with the provider-cache Redis mirror — neither workload alone justifies a new monthly SKU, but at the ~1,000-user mark the two together do, so if Redis is ever provisioned both land at once.

Until that day, swapping stores is a registration change, not a rewrite. That is the same decorator discipline that let the provider cache serve five different consumers from one implementation: put the seam in before you need it, and scaling becomes configuration.