Backfill Post Embeddings at Scale: A Resumable WP-CLI Command with OpenAI Batches and Cost Guardrails

You have four thousand posts, a semantic search feature half-built, and a browser-based reindex button that dies somewhere around post two hundred. The vectors never finish, the tab times out, and you have no idea how much money you have already spent on the API calls that did succeed.

This tutorial builds the thing you actually need: a WP-CLI command that walks your existing posts in ID order, splits each one into overlapping chunks, sends those chunks to the OpenAI embeddings endpoint in batches via wp_remote_post(), and writes the resulting vectors into a custom table keyed by a SHA-256 hash of the chunk. Because the key is the content hash, re-running the command is cheap and idempotent. Because progress is stored in an option, a crash at post 3,000 resumes at post 3,001. And because there is a dry-run mode that prints a token and dollar estimate before a single request leaves the server, you never get a surprise invoice.

Why backfill at all

Embeddings generated on save_post only cover content published after you ship the feature. Everything already in the database — the archive that gives your site its value — stays invisible to semantic search until you process it. A backfill is a one-off batch job with three awkward properties: it is long-running, it costs real money per unit of work, and it will be interrupted. Any design that does not address all three is a design that fails in production.

What you are building

• A custom table wp_post_embeddings with a unique key on (content_hash, model).
• A WP-CLI command with four subcommands: estimate, run, status, reset.
• Keyset pagination on post ID so the job resumes without re-scanning.
• Batched API calls (up to 64 chunks per request) with retry and exponential backoff.
• Hard spend caps enforced mid-run, not after the fact.

Prerequisites

• WP-CLI 2.8 or newer, PHP 8.1 or newer (the command uses typed properties and arrow functions).
• An OpenAI API key with billing enabled. Store it as a constant, never in the database.
• Shell access to the server, or a local copy of the database plus wp db export.
• A staging clone. Run the whole thing there first — the estimate will be wrong the first time and you want to find that out on a copy.

Check the environment

wp --info
wp core version
php -r 'echo PHP_VERSION, PHP_EOL;'
wp db query "SHOW TABLES LIKE '%post_embeddings%';"
read -rsp "OpenAI API key: " OPENAI_API_KEY && export OPENAI_API_KEY
wp config set WPTUT_OPENAI_KEY "$OPENAI_API_KEY" --type=constant

wp config set --type=constant writes the key into wp-config.php above the ABSPATH guard, so it is not exposed to plugins that read options. If you would rather use an environment variable, read it with getenv() and skip this step.

Step 1 Create the vectors table

Storing 1,536 floats as JSON costs roughly 30 KB per chunk. Packed as little-endian float32 it costs 6 KB. Over 20,000 chunks that is the difference between a 600 MB table and a 120 MB one, so pack the vector and unpack it on read.

CREATE TABLE wp_post_embeddings (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  object_id     BIGINT UNSIGNED NOT NULL,
  chunk_index   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  content_hash  CHAR(64) NOT NULL,
  model         VARCHAR(64) NOT NULL,
  dimensions    SMALLINT UNSIGNED NOT NULL,
  vector        LONGBLOB NOT NULL,
  tokens        INT UNSIGNED NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hash_model (content_hash, model),
  KEY object_id (object_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The unique key on hash_model is the whole trick. Two posts that share a boilerplate paragraph produce the same chunk hash, so the second one is skipped for free. A re-run after a partial failure finds the hash already present and moves on. And if you later switch from text-embedding-3-small to text-embedding-3-large, the model column keeps both sets of vectors side by side instead of silently mixing dimensions.

Step 2 Scaffold the command

Drop this in wp-content/mu-plugins/wptut-embed-backfill.php. A must-use plugin is the right home: it cannot be deactivated by accident mid-run, and it is never loaded on the front end because of the WP_CLI guard.

<?php
/**
 * Plugin Name: WPTUT Embedding Backfill
 * Description: Resumable WP-CLI backfill of post embeddings.
 */

defined( 'ABSPATH' ) || exit;

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

class WPTUT_Embed_Backfill extends WP_CLI_Command {

	const STATE     = 'wptut_embed_backfill_state';
	const MODEL     = 'text-embedding-3-small';
	const DIMS      = 1536;
	const PRICE     = 0.02;   // USD per 1M input tokens — verify before running
	const MAX_CHARS = 6000;
	const OVERLAP   = 400;
	const API_BATCH = 64;

	private string $table;

	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'post_embeddings';
	}
}

WP_CLI::add_command( 'wptut embed', 'WPTUT_Embed_Backfill' );

Declaring private string $table rather than assigning it dynamically avoids the PHP 8.2 dynamic-property deprecation notice, which WP-CLI will happily print four thousand times if you let it.

Step 3 Chunk content deterministically

Chunking must be a pure function of the post content. If the same post produces different chunks on two runs, the hashes never match and you pay twice. Strip block delimiters first — wp_strip_all_tags() leaves <!-- wp:paragraph --> comments behind on some PHP builds — then shortcodes, then collapse whitespace.

private function chunk( string $html ): array {
	$text = preg_replace( '/<!--.*?-->/s', ' ', $html );
	$text = wp_strip_all_tags( strip_shortcodes( $text ) );
	$text = trim( preg_replace( '/\s+/u', ' ', $text ) );

	if ( '' === $text ) {
		return array();
	}

	$len    = mb_strlen( $text );
	$chunks = array();
	$offset = 0;

	while ( $offset < $len ) {
		$chunks[] = mb_substr( $text, $offset, self::MAX_CHARS );
		if ( $offset + self::MAX_CHARS >= $len ) {
			break;
		}
		$offset += self::MAX_CHARS - self::OVERLAP;
	}

	return $chunks;
}

private function hash_chunk( string $chunk ): string {
	return hash( 'sha256', self::MODEL . '|' . $chunk );
}

6,000 characters is roughly 1,500 tokens — comfortably inside the 8,191-token limit of the small model and large enough that a single chunk usually holds a coherent argument. The 400-character overlap stops a sentence that straddles a boundary from being lost to both chunks. Include the model name in the hash input so a model switch invalidates cleanly.

Step 4 Estimate tokens and cost before spending

OpenAI does not expose a tokeniser endpoint, so use the standard approximation of one token per four characters of English prose. It overshoots on code and undershoots on dense technical writing, but it lands within about 15% — good enough to decide whether the job costs two dollars or two hundred.

public function estimate( array $args, array $assoc_args ): void {
	global $wpdb;

	$types = $this->post_types( $assoc_args );
	$in    = implode( ',', array_fill( 0, count( $types ), '%s' ) );
	$sql   = "SELECT ID, post_content FROM {$wpdb->posts}
	          WHERE post_status = 'publish' AND post_type IN ($in)
	          ORDER BY ID ASC";

	$rows   = $wpdb->get_results( $wpdb->prepare( $sql, $types ) );
	$chunks = 0;
	$chars  = 0;

	foreach ( $rows as $row ) {
		foreach ( $this->chunk( $row->post_content ) as $c ) {
			$chunks++;
			$chars += mb_strlen( $c );
		}
	}

	$tokens = (int) ceil( $chars / 4 );
	$cost   = ( $tokens / 1000000 ) * self::PRICE;

	WP_CLI::log( sprintf( 'Posts:  %d', count( $rows ) ) );
	WP_CLI::log( sprintf( 'Chunks: %d', $chunks ) );
	WP_CLI::log( sprintf( 'Tokens: ~%s', number_format( $tokens ) ) );
	WP_CLI::log( sprintf( 'Cost:   ~$%.4f', $cost ) );
}

Note that $wpdb->prepare() accepts an array as its second argument, which is why the placeholder list is built with array_fill() rather than string concatenation. Never interpolate post types directly into that IN clause.

Step 5 Call the embeddings endpoint in batches

The endpoint accepts an array of inputs, so one HTTP request can cover 64 chunks. That is the single biggest lever on wall-clock time: 20,000 chunks becomes 313 requests instead of 20,000.

private function embed( array $inputs ): array {
	$body = wp_json_encode( array(
		'model'      => self::MODEL,
		'input'      => array_values( $inputs ),
		'dimensions' => self::DIMS,
	) );

	$response = wp_remote_post(
		'https://api.openai.com/v1/embeddings',
		array(
			'timeout' => 60,
			'headers' => array(
				'Authorization' => 'Bearer ' . WPTUT_OPENAI_KEY,
				'Content-Type'  => 'application/json',
			),
			'body'    => $body,
		)
	);

	if ( is_wp_error( $response ) ) {
		throw new RuntimeException( $response->get_error_message() );
	}

	$code = (int) wp_remote_retrieve_response_code( $response );
	$data = json_decode( wp_remote_retrieve_body( $response ), true );

	if ( 200 !== $code ) {
		$msg = $data['error']['message'] ?? ( 'HTTP ' . $code );
		throw new RuntimeException( $msg, $code );
	}

	return $data;
}

Wrap the call site in a retry loop that catches RuntimeException and backs off on codes 429 and 5xx: sleep 2 ** $attempt seconds, up to four attempts. Do not retry on 400 or 401 — those are your bug, not the network’s.

Storing the result is straightforward, but watch the index mapping. The API returns each embedding with an index referring to its position in the input array, so pass array_values() of your slice, not the original chunk-keyed array.

private function store( int $post_id, array $slice, array $data ): void {
	global $wpdb;

	foreach ( $data['data'] as $row ) {
		$item = $slice[ $row['index'] ];

		$wpdb->insert(
			$this->table,
			array(
				'object_id'    => $post_id,
				'chunk_index'  => $row['index'],
				'content_hash' => $item['hash'],
				'model'        => self::MODEL,
				'dimensions'   => self::DIMS,
				'vector'       => pack( 'g*', ...$row['embedding'] ),
				'tokens'       => 0,
				'created_at'   => current_time( 'mysql', true ),
			),
			array( '%d', '%d', '%s', '%s', '%d', '%s', '%d', '%s' )
		);
	}
}

pack( 'g*', ... ) produces little-endian float32. Read it back with unpack( 'g*', $blob ) and you have your array again — no JSON parsing, no precision loss beyond float32 itself.

Step 6 Make the run resumable

Two things make this resumable: keyset pagination and a state option. Never use OFFSET — on a large wp_posts table it degrades linearly and it silently skips rows if anything is inserted mid-run. Instead, remember the last ID processed and ask for the next fifty greater than it.

public function run( array $args, array $assoc_args ): void {
	$dry       = (bool) WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', false );
	$max_spend = (float) WP_CLI\Utils\get_flag_value( $assoc_args, 'max-spend', 5.0 );
	$state     = $this->state();

	while ( true ) {
		$posts = $this->next_posts( $state['last_id'], 50, $this->post_types( $assoc_args ) );

		if ( empty( $posts ) ) {
			break;
		}

		foreach ( $posts as $post ) {
			$pending = array();

			foreach ( $this->chunk( $post->post_content ) as $i => $chunk ) {
				$hash = $this->hash_chunk( $chunk );
				if ( $this->exists( $hash ) ) {
					$state['skipped']++;
					continue;
				}
				$pending[ $i ] = array( 'hash' => $hash, 'text' => $chunk );
			}

			foreach ( array_chunk( $pending, self::API_BATCH ) as $slice ) {
				$slice = array_values( $slice );

				if ( $dry ) {
					$state['tokens'] += (int) ceil( array_sum( array_map(
						fn( $p ) => mb_strlen( $p['text'] ), $slice
					) ) / 4 );
					continue;
				}

				$data = $this->embed( wp_list_pluck( $slice, 'text' ) );
				$this->store( $post->ID, $slice, $data );

				$state['tokens'] += (int) $data['usage']['total_tokens'];
				$state['spend']  += ( $data['usage']['total_tokens'] / 1000000 ) * self::PRICE;

				if ( $state['spend'] > $max_spend ) {
					update_option( self::STATE, $state, false );
					WP_CLI::error( sprintf( 'Spend cap reached ($%.4f). Resume with a higher --max-spend.', $state['spend'] ) );
				}
			}

			$state['done']++;
		}

		$state['last_id'] = (int) end( $posts )->ID;
		update_option( self::STATE, $state, false );

		WP_CLI::log( sprintf(
			'Through post %d — %d done, %d skipped, ~$%.4f spent',
			$state['last_id'], $state['done'], $state['skipped'], $state['spend']
		) );
	}

	WP_CLI::success( 'Backfill complete.' );
}

The false third argument to update_option() disables autoloading. A state array that grows with every failure would otherwise be loaded on every single page request — a genuinely nasty performance bug on a busy site.

Persist state after every batch of posts, not every chunk. If the process dies mid-post you lose at most fifty posts of work, and the hash check means re-processing them costs nothing.

Step 7 Run it

# 1. Estimate — no API calls, no money
wp wptut embed estimate --post_type=post,page

# 2. Dry run — walks the real query, prints projected tokens
wp wptut embed run --post_type=post,page --dry-run

# 3. Real run with a hard two-dollar cap
wp wptut embed run --post_type=post,page --max-spend=2.00

# 4. Interrupted? Just run it again — it resumes from the state option
wp wptut embed run --post_type=post,page --max-spend=2.00

# 5. Inspect or start over
wp wptut embed status
wp wptut embed reset

Run the real pass inside tmux or screen, or under nohup. An SSH drop should not be the thing that stops a job you have already paid for.

Troubleshooting

HTTP 429 halfway through

You are hitting the rate limit for your tier. Reduce API_BATCH to 16 and add a 250 ms sleep between requests. Do not reduce MAX_CHARS — that increases the number of requests and makes the problem worse.

Duplicate rows or a unique-key violation

Two chunks in the same batch hashed identically, which happens with repeated boilerplate inside one post. Change $wpdb->insert() to $wpdb->replace(), or de-duplicate the $pending array by hash before slicing it.

Error: 'wptut' is not a registered wp command

The mu-plugin is not loading. Check the path is exactly wp-content/mu-plugins/ (no subdirectory unless you have a loader), and confirm the file has no PHP parse error with php -l.

Timeouts or memory exhaustion

Lower the posts-per-pass constant from 50 to 10 and run with wp --memory-limit=512M. Loading full post_content for fifty long posts at once is the usual culprit; select only ID first and fetch content per post if you need to.

Stale vectors after editing a post

Expected — the hash changed, so the old row is orphaned. Hook save_post to delete rows for that object_id and re-embed, or run a periodic reconciliation pass that compares stored hashes against freshly computed ones.

Next steps

With vectors in place you can build the retrieval layer: a WP_Query replacement that takes a query embedding, computes cosine similarity in SQL against the packed blobs, and returns post IDs. Feed those IDs into a Query Loop block variation so the results render with your theme’s existing patterns rather than a bespoke template.

For incremental updates, hook save_post to a single-post version of the same pipeline and let WP-Cron drain a queue. Keep the CLI command as the repair tool — it is the only thing that can rebuild the index from scratch after a model migration, and it is the only thing that will tell you what that rebuild costs before you commit to it.