Performance: /instrument-records/export buffers the whole dataset in memory (12.8s, +2.9 GiB RSS, 122 MiB body at 200k records)

Current Implementation and Limitations

GET /v1/instrument-records/export (InstrumentRecordsService.exportRecords, apps/api/src/instrument-records/instrument-records.service.ts:152) materialises the entire result set in the API process several times over, with no pagination, no streaming and no bound of any kind.

The pipeline is:

queryRecordsRaw (:452) runs an aggregation that $lookups Session and Subject for every record and $projects the full computedMeasures of each, then .toArray()s the whole thing into memory (:532).
Authorisation is applied in JavaScript, per document, after the fetch: records.filter((record) => appAbility.can('read', record)) (:540). The database does no filtering for it.
Every surviving record is shallow-copied into a new object (:540-542).
The array is chunked and each chunk is postMessaged to a freshly spawned Worker (:185-209), which structured-clones the chunk into the worker; the expanded rows are then structured-cloned back out.
results.flat() builds one array of every export row.
MsgpackInterceptor (core/interceptors/msgpack.interceptor.ts:20) calls this.packr.pack(data) — a single synchronous call — to produce one Buffer holding the whole response body.

Two additional problems in the same function:

When no groupId is supplied (the admin "export everything" path), the $match stage becomes { $expr: {} } (:475-482), which matches every document. So the admin export loads the entire InstrumentRecordModel collection.
The $match sits after both $lookup/$unwind pairs. MongoDB's optimiser does hoist it, so this is not itself the bottleneck, but it makes the intent hard to read and depends on an optimisation that is not guaranteed.

Measurements

Reproduced end-to-end in Node against MongoDB 8 with the real driver, the real @casl/prisma ability, the real export-worker.js logic and the real msgpackr Packr({useRecords:true}). Dataset: 200,000 instrument records (260 MiB), 30 computed measures each, 8 CPUs.

Group-scoped export (25,000 records):

1. aggregate + driver deserialize (25000 docs)     1061ms   heap  108 MiB
2. CASL ability.can() per record                      2ms   heap  110 MiB
3. shallow copy of every record                       2ms   heap  113 MiB
4. chunk into 8 slices                                0ms   heap  114 MiB
5. spawn 8 workers, clone in+out -> 750,000 rows    760ms   heap  472 MiB
6. msgpack pack -> 111.4 MiB response body          449ms   heap  451 MiB
TOTAL                                              2275ms   peak heap 451 MiB / rss 1009 MiB

Unscoped admin export (200,000 records):

1. aggregate + driver deserialize (200000 docs)    7518ms   heap 1075 MiB
2. CASL ability.can() per record                     10ms   heap 1083 MiB
3. shallow copy of every record                      23ms   heap 1088 MiB
4. chunk into 8 slices                                1ms   heap 1089 MiB
5. spawn 8 workers, clone in+out -> 6,000,000 rows 6311ms   heap 3998 MiB
6. msgpack pack -> 885.8 MiB response body         5389ms   heap 3504 MiB
TOTAL                                             19252ms   peak heap 3504 MiB / rss 6311 MiB

So on a mid-sized instance, a single click on "Export" in the datahub:

takes 19 seconds,
allocates 6.3 GiB RSS / 3.5 GiB heap in one request,
produces an 886 MiB response body that is fully buffered before the first byte is sent,
and holds 6,000,000 row objects live simultaneously.

Two consequences beyond latency:

Availability. Step 6 is 5.4 s of synchronous work on the main thread, and the postMessage structured clones in step 5 are synchronous on the sending side too. During that window the API serves no other request — the worker fan-out does not protect the event loop, because the marshalling in and out of the workers happens on it.
OOM. apps/api/Dockerfile sets NODE_OPTIONS="--max-old-space-size=8192", and docker-compose.yaml sets no memory limit on the api service. One export at this scale uses 3.5 GiB of an 8 GiB heap; two concurrent exports will OOM the container, and there is nothing preventing two users from clicking the button at the same time.

Worker mechanics also have a latent bug: the worker.on('message', ...) for BEGIN_CHUNK_PROCESSING is registered inside the handler for the INIT reply (:190-201), so a second INIT-shaped message would register a duplicate listener.

Associated Application Components

Server, Client

Proposed Solution

The fix is to stop materialising the whole export. In rough order of impact:

1. Stream the response instead of buffering it. Drive the Mongo cursor without .toArray() and write rows out incrementally. msgpackr provides packStream/Encoder for exactly this, or the endpoint can emit newline-delimited msgpack/JSON. This alone removes the 886 MiB buffer, the 5.4 s synchronous pack, and lets the client start receiving data in milliseconds instead of 19 seconds. Peak memory becomes O(batch) instead of O(dataset).

2. Push authorisation into the query. accessibleQuery(ability, 'read', 'InstrumentRecord') is already used everywhere else in this service; the raw aggregation should start with a $match built from the same rule (a groupId $in [...] for non-admins) rather than fetching everything and calling ability.can() per document. Today a GROUP_MANAGER still causes the whole collection to be read off disk before the rows they may not see are discarded.

3. Make groupId-less mean "my groups", not "everything". Replace $expr: {} with an explicit match derived from the ability, and put the $match first in the pipeline so intent does not depend on the optimiser.

4. Reconsider the worker pool. With streaming in place the per-record expansion is cheap and the structured-clone round trip is likely to cost more than it saves — measure before keeping it. If workers are retained, use a long-lived pool rather than new Worker() per request per chunk, and move the serialisation into the workers so the main thread never touches the full row set.

5. Bound the request. Even with streaming, an unbounded export of the whole database is worth gating: accept a date range / instrument filter, or return a paged cursor, and consider making very large exports an async job with a download link rather than a synchronous HTTP request.

Estimated Difficulty

High

Priority

High

Confirmed against a live instance — with one correction to the original report

I re-ran this against the real stack: the API running from apps/api (libnest dev), a MongoDB 8 replica set, and a database provisioned through the application's own POST /v1/setup, then grown to 5,000 subjects / 50,400 sessions / 204,800 instrument records (112 MiB).

GET /v1/instrument-records/export        HTTP 200
  wall time                              12.8 s
  response body                          128,128,198 bytes  (122 MiB)
  API process RSS, baseline              1,417 MiB
  API process RSS, peak during export    4,334 MiB          (+2.9 GiB for one request)

So the core of the report holds on the real application: one click on Export costs ~13 seconds and ~2.9 GiB of resident memory, and the entire 122 MiB body is buffered before the first byte is sent.

One thing that makes this worse than the raw numbers suggest: the demo instruments used here produce only 3 computed measures per record. Real clinical instruments commonly have 30–50. The row count, and therefore the response size and the peak heap, scale linearly with measures per record — my synthetic reproduction at 30 measures/record produced an 886 MiB body and 6.3 GiB RSS from the same 200k records.

Correction: the event-loop claim was overstated

The original report said that during the msgpack phase "the API serves no other request" and characterised it as ~5.4 s of full blocking. That is not what happens, and I was wrong to state it that way. I measured it directly by polling GET /v1/setup every 250 ms for the duration of an export:

GET /v1/setup pings during the export (n=120):
  median      : 2 ms
  p95         : 7 ms
  worst       : 1754 ms
  top stalls  : 130 ms, 1196 ms, 1754 ms
  pings > 1s  : 2

The API stays responsive for most of the export. The worker fan-out does do its job. What actually happens is two discrete main-thread stalls of ~1.2 s and ~1.75 s — the synchronous structured-clone marshalling and the packr.pack() call — out of a 12.8 s request, rather than sustained unavailability.

That is still a real problem: any request that lands in those windows waits up to ~1.8 seconds, and both the stall length and the memory cost scale with dataset size and measures per record. But it is a latency spike, not an outage, and the issue should be read that way.

What this means for the proposed fixes

The measurement re-orders them slightly:

Streaming the response (fix 1) remains the right primary fix — it addresses the 2.9 GiB peak, the 122 MiB buffer, and both stalls, since there is no longer a single large object to clone or pack.
Pushing authorisation into the query (fix 2) and bounding the request (fix 5) remain valuable and independent of the above.
Reworking the worker pool (fix 4) is lower priority than I implied. The workers are not the problem; the synchronous marshalling on either side of them is, and streaming removes that regardless of whether the pool is retained.

perf(datahub): match export rows against a set of listed subject ids

perf(web): match export rows against a set of listed subject ids

Still reproduces on main (d86740e). Since this was filed, e2007a7 (#1570) made the row filter check each row through forcedAppSubject, which fixed empty exports for group managers. It still runs after the full fetch (apps/api/src/instrument-records/instrument-records.service.ts:694-696). c363e31 added a third $lookup before the $match.

The nested listener fires on every export

This is not latent. The outer worker.on('message') (:230-240) stays registered, so the chunk result fires it again. It re-posts BEGIN_CHUNK_PROCESSING with the whole chunk before terminate(), which clones every record into its worker twice. Replaying the handlers on an EventEmitter gives INIT -> BEGIN_CHUNK_PROCESSING -> BEGIN_CHUNK_PROCESSING -> resolve -> terminate. #1807 keeps this handshake. worker.once fixes it and can land now. The test uses the existing FakeWorker in instrument-records.service.test.ts and asserts two posts per worker. The exports in testing/src/specs/datahub.spec.ts already cover the path end to end.

There is no cursor to drive

The query uses Prisma aggregateRaw (:686), which returns the whole result as one value and documents no cursor or stream (https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/raw-queries). A real cursor would need a second connection pool beside src/core/prisma.ts. Paging through Prisma avoids that, and gets fix 2 without hand-translating CASL rules into Mongo syntax:

One scoped query: findMany({ select: { id: true, instrumentId: true }, where: { AND: [accessibleQuery(ability, 'read', 'InstrumentRecord'), { groupId }, pendingFilter] } }), with the pending filter find applies (:283) and the export lacks. Instruments are still resolved once, up front, and so are feat: add the instrument hub and record provenance #1807's series names if the query also selects seriesInstrumentId.
For each page of about 1,000 ids, an aggregateRaw whose first stage is { $match: { _id: { $in: page.map((id) => ({ $oid: id })) } } }, then the existing lookups.

That covers fix 3: no groupId means "what this caller may read", which for an admin is still everything, so the cap belongs to fix 5. The $expr: {} stage and the forcedAppSubject filter both go.

Workers and the wire

Expand each page on the main thread and remove the worker. That deletes export-worker.js, thread-types.ts, the copy at apps/api/libnest.config.ts:37-40, the knip.ts:30 entry and the fallback #1653 describes. Port export-worker.test.ts to the new expander. Update runtime-and-vendor.md:127-129, apps/api/AGENTS.md:266 and packages/subject-utils/AGENTS.md:20-21, which all name the worker. Take removeSubjectIdScope from @opendatacapture/subject-utils, which the API already imports.

msgpackr 2.1.0 has no packStream, and Encoder is an alias of Packr (pack.js:1143). Write one frame per page through PackrStream in a StreamableFile, fed from an async generator with stream.pipeline. That way a slow client pauses the page loop. Writing pages without waiting for drain queues the whole export in the buffer again. MsgpackInterceptor and its test go, since this route is its only user.

PackrStream forces sequential, so the web must keep one Unpackr({ structures: [] }) across chunks, as UnpackrStream does. It must also resume from the incomplete/lastPosition error that unpackMultiple throws. decodeIter calls Buffer.concat when a frame spans two chunks, so it breaks in the browser. The XHR adapter can't hand over a partial body. adapter: 'fetch', responseType: 'stream' can (axios 1.20.0).

Once the 200 is sent, a failed page can't become a 500. End the stream with a terminal frame ({ done: true, rowCount }), and have the client reject an export without one.

The browser has its own ceiling

unparse and JSON.stringify(filteredData, null, 2) (apps/web/src/routes/_app/datahub/subjects/index.tsx:375, :380) each build one string. V8 caps a string at 2^29-24 characters, which is about 2.5M CSV rows or 1.1M JSON rows. The 6,000,000-row synthetic export fails in both formats. Streaming helps only if the client also builds a Blob from per-page strings. useDownload takes a Blob only for zip and image types, so that needs a libui change or a local helper. Excel stops at 1,048,576 rows, and downloadExcel writes past that silently. The XLSX writer's only check in xlsx 0.18.5 (xlsx.mjs:15142-15147) clamps the <dimension> ref, and it throws only under WTF. Fix 5 is required either way.

Tests

instrument-records.service.test.ts: the id query's where (asserted as the count test does), the _id $in first stage, and the terminal frame and its absence after a failed page.
datahub/subjects/__tests__/index.test.tsx: a stream with no terminal frame shows the failure notification.
testing/src/specs/datahub.spec.ts: export more records than one page holds and assert the row count.
testing/src/specs/authorization.spec.ts:559-580 reads the export over the API to check a group manager gets no other group's records. Move it to the new route and framing. It is the e2e check on the scoped id query.

Order and decisions

Do this after #1807 (new INIT payload, resolveSeriesNames) and #1717 (changes the $project this keeps). #1412 item 2 goes into the id query. Send the stored subject ids, not the unscoped ones from getListedSubjectIds (apps/web/src/utils/table.ts:13-15). Today, when the table lists GroupA$P001 but not GroupB$P001, an admin with no current group still exports both. That many ids won't fit in a query string, so the route becomes a POST with a body schema and loses the GET retry in services/axios.ts.

Open:

The cap: refuse past N rows with "narrow the filter", or run an async job. The cap is smaller: a job needs somewhere to keep the file, and file storage is optional (StorageService.isEnabled).
Whether to send one entry per record and expand rows on the client. That shrinks the body by about the number of measures per record.

_{Triage note written with Claude Code. Code references point at main @ d86740e.}

Navigation Menu

Repository navigation

Performance: /instrument-records/export buffers the whole dataset in memory (12.8s, +2.9 GiB RSS, 122 MiB body at 200k records) #1407

Current Implementation and Limitations

Measurements

Associated Application Components

Proposed Solution

Estimated Difficulty

Priority

Confirmed against a live instance — with one correction to the original report

Correction: the event-loop claim was overstated

What this means for the proposed fixes

The nested listener fires on every export

There is no cursor to drive

Workers and the wire

The browser has its own ceiling

Tests

Order and decisions

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Performance: /instrument-records/export buffers the whole dataset in memory (12.8s, +2.9 GiB RSS, 122 MiB body at 200k records) #1407

Description

Current Implementation and Limitations

Measurements

Associated Application Components

Proposed Solution

Estimated Difficulty

Priority

Activity

gdevenyi commented on Jul 20, 2026

Confirmed against a live instance — with one correction to the original report

Correction: the event-loop claim was overstated

What this means for the proposed fixes

joshunrau commented on Oct 26, 2026

The nested listener fires on every export

There is no cursor to drive

Workers and the wire

The browser has its own ceiling

Tests

Order and decisions

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions