Shrinking a Bloated WordPress Database: Revisions, Orphans and Autoload

Preparation: Backup Before and Verify After

Before touching production data, create a restorable dump and record baseline metrics. Use WP-CLI 2.11 (current stable in 2026) or mysqldump. Store the file outside the web root, for example /var/backups/wp-db-2026-06-01.sql.gz. Baseline numbers let you prove the cleanup worked, and the dump is your rollback path.

Take the backup

# WP-CLI export
sudo -u www-data wp db export /var/backups/wp-db-$(date +%F).sql --path=/var/www/html

# Or native mysqldump
mysqldump -u root -p --single-transaction --routines wordpress_db 
  | gzip > /var/backups/wp-db-$(date +%F).sql.gz

Record the baseline

Run these and save the output to a text file so you can diff later:

SELECT COUNT(*) AS revisions FROM wp_posts WHERE post_type = 'revision';
SELECT ROUND(SUM(LENGTH(option_value))/1024,2) AS autoload_kb
  FROM wp_options WHERE autoload = 'yes';
SELECT COUNT(*) AS orphan_meta FROM wp_postmeta pm
  LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL;

Common pitfall: forgetting multisite table prefixes (wp_2_posts). If you run a network, loop over each blog ID with wp db export --tables=wp_2_* or adjust prefixes per site. Rollback is simply restoring the dump: wp db import /var/backups/wp-db-2026-06-01.sql.

Step-by-Step Cleanup Process

1. Export and baseline the database (see Preparation above).
2. Delete post revisions and auto-drafts with a capped keep policy.
3. Remove orphaned options and orphaned postmeta rows.
4. Identify and fix oversized autoloaded options.
5. Re-verify counts, autoload size and front-end query time.
6. Restore from backup if any step breaks the site.

Revisions and Auto-Drafts

Every save creates a row in wp_posts with post_type = 'revision'. Over years this reaches hundreds of thousands of rows and inflates wp_postmeta too. Auto-drafts from abandoned sessions pile up as post_status = 'auto-draft'. The fix is deletion plus a permanent limit in wp-config.php.

Delete old revisions and auto-drafts

-- Delete revisions older than 30 days, keep recent ones
delete r from wp_posts r
  join wp_posts p on r.post_parent = p.ID
 where r.post_type = 'revision'
   and r.post_date < date_sub(now(), interval 30 day);

-- Remove stale auto-drafts
delete from wp_posts
 where post_status = 'auto-draft'
   and post_date < date_sub(now(), interval 7 day);

-- Clean their orphaned meta
delete pm from wp_postmeta pm
  left join wp_posts p on p.ID = pm.post_id
 where p.ID is null;

Prevent regrowth

Add to wp-config.php above the /* That's all */ line:

define( 'WP_POST_REVISIONS', 10 );
define( 'AUTOSAVE_INTERVAL', 120 );

Verification: rerun SELECT COUNT(*) FROM wp_posts WHERE post_type='revision'; and confirm it is near your cap times live posts. Pitfall: do not set WP_POST_REVISIONS to false if editors rely on history; use a number instead. If you delete too much, restore from the dump taken in Preparation.

Orphaned Options and Meta

Orphans appear when a plugin is removed without cleanup, or when posts are hard-deleted. They waste space and slow meta queries. You find them with LEFT JOIN anti-patterns.

Find and delete orphaned postmeta

-- Count first, then delete
select count(*) from wp_postmeta pm
  left join wp_posts p on p.ID = pm.post_id
 where p.ID is null;

delete pm from wp_postmeta pm
  left join wp_posts p on p.ID = pm.post_id
 where p.ID is null;

Orphaned term relationships and options

delete tr from wp_term_relationships tr
  left join wp_posts p on p.ID = tr.object_id
 where p.ID is null;

-- Options left by deleted plugins (example prefixes)
select option_name from wp_options
 where option_name like 'wpcf7_%'
    or option_name like 'revslider_%';

Review the list before deleting. For plugin leftovers, confirm the plugin is deactivated and removed from wp-content/plugins/. Verification: the anti-join counts must return 0. Rollback: re-import the backup or re-add the specific rows from the saved SQL if you kept a per-table export.

Oversized Autoload

Options with autoload = 'yes' load on every request. When this payload exceeds roughly 800 KB to 1 MB, MySQL spends real time transferring it and PHP memory climbs. This is the single most common cause of a sluggish dashboard.

Measure autoload weight

select option_name,
       round(length(option_value)/1024,2) as size_kb
  from wp_options
 where autoload = 'yes'
 order by size_kb desc limit 20;

Fix heavy rows

For rows you do not need on every page, flip autoload to no:

update wp_options set autoload = 'no'
 where option_name in ('large_transient_cache','old_plugin_blob');

-- Or delete outright if unused
delete from wp_options where option_name = 'dead_plugin_data';

Use wp_cache_flush() or wp cache flush after changes so the object cache reloads. Verification: rerun the size query; target under 800 KB. Pitfall: do not set a required core option (like siteurl) to no — the site will break. If that happens, restore the row from backup: update wp_options set autoload='yes' where option_name='siteurl';. For teams needing deeper tuning, our Custom WordPress Development team audits autoload payloads on high-traffic installs.

Verification and Rollback

After all steps, compare against the baseline file. Use Query Monitor 3.15 (2026 release) to confirm DB query time dropped. Check the front end and wp-admin for missing menus, which signals a wrongly deleted option.

Metric	Before	After target
Revisions	recorded count	near cap
Orphan meta	recorded count	0
Autoload KB	recorded size	< 800

If the site white-screens, restore immediately: wp db import /var/backups/wp-db-2026-06-01.sql. Keep the backup for seven days. Related reading: if corruption appears, see Choosing a Vendor for WordPress Database Connection Error 2026.

Action Checklist

• Export a full database dump and store it outside the web root.
• Record baseline revision, orphan and autoload numbers before deleting.
• Delete old revisions and auto-drafts, then cap them in wp-config.php.
• Purge orphaned postmeta and term relationships with anti-join deletes.
• Trim oversized autoloaded options and flush the object cache.

Need hands-on help applying this cleanup safely? Hire our engineers for a full database audit — we back up, optimize and verify so your site stays fast.

Frequently asked questions

How often should I run a WordPress database cleanup?

Run the revision and orphan purge quarterly for busy sites, or monthly for WooCommerce stores. Autoload should be reviewed whenever you add or remove a major plugin.

Is deleting revisions safe for my content?

Yes, if you keep recent ones. Revisions are copies of post edits, not the published post itself. Always keep the backup so you can restore any accidental loss.

Why is my wp-admin still slow after cleanup?

Remaining autoload bloat or a missing index is usually the cause. Check slow queries in Query Monitor and consider reviewing our notes on Choosing a Vendor for WordPress Site Not Loading Fix in 2026.

“, “faq”: [ { “question”: “How often should I run a WordPress database cleanup?”, “answer”: “Run the revision and orphan purge quarterly for busy sites, or monthly for WooCommerce stores. Autoload should be reviewed whenever you add or remove a major plugin.” }, { “question”: “Is deleting revisions safe for my content?”, “answer”: “Yes, if you keep recent ones. Revisions are copies of post edits, not the published post itself. Always keep the backup so you can restore any accidental loss.” }, { “question”: “Why is my wp-admin still slow after cleanup?”, “answer”: “Remaining autoload bloat or a missing index is usually the cause. Check slow queries in Query Monitor and review remaining plugin options.” } ], “cover”: “A modern flat technical illustration of a layered database cylinder shedding heavy blocks labeled revisions and orphaned rows, with a slim green arrow showing optimized autoload flow on a clean blue gradient background.

References & further reading

• WordPress Developer Documentation
• WordPress Theme Handbook
• WordPress Plugin Handbook