Dealing with WordPress Post Revisions

Dealing with post revisions in WordPress DatabaseDid you know WordPress, like a good boy, saves all post/page revisions in your DB — by default — for those just-in-case situations where you might need to revert to an old version? The problem with anything just-in-case is that requirement comes once in a blue moon. But you’d still like to be ready for that occasion, just like you like to be ready with backups if things go wrong.

The downside? For all you know, you DB is full of revisions — could even be in the range of thousands, tens of thousands, or worse — depending on the total number and frequency of posts on your WordPress site. End result? Your DB is becoming unnecessarily bulky.

Let’s take a look at how to tackle this situation like a lazy webmaster with a plugin called WP-Database-Optimizer-Tools.

This one comes in handy for optimising and reparing DB tables, and taking backups, if you’d like. After installation you get a new menu entry in WordPress Dashboard — DB Optimizer as you can see in the following screenshot.

The menu entries for the plugin

However, it doesn’t come with any settings to schedule the above-mentioned operations. This makes it a strict no-no for lazy admins like myself for the purpose of taking DB backups or repairing and optimising tables at scheduled intervals. Still I use it for the one single purpose — deleting post revisions whenever I’ve had enough of them — available under the DB Optimizer –> DB Optimizer sub-menu.

You see, the best way to avoid having any revisions at all is to add the following one-line statement to your wp-config.php file:

define('WP_POST_REVISIONS', false );

You can even control the number of revisions per post with this statement. For more details, refer to the Post Revisions subsection of this WordPress codex article. And I guess limiting the number to something like 10 to 15 would be a safe bet for most publishers. However, at LINUX For You, we need to publish a lot of code inside articles as well. And sometimes a statement in the code makes a plugin act awkwardly. Here’s an example.

The story goes like this: I formatted all the stuff in the post, hit save draft, check preview. WTF? Where did all those messy things inside pre tags from from? Remove those messy things carefully, hit save draft again, click preview. Whoa! Where did the whole post disappear?

Thanks to the awesome plugin developer of WP SyntaxHighlighter, he was able to identify and fix the issue very quickly — in less than a few hours if I remember correctly. But that’s not the point. The point is: if I didn’t have revisions enabled, I might have had to redo formatting the layout of the whole post again from scratch. Here, at least, I could go back to an auto-draft revision that had the full text, but only half formatted — before that awk statement triggered the plugin to malfunction.

The lesson learnt? Have as many revisions as you want while composing a post — all those auto-drafts also add up to revisions, and not just manually hitting save draft. Then at a later date, maybe weekly or something, delete all the revisions across posts that have accumulated.

This is exactly where this WP-Database-Optimizer-Tools plugins comes in handy. Under the DB Optimizer section, it shows me the number of revisions that are stored in the DB, and enables me with a way to delete them at one go. It additionally takes care of stored auto-drafts, trashed posts, comment spam, etc. Have a look at the following screenshot.

Too many revisions bloating the database?

So, I check all the boxes — most important is the optimise DB table box at the end — and hit Process. Now, checkout this next screenshot.

Ended up deleting revisions, and DB optimising tables that saved me some space

Those 280 revisions were sitting pretty with 3.3MB of space in the DB — which we’re able to free up. Neat?

If you notice carefully I missed out on the auto-draft checkbox, so that still remained. My mistake.

That leaves one thing unanswered. How to automate DB and files backups, besides table repairs and optimisations at scheduled intervals? I”ll come back later today with the review of an all-purpose plugin that we use for this exact purpose. I need time to write :-P

Feature image courtesy: Jennie. Modified and reused under the terms of CC-BY-SA 2.0 License.

All published articles are released under Creative Commons Attribution-NonCommercial 3.0 Unported License, unless otherwise noted.
Open Source For You is powered by WordPress, which gladly sits on top of a CentOS-based LEMP stack.

Creative Commons License.