CSV export endpoint ran out of memory at 500K rows: streaming it with lazyById

Our B2B ordering app lets each customer download their order history as CSV from an API endpoint. In May a wholesale customer clicked Export and got a 500 error. They tried again five times. Every attempt failed after about 40 seconds. Smaller customers were unaffected.

The PHP-FPM log had the answer in one line:

PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 4096 bytes) in /var/www/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php on line 2307

The customer had 512,000 order lines. Our memory_limit for FPM workers was 512 MB, already raised twice in the previous year by people who hit the same error on smaller customers.

Reproducing it

We seeded a local database with 600,000 order lines for one account and called the endpoint:

php artisan db:seed --class=LargeAccountSeeder
curl -s -o /dev/null -w "%{http_code} %{time_total}\n" \
  -H "Authorization: Bearer $TOKEN" http://localhost:8080/api/v1/orders/export

It returned 500 after 38 seconds. Lowering the seeded size to 200,000 rows succeeded but used 310 MB of memory per request, measured with memory_get_peak_usage(true) in a middleware.

What we ruled out

• A single huge row. The largest notes column value was 2 KB. Memory grew linearly with row count.
• Query log. DB::enableQueryLog() was off in production, so logged bindings were not the culprit.
• Raising memory again. Possible, but each FPM worker that runs an export would claim that much memory, and eight concurrent exports on one host would take the box down.

Root cause

The controller did three memory-hungry things in sequence. It loaded every row with get(), which hydrates one Eloquent model per row plus its eager-loaded product relation. It mapped that collection into arrays for the CSV, doubling the footprint. Then it wrote the CSV into a string and returned it as one response body, adding a third copy. Peak memory was roughly three times the result set, and the result set grew with the customer.

What is happening under the hood

An Eloquent model is not cheap. Each one carries attribute arrays, original values for dirty checking, relation containers and casts. For our rows that was about 1 KB of PHP memory per row, versus about 200 bytes of raw data.

The tempting fix is cursor(), which yields one model at a time. On MySQL with PDO's default buffered mode, the driver still receives the full result set into client memory before the first row is yielded. cursor() saves the cost of hydrating all models at once, but not the cost of the raw buffer. Laravel's own documentation recommends lazy() or lazyById() for very large sets because they run a sequence of bounded queries.

lazyById() uses keyset pagination: each query is WHERE id > :last ORDER BY id LIMIT :n. With an index on id (the primary key here), every chunk costs the same regardless of how deep into the table it is. OFFSET pagination gets slower as the offset grows because the database still walks and discards the skipped rows.

The final piece is the response. A streamed response writes each chunk to the client as it is produced, so memory stays bounded by one chunk. Nginx buffers FastCGI responses by default; if the body is large it spills to temp files, which works, but the client sees nothing until much later. Sending X-Accel-Buffering: no tells nginx to pass bytes through.

Environment

Minimal reproduction

// app/Http/Controllers/Api/OrderExportController.php (before)
public function __invoke(Request $request)
{
    $lines = OrderLine::with('product')
        ->where('account_id', $request->user()->account_id)
        ->orderBy('id')
        ->get();                                   // every row, hydrated

    $rows = $lines->map(fn ($l) => [
        $l->id, $l->order_id, $l->product->sku, $l->quantity, $l->unit_price, $l->created_at,
    ]);

    $csv = "id,order_id,sku,quantity,unit_price,created_at\n";
    foreach ($rows as $row) {
        $csv .= implode(',', $row) . "\n";       // whole file in a string
    }

    return response($csv, 200, ['Content-Type' => 'text/csv']);
}

Who is affected

Any endpoint that materialises a result set whose size depends on customer data: exports, reports, "download all" buttons, admin listings without pagination. Node.js, Python and Java versions of this bug look the same: findAll() into an array, then JSON.stringify or a CSV writer into a buffer.

The fix

We rewrote the endpoint to stream keyset chunks directly into the response using plain query builder rows instead of models.

// app/Http/Controllers/Api/OrderExportController.php (after)
use Illuminate\Support\Facades\DB;
use Symfony\Component\HttpFoundation\StreamedResponse;

public function __invoke(Request $request): StreamedResponse
{
    $accountId = $request->user()->account_id;

    return response()->streamDownload(function () use ($accountId) {
        $out = fopen('php://output', 'w');
        fputcsv($out, ['id', 'order_id', 'sku', 'quantity', 'unit_price', 'created_at']);

        DB::table('order_lines as l')
            ->join('products as p', 'p.id', '=', 'l.product_id')
            ->where('l.account_id', $accountId)
            ->select('l.id', 'l.order_id', 'p.sku', 'l.quantity', 'l.unit_price', 'l.created_at')
            ->lazyById(2000, 'l.id', 'id')
            ->each(function ($row) use ($out) {
                fputcsv($out, [
                    $row->id, $row->order_id, $row->sku,
                    $row->quantity, $row->unit_price, $row->created_at,
                ]);
            });

        fclose($out);
    }, 'orders-'.now()->format('Ymd').'.csv', [
        'Content-Type' => 'text/csv; charset=UTF-8',
        'X-Accel-Buffering' => 'no',
        'Cache-Control' => 'no-store',
    ]);
}

Key points:

• lazyById(2000, 'l.id', 'id') pages on the primary key, with the alias needed because of the join.
• Rows are stdClass objects from the query builder, not models, so per-row cost drops by about five times.
• Nothing accumulates: each row is written to php://output and released.
• fputcsv handles quoting, which the old implode did not (a product name with a comma broke columns for one customer last year).

We also added a composite index so each chunk query is an index range scan:

ALTER TABLE order_lines ADD INDEX idx_order_lines_account_id_id (account_id, id);

Timeouts

A streaming export of 500,000 rows takes about 25 seconds, which is inside our request_terminate_timeout of 120 seconds and nginx fastcgi_read_timeout of 120 seconds. Because bytes keep flowing, intermediate proxies with idle timeouts do not cut the connection.

Alternatives and trade-offs

• Async export job. For accounts above 2 million rows we queue a job that writes the CSV to object storage and emails a time-limited download link. It survives client disconnects and can be retried, at the cost of a less immediate experience. We use it as the fallback when a count query exceeds a threshold.
• Paginated API. For API consumers (not humans clicking a button), a cursor-paginated JSON endpoint is better: GET /orders/lines?after=<id>&limit=1000. Clients pull at their own pace.
• Unbuffered PDO queries. Setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false makes cursor() truly stream, but it ties up the connection, blocks other queries on it until the result is fully read, and is easy to get wrong. Keyset chunks are simpler.

How to test the fix

A feature test asserts the output is complete and memory stays bounded:

public function test_export_streams_large_accounts_with_bounded_memory(): void
{
    $account = Account::factory()->create();
    OrderLine::factory()->count(50_000)->for($account)->create();
    $user = User::factory()->for($account)->create();

    $before = memory_get_usage(true);
    $response = $this->actingAs($user)->get('/api/v1/orders/export');
    $csv = $response->streamedContent();
    $peak = memory_get_peak_usage(true) - $before;

    $response->assertOk();
    $this->assertSame(50_001, substr_count($csv, "\n"));
    $this->assertLessThan(64 * 1024 * 1024, $peak);
}

php artisan test --filter=OrderExportTest

Note that streamedContent() collects the stream into a string inside the test, so the memory bound must allow for the test's own copy. We assert a generous ceiling; the real check is the production metric below.

Confirming in production

We added peak memory and row count to the request log for the export route. The wholesale customer's export of 512,000 rows completed in 24 seconds with peak memory of 38 MB. Across all exports the following week, peak memory was flat between 34 and 41 MB regardless of row count, where it previously scaled at roughly 1 MB per 1,000 rows.

grep 'route=orders.export' storage/logs/laravel-2025-05-*.log \
  | jq -r '[.context.rows, .context.peak_mb] | @tsv' | sort -n | tail -5

We then lowered FPM memory_limit back to 256 MB.

Regression guard

• The feature test above runs in CI.
• A static check in code review: ->get() or ->all() on a query without limit, paginate or lazy in a controller under Api/ needs a comment explaining why.
• An alert fires when any request logs peak_mb above 200.

Security implications

Unbounded exports are a denial-of-service lever: a user with a large account, or an attacker who creates many records, could tie up FPM workers and memory with repeated requests. We added a per-user rate limit of three exports per 10 minutes. The query scopes on the authenticated user's account_id, never on a request parameter, so streaming does not change who can see which rows. CSV injection is a separate risk for spreadsheets: we prefix cells starting with =, +, - or @ with a single quote before writing.

Prevention checklist

• Assume every list a customer owns will eventually be huge.
• Use keyset chunks (lazyById, chunkById) for large reads, not get() or offset pages.
• Stream responses and disable proxy buffering for long downloads.
• Log peak memory per request on heavy routes.
• Move very large exports to background jobs with a download link.

References

• Laravel 11 docs, Chunking and lazy streaming results: https://laravel.com/docs/11.x/queries#chunking-results
• Laravel 11 docs, Eloquent cursors and memory: https://laravel.com/docs/11.x/eloquent#cursors
• Laravel 11 docs, Streamed downloads: https://laravel.com/docs/11.x/responses#streamed-downloads
• PHP manual, PDO MySQL buffered queries: https://www.php.net/manual/en/mysqlinfo.concepts.buffering.php
• nginx docs, X-Accel-Buffering and fastcgi_buffering: https://nginx.org/en/docs/http/ngx_http_fastcgi_module.html#fastcgi_buffering