WordPress database maintenance: cleaning up revisions, orphaned postmeta, and bloated tables

A WordPress database grows in two ways: intentional content (posts, pages, products, comments) and accumulated waste (revisions of every saved draft, expired transients that never get cleaned up, metadata for posts that no longer exist, auto-drafts that were never published). On a site that has been running for three or more years, the waste can outweigh the content. The database still works — MySQL does not care — but backups take longer, migrations move more data than necessary, and queries on bloated tables are slower than they need to be.

This article covers the waste, not the queries. If the problem is slow query execution on a database that is already the right size, the slow database article covers that. If the problem is specifically the wp_options table and autoloaded data, the autoloaded data article goes deeper on that one table.

What builds up in a WordPress database over time

Post revisions

WordPress saves a revision every time you click Save Draft or Update on a post or page. By default there is no limit. A post that has been edited 200 times has 200 revisions, each stored as a full row in wp_posts with its own set of rows in wp_postmeta. On a WooCommerce store, products also generate revisions.

Expired transients

Transients are temporary key-value pairs stored in wp_options (or in an object cache backend if one is active). Plugins use them to cache API responses, license checks, and computed data. When a transient expires, WordPress does not proactively delete it. It only deletes an expired transient when something reads it. On a low-traffic site with many plugins, expired transients accumulate indefinitely.

Orphaned postmeta

When a post is permanently deleted, WordPress deletes its wp_posts row but does not always clean up associated rows in wp_postmeta. Plugins that store custom fields for posts are especially prone to leaving orphans. Over time, wp_postmeta accumulates rows that reference post_id values that no longer exist.

Auto-drafts and trashed posts

WordPress creates an auto-draft every time you click "Add New" on a post or page. If you navigate away without publishing, the auto-draft stays. WordPress has a built-in cleanup for auto-drafts older than 7 days via wp_delete_auto_drafts(), but this runs on the wp_scheduled_auto_draft_delete cron event. If WP-Cron is misconfigured or disabled, auto-drafts accumulate.

Trashed posts sit in the trash for 30 days (controlled by EMPTY_TRASH_DAYS in wp-config.php) before automatic deletion. If the cron does not run, they stay indefinitely.

Table fragmentation

InnoDB tables fragment over time as rows are inserted, updated, and deleted. The table file on disk grows but does not shrink when rows are removed. The data is not lost or corrupted — the space is reused for new rows eventually — but fragmented tables use more disk and more buffer pool memory than necessary.

Limiting and deleting post revisions

Cap revisions going forward

Add to wp-config.php:

define('WP_POST_REVISIONS', 5);

This limits each post to 5 revisions. WordPress keeps the 5 most recent and stops creating new ones until older ones are pushed out. Set to false to disable revisions entirely (not recommended — revisions are a safety net for content recovery).

Delete existing excess revisions with WP-CLI

WP-CLI is the safest non-interactive way to clean the database.

# Count existing revisions
wp post list --post_type=revision --format=count

# Delete all revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

For large sites with tens of thousands of revisions, batch the delete to avoid hitting PHP memory limits:

wp post list --post_type=revision --format=ids --posts_per_page=500 | xargs wp post delete --force

Run this in a loop until the count reaches zero.

Removing orphaned postmeta safely

Orphaned postmeta rows reference post_id values that no longer exist in wp_posts. This query identifies them:

SELECT COUNT(*)
FROM wp_postmeta
WHERE post_id NOT IN (SELECT ID FROM wp_posts);

If the count is significant, delete them:

DELETE FROM wp_postmeta
WHERE post_id NOT IN (SELECT ID FROM wp_posts);

Always back up the database before running DELETE queries. A single WHERE clause mistake can wipe production data.

On WooCommerce stores with the HPOS (High-Performance Order Storage) feature enabled, order data lives in custom tables (wp_wc_orders, wp_wc_orders_meta) rather than wp_posts/wp_postmeta. Orphan cleanup queries must account for both storage systems. See the WooCommerce database optimization guide for HPOS-specific cleanup.

Expired transients: what they are and how to purge them

Check how many expired transients exist:

SELECT COUNT(*)
FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

Delete expired transients:

wp transient delete --expired

Or via SQL:

DELETE a, b FROM wp_options a
INNER JOIN wp_options b ON b.option_name = REPLACE(a.option_name, '_transient_timeout_', '_transient_')
WHERE a.option_name LIKE '_transient_timeout_%'
AND a.option_value < UNIX_TIMESTAMP();

If the site uses an object cache backend (Redis, Memcached), transients are stored there instead of wp_options, and expired entries are handled by the cache backend's TTL mechanism. In that case, wp_options transient cleanup is unnecessary.

Auto-drafts and trashed posts

Delete auto-drafts

wp post delete $(wp post list --post_type=any --post_status=auto-draft --format=ids) --force

Empty the trash

wp post delete $(wp post list --post_type=any --post_status=trash --format=ids) --force

Verify WP-Cron is running

Auto-draft and trash cleanup depend on WP-Cron. Verify it is working:

wp cron event list | grep -E "auto_draft|trash"

If these events are missing or overdue, WP-Cron may be disabled or blocked. Check wp-config.php for DISABLE_WP_CRON and verify that the wp-cron.php URL is accessible externally.

Running OPTIMIZE TABLE safely

OPTIMIZE TABLE reclaims unused space in InnoDB tables and defragments the data file. On MySQL 8.0 and MariaDB 10.6+, this operation is performed using an online DDL algorithm that does not lock the table for reads or writes. On older versions, the table may be briefly locked.

Check which tables would benefit:

SELECT TABLE_NAME,
       ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
       ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

Tables with significant free_mb values have reclaimable space. Optimize the largest offenders:

OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;

On a production site, run OPTIMIZE TABLE during a low-traffic window. Even though MySQL 8.0+ does not lock the table, the operation consumes I/O and can temporarily increase query latency.

Using WP-CLI for non-interactive database cleanup

A complete cleanup script using WP-CLI:

#!/bin/bash
set -e

echo "=== WordPress Database Cleanup ==="

echo "Deleting post revisions..."
wp post delete $(wp post list --post_type=revision --format=ids --posts_per_page=1000) --force 2>/dev/null || echo "No revisions found"

echo "Deleting auto-drafts..."
wp post delete $(wp post list --post_type=any --post_status=auto-draft --format=ids) --force 2>/dev/null || echo "No auto-drafts found"

echo "Emptying trash..."
wp post delete $(wp post list --post_type=any --post_status=trash --format=ids) --force 2>/dev/null || echo "Trash is empty"

echo "Deleting expired transients..."
wp transient delete --expired

echo "Deleting spam comments..."
wp comment delete $(wp comment list --status=spam --format=ids) --force 2>/dev/null || echo "No spam comments"

echo "Optimizing tables..."
wp db optimize

echo "Done."

Setting up a maintenance schedule

Database waste is a recurring problem. Without a schedule, the cleanup is forgotten and the database grows back to its previous size within months.

Monthly: run the WP-CLI cleanup script above. Review wp_postmeta for orphan growth. Clear expired transients.

After every major content operation (bulk import, migration, plugin uninstall): run orphaned postmeta cleanup and OPTIMIZE TABLE on affected tables.

Permanently: set WP_POST_REVISIONS in wp-config.php to cap revisions at 5–10 per post. This is the single most effective preventive measure.

What database cleanup is NOT

• Not a substitute for query optimization. Cleaning the database reduces its size but does not fix slow queries caused by missing indexes, N+1 query patterns, or expensive plugin code. If the database is small and still slow, the problem is the queries, not the data. See the slow database article.
• Not risk-free when done with plugins alone. WP-Optimize, Advanced Database Cleaner, and similar plugins call OPTIMIZE TABLE and bulk-delete operations. On a production site, these operations consume I/O and should run during low-traffic windows. The plugins themselves are fine; the timing matters.
• Not a one-time task. Without revision caps and a maintenance schedule, the database regrows to its bloated state within months. The cleanup is only half the fix; the caps and schedule are the other half.