Cleaning up the post revisions with WP-CLI

Over the months, or years, that you use WordPress, the database starts to bloat with stuff that you don’t necessarily need. Post revisions are generated regularly and are not deleted unless you do it manually or you have added code to handle that automatically.

The WordPress database does grow big if you let revisions accumulate. I have recently started working for a small company who needed someone to manage/maintain their website (hey, hire me!). The WordPress database given to me was 44MB in size – quite a big database for a simple website with a few options.

I ran a WP-CLI query using wp post list to see how many of the posts can be deleted. There were no post in the Trash, no post were saved as Draft but we had more than a 1700 post revisions! 😱

▶ wp post list --post_type='trash' 
+----+------------+-----------+-----------+-------------+
| ID | post_title | post_name | post_date | post_status |
+----+------------+-----------+-----------+-------------+
+----+------------+-----------+-----------+-------------+
▶ wp post list --post_type='draft' 
+----+------------+-----------+-----------+-------------+
| ID | post_title | post_name | post_date | post_status |
+----+------------+-----------+-----------+-------------+
+----+------------+-----------+-----------+-------------+
▶ wp post list --post_type='revision' | wc -l
    1706

Since we won’t be needing the post revisions, the next step would be to delete the post revisions. It’s just a one-liner with WP-CLI:

▶ wp post delete $(wp post list --post_type='revision' --format=ids) --force

You actually need the --force flag because you can’t send the post revision to the WordPress and it should be permanently deleted. You get this warning if you try to delete a post revision without adding --force:

Warning: Posts of type 'revision' do not support being sent to trash.
Please use the --force flag to skip trash and delete them permanently.

Once I’m done, I’ve exported the database again and compared the size. The new database size was only 5.7MB, the original database was more than 7 times bigger! That’s a great improvement!

▶ ls -lah | grep sql
-rw-r--r--    1 rikesh  staff   5.7M Mar 24 17:34 cleaned.sql
-rw-r--r--    1 rikesh  staff    44M Mar 23 22:48 original.sql