Handling Large Data Exports: Performance, Queues, and Streaming

Previously in this course, we covered Database Connection Pooling to keep our database interactions efficient. This lesson builds on that foundation by addressing the "Export Problem": when a user requests a report containing hundreds of thousands of rows, traditional request-response cycles fail due to PHP memory limits and execution timeouts.

To build a production-grade SaaS, you must treat data exports as asynchronous background tasks.

The Problem with Synchronous Exports

When you attempt to fetch 50,000 Eloquent models and convert them into a CSV in a single request, you hit two walls:

1. Memory Exhaustion: Eloquent models are heavy objects. Loading them all into an array consumes hundreds of megabytes.
2. Execution Timeouts: Browsers and load balancers (like Nginx/AWS ALB) will drop the connection if the server takes longer than 30–60 seconds to respond.

We solve this by decoupling the export process into a queued job that streams data directly to a cloud storage bucket (like S3).

Architecting the Async Export Flow

Instead of returning a file directly to the user, the flow becomes:

1. Request: The user triggers an export; the controller dispatches a GenerateReportJob.
2. Queue: The worker picks up the job and begins streaming data.
3. Storage: The worker writes the file to S3 using League\Csv or Laravel's built-in Storage facade.
4. Notification: Once finished, the job triggers an event to notify the user (via WebSockets or Email) that their download is ready.

Worked Example: Streaming with League\Csv

First, ensure you have league/csv installed. It is the industry standard for memory-efficient CSV generation.

PHP
namespace App\Jobs;

use App\Models\User;
use League\Csv\Writer;
use Illuminate\Support\Facades\Storage;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;

class GenerateUserReport implements ShouldQueue
{
    use Queueable;

    public function handle()
    {
        $filePath = 'exports/users-' . now()->timestamp . '.csv';
        $stream = fopen('php:#6A9955">//temp', 'r+');
        $csv = Writer::createFromStream($stream);

        #6A9955">// Add header
        $csv->insertOne(['ID', 'Name', 'Email', 'Created At']);

        #6A9955">// Use chunkById to keep memory usage constant
        User::query()->chunkById(1000, function ($users) use ($csv) {
            foreach ($users as $user) {
                $csv->insertOne([$user->id, $user->name, $user->email, $user->created_at]);
            }
        });

        #6A9955">// Upload to S3
        Storage::disk('s3')->put($filePath, $stream);
        fclose($stream);

        #6A9955">// Dispatch event to notify user...
    }
}

By using chunkById(), we ensure that only 1,000 records reside in memory at any given time, regardless of whether we are exporting 1,000 or 1,000,000 rows.

Why Streaming is Superior

Hands-on Exercise

1. Create a new ExportReport job.
2. Implement the chunkById pattern to fetch records from your core domain model.
3. Use a temporary local file or php://temp to construct the CSV.
4. Upload the final file to your configured Storage disk.
5. Add a Notification trigger at the end of the handle() method to inform the user via your frontend (e.g., using Laravel Echo).

Common Pitfalls

• Ignoring Memory Limits: Even with chunkById, if you eager-load relationships inside the loop, you will blow up your memory. Only select the columns you need: User::select(['id', 'name', 'email'])->chunkById(...).
• Database Timeouts: For extremely large exports, the database connection might time out during the loop. If this happens, ensure your DB_READ_TIMEOUT is configured appropriately or perform periodic reconnect() calls if necessary.
• Storage Latency: Do not write directly to the local disk of your web server; use php://temp or a dedicated temporary directory that is cleared after the job finishes to avoid filling up server storage.
• Lack of User Feedback: Never leave a user hanging. Always provide a "Processing..." state in the UI and notify the user once the file is ready for download.

Recap

Performance in data-heavy SaaS applications relies on avoiding "bloated" requests. By moving exports to the background, you keep your web processes lean and your application responsive. Always stream using chunking to maintain a flat memory profile, and leverage your queue system to handle the heavy lifting.

Up next, we will look at Security Header Configuration to ensure our exported files and browser sessions remain locked down against modern web threats.