WP-CLI Delete Commands: Which Tables Each One Touches and What It Leaves Behind

Database housekeeping in WordPress is a handful of commands that all look alike and behave nothing alike. One goes through the application and cleans up after itself; the next one touches a single table and leaves four others holding rows that point at nothing.

What makes the difference matter is that WP-CLI, with a few exceptions, has no dry run. There is no way to ask a delete command what it would do. There is only the discipline of asking a different command first.

The Two-Step Pattern That Replaces the Dry Run

Every destructive command has a harmless sibling that takes the same filter. wp post delete has wp post list; wp comment delete has wp comment list. Running the list first with --format=count answers the only question a dry run would have answered: how many rows is this about to remove.

# 0. a backup, outside the document root
wp db export /home/pi/backup-$(date +%F).sql

# 1. count, with exactly the filter that will be used
wp post list --post_type=revision --format=count

# 2. delete, with exactly the same filter
wp post list --post_type=revision --format=ids | xargs -n 100 wp post delete --force

The xargs in the last line is not decoration. Substituting several thousand IDs directly into the command line runs into the argument length limit, and the failure arrives after some of the work is done rather than before any of it – which is the one situation the whole exercise was meant to avoid.

The backup line has a detail worth keeping: without a path, wp db export writes the dump into the current directory, and the current directory during a WordPress command is usually the document root. A full database dump with a guessable name, served over HTTP, is a worse outcome than anything the cleanup was going to fix.

Two Ways to Remove the Same Row

wp post delete calls the same function the editor calls. It removes the post meta, the comments, the comment meta and the term relationships, and it fires the hooks that let plugins remove whatever they attached elsewhere – a search index entry, a cache, a row in their own table.

A direct DELETE FROM wp_posts removes one row. Everything that referred to it stays, unreachable and invisible: no query joins to a post that is gone, so the rows simply never appear again. That is tolerable until an ID is reused, and post IDs are reused after a database import – at which point the new post silently inherits the old one’s custom fields and comments.

What Each Command Actually Touches

The first row is the one that surprises people. Deleting a post does not delete the images in it – attachments are posts in their own right, with their own IDs, and they survive their parent. A site cleaned of a thousand old posts therefore still carries a thousand posts’ worth of media, which is where the disk space actually went.

The comment count row is the cheapest to fix and the easiest to forget: after any bulk comment deletion, wp comment recount puts the numbers back in agreement with the rows. Nothing breaks without it; the counts are simply wrong, everywhere, quietly.

The Trash Is Not a Backup

Without --force, deleting moves a post to the trash, which is a status change and nothing more. The row stays, the meta stays, the media stays, and the space is not freed – a trash emptied once a month is a housekeeping habit, not a safety net.

With --force the post skips the trash entirely and there is no undo inside WordPress at all. The only thing standing between a mistyped filter and a lost month is the dump written in step zero, which is the reason it is step zero.

Expired Is Not the Same as All

Transients are a cache with an expiry stored alongside the value. wp transient delete --expired removes those whose time has passed and is safe by construction: anything it deletes was already invalid.

--all is a different command wearing the same name. It also removes transients stored without an expiry, and some plugins use exactly those as their only storage for something that was expensive to compute – a licence check, an inventory, a rendered menu. Nothing breaks visibly; the site simply spends the next hour rebuilding things nobody asked it to rebuild, and one or two plugins behave as if they had just been installed.

Optimising Is a Rebuild, Not a Tidy-Up

wp db optimize runs OPTIMIZE TABLE, and for InnoDB that is not an optimisation but a full rebuild of the table plus its indexes. The table is locked for writes while it happens, which on a small blog is a second and on a large wp_postmeta is long enough for visitors to notice.

It is also the step that most reliably reclaims nothing. Space freed by deleted rows is already reused by InnoDB for new rows; the file on disk only shrinks if a great deal was deleted and nothing has been written since. Worth running once after a large cleanup, at a quiet hour, and not worth putting in a nightly cron – which is precisely where it usually ends up.