Reporting Safely: Async Jobs, CSV Streaming, and Scheduled Reports

Recognize why large reporting queries are dangerous to run synchronously inside a web request, move heavy report generation into asynchronous background jobs, stream large result sets to CSV without loading them entirely into memory, and implement cron-triggered recurring reports.

Reporting features have a particular way of quietly becoming an application's biggest reliability risk: they start out as a simple 'export to CSV' button that works fine in testing against a small dataset, and months later, once real production data has grown, that same button times out the request, exhausts PHP's memory limit, or locks up the database for every other user while it churns through millions of rows — all triggered by one person clicking a button at the wrong moment. This lesson covers the patterns that keep reporting safe at scale: why a report belongs in a background job rather than the request/response cycle, how to stream a large export to CSV without ever holding the whole result set in memory, and how to schedule recurring reports so stakeholders get them automatically instead of someone manually clicking 'export' every Monday morning.

Why Large Reports Don't Belong in a Web Request

A typical PHP web request has an implicit contract: it should complete quickly — ideally well under a second, and almost always under the request timeout configured in your web server or PHP-FPM (max_execution_time, request_terminate_timeout, or an upstream load balancer's own timeout, whichever is shortest). A reporting query that joins several large tables, aggregates millions of rows, and formats the results can easily take tens of seconds or minutes — far past any of those timeouts — and when it hits one, the user gets a blank error page after waiting the entire time, with no report to show for it.

Even setting timeouts aside, a long-running query held open for the duration of a slow report ties up a database connection (and, depending on the web server model, a PHP-FPM worker process) for that entire time. PHP-FPM pools have a finite number of worker processes; if several users trigger heavy reports simultaneously, or one report runs long enough, you can exhaust the available workers and cause ordinary, unrelated requests to queue up or fail — a reporting feature degrading the performance of the entire site for everyone, not just the person who requested the report.

Heavy aggregate queries can also compete for the same database resources — buffer pool memory, I/O bandwidth, and in some cases row or table locks — that the production database needs for everyday transactional traffic (checkouts, logins, ordinary page loads). A report that scans a large fraction of a big table can measurably slow down unrelated queries running concurrently against the same database, which is precisely the failure mode that makes 'someone ran a big report and now the whole site is slow' a recurring, real production incident at companies that don't isolate reporting from their primary transactional path.

• Long-running queries risk hitting request/PHP-FPM/load-balancer timeouts, producing a blank failure with no result after a long wait. • A slow synchronous request holds a database connection and a PHP-FPM worker for its entire duration — a finite, shared resource. • Several concurrent heavy reports can exhaust available workers, degrading or failing unrelated requests for other users. • Large aggregate queries compete with everyday transactional traffic for database I/O and memory, risking a site-wide slowdown from a single report.

Running Reports Asynchronously

The fix is architectural: a request for a report shouldn't run the report — it should enqueue a job that will run the report, and return an immediate response ('Your report is being generated, we'll notify you when it's ready'). A background worker process, running independently of the web server's request/response cycle, picks the job off the queue and does the actual heavy lifting: running the query, formatting the output file, and saving it somewhere durable (object storage, a designated directory, a database blob for smaller reports). When the job finishes, the user is notified — an email with a download link, an in-app notification, a webhook to another system — rather than being kept waiting on an open HTTP connection the entire time.

This pattern has several compounding benefits beyond just avoiding timeouts. The worker process can be tuned specifically for long-running work (a much higher execution time limit, more generous memory limits) without loosening those same limits for the ordinary web-facing PHP-FPM pool, keeping the constraints appropriate to each workload. Workers can also run against a read replica of the database rather than the primary, so even an expensive report query has zero impact on the primary database that serves live transactional traffic. And because the job is now a durable, trackable unit of work (with an ID, a status, retry semantics), a failed report can be retried automatically or investigated from a job dashboard, instead of just vanishing as a failed HTTP request the user has to notice and re-trigger themselves.

The user-facing flow typically looks like: click 'Generate Report' → the request enqueues a job and immediately returns, showing a 'processing' status; the worker runs the job and writes a status record (pending → running → complete/failed) the frontend can poll or that pushes an update via websocket/notification; once complete, the user gets a download link (often a time-limited signed URL to the generated file in object storage) rather than the file itself streamed through the original request.

• The web request should only enqueue a report-generation job and return immediately — never run the report inline. • A separate worker process (with its own, more generous execution-time and memory limits) performs the actual report generation. • Workers can query a read replica instead of the primary database, eliminating impact on live transactional traffic entirely. • Notify the user when the report is ready (email, in-app notification, signed download link) rather than making them wait on an open connection.

The Memory Problem: Loading an Entire Result Set at Once

Even once report generation has moved to a background job with no request timeout to worry about, a second, independent problem remains: memory. The naive way to build a CSV export in PHP is to fetch the entire result set into a PHP array ($rows = $pdo->query(...)->fetchAll();), then loop over that array building CSV lines, then send or save the whole string. For a modest result set this works fine; for a report over hundreds of thousands or millions of rows, holding the entire result set as PHP arrays/objects in memory at once can easily exceed memory_limit and fatally crash the job, regardless of how much execution time you've allowed it.

The fix is to never materialize the full result set in memory at all: fetch rows from the database one at a time (or in modest batches) using an unbuffered or cursor-based fetch, write each row to the output as soon as it's fetched, and let the previous row's memory be garbage-collected before the next one is even read. This turns memory usage from 'proportional to the total number of rows' into 'proportional to one row (or one small batch) at a time' — a report over ten million rows costs essentially the same memory as a report over ten rows, because at any instant only a tiny, constant slice of the data is actually held in memory.

PHP's PDO supports this via PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false for MySQL (by default, PDO's MySQL driver buffers the entire result set client-side before you can iterate it — this flag disables that buffering so rows stream from the database connection as you fetch them) combined with a foreach or while ($row = $stmt->fetch()) loop that processes and discards each row immediately rather than accumulating them into an array. PostgreSQL's PDO driver, and most non-MySQL drivers, are unbuffered by default, so this specific flag is a MySQL-specific gotcha worth remembering explicitly.

• fetchAll() loads the entire result set into a PHP array at once — memory usage scales with total row count, which can exhaust memory_limit on large exports. • Fetch and process rows one at a time (or in small batches), writing each to output immediately, so memory usage stays roughly constant regardless of total rows. • MySQL's PDO driver buffers the full result set by default — disable that with PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false to stream rows instead. • PostgreSQL and most other PDO drivers are unbuffered by default; the buffering gotcha is specifically a MySQL behavior to be aware of.

Streaming CSV Output with fputcsv

PHP's fputcsv() function writes one properly-escaped CSV row directly to any writable stream resource — including a plain file handle opened with fopen(), or, when streaming a CSV directly as an HTTP download response, the special stream php://output, which writes straight to the response body being sent to the browser as it's generated, rather than building the response as one big string in memory first. Combining fputcsv() writing to php://output with an unbuffered database fetch loop is the complete pattern for generating an arbitrarily large CSV export using an essentially constant, tiny amount of memory.

A few details matter for this to work correctly as an HTTP download: the response headers (Content-Type: text/csv, Content-Disposition: attachment; filename="report.csv") must be sent before any output begins, since headers can't be modified once the body starts streaming; output buffering should generally be disabled or flushed periodically (ob_flush(); flush(); after some number of rows) so the browser actually starts receiving and can start saving/displaying the file progressively rather than the web server holding everything until the very end; and set_time_limit(0) (or an appropriately high timeout in the worker/CLI context) is often needed since a genuinely large export may take longer than PHP's default execution time limit even though it's no longer eating unbounded memory.

The same streaming principle extends beyond CSV: any format that can be constructed incrementally (streaming JSON with careful bracket/comma handling, or a streamed XML writer) benefits from the identical idea — never build the entire output as one in-memory string or array when the data volume is unbounded or large; write incrementally to the output stream as data becomes available.

• fputcsv($handle, $row) writes one correctly-escaped CSV row directly to a stream — no manual comma/quote-escaping needed. • php://output is a writable stream representing the current response body — writing to it streams data straight to the client as it's generated. • Set Content-Type and Content-Disposition headers before any output begins; headers can't change once the body has started. • Periodically flush output buffers (ob_flush(); flush();) so the client receives data progressively instead of all at the very end. • The same 'never build the full output in memory' principle applies to any incrementally-constructible format, not just CSV.

Scheduled and Recurring Reports

Many reports aren't ad hoc — a business wants the same report, on the same schedule, delivered automatically without anyone remembering to click a button: a nightly sales summary emailed to the sales team, a weekly signup report for a growth team, a monthly financial reconciliation report for finance. These are implemented with a cron job (or an equivalent scheduled-task mechanism) that triggers a PHP CLI script on a fixed schedule, independent of any web request or user action at all.

The scheduled script follows the exact same principles as an on-demand report: it should stream large result sets rather than load them entirely into memory, and it typically writes its output to a file (or generates it as an email attachment) before delivering it — commonly via a mail-sending service's API, since attaching a genuinely large CSV directly to an email is itself often impractical, in which case the more scalable pattern is uploading the report to object storage and emailing a signed, time-limited download link instead of the raw file.

Operational care matters here too: a scheduled report job should log its own success/failure clearly (so a silently-failing nightly report doesn't go unnoticed for weeks), should be idempotent or safe to re-run if it's manually retried after a failure, and should alert someone (rather than fail silently) if it doesn't complete — the same reliability instincts that apply to any unattended, recurring background process, since nobody is watching a terminal at 2 AM when the cron job actually runs.

• Recurring reports (nightly sales summary, weekly signup report) are triggered by cron running a dedicated PHP CLI script, independent of any web request. • The scheduled script uses the same streaming/memory-safe techniques as an on-demand report, at whatever scale the data has grown to. • For large reports, upload to object storage and email a signed download link rather than attaching a potentially huge file directly to an email. • Log success/failure clearly and alert on failure — an unattended cron job that silently fails can go unnoticed for a long time.

Streaming a CSV Response Without Buffering the Result Set

Headers must be sent before any fputcsv() output begins. PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false is required on the MySQL driver for this to genuinely stream rather than buffer the whole result set first.

<?php
declare(strict_types=1);

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="sales_report.csv"');

$stmt = $pdo->prepare('SELECT id, customer, total_cents, created_at FROM orders');
$stmt->execute();

$out = fopen('php://output', 'w');
fputcsv($out, ['id', 'customer', 'total_cents', 'created_at']); // header row

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, $row); // write and discard immediately — no array accumulation
}
fclose($out);

Code Examples

The web request only creates a job record and pushes to a queue — it returns to the user in milliseconds regardless of how long the report will actually take. All the expensive work, including its own error handling and status tracking, happens later in a worker process with no request timeout to worry about.

$queue->push('generate_sales_report', [...]); // enqueue, don't run inline

Disabling buffered queries and fetching one row at a time — rather than calling fetchAll() — means memory usage stays flat regardless of whether the report covers a thousand orders or ten million; each $row is fully written and discarded before the next one is even fetched from the database connection. Writing to a temp file (rather than php://output) here because this runs in a background worker, not a live HTTP response — the file is uploaded to object storage afterward and a download link is emailed to the user.

<?php
declare(strict_types=1);

// Web request handler — returns almost instantly, never runs the query itself
function requestSalesReport(int $userId, string $startDate, string $endDate, Queue $queue): array
{
    $jobId = bin2hex(random_bytes(16));

    saveReportJobStatus($jobId, 'pending', $userId);

    $queue->push('generate_sales_report', [
        'job_id' => $jobId,
        'user_id' => $userId,
        'start_date' => $startDate,
        'end_date' => $endDate,
    ]);

    return ['job_id' => $jobId, 'status' => 'pending'];
}

// Worker process — runs independently, with its own generous time/memory limits
function handleGenerateSalesReport(array $payload): void
{
    saveReportJobStatus($payload['job_id'], 'running', $payload['user_id']);

    try {
        $filePath = streamSalesReportToFile($payload['start_date'], $payload['end_date']);
        $downloadUrl = uploadToObjectStorage($filePath, "reports/{$payload['job_id']}.csv");

        saveReportJobStatus($payload['job_id'], 'complete', $payload['user_id'], $downloadUrl);
        notifyUserReportReady($payload['user_id'], $downloadUrl);
    } catch (Throwable $e) {
        saveReportJobStatus($payload['job_id'], 'fai
…

<?php
declare(strict_types=1);

function streamSalesReportToFile(string $startDate, string $endDate): string
{
    $pdo = new PDO(
        'mysql:host=localhost;dbname=shop;charset=utf8mb4',
        getenv('DB_USER'),
        getenv('DB_PASS'),
        [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false] // stream rows, don't buffer whole result
    );

    $stmt = $pdo->prepare(
        'SELECT o.id, o.created_at, c.name AS customer, o.total_cents
         FROM orders o
         JOIN customers c ON c.id = o.customer_id
         WHERE o.created_at BETWEEN :start AND :end
         ORDER BY o.created_at'
    );
    $stmt->execute(['start' => $startDate, 'end' => $endDate]);

    $tmpPath = tempnam(sys_get_temp_dir(), 'report_');
    $out = fopen($tmpPath, 'w');
    fputcsv($out, ['Order ID', 'Date', 'Customer', 'Total ($)']);

    $rowCount = 0;
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($out, [
            $row['id'],
            $row['created_at'],
            $row['customer'],
            number_format($row['total_cents'] / 100, 2),
        ]);
        // Only one row is ever held in memory at a time; $row is discarded each
        // iteration, so this loop's me
…

Synchronous vs Asynchronous Reporting

Moving report generation off the request path removes both the timeout risk and the shared-resource contention.

Synchronous (risky):                 Asynchronous (safe):
 ┌──────────┐  runs query  ┌─────┐    ┌──────────┐ enqueue  ┌───────┐  worker   ┌────────────┐
 │ Browser   │ ───────────▶│ DB   │    │ Browser   │ ────────▶│ Queue  │ ────────▶│ Report worker│
 │ (waits...)│◀── times out└─────┘    │ (instant  │          └───────┘          │ streams rows │
 └──────────┘   or crashes            │  response)│                             │ to CSV file  │
                                       └──────────┘◀── notified when ready ──────└────────────┘

Important Rules

Never run a heavy aggregate/reporting query synchronously inside a web request — enqueue a background job instead.

Run report-generation workers against a read replica where possible, to fully isolate reporting load from live transactional traffic.

Never call fetchAll() (or build an equivalent full in-memory array) for a result set that could grow large — fetch and process one row at a time.

Disable PDO's MySQL result-set buffering (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false) before streaming a large query's results.

Send Content-Type/Content-Disposition headers before writing any CSV output, since headers can't change once the response body has started.

Nightly Reports as a Standard Ops Pattern

Many SaaS products send an automated nightly or weekly summary email (new signups, revenue, churn) generated by a cron-triggered script that streams the underlying query results directly into the report rather than loading them into memory, specifically so the same script keeps working unmodified as the company's data volume grows from thousands to millions of rows.

$rows = $pdo->query('SELECT * FROM orders')->fetchAll(); $csv = ''; foreach ($rows as $row) { $csv .= implode(',', $row) . "\n"; } echo $csv;

$stmt = $pdo->query('SELECT * FROM orders'); $out = fopen('php://output', 'w'); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { fputcsv($out, $row); } fetchAll() combined with building one giant string holds the entire result set (and the entire output) in memory simultaneously — for a large orders table this can exhaust memory_limit and crash the script; streaming one row at a time keeps memory usage constant.

// Button click handler runs the report inline in the request function exportReport() { $data = runHeavyReportQuery(); return csvResponse($data); } function exportReport() { $queue->push('generate_report', [...]); return jsonResponse(['status' => 'processing']); }

Running the heavy query synchronously in the request risks hitting execution-time limits, ties up a PHP-FPM worker and database connection for the report's full duration, and can slow down the database for every other concurrent user.

What is the main risk of running a large reporting query synchronously inside a web request? A. PHP cannot execute SELECT queries longer than 100 characters B. It risks hitting execution-time timeouts, ties up a worker/connection for its full duration, and can slow the database for other concurrent users C. CSV files can only be generated from a CLI script, never from a web request D. The database will automatically reject any query that takes longer than one second

B. It risks hitting execution-time timeouts, ties up a worker/connection for its full duration, and can slow the database for other concurrent users — A long-running synchronous report risks timeouts, occupies a limited PHP-FPM worker and database connection the whole time, and its resource usage can degrade unrelated concurrent traffic on the same database.

What should a web request do when a user asks for a large report, instead of generating it inline? A. Increase max_execution_time to an unlimited value and run the query anyway B. Enqueue a background job to generate the report and return an immediate response, notifying the user when it's ready C. Return only the first 100 rows and silently drop the rest D. Cache the previous report indefinitely instead of generating a new one

B. Enqueue a background job to generate the report and return an immediate response, notifying the user when it's ready — Enqueuing the work and returning immediately keeps the request fast and avoids tying up shared resources; a background worker with its own generous limits handles the actual heavy lifting.

Why does calling fetchAll() on a very large result set risk crashing a PHP script? A. fetchAll() is deprecated and throws an error in PHP 8 B. It loads every row into a PHP array in memory at once, so memory usage scales with the total row count and can exceed memory_limit C. fetchAll() only works with PostgreSQL, not MySQL D. It automatically retries the query multiple times, multiplying memory usage

B. It loads every row into a PHP array in memory at once, so memory usage scales with the total row count and can exceed memory_limit — fetchAll() materializes the entire result set as PHP data structures simultaneously; for a result set with millions of rows, this can exceed the configured memory_limit and fatally crash the script.

What does writing to the php://output stream with fputcsv() accomplish that building a big string with echo does not? A. It compresses the CSV file automatically B. It streams each row directly to the response body as it's generated, instead of building the entire output in memory before sending anything C. It changes the file format from CSV to JSON D. It skips writing the header row automatically

B. It streams each row directly to the response body as it's generated, instead of building the entire output in memory before sending anything — php://output represents the outgoing response body; writing to it with fputcsv() sends each row as soon as it's produced, so memory usage stays proportional to one row at a time rather than the whole export.

Write a PHP function streamOrdersCsv(PDO $pdo, string $outputPath) that queries all rows from an 'orders' table using an unbuffered PDO fetch loop, and writes them to a CSV file at $outputPath using fputcsv(), without ever calling fetchAll() or holding the full result set in memory.

<?php function streamOrdersCsv(PDO $pdo, string $outputPath): void { $stmt = $pdo->query('SELECT id, customer, total_cents, created_at FROM orders'); $out = fopen($outputPath, 'w'); fputcsv($out, ['id', 'customer', 'total_cents', 'created_at']); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { fputcsv($out, $row); } fclose($out); }

Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false on the connection (or on the specific statement), open the output file with fopen(), and use a while ($row = $stmt->fetch()) loop.