How to Clean Up wp_postmeta Without Breaking WordPress

The wp_postmeta table in your WordPress database holds the extra details about your posts, pages and media. When it gets large, some guides tell you to empty it. Don't. Emptying it deletes data your site needs, and there's no undo.

What you can safely remove are orphaned rows: meta whose post no longer exists. This post covers what the table holds, the backup to take first, and how to find, count and delete only those rows. I host and look after WordPress sites for clients, and this is the cleanup I'd do on one of them.

What wp_postmeta holds

WordPress's database description says each post's meta data is stored in wp_postmeta, and that plugins may add their own information to it. Every row has four columns: meta_id, post_id (the post it belongs to), meta_key (the name) and meta_value (the data).

"Post" means more than a blog post here. Pages and navigation menu items live in the posts table too, and each file in your media library has meta of its own. Some of what WordPress itself keeps in wp_postmeta:

• Featured images. A post's featured image is stored under _thumbnail_id (get_post_thumbnail_id()).
• Media files. WordPress finds each uploaded file through _wp_attached_file (get_attached_file()) and keeps its details, including its resized copies, in _wp_attachment_metadata (wp_get_attachment_metadata()).
• Page templates. The template you pick for a page is stored as _wp_page_template (get_page_template_slug()).
• Menus. Where each menu item points is stored in keys such as _menu_item_type, _menu_item_object_id and _menu_item_url (wp_update_nav_menu_item()).

Keys that start with an underscore don't appear in the custom fields list on the edit screen (Plugin Handbook), so most of this never shows in the dashboard. Plugins add their own keys alongside.

Never empty the table

Some guides tell you to run TRUNCATE TABLE wp_postmeta;. Don't. MySQL's manual says TRUNCATE TABLE empties a table completely and can't be rolled back (MySQL manual). Every featured image setting, media file path, page template choice and menu link in the list above would go at once, along with every plugin's data. The only way back is a backup.

Why it grows

Two things fill it up:

• Plugins store data there. A plugin that saves something for each post adds rows for each post, and those rows belong to live content.
• Leftovers. WordPress leaves clean-up to each plugin. Its Plugin Handbook tells developers to remove their data when the plugin is uninstalled, meaning deactivated and then deleted, and not when it's only deactivated (Uninstall Methods). A plugin you've only deactivated keeps its data, and one that skips the clean-up leaves rows behind after you delete it.

Orphaned rows are a narrower case. When WordPress permanently deletes a post, it deletes the post's meta fields with it (wp_delete_post()). So meta with no post usually means the post was removed some other way, such as directly in the database.

Back up first

WordPress's guide strongly recommends backing up your database regularly and before an upgrade (Backing Up Your Database). Treat this cleanup like an upgrade.

• In phpMyAdmin: select your WordPress database, open the Export tab, leave the Quick method selected and click Go. WordPress's guide walks through it, and through restoring.
• With WP-CLI: wp db export saves the database to a .sql file (wp db export).

Know how you'd restore that file before you go on. If you have a staging copy of the site, run everything there first.

Check your table prefix

The queries below use the default wp_ prefix. Yours may be different: it's set by $table_prefix in wp-config.php (wp-config.php documentation), and wp db prefix prints it (wp db prefix). Change the table names in every query to match.

On a multisite network, each extra site has its own numbered tables, such as wp_2_postmeta and wp_2_posts (database description). Always pair a site's postmeta table with the same site's posts table. Joining wp_2_postmeta to wp_posts would check against the wrong site's posts and delete rows that belong to live content.

Remove orphaned rows, step by step

Run these wherever you run SQL against the site's database: phpMyAdmin, a desktop client, or WP-CLI's wp db query (wp db query). Each one uses the same LEFT JOIN: it lines every meta row up with its post, and where there's no post, the post's columns come back empty (NULL). That's how MySQL's manual shows finding rows with no match in another table (MySQL manual: JOIN).

1. Count the orphans

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

Write the number down. If it's zero, you're done.

2. See what they are

SELECT pm.meta_key, COUNT(*) AS orphan_rows
FROM wp_postmeta AS pm
LEFT JOIN wp_posts AS p ON p.ID = pm.post_id
WHERE p.ID IS NULL
GROUP BY pm.meta_key
ORDER BY orphan_rows DESC;

This lists the orphaned rows by key, most first. Look before you delete. If a key you don't recognize shows up in large numbers, find out which plugin uses it before you go on.

3. Delete them

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

This is MySQL's multiple-table DELETE with a LEFT JOIN, the form its manual gives for deleting rows that have no match in another table (MySQL manual: DELETE). Only rows from pm, the postmeta table, are deleted; the posts table is only used to check. MariaDB documents the same multiple-table form (MariaDB documentation). The number of rows deleted should match your count from step 1. If it's far off, restore the backup and find out why before you try again.

4. Check the site

Open the home page and a few posts and pages. Look at the media library, your menus, and your forms. Check the settings of the plugins you rely on. If anything is missing, restore the backup.

5. Optional: reclaim the space

After a large delete you can rebuild the table:

OPTIMIZE TABLE wp_postmeta;

MySQL's manual says that for an InnoDB table kept in its own file, this reorganizes the table and its indexes and lets the freed disk space go back to the operating system (MySQL manual: OPTIMIZE TABLE). It rebuilds the whole table, so run it when the site is quiet.

Leave live meta keys alone

The steps above only touch rows whose post is gone. Deleting rows by meta_key is riskier. A key you don't recognize may belong to a plugin or theme you still use, and many are hidden from the dashboard because they start with an underscore. Delete a key a plugin still reads, and the plugin loses whatever it stored there, with no warning. If a key belongs to a plugin you've already deleted for good, check that nothing else uses the same name, take a fresh backup, and remove it on its own.

If you use a cleanup plugin instead, the same rules apply: back up first, and know what each option deletes before you tick it.

For the rest of the routine, my monthly WordPress checklist lists ten tasks in order, and what it takes to look after a WordPress site explains why each part matters.

How I can help

If you'd rather not run SQL on your live site, I host and look after existing WordPress sites case by case. Tell me about yours and I'll send a written quote for hosting and upkeep before anything changes. Content and design changes aren't included. A $500 deposit starts the quote. It's refundable until you accept, and credited to the work. Ask for a quote.