Symptom: the prefix DELETE looked safe until it wasn’t
You decide to clean up wp_postmeta. You pick a key prefix left behind by a plugin you removed two years ago, run DELETE FROM wp_postmeta WHERE meta_key LIKE '_elementor_%', and move on. The query returns a large affected-rows count, which feels like progress.
What it actually did was remove every row matching that prefix, including rows attached to posts that still exist and are still being edited. The prefix is a string match, not a liveness test. A DELETE ... WHERE meta_key LIKE 'example_key_%' is exactly the shape recommended in cleanup write-ups, and it is exactly the shape that cannot tell an abandoned key from a key still in use on a live post (Anchor Host).
The failure is not the SQL. It is that a key-level question (“is this meta_key still written?”) and a row-level question (“do these rows still have a parent post?”) were collapsed into one decision. This article builds a read-only REST endpoint that reports both numbers per meta_key, so the dangerous middle — keys that are partly orphaned — is visible before anyone types DELETE.
Trace: what the two questions actually look like in SQL
The key-level question is a grouped count. The canonical shape is a GROUP BY meta_key with COUNT(*), ordered descending, which is the query most people paste into phpMyAdmin first (nicomollet gist):
SELECT meta_key, COUNT(*) AS total
FROM wp_postmeta
GROUP BY meta_key
ORDER BY total DESC;The row-level question is a LEFT JOIN against wp_posts where the joined ID is null. That is the standard orphan check (Anchor Host):
SELECT pm.meta_key, COUNT(*) AS orphans
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL
GROUP BY pm.meta_key
ORDER BY orphans DESC;Run separately, each answers half the question. Run together, they answer the one that matters: for a given key, how many rows exist, and how many of those rows are attached to nothing.
Root cause: the partial-orphan fraction is the dangerous case
Three states fall out of the two columns:
- Total > 0, orphans = 0. The key is live. Every row has a parent. Deleting by key name destroys data.
- Total = orphans. The key is entirely dead. Every row is parentless. Deleting by key name is safe in the narrow sense that no live post loses metadata.
- 0 < orphans < total. The key is partly dead. Some rows belong to deleted posts; some belong to live ones. A prefix
DELETEremoves both. This is the case the endpoint exists to surface.
As a hypothetical illustration: a site where _elementor_ keys show 40,000 total rows and 9,000 orphans. A prefix delete would remove 31,000 rows still attached to live pages. The endpoint’s two-column output is what makes that visible before the write runs. The numbers are illustrative, not a measurement of any particular install.
Fix: register a read-only route on rest_api_init
Custom routes are registered with register_rest_route(), and the documentation is explicit that it should be called in a callback on rest_api_init to avoid doing extra work when the API is not loaded (Adding Custom Endpoints). Calling it earlier triggers a _doing_it_wrong notice; the source checks did_action( 'rest_api_init' ) and complains if the action has not fired (register_rest_route()).
Since WordPress 5.5, a permission_callback is required. Omitting it produces a _doing_it_wrong notice telling you to use __return_true for public routes (register_rest_route()). This endpoint is not public; it reads site-wide metadata, so it gets a real capability check.
add_action( 'rest_api_init', function () {
register_rest_route(
'jv-meta-audit/v1',
'/meta-keys',
array(
'methods' => 'GET',
'callback' => 'jv_meta_audit_get_keys',
'permission_callback' => 'jv_meta_audit_permissions',
'args' => array(
'min_total' => array(
'type' => 'integer',
'default' => 1,
'sanitize_callback' => 'absint',
'validate_callback' => 'rest_validate_request_arg',
),
'key_like' => array(
'type' => 'string',
'required' => false,
'sanitize_callback' => 'sanitize_text_field',
'validate_callback' => 'rest_validate_request_arg',
),
),
)
);
} );The namespace follows the vendor/v1 convention the handbook recommends to prevent clashes between plugins (Adding Custom Endpoints). The args map gives you sanitization and validation before the callback runs, so the callback can assume its inputs are clean.
The permission callback
The permissions callback runs after remote authentication, which sets the current user, so current_user_can() is the right tool (Adding Custom Endpoints). The handbook recommends checking a capability rather than merely checking that a user is logged in.
function jv_meta_audit_permissions( WP_REST_Request $request ) {
if ( ! current_user_can( 'manage_options' ) ) {
return new WP_Error(
'rest_forbidden',
'You cannot read site-wide postmeta distribution.',
array( 'status' => 403 )
);
}
return true;
}manage_options is a defensible default for a diagnostic that exposes the shape of the database. If your editorial team should see it without full admin, register a custom capability and grant it deliberately; the choice is yours, but name it explicitly rather than falling back to read.
One authentication detail that bites people testing with curl: cookie authentication requires the wp_rest nonce, passed as X-WP-Nonce or the _wpnonce parameter. Without it, the API sets the current user to 0 and treats the request as unauthenticated even if you are logged in (Authentication). For command-line testing, Application Passwords over HTTPS are the documented path.
The query: one statement, two counts, prepared
All untrusted values in an SQL statement must be escaped, and $wpdb->prepare() is the mechanism WordPress provides (wpdb). The table name comes from $wpdb->postmeta, not from user input. The LIKE pattern and the threshold are the only variables, and both go through placeholders.
function jv_meta_audit_get_keys( WP_REST_Request $request ) {
global $wpdb;
$min_total = (int) $request->get_param( 'min_total' );
$key_like = $request->get_param( 'key_like' );
$sql = "SELECT pm.meta_key,
COUNT(*) AS total,
SUM( CASE WHEN p.ID IS NULL THEN 1 ELSE 0 END ) AS orphans
FROM {$wpdb->postmeta} pm
LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id";
$where = array();
$params = array();
if ( $key_like ) {
$where[] = 'pm.meta_key LIKE %s';
$params[] = '%' . $wpdb->esc_like( $key_like ) . '%';
}
if ( $where ) {
$sql .= ' WHERE ' . implode( ' AND ', $where );
}
$sql .= ' GROUP BY pm.meta_key';
$sql .= ' HAVING total >= %d';
$params[] = $min_total;
$sql .= ' ORDER BY total DESC';
$prepared = $wpdb->prepare( $sql, $params );
$rows = $wpdb->get_results( $prepared, ARRAY_A );
if ( null === $rows ) {
return new WP_Error(
'meta_audit_query_failed',
'The distribution query failed.',
array( 'status' => 500 )
);
}
$out = array();
foreach ( $rows as $row ) {
$total = (int) $row['total'];
$orphans = (int) $row['orphans'];
$out[] = array(
'meta_key' => $row['meta_key'],
'total' => $total,
'orphans' => $orphans,
'live' => $total - $orphans,
'orphan_fraction' => $total > 0 ? round( $orphans / $total, 4 ) : 0.0,
);
}
return new WP_REST_Response( $out, 200 );
}Two details worth naming. First, esc_like() escapes the wildcard characters in the user-supplied pattern before they are wrapped in %; without it, a key_like of _ would match any single character. Second, HAVING is used rather than WHERE because the threshold applies to the aggregate, not to individual rows.
Return a response, do not emit one
The handbook is direct: a REST route’s callback should always return data and should not attempt to send the response body itself. Calling wp_send_json() or die( wp_json_encode( $data ) ) inside a REST callback bypasses the server’s handling of links, embedding, and headers, and since WordPress 5.5 triggers a _doing_it_wrong notice (Adding Custom Endpoints). Return WP_REST_Response or WP_Error and let the server serialize.
Reading the output
Request the endpoint with a nonce or an Application Password:
curl --user "USERNAME:APP_PASSWORD" \
"https://example.com/wp-json/jv-meta-audit/v1/meta-keys?min_total=100"A response row looks like this:
{
"meta_key": "_elementor_data",
"total": 40000,
"orphans": 9000,
"live": 31000,
"orphan_fraction": 0.225
}Sort by orphan_fraction descending to find the dangerous middle. A fraction of 1.0 is a fully dead key; a fraction of 0.0 is a live key; anything between is where a prefix delete would hit live posts. The live column is the one to quote in a cleanup ticket, because it is the number of rows a key-level delete would destroy.
For a quick sanity check against the endpoint, run the same aggregate in WP-CLI:
wp db query "SELECT pm.meta_key, COUNT(*) AS total,
SUM(CASE WHEN p.ID IS NULL THEN 1 ELSE 0 END) AS orphans
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
GROUP BY pm.meta_key
ORDER BY total DESC
LIMIT 20;"If the endpoint and the CLI disagree, the endpoint is filtering by min_total or key_like and the CLI is not. Check the query string before assuming a bug.
Optional: wrap it in a controller
If you expect to add per-key drill-down later — say, /meta-keys/{key}/orphans returning the post IDs — the controller pattern is the documented way to structure that. Subclass WP_REST_Controller, implement register_routes(), get_items(), and get_items_permissions_check(), and instantiate the class inside the rest_api_init callback (Controller Classes). The base class supplies prepare_response_for_collection(), get_collection_params(), and schema helpers you would otherwise reimplement. For a single read-only route, the closure above is less machinery; for a family of routes, the controller pays for itself.
Verification: prove the endpoint before trusting it
Three checks, in order:
- Route is registered. Visit
/wp-json/jv-meta-audit/v1while authenticated. The index should list the route. If it 404s, therest_api_initcallback did not run — check that the code is in a plugin or mu-plugin, not in a theme file loaded after the REST server initializes. - Permission is enforced. Request the endpoint without credentials. You should get a
rest_forbiddenerror, not data. If you get data, thepermission_callbackis returning true unconditionally. - Numbers match a known key. Pick a meta_key you know is live on a specific post, request the endpoint with
key_likeset to that key, and confirmorphansis 0 andtotalis at least 1. Then pick a key you know belongs to a deleted post and confirmorphan_fractionis 1.0.
If you use Query Monitor, the endpoint’s query appears in the REST API request panel with the prepared SQL and the elapsed time. That is the place to confirm prepare() substituted the expected values rather than leaving a literal %s in the statement.
What this endpoint does not do
It reports. It does not delete. That separation is deliberate: mixing reporting and deletion in one route means a mis-scoped permission_callback or a stale nonce turns a diagnostic into a destructive action. With the endpoint read-only, the worst outcome of a bad request is a wrong number.
Deletion stays a separate, backed-up, batched operation. Export the table first (wp db export), delete in batches sized to your host, and re-run the endpoint afterward to confirm the orphan counts moved as expected. The endpoint tells you which keys are candidates; it does not tell you that a given key is safe to remove, because “safe” depends on whether the plugin that wrote it is coming back.
One more boundary: the endpoint reports the distribution of meta_key values in wp_postmeta. It says nothing about wp_usermeta, wp_termmeta, or wp_commentmeta, each of which has its own orphan problem and its own join target. If you find yourself wanting a unified view, that is a different endpoint with a different permission story.
FAQ
Why not just run the SQL in phpMyAdmin?
You can, and for a one-off audit you should. The endpoint earns its keep when the audit is recurring, when more than one person needs to see it without database credentials, or when you want the output in a shape a script can consume. It also keeps the query in version control, which a phpMyAdmin session does not.
Does the endpoint slow down the site?
It runs only when requested, and only for users with the capability you chose. It does not hook into front-end requests. The aggregate itself scans wp_postmeta, so on a very large table it will take time proportional to the table size; that is a property of the query, not of the endpoint. Run it during a quiet window if the table is large.
Can I filter to a single key?
Yes. Pass key_like as a query parameter. The value is escaped with esc_like() and wrapped in %, so it matches as a substring. For an exact match, add a separate key argument with an = comparison rather than overloading the LIKE.
What capability should I use?
There is no single correct answer. manage_options is the conservative default because the output describes the shape of the database. If you want editors to see it, register a custom capability such as read_postmeta_distribution and grant it to the roles that need it. The handbook’s guidance is to check a capability rather than merely checking login state (Adding Custom Endpoints).
Why does my curl request return rest_forbidden even with a valid login?
Cookie authentication requires the wp_rest nonce. Without it, the API treats the request as unauthenticated and sets the current user to 0 (Authentication). Use Application Passwords over HTTPS for command-line testing, or pass X-WP-Nonce from a page where the nonce is localized.