Streaming a large CSV download in PHP without exhausting memory
I need an endpoint that exports a few hundred thousand rows as CSV for download. The naive version builds the whole CSV in a string or an array and then returns it, which blows the memory limit on the big exports. I want the rows to flow out to the client as they are read from the database, with flat memory.
What is the correct pattern, php://output with fputcsv inside a streamed response, and what are the gotchas with output buffering, headers, and the web server holding the whole thing anyway? Want to get this right rather than just bumping memory_limit.
The pattern is to open php://output as a stream and fputcsv each row to it as you read from the database, never building an array. Send your Content-Type and Content-Disposition headers first, then stream. The key is that php://output writes through PHP output buffering to the client, so as long as you are not accumulating rows yourself, PHP memory stays flat regardless of row count. fputcsv also handles the quoting and escaping correctly so you do not hand-roll CSV.
The gotcha that bites everyone is output buffering. If an ob_start is active, either from the framework or from a stray buffer, PHP holds your whole output in memory and your streaming becomes accumulating again. Flush and end any active buffers before you start, and call flush after writing batches so bytes actually leave the process. If memory still climbs while streaming, an output buffer is almost always the culprit.
On the database side, pair this with an unbuffered or chunked read or you just moved the memory problem from the CSV string to the result set. If you load all rows into PHP first and then stream them out, you are still holding everything. Use a server-side cursor or id-based paging so rows arrive in batches, and the streaming write plus the streaming read together give you genuinely flat memory end to end.
The buffering warning was the missing piece, the framework had a buffer active and that is why my first streaming attempt still grew. Ending the buffer before streaming and pairing fputcsv with id-based paging on the query gives me flat memory the whole way down now. Returning a streamed response so the framework does not try to capture the body was also necessary. Works on the big export without touching memory_limit.
Two finishing touches: write a UTF-8 BOM first if the file will be opened in Excel, otherwise non-ascii columns garble, and set the response so the proxy does not buffer the whole thing. Some reverse proxies buffer the full response before sending, which defeats client-side streaming even when PHP is doing it right. A header to disable proxy buffering on that route keeps the download progressive for the user.
```php blocks are runnable.