Why do large CSV exports in Node.js run out of memory?
Most export endpoints start the same way: run one SELECT, get the rows back, turn them into CSV, send the result. That works on test data and fails in production once the table is large. There are two common ways it fails, and the second can look like a fix for the first.
Failure mode 1: materializing the result set
A plain query through a typical Postgres client buffers the entire result set before returning it, so you get every row as an array of JavaScript objects. Mapping that array to CSV lines and joining them creates a second full copy as a string. Peak memory scales linearly with row count, and per-row object overhead is often several times the size of the CSV it produces. At some row count the process hits its heap limit and crashes, or the container is OOM-killed.
Failure mode 2: writing without listening
The usual next step is to loop over rows and call res.write() for each one. But res.write() returns a boolean. false means the stream's internal buffer is above its high-water mark and you should wait for drain before writing more. If you ignore it, every write still succeeds and gets queued in process memory. A slow client then lets the server produce data faster than the socket can send it. The unbounded buffer has only moved from your array into the response stream.
The goal
The goal is an export whose memory use depends on buffer sizes you choose, not on how many rows the query returns. This is for backend and platform engineers who serve exports with no practical upper bound on size.
const { once } = require('node:events');
const { Pool } = require('pg');
const pool = new Pool({ connectionString: 'postgres://export_user:changeme@db.internal:5432/app' });
// Failure mode 1: the result set is held in memory twice.
async function exportNaive(req, res) {
// pool.query() resolves only after every row is buffered as a JS object.
const { rows } = await pool.query('SELECT id, email, created_at FROM orders');
// map + join builds a second full copy as one string. (CSV escaping omitted.)
const csv = rows.map((r) => `${r.id},${r.email},${r.created_at.toISOString()}`).join('\n');
res.setHeader('Content-Type', 'text/csv');
res.end(csv);
}
// Failure mode 2: rows arrive incrementally, but write()'s return value is ignored.
// rowSource is any async iterable of rows, such as a cursor-backed stream.
async function exportIgnoringBackpressure(rowSource, res) {
res.setHeader('Content-Type', 'text/csv');
for await (const r of rowSource) {
// Returns false once the buffer passes highWaterMark. Each line is still
// queued in process memory, so a slow client lets this queue grow unbounded.
res.write(`${r.id},${r.email},${r.created_at.toISOString()}\n`);
}
res.end();
}
// The minimal correction: stop producing until the socket catches up.
async function exportRespectingBackpressure(rowSource, res) {
res.setHeader('Content-Type', 'text/csv');
for await (const r of rowSource) {
if (!res.write(`${r.id},${r.email},${r.created_at.toISOString()}\n`)) {
await once(res, 'drain'); // wait until buffered data is flushed to the socket
}
}
res.end();
}
Side-by-side handlers showing how buffering the whole result set and ignoring res.write()'s false return both grow memory with row count, followed by the minimal fix: await 'drain' whenever write() returns false.
How does backpressure work in Node.js streams?
Every stream in the chain has an internal buffer whose limit is set by highWaterMark. On the writable side, write() always accepts the chunk, but it returns false once the buffered amount reaches that limit. That return value asks the producer to stop. A well-behaved producer then waits for the 'drain' event, which fires once the buffer has emptied enough to accept more. If a producer ignores false and keeps writing, the buffer keeps growing, and you are back to holding the whole export in memory.
Readables follow the same rule from the other direction. Inside a Readable, push() returns false when the readable buffer is full. After that, the stream stops calling _read() until a consumer takes data out. Consumption is therefore pull-based: nothing upstream does work until something downstream asks for it.
pipeline() connects these signals for you. An async generator fits the same model, because its loop body runs only when the next stage requests another value.
Postgres async CSV gzip HTTP client
cursor --> generator --> transform --> stream --> response --> socket
rows row objects text chunks bytes bytes
<-------- demand: "ready for more" travels upstream ----------
(slow network fills each buffer in turn; the cursor stops fetching)
The buffers do not all measure the same thing. Object-mode streams count objects, not bytes, so a limit of 16 means 16 rows, whether each row is 50 bytes or 50 KB. On the stages that carry row objects, choose highWaterMark based on your worst-case row size. Byte-mode stages such as gzip and the response are bounded in bytes.
DATA (downstream) DEMAND (upstream)
v ^
+----------------------+ |
| Postgres cursor | fetches next batch only |
+----------------------+ when generator pulls |
| rows |
v |
+----------------------+ objectMode: HWM counts |
| async generator | rows, not bytes |
+----------------------+ runs per next() request |
| row objects |
v |
+----------------------+ objectMode in, bytes out|
| CSV transform | push() false -> pause |
+----------------------+ |
| text chunks |
v |
+----------------------+ byte mode: HWM in bytes |
| gzip stream | write() false -> wait |
+----------------------+ for 'drain' |
| compressed bytes |
v |
+----------------------+ write() false when the |
| HTTP response | socket can't keep up |
+----------------------+ |
| bytes |
v |
+----------------------+ |
| client socket | slow link fills buffers-+
+----------------------+
Rows flow down from the Postgres cursor to the client socket, while demand flows back up: a slow network fills each stage's buffer in turn, and the cursor stops fetching until the next stage asks for more (object-mode stages count rows, byte-mode stages count bytes).
How do you read rows incrementally from Postgres with a cursor?
A plain SELECT returns its rows over one wire response, and most Node.js drivers collect every row into an array before resolving the query promise. For a 20-million-row export, the full result set lands on the heap before your first CSV byte is written. Backpressure further down the pipeline can't help, because the memory is already allocated.
A server-side cursor solves this by letting the client decide how many rows to receive. Open a transaction, run DECLARE export_cur NO SCROLL CURSOR FOR SELECT ..., then repeatedly issue FETCH 1000 FROM export_cur until a fetch returns zero rows. Finish with CLOSE and COMMIT. Postgres keeps the cursor's position between fetches, so each round trip only materializes one batch in Node.
Wrap the loop in an async generator that yields rows one at a time and runs CLOSE, ends the transaction and releases the connection in a finally block. Passing the generator to Readable.from() gives you an object-mode stream that only advances the generator when its buffer has room. The next FETCH is sent only after downstream stages have pulled the previous batch through. This links the database round trips directly to the client's download speed.
The cost is that the transaction stays open for the whole export, and the same is true of the pooled connection and the snapshot. A long-running snapshot holds back vacuum cleanup. While the generator waits on a slow consumer, the session is idle in transaction, so idle_in_transaction_session_timeout can terminate it. Run large exports against a replica where you can, and size the pool so exports can't starve normal traffic. Cursor and query-stream helper packages exist for common client libraries if you'd rather not manage FETCH yourself.
import { Readable } from 'node:stream';
import pg from 'pg';
// Small pool so long-running exports can't starve normal traffic.
const pool = new pg.Pool({ connectionString: process.env.EXPORT_DATABASE_URL, max: 4 });
async function* exportRows(batchSize = 1000) {
const client = await pool.connect();
let committed = false;
try {
// A cursor only exists inside a transaction, which stays open for the whole export.
await client.query('BEGIN READ ONLY');
await client.query(
'DECLARE export_cur NO SCROLL CURSOR FOR SELECT id, email, total, created_at FROM orders ORDER BY id'
);
while (true) {
// Only this batch is materialized in Node; Postgres remembers the cursor position.
const { rows } = await client.query(`FETCH ${batchSize} FROM export_cur`);
if (rows.length === 0) break;
// The generator suspends here until downstream pulls, so the next FETCH waits too.
yield* rows;
}
await client.query('CLOSE export_cur');
await client.query('COMMIT');
committed = true;
} finally {
// Runs on success, on error, and when the consumer stops early (e.g. client disconnect).
let releaseErr;
if (!committed) await client.query('ROLLBACK').catch((err) => { releaseErr = err; });
// Passing an error to release() discards a possibly broken connection instead of reusing it.
client.release(releaseErr);
}
}
// Object-mode stream; it advances the generator only when its internal buffer has room.
export const rowStream = () => Readable.from(exportRows());
An async generator issues DECLARE and batched FETCH calls inside a read-only transaction. Readable.from() wraps it so each new batch is fetched only after downstream stages have consumed the previous one, and the finally block always ends the transaction and releases the connection.
How do you implement the CSV transform and compose it with pipeline()?
Whichever cursor source you choose, the next stage turns row objects into CSV text. A Transform with writableObjectMode: true accepts rows and pushes strings. Because push() returns false when the readable buffer is full, the transform participates in backpressure without extra code.
Escaping is where hand-rolled exporters usually break. Wrap a field in double quotes if it contains a comma, a double quote, a carriage return or a line feed, and double every embedded quote. Keep null distinct from the empty string. Emit null as an empty unquoted field and the empty string as "", so a consumer can tell "no value" from "blank value." Push the header row once, before the first data row. If spreadsheet users will open the file, also prefix a UTF-8 BOM (\uFEFF) so Excel detects the encoding.
Exports that end up in spreadsheets also carry formula-injection risk. A cell beginning with =, +, - or @ can execute as a formula. Prefixing such cells with a single quote neutralizes them, but it also changes the data. Make that a documented, per-export decision. If you need configurable delimiters, dialects or this kind of policy, a well-tested CSV library is safer than your own escaper.
Compose the stages with pipeline() from stream/promises: cursor stream, CSV transform, optionally zlib.createGzip(), then the response. Set headers before the first byte goes out:
Content-Type: text/csv; charset=utf-8Content-Disposition: attachment; filename="export.csv"Content-Encoding: gzip, if you compress
Chained .pipe() calls don't forward errors downstream or destroy upstream streams. A failure there can leave the cursor and its pooled connection open. pipeline() destroys every stage when one fails and rejects a single promise you can handle.
const { Transform } = require('node:stream');
const { pipeline } = require('node:stream/promises');
const zlib = require('node:zlib');
// null/undefined -> empty unquoted field; '' -> "" so consumers can tell them apart.
// Formula-injection neutralizing (=, +, -, @) is deliberately NOT done here: it is a per-export policy.
function escapeCsv(value) {
if (value === null || value === undefined) return '';
const s = String(value);
if (s === '') return '""';
return /[",\r\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}
function csvTransform(columns, { bom = true } = {}) {
let headerSent = false;
const header = () => (bom ? '\uFEFF' : '') + columns.map(escapeCsv).join(',') + '\r\n';
return new Transform({
writableObjectMode: true, // rows in, strings out; backpressure handled by the Transform machinery
transform(row, _enc, cb) {
const prefix = headerSent ? '' : header();
headerSent = true;
cb(null, prefix + columns.map((c) => escapeCsv(row[c])).join(',') + '\r\n');
},
flush(cb) { cb(null, headerSent ? '' : header()); }, // empty result still gets a header
});
}
async function exportUsers(req, res, cursorStream) {
const useGzip = /\bgzip\b/.test(req.headers['accept-encoding'] || '');
// Headers must be set before pipeline() writes the first byte.
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
res.setHeader('Content-Disposition', 'attachment; filename="export.csv"');
res.setHeader('Vary', 'Accept-Encoding');
if (useGzip) res.setHeader('Content-Encoding', 'gzip');
const stages = [cursorStream, csvTransform(['id', 'name', 'email'])];
if (useGzip) stages.push(zlib.createGzip());
try {
await pipeline(...stages, res); // any failure destroys every stage, closing the cursor
} catch (err) {
console.error('CSV export failed', err); // response is already destroyed; just log
}
}
A CSV Transform that quotes and doubles embedded quotes, keeps null distinct from empty string, emits a BOM and header, and is composed with an optional gzip stage into the HTTP response via pipeline() from stream/promises so one failure tears down every stage.
What happens when the client disconnects or the export fails mid-stream?
When the client disconnects, the HTTP response closes before the pipeline finishes. pipeline() treats that as a failure: it destroys the remaining stages and rejects, typically with a premature-close error. Destroying the source stream does not clean up the database, though. Do that yourself in a finally block: close the cursor, roll back the transaction (a read-only export has nothing to commit, and rollback releases its snapshot), and release the client back to the pool. If you skip any of these steps, each aborted download leaks a pooled connection, and the pool eventually starves.
If rows come from an async generator, pass an AbortSignal to it and check the signal between batches. The generator then stops issuing fetches as soon as the request is gone, instead of discovering it on the next failed write.
Mid-stream failures have one more constraint. Status and headers are sent with the first byte of the body, so once rows are flowing you cannot turn an error into a 500. The client sees a 200 followed by a truncated body. All the server can do is destroy the socket so the transfer does not appear to have ended cleanly. Make truncation detectable in the file itself. Common options are a trailing marker row, a final row count, or a checksum, sent in a trailer or a closing line, which the consumer verifies before trusting the file.
Backpressure bounds memory, not time. A slow or stalled client can hold a connection and an open transaction indefinitely. Set idle and socket timeouts, and enforce a maximum export duration that aborts the pipeline when it expires.
How do you verify memory stays bounded and tune the export?
To verify the export, seed a synthetic table large enough that buffering everything would plainly exceed your container's memory limit. generate_series in Postgres works well for this. Then fetch the export with curl --limit-rate 50k -o /dev/null so the client is slower than the database. A throttled client keeps the socket's buffer full, which forces backpressure to propagate back to the cursor. While the export runs, log process.memoryUsage() every second and track rss, heapUsed and external. A correct pipeline shows RSS rising briefly and then holding flat for the rest of the export. If RSS keeps climbing, capture two v8.writeHeapSnapshot() files a minute apart and compare them to see which objects are accumulating.
As an illustrative estimate, assume 2,000 rows per fetch at roughly 500 bytes each. Each batch then holds about 1 MB in flight, plus whatever the stream buffers hold. That total should not change between a one-million-row export and a hundred-million-row export.
| Knob | Larger value | Smaller value |
|---|---|---|
| Cursor fetch size | Fewer round trips, more memory per batch | Less memory, more latency overhead |
highWaterMark (object-mode stages) |
Counts objects, so more rows buffered | Tighter memory bound |
highWaterMark (byte stages) |
Counts bytes, so fewer, larger writes | More frequent writes |
| Rows joined per string chunk | Fewer write() calls, less per-chunk overhead |
Finer-grained backpressure |
| gzip level | Less bandwidth, more thread-pool CPU | Faster compression, larger payload |
Compression runs on libuv's thread pool, so high gzip levels compete with DNS lookups and file I/O for the same threads. If a reverse proxy already compresses responses, let the proxy handle it and send uncompressed output from Node.
Verifying a bounded pipeline once is cheaper than repeatedly raising memory limits after OOM kills. It also avoids investigating heap dumps under incident pressure.
When should you avoid streamed exports, and what are the alternatives?
Cursor streaming ties one pooled connection and one open transaction to each export for its entire duration. That duration depends on the slowest client, not on the database. A handful of concurrent exports to slow consumers can drain the pool and stall ordinary requests.
The open transaction also holds a snapshot, so vacuum cannot remove row versions that snapshot might still need. Busy tables bloat for as long as the export runs. To limit the damage:
- Cap concurrent exports with a semaphore or a small dedicated pool.
- Consider exporting from a read replica. Long queries there can be cancelled by replication conflicts, and enabling
hot_standby_feedbackto prevent that pushes the vacuum delay back onto the primary.
| Approach | Strengths | Costs |
|---|---|---|
| Cursor streaming | Consistent snapshot; little new infrastructure | One connection and transaction per export; client speed sets duration |
| Keyset pagination | Short independent queries; resumable from the last key after a reconnect | No snapshot consistency across pages; needs a unique, indexed sort order |
| Async job to object storage | Retries, resumable downloads, reusable output | Queue, worker, storage and expiry cleanup; user waits for a link |
Keyset pagination (WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT n) avoids long transactions. The trade-off is that rows inserted or updated between pages can appear twice or be missed.
Development cost differs between the options. Streaming reuses your existing HTTP handler, but every failure path happens mid-response, after headers are sent. Async jobs take more to build. In return, a failed export becomes a retryable job state rather than a truncated download, which is usually the better trade for exports that are very large or requested repeatedly.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to node.js stream csv export, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.