wp_postmeta pruning: clearing 4.2M rows without ACF damage

The Loom showed a WordPress admin sitting on a spinner for fourteen seconds before the posts list rendered. The agency lead recorded it at 23:41 on a Tuesday, after a client had complained that editorial was getting bumped off the CMS during deadline week. The site was a content-heavy publisher running WordPress 6.4 on a tuned MariaDB box. CPU on the database server idled at four percent. The bottleneck was wp_postmeta.

SHOW TABLE STATUS LIKE 'wp_postmeta' came back with a row count north of 4.2 million on a site with roughly 8,400 published posts. That ratio, about five hundred meta rows per post, was the smell. This post walks through what we found, the ACF gotcha that almost made the cleanup worse than the bloat itself, and the SQL we ended up running on a Dutch agency's legacy site last month.

The audit before the prune

Pruning wp_postmeta is one of those jobs where running the cleanup is the easy part. The risky part is knowing exactly what you are about to delete. Before touching anything, we ran three counting queries against a read replica.

Orphaned meta on posts that no longer exist:

SELECT COUNT(*) AS orphan_rows
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

This came back with 612,034 rows. Two plugins (one was an old object-cache plugin removed in 2022, the other was a since-replaced SEO plugin) had been writing meta on post-delete hooks they did not also clean up on uninstall. Both had been gone from the site for over a year. Their meta had not.

Meta belonging to revisions:

SELECT COUNT(*) AS revision_meta
FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'revision';

Another 1.84 million rows. WordPress stores ACF values on every revision by design, and this publisher had editors who saved drafts compulsively. Multiply 8,400 posts by an average of fourteen revisions each, multiply that by sixteen ACF fields per post template, and you get the picture.

The third query was a meta_key distribution scan, which is the one that usually tells you which plugin is the culprit:

SELECT meta_key, COUNT(*) AS n
FROM wp_postmeta
GROUP BY meta_key
ORDER BY n DESC
LIMIT 30;

The top of the list looked roughly like this on their site:

meta_key                    | n
----------------------------|--------
_edit_lock                  | 412,103
_edit_last                  | 408,776
_wp_old_slug                | 287,442
rocket_clean_post           | 211,008
_yoast_wpseo_focuskw_text   | 198,540
_acf_changed                |  91,221
hero_image                  |  84,610
_hero_image                 |  84,610

The rocket_clean_post rows belonged to a plugin uninstalled in 2023. The _wp_old_slug rows had piled up from years of slug edits and were technically still useful, so we left them. The remaining ~1.7 million rows were real, current post meta. Roughly sixty percent of the table was reclaimable. Before any DELETE statement ran, we made a hot copy:

mysqldump --single-transaction --quick \
  --triggers --routines \
  agency_db wp_postmeta wp_posts \
  | gzip > postmeta_backup_2026-05-13.sql.gz

For a site this size the dump took eleven minutes and produced a 380 MB compressed file. Cheap insurance.

The ACF trap most cleanup scripts step in

Advanced Custom Fields stores every field as two rows in wp_postmeta. One holds the value. One holds the reference to the field definition. The reference row's meta_key starts with an underscore, and its meta_value is the field's group key, like field_5f8a1b2c3d4e5. ACF needs the pair to render the field on the front end.

Concretely, for a single text field called hero_title you get:

meta_key      | meta_value
--------------|-------------------------
hero_title    | "Spring collection"
_hero_title   | "field_5f8a1b2c3d4e5"

Repeater and flexible content fields multiply this. A repeater with six rows and four sub-fields becomes 48 rows in wp_postmeta, plus an index counter row, plus all the underscore-prefixed references. Drop one half of any pair and ACF stops resolving the field on render. The ACF docs are explicit about this: the underscore key is what makes the value addressable to the field group.

The naive cleanup script you find on most blogs is this:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

That query is safe for the strict orphan case. If the parent post is gone from wp_posts, both halves of the ACF pair go with it, and ACF never knew the post existed in the first place. It is not safe if you try to extend it to "delete meta where the field is not in the active ACF group". Plenty of valid pairs live on posts you still want, just with field keys that have been renamed or re-grouped in ACF over the years. We have seen one-line cleanup snippets break front-end blocks on production sites three days after the prune, when an editor finally re-saved a page that had been quietly serving cached HTML.

The prune we ran

With the backup in place, the actual cleanup was three batched DELETEs. We ran them in a transaction window with the agency's editorial team paused for forty minutes on a Saturday morning.

Batch one, orphaned meta where the parent post is gone:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL
LIMIT 5000;

We wrapped this in a shell loop that re-ran the statement until ROW_COUNT() returned zero. Batching is non-optional on a table this size. A single unbounded DELETE on 600k rows will hold the table lock long enough for any concurrent INSERT (which on WordPress means literally any page save, comment, or background cron) to time out.

while :; do
  rows=$(mysql -BN agency_db -e \
    "DELETE pm FROM wp_postmeta pm \
     LEFT JOIN wp_posts p ON p.ID = pm.post_id \
     WHERE p.ID IS NULL LIMIT 5000; \
     SELECT ROW_COUNT();")
  echo "deleted $rows"
  [ "$rows" -eq 0 ] && break
  sleep 1
done

Batch two, meta on revisions. Revisions themselves are useful, since they are how editors recover work, and we did not want to drop them. We did want to drop the ACF meta that WordPress had cloned onto each revision, because that meta is never queried on the front end:

DELETE pm FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'revision'
LIMIT 5000;

Same shell loop wrapping. This one was the bulk of the work, 1.84 million rows in about twenty-two minutes on a 4-vCPU MariaDB instance. We watched SHOW PROCESSLIST in another tab the whole time. No lock-wait timeouts on the application side.

Batch three, the named-key prune for plugins long uninstalled. Plugin meta keys are predictable, which makes this the safest of the three:

DELETE FROM wp_postmeta
WHERE meta_key IN (
  'rocket_clean_post',
  'rocket_lazyload_excluded',
  'old_seo_plugin_score'
)
LIMIT 5000;

After the last batch, an OPTIMIZE TABLE wp_postmeta; reclaimed roughly 1.6 GB of disk and rebuilt the indexes. On InnoDB this is equivalent to ALTER TABLE ... ENGINE=InnoDB, which writes a new copy of the table and swaps it in. Plan for double the table size in free disk during the operation, and run it in the maintenance window with the site in read-only mode if you can manage it. The MySQL docs cover the exact lock behaviour and are worth reading before you press enter on a production primary.

Final row count: 1,742,803. The admin posts list rendered in 0.6 seconds. Editorial was unpaused at 11:47.

The maintenance that now runs nightly

The prune is the one-time fix. The next question is how the table got there in the first place, so it does not refill in eighteen months. We put four things in place.

Revision cap

WordPress keeps unlimited revisions by default. For a site with ACF-heavy templates, that is a postmeta amplifier. We capped revisions per post at ten in wp-config.php:

define( 'WP_POST_REVISIONS', 10 );

Ten was the agency's editorial team's preference. Five is fine for most sites. Setting the constant to false turns revisions off entirely, which we would not recommend on a publisher. The cap only applies to revisions created after the constant is set, so the historical backlog still needs a one-shot prune.

Orphan sweep, nightly

A small WP-CLI script runs at 03:00 server time and re-runs the orphan check. If it finds more than a thousand orphans, it prunes them in 5,000-row batches and pages on Slack if the count is over fifty thousand. Fifty thousand is the rough threshold for "a plugin is misbehaving", and you want to know about that on the day it starts, not at the next quarterly audit.

wp db query "DELETE pm FROM wp_postmeta pm \
  LEFT JOIN wp_posts p ON p.ID = pm.post_id \
  WHERE p.ID IS NULL LIMIT 5000;"

Autoload audit, weekly

While we were in the database we noticed wp_options had 38 MB of autoloaded data, mostly from an analytics plugin caching API responses with autoload = 'yes'. Postmeta bloat and options bloat tend to show up together, so the weekly job also runs:

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY bytes DESC
LIMIT 20;

Anything over 100 KB gets flagged for review. The autoloaded options are read on every request, so this matters more than postmeta bloat for time-to-first-byte. Two of the flagged rows on this site were JSON blobs left behind by a font-loader plugin. They came out in the same maintenance window.

Meta-key drift alert

The fourth job is a weekly diff of the meta_key distribution against a baseline snapshot. If a new meta_key shows up with more than fifty thousand rows, the script writes the key, the count, and the top three post IDs to a Slack channel. That is how you catch a freshly installed plugin writing where it should not, before it becomes next year's audit.

How we audit this on the Pier side

When we built Pier we ran into this exact pattern across customer sites often enough that we wrote a postmeta-shape view into the MySQL editor: row counts grouped by meta_key, orphan totals, revision share, top autoloaded options, all in one panel. Every destructive query we run from Pier writes a backup of the affected rows into the version history before the DELETE fires, so the rollback is one keystroke if the prune was wrong.

If you have not yet looked at your wp_postmeta on the largest WordPress site you maintain, run the three counting queries from the audit section tonight. That single read-only check tells you whether you have a postmeta problem or not, and it costs less than a minute on the database.