WordPress performance: Database-level request coalescing for REST API

Last month, I spent about three days refactoring a REST API endpoint that was consistently timing out under load. Every time a client requested a list of products with their associated metadata, the server would trigger an N+1 query storm, hitting the database for every single item in the loop. The fix wasn't just caching; it was implementing request coalescing to batch those database operations into a single, efficient query.

If you’re building high-traffic endpoints, you’ve likely seen the performance degradation caused by fetching child data inside a foreach loop. When you're managing complex data structures, understanding WordPress performance: implementing database partitioning for scale is a great start, but it won't save you from inefficient application-level logic.

The N+1 Problem in REST API Hydration

When you register a custom field in the REST API using register_rest_field, WordPress executes your callback for every post in the collection. If your callback hits the database to fetch meta or related terms, you're looking at a linear growth in query count as the collection size increases.

We first tried solving this by wrapping the queries in a simple static variable cache. While that helped with repeat requests, it didn't solve the initial hydration phase. The server was still firing 50+ queries for a single page of results. We needed to move toward a pattern where we collect all necessary IDs first and then perform one bulk lookup.

Implementing Request Coalescing

Request coalescing effectively pauses the execution of individual callbacks, collects the required data points (like post IDs), and executes a single query to hydrate the entire set.

Here is how I implemented a basic version of this in a recent project. I use a private class variable to track the "pending" IDs and a filter on rest_prepare_{post_type} to trigger the bulk lookup.

PHP
class ProductHydrator {
    private static $pending_ids = [];
    private static $data_cache = [];

    public static function collect_id($id) {
        self::$pending_ids[] = $id;
    }

    public static function hydrate() {
        if (empty(self::$pending_ids)) return;

        global $wpdb;
        $ids = implode(',', array_map('intval', array_unique(self::$pending_ids)));
        
        #6A9955">// Single query to fetch all metadata at once
        $results = $wpdb->get_results("SELECT post_id, meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id IN($ids)");
        
        foreach ($results as $row) {
            self::$data_cache[$row->post_id][$row->meta_key] = $row->meta_value;
        }
        
        self::$pending_ids = [];
    }

    public static function get_data($id) {
        return self::$data_cache[$id] ?? [];
    }
}

By hooking into the REST API lifecycle, you can ensure hydrate() runs exactly once before the response is serialized. This approach is significantly more performant than the standard approach, often reducing latency by around 280ms on heavy collections.

Why This Matters for WordPress Performance

Database optimization isn't just about indexing; it's about reducing the chatty nature of your application. While tools like WordPress database optimization: implementing HyperDB for scaling help you scale your infrastructure, they can't fix fundamentally broken loops.

If you are dealing with large datasets, remember that WordPress performance: streaming large REST API exports is another powerful technique to pair with coalescing. When you combine batch-loading metadata with streaming responses, you drastically lower the memory footprint of your PHP processes.

Trade-offs and Considerations

The biggest downside to coalescing is complexity. You are essentially creating a mini-ORM layer that needs to be manually cleared and managed. If you forget to clear the pending_ids array, you might end up with stale data or massive queries that exceed max_allowed_packet limits.

I'm still experimenting with whether it's better to use a dedicated object cache approach or this static-variable pattern. For high-concurrency environments, you'll want to ensure your cache logic is strictly scoped to the current request execution to avoid cross-pollination of data between API calls.

Frequently Asked Questions

Does this approach replace the need for object caching? No. Object caching should be your first line of defense. Coalescing is specifically for when you have to fetch a large number of items in a single request and want to avoid the N+1 trap.

Is it safe to use global $wpdb in this way? Yes, as long as you sanitize your IDs. The example above uses intval to ensure the list of IDs is safe before injecting it into the query string.

When should I avoid coalescing? Don't bother with this if your endpoint is only ever returning a single item. The overhead of managing the collection logic outweighs the benefit of a single query.

I’m still refining how to handle partial failures in the bulk query—if one ID fails, should the whole batch fail? For now, I’m wrapping the hydration in a try-catch block, but it’s a work in progress. It’s a messy fix, but it beats a slow site every time.