Streaming a CSV Export From a Web App Without Memory Spikes

The first export feature almost everyone writes works like this: run the query, put the rows in a list, turn the list into a CSV string, return the string. It is five lines, it passes testing on 200 rows and it breaks in production on the first customer with a large table, either with an out-of-memory error or with a request that hangs and times out. The fix has the same structure every time: do not hold the file, produce it. Read a row, write a row, forget the row. This article measures the difference on one million rows, shows how to build a streaming response with the right headers, covers the proxy behavior that quietly defeats streaming and says when streaming is not enough. It is part of CSV import and export features in applications, and a close relative of generating a CSV download in JavaScript for the browser side.

The measurement

The experiment writes the same one million rows two ways. In the first, every row goes into an array and the array is written at the end. In the second, each row is written as soon as it is generated. Both use fputcsv and produce the same 40.7 MB file:

function rows(int $n): Generator {
    for ($i = 1; $i <= $n; $i++) {
        yield [$i, "Customer $i", "2026-10-" . str_pad((string) (($i % 28) + 1), 2, '0', STR_PAD_LEFT),
               number_format($i % 1000 + 0.5, 2, '.', '')];
    }
}

// array: collect everything, then write
$all = [];
foreach (rows($n) as $row) { $all[] = $row; }
foreach ($all as $row) { fputcsv($out, $row, ',', '"', ''); }

// stream: write each row as it is produced
foreach (rows($n) as $row) { fputcsv($out, $row, ',', '"', ''); }

array   rows=1000000 time=1.43s peak memory=336.0 MB file=40.7 MB
stream  rows=1000000 time=1.09s peak memory=2.0 MB file=40.7 MB

The array version peaked at 336 MB for a 40.7 MB file, an overhead of about eight times, because each PHP row is an array with its own bookkeeping. Under PHP's default memory_limit of 128 MB, it did not finish:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2097152 bytes)

The streaming version used 2 MB, the same at a million rows as at ten, and ran a little faster. The memory cost of the array approach grows with the table, so it works until a customer has enough data, and then it fails on exactly the customer who needed the export. The cost of the streaming approach is constant. Other languages differ in the multiplier and not in the shape: a list of tuples in Python and an array of objects in JavaScript carry similar overhead.

The generator is the important part. It is a function that yields one row at a time, and the consumer pulls from it. Wherever your rows come from, a database cursor, a paged query, a file, wrap them in a generator or iterator and write them as you go. With a database, use an unbuffered cursor or a library's lazy iteration: in Laravel the cursor() and lazy() methods, in Django .iterator(), in Rails find_each or find_in_batches, in PDO an unbuffered query. The default in most libraries is to fetch all rows into memory first, which defeats the generator, so check that the query itself is streamed: memory that still grows means a buffered driver.

The response

In a web app, "write" means writing to the HTTP response. The headers matter, and they are short:

Content-Type: text/csv; charset=utf-8
Content-Disposition: attachment; filename="customers-2026-10-02.csv"
Cache-Control: no-store
X-Accel-Buffering: no

Content-Type: text/csv with an explicit charset tells the browser and any proxy what the file is. Content-Disposition: attachment makes the browser download it and filename names it; build the name from the content and the date, since export.csv ten times in a downloads folder is not helpful. For a filename with non-ASCII characters, use the filename*=UTF-8''... form from RFC 6266. Cache-Control: no-store keeps a proxy or browser from caching a file that may hold private data. And X-Accel-Buffering: no is an nginx-specific header that tells nginx not to buffer the response, which brings us to the quiet failure.

A reverse proxy such as nginx, by default, buffers the response from the application before sending it to the client. A streaming export behind a buffering proxy still holds the file in memory, on the proxy instead of in the app, and the user sees no download progress until the whole file has been generated. The header turns it off for that response, and the equivalent for a CDN or a managed load balancer is in its documentation. Check by downloading a large export and watching whether the browser starts receiving bytes immediately. If it does not, something between you and the user is buffering.

Frameworks wrap all this. In Laravel, response()->streamDownload(function () {...}, 'customers.csv', $headers) runs the callback and sends what it writes; in Django, a StreamingHttpResponse over a generator; in Rails, a response body that is an enumerator; in Express, piping a stream into res. I have described these from their documentation and did not run them here. In each, the callback receives an output handle, you open php://output or the equivalent and write rows with the language's CSV writer.

Details that bite

The byte order mark. If users open the file in Excel by double-clicking, write EF BB BF as the first three bytes so that accented characters survive, and offer an alternative without it for programs (Excel CSV UTF-8 versus CSV comma delimited). A good pattern is two buttons: "Download for Excel" and "Download CSV".

Formula injection. Text a user typed that starts with =, +, - or @ must be neutralized in a file meant for a spreadsheet (escaping formula characters in a CSV export). Do it in the row-producing function, so every export path gets it.

Headers first, before any query can fail. Once the first byte is sent, the status code is 200 and cannot change. If the query fails halfway, you cannot tell the client; the connection ends with a truncated file that may look fine. Run the query, fetch the first row, then send headers and start writing, so that an immediate failure is a proper error page. For a failure in the middle, write an error marker row, or better, make a truncation detectable: the user sees fewer rows than the on-screen count. Testing that a CSV export was not truncated covers the checks.

Client disconnects. If the user closes the tab, the app should stop producing. Most servers signal it: PHP's connection_aborted() or ignore_user_abort(false), a closed socket in Node. Without the check, an abandoned export keeps reading the table for minutes.

Flush. Output buffers hold bytes until they are full. A streaming export should flush periodically (every few thousand rows) so that the client and the proxy see data, and so that a stalled connection does not look idle. Do not flush after every row: the system call dominates.

Compression

CSV compresses to a fifth or a tenth, so Content-Encoding: gzip on a large export saves a lot of transfer time. Compress as a stream, not at the end: most web servers can gzip responses on the fly (nginx's gzip on with text/csv in gzip_types), which composes with streaming if buffering is off. Do it at the proxy when you can, since it keeps the application code simple, and remember that compression adds CPU time on a path that is already busy.

When streaming is not enough

Streaming fixes memory. It does not fix time. A table of ten million rows takes minutes to read and write, and an HTTP request that lasts minutes will hit a timeout somewhere: the proxy's, the load balancer's, the browser's or a mobile network's. It also ties up a web worker and a database connection for the whole duration, which at ten concurrent exports can starve the rest of the application. Streaming is the right answer for exports that finish in a handful of seconds. For longer ones, hand the work to a background job that writes a file and notifies the user, as in background jobs for large CSV exports, and for read-heavy exports, run them against a replica, not the primary.

Set the cutover from measurement: time the export of your largest customer, and if it exceeds a third of your tightest timeout, make it a job. Also limit who can run an export and how often, since a full-table download repeated every minute is a denial of service you wrote yourself. The headers are defined in RFC 6266 and RFC 4180, and PHP's output functions in the fputcsv reference.