Clean up WordPress database bloat by removing orphaned postmeta and revisions

Home » MySQL » Clean up WordPress database bloat by removing orphaned postmeta and revisions
July 30, 2022 MySQL, Tips
Clean up WordPress database bloat by removing orphaned postmeta and revisions

WordPress databases accumulate bloat over time from sources that are easy to overlook: every post revision creates a copy of the entire post content in wp_posts, orphaned rows pile up in wp_postmeta when posts are deleted without cleaning their meta, the wp_options table fills with expired transients that were never purged, and spam comments accumulate in wp_comments. On an active site that has been running for several years, wp_postmeta can contain millions of orphaned rows, wp_posts can have ten times more revision rows than actual posts, and wp_options can hold thousands of expired transients. This bloat increases the time of every database query that touches these tables, inflates backup file sizes, and slows down admin screens. The safe cleanup order is: first identify the scope of the problem with COUNT queries, then delete in batches to avoid locking tables for a long time, then run OPTIMIZE TABLE to reclaim the freed disk space and rebuild the table index. WordPress’s built-in revision system can be configured with WP_POST_REVISIONS in wp-config.php to limit the number of revisions kept per post going forward. WP-CLI provides purpose-built commands for this cleanup that are safer than raw SQL because they go through WordPress hooks: wp post delete --post_type=revision and wp transient delete --expired. Always take a database backup before running any bulk delete operation — the backup script guide shows how to automate this. Review the slow query log guide after cleanup to confirm query times improved.

Problem: The WordPress database has grown to several gigabytes due to post revisions, orphaned postmeta, expired transients, and spam comments, causing slow queries and large backup files.

Solution: Audit the bloat with COUNT queries, then clean each category safely with WP-CLI or batched SQL:

-- Audit: count revisions
SELECT COUNT(*) AS revision_count FROM wp_posts WHERE post_type = 'revision';

-- Audit: count orphaned postmeta (rows whose post no longer exists)
SELECT COUNT(*) FROM wp_postmeta pm
WHERE NOT EXISTS (SELECT 1 FROM wp_posts p WHERE p.ID = pm.post_id);

-- Audit: count expired transients in wp_options
SELECT COUNT(*) FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
  AND option_value < UNIX_TIMESTAMP();

-- Audit: count spam and trashed comments
SELECT post_status, COUNT(*) FROM wp_comments
GROUP BY comment_approved;

-- Delete all post revisions (safe — revisions have no wp_postmeta of their own usually)
DELETE FROM wp_posts WHERE post_type = 'revision';

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

-- Delete expired transients (both the value and the timeout key)
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
  AND option_name NOT LIKE '_transient_timeout_%'
  AND option_name IN (
      SELECT CONCAT('_transient_', REPLACE(t.option_name, '_transient_timeout_', ''))
      FROM (SELECT option_name FROM wp_options
            WHERE option_name LIKE '_transient_timeout_%'
              AND option_value < UNIX_TIMESTAMP()) t
  );

-- Reclaim disk space and rebuild indexes after bulk deletes
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;

# Safer cleanup via WP-CLI (runs through WordPress hooks)
# Delete all revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

# Delete expired transients
wp transient delete --expired

# Delete spam and trash comments
wp comment delete $(wp comment list --status=spam --format=ids)
wp comment delete $(wp comment list --status=trash --format=ids)

# Optimise all tables
wp db optimize

# Limit future revisions — add to wp-config.php
# define( 'WP_POST_REVISIONS', 3 );

NOTE: Run the SQL DELETE statements in batches with a LIMIT clause on large tables to avoid a single long-running query that locks the table: add LIMIT 10000 to each DELETE and run it repeatedly until affected rows reaches zero. The OPTIMIZE TABLE command rebuilds the table and its indexes, which can take several minutes on large tables — run it during a low-traffic window. Add define( ‘WP_POST_REVISIONS’, 3 ); to wp-config.php to prevent unlimited revision accumulation going forward — three revisions per post is enough for most sites.

Related posts