“How hard can it be? It’s just SELECT * FROM sales and write to a CSV.” – Me, before the server begged for mercy.

I learned the hard way that generating a multi-gigabyte report on a 2-GB-RAM server is like trying to move an entire public library using a single bicycle—you need lots of small, well-balanced trips rather than one back-breaking haul. In this guide I’ll show you how to box up that data and shuttle it safely, keeping your NestJS app upright and your cloud bill slim.

We’ll walk through an iterative journey—from the naive approach that topples over, to a streaming solution that glides along smoothly. Feel free to skip ahead if you only need the final recipe, but the detours hold valuable lessons!

Source code on GitHub

Table of Contents

  1. The naïve approach – why it melts your RAM
  2. Streaming with pg-query-stream (step-by-step)
  3. Turbo mode – COPY TO STDOUT
  4. Guardrails with Docker resource limits
  5. Putting it to the test
  6. When NOT to stream
  7. Conclusion & next steps

1. The naïve approach – why it melts your RAM

My first instinct was to await this.salesRepository.find() and then json2csv the resulting array.

// ❌ Bad idea – loads *everything* into memory
const rows = await this.salesRepository.find();
return parse(rows); // => 💥  Out Of Memory

With 50 million rows each weighing ~100 bytes, that’s ~5 GB on the heap – far beyond my puny box.

Takeaway: batching is not enough; we need true streaming.

2. Streaming rows with pg-query-stream

PostgreSQL supports cursors under the hood. The wonderful pg-query-stream package exposes that as a Node stream. NestJS integrates nicely because everything is just JavaScript.

// reports.service.ts
@Injectable()
export class ReportsService {
  constructor(private readonly dataSource: DataSource) {}

  async streamCsv(res: Response) {
    const query = new QueryStream(`SELECT id, amount, created_at FROM sales ORDER BY created_at`);
    const client = await this.dataSource.driver.connect();

    return new Promise<void>((resolve, reject) => {
      const stream = client.query(query);
      const stringify = csvStringify.open({ header: true });

      res.setHeader("Content-Type", "text/csv");
      res.setHeader("Content-Disposition", 'attachment; filename="sales.csv"');

      stream
        .pipe(stringify)
        .pipe(res)
        .on("finish", () => {
          client.release();
          resolve();
        })
        .on("error", (err) => {
          client.release(err);
          reject(err);
        });
    });
  }
}

How the pipeline stays lean:

  1. Cursor paging – Postgres ships ~10 000 rows per network packet instead of the whole result set.
  2. Back-pressure aware streams – pg-query-stream respects Node’s highWaterMark, pausing the cursor when downstream is busy.
  3. Transform stage – csv-stringify converts objects → CSV one row at a time; memory never spikes.
  4. Constant-memory write – res is an OutgoingMessage stream; chunks flush to the socket immediately so the heap tops out around ~30 MB even for gigabyte files.
  5. GC friendliness – Objects are short-lived and quickly cleared by V8’s nursery GC, avoiding full-heap collections.

Pro tip: tune batchSize (2nd arg to QueryStream) and Node’s --max-old-space-size to squeeze the last drops of performance.

Performance numbers

Dataset Heap max Time to first byte Total time
50 M rows (4 GB CSV) 40 MB 320 ms 11 m 12 s

The CPU graph stayed below 65 % on a single vCPU, and RSS never crossed 120 MB.

3. Turbo mode: COPY TO STDOUT

If you don’t need to massage the data, Postgres can out-stream us all.

const copyFrom = require("pg-copy-streams").to;
const stream = client.query(copyFrom(`COPY (SELECT id, amount, created_at FROM sales) TO STDOUT WITH CSV HEADER`));
stream.pipe(res);

This shaves ~30 % off total time and halves CPU usage because Node no longer JSON-parses anything.

Trade-off: you lose TypeORM/Prisma abstractions and any middleware transforms.

4. Guardrails with Docker resource limits

It’s easy to “works on my machine” a streaming endpoint only to have it GigaLeak in prod. Testing with cgroups is the cure.

# Run NestJS with 256 MB RAM and half a CPU
docker run -m 256m --cpus="0.5" \
  -p 3000:3000 mycorp/reports:latest

Watching usage

# In another terminal
docker stats --no-stream

If the container OOM-kills, you know you still have hidden buffers. Dial metrics with:

# package.json
"scripts": {
  "start:prod": "node --expose-gc dist/main.js"
}

Then inside your code sprinkle:

import * as v8 from "v8";
console.log("heapUsed", v8.getHeapStatistics().used_heap_size);

Track GC pauses with --trace_gc --trace_gc_verbose flags for deeper dives.

5. Putting it to the test

  1. Unit test the service by mocking the Postgres client and ensuring stream.pipe is called.
  2. Integration test inside Docker using autocannon:
# 2 concurrent connections, 30-second run, timeout high enough for full CSV transfer
autocannon -c 2 -d 30 --timeout 100000 \
  http://localhost:3000/reports/csv?mode=stream

Example results on my 256 MB-RAM container:

Latency (avg)        27.49 s
Requests/sec         0.07
Throughput           2.25 MB/s
4 requests in 30.0 s, 67.6 MB read
Memory RSS before: 22 MiB | after: 55 MiB
  1. Chaos test: halve the memory limit and re-run. If it still passes, ship it!

6. When NOT to stream

  • You need complex joins that mutate per-row state (think window functions) – a materialised view might be cheaper.
  • The report is <50 k rows – latency from streaming setup outweighs gains.
  • Your infra already uses a dedicated ETL pipeline like Apache Beam; reuse that instead.

7. Conclusion

Generating huge reports doesn’t have to mean huge servers. By leaning on Postgres cursors, Node streams, and Docker’s guardrails we can deliver multi-gigabyte files from a single-core instance without ever seeing the dreaded OOM killer.

What other performance rabbit holes should we dive into next? Let me know in the comments below.