The wp_postmeta table stores supplementary data for posts, pages, products and orders: each unique field and each plugin setting occupies a single row. It does not have its own cleaner. If you remove a plugin, its rows usually remain, as few plugins delete their data upon removal, and after a major version update, old key names may also remain.

For an online shop, the table grows to contain millions of records over the years. You’ll notice this in three ways: filtered queries (such as product filters) become slower, backups and restores take longer, and storage space runs out.

1. How big is the table, and what does it contain?

You can view the table sizes in phpMyAdmin (cPanel ‘Databases’ section) on the ‘Structure’ tab of the database. The rows are organised by key name:

SELECT meta_key, COUNT(*) AS db
FROM 1vL7334_postmeta
GROUP BY meta_key
ORDER BY db DESC
LIMIT 40;

The table prefix (1vL7334_ in the example) is random on websites installed with WP Toolkit; you can view your own using the wp db prefix command, and it is also visible in the left-hand list in phpMyAdmin. From the command line, you can run the query using the mysql command; however, the wp db query command does not work on aWh’s cPanel servers (as proc_open is disabled). The configuration of the ~/.my.cnf file containing the password is described in the article on using WP-CLI.

cd ~/public_html
P=$(wp db prefix); DB=$(wp config get DB_NAME)
mysql "$DB" -e "SELECT meta_key, COUNT(*) AS db FROM ${P}postmeta GROUP BY meta_key ORDER BY db DESC LIMIT 40"

2. What is unnecessary?

Keys for removed plugins. The prefix of the key names usually refers to the plugin (_yoast_, _elementor_, _wc_, _aioseo_). Compare this list with your installed plugins:

wp plugin list --fields=name,status

If a key family is large and the extension is no longer listed, there is a good chance it can be deleted. If you are unsure, search for the key name: the extension’s documentation or forum will usually tell you which extension it is.

Orphaned lines whose corresponding entries no longer exist (for example, following deleted orders or entries permanently deleted from the recycle bin):

SELECT COUNT(*)
FROM 1vL7334_postmeta pm
LEFT JOIN 1vL7334_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

On a healthy website, this figure is zero or very small.

WooCommerce orders. The newer version of WooCommerce stores orders in its own tables (HPOS). If your website has been migrated and synchronisation is disabled, the old order data may still be present in the wp_postmeta table. Do not delete these manually; instead, use WooCommerce’s own tool (WooCommerce › Settings › Advanced › Functions), and only do so once you have verified that the orders are complete in the new tables.

3. Backup

Before deleting anything, perform a backup of the database:

mysqldump --single-transaction --no-tablespaces "$DB" > ~/postmeta-elott-$(date +%F).sql
tail -1 ~/postmeta-elott-$(date +%F).sql

The last line should read -- Dump completed on … The ‘Export’ tab in phpMyAdmin and the daily JetBackup backup are also good options (restore from backup).

4. Deletion in small batches

A single large DELETE command on hundreds of thousands of rows will lock the table for a long time. As every entry and order save writes to this table, the website will slow down during this time, the checkout may freeze, and the operation may also hit the hosting resource limit. It is better to delete in batches of 5,000 rows, with short breaks in between:

KULCS='_regibovitmeny_adat'
while :; do
  N=$(mysql "$DB" -N -e "DELETE FROM ${P}postmeta WHERE meta_key = '${KULCS}' LIMIT 5000; SELECT ROW_COUNT();")
  echo "törölve: $N"
  [ "$N" -eq 0 ] && break
  sleep 1
done

It loops through every row and stops when there is nothing left to delete. The same pattern applies to orphaned rows; the query is:

DELETE pm FROM 1vL7334_postmeta pm
LEFT JOIN 1vL7334_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

MySQL does not allow this format with LIMIT; if the number of results is large, delete them by ID range (for example, AND pm.meta_id < 100000, then < 200000), or use a cleaning plugin such as WP-Optimize, which also works in batches.

Without SSH, you can run the same queries in the SQL tab of phpMyAdmin; repeat the LIMIT 5000 version until the number of affected rows drops to zero.

5. Compressing the table

The space occupied by the deleted rows within the table is freed up, but the file on the disk will not become smaller, nor will the backup process be speeded up, until the table is rebuilt:

mysql "$DB" -e "OPTIMIZE TABLE ${P}postmeta"

In the response, the comment Table does not support optimize, doing recreate + analyze instead is normal (this is how MySQL rebuilds the table for InnoDB tables); the key point is the OK status in the last line. The operation locks the table for a few seconds, so run it during periods of low traffic. In phpMyAdmin: select the table and choose the ‘Optimise Table’ operation.

Verification

Run the first query again and compare the number of rows with the table size. Go through the important parts of the website (product pages, filters, checkout, page builder templates): if anything is missing, you have deleted a key that was still in use, and you can restore it from the backup.

If, in addition to the wp_postmeta table, the admin interface is also running slowly, the article on automatically loaded data in the wp_options table may help; if the database is growing due to background tasks, please read the Action Scheduler article.