How a 'Simple' CSV Export Crashed Our Node Service (And the One-Line Fix That Wasn't)

A few weeks ago I got paged for a feature I’d almost forgotten we had: bulk user export to CSV. It had run fine for over a year. Then one of our bigger tenants - a few million user records - clicked export, and about ten minutes later a worker process died with OOMKilled.

No stack trace, no clean error. Just a dead process and a half-written file sitting in temp storage.

The Root Cause

Small tenants had been hiding a real problem. Here’s roughly what the export code looked like:

async function exportUsers(tenantId, res) {
  const users = await knex('users').where({ tenant_id: tenantId }); // loads EVERYTHING
  const rows = users.map(toCsvRow);
  res.send(rows.join('\n'));
}

knex('users').where(...) returns a promise that resolves with the entire result set as an array. For a tenant with a few thousand users, that’s fine. For a tenant with millions of rows, we were holding gigabytes of user objects in memory before we’d written a single byte to the response. It wasn’t a bug that showed up in code review - it just looked like a normal query.

The Fix: Stream It

The fix wasn’t more memory, it was to stop materializing the whole dataset at all. Knex actually has a built-in .stream() method that gives you a proper readable stream backed by a DB cursor, instead of an in-memory array:

const { pipeline } = require('node:stream/promises');
const { Transform } = require('node:stream');

async function exportUsers(tenantId, res) {
  res.setHeader('Content-Type', 'text/csv');
  res.setHeader('Content-Disposition', `attachment; filename="export-${tenantId}.csv"`);

  const dbStream = knex('users')
    .select(['id', 'name', 'email'])
    .where({ tenant_id: tenantId })
    .stream({ highWaterMark: 500 });

  const csvTransform = new Transform({
    objectMode: true,
    transform(row, _enc, callback) {
        try {
            callback(null, toCsvRow(row) + '\n');
        } catch (error) {
            callback(error);
        }
    },
  });

  await pipeline(dbStream, csvTransform, res);
}

Why this actually works, not just “looks streamy”:

• .stream() uses a real cursor under the hood, so Knex isn’t buffering the full result set before handing it to you - rows come off the wire in small batches.
• pipeline() from stream/promises wires it all together and handles backpressure automatically. If the client’s connection is slow and res can’t accept data fast enough, pipeline pauses the upstream DB read until the response catches up. You don’t write any manual throttling - it’s just how streams work.
• pipeline also handles cleanup and error propagation for you. If any stage errors out, the others get destroyed properly instead of leaking a connection or a half-open cursor - something manual .pipe() chains have historically gotten wrong.
• highWaterMark: 500 controls how many rows get buffered at once. Tune it based on row size; the goal is small, bounded memory use, not zero buffering.

After this change, memory stayed flat regardless of tenant size. The export took a little longer wall-clock for huge tenants, but it stopped crashing - and CPU/memory graphs went from a slow climb-and-die pattern to a boring flat line, which is exactly what you want to see in production.

What I Took Away From This

• “It works” and “it scales” are different claims. This code passed every test we had - until a tenant’s data outgrew our assumptions.
• Load test with an outlier dataset, not just a realistic one. We didn’t have a “one giant tenant” scenario in our test suite. We do now.
• Backpressure is the actual point of streaming, not just lower memory. A slow consumer naturally throttles a fast producer without you writing any rate-limiting logic yourself.

If you’re building any bulk export, import, or report feature, it’s worth asking early: what happens when this runs against your biggest possible customer, not your average one? Usually the honest answer is “I don’t know” - and that’s the moment to reach for a stream instead of an array.