WordPress Database Bloat: Cleaning Revisions, Transients, and Spam

A fresh WordPress database is a few megabytes. After a year of running, it can balloon to hundreds of megabytes — even gigabytes — of accumulated junk. Post revisions, expired transients, spam comments, and orphaned metadata silently slow down every query.

The Hidden Cost of Database Bloat

Every database query scans through or indexes this bloated data. More rows mean slower queries, larger backups, and longer migration times. A database with 200,000 post revisions does not just waste disk space — it makes your wp_posts table queries measurably slower.

Post Revisions

WordPress saves a new revision every time you hit Save or Update. A post edited 50 times has 50 revisions stored as full copies in wp_posts, each with its own set of meta data in wp_postmeta.

To see how many revisions you have:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

To clean them up (keep the 3 most recent per post):

DELETE FROM wp_posts WHERE post_type = 'revision'
AND ID NOT IN (
    SELECT ID FROM (
        SELECT ID FROM wp_posts WHERE post_type = 'revision'
        ORDER BY post_date DESC LIMIT 3
    ) AS keep
);

Expired Transients

Transients are temporary cached data stored in wp_options. Expired transients should be cleaned up automatically, but WordPress only deletes them when they are accessed — orphaned transients accumulate silently.

DELETE FROM wp_options WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

DELETE FROM wp_options WHERE option_name LIKE '_transient_%'
AND option_name NOT LIKE '_transient_timeout_%'
AND option_name NOT IN (
    SELECT REPLACE(option_name, '_transient_timeout_', '_transient_')
    FROM wp_options WHERE option_name LIKE '_transient_timeout_%'
);

Spam and Trashed Comments

If you have been running a site for years, you might have thousands of spam and trashed comments:

DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';

Orphaned Post Meta

When posts are deleted, their meta data sometimes remains:

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

After Cleanup: Optimize

After deleting rows, run OPTIMIZE TABLE to reclaim disk space and defragment the tables:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;

If running SQL manually feels risky, WP Multitool's Database Cleanup module handles all of this through a visual interface. It shows you exactly what will be deleted with size estimates before you confirm, and creates an automatic backup first.

Schedule a database cleanup once a month. Your queries — and your backup process — will thank you.