Tips for Trimming WordPress Database Revisions

Migrating a WordPress site onto the Pantheon platform is an easy process and can be done with just a few clicks. However, one of the limitations to that import process is the size of the tarball you create.

If it's too large, it is unable to be imported. One of the factors that determines the size of that tarball is your database export. Fortunately, there are several things you can do to shrink it and here are a few quick ways to remove some unneeded records, reduce the size of your overall export and clean up your database as well.

Removing all the “Revision” Records in the Posts Table

WordPress is a wonderful tool, but there are still a few holes in its architecture. The fact that revisions stack up in your database is one of these holes. Normally it is not a problem but if you are preparing to export a large WordPress-powered site to Pantheon, you could quickly hit the limits of our importer. (100MB for files uploaded, 500 MB for files passed in via URL.) One easy way to lower the size of your database is to remove all the "revision" records in the Posts table.

Revisions

WordPress stores revisions of posts. As you make changes and save them - whether in draft mode or published - WordPress stores a "Revision History" for each post. This is a very good thing: it has saved my bacon on more than one occasion. However, once a post goes live, I rarely need those revisions. Sadly though, WordPress has yet to incorporate a DB Cleanup feature that allows you to specify something like "Remove revisions after 30 days" or something. The problem is that each of these records contains a complete copy of the entire post. For verbose writers like myself, this can be a problem. 

Currently, the wp_posts table in my personal blog contains 10,820 records. Looking at it, 2,464 of those records are historical revisions. Let's take a look at a single post to see what that looks like in detail.

I am arrogant enough to have an Electronic Press Kit on my blog. I use it when people ask me to speak and want a bio, picture, etc. That is an old page on my site, originally created many years ago, so it has some history. In my wp_posts table, that page id is 302. Let's look at it and its history.

The first SELECT picks up the parent record, the next one picks up all the revisions. Using the database that powers my database, this returns 34 rows: the original and 33 revisions. The latter 33 records are not necessary and could easily be done away with. There are a few ways you can do this.

1. Manually
If you have the MySQL command line tool installed you can issue the command directly. This is by far the easiest way.

• Connect to your database using the MySQL Command Line tool or your favorite SQL tool.

• Copy and paste this command into your command window once connected.

  DELETE FROM wp_posts WHERE post_type=”revision”;

• Boom goes the dynamite. The records are now gone.

2. Via Functions.php

• Open the functions.php file for your site.

• Somewhere near the top of your functions.php file - but AFTER the opening <?PHP tag - paste this line:

  add_filter( 'wp_revisions_to_keep', 'limit_revision', 10, 2 );

  function limit_revision($num, $post) {

  if( ('post' == $post->post_type) || ('page' == $post->post_type) ) $num = 5;

  return $num;

  }

• Save it, now open a browser and visit your blog.
• Once you've visited your home page, you can re-open functions.php and remove that line and save the file again.
• If you are not comfortable issuing commands directly to your database, there is another way. It does, however, require being comfortable with PHP code.

You only need to do it once and all the old revisions are gone. It is safe to leave the line in your functions.php. However, I do not recommend that as it will be executed every time anyone visits a page on your blog. That is a lot of deleted statements and it will affect your blog's performance.

3. Via wp-config.php
Another option is via wp-config.php. By adding this line, you can limit posts' revisions to five, resulting in a smaller database.

define('WP_POST_REVISIONS', 5);

4. Via WP CLI
If you prefer to use the command line, you can use this command:

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

5. Plugin Alternatives

Finally, if you would prefer to stay away from commands altogether, there are a few plugins that will help clean up database revisions.

• WP Optimize Cache is an all-in-one WordPress performance plugin that caches your site, cleans your database and compresses your images.
• WP Sweep uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries.
• Simple Revisions Delete lets you delete your posts' revisions individually or all at once (purge or bulk action).

That's all there is to it. Even if you are not exporting your database to import into Pantheon, this is just good database hygiene.