The Friday Evening Production Crash

A few years ago, I got an urgent support ticket on a Friday evening. A client was trying to download their annual sales report. In our local development environment with 100 rows, the export worked perfectly. But in production, with over 500,000 records, the application threw a 500 internal server error. The logs showed the classic PHP error: Allowed memory size of 512MB exhausted.

Many developers try to fix this by increasing the memory limit in php.ini to 1GB or even 2GB. But that is just a temporary patch. If your database grows, your application will crash again. The real solution is to stream the data. Instead of loading all records into RAM and generating a massive file, we can fetch records in small chunks and send them directly to the browser.

In this guide, I will show you how to combine Laravel's LazyCollection and Symfony's StreamedResponse to export millions of rows with flat memory usage.

Why Traditional Exports Crash Your Server

To understand the solution, we must first understand why the traditional approach fails. In a standard Laravel setup, developers often write code like this:

public function export() {
    $orders = Order::all(); // Loads all records into RAM
    $csvData = "";

    foreach ($orders as $order) {
        $csvData .= $order->id . "," . $order->amount . "\n";
    }

    return response($csvData, 200, [
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename="export.csv"',
    ]);
}

This code has two major memory bottlenecks:

• Eloquent Hydration: Order::all() fetches every row from the database and instantiates a PHP object for each row. If you have 100,000 rows, PHP has to manage 100,000 heavy objects in memory.
• String Accumulation: The $csvData string keeps growing in size. PHP has to allocate a continuous block of memory to hold this massive string before sending it to the user.

The Solution: LazyCollection + StreamedResponse

To solve this, we need to stream the response. Streaming means we open a connection to the browser, write a single row of CSV data, flush it to the network, and immediately free up that memory. The browser receives the file progressively, and our server's RAM usage remains almost flat.

1. Using LazyCollection to Fetch Rows One by One

Laravel provides a cursor() method on Eloquent queries. Instead of returning a standard collection, cursor() uses PHP generators to yield one database row at a time. It wraps this logic inside a LazyCollection. Under the hood, Laravel only keeps a single database record in memory at any given moment.

2. Using StreamedResponse to Send Data Progressively

Symfony's StreamedResponse allows us to define a callback function. Inside this callback, we can write data directly to PHP's output stream (php://output). The web server sends this output to the client in real-time.

Step-by-Step Implementation

Let's write a practical, clean controller action to export a large dataset of orders. I will also include eager loading to prevent N+1 query issues during the stream.

namespace App\Http\Controllers;

use App\Models\Order;
use Symfony\Component\HttpFoundation\StreamedResponse;

class ExportController extends Controller
{
    public function exportOrders()
    {
        // 1. Define the headers for file download
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="orders_export.csv"',
            'Cache-Control' => 'no-cache, no-store, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0',
        ];

        // 2. Create the StreamedResponse
        $response = new StreamedResponse(function () {
            // Open the PHP output stream
            $handle = fopen('php://output', 'w');

            // Add the CSV header row
            fputcsv($handle, ['Order ID', 'Customer Name', 'Amount', 'Status', 'Created At']);

            // Fetch records using cursor() to keep memory low
            // We eager load the 'user' relationship to prevent N+1 queries
            Order::with('user')->cursor()->each(function ($order) use ($handle) {
                fputcsv($handle, [
                    $order->id,
                    $order->user->name ?? 'Guest',
                    $order->amount,
                    $order->status,
                    $order->created_at->toDateTimeString(),
                ]);
            });

            // Close the stream
            fclose($handle);
        }, 200, $headers);

        return $response;
    }
}

Crucial Database Tweaks for MySQL

Even with cursor(), you might still run into memory issues if you are using MySQL. By default, the PHP PDO driver buffers queries. This means MySQL will fetch the entire result set into the PHP process memory before Laravel can even start looping through it.

To fix this, we need to use unbuffered queries. When using a cursor in Laravel, you can disable buffering by setting the PDO configuration or using a raw connection. However, the easiest way to handle extremely large datasets in MySQL is to chunk your cursor or configure your database connection to disable buffering for this specific operation:

// Disable PDO buffering for this connection session

DB::connection()->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

Order::cursor()->each(function ($order) {
    // Process row
});

Note: If you disable buffered queries, you cannot run any other database queries on that same connection while the cursor loop is active. This is why eager loading your relationships beforehand is absolutely critical.

Common Pitfalls and How to Avoid Them

Over the years, I have seen developers make a few common mistakes when setting up streamed exports. Keep these in mind:

1. Running N+1 Queries Inside the Loop

If you access a relationship inside the each() loop that was not eager-loaded, Laravel will execute a new database query for every single row. If you are exporting 100,000 rows, this means 100,000 extra queries. Always use with() to load your relations before calling cursor().

2. Leaving Query Logging Enabled

In your local environment, or if you have certain debug packages enabled in production, Laravel logs every executed query in memory. For a massive loop, this query log will quickly consume all your RAM. You can disable query logging explicitly before starting the export:

use Illuminate\Support\Facades\DB;

DB::disableQueryLog();

3. Execution Timeouts

Streaming saves memory, but it still takes time to process millions of rows. If your PHP configuration has a 30-second execution limit (max_execution_time), the script will timeout mid-way. You might need to increase the execution limit at the start of your controller action:

set_time_limit(0);

When to Use Queues Instead of Streaming

Streaming is excellent when a user needs an immediate download and the dataset takes less than a minute or two to process. However, if your export has millions of rows, complex calculations, or third-party API calls for each row, streaming directly to the browser is a bad user experience. The connection might drop, or the browser might timeout.

In those extreme scenarios, I prefer to push the export task to a Laravel queue. The background worker generates the CSV file, uploads it to AWS S3, and emails a download link to the user when it is ready.

Conclusion

Handling large data exports does not require expensive server upgrades. By replacing standard Eloquent collections with LazyCollection and utilizing Symfony's StreamedResponse, you can stream massive files while keeping your server's memory usage low and stable. Try refactoring your heavy export endpoints with this approach, and your production servers will thank you.

Common Questions

What is the difference between chunk() and cursor() in Laravel?

Chunk() retrieves a specific number of records at a time (e.g., 1000) and processes them in batches. Cursor() uses PHP generators to fetch and instantiate only one Eloquent model at a time, making it even more memory-efficient for continuous streams.

Will StreamedResponse work with Laravel Horizon or queues?

No, StreamedResponse is meant for immediate HTTP responses sent directly to the browser. For queue-based exports, you should write the data to a file on disk (like S3) using Laravel's Storage facade and notify the user when finished.

How do I prevent PHP timeout issues during large exports?

You can use set_time_limit(0) at the beginning of your export controller to disable PHP's execution time limit, but make sure your web server (like Nginx or Apache) also has a sufficiently high timeout configuration.