WP_Query at Scale: Performance and Memory

A WP_Query that loops over thousands of posts gets slow and memory-hungry for four avoidable reasons: it loads every matching post object into memory at once, it primes meta and term caches you may never read, it runs a second query to count total rows you never use, and on a long run it lets the object cache grow without bound. Batch the query and flush the object cache each batch and memory stays flat; pass fields => ids when you don't need the post bodies; set no_found_rows => true to skip the count. The one knob that bites back is the meta cache: turn it off and then read a meta value per row and you have traded memory for an N+1 query storm. Here is each knob, measured.

The numbers below are from a WP-CLI run over ~5,000 posts on a local site, the same context as the bulk-script harness. Absolute figures vary by host; the ratios and the shape are what matter.

The memory problem: one big page vs batches

The default way to "get everything" is posts_per_page => -1. It works until it doesn't: every matching post is hydrated into a full WP_Post object in memory at once, so peak memory grows linearly with the row count. At a few thousand posts it is merely wasteful; at fifty thousand it is an out-of-memory fatal before your loop even starts.

Batching fixes it. Pull a fixed page, process it, flush the object cache, pull the next. Peak memory then depends on the batch size, not the table size, so it is flat whether the table holds five thousand rows or five million:

The naive run here peaks at 119.8 MB, the batched-and-flushed run at 85.8 MB, for the same work at the same speed. The 34 MB gap is the 5,000 post objects held in memory simultaneously. Multiply the row count by ten and the naive peak climbs with it while the batched peak does not move, that is the whole point of batching.

WP_CLI\Utils\wp_clear_object_cache() is what keeps the batched run flat: it resets the in-process object cache and clears $wpdb->queries (which accumulates every query when SAVEQUERIES is on) at the end of each batch, so neither grows across the loop.

The query problem: cache priming and the N+1 trap

The second cost is query count, and this is the knob people get backwards. When WP_Query returns full post objects it primes the meta cache for the whole page in one query (a single WHERE post_id IN (...)). That is a feature. The common "optimization" of setting 'fields' => 'ids' and 'update_post_meta_cache' => false skips that prime, which is a genuine win only if you do not then read meta. If you do read a meta value per row after disabling the cache, every get_post_meta() becomes its own database round trip.

This is the kind of claim worth seeing rather than trusting, so here are the actual queries WordPress runs. The N+1 path issues one wp_postmeta SELECT per post; the primed path issues a handful for the entire batch:

That is 201 queries versus 4 for the same 200 posts. Those repeated SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (4785) lines are the N+1 in the flesh, one per post, each a separate trip to MySQL. The rule that falls out of it:

• Only need the IDs (you are deleting, re-saving, or fetching meta yourself in bulk): fields => ids plus update_post_meta_cache => false and update_post_term_cache => false. Nothing reads meta, so nothing pays for the missing cache.
• Reading a meta value per row: leave the meta cache priming on (it is the default). One bulk prime per batch beats one query per row, every time.

no_found_rows => true is the free win in both cases: it drops the separate SELECT FOUND_ROWS() count that pagination needs and a bulk job does not.

Tuning memory for the run

When a job genuinely must hold more than the default CLI memory limit allows, raise it for that one invocation rather than editing php.ini globally:

php -d memory_limit=512M "$(which wp)" eval-file te-bulk.php

But reach for that only after batching. A higher memory limit lets a naive -1 run survive a bigger table; batching means you never needed the headroom in the first place. The order matters: batch first, raise the limit only if a single batch's legitimate working set still exceeds the default.

The knobs, summarized

The pattern that combines all of this safely is the WP-CLI bulk-script harness; this article is the reasoning behind each line of it. When the write you are doing inside the loop is a custom-field update, wrap it in the safety rails from safely bulk-updating custom fields so a slow query is the worst thing that happens, not a corrupted field.