A WordPress database collects sediment: every draft saved, every plugin tried and deleted, every cached value that never expired. Years of it makes backups slow, migrations painful, and some queries genuinely sluggish. This cleanup runs with WP-CLI over SSH, and every destructive step here comes after the backup — portage rules, pack before you carry.

Back up and measure the starting size

wp db export ../backups/pre-cleanup-$(date +%Y-%m-%d).sql
wp db size --human-readable

Write the number down — the whole payoff of this lesson is comparing it to the one at the end. If restoring that export isn’t something you’ve done before, run the backup lesson first, because today you’ll be deleting things on purpose.

See which tables are fat

wp db size --tables --human-readable

Read the list like a map. wp_posts and wp_postmeta huge? Revisions, probably. wp_options huge? Transients and plugin leftovers. A table named after a plugin you removed in 2021? That’s step five. Knowing where the weight is keeps you from cleaning the wrong closet.

Trim revisions and auto-drafts

WordPress keeps every revision of every post forever by default. Count them, then clear them:

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

(If either delete complains about no IDs, that list was already empty — that’s a pass, not a failure.) Then cap future pileup in wp-config.php, above the “That’s all, stop editing!” line:

define( 'WP_POST_REVISIONS', 5 );

Five is plenty of undo for most sites without hoarding a decade of drafts.

Clear expired transients

Transients are cached values with an expiry date — but WordPress only deletes an expired one if something asks for it again. The ones nothing asks about just sit in wp_options, sometimes tens of thousands deep:

wp transient delete --expired

This one is entirely safe: everything it removes was already past its sell-by date. It’s also a great weekly cron job so the pile never rebuilds.

Find tables from plugins long gone

Most plugins create their own tables and leave them behind when deleted:

wp db tables
wp plugin list --field=name

Compare the two lists. A table whose name matches no active plugin and no core table (core tables are the ones like wp_posts, wp_options, wp_users) is probably an orphan. Verify before dropping — search the table name plus “wordpress” online, and when in doubt, leave it. Then:

wp db query "DROP TABLE wp_departed_plugin_data;"

This is the one irreversible step in the lesson, which is exactly why the backup from step one exists.

Optimize and measure again

Deleting rows doesn’t shrink files until you ask MySQL to tidy up:

wp db optimize
wp db size --human-readable

Compare against your step-one number — a long-neglected site commonly drops by half or more. Then verify the site itself: load the front page, log in, open a recent post and check its revision history still shows the kept few, and click through whatever the site is for. If it all behaves and the number is smaller, you’re done — and next time, the cleanup takes ten minutes because the sediment never got deep.