WordPress Performance: Streaming Large REST API Exports

Last month, I was debugging a client’s plugin that crashed every time they tried to export a CSV of 50,000 orders. The site was running on a standard 256MB PHP memory limit, and the get_results() call was trying to pull the entire order history into a single, massive array. It’s a classic trap: WordPress developers often treat the database like a local variable, forgetting that PHP wasn't designed to hold thousands of complex objects in RAM simultaneously.

If you’re building data-heavy plugins, you need WordPress performance strategies that go beyond simple caching. Relying on standard wpdb methods for large datasets is a recipe for Fatal error: Allowed memory size exhausted.

Why Standard Queries Fail

When you use $wpdb->get_results(), WordPress fetches all rows, hydrates them into objects, and dumps them into an array. If each order row is 5KB, 50,000 rows consume roughly 250MB just for the raw data—before you even start formatting the JSON for your REST API.

I initially tried increasing the memory_limit to 1GB. It worked for a few weeks, but as the store grew, the same crash returned. It wasn't a resource issue; it was a fundamental architectural flaw. I needed to move away from "fetch-all" and toward database streaming.

Implementing Generators for Memory Optimization

The solution is to use PHP generators combined with unbuffered queries. Generators allow you to iterate over data without loading it all at once, effectively keeping your memory footprint flat regardless of the dataset size.

First, we need to ensure we aren't using the buffered query mode. By default, mysqli buffers the result set. We can bypass this by executing a raw query through wpdb using a custom connection or by carefully managing the wpdb object.

Here is how I implemented a streamable export in my latest project:

PHP
function get_order_generator( $wpdb ) {
    #6A9955">// Unbuffered query to prevent MySQL from loading everything into RAM
    $wpdb->query( "SET @stream_query = 'SELECT * FROM {$wpdb->prefix}posts WHERE post_type = \"shop_order\"'" );
    $results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}posts WHERE post_type = 'shop_order'", OBJECT_K );

    #6A9955">// Using a generator to yield one row at a time
    foreach ( $results as $row ) {
        yield $row;
    }
}

Wait—the code above still uses get_results(), which buffers. To truly achieve memory optimization, you must use a direct mysqli resource if you're dealing with millions of rows. For standard plugin use cases, you can emulate streaming by using LIMIT and OFFSET inside a generator, which keeps memory usage low at the cost of slight database overhead.

Refactoring for Scale

When your data grows, even LIMIT/OFFSET becomes slow because of index scanning. Instead, I use a "keyset pagination" approach. This is much faster and pairs perfectly with PHP generators.

PHP
function stream_orders_efficiently( $last_id = 0 ) {
    global $wpdb;
    
    while ( true ) {
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}posts WHERE ID > %d AND post_type = 'shop_order' ORDER BY ID ASC LIMIT 100",
            $last_id
        ) );

        if ( empty( $rows ) ) break;

        foreach ( $rows as $row ) {
            $last_id = $row->ID;
            yield $row;
        }
    }
}

By yielding one row at a time, your REST API endpoint can iterate through the generator and stream the JSON output directly to the output buffer. This keeps your memory usage roughly constant—around 10-15MB—whether you’re processing 100 rows or 100,000.

Integrating with REST API

To serve this via the REST API, you shouldn't return a standard array. Instead, use a callback that writes directly to php://output. This is crucial for database streaming because it sends data to the client incrementally.

If you are dealing with complex data relationships, consider how WordPress Row-Level Security: Implementing Database Query Filtering affects your streaming. If your query filters are complex, ensure they don't break the index being used for your pagination, or you'll see a massive spike in query time.

What I’d Do Differently

Looking back, I wasted two days trying to optimize the JSON encoding process before realizing the bottleneck was the database driver itself. If I were starting a new project today, I’d look into WordPress wpdb Custom Database Driver: Scaling External Data Sources to see if I could offload the heavy lifting to a specialized analytics store like ClickHouse or an indexed Elasticsearch instance.

Also, don't forget to check your WordPress Options API: Understanding Autoloading and Performance settings. Sometimes, the memory overhead isn't the export itself, but the massive amount of autoloaded options being pulled into memory on every single request.

Streaming data is a mindset shift. It forces you to stop thinking about "the results" as a single object and start thinking about them as a continuous flow of information. It’s harder to debug, but it’s the only way to build truly scalable WordPress plugins.

FAQ

Q: Can I use WP_Query for streaming? A: Generally, no. WP_Query is designed to instantiate WP_Post objects, which is extremely memory-intensive. For large exports, stick to $wpdb and raw SQL.

Q: Does this work with wp_json_encode()? A: Not directly. wp_json_encode() expects the full data structure. If you are streaming, you’ll need to manually construct the JSON string or use a library that supports streaming JSON encoders.

Q: Is there a risk of hitting execution time limits? A: Yes. Streaming doesn't change the script execution time. Use set_time_limit( 0 ) carefully, and ensure your database indexes are optimized so each chunk fetch happens in milliseconds.