Cleaning WordPress Databases

WordPress is a fine blogging software but the standard package lacks a disturbing number of basic features, even including Akismet and Jetpack. Yesterday I discovered that WordPress, which automatically saves post revisions for every edit and preview, never ever deletes them! Not only does WordPress not delete old revisions automatically, it does not even offer manual controls for that purpose. You are actually expected to directly edit your MySQL database, using phpMyAdmin or the like. I can only warn against such attempts – it’s way too easy to accidentally destroy your database that way.

Once I realized what was missing, the fix was fortunately easy: just download one of several third-party plugins for database maintenance. Rolf van Gelder’s unsexily named Optimize Database after Deleting Revisions is under active development and does just what it says. Not mentioned explicitly, this plugin also deletes unused auto-save drafts (comprised under post orphans). There’s even a scheduler but manual maintenance works fine, too.

Another option that I successfully tested on my WordPress 3.6 installation is Ruhani Rabin’s WP-Optimize. It lacks an automatic scheduler but shows database statistics before the optimization, rather than only afterward. However, it was last updated in December 2011 so you’ll probably want to try van Gelder’s plugin first.

Whichever you choose, expect a dramatic improvement in space usage. In my case, nearly 500 obsolete revisions were deleted, shrinking the post database from over 6 MB to 730 KB! This effect scales with larger blogs, too – reviews claim up to 100 recovered megabytes. Not exactly trivial if you’re on a cheap hosting account with a limited shared database…

2013-08-08: Ryan Markel informed me of a WordPress configuration option that can limit the number of revisions saved per post. That’s nice but really not sufficient as the limit is set per post, so the post DB still keeps obsolete revisions around forever, wasting space linear to the total number of posts. You need one of the above plugins to clean them up.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.