Hard Lessons: Scaling our AI powered Spreadsheet tool

For the past two years I’ve been working with a team building AI powered RFP tools for a few clients we have. They’ve been happily using our product for months now- especially the spreadsheet features I’ve helped build. However, last week they contacted us and told us that one of the spreadsheets they uploaded to the site mysteriously doesn’t work anymore. And it failed in a really weird way which led the team down a rabbit hole of performance optimizations, process failures, and heap memory exploration that taught us lessons that will stick with us going forward. I have to thank my amazing seniors Alfred Wong and Tommy Trinh for doing a lot of the investigation in the pods that allowed us to finally fix this problem. In this article, I will be sharing those learnings with you.

Silent Pod Deaths

We began our investigation by checking our Google Cloud Platform (GCP) console for error logs. We noticed that when we attempted to generate data for our spreadsheets, our API pods were dying and left nothing behind. No stack traces or other warnings. Our dashboard showed memory spiking to 9 GiB, then it flatlines as the container restarts. Then the next request would run like nothing happened.

Eventually we figured out something. When a Node process hits its own memory limit, V8 always logs an error before dying. However, if a Node process just vanishes without saying a word, it didn’t crash from the inside. Instead, the Linux kernel’s Out of Memory (OOM) killer steps in and kills the container from the outside because the total RAM usage blew past what the container was given.

Learning #1: If a Node container dies silently after a memory spike, the OS kernel terminated it externally for exceeding its RAM allocation.

Naive Fixes & Misdiagnosis

We were under a time crunch because we wanted to resolve this issue as soon as possible for our clients. We assumed this might’ve been some sort of memory leak. Getting the service back online was our highest priority. So our first naive fix was to give our pods more RAM by bumping up the V8 heap limit:

env:
  - name: NODE_OPTIONS
    value: "--max-old-space-size=12288"

This was a mistake.

For some background, our container runs six Node processes (4 API instances and 2 worker instances) managed by PM2. Because NODE_OPTIONS applied globally, every single one of those 6 processes inherited the 12 GB limit. We had just permitted six processes to claim up to 72 GB of memory combined inside a container of only 14 GiB of RAM. Before this change, a runaway process would at least hit its own V8 limit and crash. But now, no process hit the V8 limit cause the Linux kernel killed the entire container.

Learning #2: --max-old-space-size is per process. When running multiple Node instances in one container, set heap limits per application in your process manager (like PM2) and keep the combined total below the container’s RAM

Good news is we noticed that what we thought was a memory leak was actually an allocation-rate problem. Our application was spawning temporary objects faster than V8 could clean them up. This became evident by observing our CPU graph showing 83% of our CPU usage was spent running garbage collection. V8 was constantly working overtime to free up space and using up resources. Giving more heap memory on an allocation-rate problem won’t fix the root issue- it just leads to longer garbage collection pauses.

Learning #3: If CPU is maxing out on garbage collection, throwing more heap memory at an allocation-rate problem won't solve it—it just increases GC pause times and masks the root cause.

Finding Invisible Overheads in Audit Logging

This section’s going to have code blocks but stay with me it’s worth it haha

While hunting the main crash, we profiled our standard request path and found a silent performance sink dragging down our entire backend. For security purposes we had to keep an Audit Log of how people used our API. For which we added an @Audit() decorator to log over 198 API endpoints. We ended up uncovering a silent performance disaster. It wasn't the root cause of our pod crashes, but solving it was a huge win as it resulted in reducing audit logging RAM usage by 80%.

A single GET /sheet/:sheetName call was generating a 358 kB audit row- 98.7% of which was cell grid data. Nobody explicitly decided to audit giant spreadsheet grids; it just happened automatically underneath that generic decorator. Worse, NestJS's tap() operator triggered the audit logger before the HTTP response finished serializing, forcing Node to hold five full copies of the cell grid in memory simultaneously across defensive clones, HMAC hashes, and ORM flushes.

By replacing defensive cloning with an inline JSON.stringify replacer, streaming data directly to HMAC buffers, and deferring audit writes using setImmediate, we brought held memory per request down from 20.1 MB to 4.1 MB.

I'll be writing a dedicated article in the future breaking down how we solved this in detail and teaching how to build hyper-efficient Node.js audit logging systems from scratch. So stay tuned!

Heap Snapshots of a Running Pod

The audit log fixes slashed our baseline memory, but those mysterious 9 GiB spikes were still nuking our pods under heavy runs. Some further profiling lead to nowhere so we decided to look inside the V8 heap to see what was going on.

V8 has a handy built-in feature: it can automatically write a heap snapshot right before a process runs out of memory. We gated it behind an environment variable (API_HEAP_SNAPSHOT=1) so it would never trigger unexpectedly in production, passed --heapsnapshot-near-heap-limit=1 to Node, and mounted a volume to catch the output file.

We caught a few small snags:

Our initial heap limit was too generous as we had set Node’s heap cap to 6144 MB but crashes were happening when live runs hit around 4-5 GB. V8’s automatic snapshot was not being fired. We fixed it by bringing the heap cap down to 4096 MB

PM2 killed Node while it was writing the snapshots. This was an interesting way of learning that serializing 4GB heap would spike RSS memory to 8.6 GB. Our PM2 has a max_memory_restart limit set at 7.6 GB. We fixed this by doubling our PM2 memory limits temporarily

If you weren’t already yelling this at us, it is at this point where we admit that waiting for crashes to compare state is a terrible workflow. You need to compare what a “healthy” heap looks like against a “bad” heap. So instead of waiting for OOM crashes, we added NODE_OPTIONS=--heapsnapshot-signal=SIGUSR2 so we can trigger a snapshot on demand by running kill -USR2 <pid>

Once we pulled the 2.8 GB file out via kubectl cp and loaded it into Chrome DevTools, we finally had the raw data to figure out what was actually breaking.

Learning #4: To capture heap logs, lower the V8 heap limit to just above peak usage so the snapshot trigger actually fires. Also budget in at least 2x the heap cap in the container/PM2 memory so process managers don’t kill the Node process mid-dump

The Root Cause

We loaded the 2.8 GB snapshot file into Chrome DevTools. We saw that we have 17.5 million instances of strings and 14 million object instances

We took four steps inside Chrome DevTools to trace millions of tiny objects to diagnose the root cause.

We counted the frequency of the keys in the logs and found the the key for our spreadsheet data appearing 162 times when it should’ve been loaded once

We picked one copy and opened the “Retainers” pane and climbed up the parent references until we exited application code and hit our database infrastructure. We find that the data was inside the raw database result set inside the Postgres connection pool

The row objects carried the alias f4__meta_data. MikroORM aliases in populate order, pointing straight to a 4-element populate array in our code

14.02 million objects divided by 161 rows is roughly 87,000 objects per row. The crashing spreadsheet had 89,700 cells. The math matched: every single returned database row was holding a full, parsed copy of the spreadsheet grid

The retainer trace led to this MikroORM call in our spreadsheet request path:

await this.em.findOne(KnowledgeRecordItemEntity, { folder: { id: folderId } }, {
  populate: ['folder', 'folder.files', 'knowledgeRecord', 'files'],
});

Because it populated two joined collections, Postgres executed a Cartesian join returning 161 rows. Every single row pulled the heavy meta_data JSONB column (22.2 MB of raw JSON per row). node-postgres eagerly parses JSONB on query return, so this single call allocated ~2.4 GB of heap instantly. Worse, all callers of this method only needed the item's id. When a user clicked "Generate All" on a 200-row sheet, this query ran repeatedly, spiking memory to 9 GiB and triggering the kernel OOM killer.

We fixed this by changing the query to exclude the heavy column. We didn’t make it a lazy entity field.

Learning #5: Use retainer trees to pinpoint runaway queries, and use explicit column exclusions that break the build at compile time over lazy entity fields

"We Already Solved This" — The Forgotten Lesson & The Postgres Trap

Once we identified that the spreadsheet data was being the memory hog, our initial idea was to fix it directly in SQL by stripping that key before Postgres returned the row of data. However, when we benchmarked it, the performance tanked. After some investigation we found that large “TOASTed” JSONB columns in postgres are streamed to the client as binary chunks. As soon as we apply a JSON operator like - 'excelData', Postgres can no longer pass the raw stream through. It is forced to decompress (de-TOAST), parse, mutate, and rebuild the JSON document on the database CPU for every single row. This added 2 full seconds of server CPU overhead. Stripping the key in SQL was a wrong turn but at this point we remembered that we solved this case a year before.

Ignored Precedents

In 2025, when we started work on spreadsheet we quickly came into experiencing out of memory errors. To solve for this we built our own Compression Service. A sheet’s cell data was gzipped and base64-encoded. This turned the cell data into an opaque string that was structurally immune to node-postgres’s eager parsing.

Fast forward to this year, a new feature introduced spreadsheet parsing and stored an uncompressed workbook inside files.meta_data.excelData. If we take a look at their type definition we can see a clear picture:

// sheet.entity.ts — The old
@Property({ fieldName: 'cell_data', type: 'string', nullable: true })
cellData: string | null;         

// file.entity.ts — The broken
@Property({ fieldName: 'meta_data', type: 'json', nullable: true })
metaData?: TFileMetaData;     

When our 161-row query executed, node-postgres eagerly parsed every JSON column on every row. The structural difference in memory allocation was massive:

Earlier we compressed cellData to save disk and bandwidth. But gzipping the grid made it opaque to the database driver, which made it immune to eager JSON parsing. We got that protection on cellData, and missed it on meta_data because nothing at the new call site indicated the original problem existed.

New Tests, No Cost Coverage

The wild part is that the PR introducing this bug was insanely well-tested. The tests verified parsing succeeded, status flags updated, and file streams handled correctly. It had 100% behavioral coverage, but not a single test checked how much a database row weighed or how many objects it allocated.

Learning #6:
Modifying JSONB columns inside SQL list queries forces Postgres to de-TOAST and re-parse data on the DB CPU. Exclude heavy columns at the projection layer instead.
Passing behavioral tests mean nothing if your data structures grow without bound. Write tests that assert payload weight boundaries.

Retrospective

Why did this incident happen in the first place?

The original engineering team that built the spreadsheet engine had mostly transitioned to building a new platform for our other customers. Because the original platform had been running stably for over a year, there was confidence that minor feature additions could be handled by newer developers paired with AI coding agents (like Claude), which were expected to pick up existing codebase patterns.

That assumption failed here. When the new spreadsheet parsing was added, the feature was implemented by an engineer who hadn't worked on the original spreadsheet architecture, and it wasn't reviewed by anyone from the original team.

AI agents work strictly from local context. Claude generated code that was locally consistent, highly tested, and functionally correct. It passed all behavioral unit tests and internal PR checks before deploying to production. However, it had no historical context to look back at PRs from six months prior or recognize that stored spreadsheet structures needed to be gzipped to prevent driver-level parsing spikes.

Maintaining long-term product context within an engineering team is essential. AI agents and new team members can generate excellent feature code, but they lack the historical context of past architectural failures unless those constraints are explicitly enforced by the codebase itself (via lint rules, call site constraints, or payload weight tests).

P.S.

I hope you found this article insightful. Especially how we explored and discovered what was wrong.

Check out my other work if you wanna pick my brain :)