Nov 17, 2026
Your export button times out on big tables
Every dashboard ends up with an Export button sooner or later. Somebody wants the orders in a spreadsheet, and the button works fine in development — two hundred rows, instant download. Then it meets a real shop with eighty thousand orders, and the button spins, the request dies, and sometimes the server goes with it.
The usual shape of the bug looks like this:
app.get("/api/orders/export", async (req, res) => {
const { rows } = await pool.query(
"SELECT id, created_at, total FROM orders WHERE shop_id = $1",
[req.user.shopId]
);
const csv = stringify(rows); // one giant string
res.setHeader("Content-Type", "text/csv");
res.send(csv);
});
Three things happen in sequence, and each one holds the whole dataset. The query buffers every row into one array. The CSV library turns that array into one giant string. Then res.send holds that string while the client downloads it. At peak, the server is carrying two or three copies of the same data in memory, and nothing leaves the server until all of it is ready. On a small server that is an out-of-memory crash. On a serverless function it is a timeout: the function is killed before the first byte ever goes out, and the user gets nothing.
The fix is to stop collecting and start streaming. A row should leave the server as soon as it has been turned into CSV, and the server should forget it the moment it does. Node streams are built for exactly this, and PostgreSQL can feed them directly. pg-query-stream turns a query into a readable stream backed by a server-side cursor: instead of pool.query() waiting for every row to arrive, rows come through in small batches while the rest are still in the database.
import { pipeline } from "node:stream/promises";
import QueryStream from "pg-query-stream";
import { stringify } from "csv-stringify";
app.get("/api/orders/export", async (req, res) => {
const client = await pool.connect();
try {
res.writeHead(200, {
"Content-Type": "text/csv",
"Content-Disposition": "attachment; filename=orders.csv",
});
const query = new QueryStream(
"SELECT id, created_at, total FROM orders WHERE shop_id = $1 ORDER BY created_at",
[req.user.shopId]
);
const dbStream = client.query(query);
await pipeline(dbStream, stringify({ header: true }), res);
} catch (err) {
if (!res.headersSent) res.status(500).json({ error: "Export failed" });
else res.destroy();
} finally {
client.release();
}
});
Three streams, one pipeline: the database stream produces rows, the CSV transform turns each row into a line, the HTTP response carries it to the browser. Memory stays roughly flat no matter how many rows there are, because only one batch is in flight at a time. The download starts almost immediately instead of after the whole query finishes, which is usually what makes the timeout go away on its own.
The part that makes this safe is backpressure, and it comes free with pipeline. If the user is on a slow connection, the response cannot drain fast enough, and the pipe tells the database stream to slow down instead of buffering rows in Node. Without that, streaming is just a slower way to run out of memory: a fast database pouring into a slow socket still has to put the rows somewhere. pipeline also wires up the error path, so a failure anywhere in the chain rejects the await and the pooled client gets released in the finally — an export that leaks its connection on every failure will quietly drain the pool.
Two details worth getting right. First, do not build the CSV by hand. A value with a comma, a quote, or a newline in it breaks a naive join(","), and real data has all three — a shop name with a comma, a note with a line break. A real stringifier quotes those fields properly; hand-rolled CSV is a bug report waiting for the first customer with a comma in their name. Second, do not fake the streaming with a loop of LIMIT and OFFSET queries. It is far slower than a cursor, and on a live table it can skip rows and repeat rows as new orders land between pages — the same offset drift from the pagination post, wearing a different hat.
And the honest limits, because streaming does not make an export free. The export holds one pooled connection for its entire duration, so ten simultaneous exports are ten connections the rest of the app cannot use — cap concurrent exports or queue them. A streaming response still has to finish inside the platform's request timeout, so the truly huge exports do not belong in a request at all: run them as a background job and email the download link when it is ready. That is the normal pattern, and the stream is what the job uses too — the code does not change, only where it runs. And one query is one consistent snapshot, so the export reflects the data as it was when the query started, not as it is when the last row downloads. Say that in the UI if it matters; a file that took ten minutes to download should not pretend it is a live view.
The rule I use now: if a response is built from a query, the query should stream. Loading the whole table into memory to hand it back one row at a time was never the plan — it was just the default, and the default only works until the table gets big.