The order export had worked for four years. It stopped working for one customer, who had four hundred thousand orders where the next largest had eleven thousand, and the failure was a blank page and a line in the error log about the memory limit.
The symptom
$ curl -s -o /dev/null -w '%{http_code}n'
'/admin/exports/orders?customer=4471'
500
$ tail -2 /var/log/php/error.log
PHP Fatal error: Allowed memory size of 536870912 bytes
exhausted (tried to allocate 20480 bytes) in Collection.php:1204
# and the shape of the code:
# fetchAll → 400,000 rows as arrays
# ->map() → 400,000 more, as a second array
# implode() → one 180 MB string
# return → the response holds it tooFour copies of the data alive at once, three of which exist only because each step in the pipeline materialises its result. Raising the memory limit had been the previous fix and had worked twice.
Why it happens
Every convenient abstraction in the chain returns an array — the query result, the collection map, the string join, the response body — and each one is correct in isolation. The pipeline only fails when the data is large enough that four copies do not fit.
That threshold moves with the data rather than with the code, which is why an export written against a realistic dataset in 2017 fails in 2021 with no commit to point at. It also means raising the memory limit works, twice, and buys progressively less each time — the third raise on this system would have been to a gigabyte for one customer.
The fix
A generator, and the query that makes it meaningful
// looks like streaming, and is not: the driver has
// already buffered the whole result set in memory
foreach ($pdo->query($sql) as $row) { }
// unbuffered: rows arrive from the server as they are read
$pdo = new PDO($dsn, $user, $pass, [
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
]);
// the constraint: no other query may run on this connection
// until the result is consumed. a lookup mid-loop is fatal.The unbuffered mode is what makes a generator actually stream, and it is the piece people miss — a generator over a buffered result saves the second copy and not the first. The one-query-at-a-time constraint is real and is why this belongs on a dedicated connection rather than switched on globally.
public function rows(int $customerId): Generator
{
$stmt = $this->export->prepare(
'SELECT o.id, o.placed_at, o.total_cents, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = ? ORDER BY o.id'
);
$stmt->execute([$customerId]);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
yield $row;
}
}FETCH_NUM rather than FETCH_ASSOC is a small saving per row that matters at four hundred thousand — the associative version allocates the column names again for every row. Doing the join in SQL rather than loading customers separately is the other half, and it is what keeps this to one pass.
Streaming the response, and the buffering that undoes it
return response()->streamDownload(function () use ($customerId): void {
$out = fopen('php://output', 'w');
$i = 0;
fputcsv($out, ['id', 'placed_at', 'total', 'customer']);
foreach ($this->export->rows($customerId) as $row) {
fputcsv($out, $row);
// push what is buffered out to the client
if (++$i % 1000 === 0) { flush(); }
}
}, 'orders.csv', ['Content-Type' => 'text/csv']);# and the places that will buffer it anyway
fastcgi_buffering off; # nginx holds the whole body
gzip off; # gzip buffers in order to compress
proxy_buffering off; # if there is a proxy in front
# plus output_buffering = Off, or PHP holds it all tooFour independent layers will each happily buffer the whole response, and disabling them one at a time while wondering why memory is still growing is the usual debugging sequence. Disabling gzip is a real cost on a CSV, which compresses extremely well, and is the trade for constant memory.
Sending the headers before the first row is what makes the browser start the download immediately, which matters because a four-hundred-thousand-row export takes ninety seconds and a blank page for ninety seconds is a support ticket.
The point at which it should be a job
streaming a response works, and has limits:
the connection is held for the duration — a php-fpm
child is occupied, and nginx has one too
it cannot be retried: a drop at 90% discards 90%
it cannot be resumed, cached or shared
a timeout anywhere in the chain kills it
over ~60 seconds, or ~100,000 rows, the honest answer is
a job, object storage and an email.// the version that scales past a request
final class GenerateOrderExport implements ShouldQueue
{
public $queue = 'bulk';
public $timeout = 900;
public function handle(): void
{
$tmp = tmpfile();
foreach ($this->rows() as $row) {
fputcsv($tmp, $row);
}
// writeStream, not put — put would take a string
Storage::disk('s3')->writeStream($this->path(), $tmp);
$this->export->markReady($this->path());
Mail::to($this->export->user)->send(new ExportReady($this->export));
}
}writeStream rather than put is what keeps the upload streaming as well — passing a string uploads a string, which reintroduces the original problem at the last step. A temporary file rather than memory is the intermediate, and it is bounded by disk rather than by the memory limit.
The signed URL with an expiry is the last piece and is the part that gets forgotten: an export containing customer data on public object storage is a data breach with a friendly interface.
Verifying it worked
# memory against row count, which should be flat
$ for n in 1000 10000 100000 400000; do
> php artisan export:orders --limit=$n --measure
> done
1000 peak 18.4 MB
10000 peak 18.6 MB
100000 peak 18.9 MB
400000 peak 19.1 MB
# was: 41 MB, 188 MB, 512 MB (fatal), 512 MB (fatal)
$ curl -s -o /tmp/o.csv -w '%{size_download} %{time_total}n'
'/admin/exports/orders?customer=4471'
42104882 88.402
$ wc -l /tmp/o.csv
400001 /tmp/o.csvFlat memory across a forty-fold increase in rows is the assertion, and it belongs in a test with a generated fixture rather than as a manual check. The row count in the downloaded file is the correctness half — a streaming export that silently truncates is the failure mode this design makes possible.
What this costs
A connection held open for ninety seconds and a request that cannot be retried, which is a worker occupied and a user with no way to recover from a dropped connection. That is acceptable for the eleven-thousand-row case and is not for the four-hundred-thousand one, which is why both paths exist and the threshold is a number somebody chose.
Disabling buffering also disables gzip for that route, and a CSV compresses by about eighty per cent — so the streaming version transfers five times the bytes. On a fast connection that is invisible and on a slow one it is the dominant cost, which is another argument for the job-and-download path being the default rather than the escape hatch.